TM1 Forum

Hi there,

I have two cubes having the same structure. Is it possible to reference these two cube in the same active form? I am building a variance report to show the variance between these two cubes.

The two cubes are using the same dimensions except the version dimension.

Thanks in advance.

tm1green wrote:Hi there,

I have two cubes having the same structure. Is it possible to reference these two cube in the same active form? I am building a variance report to show the variance between these two cubes.

The two cubes are using the same dimensions except the version dimension.

Thanks in advance.

In answer to your question yes, of course. The whole point of cell-based architecture and queries and the formula based approach in Perspectives is that data from multiple cubes can easily be mixed in one report. For this to work in an active form the cubes don't need to have the same structure, only to share the relevant structures. Note only one slice will really be an active form as such, for references to subsequent slices or cubes you will just use a standard VIEW formula and point the DBRW references to the VIEW and not to the TM1RptView. Why don't you actually try it, it's not that complicated.

However as to the matter of 2 cubes having the same structure but different data content and you want to do a variance report in Excel?? This sounds like some very poor design. There should never be a cube for "actuals" and a cube for "budget" (for example ...) with the same structure. Such a case should be handled in one cube with a version dimension to segment the data types. Then the OLAP database can do the lifting and automate the variance reporting, not to mention it will be easier and better for the users to have a single data source. I kinda suspect you may have a similar situation.

It is possible but only one of the cubes can reference the TM1RPTVIEW formula. The other should reference a simple VIEW formula as if it was a slice.
zero suppression will work only for the cube that references the tm1rptview

Thanks lotsaram and mvaspal for the replies. Do you guys happen to have some examples which show an active form sourcing from multiple cubes?

Back to lotsaram's question on the cube design. You are right on having budget and actuals in one cube. We are doing exactly that. The two cubes that I was referring to, one is the live cube while the other is a repository cube (snapshot at certain point of time) which contains values only. The variance report will show what has been changed since the last snaphot.

Thank you.

Guys,

I just tried your suggestion and managed to get it to work but Excel is showing formula inconsistency error as the formulas used in the area are not consistent due to different cube reference. Overall it works.

Thank you.