Best Practice to save Rules Values as Stored Values
Posted: Sat Sep 23, 2017 4:37 pm
Using a simple example to explain my question, assume I've got 2 cubes "Reporting" and "Budgeting". I use my Budgeting cube to derive my budget based on some value using rules. I then want to save the rules-derived value from my Budgeting cube in my Reporting cube as a stored value.
What are the best practices around this?
To illustrate further using my simple example, assume ...
The Reporting cube is made up 2 dimensions "Gl Accounts" and "Version" and looks like the following:
_
The Budgeting cube is also made up 2 dimensions "Gl Accounts", "Budget Measures" and look likes the following:
_
To derive the "Budget" value in the Budgeting cube, I use a rule:
What I want to do next is save the resulting derived value from the Budgeting cube to the Reporting Cube as a stored value rather than as rules-derived value. For example, I understand I could create a rule for it in the Reporting cube with a rule similar to the below.
However, I assume that the associated values in the Reporting cube would still be derived each time they are called by users. Therefore, the reason I want to do this because I'm assuming that it is better for performance when dealing with very large cubes.
Is it best to just use the rules using the "DB" function and not worry about performance as the impact is small even in large cubes?
Is it best to create a TI process which dumps the data from one cube to the other?
What if I wanted to use allocations to "split" the "Expenses" from the Budgeting cube between "Expenses" and "Other" in the reporting cube? This is illustrated using rules in the documentation, but ends up being derived.
What are the best practices around this?
To illustrate further using my simple example, assume ...
The Reporting cube is made up 2 dimensions "Gl Accounts" and "Version" and looks like the following:
_
Actuals | Budget | |
Revenues | 1,000 | 0 |
Expenses | -600 | 0 |
Net Income | 400 | 0 |
The Budgeting cube is also made up 2 dimensions "Gl Accounts", "Budget Measures" and look likes the following:
_
Actuals | % Change | Budget | |
Revenues | 1,000 | 10% | 1,100 |
Expenses | -600 | 10% | -660 |
Net Income | 400 | 440 |
To derive the "Budget" value in the Budgeting cube, I use a rule:
Code: Select all
['Budget']=N:['Actuals'] * (1 + ['% Change']);
Code: Select all
['Budget']= DB('Budgeting', !GLAccount, 'Budget');
Is it best to just use the rules using the "DB" function and not worry about performance as the impact is small even in large cubes?
Is it best to create a TI process which dumps the data from one cube to the other?
What if I wanted to use allocations to "split" the "Expenses" from the Budgeting cube between "Expenses" and "Other" in the reporting cube? This is illustrated using rules in the documentation, but ends up being derived.