1 Reply Latest reply: Jan 15, 2012 4:38 AM by Corrado Labinaz RSS

    Aggregation at Each Hierarchy level and also at each dimension

    906340
      Hi,

      I am new to obiee and datawarehousing, it would be great if you guys can help me solve this issue..

      sales--> Regular OLTP table
      -----------          
      customer_id     store_id     sales_amt
      1     1     100
      2     1     20
      3     1     10
      1     2     10
      1     3     40
      4     3     100
      1     4     20
      4     4     10

      designed OLAP tables as below:
      --------------------------------------------
      Store_dim--> this is a hierrachy table
      -----------------          
      Store_id     Market_id     Region_id
      1     1     1
      2     1     1
      3     2     1
      4     2     1

      now users want to measure distinct customers visiting the stores (and this should work going up in hierarchy)

      SALES_FACT
      ------------------

      store_id     num_of_distinct_customers
      1     3
      2     1
      3     2
      4     2

      if i design the fact table as above the counts are correct at granular level(store).. but going up in hierarchy it is giving me wrong counts..

      for example if i ask for distinct customers at market it returns me

      market1--> 4
      market2-->4

      but this should be
      market1-->3
      market2-->2

      please help me on desigining this fact and also how to solve this scenario in obiee..


      Thanks,
      Mahi