September 29, 2014 at 4:33 am
Hello All,
I am looking for advice on how to deal with a unique calculation (well unique to me).
I am new to SSAS so if you can point me to a tutorial or maybe give me the definition of what I am trying to do I can research. I am using Excel 2013 to query the cube. I am on 2008 R2, standard edition.
My example
Dim A : YEAR MONTH
DIM B : Route
DIM C : BUSTYPE
Dim D : Yes no indicators like Loyalty program
FACT
Passengers
Seats
Miles
Some Revenue stuff
YearMonthRouteBUSTYPLoyPAXSeatsMiles
20141AirportMinivanY36 30
20141AirportMinivanN26 30
20142AirportMiniBusN2026 30
End Goal:
Month PAXAvail Seat MilesRevenue Seat Miles
15180150
220780600
TOTAL25960750
The issue is at a leaf level it should only use the Min / Max / AVG,
but when you start to add up months, years, routes, bustyp then it should grab
the unique value for the leafs and then add up.
Doug
September 29, 2014 at 5:38 am
Am assuming you're concerned about the available seats measure. To me, you've got a design issue, the available seats shouldn't be part of this particular fact table as it doesn't match the grain. Effectively, what you have is a budget and you wouldn't put the budget figure for sales on every sales transaction, right?
Add a new fact, related to bus type. This is where the available seat count lives (could possibly be related to date also but that would assume the seat count can change per vehicle type per month).
When you create your Calc members to get seats X miles, both of these are additive when used individually but you may want to try a measure expression for the multiplication to ensure you get sum (AxB) rather than (sum (A) X sum (B)).
Steve.
September 30, 2014 at 5:33 am
Steve,
You are spot on with the seats changing by month. Some seats are broken and not sold.
So I have my new fact table and all relates to it but for one item. This is causing the cartesian product. I was wondering however if the Calc is my answer. On one of the pulls for a number, rather than a fact table it was in a calculated member and reads as follows: CLNG([SM ROUTE].[ROUTE].CURRENTMEMBER.PROPERTIES("MILES TRAVELED")).
Should I be looking to solve the issue in the calculations or just the design?
Thanks again for taking the time to help a new guy walk through this.
Doug
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply