PDA

View Full Version : diferent granularity problem



abz_diego
10-10-2003, 05:45 AM
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.

jhyde
10-10-2003, 10:19 AM
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>

abz_diego
10-11-2003, 03:07 AM
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?.

sgwood
10-11-2003, 03:59 PM
The answer is to avoid having a time dimension table.
Here's how:
https://sourceforge.net/forum/message.php?msg_id=2207472

mondrian_calinm
07-27-2006, 06:59 AM
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

vectorr3
04-03-2008, 03:47 PM
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?

Thanks in advance
Agustin

calinm
10-15-2008, 02:26 PM
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