1. Junior Member
Join Date
May 2014
Posts
8

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

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. Senior Member
Join Date
Jan 2013
Posts
796
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. Junior Member
Join Date
May 2014
Posts
8
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. Senior Member
Join Date
Jan 2013
Posts
796
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.

#### Posting Permissions

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