A semantic approach to automated design and construction of star schemas

Abstract

Designing a star schema is a complex and time-consuming process requiring an expert to perform several tasks such as denormalization, dimension design, and construction of fact tables. This study presents a method to automatically design and generate star schema models, or so-called multidimensional models. We first introduce a method to incorporate a novel knowledge-based framework to enable an automation system to construct dimensional and fact tables as well as measures, which are the key elements of star schema models. The proposed framework provides a capability of column name identification using the arithmetic coding approach and measures identification using a natural language processing framework (NLP), resulting in dimensions and fact tables being constructed automatically without human intervention. Although the current version of our system is limited to reading data from semi-structured datasets such as CSV files and spreadsheets, the experimental results demonstrate that our framework can generate a star schema effectively, and can support online analytical processing (OLAP) operations. The experimental results show that our method is superior to other conventional approaches, achieving 96.67% accuracy for numerical data, higher than any of the prior models used for comparison.

Keywords

• semantic approach
• knowledge-based
• star schema
• data warehouse