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:
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.
Forget it, I figured it out, I just set the aggregation STDEV in the DW itself instead.