Joining two cubes 3

[RicoCali]

I have a situation where measure 1 is dimensioned by dimension A, B, C, D. Measure 2 is only dimensioned by B. Therefore I put the measures in there own separate cube. But now I want to join them so I can report them together. How is that accomplish?

 

[MDXer]

you need to create a virtual cube. look in BOL for virtual cube. it is realy simple to do

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

 

[jl162401]

Virtul Cube or MDX

 

[davidagnew37]

I need to do the same thing....where and what is BOL ?

 

[MDXer]

BOL is SQL Books online. you find it in the start menu with your SQL application icons.

Yes you can use MDX to accomplish this task using the lookup cube function. However this can sometimes be very complex MDX to write. By far the easiest method is to create the virtual cube.

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

 

[davidagnew37]

Thanks MDXer,

I have created a virtual cube and still the Dimension filters affect all measures/columns. Can you please give me some direction on how to achieve this? Do I have to use Private Virtual Dimensions for those dimensions I only want to be affected?

Thanks

david

 

[MDXer]

is it possible to explain your situation and goals utilizing the Foodmart cubes. THis way I have some type of refference I can physically look at.

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

 

[davidagnew37]

Unfortunately I cant really reference an example in Foodmart. Hope this helps......

I eventually hope to achieve having three measures in a virtual cube:
"Actual", "Budget" and "Full Year Budget".

The dimensions I have are:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates
- Budget Name

The Problem is that both "Actual" & "Budget" need to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Time/Dates (exclusive to "Actual", "Budget")

BUT "Full Year Budget" needs to be filtered/dimensioned by:
- Account No_ / Name
- Cost Code
- Sub Cost Code
- Budget Name (exclusive to "Full Year Budget")


The problem being the following dimensions:
- Time/Dates (exclusive to "Actual", "Budget")
- Budget Name (exclusive to "Full Year Budget")


I thought the best way forward is to have 2 temp tables where this data is coming from. One for "Actual", "Budget" - as these share the same dimension Filters which I have put into one cube. ....then another cube for "Full Year Budget".

The problem is when I create the virtual Cube and the Dimensions affecting ALL measures....perhaps the underlying problem is when I create the two cubes which eventually create the Virtual Cube - in the way that I use shared and private dimensions.

I have tired creating virtual dimensions also. Is this the right direction to go in?

 

[skitter]

To deal with a budget/actual here is a simple method :

- assuming budget and actual data are in the same fact table but on different rows and scenarios are on a dimension.

1.create 2 views (one for actual, one for budget) filtered by the scenario dimension.

2.create 2 cubes (one for the budget positions, one for the actual positions)

3.enable data at non leaf levels in the account dimension

4. join the 2 cubes into one virtual cubes (dimension to carry forward are the one common to the 2 cubes).

Hope this helps