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.  We can import actual data in bulk from .csv files.  We can write SQL queries to copy actual data from source databases directly into the Fact Tables.  We can also create SSIS packages to bring in data from other systems.  This article introduces the best practice of managing actual data in a Kepion database, and walks through the process of building a typical input form that displays actual data.  

Data Partition Configurationon 

We suggest creating a specific partition in each model to store actual data as long as it is not manually entered from user input form.  

In most cases, actual data is static.  It gets updated on a scheduled basis (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 prior months of selected year, and Forecast data for forward months.  The key to constructing this form 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 Dimension and Scenario Dimension.  We need to make use of Standard Variables to specify the Actual and Forecast scope. The scope is dynamically based on the current month, which means we'll need to define a Standard Variable to represent that, first.

1. 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: represent 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

2. Create Deployment Script

In a live application, we will need to update current month standard variable.  We can either manually update it, or we can use a SQL script to automatically update this value based on the actual 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'

3. Form Design

Now let's create a new Form, or update an existing Form, to configure our planning form. In the example below, we've created a simple Form with just the Account, Scenario, and Time Dimensions.

In the LAYOUT tab, we need to add Scenario as a Filter Variable.   On Time dimension, we choose the Fiscal Calendar Hierarchy because this is the hierarchy we used 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. 

Now let’s preview the Form, and it should show the months and scenarios correctly.