Overview
In this session, you will identify the dimensions and hierarchies driving the forms we defined. This process is broken down into three steps:
- Create a list of potential dimensions.
- Determine if you can consolidate or remove any potential dimensions you have identified, before creating your final list.
- Define all the members that will populate these dimensions.
Transcript
[transcript]
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 and 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. Each of our filters also needs to be driven by a dimension, so let's add fiscal year, scenario, entity, and brand.
The deflator percent 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 seven 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 SKUs belong 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 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 descriptive names 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 member ID. A member ID 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.
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 parent member label column.
We can derive the dimension members from our P and L report. The hierarchy on the report displays the parent members below 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 and L header to the top of the hierarchy to help organize the accounts.
To define the hierarchy structure, we simply need to add the parent of each member to the parent member label column.
The member name can be the same as the member label.
Dimension members at the lowest level of the hierarchy are called leaves. Any leaf member we want to be inputable needs to have a one in the input column, while all non leaf members can have a zero, as their values will be automatically aggregated from the leaves.
We can leave the annotation field as zero 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, entity, 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 and L report, we also need to add the accounts from the deflator percent 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.
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 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.
[/transcript]