April 16, 2012 at 9:03 am
I've got an issue that I'm struggling to get my head around. Normally, in a fact table you've got a bunch of foreign key dimension columns and a bunch of measure columns. The value in the measure column for a particular row is the value for the exact combination of dimensions on that row and, by grouping by any dimension value and summing the measure you get the total for that dimension.
E.G. say I have a productivity forecast table that has Product, Day and Depot dimensions and a UnitsProducedForecast measure. To get the forecast of units to be produced by depot I just sum UnitsProducedForecast and group by Depot. To get the forecast of units by depot and time I group by depot and day (possibly rolling days up into weeks, months or whatever is apropriate). Groovy, no problem there.
Now lets say that we do different kinds of forecast. Maybe we have a contracted forecast, a planning forecast, an delivery forecast etc. These don't naturally add together, rather they sit alongside each other. If I add Forecast Type as a dimension then the above queries would break. summing the UnitsProducedForecast grouped by depot would no longer give me the correct forecast for a depot, it would give me the sum total of all the forecast types for each depot.
How do I hold that data so I can use it effectively in SSAS (or any other analytical program for that matter)?
I'm thinking that the forecast type shouldn't be a dimension but rather I should have a measure column for each forecast type. The problem there is that the forecast types tend to change over time and I'd constantly be rewriting the table definitions. In transactional DB terms having a forecast type column would be the correct way to handle it (bear in mind I come from a transactional system background so that's where my expectations come from).
In fact, even as I write this I'm convincing myself that individual measure columns is the correct way to go and I just have to live with the fact that I'll change the columns over time. I still can't help feeling I'm missing a trick, though.
Whats the correct way to handle this in the BI world.
April 17, 2012 at 2:50 am
Hi,
not sure I understand your question properly, but essentially SSAS does have a built-in mechanism to deal with measures that should not aggregate - look up additive and semi-additive measures (which is a property of a measure in cube designer) - these properties are set for example when dealing with bank account balances, inventory levels (in these examples you also do not want to sum up all bank balances or all invenory levels - you want the lastnonempty row (another term to look up on google).
Here are some useful links
Configure Measure Properties: http://msdn.microsoft.com/en-us/library/ms175623.aspx
Semi Additive Measures using SQL Server Standard : http://www.purplefrogsystems.com/blog/2008/04/semi-additive-measures-using-sql-server-standard/
I hope this helps,
B
April 17, 2012 at 1:38 pm
Ah, that sounds like the sort of thing I'm after! I haven't got time to read up now (just leaving work) but I'll check it out tomorrow. Thanks for the pointers.
April 18, 2012 at 5:59 am
Just had a chance to do a quick read up and this is definitely what I was after. I think my problem was that I just didn't know what to google for.
Thanks bleroy. You've been a great help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply