Is it possible to create one bigger cube of smaller ones?
I have two smaller cubes with 3 dimensions each and 2 of them are common for both of the cubes.
I need to create a bigger cube out of the two with their base measures and the two shared dimensions only.
Is this even possible in AWM and if it is how?
Not sure what you are trying to achieve but here is what I could think :-
1. Create a additional cube with 4 dimensions and within this cube you can create two calculated measure. Each calc measure will have fourth dimension value set to ALL using QDR(OLAP DML Expressions).
2. Delete both the cubes and have one cube with 4 dimension and load two measures within one cube(if facts are related to each other).
You can create another cube (lets call it reporting cube or RPT_CUBE) which is dimensioned by all dimensions from all the stored cubes.
This RPT_CUBE will only have calculated measures and no stored measure.
CUBE C1 <D1, D2, D3>
CUBE C2 <D2, D3, D4>
RPT_CUBE <D1, D2, D3, D4>
Then create all your calculations (i.e., calculated measures) inside RPT_CUBE.
The reporting tool or queries will only access measures in RPT_CUBE.
There are lot of benefits to this approach.
If you add more stored cubes (and stored measures) later on then you will just have to either add new calculated measures (or change existing measures) in RPT_CUBE to point to new measures.
Just keep in mind that RPT_CUBE should be dimensioned by all dimensions.
To create calculated measures in RPT_CUBE, you will use OLAP EXPRESSION syntax.
I'll try to explain what we did to achieve our goal.
We manually joined the views of multiple cubes and created a new view to use as a fact table, based on the aggregated values from the smaller ones and used it to create THE cube and it works for us. Thank you for your replies!