/
How to Import Data from Excel into Analytics

Pricefx Classic UI is no longer supported. It has been replaced by Pricefx Unity UI.

 

How to Import Data from Excel into Analytics

Owned by Jana Volencová (Deactivated)
Last updated: Dec 28, 2022 by Jan Panoušek
5 min read

The following procedure explains how to manually import data stored in an Excel spread sheet into Analytics:

If necessary, you can add fields later in Data Feed or Data Source; calculated fields can be added also in Datamart.

If the Data Feeds are not empty in the beginning and that data is already in your Datamart, you may want to delete it by running the 'truncate' job in the Data Loads tab.

Create a Data Source

The first step is creating a Data Source. The reason is that you have to create a data table with the correct structure to which you will import the data.

  1. Click the Add Source button to create a new Data Source.

  2. Copy the first 10 rows or so from the Excel file, including the header, into clipboard.

  3. Go back to your new Data Source and click Add Fields.

  4. Paste the rows from the clipboard into the text area and click Apply.

  5. Set each field's Data type if it was not set up correctly from the data sample.

  6. Mark at least one field as 'Key' in the Function column.

  7. Define Measure for Money and Number data types.

  8. Click the Deploy button.

When you deploy a Data Source, a Data Feed and a Data Load are generated automatically.

Upload Data into Data Feed

A Data Feed is created for each deployed Data Source, with an auto-generated name, generic field names (attributeX) and a label, which is the name of the Data Source.

Upload your data into the Data Feed:

  1. Go to Analytics > Data Manager > Data Feeds.

  2. Select the Data Feed generated for your Data Source (it has its name as a label).
     Make sure the labels are there and exactly match the one used in the Data Source.

  3. Switch to the Data tab and click Upload.
    For the file requirements (format, size, delimiters etc.), see Data Feeds.

  4. Browse for your Excel file and click OK.

  5. Wait for the upload to complete. This might take a while depending on the size of your data.

Flush Data to Data Source

Loading into the source is actually loading into the staging table of that source – new records are not visible in the source until the staging table is flushed.

Flush the data from the Data Feed to the Data Source:

  1. Once the data is loaded into the Data Feed, go to Analytics > Data Manager > Data Loads.

  2. Find and open the Flush task (the task whose 'Type' has the value 'Flush') with the label of your Data Source.

  3. (Optional) Go to the Calculation step and select a logic to manipulate the data.

     By clicking the Default Formula button, you will create a new calculation logic containing all fields from the Data Feed as elements. Edit the logic as necessary.

  4. Click the Run Data Load button and wait for the flush to complete. This might take a while depending on the size of your data.

Create a Datamart

Create and deploy a new Datamart:

  1. Go to Analytics > Data Manager > Datamarts.

  2. Click the Add Datamart button.

  3. Specify the name and label and open the newly created Datamart.

  4. Set the Source of the Datamart to be your Data Source.

  5. Select the reporting currencies. All transactions are converted (based on the currency Data Source) to the selected currencies.
    For more details on currencies, see How to Set up Currencies.

  6. Pull in all the necessary fields from the Field Picker (select and click Add to fields).

    • If you have Per-Unit fields in the Data Source, make sure you have a Per Unit Basis field function set.

    • If you are using Date fields, make sure you have a Pricing Date field function set.

    • If you are using Currency fields, make sure you have a Pricing Date field set and the Currency (ccy) data source populated.

    • If you are using UoM fields, make sure you have the UoM (uom) data source populated.

  7. Click the Deploy button.

Load the Data from Data Source to Datamart

If you are not using Date fields, you can omit the next five steps and refresh the Datamart immediately.

If you are using Date fields, do the following:

  1. Go to Analytics > Data Manager > Data Loads.

  2. Find and select the Calendar task with the Calendar Type.

  3. Click the Run Data Load button at the bottom of the table.

  4. Wait for the Calendar task to finish. This will populate the Calendar data source with Year, Quarter, Month and Week data.

Now you can refresh the Datamart:

  1. Go to Analytics > Data Manager > Data Loads.

  2. Expand the node containing your Datamart.

  3. (Optional) Add a Calculation task and select a calculation logic to manipulate the loaded data.

    1. Create a new Data Load by clicking the Add Data Load button and give it the same name as the target Datamart and set the type to Calculation.

    2. In the Target field, select the target Datamart from the drop-down list. In the Target step, you can filter the target's rows so that the logic is only run on certain rows.

    3. In the Source step, you can filter the source's rows so that the logic is only run on certain rows.

    4. Go to the Calculation step and select your logic from the drop-down list.

    5. To test the logic, click the Test Logic button. You will see the results in the Formula Outputs and Results tables.

  4. Select the Refresh task (the task whose 'Type' has the value 'Refresh').

  5. Click the Run Data Load button at the bottom of the table.

  6. Wait for the Refresh task to finish. This might take a while depending on the size of your data and complexity of the setup.

Found an issue in documentation? Write to us.