Daanse Tutorial - Cube Hierarchy Query Join Multi
If the database structure follows the Third Normal Form (3NF), hierarchies in a cube are not stored in a single table but are distributed across multiple tables.
For example, consider a Geographical hierarchy with the levels Town and Country and Continent. If each entity is stored in a separate table, with a primary-foreign key relationship linking them, a query must be defined that incorporates both tables and specifies how the levels are joined. In This case, the query must include a join between the Town and Country tables, as well as a join between the Country Join and Continent tables. The following example demonstrates how to define such a query.
Database Schema
The cube defined in this example is based on three tables: Fact, Town, and Country.
- The
Fact
table contains measures and a reference to theTown
table. - The
Fact
table is linked to theTown
table through theTOWN_ID
column, which corresponds to theID
column in theTown
table. - The
Town
table includes a column that references the primary key of theCountry
table. - The
Country
table consists of two columns:ID
(primary key) and Name as well as referenct to theContinent
. - The
Continent
table consists of two columns:ID
(primary key) and Name.
This structure ensures that the hierarchy is properly normalized, following the Third Normal Form (3NF).
<roma:DatabaseSchema id="_databaseSchema_main">
<tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
<columns xsi:type="roma:PhysicalColumn" id="_column_fact_townId" name="TOWN_ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_town" name="Town">
<columns xsi:type="roma:PhysicalColumn" id="_column_town_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_town_name" name="NAME"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_town_countryId" name="COUNTRY_ID" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_country" name="Country">
<columns xsi:type="roma:PhysicalColumn" id="_column_country_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_country_name" name="NAME"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_country_continentId" name="CONTINENT_ID" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_continent" name="Continent">
<columns xsi:type="roma:PhysicalColumn" id="_column_continent_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_continent_name" name="NAME"/>
</tables>
</roma:DatabaseSchema>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query - Level Town
The TableQuery for the Town level directly references the physical Town table.
<roma:TableQuery id="_query_town" table="_table_town"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query - Level Country
The TableQuery for the Country level directly references the physical Country table.
<roma:TableQuery id="_query_country" table="_table_country"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query - Join Town to Country
The JoinQuery specifies which TableQueries should be joined. It also defines the columns in each table that are used for the join:
- In the lower-level table (
Town
), the join uses the foreign key. - In the upper-level table (
Country
), the join uses the primary key.
<roma:JoinQuery id="_query_townToCountry">
<left key="_column_town_countryId" query="_query_town"/>
<right key="_column_country_id" query="_query_countryToContinent"/>
</roma:JoinQuery>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query - Level Country
The TableQuery for the Continent level directly references the physical Continent table.
<roma:TableQuery id="_query_continent" table="_table_continent"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query - Join Town-Country-Join to Continent
The JoinQuery specifies which Queries should be joined. It also defines the columns in each table that are used for the join:
In this vase we join a TableQuery with a JoinQuery.
- In the lower-level it is the JoinQuery (Town-Country), the join uses the foreign key.
- In the upper-level it is the TableQuery (Continent), the join uses the primary key.
<roma:JoinQuery id="_query_townToCountry">
<left key="_column_town_countryId" query="_query_town"/>
<right key="_column_country_id" query="_query_countryToContinent"/>
</roma:JoinQuery>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Query Fact
The TableQuery for the Level, as it directly references the physical table Fact
.
<roma:TableQuery id="_query_fact" table="_table_fact"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Level - Town
The Town
level uses the column attribute to specify the primary key column. Additionally, it defines the nameColumn attribute to specify the column that contains the name of the level.
<roma:Level id="_level_town" name="Town" column="_column_town_id" nameColumn="_column_town_name"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Level - Country
The Country
level follows the same pattern as the Town
level.
<roma:Level id="_level_country" name="County" column="_column_country_id" nameColumn="_column_country_name"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Level - Continent
The Continent
level follows the same pattern as the Town
and Country
level.
<roma:Level id="_level_continent" name="Continent" column="_column_continent_id" nameColumn="_column_continent_name"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Hierarchy
This hierarchy consists of three levels: Town
, Country
and Continent
.
- The primaryKey attribute specifies the column that contains the primary key of the hierarchy.
- The query attribute references the Join-query used to retrieve the data for the hierarchy.
The order of the Levels in the hierarchy is important, as it determines the drill-down path for the hierarchy.
<roma:ExplicitHierarchy id="_hierarchy_townHierarchy" name="TownHierarchy" primaryKey="_column_town_id" query="_query_townToCountry" levels="_level_continent _level_country _level_town"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Dimension
The Dimension has only one hierarchy.
<roma:StandardDimension id="_dimension_continentCountryTown" name="Continent - Country - Town" hierarchies="roma:ExplicitHierarchy _hierarchy_townHierarchy"/>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Cube and DimensionConnector and Measure
The cube contains only one Measure in a unnamed MeasureGroup and references to the Dimension.
To connect the dimension to the cube, a DimensionConnector is used. The dimension has set the attribute foreignKey
to define the column that contains the foreign key of the dimension in the fact table.
<roma:PhysicalCube id="_cube_queryLinkedTables" name="Cube Query linked Tables" query="_query_fact">
<dimensionConnectors foreignKey="roma:PhysicalColumn _column_fact_townId" dimension="roma:StandardDimension _dimension_continentCountryTown" id="_dimensionConnector_continentCountryTown"/>
<measureGroups>
<measures xsi:type="roma:SumMeasure" id="_measure_theMeasure" name="theMeasure" column="_column_fact_value"/>
</measureGroups>
</roma:PhysicalCube>
Note: This is only a symbolic example. For the exact definition, see the Definition section.
Definition
This files represent the complete definition of the catalog.
<?xml version="1.0" encoding="UTF-8"?>
<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:roma="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping">
<roma:Catalog description="Multi-level hierarchy with joins" name="Daanse Tutorial - Cube Hierarchy Query Join Multi" cubes="_cube_queryLinkedTables" dbschemas="_databaseSchema_main"/>
<roma:DatabaseSchema id="_databaseSchema_main">
<tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
<columns xsi:type="roma:PhysicalColumn" id="_column_fact_townId" name="TOWN_ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_town" name="Town">
<columns xsi:type="roma:PhysicalColumn" id="_column_town_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_town_name" name="NAME"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_town_countryId" name="COUNTRY_ID" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_country" name="Country">
<columns xsi:type="roma:PhysicalColumn" id="_column_country_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_country_name" name="NAME"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_country_continentId" name="CONTINENT_ID" type="Integer"/>
</tables>
<tables xsi:type="roma:PhysicalTable" id="_table_continent" name="Continent">
<columns xsi:type="roma:PhysicalColumn" id="_column_continent_id" name="ID" type="Integer"/>
<columns xsi:type="roma:PhysicalColumn" id="_column_continent_name" name="NAME"/>
</tables>
</roma:DatabaseSchema>
<roma:TableQuery id="_query_continent" table="_table_continent"/>
<roma:TableQuery id="_query_country" table="_table_country"/>
<roma:TableQuery id="_query_fact" table="_table_fact"/>
<roma:TableQuery id="_query_town" table="_table_town"/>
<roma:JoinQuery id="_query_countryToContinent">
<left key="_column_country_continentId" query="_query_country"/>
<right key="_column_continent_id" query="_query_continent"/>
</roma:JoinQuery>
<roma:JoinQuery id="_query_townToCountry">
<left key="_column_town_countryId" query="_query_town"/>
<right key="_column_country_id" query="_query_countryToContinent"/>
</roma:JoinQuery>
<roma:Level id="_level_continent" name="Continent" column="_column_continent_id" nameColumn="_column_continent_name"/>
<roma:Level id="_level_country" name="County" column="_column_country_id" nameColumn="_column_country_name"/>
<roma:Level id="_level_town" name="Town" column="_column_town_id" nameColumn="_column_town_name"/>
<roma:ExplicitHierarchy id="_hierarchy_townHierarchy" name="TownHierarchy" primaryKey="_column_town_id" query="_query_townToCountry" levels="_level_continent _level_country _level_town"/>
<roma:StandardDimension id="_dimension_continentCountryTown" name="Continent - Country - Town" hierarchies="_hierarchy_townHierarchy"/>
<roma:PhysicalCube id="_cube_queryLinkedTables" name="Cube Query linked Tables" query="_query_fact">
<dimensionConnectors foreignKey="_column_fact_townId" dimension="_dimension_continentCountryTown" id="_dimensionConnector_continentCountryTown"/>
<measureGroups>
<measures xsi:type="roma:SumMeasure" id="_measure_theMeasure" name="theMeasure" column="_column_fact_value"/>
</measureGroups>
</roma:PhysicalCube>
</xmi:XMI>
Tutorial Zip
This files contaisn the data-tables as csv and the mapping as xmi file.