US and Worldwide: +1 (866) 660-7555
Results 1 to 6 of 6

Thread: Combining data across two cubes

  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Default 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.

    Charlie

  2. #2
    Join Date
    Jan 2013
    Posts
    356

    Default

    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).

  3. #3
    Join Date
    Jul 2013
    Posts
    29

    Default

    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.

  4. #4
    Join Date
    Jan 2013
    Posts
    356

    Default

    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.

  5. #5
    Join Date
    Jul 2013
    Posts
    29

    Default

    Schema.jpg

    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.

  6. #6
    Join Date
    Jan 2013
    Posts
    356

    Default

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •