A challenging CUBE related question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

A challenging CUBE related question

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-08 : 07:51:07

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.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-08 : 09:15:01

An alternative approach is to build two cubes. Planned and Actual, with shared dimensions.
Then build a virtual cube from those 2 cubes.

Otherwise it sounds to me like the planned data is not being read into the cube due to missing joins.
What is the structure of you fact + dimension tables in the datamart ?

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-08 : 22:29:07

Thanks for the tip rockmoose. I have never used Virtual Cubes but i guess this could be the first time. Wish me luck :(

Yes I think so too that the missing joins are what is causing the problem.

I am not clear regarding your question (below). Can you kindly clarify:
"What is the structure of you fact + dimension tables in the datamart ?"

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-09 : 03:21:26

I was just asking how the tables you are using in the datamart are defined.
You could provide CREATE TABLE statements, that way it is easier to se how the tables are joined
and that might help in solving the problem.
I find virtual cubes very good for combining data from different sources / different granularity.

rockmoose

Tim
Starting Member

392 Posts

Posted - 2004-12-23 : 09:05:28

J, I think you should consider a "scenario" deimension rather than repeating your measures with "actual" or "planned" appended. Also you may be able to distribute the planned data down to the customer grain somehow, in order to gaet it to same rain. Risk here is that your users give more respect to the cusomter level planned data than it deserves.

Rockmoose, when you use this technique, you are always bring the actuals up to the planned data grain, that is, virtual cube brings all data to highest common denominator?

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-24 : 04:35:24

Hi all,

Finally got to trying your suggestions (at least a few of these)... I tried using Virtual Cubes but so not get an option to select 2 facts tables... I know thismay sound silly but from thereply, I got the impression that I will somehow be able to use 2 facts tables (one containing actual Sales related data and the other having plan related data) and will then be able to "merge" them somehow????

Can you guide me on theright track a bit please.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-26 : 15:49:46

quote:
Originally posted by Tim

Rockmoose, when you use this technique, you are always bring the actuals up to the planned data grain, that is, virtual cube brings all data to highest common denominator?

You can force the planned data to the lowest grain by assigning the planned data to a member at the lowest level, for example the last day of each month.
It is also possible to use Parent-Child dimensions where You can have data on "non-leaf" members.
Otherwise You are correct in your assumption.

Virtual cubes:
A virtual cube is a view of 1 or more "regular" cubes.
The steps to be taken are the following:
1. construct 1 regular cube from sales_data fact table
2. construct 1 regular cube from planned_data fact table
3. construct a virtual cube from those 2 cubes
Use shared dimensions for all dimension that can potentially be shared between cubes.

Distributing the "higher" grain planned data to grain of the actual sales is also a good idea. It is easier to work with data with the same granularity.
It is possible to hide the measures of the planned data if one drills down to the lowest level.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-26 : 23:24:22

Thanks. Will give it a try. Till then can you kindly explain how can I do the following:

"It is possible to hide the measures of the planned data if one drills down to the lowest level."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 04:29:28

quote:
Originally posted by Joozh

Thanks. Will give it a try. Till then can you kindly explain how can I do the following:

"It is possible to hide the measures of the planned data if one drills down to the lowest level."

Lets say for example that You have a dimension [SaleDate] and a measure [PlannedSale] that makes no sense to show at the day level of that dimension.
What You do is to create a regular measure [PlannedSale_hidden] with the visible property set to "no" and calculated member [PlannedSale] that hides the day level.

MDX of the calculated member:
iif([SaleDate].currentMember.Level is [SaleDate].[Day], null, [Measures].[PlannedSale_hidden])

The users of the cube will see the calculated member [PlannedSale], but if they drill down to Day level then it will not display any data.
( While testing things out, it is wise to keep all measures visible, and when things are working correctly You can hide the appropriate measures, that shouldn't be seen by the user. )

Does this make any sense to You?

-----------------------------------------------------------------------------------------------
I see now in your original post that the Customer dimension is not planned to Customer level,
That shouldn't be a problem:
There are some options:
1. don't include that dimension in the "Planned" cube, if the user drills down on Customer then no planned data will be displayed.
2. You have to dsitribute the planned data to Customer level (as Tim said) with some algorithm to be able to view estimated planned data at Customer level.
3. Show the total Planned value for all Customer for each customer, that way the user can see how much each customer has contributed to the total Planned value.

MDX(3) Take the planned value for the "All customers" member:
([Customer].[All Customers],[Measures].[PlannedSale])

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-27 : 04:47:42

Thanks rockmoose.

This gives some intersting and neat ideas.
I am realtively new to this world and thus it will take me a while to fully absorb and put your suggestion in the form of a solution but YES, I am able to follow a good portion of your mail.

Many thanks for yur reply. I have afeeling that this particular suggestion will come in handy on quite a few occassions.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 05:07:50

You're welcome
Happy Holidays!

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2004-12-27 : 05:09:46

Thanks rokcmoose and the same to you :)

Thanks & Regards.

-J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources