The Aimplan data model
This chapter will give you an introduction to how Aimplan store data from the Aimplan Planning and Reporting visual. That is - how planning, forecasting and other input numbers entered by the end-users are stored within a multi-dimensional space in a planning-fact storage table in the database.
This chapter is not related to tables used for storing master data from the Aimplan Data input table visual.
This picture below shows how data are stored, and is an example of the planning data delivered from Aimplan to Power BI to be used as a table in Power BI (direct query or imported).
(click the below picture to view in full screen)
When creating a fact table that will be used for planning data, the system creates the table with a fixed set of 12 system fields that are mandatory for the system to work.
- RowId - is a autogenerated unique Id for each stored row in this fact table
- RowStatusId - Indicates the status of each row. 0 means that it's a valid row, and in the database view above exposed to Power BI, this will allways be 0
- UpdatedAt - indicates the exact point in time when a user entered the number, the value in the Value column and saved the data base to the database
- UpdatedByUserId - is the user Id or user Key indicating which user edited the record. Can be joined to the DimUser view also exposed from Aimplan to Power BI to understand who has entered the data
- CommitId - is a unique Id of each batch save transaction. Say that a user changes five numbers/values and press save. All these 5 records will get the same CommitId. This CommitId is an increasing number, so if sorting transactions by this field you can see in which order the commits have been performed. This field can be used to incrementally perform data extractions from the table, not needing to load all records to another system every time
- ScenarioKey - is a mandatory system dimension. Every number entered must be stored within a scenario (i.e. Budget 2025 or similar). The Scenarios are maintained in the Aimplan portal and exposed as a dimension table to Power BI named DimScenario.
- DateIntKey - is a mandatory system dimension. Every number entered by the user must be stored in some kind of relation to when in time the value is relevant. Say that you want to store a value for january 2025, this DateIntKey should be 20250101 (if using first date in month), or 20250131 (if using last date in month). This approach to always store planning data on the lowest level day-level, makes it possible to use one single field to store data per year, per quarter, month, week or day, but using a 8 character ISO-date format.
- MeasureKey - is a mandatory system 'dimension'. Aimplan uses an so called 'entity-attribute-value' data model to store data. This means that this fact table only have one single value-field called 'Value'. The MeasureKey describes what each number in the Value field means. I.e. the first row in the picture above shows a value of 200, with a MeasureKey 'M6'. In the Aimplan admin portal you can create and name measures, and in this example M6 means 'Plan Sales Qty'. Measures are exposed to Power BI in the DimMeasure view. Note: this view is not needed to use in the Power BI data model. Instead you should create individual measures in Power BI to reflect each measure you have created in the Aimplan portal. The advantage of this data model is that you can add unlimited number of measures to the model, without making any changes to the underlying storage table. It also makes it possible to store meta-data about each value, such as ValueComment below.
- CurrencyKey - is a non mandatory system dimension. If the Value is a currency amount, it's recommended to here add the currency code such as USD, EUR, SEK etc. If you want to work with multiple currencies you should create your own Currency dimension table in your Power BI model. For measures not using currency, there is a default currency code of 'NON' autogenerated. I.e. if you do not specify a CurrencyKey in the Aimplan Planning and Reporting visual, data will be stored with the currency 'NON'.
- Value - is the cell value the end-user is entering into the planning table. This is a decimal(18,5) field type that can store up to 5 decimals.
- ValueComment - (not implemented yet), will be used to store cell comments related to each Value. I.e. similar to cell comments in Excel.
The above fields are the default generated fields when creating a planning fact table. But you want to store your data also by account, customer, product, employee and other dimensions. In the admin portal where you design your storage table, you can add as many extra dimension key fields or attribute fields as you whish.
In the example picture above, seven extra dimension key fields have been added to store measure planning data in many other dimensions. Each of these fields have a 'not applicable' default value/key such as '¤NA'. This implies that if you for example have a planning form to store salaries per employee per month, you should provide an EmployeeKey, but you do not need to provide a ProductKey, CustomerKey etc instead each salary related row will get a '¤NA' value.
A good practice in data warehouse design and Power BI models is to in each dimension table have such 'not applicable' member that will be shown in end-user reports instead of blank.