I habe a sales cube that includes a item dimension with the following levels

1. Item group
2. Item
3. Item variant

The fact table relates to this dimension by the item and variant fields.

I would now like to add budgets to the cube, but I only have these at Item
group level, so the question is how can I add the budgets to my cube, when
the fact data does not exist at the same detail level ?

I've tried creating a new virtual dimension based on the item dimension,
with only the item group level, which resultet in some insane values for my
budgets.

Any help would be greatly appreciated.

Thomas

Hi Thomas,


This scenario has a straightforward solution in AS 2005 (since you can
use the granularity attribute); but maybe you're working with AS 2000.
In that case, a common approach is to create 2 cubes (budget and sales),
with appropriate lower levels disabled in the budget cube. These are
then combined in a virtual cube - more details are in these earlier
threads:


http://groups.google.com/group/micro...olap/msg/7c61e
f9b0ad20337?hl=en&
From: George Spofford <geo... (AT) dsslab (DOT) com>
Date: Thu, 02 May 2002 12:01:47 -0400
Subject: Re: Loading Data at different Levels

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.
...

http://groups.google.com/group/micro...olap/msg/138ca
29d5c263b1f?hl=en&
From: George Spofford <geo... (AT) dsslab (DOT) com>
Date: Mon, 26 Aug 2002 09:41:40 -0400
Subject: Re: Different granularity for measures

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.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks.

I'm using SQL Server 2000 so disabling the lower levels was the solution.

Best regards
Thomas

"dpuri" wrote: