Daanse Tutorial - Aggregation Agg Exclude
This tutorial discusses TableSource with AggregationExclude. AggregationExclude defines exclusion rules that prevent specific tables from being used as aggregation tables, even if they would otherwise match aggregation patterns or be considered suitable for aggregation optimization. AggregationExclude is essential for maintaining aggregation accuracy and system reliability by providing explicit control over which tables should be avoided during aggregation table discovery and selection.
Database Schema
The cube defined in this example is based on a single table that stores all the data. The table is named Fact and contains two columns: KEY and VALUE. The KEY column serves as a discriminator, while the VALUE column contains the measurements to be aggregated.
<relational:Schema xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmi:id="_schema">
<ownedElement xsi:type="relational:Table" xmi:id="_table_fact" name="Fact">
<feature xsi:type="relational:Column" xmi:id="_column_fact_key" name="KEY"/>
<feature xsi:type="relational:Column" xmi:id="_column_fact_value" name="VALUE"/>
</ownedElement>
<ownedElement xsi:type="relational:Table" xmi:id="_table_agg_01_fact" name="agg_01_Fact">
<feature xsi:type="relational:Column" xmi:id="_column_agg_01_fact_key" name="KEY"/>
<feature xsi:type="relational:Column" xmi:id="_column_agg_01_fact_key_1" name="KEY"/>
</ownedElement>
</relational:Schema>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. The query element is not visible to users accessing the cube through the XMLA API, such as Daanse Dashboard, Power BI, or Excel.
<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
<rolapsrc:TableSource xmi:id="_tablesource_fact" table="_table_fact">
<aggregationExcludes xmi:id="_aggregationexclude_agg_01_fact" name="agg_01_Fact"/>
</rolapsrc:TableSource>
<relational:Table xmi:id="_table_fact" name="Fact">
<feature xsi:type="relational:Column" xmi:id="_column_fact_key" name="KEY"/>
<feature xsi:type="relational:Column" xmi:id="_column_fact_value" name="VALUE"/>
</relational:Table>
</xmi:XMI>Note: This is only a symbolic example. For the exact definition, see the Definition section.
Definition
This file represents 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:relational="http://www.omg.org/spec/CWM/1.1/resource/relational" xmlns:rolapcat="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/catalog" xmlns:rolapcube="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube" xmlns:rolapmeas="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/olap/cube/measure" xmlns:rolapsrc="https://www.daanse.org/spec/org.eclipse.daanse.rolap.mapping/database/source">
<relational:SQLSimpleType xmi:id="_sqlsimpletype_character_varying" name="CHARACTER VARYING" structuralFeature="_column_fact_key _column_agg_01_fact_key _column_agg_01_fact_key_1" typeNumber="12"/>
<relational:SQLSimpleType xmi:id="_sqlsimpletype_integer" name="INTEGER" structuralFeature="_column_fact_value" typeNumber="4"/>
<rolapcat:Catalog xmi:id="_catalog_aggregation_agg_exclude" description="Aggregate exclusion patterns" name="Daanse Tutorial - Aggregation Agg Exclude" cubes="_physicalcube_cube" dbschemas="_schema"/>
<relational:Schema xmi:id="_schema">
<ownedElement xsi:type="relational:Table" xmi:id="_table_fact" name="Fact">
<feature xsi:type="relational:Column" xmi:id="_column_fact_key" name="KEY" type="_sqlsimpletype_character_varying"/>
<feature xsi:type="relational:Column" xmi:id="_column_fact_value" name="VALUE" type="_sqlsimpletype_integer"/>
</ownedElement>
<ownedElement xsi:type="relational:Table" xmi:id="_table_agg_01_fact" name="agg_01_Fact">
<feature xsi:type="relational:Column" xmi:id="_column_agg_01_fact_key_1" name="KEY" type="_sqlsimpletype_character_varying"/>
<feature xsi:type="relational:Column" xmi:id="_column_agg_01_fact_key" name="KEY" type="_sqlsimpletype_character_varying"/>
</ownedElement>
</relational:Schema>
<rolapsrc:TableSource xmi:id="_tablesource_fact" table="_table_fact">
<aggregationExcludes xmi:id="_aggregationexclude_agg_01_fact" name="agg_01_Fact"/>
</rolapsrc:TableSource>
<rolapcube:PhysicalCube xmi:id="_physicalcube_cube" name="Cube" source="_tablesource_fact">
<measureGroups xmi:id="_measuregroup">
<measures xsi:type="rolapmeas:SumMeasure" xmi:id="_summeasure_measure" name="Measure" column="_column_fact_value"/>
</measureGroups>
</rolapcube:PhysicalCube>
</xmi:XMI>Tutorial Zip
This file contains the data-tables as csv and the mapping as xmi file.