1 Reply Latest reply on Nov 14, 2015 3:48 AM by 679874

    Standard Deviation Question...

    679874

      Hi All,

       

      Is there a way in OBIEE (I'm sure there is, just can't figure it out) to compute Standard Deviation in the FACT Table in the BMM layer if you've already aggregated the data attributes?

       

      For example, say I have two attributes in the FACT Table in the data warehouse, Count of Customers (C_CUST) and Total Dollars Spent (T_DOLLARS) which come from the Customer dimension in a merge statement like:

       

      • MERGE INTO FACT
      • BLAH BLAH COUNT(DISTINCT CUSTOMER), TOTAL(DOLLARS SPENT)
      • BLAH BLAH BLAH BLAH;

       

      Using average as an example, I can't put the aggregate AVG on the Total Dollars Spent fact attribute, if I do I just get a blank field in Answers. But if I create a new Logical Column with  T_DOLLARS / C_CUST I get the correct average.

       

      So if I put STDDEV on T_DOLLARS I just get no results (which makes sense since it doesn't have all the numbers in the range to make the correct calculation in the fact table), but is there another way to do this? I can do this in Analytics in the customer dimension directly, but then I'm not joined to the Fact table where all the other dimensions are joined.

       

      Thanks all.