Importing Transaction Data into a Model from a File
You’ve created your model, the next step is to import your transaction data from a file. From the modeling screen, select the Model you just created and then select the -> ] icon to see the Import Data from File option.
Next, select the datasource file you’d like to import.
Initially, a sample of your data is loaded. All the data preparations and transformations done on the sample data will be applied to the entire dataset.
You can import data into an existing model or a new model. If it’s an existing model, the data integration window shows the initial mappings of dimensions. If the mappings are not correct or need to be changed, then select the column from the right-side details panel. Any quality issues are displayed and you have the option to map your data to the existing model.
Once the dimensions are properly mapped, the next step is the data transformation. You can select the data cell and create transformations such as:
- Replace or delete columns
- Delete rows
- Convert to upper case or lower case
Here, we will use a sample transformation function on the Product dimension data and replace all the # with PRD0006.
First, we click on the Product column, then select the transformation icon.
We will use the suggested smart transformations to replace # with PRD0006 as shown in the figure above.
Go to the Model details pane, which is the cube icon in the same window under the Mapping Options and click on View all options.
Since this is a Planning model, you have to change the Mapping Options. Under Mapping options, choose the Convert value symbol by account type.
This option shows how accounting data is handled in this import. Special data types for accounting data are available, which automatically apply negative signs to values in the database as necessary.
After all columns are mapped and the transformations are applied, you can further validate your data for any quality issues. In our scenario, there are no data quality issues so we can begin importing the data into the model.
Since this is a planning model, we will have different versions of data such as:
- Actual Data
- Forecast Data
- Planning Data
- Budget Data
In this case, we’re loading Actual Data, so under the Category option, select Actual from the left dropdown box and Version name Actual on right side.
If you select a different version from Category dropdown box such as Forecast, then on the right side, type the version name as Category so the data will be loaded correctly into the right version.
If done successfully, you should see this message.
In the Model details pane (cube icon) you can select the type of Import Method option.
- Update —updates the model with data values
- Clean and Replace selected version data— deletes existing data and the new data replaces the old data in the model
- Append—adds the values into the model, even the duplicate records are added into the model
We can select the Update option and click on Finish Mapping button. Data will be loaded into the Model. It will also show the number of records loaded.
If there are any rejected records, you can open the rejected records and do further analysis on why any records were rejected.
We have successfully created a planning model, consisting of Account Dimension, Organizational Dimension, and Generic Dimension, as well as used generated Time and Category Dimensions. After the model is created, we imported the Transaction Data into the Model.