Eclipse Daanse

Appearance

Daanse Tutorial - Level If Blank Name ​

A basic OLAP schema with a level with property Level has attribute HideMemberIf.IF_BLANK_NAME Catalog has two cubes with one level with HideMemberIf atribut and with multiple levels

Database Schema ​

The cube defined in this example is based on a 3 tables that stores all the data.

  • The phisical table is named Fact uses for Cube1 and contains two columns: DIM_KEY and VALUE. The DIM_KEY column serves as a discriminator, while the VALUE column contains the measurements to be aggregated.
  • The phisical table is named Level_1 uses for Level1 and contains 2 columns: KEY, NAME .
  • The phisical table is named Level_2_NULL uses for Level2 and contains 3 columns: KEY, NAME, L1_KEY.
xml
<roma:DatabaseSchema   id="_databaseSchema_ifblankname">
  <tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
    <columns xsi:type="roma:PhysicalColumn" id="_column_fact_dim_key" name="DIM_KEY" type="Integer"/>
    <columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
  </tables>
  <tables xsi:type="roma:PhysicalTable" id="_level_2_null" name="Level_2_NULL">
    <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_key" name="KEY" type="Integer"/>
    <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_name" name="NAME"/>
    <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_l1_key" name="L1_KEY" type="Integer"/>
  </tables>
  <tables xsi:type="roma:PhysicalTable" id="_level_1" name="Level_1">
    <columns xsi:type="roma:PhysicalColumn" id="_level_1_key" name="KEY" type="Integer"/>
    <columns xsi:type="roma:PhysicalColumn" id="_level_1_name" name="NAME"/>
  </tables>
</roma:DatabaseSchema>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Query Fact ​

The bridge between the cube and the database is the query element. In this case, it is a TableQuery, as it directly references the physical table Fact.

xml
<roma:TableQuery  id="_queryFact" table="_table_fact"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Query Level1 ​

The bridge between the cube and the database is the query element. In this case, it is a TableQuery, as it directly references the physical table Level_1.

xml
<roma:TableQuery  id="_queryLevel1" table="_level_1"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Query Level2 ​

The bridge between the cube and the database is the query element. In this case, it is a TableQuery, as it directly references the physical table Level_2_NULL.

xml
<roma:TableQuery  id="_queryLevel2Null" table="_level_2_null"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Query Join ​

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 (Level_2_NULL), the join uses the foreign key L1_KEY.
  • In the upper-level table (Level_1), the join uses the primary key KEY.
xml
<roma:JoinQuery  id="_queryJoin">
  <left key="_level_2_null_l1_key" query="_queryLevel2Null"/>
  <right key="_level_1_key" query="_queryLevel1"/>
</roma:JoinQuery>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

DimensionMembersHiddenIfBlankName ​

The Dimension has only one hierarchy.

xml
<roma:StandardDimension  id="_dimensionmembershiddenifblankname" name="DimensionMembersHiddenIfBlankName" hierarchies="roma:ExplicitHierarchy _hierarchy1_1"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Hierarchy1 ​

This hierarchy consists two levels Level1 and Level2.

  • The primaryKey attribute specifies the column that contains the primary key of the hierarchy.
  • The query attribute references the query used to retrieve the data for the hierarchy.
xml
<roma:ExplicitHierarchy  id="_hierarchy1_1" name="Hierarchy1" primaryKey="_level_2_null_key" query="_queryJoin" levels="_h1Level1 _h1Level2"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Level1 ​

The Level uses the column attribute to specify the primary key KEY from Level_1. Additionally, it defines the nameColumn NAME from Level_1 attribute to specify the column that contains the name of the level.

xml
<roma:Level  id="_h1Level1" name="Level1" column="_level_1_key" nameColumn="_level_1_name"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Level2 ​

The Level uses the column attribute to specify the primary key KEY from Level_2_NULL. Additionally, it defines the nameColumn NAME from Level_2_NULL attribute to specify the column that contains the name of the level. Level has attribute HideMemberIf.IF_BLANK_NAME Hide members that have blank or null names. Useful for filtering out incomplete data where member names are missing from the source system.

xml
<roma:Level  id="_h1Level2" name="Level2" column="_level_2_null_key" hideMemberIf="IfBlankName" nameColumn="_level_2_null_name"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Measure1 ​

    Measure1 use Fact table VALUE column with sum aggregation in Cube.
xml
<roma:SumMeasure  id="_measure1" name="Measure1" column="_column_fact_value"/>

Note: This is only a symbolic example. For the exact definition, see the Definition section.

Cube ​

In this example uses cube with fact table Fact as data.

xml
<roma:PhysicalCube   id="_hiddenmembersifblankname" name="HiddenMembersIfBlankName" query="_queryFact">
  <dimensionConnectors foreignKey="roma:PhysicalColumn _column_fact_dim_key" dimension="roma:StandardDimension _dimensionmembershiddenifblankname" overrideDimensionName="DimensionMembersHiddenIfBlankName" id="_dc_dimensionMembersHiddenIfBlankName"/>
  <measureGroups>
    <measures xsi:type="roma:SumMeasure" id="_measure1" name="Measure1" 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
<?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="Level handling blank names" name="Daanse Tutorial - Level If Blank Name" cubes="_hiddenmembersifblankname" dbschemas="_databaseSchema_ifblankname"/>
  <roma:DatabaseSchema id="_databaseSchema_ifblankname">
    <tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
      <columns xsi:type="roma:PhysicalColumn" id="_column_fact_dim_key" name="DIM_KEY" type="Integer"/>
      <columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
    </tables>
    <tables xsi:type="roma:PhysicalTable" id="_level_2_null" name="Level_2_NULL">
      <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_key" name="KEY" type="Integer"/>
      <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_name" name="NAME"/>
      <columns xsi:type="roma:PhysicalColumn" id="_level_2_null_l1_key" name="L1_KEY" type="Integer"/>
    </tables>
    <tables xsi:type="roma:PhysicalTable" id="_level_1" name="Level_1">
      <columns xsi:type="roma:PhysicalColumn" id="_level_1_key" name="KEY" type="Integer"/>
      <columns xsi:type="roma:PhysicalColumn" id="_level_1_name" name="NAME"/>
    </tables>
  </roma:DatabaseSchema>
  <roma:TableQuery id="_queryFact" table="_table_fact"/>
  <roma:TableQuery id="_queryLevel1" table="_level_1"/>
  <roma:TableQuery id="_queryLevel2Null" table="_level_2_null"/>
  <roma:JoinQuery id="_queryJoin">
    <left key="_level_2_null_l1_key" query="_queryLevel2Null"/>
    <right key="_level_1_key" query="_queryLevel1"/>
  </roma:JoinQuery>
  <roma:Level id="_h1Level1" name="Level1" column="_level_1_key" nameColumn="_level_1_name"/>
  <roma:Level id="_h1Level2" name="Level2" column="_level_2_null_key" hideMemberIf="IfBlankName" nameColumn="_level_2_null_name"/>
  <roma:ExplicitHierarchy id="_hierarchy1_1" name="Hierarchy1" primaryKey="_level_2_null_key" query="_queryJoin" levels="_h1Level1 _h1Level2"/>
  <roma:StandardDimension id="_dimensionmembershiddenifblankname" name="DimensionMembersHiddenIfBlankName" hierarchies="_hierarchy1_1"/>
  <roma:PhysicalCube id="_hiddenmembersifblankname" name="HiddenMembersIfBlankName" query="_queryFact">
    <dimensionConnectors foreignKey="_column_fact_dim_key" dimension="_dimensionmembershiddenifblankname" overrideDimensionName="DimensionMembersHiddenIfBlankName" id="_dc_dimensionMembersHiddenIfBlankName"/>
    <measureGroups>
      <measures xsi:type="roma:SumMeasure" id="_measure1" name="Measure1" 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.

Download Zip File

Released under the Eclipse Public License 2.0

Copyright © 2025 - Eclipse-Daanse