Author: Ian Britz
In the last article of our Introduction to Modeler series, all that's left to do is define the Data Rule to calculate the information in our Net Income Form. Luckily, most of the calculations in the Form are just simple aggregations. Just like how the All member in our Quantity Form automatically aggregates all the data in each row and column, the Account Dimension automatically aggregates information up the hierarchy.
This means that if we add our product revenue, product cost, and operating expense information to the leaf members of the Account Dimension, the Parent Members will automatically aggregate the data, all the way up the hierarchy. Furthermore, each member of the Account Dimension has an Account Type attribute that determines whether it's an Income, Expense, or more, and will add or subtract the data appropriately, based on its type.
Note: Every member needs an Account Type attribute selected for the aggregation to work. The only attribute that doesn't aggregate is Header.
Step 1. Let's create a new Data Rule, and name it appropriately. Select the Type as VIEW again. For this Rule we'll need to make the same calculations as in the previous Rule, but this time target the Account Dimension, instead of the Metric Dimension. We'll also need to combine their results with the information from the Expense Model.
Step 2. This time, we'll need data from two different sources-- the Assumption Model, and the Expense Model. Instead of pulling the information in the Assumption Model directly from the Fact Table again and then pivoting the data a second time, we can save a step and actually just link to the pivoted data from the previous Rule. Select Link from the Data Source, and choose Calculate Revenue and Cost.Pivot.
Step 3. For the operating expense data, we can just pull directly from the Fact Table. Select Expense - (Writeback) as the Source.
Step 4. Let's calculate the data the same way as in the previous Rule, but this time we need to target members of the Account Dimension, instead of the Metric Dimension, for the results.
Note: The Product Revenue here is [Account].[Product Revenue] not [Metric].[Product Revenue]
Step 5. Then unpivot the data along the Account Dimension. If we set the Columns correctly in the previous step, your Rule should look like the example below.
Step 6. Now that we have all the data we need, let's combine it into one table. Select Union from the Transform options. Choose Expense - Writeback as the Source and union it with the table we previously unpivoted.
Step 7. Now publish the result to our Reporting Model.
Step 8. After deploying the Application again, we should see our Net Income Form completely populated. Note that even though we only supplied data to the Product Revenue, Cost of Goods Sold, and four children of the Total Operating Expenses members, all of the other members automatically aggregated the correct data.
Step 9. Unfortunately, it looks like with the sample data we provided for our company, we can see that we're actually losing a few hundred thousand dollars a year! Let's try playing with the data on the input Forms to find out how much we'll need to sell to fix our bottom line.
Now that you understand the fundamentals of using the Modeler and setting up an Application, you can start building solutions of your own. Check out the Sample Apps section for inspiration, or the Video Tutorials section for more instruction. There's also a collection of useful articles here on more advanced Modeler features.
If you have any questions or concerns, please submit a ticket, or email us at email@example.com.