Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Virtual Cubes - Advice

  1. #1
    Join Date
    May 2014
    Posts
    8

    Default Virtual Cubes - Advice

    Hi

    I'm facing a desing issue. If you see "Picture 1.png", you'll see I have my "Deads" cube in day granularity (Time dimension), and I have to create now another cube called "Populations" wich is in year granularity.

    Picture 1




    My bussiness users wants to get some statistics between Deads and Population, but the population raw data is set by year. I came up with the creation of a virtual cube, but the different Time granularities of these two dimensions has troubled me. The "Population" dimension has not been created yet.


    Please, see "Picture 2.png" to see the fix I was thinking. I think that with pure SQL I could get what I want, but MDX has made my life a little easier.

    Picture 2


    Any idea what I could do here??

    I've read this at
    http://infocenter.pentaho.com/help/index.jsp?topic=%2Fanalysis_guide%2Fconcept_virtual_cube.html

    Virtual cubes are useful for situations where there are fact tables of different granularities (for instance, one Time fact table might be configured on a Day level, another at the Month level)


    But, I don't know how this can be possible.


    Bye, and best regards


    Jonathan

  2. #2
    Join Date
    Jan 2013
    Posts
    796

    Default

    What you propose is a reasonable way to go about it, i.e. have a split [Year] out into a separate dimension and have that dim shared by both cubes. I've worked with some models that actually had each time attribute split out as separate dimensions. That can be helpful both for cases like yours where you have cubes of different granularities, as well as to allow mixing and matching time attributes (for example, putting years across the columns, quarters down the rows). Another alternative: I've seen some models where a Year level fact table arbitrarily chooses a day (say January 1st) and forces the cubes to act as if they are the same time granularity. Using that design can get tricky, though, because you would want the measures from the [Population] cube to show the year value regardless of what month or day was in context, so you need to wrap those measures in calculations which change context to the relevant year.

  3. #3
    Join Date
    May 2014
    Posts
    8

    Default

    It's funny because Mondrian documentation says that virtual cubes are useful to sincronize cubes with diferrent granularities, but I dont see how to get there, and the documentation it's quite poor and short.

  4. #4
    Join Date
    Jan 2013
    Posts
    796

    Default

    You are right, there should be more and better documentation on this. The idea here is just to include the jointly-applicable attributes in shared dims across the two cubes. This can mean splitting apart hierarchies where lower levels are not applicable to all base cubes, and/or creating alternate dimensions.

Tags for this Thread

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.