SYSPRO (Unified Data Model example)
Overview
This article provides a step-by-step guide for deploying the SYSPRO Unified Data Model (UDM) solution. This includes the steps necessary to go through a typical deployment a solution, as well as a set of instructions that are curated specifically for the UDM solution.
Solution deployment
Select New resource > Model and choose SYSPRO.
Click DEPLOY.
Click Next on the Connect screen.
Enter in the relevant server information and click Next on the Create Model screen.
Click Next on the Configure Calendars screen.
Applying the Unified Layers
Unified Layers (ULs) are resources that provide an abstraction layer for pipelines. They standardize the details of transformed pipelines, providing a set of common functional outputs that are reusable across data models and reporting tools.
The ULs that are included in the UDM solution gives the data modeler the opportunity to transform data in order to match the functional requirement of the layer. The definitions of each layer and their function can be found in the following document: ‘Unified Layers – Layer definitions’. It is important for data modelers to familiarize themselves with these definitions, as the layers act as a contract between ZAP and a data modeler. Editing a provided Unified Layer may negatively affect reports or other pipelines that use it. Therefore, the goal is to use the provided unified layers as an end to work towards through transformation of data.
Once familiar with a unified layer, a data modeler can apply it to a pipeline, and work towards transforming the data to match the functional requirements of each of the columns within the layer. Let’s go through a worked example of applying the ‘Sales invoice lines’ unified layer. Before moving on, please ensure you are familiar with the definitions of the ‘Sales invoice line’ unified layer in the ‘Unified Layers – Layer definitions’ document.
Note
When applying a Unified Layer to a data model pipeline, consider the following:
Only one Unified Layer can be applied to a pipeline.
A Unified Layer can only be applied to one pipeline. A Unified Layer can be reused by creating a copy of the Unified Layer resource in the RESOURCE EXPLORER.
Database details in the Pipeline pane are constrained by Unified Layer.
Click on the ‘Worked Example – Sales Invoice lines’ data source to open it.
Select the checkboxes next to both pipelines, then click the ‘Add Pipelines’ button.
Once added, click the Save and Close button.
Back on the model screen, you should have two new pipelines from the spreadsheet data source.
Click on the ‘ERP Sales Line Records’ pipeline, and analyze the columns and data.
This has sales invoice data, and therefore, it is likely that the ‘Sales invoice lines’ unified layer should be used with this pipeline. In the right-hand pane of the pipeline, under the ‘Unified Layer’ section, apply the ‘Sales invoices lines’ unified layer.
You will see that columns are automatically mapped based on their names. It is important to review these columns to ensure they are functionally the same as the layer column they are being mapped to (by looking at the definition and description of the column in the ‘Unified Layers – Layer definitions’ document).
In this case, we can assume that the automatically mapped columns are functionally correct. However, we still need to map some columns, as can be seen by the warning messages:
Let’s map the remaining columns as best we can.
Map ‘Amount excl tax’ to the ‘Invoice line amount’ column.
Map ‘Qty’ to ‘Invoiced Qty’.
Map ‘Item code’ to ‘Item No.’.
Map ‘COGS’ to ‘Cost of sales amount’.
If raw data is not available for a particular UL column, but it can be derived from existing source columns, we can create a calculation in a previous step. For example, let us now create a calculation to map to the ‘Gross profit amount’ UL column:
Create a new Basic step and name it ‘Gross Profit calc’
Click the ‘+ Calculation’ button and name the calculation ‘Gross Profit’.
Copy and paste the following formula into the SQL expression, and click Apply: [Amount excl tax] - [COGS]
Now go to the Unified Layer and map the new ‘Gross Profit’ calculation to the ‘Gross profit amount’ unified layer column:
Save and Close the pipeline.
This would be the approach necessary to map any ‘key’ columns in the layer which are used for warehouse relationships, which is necessary for Power BI and Tableau analytics. For example, the ‘Item key’ column in the layer could be mapped with a calculation that uses the following code: [Item code]+'_'+[Company]
This column would have to be mapped on both Primary and Foreign key tables. For more information on key columns, please see the ‘Unified Layers – Layer definitions’ document.
We have a ‘Budget amount’ column in our UL template, but our sales budget figures are in a different table – the ‘ERP Sales Forecast records’ table. We will have to find a way to bring in the budget figures into the Sales line table. In this case, we will choose to do a union.
Go into the ‘ERP Sales Forecast records’ table.
Ensure all columns are selected from the Columns drop-down.
Update the data type of the Month and Year columns to be text.
Add a new Basic Step called ‘Budget date calc’.
Create a new date calculation called ‘Budget date’ with the following code, and click Apply: convert(date,CONVERT(varchar(10),([Year]+'0'+[Month]+'01'),101)).
Save and close the pipeline.
Reopen the ‘ERP Sales Line Records’ pipeline, and create a new Union step, named ‘Union Budgets.
Now let’s map the relevant columns:
Map Budget date to Invoice date.
Create a new column for Sales forecast amount
Go to the Unified Layer step and map to the layer:
Map ‘Sales forecast amount’ to ‘Budget amount’.
If you expand the preview, you can see what the data looks like.
Save and close the pipeline and process the model.