Manage Actual Data

Introduction

In a live system, actual data is important for reporting and data analysis. There are various methods to load actual data into Kepion databases:

• Importing actual data in bulk through .csv files.
• Writing SQL queries to copy actual data from source databases directly into the Fact Tables.
• Creating SSIS packages to pull data from other systems. 

This article explains how to best manage actual data in a Kepion database, and walks through the process of building a typical input Form that displays actual data.  

Data Partition Configuration

We suggest creating a specific partition in each Model to store your actual data, as long as it is not manually entered on the Form. 

In most cases, actual data is static. It receives regular, scheduled updates (e.g., daily, weekly, monthly, etc.)  Therefore, we can set its partition mode to MOLAP to achieve the best performance.  

Configure Input Form 

Here is a typical forecast Form. This Form displays Actual data for Jan-March 2020, and Forecast data for Apr-Dec 2020. The key to constructing a Form like this is configuring the column axis dynamically, so that the Actual and Forecast periods will update automatically over time.

The column axis includes both the Time and Scenario Dimensions. We need to use Standard Variables to dynamically specify the current month, which will, in turn, determine the scopes of Actual and Forecast. In other words, the current month will change throughout the year, and what were once forecasted figures needs to be replaced with actual data.   

Create Standard Variables

We need to create three Standard Variables:

• Current Month: Represents the Time MemberId for the value of the current month.
• Prior Months: Represents all months before current month
• Forward Months: Represents current month and all months after current month

Current Month Definition

 Prior Months Definition 

NULL:[Time].[Fiscal Calendar].&[${Current Month}].Lag(1)

 Forward Months Definition 

[Time].[Fiscal Calendar].&[${Current Month}]:NULL

Create Deployment Script

In a live Application, we will need to update the Current Month Standard Variable.  We can either manually update the value, or we can use a SQL script to automatically update this value based on the current date.

The following is an example of a script which will run every time the application is deployed:

UPDATE [dbo].[ApplicationVariables]
   SET [VariableValue] =  (SELECT CONVERT(varchar(12) , GETDATE(), 112 )) / 100 * 100
WHERE VariableName = N'Current Month'

Form Design

Let's create a new Form, or update an existing Form, to display the Actual and Forecast scenarios. In the example below, we will create a simple Form using the Account, Scenario, and Time Dimensions.

In the Layout tab, we need to add Scenario on the Column and Filter Variable fields. For Time, select Fiscal Calendar for the Column as well. We used the Fiscal Calendar hierarchy to define Prior Months and Forward Months.

Then we need to define the column axis. We can combine the Standard Variables from earlier in the Time Dimension with corresponding scenarios in the Scenario Dimension to achieve the correct Form structure, as shown below. 

Go to the General tab to preview the Form. It should show the months and scenarios correctly.