In Part II of this series we’ll start putting together our application in Kepion. In this session, we’ll create a new application and configure its default settings. Then we’ll build all the Dimensions we outlined in our Architecture Document. And lastly, we’ll create Leveled Hierarchies on our Product and Entity Dimensions.
In Part II of this series we’ll start putting together our application in Kepion. As Dimensions are the basic building blocks of an Application, we’ll begin there.
In this session we’ll create a new application in Kepion and configure its default settings. Then we’ll build all the Dimensions we outlined in our Architecture Document. And lastly, we’ll create Leveled Hierarchies on our Product and Entity Dimensions, to help better organize them.
Let’s begin by opening the System Module in Kepion. This is the area where you can manage your Applications. Let’s click Add. This will generate a new Application Database. Give it the name Kepion Training – Revenue Planning.
Now let’s open the Application in the Modeler. The Modeler is where we can actually build, or model, an Application. In the navigation pane on the right, if we select Application, we can set an image or change the description of our application. In the Settings tab, we can set some defaults, like adding a color template, which will help keep our design consistent throughout the build, or changing the way numbers are formatted and displayed.
Now let’s start on the Dimensions. We can see the three system-defined Dimensions here: Account, Time, and Scenario.
As we mentioned before, Kepion will automatically populate the Time Dimension.
Each Member List here is further organized into a Fiscal and Annual Calendar.
By default, both calendars start from January. The Fiscal Calendar, however, can be configured to start on a different month. For example, we can define Fiscal Year 2020 as starting on October 1st. Then if we go to any of the fiscal year Hierarchies, we can see that the definition has been updated.
Now let’s define the rest of the dimensions we need.
There are four ways to load data into a Dimension. We can enter the members directly through the UI, import from .csv files, write SQL queries to import from other databases, or create SSIS packages to import data from other source systems.
In the database, the tables that store the Dimension data are named D underscore the Dimension Name. Member Lists are named H underscore Dimension Name, Member List Name. For example, D_Account, or H_Time_Months. If we wanted, we could populate the Dimensions by writing directly to these tables.
However, for the Account Dimension, let’s import from a .csv file, so we can make use of the Spreadsheets we created earlier in our Architecture Document. First, we’ll go to the default Account Member List and then export a template. Then we can open the template, copy the data from our architecture document, and paste it into the csv file. Now let’s import that file back into the Account Dimension.
Now we can clearly see the structure of the parent-child Hierarchy, with P&L and Statistical Accounts as two root members.
Next is the scenario Dimension. As this dimension only contains three members, let’s add them directly through the UI. Click Add New, to add three new Dimension members, and then click on the Member Labels to rename them Actual, Plan, and Forecast. Notice that the default MemberName is automatically updated to match. Let’s also un-check the Input option for Actual, as Actuals are generally imported from another system and not input through the UI.
Now that we’ve configured the three default dimensions, let’s add the other two dimensions we’ll need: Product and Entity. To create a new Dimension, select ‘All Dimensions’ and then click ‘Add.’ Name it: ‘Product’.
The Copy From option allows us to copy an existing Dimension. The Dimension Size optimizes performance based on how many Members our Dimension will contain. Checking this will add a default member list to the Dimension. And the security option here we’ll return to in a later session. It’s disabled by default to increase performance.
Let’s click Save.
Under our Dimension, there are three items created by default. The attributes section allows to define and manage the attributes for our Dimension. The All_ Member List here is a special Member List that contains a record of all the Members in the Dimension. However, this list can’t be used to define a Model.
The last item here is the default Member List we created by checking the ‘create default member list’ option. We need at least one Member List here to use our Dimension in a Model.
Let’s start by adding an attribute. In the attribute tab, click add, and then specify the attribute name and data type. We’ll name it Brand, and use the default settings.
Now if we take a look at the Product Member List, we can see the attribute we added. Now we can import the Dimension Members.
I created these csv files in advance, using the same process we used earlier populate the Account Dimension. All of these files are available to download below the video.
This Dimension contains members from two different granularities: Brand and SKU, so let’s create two additional Member Lists to separate them out. We’ll name the first one Brands-- For the Mode, let’s select ‘List’. And then name the second one SKUs.
For the Brands Member List, we’ll do the same thing, but this time only import the Brands.
And likewise, for the SKUs Member List, we’ll import just the SKUs.
Then we’ll follow the same process to create our Entity Dimension. Your final Dimension should look similar to this.
Now that we have all of our Dimensions configured, let’s define some Leveled Hierarchies, to help organize them. Let’s select the Product Dimension first, and then click Define Hierarchy. We can name it ‘Product Hierarchy’.
All we need to do is define the ordering, by selecting the highest level first. So we’ll put Brand on the top level, and then the MemberId, which represents the lowest granularity, the SKUs, on the bottom. Click Save, and we can see the hierarchy we defined: each SKU is sorted by the Brand it belongs to.
Then we can define the Entity Hierarchy in the same way. Your final result should look similar to this.
In this session we created a new Application and configured its default settings. Then we built all the Dimensions we outlined in our Architecture Document. And lastly, we created Leveled Hierarchies on our Product and Entity Dimensions.
In our next session, we’ll use these Dimensions to build the Models we need.