Good morning,
A am actually prototyping at customer's site and I have the following problem for which I need some help:
- For performance reasons, I have created an application which contains basically an agregated facts table, a detailed facts table and dimensions tables linked to the agregated facts table, since 90% of anaylsis are performed on agregated data. Detailed fatcs are here only for specific queries.
The agregated fatcs table containing 30 million rows has a structure similar to this one:
hash_key
product_code
period_code
amount
quantity
The detailed facts table containing around 120 million rows has a structure similar to
hash_key_2
customer_code
detailed amounts and quantities
Hash keys have been created to enable the links between detailed and agregats tables. The formula to create the hash keys is basically
autonumberhash128(product_code,period_code)
Thus, they contain exactly the same values in both tables. Their name is different though to disable the natural link in the data model. The detailed facts table is considered as a data island.
Performances are good.
Now let's say, I want to calculate the distinct count of customer_codes according to current selections
As it is a very punctual demand, I execute the following formula:
COUNT({<hash_key_2=P(hash_key)>} distinct customer_code)
and it works... as far as I don't want to produce a chart like this one:
Product Code Customer Count
P1 10
P2 5
P3 7
Total: 22
With this formula, what I get in facts is
Product Code Customer Count
P1 22
P2 22
P3 22
22 corresponding to the total of all rows, which is basically normal. Is there any possibility to perform such analysis or should I also xonsider to add PRODUCT_CODE field to my detailed facts table?
