Use case: Move data between cubes (Model Manager)

This use case describes how to use Model Manager to pull data from one cube into another. You can also accomplish this using Process Manager.

The business problem

Organizations often choose to report their financials (actual or budget) at the General Ledger level while performing their budgeting at a lower level of detail (by sales person, by product, by customer). Typically this data resides in separate models, making it difficult to consolidate data, to report the full budgetary picture, and to do an actual-versus-plan comparison.

 Example:  You are budgeting at the sales person and product level for one GL account in a Sales cube, while also budgeting expenses at a higher, more "rolled up" level in the Finance cube. The result is budget data by each sales person and by each product, in the Sales cube, and expense budget data by company in the Finance cube.

The solution

Link the two cubes, then link accounts from one cube to the other, using the account's Calculation Method. The data updates occur automatically.

The benefits

The approach described in this use case has the following advantages:

  • You get a consolidated as well as a detailed view of your budget data, in two different cubes.
  • You can accurately forecast using either the consolidated budget or the detailed budget.
  • You can compare actual data to plan data at both the consolidated and the detailed level.

How it's done

  1. Make sure that these prerequisites exist:
    • The two cubes belong to the same data connection.
    • Dimensions shared between the cubes have identical names.
  2. In Model Manager, select the cube that you want to copy data into.
  3. Select the Account dimension.

  4. For each member of Account that requires data, in the Calculation Definition drawer, from Calculation method, select Get data.

  5. From Source cube, select the cube that contains the account you want to retrieve data from.

  6. When prompted, select the cube that you want to copy data from.

     Note:   Any shared dimensions are automatically selected, in order to ensure a 1:1 transfer of data. For example, if departments are shared, pulling data from an account/department combination in the source cube causes that data to transfer to the matching account/department combination in the target cube.

  7. For any dimensions that are not common or shared, to choose the specific member from Source Cube Members, open Selector:

  8. Save.

From now on, when the specified account is referenced, it will always pull data from the source cube. There is no need to run a process—this feature works just like a Standard Formula.