There are number of ways to improve performance of reports,
and one of the mechanism is using aggregate tables. Aggregate tables
store pre-calculated measures that have been aggregated over a set of
dimensional attributes.
Generally aggregate tables are used along with level based hierarchy to set aggregation content for the fact table to corresponding levels in dimension hierarchies.
When Parent-Child hierarchies are used, a priority group settings are used to select data from aggregate table or detail table based on columns selected from dimension tables.
To set up and use aggregate tables with Parent-Child hierarchy, following below steps:
1. Create aggregate table
2. Setup Parent-Child hierarchy on a dimension
3. Setup Physical and Logical Data Models
4. Set Priority group
5. Test
1. Create aggregate table
Create fact aggregate table on dimension SalesMan at one level up (ie at salesman 2, 3 4 and 10) using ETL or any other preferred method. You can create aggregate table at levels depending on customer’s requirement.
Aggregated fact table on dimension SalesMan at one level up as shown below.
2. Setup Parent child hierarchy on a dimension
Followed steps from following link to setup Parent-Child hierarchy on SalesMan Dimension as shown below.
3. Setup data model in Physical and BMM Layer
Setup physical data model as shown below with following joins.
Dim_SalesMan_Hierarchy.MEMBER_KEY = SALES_FACT_AGG.SALES_PERSON_KEY
SALESMAN.KEY = Dim_SalesMan_Hierarchy.ANCESTOR_KEY
Dim_SalesMan_Hierarchy.MEMBER_KEY = SALES_FACT.SALES_PERSON_KEY
PERIOD.KEY = SALES_FACT.PERIOD_KEY
Next in BMM layer, create a new fact table (SALES_FACT) with logical table source SALES_FACT and SALES_FACT_AGG
BMM Data Model
4. Setup Priority Group
Priority group numbers determine which logical table source should be used for queries when there are more than one logical table source that can satisfy the requested set of columns.
To assign priority group numbers, rank logical table sources in numeric order with 0 being the highest-priority source. In this case we setup priority group = 0 on Aggregate table because first we want to send queries to Aggregate table (if satisfied for requested set of columns)
Priority group =1 is set on Detail Sales Fact (SALES_FACT) Table.
5. Test
If we add salesman hierarchy and sales columns, the query returns data from Aggregate table
nqquery.log
If we add year column along with salesman hierarchy and sales columns, query returns data from Detail Fact table.
nqquery.log
Generally aggregate tables are used along with level based hierarchy to set aggregation content for the fact table to corresponding levels in dimension hierarchies.
When Parent-Child hierarchies are used, a priority group settings are used to select data from aggregate table or detail table based on columns selected from dimension tables.
To set up and use aggregate tables with Parent-Child hierarchy, following below steps:
1. Create aggregate table
2. Setup Parent-Child hierarchy on a dimension
3. Setup Physical and Logical Data Models
4. Set Priority group
5. Test
1. Create aggregate table
Create fact aggregate table on dimension SalesMan at one level up (ie at salesman 2, 3 4 and 10) using ETL or any other preferred method. You can create aggregate table at levels depending on customer’s requirement.
Aggregated fact table on dimension SalesMan at one level up as shown below.2. Setup Parent child hierarchy on a dimension
Followed steps from following link to setup Parent-Child hierarchy on SalesMan Dimension as shown below.
3. Setup data model in Physical and BMM Layer
Setup physical data model as shown below with following joins.
Dim_SalesMan_Hierarchy.MEMBER_KEY = SALES_FACT_AGG.SALES_PERSON_KEY
SALESMAN.KEY = Dim_SalesMan_Hierarchy.ANCESTOR_KEY
Dim_SalesMan_Hierarchy.MEMBER_KEY = SALES_FACT.SALES_PERSON_KEY
PERIOD.KEY = SALES_FACT.PERIOD_KEY
Next in BMM layer, create a new fact table (SALES_FACT) with logical table source SALES_FACT and SALES_FACT_AGGBMM Data Model4. Setup Priority Group
Priority group numbers determine which logical table source should be used for queries when there are more than one logical table source that can satisfy the requested set of columns.
To assign priority group numbers, rank logical table sources in numeric order with 0 being the highest-priority source. In this case we setup priority group = 0 on Aggregate table because first we want to send queries to Aggregate table (if satisfied for requested set of columns)
Priority group =1 is set on Detail Sales Fact (SALES_FACT) Table.5. Test
If we add salesman hierarchy and sales columns, the query returns data from Aggregate table
nqquery.logIf we add year column along with salesman hierarchy and sales columns, query returns data from Detail Fact table.nqquery.log
No comments:
Post a Comment