0 Replies Latest reply: Oct 12, 2011 4:49 AM by Christophe JOUVE RSS

    Agregates, facts and P()

    Christophe JOUVE

      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?