Hi All!
I've reached a point where my initial approach results in very bad performance on a large data volume. I believe it could be changed by changing the join condition and creating correct expressions in charts. I'm stuck here and would greatly appreciate your help in finding solution.
We have:
1. Fact table with > 6 dimensions and 1 metric. Dimensions DO NOT have all possible values (some of them are not populated and omitted).
2. Population table with 5 dimensions and 1 metric. All dimensions exist in Fact table and contain all range of possible values (nothing is missing).
Chart requirements for the Metric 'Rate':
1. One or two dimensions. For one dimensional chart a dimension is from Fact table which doesn't exist in Population. For 2-dimensional chart one dimension is from Fact table which doesn't exist in Population and another one is from Population table.
2. Expression:
2.1 sum up Fact's metric for Fact's Age Groups available for dimension(s).
2.2 sum up Population's metric for each Age Group. Other 4 Population's dimensions should include All possible values specified by a user.
2.3. Divide 2.1 by 2.2.
2.4 Sum up result from 2.3 for each value in dimension(s).
Implementation:
Option 1.
- Tables are joined by Age Group field.
- Rate for one dimension:
=sum(aggr(sum({<FACT_1.Year = P(POPULATION_1.Year), FACT_1.Gender = P(POPULATION_1.Gender), FACT_1.Race = P(POPULATION_1.Race), FACT_1.Area = P(POPULATION_1.Area)>}
[FACT_1.Inidence #])/aggr(sum(POPULATION_1.Population), [Age Group]),FACT_1.Site, [Age Group]))
- Rate for two dimensions:
=sum(
aggr(sum(if(FACT_1.Gender = (POPULATION_1.Gender) and FACT_1.Year =(POPULATION_1.Year) and FACT_1.Race = POPULATION_1.Race and FACT_1.Area = (POPULATION_1.Area), [FACT_1.Inidence #]))/
sum(POPULATION_1.Population), [Age Group], FACT_1.Site, FACT_1.Race, POPULATION_1.Race))
Performance is bad for large data volume when using option 1.
Could you please help me in creating correct formulas for Option 2 where I join tables by a composite key created out of 5 shared dimensions? We should not loose population values for Age Group if there are no fact values for some Race, Gender etc.
Attached is a test QVW . First tab is for option 1 (correct outputs). The second tab is for the second option where I need you help.
Million thanks in advance!
