Set modifiers for metrics and dimensions from 2 tables shown in one chart

[#1]

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!