## OLAP Cube Calculated Averages

 Author Message Marco Barroso SSC Rookie Group: General Forum Members Points: 27 Visits: 311 I was hoping someone could shed some light on this question:I have an OLAP Data cube using the following structure. (Below is a simplified version)`Measure------- Total Transactions (int) Dims---- Location Location_Key (int) Location_Name (varchar) HOD HOD_Key (int) HOD_Name (varchar) DOW DOW_Key (int) DOW_Name (varchar) Date Date_Key (date) ... TransType TransType_Key (int) TransTypeID (int) TransTypeName (varchar) Fact---- ID Date_Key TransDate_Time Location_Key HOD_Key DOW_Key TransType_Key ...1 01/01/2014 01/01/2014 12:00:01.120 1 1 1 22 01/01/2014 01/01/2014 12:00:02.200 1 1 1 23 01/01/2014 01/01/2014 12:00:03.300 1 1 1 2... ---HOD_Key HOUR1 [00:00 - 01:00]2 [01:00 - 02:00]3 [02:00 - 03:00]4 [03:00 - 04:00]5 [04:00 - 05:00]...DOW_Key DOW1 Sunday2 Monday3 Tuesday4 Wednesday...`Question:1) I need to be able to include averages next to the respective Transaction Count aggregations using multiple dimensions. `Example: a) Location Date TransType TransactionCount DOWAverage 1 12/01/2014 PYMT 2000 2100 1 12/02/2014 PYMT 2500 2400 1 12/03/2014 PYMT 3000 3200b) Location Date HOD TransType TransactionCount HODAverage 1 12/01/2014 [07:00-08:00] PYMT 200 210 1 12/01/2014 [08:00-09:00] PYMT 250 240 1 12/01/2014 [09:00-10:00] PYMT 300 320 `2) Averages need to be the last 120 days of (Location + DOW + TransType + sum(transactions) ), (Location + HOD + TransType + sum(transactions)), (other combination of dims)What would be a good way to build this into the OLAP cube ? Thanks PB_BI SSChampion Group: General Forum Members Points: 10181 Visits: 2983 Firstly I would make day of week an attribute of your date dimension and put it in a hierarchy. Then you could use the AVG function to achieve what you need to. See the last example here:http://www.mdxpert.com/Functions/MDXFunction.aspx?f=82 Im on LinkedIn