Skip to content

Latest commit

 

History

History
201 lines (130 loc) · 7.32 KB

PowerBiVisualisation.md

File metadata and controls

201 lines (130 loc) · 7.32 KB

PowerBI Visualization

In this section we'll setup some example powerBI reports.

We will be using PowerBI Desktop for this.

Setup & Importing Data

In PowerBI, we first need to connect to our Synapse.

  • Choose Get Dataand select Synapse Analytics (SQL DW)

  • In the next screen fill in the server and database. You can find the server in the Azure Portal as Dedicated SQl Endpoint in the overview blade of your Synapse Workspace. The Database is the SQL server pool you created.

  • Select Import Data

  • Use your Azure credentials to logon or the userid and password used during the Synapse Workspace creation.

  • Select the 3 tables created in the previous steps.

  • Select Transform Data In order for all 3 tables to have the same sales order number, we'll convert the sales order number from string to integer. In the 3 tables select the sales order number column and change the type to Whole Number. The formula for the column will then change to Table.TransformColumnTypes(dbo_SalesOrderItems,{{"SalesOrder", Int64.Type}}).

    • For SalesOrderHeaders, change the SALESDOCUMENT column. The transformation will remove the leading zeros
    • For SalesOrderItems, change the SalesOrder column
    • For Payments, change the SalesOrderNr column

  • Select Close & Apply

Create the Relational Model

In this step we'll model the relationships between the tables. The Relationships are as follows :

SalesOrderHeader 1:n SalesOrderItems

Payment 1:1 SalesOrderHeader

  • Switch to the Modelview

  • From the SalesOrderHeaderstable, select the SALESDOCUMENTfield and drag and drop it on the SalesOrderfield of the SalesOrderItemstable. The relationship defaults to 1:*

You can look at the relationship details by double clicking.

  • In the same way create the relationship between the Paymentsand the SalesOrderHeaders table using the SalesOrderNrand SALESDOCUMENTfield.

  • The end results looks as follows :

You can now start building the reports.

Data Visualisation

To start the visualization, switch to the Report view.

Some example Reports are given beneath. Feel free to experiment.

Sales per Date and CustomerGroup

  • Select a Stacked Column Chart.
  • Use the SalesOrderHeaders.CREATIONDATE hierarchy as X-axis
  • Use SalesOrderHeaders.TOTALNETAMOUNTas Y-axis
  • Use SalesOrderHeaders.CUSTOMERGROUPas Legend

Note: You can drill down from Year > Quarter > Month due to the date hierarchy.

Sales per Region and CustomerGroup

  • Select Map.
  • Use SalesOrderHeaders.CITYNAME as Location
  • Use SalesOrderHeaders.CUSTOMERGROUP as Legend
  • Use SalesOrderHeaders.TOTALNETAMOUNT as Bubble size

Note: when you select a CustomerGroup and Quarter in the Sales Report, the Map report will automatically update and only show this data.

Payments per Date and CustomerGroup

  • Select a Stacked Column Chart
  • Use Payments.PaymentDate hierarchy as X-axis
  • Use Payments.PaymentValue as Y-axis
  • Use SalesOrderHeaders.CUSTOMERGROUP as Legend

The CustomerGroup is retrieved via the 1:1 relationship between the SalesOrderHeadersand Payments table.

Note : the Payments report is not identical to the Sales report. Payment of a Sales Order is typically later then the data on which the Sales Order was issued.

Sales Per CustomerGroup and MaterialGroup

  • Select a 'Stacked Bar Chart'
  • Use SalesOrderHeaders.CUSTOMERGROUPas X-axis
  • Use SalesOrderItems.NetAmountas Y-axis
  • Use SalesOrderItems.MaterialGroupas Legend

Payment Offset per CustomerGroup

With this report we'll show the average number of days by which each customergroup pays his SalesOrders. Afterwards we can compare this with the outcome of our Machine Learning Model. For this we need to join the SalesOrderHeaders and the Payment data to calculate the number of days between the billing date and the payment date.

Note : In the ML part you created a similar view in Synapse. This section explains how you can create a 'view' locally in PowerBI.

Merge SalesOrderHeaders and Payments

  • Under Home select Transform data
  • Select the SalesOrderHeaderstable
  • Select Merge Queries > Merge Queries as New

  • Define the merge with the Payments table
    • In SalesOrderHeadersselect the SALESDOCUMENTcolumn
    • In Payments select the SalesOrderNrcolumn
    • Select Inner Join

  • Rename the merged table to SalesOrderPayments

  • In the SalesOrderPaymentstable select column Payments. Expand this column and select the fields PaymentNr, PaymentDate, PaymentValue, Currency

  • Select Apply under Close & Apply

Calculate Payment Offset

We now need to calculate the difference between the Billing date and the actual payment date.

  • Add a new Custom Column to the SalesOrderPayments table

  • Name the column Offset
  • Use the following formula
Duration.Days([Payments.PaymentDate]-[BILLINGDOCUMENTDATE])
  • Change the data type to Whole Number
  • Use Close & Apply from the Home tab to switch to the data view

Average Offset Report

  • Swith to the reporting view
  • Select a Stacked Column chart
  • Use SalesOrderPayments.CUSTOMERGROUP as X-axis
  • Use SalesOrderPayments.Offset as Y-axis
  • Select Average instead of the default sum

(Optional) Boxplot

If you'd like a more detailed view on the payment offset then you can use a 'Box Plot'. This gives you an idea of the variance on the offset. For this you have to import a Box and Whisker chart visualization. In the Visualizations view, press the 3 dots and select Get more visuals.

Search for Box and Whisker chart and press Add.

You can now use the chart in your visuals

  • Use SalesOrderPayments.CUSTOMERGROUP as Category
  • Use SalesOrderPayments.Offset as Sampling
  • Use Average of Offset as Value

From this diagram you can see that:

  • CustomerGroup1 pays within 70 days +/- 10 days
  • CustomerGroup2 pays within 30days +/- 5 days
  • Other customergroups pay after 10 days

This should correspond to the outcome of ML Model.

Continue with the next step