Overview
In this session, you will create a new application and configure its default settings. You will then build all the planned dimensions and create leveled hierarchies with your product and entity dimensions.
Transcript
[transcript]
In part two 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 and 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 left, 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 twenty five as starting on October first. 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_ followed by the dimension name. Member lists are named H_ followed by the dimension name and member list name. For example, D_Account or H_Time_Months.
If we want to, 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 and 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 member name is automatically updated to match. Let's also uncheck 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. The security option here we'll return to in a later session. It's disabled by default to increase performance.
Let's click add.
Under our dimension, there are three items created by default. The attribute section allows us 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.
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 to 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. 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 add hierarchy.
We'll define it on our product member list. 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 member ID, which represents the lowest granularity, the SKUs.
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 outlined.
[/transcript]