We have a requirement to build a cube based on data that will require
an Unbalanced Dimension.
We have plan data at the following levels in the PRODUCT hierarchy:
ALL PRODUCTS -> CATEGORY

We have all other data at:
ALL PRODUCTS -> CATEGORY -> SUBCAT -> ITEM

Can we build a Plan cube using the abbreviated dimension, a Cube for
the other Full dimension data and then join them in a virtual cube?
Will this allow us to overcome the inability to aggregate up unbalanced
dimensions? If not, does anyone have any good alternatives/suggestions?
Thanks.

When loading data at different levels of a dimension, the virtual cube
approach is discussed in past posts to this newsgroup. There would be a
shared Product dimension, but its lowest levels would be disabled in the
Plan cube. Here is one of the posts that described such an approach:

http://groups.google.com/groups?hl=e...1F9F6%40dsslab
.com
Subject: Re: Loading Data at different Levels
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-05-02 08:58:03 PST


A virtual cube should be very fast, and I would recommend this over
parent-child dimensions in this case.

In one regular cube, you disable the month level so that the forecast
data loads to quarter. In the other regular
cube, you leave months enabled. Joining these in a virtual cube means
you don't need to use the LookupCube() function,
of course. If you were using LookupCube() in a virtual cube, then there
was unnecessary MDX.

When you do this, is it slow to process or slow to query? MOLAP and
HOLAP aggregations will be fast to query; complex
MDX will be slow; hopefully, it's not unnecessarily complex.

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
george (AT) dsslab (DOT) com

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Deepak, this was very helpful. I'll post again if we run into any
other issues.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Marc,

One other thing to watch out for, when joining a fact table to an
intermediate level of a dimension, is the potential for multiple
dimension records to join to a fact record. Here are a couple of
techniques from another earlier post:

http://groups.google.com/groups?hl=e...C9DB0D%40dssla
b.com

Subject: Re: Different granularity for measures
View: Complete Thread (4 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-08-26 06:53:51 PST

You need to either optimize the joins away for the SKU and Category
tables,
or snowflake them so that SKU information is in a separate table from
category information. (You could do both, too.)

In order to optimize the joins, ensure that Category keys are unique
within
the level. Then, AS2K won't attempt to join in the product dimension
when it
processes the cube. The join is what throws off the numbers, as you get
N
fact rows per 1 dimension row.

Your Forecasts will still have SKU disabled and join to Prod_catg_key in
the
category table (manually ensure this is set in the cube editor). You SKU
information will join to SKU key.

HTH
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the heads-up! Your assistance is much appreciated.

- Marc

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Still don't get this.....sorry for being a bit slow....

Brian Altmann gave me some pointers as well a while back...

Can someone help me once again....a dimension with 2 levels where one
measure is at top level and another measure is at bottom level - how do i
create these cubes and getting them into a virtual cube ?

Tried putting the dimension into a cube and disabling lowever level but when
looking at this cube alone (not the virtual) the result is already wrong -
but the granularity of the fact table isn't on the lowest level - what will
it help to disable these levels ? Do I have to get it down to the lowest
level and then disable ?

Hard to explain by post....

/Michael
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Got it !!!!

Read it once again....just needed to change the joins in the cube editor...

Great !!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Deepak,

Your suggestions worked great, and now I am trying to implement the same
thing for the time dimension. With the previous problem, the Category
level at which my data stopped was the top level in the dimension (other
than the ALL level).

With Time, my data ends at the Month Level, which is below Year and
Quarter, and above Week.

YEAR -> QUARTER -> MONTH -> WEEK

Normally we have a single DIM_TIME table whihc is a denormalized table
containing records for each level (much like our product table).
Referencing George's comments you forwarded, I created the following
tables:

DIM_YEAR
Year_Id
Year_Descr

DIM_QUARTER
Quarter_Id
Quarter_Descr

DIM_MONTH
Month_Id
Month_Descr

DIM_WEEK
Year_Id
Quarter_Id
Month_Id
Week_Id
Week_Descr


I then created a dimension using these tables in which I created a
snowflake joining all the tables to the DIM_WEEK Table via thier IDs.
When I implemented this dimension in the cube for which the data ended
at the month level, I joined DIM_MONTH to the fact table via Month_Id
and disabled the week level.

When I then processed the cube, I noticed that I was getting duplicate
records (one for each Week of the Month on the DIM_WEEK table).

I then changed the Structure to look like this:

DIM_YEAR
Year_Id
Year_Descr

DIM_QUARTER
Year_Id
Quarter_Id
Quarter_Descr

DIM_MONTH
Quarter_Id
Month_Id
Month_Descr

DIM_WEEK
Month_Id
Week_Id
Week_Descr

And created the dimension using a "Cascading" join sequence:
DIM_YEAR -> DIM_QUARTER -> DIM_MONTH -> DIM_WEEK

and implemented the dimension in the cube as before.

This eliminated the duplicates and seems to be working fine.

My question is: Is this the appropriate way to do what I am trying to
do? I know George mentioned a Snowflake schema, but I was not able to
get it to work. Sorry for the long post, and thanks in advance for any
guidance.

- Marc

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Marc,

I think that the "cascading" join sequence is the correct approach in
this case, because then each higher level in the hierachy can be
directly "rolled up" from its lower level. So, "Quarter" can be directly
derived from "Month".

In the first schema, the problem is that higher levels are directly
derived from the lowest ("Week") level, so it is impossible to avoid
that table in the cube process joins.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Deepak. Glad to hear I was on the right path

- Marc



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!