April 17, 2014 at 2:32 pm
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_TimeLocation_Key HOD_Key DOW_Key TransType_Key ...
1 01/01/2014 01/01/2014 12:00:01.120111 2
2 01/01/2014 01/01/2014 12:00:02.200111 2
3 01/01/2014 01/01/2014 12:00:03.300111 2
...
---
HOD_Key HOUR
1[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 DOW
1 Sunday
2 Monday
3 Tuesday
4 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 3200
b) 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
April 22, 2014 at 5:07 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply