January 14, 2014 at 6:26 am
Good Morning All,
I'm working on developing a cube that measure's budget and actual cost for a customer that I'm working with. We have serveral dimensions that comes into play:
Organization - this dimension defines the various internal departments at the customer location where each department sets a budget or actual cost for each month.
DateTimePeriod - this dimension defines the transaction date when the budget or actual cost was recorded. This dimension contains year, quarter, month and day columns.
Expense Item - this dimension defines a specific expense item that a budget and actual cost is assigned too such as rent, utility, software licences,etc...
Cost Type - this dimension defines if the cost within the fact table is a budget or actual cost.
Within my fact table I store primary key fields values for each of the dimension table listed above. Included with this table is a cost column that represents the budget or actual cost. The problem that I'm having is....The budget cost and actual cost are seperate records...For example, I have one record that has the budget cost and then I have another record that has the actual cost....
My feeling is that the budget and cost records should be store on same record instead of seperate records. Also I would like note we're using PerformancePoint to surface the cube data to the client and both the budget and cost needs to drill down to the month level only for phase 1. I have a feeling that the customer would want in the in future to measure down to the day level...
So my question is....What is a better design:
Keeping the actual and budget costs within a fact table on seperate rows using the Cost Type dimension to identify if the cost is a budget cost or an actual cost or....
Keeping the actual and budget costs within a fact table on the same row and removing the need for a Cost Type dimension......
Please help...
January 14, 2014 at 6:30 am
You should definately store them on the same row in different columns.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2014 at 6:51 am
Why????
January 14, 2014 at 6:57 am
Because budgets and actuals are not the same thing.
If you want to compare them (for example to calculate variances or KPIs), querying becomes much more difficult if you store them as two seperate rows. If they are in the same row, you can do very easy calculations using the columns.
But it is not even sure you can store them in the same fact table. Are the actuals and the budgets on the same grain? What if actuals are on a daily basis, but budgets are calculated on a monthly basis? How are you going to store them in the same table? It's possible you need two seperate fact tables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2014 at 7:01 am
For phase 1, they need to be measure by the month level only which is the reason why I feel they should be on the same record, but in the future (and I have a good feeling about this) we'll have seperate the budget and actual into their own fact tables, as you said....My question is breaking out the costs into seperate tables, would this require MDX query development...
January 14, 2014 at 7:09 am
Personally I'd go with the separate table approach "now". You know you're heading that way anyway, the overhead of introducing it now is minimal compared to how you're going to have to refactor in the future.
Depending on where you are in your SSAS journey I'd also recommend grabbing two books - look for Teo Lachev and then Ferrari and Russo - these are here and here on Amazon. You'll get a great idea of many design patterns.
Steve.
January 14, 2014 at 7:18 am
stevefromOZ (1/14/2014)
Personally I'd go with the separate table approach "now". You know you're heading that way anyway, the overhead of introducing it now is minimal compared to how you're going to have to refactor in the future.Depending on where you are in your SSAS journey I'd also recommend grabbing two books - look for Teo Lachev and then Ferrari and Russo - these are here and here on Amazon. You'll get a great idea of many design patterns.
Another great book (and technology independent) about dimensional modelling is Star Schema - The complete reference.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2014 at 7:19 am
Thanks for the book references....I was wondering if had move each cost into their seperate tables, where the budget costs is record 1st of every month and the actual cost is recorded every day; if needed to do a comparions between them, for say like YTD, MTD..I would need to write some MDX query to aggregate actual costs to perform the MTD or YDT comaprsion reports correct?
January 14, 2014 at 7:24 am
brandonmichaelhunter 57708 (1/14/2014)
Thanks for the book references....I was wondering if had move each cost into their seperate tables, where the budget costs is record 1st of every month and the actual cost is recorded every day; if needed to do a comparions between them, for say like YTD, MTD..I would need to write some MDX query to aggregate actual costs to perform the MTD or YDT comaprsion reports correct?
That's correct. You could also create a monthly snapshot table for the actuals. In that case it's pretty easy to compare actuals versus budgets.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 16, 2014 at 10:18 am
Some consider actuals and budget two different business processes that deserve their own fact tables. Then, a second level consolidated fact table can be created from those two business processes where the actual and budget amounts exist as two separate columns on the same row.
Whatever you do, remember this:
When combining data from 2 different business processes into the same table, make sure the data lives at the same grain and dimensionality; otherwise you can get pinned into a corner being forced to eliminate or aggregate some dimensions to force the "one-to-one correspondence" between the processes.
Forcing the "one-to-one correspondence" between the processes is something OLAP tools handle fairly well. Sorry for digressing a bit.
January 16, 2014 at 10:27 am
When there is more than one "budget scenario" for which you are modeling, it may get a little hairy. This Kimball Design tip suggests the single table approach for when one must deal with more than one budget scenario:
http://www.kimballgroup.com/2006/08/02/design-tip-82-turning-the-fact-table-on-its-head/
January 16, 2014 at 1:43 pm
sneumersky (1/16/2014)
When there is more than one "budget scenario" for which you are modeling, it may get a little hairy. This Kimball Design tip suggests the single table approach for when one must deal with more than one budget scenario:http://www.kimballgroup.com/2006/08/02/design-tip-82-turning-the-fact-table-on-its-head/
What they don't mention in this design tip is that you make the "generic" fact semi-additive, which means you constantly have to put constrains on queries.
I'd rather have two starts you can join together through conformed dimensions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 16, 2014 at 4:13 pm
Definitely a PITA....something we don't want to do for the end user.
January 17, 2014 at 3:11 am
sneumersky (1/16/2014)
Definitely a PITA....something we don't want to do for the end user.
What does PITA stand for?
January 17, 2014 at 3:13 am
Lempster (1/17/2014)
sneumersky (1/16/2014)
Definitely a PITA....something we don't want to do for the end user.What does PITA stand for?
Pain in the *ss.
Or a kebab sandwich, whatever you prefer. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply