Daanse Tutorial - Virtual Cube Calculated Member

Cube with virtual cube with dimension references from other cubes and with calculatedMember A virtual cube that combines measures and dimensions from multiple physical cubes into a unified analytical view. Virtual cubes enable cross-cube analysis by creating a logical integration layer over existing physical cubes, allowing users to analyze related metrics from different business processes in a single query Catalog have two physical cubes Cube1 and Cube2 and virtual cube Cube1Cube2. CalculatedMember from virtual cube uses MeasureCube1 from Cube1 and MeasureCube2 from Cube2 in formula.

Database Schema

The cube defined in this example is based on a table that stores all the data. The table is named Fact uses for Cube1 and contains two columns: KEY and VALUE. The KEY column serves as a discriminator, while the VALUE column contains the measurements to be aggregated.

<roma:DatabaseSchema   id="_databaseSchema_calculatedmember">
  <tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
    <columns xsi:type="roma:PhysicalColumn" id="_column_fact_key" name="KEY"/>
    <columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
  </tables>
</roma:DatabaseSchema>

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

Query

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.

<roma:TableQuery  id="_table_factQuery" table="_table_fact"/>

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

MeasureCube1

Measure uses C1_Fact table VALUE column with sum aggregation in Cube1.

<roma:SumMeasure  id="_measurecube1" name="MeasureCube1" column="_column_fact_value"/>

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

Sum_Cub

CalculatedMember uses MeasureCube1 from Cube1 and MeasureCube2 from Cube2 in formula.

<roma:CalculatedMember  id="_cm_Sum_Cub" name="Sum_Cub" formula="[Measures].[MeasureCube1] + [Measures].[MeasureCube2]" hierarchy="roma:ExplicitHierarchy _hierarchy_HierarchyWithoutHasAll"/>

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

MeasureCube2

Measure uses Fact table VALUE column with sum aggregation in Cube2.

<roma:SumMeasure  id="_measurecube2" name="MeasureCube2" column="_column_fact_value"/>

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

Cube1

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

<roma:PhysicalCube   id="_cube1" name="Cube1" query="_table_factQuery">
  <dimensionConnectors foreignKey="roma:PhysicalColumn _column_fact_key" dimension="roma:StandardDimension _dimension1" overrideDimensionName="Cube1Dimension1" id="_dc_cube1Dimension1"/>
  <measureGroups>
    <measures xsi:type="roma:SumMeasure" id="_measurecube1" name="MeasureCube1" column="_column_fact_value"/>
  </measureGroups>
</roma:PhysicalCube>

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

Cube1

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

<roma:PhysicalCube   id="_cube1" name="Cube1" query="_table_factQuery">
  <dimensionConnectors foreignKey="roma:PhysicalColumn _column_fact_key" dimension="roma:StandardDimension _dimension1" overrideDimensionName="Cube1Dimension1" id="_dc_cube1Dimension1"/>
  <measureGroups>
    <measures xsi:type="roma:SumMeasure" id="_measurecube1" name="MeasureCube1" column="_column_fact_value"/>
  </measureGroups>
</roma:PhysicalCube>

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

VirtualCubeMeasureOnly

Virtual cube uses mesures from Cube1 and Cube2. Virtual cube has references for them. Also virtual cube has references to dimensions from Cube1 and Cube2

<roma:VirtualCube  id="_cube1cube2" name="Cube1Cube2" dimensionConnectors="_dc_cube1Dimension1 _dc_cube2Dimension1" referencedMeasures="roma:SumMeasure _measurecube1 roma:SumMeasure _measurecube2">
  <calculatedMembers id="_cm_Sum_Cub" name="Sum_Cub" formula="[Measures].[MeasureCube1] + [Measures].[MeasureCube2]" hierarchy="roma:ExplicitHierarchy _hierarchy_HierarchyWithoutHasAll"/>
</roma:VirtualCube>

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="Calculated members in virtual cubes" name="Daanse Tutorial - Virtual Cube Calculated Member" cubes="_cube1 _cube2 _cube1cube2" dbschemas="_databaseSchema_calculatedmember"/>
  <roma:DatabaseSchema id="_databaseSchema_calculatedmember">
    <tables xsi:type="roma:PhysicalTable" id="_table_fact" name="Fact">
      <columns xsi:type="roma:PhysicalColumn" id="_column_fact_key" name="KEY"/>
      <columns xsi:type="roma:PhysicalColumn" id="_column_fact_value" name="VALUE" type="Integer"/>
    </tables>
  </roma:DatabaseSchema>
  <roma:TableQuery id="_table_factQuery" table="_table_fact"/>
  <roma:Level id="_level" name="Level" column="_column_fact_key"/>
  <roma:ExplicitHierarchy id="_hierarchy_HierarchyWithoutHasAll" name="HierarchyWithoutHasAll" hasAll="false" primaryKey="_column_fact_key" query="_table_factQuery" levels="_level"/>
  <roma:StandardDimension id="_dimension1" name="Dimension1" hierarchies="_hierarchy_HierarchyWithoutHasAll"/>
  <roma:PhysicalCube id="_cube1" name="Cube1" query="_table_factQuery">
    <dimensionConnectors foreignKey="_column_fact_key" dimension="_dimension1" overrideDimensionName="Cube1Dimension1" id="_dc_cube1Dimension1"/>
    <measureGroups>
      <measures xsi:type="roma:SumMeasure" id="_measurecube1" name="MeasureCube1" column="_column_fact_value"/>
    </measureGroups>
  </roma:PhysicalCube>
  <roma:PhysicalCube id="_cube2" name="Cube2" query="_table_factQuery">
    <dimensionConnectors foreignKey="_column_fact_key" dimension="_dimension1" overrideDimensionName="Cube2Dimension1" id="_dc_cube2Dimension1"/>
    <measureGroups>
      <measures xsi:type="roma:SumMeasure" id="_measurecube2" name="MeasureCube2" column="_column_fact_value"/>
    </measureGroups>
  </roma:PhysicalCube>
  <roma:VirtualCube id="_cube1cube2" name="Cube1Cube2" dimensionConnectors="_dc_cube1Dimension1 _dc_cube2Dimension1" referencedMeasures="_measurecube1 _measurecube2">
    <calculatedMembers id="_cm_Sum_Cub" name="Sum_Cub" formula="[Measures].[MeasureCube1] + [Measures].[MeasureCube2]" hierarchy="_hierarchy_HierarchyWithoutHasAll"/>
  </roma:VirtualCube>
</xmi:XMI>

Tutorial Zip

This files contaisn the data-tables as csv and the mapping as xmi file.

Download Zip File