Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OLAP Cube Calculated Averages Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:52 AM
Points: 6, Visits: 274
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 2
2 01/01/2014 01/01/2014 12:00:02.200 1 1 1 2
3 01/01/2014 01/01/2014 12:00:03.300 1 1 1 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
Post #1562853
Posted Tuesday, April 22, 2014 5:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:12 AM
Points: 513, Visits: 1,000
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





I'm on LinkedIn
Post #1563769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse