In this session, we'll identify the Dimensions and Hierarchies that will drive the Forms we defined. We'll first create a list of Potential Dimensions; then determine if we can consolidate or remove any of the Potential Dimensions we identify, before creating our final list; and lastly, we’ll define all the Members that will populate these Dimensions.
In this session, we’ll learn how to identify the Dimensions and Hierarchies that drive the Forms we defined. Remember that a Dimension is just a way to group together related data.
We’ll do this by analyzing each of our Forms and creating a list of Potential Dimensions. Then we’ll decide if we can remove or combine any of the Potential Dimensions we identify, before creating our final list. And lastly, we’ll define all the Members that will populate these Dimensions.
Let’s take a look at our Forms now.
The rows in the P&L Form are all different accounts, so let’s add Account to our list of Potential Dimensions. The columns contain the Months of the Fiscal Year, so we can add that as well. And each of our Filters also need to be driven by a Dimension, so let’s add on Fiscal Year, Scenario, Entity, and Brand.
The Deflator % Form also uses accounts on the rows, months on the columns, and fiscal year, scenario, entity, and brand for the filters, so we don’t need to add anything to our list here.
The Dimensions in the Trade Spend Form are the same as the previous ones.
And finally, the Base Assumption Form only differs by having SKUs on the rows, so let’s add one more item to our list.
Now let’s take a look at what we have so far. These are the 7 Potential Dimensions we derived from the Forms.
Next, we should check if any of these Dimensions are related. If so, we could consider combining them together for efficiency. Since Months and Fiscal Year are both time concepts, let’s combine them into a Time Dimension. The Brands and SKUs we know are related, as a SKU belongs to a brand. Therefore, they can belong to the same Dimension. We’ll call it Product.
As none of the others could be easily combined, these are the five Dimensions that we’ll use in this application.
Now we need to define all the members within each Dimension. This is a sample of the information we need to define a Dimension Member in Kepion. It begins with a Member Label column. Member Labels are used as unique identifiers within Kepion, and ideally, they’re not changed once they’ve been set. It’s recommended to choose a descriptive name for Member Labels, so that the members can be easily identified.
Next, there’s a Member Name Column, which is a friendly display name for your Dimension Member. The name here will not impact functionality, other than how the Dimension Member is displayed within Kepion.
Then we have an input and an annotate column, whose values are either true or false. These determine whether a Dimension Member is inputable, and whether or not that input can be text values.
In addition, all Dimension Members are automatically assigned a MemberID. A MemberID is a numeric key used internally for references.
If you want to add more information than this to your Dimension Members, you can create additional columns called “Attributes,” and define them however you’d like.
Kepion applications come with three system-defined Dimensions: Account, Time, and Scenario.
Kepion automatically generates members for the Time Dimension. It contains Member Lists at four different levels of granularity: Years, Quarters, Months, and Days.
Member Lists are a way to further organize the members within a Dimension. They can be defined as either a flat list, or a parent-child hierarchy.
Let’s start populating our Dimensions and Member Lists. We’ll begin with Account.
The Account Dimension is used to define a general ledger. In addition, Account is a parent-child Hierarchy so it also has a ParentMemberLabel column. We can derive the Dimension members from our P&L Report. The hierarchy on the report displays the Parent Members on the bottom of the Child Members, so let’s move them back to the top in our Member List.
The indent is there just to make the Hierarchy more clear. I’ve also added a P&L Header to the top of the Hierarchy to help organize the accounts. To define the Hierarchy Structure, we just need to add the parent of each member to the ParentMemberLabel column.
The MemberName can be the same as the MemberLabel.
Dimension Members at the lowest level of the hierarchy are called “Leaves.”
Any leaf member that we want to be inputable needs to have a 1 in the input column, while all non-leaf members can have a 0, as their values will be automatically aggregated from the leaves. We can leave the Annotation field as 0, because none of the members need to allow text input.
The Account Dimension is special, as it also comes with an Account Type attribute. Kepion has a large number of account types to choose from, such as Income, Expense, Asset, Rate, Equity and more. All of them can be categorized as either Debits, Credits, or Neither. This category will decide the account roll-up operators. For example, Debits will automatically roll-up into other Debits; Credits will roll-up into other Credits; And Debits will be subtracted from Credits as they roll-up together.
In practice, we only need to assign the right account type to each account member; then when we populate the leaf members, the aggregation will happen automatically. Let’s add Account Types to our Member List here.
In addition to the accounts from the P&L report, we also need to add the Accounts from the Deflator % and Base Assumption Forms. However, the accounts from these Forms aren’t part of our General Ledger, and their values don’t need to roll-up. We call these types of accounts: Statistical Accounts.
Let’s create a new Header in our Account Hierarchy and add the rest of the accounts under there. As their values don’t aggregate, we don’t need to define an Account Type for any of these members.
The Scenario dimension is relatively simple to define. We’ll add three basic scenarios: actual, plan, and forecast.
Now let’s look at the Product Dimension. In this Dimension, we’ll include both Brands and SKUs.
Since we know a Brand maps to a particular group of SKUs we should define a Hierarchy here, to help keep the Dimension Members organized.
Rather than building a Parent-Child Hierarchy as we did with the Account Dimension, the general practice in Kepion is to build a Leveled Hierarchy as they’re more efficient in roll-up and easier to maintain.
The structure of a Leveled Hierarchy is determined by the attributes of the members, rather than a parent-child relationship.
To build this Hierarchy, we’ll need to include an attribute called Brand to categorize the Dimension Members.
Likewise, we can define the Entity Dimension in a similar manner, this time, categorizing the members by the region they belong to.
In this session, we examined each of our Forms and analyzed the types of data they were driven by. Then we used this information to create the list of Dimensions we needed. Lastly, we defined the Members that populated those Dimensions.
In the next session we’ll look at the last three steps of designing the Architecture of the Application: identifying the Models, Rules, and Apps.