Overview
In this session, you will complete the final three steps of our architecture design process:
- Determine the models you will need to build.
- Define the business rules you will need to configure.
- Determine the apps you will need to build.
Transcript
[transcript]
In this session, we'll walk through the final three steps of our architecture design process, identifying our models, rules, and apps. We'll begin by looking at the relationships between our forms and dimensions to determine how many models to build. Then we'll analyze the calculations and the forms to define the rules we'll need to configure. And lastly, we'll determine the scope of our application and the number of apps we'll need to build.
The key to distinct models is the member lists that define them. A simple way to determine how many models we'll need is to create a table with the dimension member lists on the rows and forms on the columns. Here, we'll separate out product brands and SKUs, as each model can only use one member list from a dimension.
Next, we need to mark which member lists each form needs access to. For example, account is used on the rows in the first three forms and as a filter in the last form, so we'll mark each form with an X.
This means that the account member list will need to exist in every model. Likewise, time, scenario, and entity are used as filters in every form, so let's mark those as well.
The product member list with brands and SKUs will be used in the P and L report. However, the deflator percent and trade spend forms will only need to input at the brand level, so we'll include just that member list. The base assumption form, on the other hand, only needs access to the SKUs.
Looking at the completed table, we can see that the P and L report and the base assumption form need a different set of member lists than the other two forms, which means they'll each need to belong to their own model.
The deflator percent and trade spend forms, on the other hand, use all the same member lists and could belong to the same model. However, as their purposes are very different, we'll separate them as well to keep the data better organized.
Note that this is just a matter of preference, and the way we make decisions like this will vary based on the requirements of the project.
The next step in the design process is defining rules. There are primarily two different types of rules used in Kepion: SQL and MDX.
SQL rules can be defined either directly using raw SQL scripts or through the data rule designer, which we'll explain in more detail in a later session. As all of the data we're referencing ultimately exists in relational tables in a database, SQL can be used to accomplish nearly any task in Kepion.
MDX, on the other hand, is used only when it's necessary to directly define a calculation on the aggregated data in the OLAP cube. Some common scenarios include variance and percent calculations. As MDX can sometimes impact performance, it's advised to write rules using SQL whenever possible.
Now let's take a look at our reports and determine which calculations we'll need to define rules for.
All of the data in the P and L report is calculated. Gross sales before returns comes from the base assumption form. It's the sum of the revenue of all products.
Returns is then calculated by multiplying this with a value from the deflator percent form. All of the accounts under on sale discounts are calculated in a similar way, while the post invoice accounts are just aggregations from the trade spend form.
The cost of goods sold is the sum of the cost to produce each product times the quantity sold. Then we have some more accounts that are calculated using revenue multiplied by rates from the deflator percent form.
The bolded accounts are hierarchy roll ups. We can skip them when defining rules, as this kind of aggregation happens automatically in Kepion.
In the last row, we have the gross margin percent, which is calculated as the ratio of gross margin to gross sales after returns.
The other three forms are gathering the input that will be used to perform the calculations we identified.
Here we've outlined the final list of calculations in our architecture document. We'll return to this in a later session when it comes time to implement these rules in Kepion.
Now we need to clarify the scope of the application. How many dashboard apps are we going to need to tie every part of the planning process together?
Our sample spreadsheet is a simple example. It only covers a single revenue planning workflow, which we can likely implement using just a single app.
In real projects, we may face more complicated situations. Then we need to decide not only how many dashboard apps to build, but also whether to put all dashboard apps into one application or separate them into different applications.
To make the best decision, we need to consider a few factors, including the scale of each app, the project schedule, whether the apps are related or not, and the customer requirements.
In this session, we used the relationships between our forms and dimensions to determine how many models to build. Then we analyzed the calculations in our forms to determine the rules we'll need to configure. And lastly, we scoped the workflow of our application down to a single dashboard app.
This brings us to the end of the architecture design process. In part two of this series, we'll begin the actual process of building and implementing the dimensions, models, and forms we outlined using Kepion.
[/transcript]