New to Virtual Cubes - Need some input please 1

[dean12]

Consider 3 databases with sales information. One for US, one for Europe, one for Middle East. All three databases are identical in design (tables, keys, fields, etc) but of course the data in the tables differs.

Imagine a table called COUNTRY and a table called SALES. So we have basically a list of countries and sales by country. Thus one dimension "Country" and one measure "Sales". We will assume all figures are in the same currency.

In my traditional view of things with a single database, it's a very simple cube and I can see sales by country. Now the users want to combine all three databases to see a single listing of sales individually by country.

It looks to me when building the virtual cube that I select the sales measure 3 times, once from each cube and I get measures like "Sales", "Sales1", "Sales2". I am only able to select the dimension "Country" once.

What I saw from the data display in MS Analysis Services was a display of sales by country for my one dimension and for Sales1 and Sales 2 it just said "All Countries".

So my question is: Is this what virtual cubes are used for or do I need to build a 4th database with all countries combined into a single "Country" table and all sales figures combined into a single "Sales" table?

And if a virtual cube will do the job, how do I get these 3 columns of sales figures into a single column?


Thanks.

 

[johnherman]

The dimension Country must be a Shared Dimension and must be a shared dimension when you build each cube - there is no way to retrofit this.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[MDXer]

John is right that the dimension needs to be shared, but as far as the fact table goes there is no need to build the same cube 3 times and then bring them into a virtual. This is the exact scenario that Cue Partions are meant to solve. Cube partitions allow you to build a single cube from multiple yet identical fact tables. If you move your country to a shared dimension and then use a distinct SK in your fact and dimension tables where say

USA_Sales_Fact has a country key of 1 which maps to USA in your country dim and CANADA_Sales_Fact has a country key of 2 which maps to Canada in your country dim.

Obviously the key numbers can be anything as long as they are distinct by country. If you have this then you can also slice your cubes along the country dimension which will improve query performance against your cubes.

Another benefit of this will be the output the user sees. In a virtual Cube you are going to have 1 sales measure for each country resulting in the following

           SALES 1       SALES 2       SALES 3
           -------       -------       -------
USA         100          0             0
CANADA      0            500           0
MEXICO      0            0             250

By using partitions the ouput would look more like

        SALES
        -----
USA     100
CANADA  500
Mexico  250

Which when clculations or side by side comparisons are happening is much easier to accomplish.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

As (seemingly) always, MDX'er has explored more of the functionality of Olap/Analysis Services that have I. 'nother star for the guru.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[MDXer]

LOL, Thanks. JUst think when SQL 2005 is released we all get to start over, as AS has been revamped. Both for the good and some aspects I don't really like.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck