July 24, 2008 at 8:00 am
Guys Hi,
i have a question for you please!
The management asks for a daily sailes report. This reports contains per month the actual sales vs the budget sales, and their difference(delta). By the phrase "Budget Sales" we mean, the quantity (and also income revenue) our company wanted to "sell" in each month for each product.
I have a fact table containing the sales. Thus the fact has the invoice No, the day of invoice, the quantity and revenue, and other dimension keys such as customer, sales territory etc.
From this you can draw the conclusion that the grain (detail level) is customer - invoice - date - product - quantity and revenue.
Now i was wondering wether i can put my budget measure (apart from quantity and revenue, that are per invoice) in the same fact table.
But the problem is, that my Budget numbers are per product, and per month. Why is this a problem? because my fact table is per invoice number.
A though that came up to mind, is to actually count the numbers of invoices per product and per month, and then devide my "monthly per product budget" to these invoices. For example, if my budget for january 2008 for "product A" was 100.000 euros, and in january i had 55 invoices regarding the "product A", then i should devide my budget for january with the number of invoices (100.000 / 55) and put the result as a budget measure for each invoice. How does this sound to you?
Is this is good approach or should i consider something else, like creating a second fact table, or even put the budget in a dimension, etc?
Your help is much appreciated...
July 24, 2008 at 10:35 am
Personally I'm usually opposed to trying to coerce the budget figures (or any other measure thats not at the same grain) in to the fact grain. If you're using SSAS2K5, then you can simply use another fact table with a similar grain (likely date[month] - product - quantity and revenue) and then your users can report at the month level, by product, the budgeted and actual sales. If they then want to see what the breakdown of actuals were, they drill in to the figures using the deeper levels of Time dimension and also the Customer and Invoice dims.
The primary reason i wouldn't be allocating the budget figure across the customers/invoices is that would then give the impression to the end user that you budgeted at the customer/invoice level (which you didn't). If your users want to see the budget against the customer, then let them budget by customer/invoice and then bring that data in (not a lot of companies budget at the customer [definitely not invoice] level).
Steve.
July 25, 2008 at 2:53 am
Steve thank you for your answer.
I concluded to what you said not only because Kimball and you say so, but also for another very obvious reason. You can not "evenly break" the budget as per invoice for each month - day etc.. Imagine that your budget for a new product was x units on January, but you actually did not sell any of this product on January. 🙂 Where does the budget go? lol?
My question is that if a create two fact tables, the budget fact one will be per month while the invoice fact one will be per day.
How am i going to handle this? Will i need a seperate date dimension table? (Your opinion). And will I join the two fact tables how? Will it be through the customer dimension? (on customer key)? or through a single date dimension?
your knowledge is precious to me! 🙂
July 25, 2008 at 3:01 am
Hi,
Have 2 fact tables, and create 2 measure groups in your cube - one for Sales and the other one for Budget.. that is a solid design with more flexibility also..
Warm Regards,
Neel aka Vijay.
July 25, 2008 at 3:06 am
Hi Neel,
truly appreciate your prompt reply. I will do as you said reagrind measure groups.
However, please inform me, as i am new, on the subject wether i should join the two facts, on lets say customer id, or should i join the two facts through a dimension, like customer dimension, or date dimension...
What is the recomended approach?
July 25, 2008 at 3:07 am
You can have common dimensions like Time and Product. In the 'dimension usage' option in the cube, you should link the dimension keys with corresponding foreign keys in the fact tables. For example, date key will be linked to 'sales day' in the sales fact table and month will be linked to the 'budget month' in the budget fact table...
hope this helps.
Warm Regards,
Neel aka Vijay.
July 25, 2008 at 3:14 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply