1. Junior Member
Join Date
May 2003
Posts
22

## diferent granularity problem

Hello:
I am trying to build a model where there are 2 cubes: Sales and Budget sales. The first cube has daily granularity, and the Budget cube has monthly granularity.
my idea was to construct a virtual cube with sales and budget cubes. But if i want to compare measures of this two cubes, i should to have a shared time dimension for this two cubes.
my question is: what is the best strategy for modeling this situation? parent child time dimension?, there is another form to do that?
tnanks, any help is very important.

Join Date
Nov 1999
Posts
1,618

## RE: diferent granularity problem

You should create a shared time dimension, but join it into the sales cube at the day level, and into the budget cube at the month level. Use the 'level' attribute of <DimensionUsage> in order to achieve this.
<Schema>
<Dimension name="Time">
...
</Dimension>
<Cube name="Sales">
...
<DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
</Cube>
<Cube name="Budget">
...
<DimensionUsage name="Time" source="Time" level="[Time].[Month]"/>
</Cube>
</Schema>

3. Junior Member
Join Date
May 2003
Posts
22

## RE: diferent granularity problem

ok, this solve part of the problem. But i have another question.
the sales cube is supported with a sales fact table, the fields are:
Time_id(day), customer_id, employee_id, .., sales , unit sales.
my time dimension table has the following atributes: time_id, day
, week, month, quarter, year.
the budget sales is supported with another fact table. But in this fact
table i need to storage budget with monthly granularity. the fields are:
Time_id(month), customer_id , budget sales.
I mean, in my database i have only 12 budget sales values for storage
in a particular year. for this reason i need a "month_id", but in my
time dimension table i have day information only.
How can i do to take a particular month reference or key?.

4. Senior Member
Join Date
Sep 2002
Posts
475

## RE: diferent granularity problem

The answer is to avoid having a time dimension table.
Here's how:
https://sourceforge.net/forum/messag...msg_id=2207472

5. Junior Member
Join Date
Jan 2003
Posts
4

## RE: diferent granularity problem

Hi Julian,
Would I still use the level="[Time].[Month]"/> Syntax (I see this post is 2 years old)
There is no sample of DimensionUsage with a level attribute in the FoodMart, and when I try to use that in my schema it does not work correctly.
When I use level="[Time].[Month]" I get an exception because Util.lookupHierarchyLevel returns null.
When I use just level="Month", it will join to the right column, but the sum will be about 30 times bigger, because there are about 30 days in a month.
Thanks!
Calin

6. Member
Join Date
Nov 2006
Posts
54

## I can't achieve this too

Hi. I have a shared snowflake time dimension.

I have a cube wich uses this time dimension aggregated by month, I have specified in dimension usage Level="Month".

But it doesn't work in a virtual cube!!!

What I'm doing wrong?

Agustin

7. Junior Member
Join Date
Jul 2006
Posts
13

## level attribute of DimensionUsage

Originally Posted by mondrian_calinm
Hi Julian,
Would I still use the level="[Time].[Month]"/> Syntax (I see this post is 2 years old)
There is no sample of DimensionUsage with a level attribute in the FoodMart, and when I try to use that in my schema it does not work correctly.
When I use level="[Time].[Month]" I get an exception because Util.lookupHierarchyLevel returns null.
When I use just level="Month", it will join to the right column, but the sum will be about 30 times bigger, because there are about 30 days in a month.
Thanks!
Calin
Two years later I have the same problem. It would be wonderful if I could get an answer to this. The level attribute of the DimensionUsage works fine if I snowflake the dimension, but it returns the wrong results if I don't.

Thanks a bunch,

Calin

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•