SQL Server Performance Forum – Threads Archive

A challenging CUBE related question

Hi, I have data coming in from our OLTP’s view which has the following sales related information: 1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount
I am bringing in this information into my "staging area" and from there I am building my
Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSales and Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways… Now the problem: I also have planning related data available from another OLTP based view which contains the following information: 1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale) You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level). After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products: Planned Sale, Actual Sale, Planned Qty., Actual Qty, However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts …. I really hope that my questions and requirement is clear.Can someone please help me get to the solution. Many many TIA.

Dont you think you are better off creating a seperate Fact table for your planning data and also create a seperate cube. You can also make product and time a shared dimension so you will not need to create seperate dimensions for the cube. But if this is not possible, for the planned data in the customer dimension you can create an NA (not applicable) member and assign all planned data the key value for the NA member. For the time dimension it can be trickier, you see a FACT table should always containt the lowest level of detail this is also known as the Leaf Level. What key value are you using for the time dimension in this planned data. If you are using null then when processing time comes it will not join the fact table with the time dimension for this data resulting in missing dimension keys. Raulie
Hewlett-Packard Company

Thanks for the reply. I get some ofyour points but plese allow me to get back to you after I have absorbed all of it. It’s frustrating that despite playing with cubes and stuff for the past 4-5 months (without traiing and using help from forums as this) I have been able to get this far but anyone else in the same time would have gained much more knowledge and experience. Tell me about disappointments <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

Hi Raulie, I finally ws able toabosrob your suggestion (i think) and thus created a dummy customer…. Now I am able to see the planned data alsoand will be fine as long as I do not zoom into the Customer dimension. I think I now also understand your following point: "For the time dimension it can be trickier, you see a FACT table should always containt the lowest level of detail this is also known as the Leaf Level. What key value are you using for the time dimension in this planned data. If you are using null then when processing time comes it will not join the fact table with the time dimension for this data resulting in missing dimension keys" Since the plan data is available till month level so I will be fine as long as I do not zoom deeper than the month level on my time dimension….. I think this is what you saying. Right? Thanks for your tip <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards.

]]>