Combining data across two cubes
I have two tables. Each table has their own cube defined in my schema. However, I want to define some calculated metrics based on values from the other table. For example, I want to total up the revenue in one cube, and divide that by a measure in the other cube. Do I have to create a Virtual Cube to do that, or can I simply add a calculated measure that refers to things in another cube? I don't want to slice along the same dimensions. I just want to sum up all members in the other cube.
Yes, a Virtual Cube is the way to do that. You can't create a calculated member that references a measure that's not a part of the current cube. (Analysis Services 2000 had the LookupCube() function to look up a value from a different cube, but even back then Virtual cubes were almost always preferred).
Hi, I m trying to make a new measure in virtual cube with a table present in the parent cube (like we put conditions in measureExpression as [case when condition then value else 0 end]). In my App i have to add dynamic measures to the cube. Is the virtual cube the only way, then how or any better option to incorporate dynamic measures. Can we use Dyanmic Schema Processor to change the existing MeasureExpression values and also add new measures to the exisiting cube.
Can you give a little more background on your use case? It would be good to see if there are alternatives. Trying to dynamically insert new MeasureExpressions is a pretty heavy hammer-- it invalidates the cache and will force a reload of the schema. Typically if there are dynamic calculations the best way to accomplish them is to use calculated members defined in your queries.
HI, Thanks for the reply. I have this schema structure. Now, i want to filter based on country and sales. Something like
Select Measures.[Something] on Axis(0),
Filter (Crossjoin(Crossjoin(customer.members, store.members), county.[US]), Measures.[Somthing] > values ) on Axis (1)
From [Virtual Cube]
where (Time.From : Time. To )
This is just mapped my schema to the foodmart schema. But in my case, it the user adds a new rule then, i've to include that measure in schema. The Measure filter valuse may change dynamically (like case when fact_table.col > val then fact_table.col else 0 end). This values may change dynamically and also a new measure might be added (like case when fact_table.col value in a range then 1 else 0).
Can you suggest any better way to do this.
Thanks in Advance.
I'm still not clear on your scenario, but it is sounding like a Dynamic Schema Processor may be appropriate. That should give you the flexibility to add and modify expressions as needed.
Tags for this Thread