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.
Select the source file, which will then get uploaded and click on Import
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
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.
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.
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.
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.
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.