Skip to Content

Following on from Part 1 where we manually created a Model for analytics, we’ll now take a look at how we load data into the Model from an Excel file. This will load data into the Model for the first time but is also the same process for updating data (based on an Excel source).

 

From the Modeler we need to select the relevant model, click the import button and select Import Data From File.

Pic1 - Modeler.png

 

Select the source file, which will then get uploaded and click on Import

Pic2 - Import from file.png

 

The rows and columns then get scanned before you’re presented with the data view. We also see a details panel on the left of the screen which gives an overview of the column mapped in the file along the mapped dimension in the model we are loading. The panel also allows us to:

  • Change the data source names
  • Change the Model we are mapping to too
  • Choose the Category (Actual, Planning, Budget, Forecast or Rolling Forecast) – in this case we are loading actuals only
  • Select the import method (see table below for options)
  • Use the first row of the file as the column headers

 

Import Type

Overview

Replace

Updates the existing data and adds new entries to the target model.

Clean & Replace

Deletes the existing data and adds new entries to the target model.

Append

Keeps the existing data as is and adds new entries to the target model.

 

As we specified which Model the data was to be loaded the system has been able to build a proposal field mapping of the source file field to the Model dimensions. The mapped field have the Model’s dimension name displayed at the top of the column (there is also a progress bar which shows the mapping of the values in the dimension, essentially a master data mapping). Those fields/columns of the source file that aren’t mapped have Not Mapped at the top.

 

Pic3 - Data View.png

 

It’s then a case of mapping all of the source files columns that are not currently mapped and need to be so. I had a few in my case, such as mapping the Order Date field to the Time dimension (which got created automatically when I created the Model). You can select the dimension from a drop down list.

 

Pic4 - Selecting the Dimension.png

 

The measures that haven’t been automatically mapped by the system also need to be done manually. I used the SignedData dimension option in the dropdown. I then made sure in the Order Quantity column in the file mapped to the correct Account Member (i.e. the relevant measure in the Model) – I had to double check all of the source file’s measures columns which had spaces (e.g. Order Quantity) and check the mappings.

 

Pic5 - Measure mapping.png

 

 

After making all of the necessary mappings, you can then click Finish Mapping on the panel and then confirm it inthe dialog box. The system then issues a message detailing the number of successful records loaded. You’re before you’re taken back to the Modeler screen and ready to start using the data.

 

Next up in Part 3 of this blog series we’ll take a look at consuming and visualising our data in a Story board.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply