Author: Ian Britz
In this article we'll create the fundamental building blocks of our Application: Dimensions. Dimensions are simply tables used to organize and filter related data. For example, we could have a Geography Dimension that contains members such Argentina, Australia, Canada, and China, or a Department Dimension with members like Marketing, Human Resources, Sales, and Production.
In our Application, we're going to use 5 Dimensions:
- Account - Financial accounts, such as Product Revenue, Operating Income, and Gross Margin.
- Time - A pre-generated list of years, quarters, months, and days.
- Scenario - Common business scenarios such as Actual, Plan, and Forecast.
- Product - The five different products our business is selling.
- Metric - The different product-related metrics we need to track, such as Quantity, Price, and Cost of Goods.
Let's see how to configure them below.
Step 1. Navigate to the All Dimensions node in the right-hand navigation pane. Every Application comes with an Account, Time, and Scenario Dimension pre-generated.
Step 2. Open the Time Dimension. You'll see that it's already populated with members representing the previous 5 years and next 3 years. The Dimension is further organized by Member Lists, which are a subset of related Members within a single Dimension.
Step 3. Let's expand the Scenario Dimension. This one does not contain pre-generated members, so we'll need to add a few. In the right-hand navigation pane, select the Scenario Member List under the Scenario Dimension. Click the Add New button in the ribbon three times.
Step 4. Double-click on the default Member Labels and change their names to Actual, Plan, and Forecast. Make sure the check-box in the Input column is checked for each. Members without this option selected won't have input capabilities on Forms. The Annotate option is used to allow text input on Dimensions.
Note: Changes made in the Dimension section will be immediately saved in the database.
Step 5. We can also export and import Member Lists from other Applications. In the Account Member List of the Account Dimension, click on the Import button in the ribbon.
Step 6. Import the account_dimension.csv file attached below. Make sure the Decimal symbol is consistent with the file used.
Step 7. Your Account Dimension should now look like the example below. You can also create this Dimension manually, and organize the hierarchy with the Move buttons in the ribbon.
Note: You can define a Parent-Child Hierarchy when the Member List is in Hierarchy mode. If a Parent-Child Hierarchy is not required, ensure the Member List is in List mode for best performance.
Step 8. Now that we've configured the three pre-generated Dimensions, let's add the two additional Dimensions we'll need. Return to the All Dimensions node and click Add. Configure the Product Dimension as in the example below.
Step 9. Within the Product Member List of the Product Dimension, add five products. Name the products however you'd like.
Step 10. Then add the Metric Dimension in the same way, and populate it with the following members. You can import this Member List with the metric_dimension.csv file attached below. These metrics will be used to capture user input in our Assumption Model.
Note: It's now the case that both our Account and Metric Dimensions have a member named Product Revenue. These two members are not the same. One is [Account].[Product Revenue] while the other is [Metric].[Product Revenue]
Now that we have all of our Dimensions configured, in the next section we'll look at combining our Dimensions together into different Models.