Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: diferent granularity problem

  1. #1
    Join Date
    May 2003
    Posts
    22

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

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default 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. #3
    Join Date
    May 2003
    Posts
    22

    Default 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. #4
    Join Date
    Sep 2002
    Posts
    475

    Default 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. #5

    Default 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. #6

    Default 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?

    Thanks in advance
    Agustin

  7. #7
    Join Date
    Jul 2006
    Posts
    13

    Question level attribute of DimensionUsage

    Quote Originally Posted by mondrian_calinm View Post
    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.