This brings up an excellent question - I know virtual cubes can be used to allow the particular cubes to contain a subset of the measures, but can it be used to allow different cubes to contain a subset of dimensions?

i.e., Measures M1, M2, M3, M4 and M5. Dimensions D1, D2, D2, D3, D4, and D5.

I know its easy to have 2 cubes

Cube1: M1, M2 + D1, D2, D3, D4, and D5
Cube2: M3, M4, M5 + D1, D2, D3, D4, and D5

Then, if you bring them together its the same as if you just had everything in one cube.

Now - what I want to do is have:

Cube1: M1, M2 + D1, D2, D3
Cube2: M3, M4, M5 + D3, D4, and D5.

I've found the statement "Virtual cubes must have one dimension in common". So, what if we add a primary key to the fact table, and use that as a 'fake dimension' to link the two? The fake dimension would be a single dimension with the numbers 1 to a million or however many rows are in the fact table. So, then we would have:

Cube1: M1, M2 + DPK, D1, D2, D3
Cube2: M3, M4, M5 + DPK, D3, D4, D5

Thus each 'event' (like a sale at a store) would have a primary key and the dimensions (salesperson, storeid, etc.) and the facts (price, totalprice, etc.) which would all be linked through the primary key which identifies this as a 'single event'.

What do you think?


Also, does anyone know of exactly how Analysis Services deals with virtual cubes - for example - if you have one common dimension from your 2 specific cubes like 'Sales Date', then does it aggregate all sales on that sale date (it has no other info about how to correctly put together the data) - I can't find anywhere where it really says how it decides how to deal with the data. If there are any white papers or better analysis of virtual cubes please let me know.

Thanks!


(ps. An obvious example of where we might want to do this --> we have a very large cube that has 10 measures and 10 dimensions or so relating to price and then 15 more measures and 20 dimensions relating to total cost accounting (which are rarely used -but are important- in the client app) so what I would like to do is have Cube_Price and Cube_Cost and then bring them together through a virtual cube that would be exactly the same as if they were all in a single cube...)