Combining time dimensions in Virtual cubes

Question

Combining time dimensions in Virtual cubes

SQLPhil

SSCarpal Tunnel

Points: 4204
More actions
November 14, 2006 at 8:27 am

#171270

We've got a rather large performance database with some 600+ million rows. We have a cube that processes this data with a time dimension taking information from the fact table, but this is now taking more than 30 hours to process!
What I have aspirations of doing is breaking up this beast into smaller cubes (preferably quarterly cubes containing performance information for each quarter of the year). I'll then have a virtual cube that sits on top of these, combining the data in each of these quarterly cubes so I can present it in a number of pivot tables/charts. Each of these quarterly cubes will have the same schema, same dimensions, same measures. The only thing that will be different is the data they contain.
However, we're having difficulty in combining the dimensions, in particular the time dimensions. Is there any way of combining the data from these time dimensions (much like a SQL Union query)?
Alternatively, as my Analysis Services skills are pretty limited, if anyone has any ideas on how to improve the efficiency of this model then I welcome any suggestions.
Many thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Phil, have you checked out Partitioning at all yet? If you're using AS2K5 it's quite simple to implement and maintain. AS2K is still quite easy to create the partitions but maintenance is a bit harder.

I recently built a 13 partition (single) cube with ~105mm ros per partition. When someone had tried to build this with all ~1.2bn rows in a single partition, it wouldn't even open. WIth the partitions it actually performs quite well. Just fyi, the recommendations I've had from sources I trust is to aim for a max of ~20mm rows per partition, so you may want to aim for this if you can.

Steve.

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 2

Steve,

Thanks for this. Your solution sounds good, but unfortunately we're lumbered with the Standard edition, and not Enterprise, so partitioning is not an option.

Also, we haven't hit the heady heights of 2005 yet, so still using 2000.

Pete Brown SSC Eights! Points: 984 More actions · Answer 3

What levels does your time dimension support ?

If the levels are Year,Quarter,Month,Day then a simpler solution might be to generate your time dimension from another table (not the 600 m row fact table.)

There are a specific number of days in a month, a specific number of months in a quarter and a specific number of days in a specific month therefore it may be possible for a table to be generated with all of the available members.

This would mean that the Time dimension would only need to be refreshed periodically and not every time from the fact table (consider how many duplicate time members that table contains!)

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 4

Pete,

Thanks for the tip! At the moment we delve down to the minute level. As this is going to be an archive cube to be used for trend analysis, we shouldn't have to go down that far and the time dimension will be configured accordingly. As such, your suggestion is one option we have considered.

However, the idea behind using multiple cubes and viewing all the data in a virtual cube was so we could have the option of clearing out some of the data that had been processed from the database. Therefore, we could process a quarterly cube and then leave it alone to just sit there with the relevant data in. Though space/capacity isn't a major concern at present, eventually I do want to be able to do trend analysis over a number of years and so require a solution that will allow me to do this in the most efficient way possible.