Understand Datamarts for Optimization

As a Pricing Master Data Administrator, you can use the predefined data sources to load and update the master data and create a specific group of data, called Datamarts, for further evaluation and processing.

Open

The following Data Sources are prebuilt in Pricefx: 

• Product – Typically replicates the Product master table both in structure and content.  

• Customer – Typically replicates the Customer master table both in structure and content. 

• UoM – Unit of measure (to convert different units of measure and recalculate values, e.g. kg to gram).  

• Ccy – Currency conversion table to recalculate money values to reporting currencies.  

• Cal – Calendar to create week, month, quarter, and year fields from the date fields.  

1. Go to Analytics > Data Manager > Data Sources from the main menu. 

Open

1. Review what type of the

Data Sources are available in the current setup, you can use the “Add Source” button to create a new data source.  

Note: For now, this will be an overview only step, no actions required. 

1. Open the

Product Data Source and review available fields and columns. Notice each field is defined with specific data type and measurement. Here you can add an individual field or a group of fields using the buttons on the top-right area.

The columns in the data source: 

Deployment – the status of the data and whether it is written in the PA database  

Key – the mapping element, usually Id 

Data Type – type of the data field 

Format – additional definition of the data type 

Label – name of the column that will be displayed in other tables 

Name – name of the data entry used by the system 

Measure – the way how quantitative data should be aggregated 

Dimension – the way how qualitative data (string, text) should be aggregated 

Note: When you need to synchronize the data with the Master Data tables (if there were any updates), the “Sync Meta Data” will be then used. As soon as any changes have been done to the Data Source structure, it should be always deployed to the server by the “Deploy” action. Once the changes are deployed, there will be a database sign in the column “Deployment”.  

Add Field – allows the addition of a new data field into the Data Source. 

Add Fields – allows the addition of more than one new field into the Data Source. 

Sync Meta Data – helps to synchronize the customer/product master to the customer/product Data Source. 

Import/Export – allows the user to import/export a schema by copying/pasting text to/from a text editor (e.g. Notepad) and save it as a plain text. 

Deploy – helps to save and activate the Data Source. As soon as the Data Source is deployed, the Data Feed and Data Load are automatically generated. 

1. To see what type of data is included into the data source, navigate to the “

Data” tab on the top left corner.  This section displays the “actual” data stored in the fields. 

1. In the standard business cases after updating the data source, you will need to perform a data load action, to load the data itself into the Data Source. For that you will need to go to

Analytics > Data Manager > Data Load from the main menu.  

Note: For now, this will be an overview only step, no actions required.

1. In order to perform the data load, you will need to find the relevant data source with type “

internal copy” (for master data tables) or “flush” (for uploaded externally files), click on the Product label for the data loads. 

1. To perform the new data loading, you must click on the “

Run Data Load” button. Click OK in the confirmation message. 

Note: For now, this will be an overview only step, no actions required. 

1. After the data load, you will need to go back to

Data Sources and verify whether the data has been correctly loaded. You will be able to see records loaded in the “# Rows” column. 

1. You can follow up the previous steps and review

Customer and TXStandardData data sources and how are they setup. 

Note: For now, this will be an overview only step, no actions required. 

A Datamart is essentially a way to join the two or more sets of data to allow you to run queries, set prices, create charts, and optimize your pricing across both sets of data.  

Datamart is a Pricefx virtual table that can combine different data sources and it is an important element for variety of the analysis in Pricefx. 

1. Go to Analytics > Data Manager > Datamart from the main menu. 

1. The system displays the available Datamarts to reuse or update. 

Today you need to create your own Datamart for further sales performance evaluation. You will use the sales transactional data as your data source for this exercise. 

To create a new Datamart: 

1. Click on the “Add Datamart” button on the top right corner.

1. The dialogue window will open. Type in the name (StudentX) and label of the Datamart. Click on the “Add” button. 

1. The system will display a green confirmation message on the top of the screen. Once it has been successfully created, review the Datamart by clicking on it. 

1. You will need to choose the main data source base of which Datamart will be built in the Options menu. In your case it is TXStandardData (DS), select it and click on Confirm the button on the confirmation message. 

Label – allows the user to set a Datamart label/name. 

Source – allows the user to choose a Data Source from which the data will be pulled. 

Normalization – you can decide whether you prefer quicker Datamart queries or refreshes. This option allows you to copy more fields to the DM table to speed up queries at the expense of DM refresh performance or vice versa. 

Base Currency – allows the user to set the base currency for this Datamart. 

Base UOM – allows the user to set the base unit of measure for the Datamart. 

1. To combine different sources besides Transaction data you will need to add “Customer Id” and “Product Id” from the field picker to the Datamart by checking the box to those 2 fields in the field picker menu and selecting “Add to fields” button (you can navigate through the existing fields, by scrolling down through the list). 

1. Once the previous action has been accomplished, you will be able to see more fields in the field picker from other Data Sources, e.g. Product and Customer (pages at the bottom are added). Selected fields are added to the Datamar

From the Customer DMDS source you need to pick (check the box) the following fields: Customer Name, Country, Industry, Region, and Customer Group.

From the Product DMDS source: select Product Group. 

From TX DMDS source: select Global List Price, Gross Margin, Cost, Promotion Discount, Invoice Price, Local List Price, Net Price, and Quantity. 

Click on the Add to Fields button after every field selection. 

You can see how the selected fields are displaying on the right side as you add them together

You will add 1 more field to the Datamart by typing a formula to store the margin. Click on the “Add Field” button (top left corner). 

1. Enter the following data and click on the button. 

Data Type: Money 

Name: Margin 

Label: Margin 

Expression: InvoicePrice-Cost 

The new field is added to the Datamart (at the bottom of the list):

Now you are ready to save the new Datamart into the Analytics server. Select “Deploy” on top right corner

In the opened pop-up window, you will see all the fields that will be written into the Datamart. Select “Deploy” button again. Click “OK” in the confirmation message

The system will display a list with all the fields already defined in the Datamart. 

You need to make sure that it has all relevant data rows from other data sources. To assure that go to Analytics > Data Manager > Data Loads from the main

In the list of different data sources, scroll down and look for your latest created Datamart with DM.StudentX (you can use the Label field to search for it). Click on the one with the column Type = Refresh. 

In the left side on the Options area, uncheck the “Incremental” check box, which is used for partial calculation.

Click on the “Run Data Load” button. Click on the OK button on the confirmation message. 

Go back to Data Loads and review Start Date and End Date time stamps. This will indicate whether the calculations are finished. 

Click on the Refresh button until the Data Load is with status “Ready” to be used. 

Now you need to verify whether the data has been loaded into the Datamart. Go back to Datamart and check the column “#Rows”. 

Select the Datamart and verify the data is loaded in the Data tab.