Business Intelligence (BI) fosters proper decision-making in organizations, mainly by providing the means to analyze historical data stored in repositories called Data Warehouses (DW). However, formal representation of which concepts are implemented in a DW rarely exists, which would be important to clarify and semantically describe the domain concepts behind the data stored in a DW, as well as the analytical concepts that are available for the BI tools. Examples of important pieces of knowledge that are frequently hidden into the DW are: which domain concepts are available as analysis perspectives (dimensions), how the domain concepts relate to each other, which metrics (facts) are available and what do they mean, which domain perspectives are considered for each metric and how metrics may be aggregated. On the other hand, one of the relevant uses of an ontology for the Computer Science area is as a codified artifact that formally represents a shared conceptualization about a universe of discourse. Therefore, ontologies can be used to represent both domain and analytical concepts codified and stored in a DW. However, extracting these concepts from an already-inproduction DW is not a trivial task, especially in medium and large organizations, often with tens of metrics and tens (even hundreds) of dimensions and potential aggregations. In this paper, we define a set of mapping rules from DW constructs to conceptual elements (concepts and relationships), towards automatically extracting an ontology codified in OWL. The proposal was successfully evaluated in a real scenario of a Brazilian financial institution.
Organizations are overloaded by the increasing amount of data, which is continuously
generated and stored in corporate repositories, to be analyzed for proper
decisionmaking [
Despite the importance of analytical tools provided by BI solutions for current
organizations, there are challenges to leverage their impact on the decision-making
process [
On the other hand, a relevant use of ontologies for the Computer Science area is as an artifact that formally represents a shared conceptualization of a domain of discourse, through its key elements: concepts and relationships. Therefore, it is a natural artifact to describe the the semantics behind the data and metadata stored in a DW, thus providing a rich, explicit and upgradeable conceptual representation of the organizational data. Therefore, it would be very useful an application ontology describing the concepts implemented in the DW, in order to support the data analysis task, to explicit the concepts available and the relationships between them and the possible operations to be performed.
In this paper, we define a set of mapping rules from DW structural constructs to conceptual elements (concepts and relationships), towards automatically extracting an ontology codified in OWL, within BI systems. The proposal was applied in a real scenario.
This article is organized as follows: Section 2 presents the theoretical basis of the work, Section 3 presents our proposal, Section 4 describes the application scenario and the application example and Section 5 describes related works on automatic generation of ontologies from relational databases. Section 6 contains final considerations.
Business Intelligence (BI) is a set of theories, methodologies, architectures and
technologies in order to recover and transform raw data into meaningful and useful
information, allowing the operational, tactical and strategic levels of an organization
make better and more agile decisions [
OLAP (Online Analytical Processing) is the ability to analyze and manipulate information from multiple perspectives. OLAP tools provide its users with this capability through interactive interfaces that enable the execution of analytical operations defined as OLAP operators.
[
The multidimensional modeling is a subject-oriented data modeling technique, widely used in BI environments in Data Warehouse projects. The basic elements of multidimensional models are facts and dimensions. Facts are indicators (measures / metrics) to be analyzed and dimensions are analytical views on stored facts. Facts and dimensions are stored in different tables. A particular type of fact table is the Factless Fact table, because it have no measure columns. The information represented is the relationship between elements of dimension tables referenced by the fact table.
In a DW implemented in a relational database, fact tables are related to dimension tables. In the star schema type model, the dimensions are denormalized tables and each can store multiple levels of the same analysis. An example would be the eTmi dimension, which can store in a single table the analysis (dimension levels) aDy , hMtno and Year . In snowflake type model, the dimensions are normalized tables and each table is a analysis level. For the same example where the eTmi dimension was used, there would be a table (dimension) for the analysis Day , related to another table to hMtno , and this related to another table for Year . Both the types, or a hybrid of them, are used in DW implementations and shows that exists a diversity in their data structure that can make the identification of concepts stored a complex task to the BI analysts (IT analyst responsible for BI systems).
In typical BI systems scenarios, a knowledge representation would allow a business analyst or a BI analyst to know the measures available for analysis, for which analysis views are available (granularity / summarizability) and the relationships between these views, for example.
Consider a real scenario of a BI system about employees of a financial institution participating in a pension fund. Business analysts are provided with an OLAP tool for building analysis, reports and interactive dashboards. However, business analysts are highly dependent on IT specialists to build reports and perform analysis due to difficulty in knowing the information available and crossing possibilities between them. However, the IT department does not have sufficient availability to attend the demand and the available documentation does not follow the changes that occur in the technological environment. In this scenario, a description of the concepts and their relationships available for analysis in the BI environment provide the users with knowledge allowing them to make better decisions. Moreover, it can also help BI analysts to explain discrepancies between the data schema and the application layer and support data integration demands.
For instance, suppose that the business area needs to perform an analysis about the financial impact of employees retirement. The retirement value of an employee is based, among other variables, on the amount of his salary. With the available OLAP tool, a business analyst easily visualizes a metric with the employees salary. However, other information such as the grouping or filtering possibilities of such information, the possible dimensions of use or the granularity of information are not provided. Additionally, other information related to the chosen one would provide the analysts with more insights towards a better decision, such as the quantity of dependentes, gender or city of residence. An ontology could represent ySalr and auyQitn of dstepn as metrics associated to a temporal dimension and to other dimensions such as Grden and Rescndi ycti . Thus, this ontology can be used as an artifact for providing extra knowledge towards a better decision.
In this work, we propose OntoDW, an approach for automatically extracting an ontology from the Data Warehousing structural constructs (schema metadata) and contents (data) within a BI system. The elements of the generated ontology are obtained by specific mapping rules of our proposed method. The hypothesis for this proposal is that it is possible to generate ontologies from data warehouses through the use of specific mapping rules, and this ontology will reflect the knowledge about OLAP analysis task present in the data and metadata of the Data Warehouse.
The obtained ontology should include not only explicit knowledge about the data structures (such as translating from tables to classes, for example), but also about the semantics (such as class categorization). A domain ontology comprises the concepts present in the Data Warehouse, without specifying the possible operation possibilities to perform. For this reason, the proposed solution generates an ontology and includes classes relating to an OLAP task metamodel.
The generated ontology will be composed of concepts that reflect the multidimensional data schema (fact tables and dimension tables) and concepts associated with the analysis operations in BI systems (such as summarizability, the possible analyzes to perform). For this, the following input elements for the ontology generation process are used: the DW, an OLAP task metamodel, a domain metamodel and the set of mapping rules defined in this proposal, as in Figure 1.
The Data Warehouse metadata (logical schema of the database) is taken into account by the proposed mapping rules. Additionally, access to DW data is required in cases where the metadata does not provide enough information to identify concept instances.
The OLAP task metamodel presents predefined concepts and relationships
associated to information analysis in BI systems, such as Measure and Dimension
concepts. In this work, we adopted the OLAP task metamodel proposed by Prat et al.
[
The domain metamodel presents domain specific concepts on which the system is included and may be represented by a data dictionary, a terminology standard or a glossary, which are simple components that are traditionally found in organizational environments. The domain information will be used to name concepts according to business terms already established.
The current implementation of our proposal defined mapping rules (described below) to the following concepts of the metamodel: Fact, Dimension, DimensionLevel,
the classes present in the task metamodel, but are the main concepts for a rich and aligned ontology for the BI system.
This section describes the mapping rules defined from Data Warehouses elements (data
and metadata) to some ontologies concepts of the used OLAP task metamodel (Figure
2). The OntoDW ruleset differs from the rules defined by Prat et al. [
The OntoDW rules for concepts extraction do not contain rules defined by Prat et
al. However, for the generation of OWL ontology after concepts identification, some
these rules were used, with adjustments. The used rules define the concepts as subclasses
of the appropriate classes that represents in the OLAP task metamodel used. Ex.:
“Transformation T2.1: Each dimension of the multidimensional model is defined as a
subclass of the class Dimension in the OWL-DL ontology” [
It is assumed that there is a Fact (or fact table in the DW schema) for each table that has at least one column as a foreign key, but that is not referenced by any foreign key of another table DW schema. This rule is justified by the own definition of star schema. It is assumed that F is a Factless Fact table if, additionally, there are no numeric type columns outside the primary key.
Rule R1: For each table T1, T1 is mapped to a fact F1 if there is not a table T2 (T2 ≠ T1) that references T1 via foreign key and T1 references a table T3 via foreign key. Let PK={C1,...,Ci} be the F1 columns subset that composes its primary key and NK={Ci+1,...,Cn} the F1 columns subset that not composes its primary key. For each fact F1, F1 is classified as Factless Fact if there is no X column (X ∈ NK) of numeric type as foreign key.
It is assumed that there is a dimension (or dimension table in the DW schema) for each table that is referenced by a foreign key from another table of the DW schema. This rule is justified by the own definition of star schema.
Rule R2: For each table T1, T1 is mapped to a dimension D1 if there is a table T2 (T2≠ T1) that references T1 via foreign key.
A dimension level is a subdivision of a dimension and represents an analysis view. A dimension can have more than one level, if the table from which it was mapped is denormalized. It is assumed that there is a dimension level for each set of columns of a dimension where no column is a foreign key and, for each value of a column of this set, the same corresponding value occurs in another column of this same set. The columns which are foreign keys are not considered because they represent the relationship to another level. The restriction on the columns values is justified by the fact that a record in a dimension level must be unique, like a dimension table.
Rule R3: For each dimension D1, let ND={C1,...,Cj} be a D1 columns subset. ND is mapped to a dimension level N1 if, ∀ a, Ca (Ca ∈ ND) is not a foreign key and, ∀ b, a value in Ca always has the same corresponding value in Cb (Cb ∈ ND).
There are two scenarios for mapping measures. In scenario 1, it is assumed that in a F1 fact, a numeric type column that is not part of the primary key and that is not a foreign key is a measure. The column must be numeric to enable aggregation operations on its values, such as the sum or average, for example. In scenario 2, if F1 is a Factless Fact, there is a measure M1 with no corresponding column in the DW table.
Rule R4: For each fact F1, let NK={Ci,...,Cn} be the F1 columns subset that does not compose its primary key. For each Ca (Ca ∈ NK), Ca is mapped to a measure M1 if it is a numeric type column and it is not a foreign key.
Rule R5: For each fact F1 classified as Factless Fact, F1 is mapped to a measure M1.
The summarizability along dimensions of a M1 measure represents all M1 relationships with the mapped dimensions through the fact tables that contain M1.
Rule R6: Be F1={Fi,...,Fj} the set of all DW fact tables that contain the M1 measure and D={Dm,...,Dn} the set of all DW dimensions. For each measure M1, M1 is mapped to a summarizability along dimension instance AD1 of M1, related to M1 and to dimension D1 (D1 ∈ D), if, ∀ a, D1 is related to Fa (Fa ∈ F1) via foreign key.
This Section illustrates the application or our proposed solution in the scenario of a pension fund of one of the largest financial institutions in Brazil. The chosen domain comprised employees information, on which we focus in this paper since it is easier to understand for non-experts; moreover, the chosen DW scheme applied a variety of multidimensional modeling techniques and the data stored in the DW is known to be consistent; finally, it is an strategic subject for both the Business Intelligence area of the institution and the business area responsible of this data.
The DW stores data loaded since 1997, totaling tens of millions of records in the tables. This data relates to 275,000 employees and former employees of the financial institution, in various analysis views, composing a rich environment of information that can be used for management actions and analysis on the actuarial calculation and monitoring of the staff. Every month, the registration information of the employees is loaded in the DW and integrated with information about the pension funds in which each employee participates. This DW scheme contains 62 tables (50 dimension tables, 11 fact tables and 1 control table). The DW is implemented on DBMS Oracle 11g, the same platform used for the development of OntoDW.
Figure 3 shows a cutout of the physical model of the data schema. There are 2 fact / aggregation tables (CAUINTF_ and CUIN_F3AGR ) and some of the existing dimension tables that relate to them, storing analysis views of the measures / metrics available. A control table that keeps track of which data is already loaded into the DW is also present (RE_TUGLMSCA ).
The fact / aggregation tables of Figure 3 present only a subset of their columns, since the total number of columns in the tables is very high (50 columns for the CAUINTF_ table). This is due to the high number of dimensions and metrics existing for this subject in the DW and also to the fact that it is an old data structure, which has undergone several evolutionary and corrective maintenance changes. This makes it more difficult for analysts to perform analysis of the model for identifying information in the DW. For example, the Syalr measure is stored by the VALT_SIRCP column. However, it is present in these two tables with a confusing identification and different possible analysis views from the data model.
To illustrate the results of our application example, we chose an excerpt of the ontology generated by OntoDW on top of the DW schema of Figure 3, related to aggregability along dimensions. It contains conceptual elements representing possible analysis from business analysts on top of the data in the Data Warehouse and to present different extracted concepts. The validation criteria for the proposed approach is that the generated ontology reflects the knowledge present in the data and metadata of the DW.
Figure 4 is a screenshot from Protégé (www.protege.stanford.edu/) showing instances of the SummarizabilityAlongDimension class found by OntoDW. The screen display is divided into three parts: the leftmost subdivision shows the classes defined in the ontology, the central subdivision shows instances of the selected and rightmost subdivision shows the metric that has its summarizability represented (highlighted in red) and the dimensions by which it is possible to analyze the metric.
The VeaA-mLSROPITC measure highlighted in Figure 4 enables the analysys of the employees salary considered by the pension fund to actuarial calcularion, benefits payment and revenue collection. The name of the metric was defined from the name of the column in the fact table that stores its data (VALT_SIRCP ). Using the separator (“_“) as a parameter, the terms have been extracted and were consulted in the glossary of organizational business terms; if found, the term is replaced by the original term in the glossary. The terms are then concatenated with the other separator (“-“), also defined as a parameter. An identifier prefix of the class that defines the instance was also defined (“eam ”), to help BI analists.
The named instance VsA-adLSROCPIT (where “sad ” stands for “summarizability along dimension”) relates to VeaA-mLSROI ARPTICO measure, connecting it to all its possible dimensions for analysis. Figure 5 is an excerpt of the resulting ontology and presents the same instances of Figure 4, but represented graphically by OntoGraf plugin of Protégé.
Considering all the concepts that were automatically explicited by OntoDW in the generated ontology, the application example is considered successfully performed. From the data and metadata of the DW, the instances of classes Fact, Dimension, DimensionLevel, Measure and SummarizabilityAlongDimension, and the relationships between them, were mapped. With the graphically represented ontology, the identification of the instances and the relationship among them can be made more quickly and intuitively. Returning to the example analysis described above, the BI analyst can easily deduce from Figure 5 the metric analysis possibilities associated with salary amount in relation to the dimensions found in DW.
We carried out a bibliographic search looking for works related to the problem in
question, and found some studies that have explored the generation of ontologies from
data structures. Although there are the different approaches in the literature for
automatic generation of ontologies [
Prat et al. [
Gil et al. [
[
[
The analysis of the aforementioned studies showed the absence of solutions for generating ontologies automatically from DWs. In particular, the use of another source of data (other than the multidimensional data structure) for generating an ontology would require the existence of up-to-date documentation in synchrony with the concepts implemented to throughout the system life cycle, which is unrealistic in practice. It is very difficult to keep another source of information available for use in the generation of an ontology, that is always current. On the other hand, in a BI system based on a DW, the multidimensional data structure is part of the implemented system.
This article proposed a set of mapping rules for automatically generating an ontology for BI systems from Data Warehouses, contributing to solve the lack of a formal knowledge representation that explicits and semantically describe the data and metadata of BI systems stored in the DW.
Advantageously, the use of DW elements to generate an ontology provides a source of information shared with the BI system, ensuring alignment between the concepts implemented in the system and domain concepts that the extracted ontology proposes to represent. In addition, this source of information allows the inference of BI domain concepts, such as summarizabilities, task more difficult to perform using a operational database. The characteristics of stored data, such as volume and sparsity, may also be used to infer the ontology elements. For example, an aggregate table of employees by age group tends to be less volumous than a fact table in the level of employee or age.
The ontology generation from DWs presents as challenges some issues that are inherent characteristics commonly found in BI systems, such as the large volume of data stored in data structures, that make it difficult to manipulate the data stored in the repository and the structure that contain them, and the denormalisation of data models that make it difficult to identify the relationship between the classes and their properties.
This generation of ontology should be automatic because of problems relating to their manual construction. This facilitates keeping the consistency of the ontology with the DW elements along the application life cycle. In cases of changes due to evolutionary system maintenance, when new facts and dimensions are freqeuntly included, the proposed approach may be reexecuted so as to update the existing conceptualization.
The proposed mapping rules extends the state of the art in the generation of ontologies from BI environments. These rules deal with more specific aspects of multidimensional modeling and takes both the data and metadata present in the DW data structures into account.
The contributions of this proposal are the creation and improvement of mapping rules from data warehouses elements to ontology concepts, which address specific aspects of multidimensional modeling and OLAP applications to use the data and metadata in the DW, and implementation of a tool for automatic generation of ontologies, using mapping rules, the system domain information and an OLAP task metamodel, besides the Data Warehouse.
As a future task, a survey with BI professionals will be conducted to evaluate the extraction of concepts rules using their theoretical knowledge and experience. Users of the BI application scenario will also validate the premise that a representation of knowledge can support the analysis of data in the DW. The survey form will present issues containing parts of the generated ontology and the participant shall provide its opinion on the usefulness of representations submitted. .
The authors would like to thank FAPERJ (E-26/203.446/2015 - BBP), FAPES, CAPES and CNPq for partially funding their research projects.