Combine 2 tables in 1 cube 3

[cthanner]

Hi there,

I would like to combine 2 tables out of my data source (currently I'm experimenting with the Foodmart-example, sales_fact_1997 and sales_fact_1998 tables) into one cube.

I could make the join in Access with union all for example, but I was thinking, that there must some way in Analysis Services as well, like for example using MDX (with which I'm unfortunately not really experienced).

Can someone help me out with a more elegant way to combine 2 tables, or else tell me whether it's usual to make such a join in Access and not in Analysis Services.

Greetings,
Chris

 

[johnherman]

You can create two cubes and join them as one virtual cube in Analysis Services. You must have shared dimensions to do 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's method is one way. However in the case of the Sales_Fact_1997 and Sales_Fact_1998 tables these are better used in partitions. You can add a partition to you cube that points to a seperate but identical fact source. The advantages to partitions are

1) Less cubes to maintain.
2) Processing: you only need reprocess the current partition
3) Partition Slice. When creating the partition you can define the the dimensionality that the partition represents which optimizes queries.
4) Aggregations aggregations can be different from partition to partition optimizing query times.
5) With a bit of dso programming you can paralell process you partitions for shorter build times.


Now as to the partition slice the way it works is this

Scenario 1:
you have a cube with 1 partition that has all sales data 1997 and 1998 if you query for jan of 1997 it has to query the entire partition to get results. If the dataset is large enough this can be time consuming.

Scenario 2:
You have a cube with 2 partitions one with 1997 and one with 1998 data but no partition slice defined. The query must scan both partitions if you are again querying for Jan 1997.

Scenaio 3:
1 Cube 2 partitions with appropriate slicing applied. You again Query for Jan 1997 only now the cube has a pointer that says all 1997 Data in in the single partition that is smaller than the partition in scenario 1 so the engine only queries that partition.


The method you use to join Fact data partitions vs. Virtual cube really depends on the structure of the data. Same data just different source I would use partitons. Related data different source I would use virtual cubes. The exception being a Distinct count aggregate which should always exist in a seperate cube that is then joined to other cubes by use of a virtual cube.

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

 

[jrzesz]

I like th info MDXer, but I have another question for you. I am trying to filter records that in SQL would take a left join or correlated subquery. I really don't want to have to make multiple cubes to do this, the tables arent the same structure, what would you advise?

Thanks, Jrzesz

 

[johnherman]

Can you create a view which would be consistent across all of the fact tables? If not, you probably wouldn't be able to combine the cubes, anyway.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright