Tag: DataScience

How to track data lineage with SQL Server Graph Tables – Part 5 how to visualize the data using R

In my previous article I explained how you can query the graph tables in SQL Server to pull out pertinent information regarding the lineage of various data fields in your business intelligence system. In this article I will show you how you can visualize the results using R inside of SQL Server.

I completed these steps using a docker image of SQL Server 2019. I was curious to test the performance of using a SQL Server docker image and I thought I would kill two birds with one stone and finish the series using docker. There were a couple of unexpected extra steps that I had to figure out to get this working properly inside of a docker container which I will call out and explain along the way.

Install R packages

We will use a package called igraph to visualize the data. Before you can use this package, you will need to download and install it.

To properly add packages to SQL Server2019 or later you must use sqlmlutils.

Docker image issue #1

In order to use Machine Learning Services in the SQL Server docker image you must accept the End-User License Agreement associated with both SQL Server and SQL Server Machine Learning Service. I also mounted a volume so that the images generated by the R script are easily accessible from outside the container. To do this start the docker image with the following command.

docker run -d -e MSSQL_PID=Developer -e ACCEPT_EULA=Y -e ACCEPT_EULA_ML=Y -e MSSQL_SA_PASSWORD=YourPassword -v C:\Temp:/var/opt/mssql/images -p 1433:1433 mssql-server-mlservices

Next you will need to install sqlmlutils into the docker image. The instructions provided by Microsoft, which can be found here Install R packages with sqlmlutils – SQL Server Machine Learning Services | Microsoft Docs do not work for a docker image.

Docker image issue #2

The quick and dirty way to install sqlmlutils to the docker image is to bash into it. This is considered hacky and should be avoided in a production environment. For production I would create a dockerfile so that package dependencies are part of the docker image definition and are installed on build.

To bash into the container running SQL Server using the following command.

docker exec -it <your container id> "bash"

To find out the ID of the container running SQL Server run the following command

docker ps --filter "ancestor=mssql-server-mlservices"

Hopefully you will have only one container built off of the mssql-server-mlservices image.

Once you have connected into the container you will need to install some additional software into the Linux Ubuntu running the container.

Docker image issue #3

Before you can install new R packages into the container you will need to update and install some additional dependences.

sudo apt-get update


sudo apt-get install build-essential gdb


sudo apt-get install gfortran

Once you have installed these dependencies you can add packages to the container, for simplicity I have added the igraph package directly using the following command.

sudo su - -c "R -e \"install.packages('igraph', repos='http://cran.rstudio.com/')\""

In production I would have installed the sqlmlutils and then used this package to install igraph, however to do this I would have to first install unixodbc-dev, then ODBC, then manually install SQLMLUTILS from the zip file.

If you have a problem installing igraph you most likely need to the ~/.R/Makevars file using the following command

cd ~

cat > Makevars

C=gcc-5

CXX=g++

CXX1X = g++-5

CFLAGS = -std=c99 -Wall -pedantic

Press CTRL-D to save and exit the file

Enable External Scripts

Now that your environment is setup you need to enable external scripts to run in your SQL Server instance. To do this connect into your SQL Server instance using SQL Server Management Studio or Azure Data Studio and then run the following command.

exec
sp_configure
'external scripts enabled', 1;
RECONFIGURE;

Generate image using R

Now that we are all setup we can use R to generate the following visuals. This images will be stored as PNG files on your C:\Temp folder (the one mounted in your container if using a container)

System Data Flow

The system level image was generated with the following code.

--System level
EXECUTE
sp_execute_external_script
@language =
N'R',
@script =
N'
    require(igraph)
    g <- graph.data.frame(graphdf)
    V(g)$label.cex <- 2
    png(filename = "/var/opt/mssql/images/SystemDataFlow.png", height = 800, width = 1500, res = 100);
    plot(g, vertex.label.family = "sans", vertex.size = 5)
    dev.off()',
@input_data_1 =
N'
 SELECT 
        src.SystemName AS Source,
        trgt.SystemName AS Target
 FROM 
         [DataLineage].[dbo].[DataEntity] src,
 [DataLineage].[dbo].[DataFlowsTo] df,
 [DataLineage].[dbo].[DataEntity] trgt
 WHERE MATCH(src-(df)->trgt);',
@input_data_1_name =
N'graphdf'
GO

Database Level

The database level image was generated with the following code.

--Database level
EXECUTE
sp_execute_external_script
@language =
N'R',
@script =
N'
    require(igraph)
    g <- graph.data.frame(graphdf)
    V(g)$label.cex <- 2
    png(filename = "/var/opt/mssql/images/DatabaseDataFlow.png", height = 2400, width = 3000, res = 50);
    plot(g, vertex.label.family = "sans", vertex.size = 5)
    dev.off()',
@input_data_1 =
N'
 SELECT 
        src.DatabaseName AS Source,
        trgt.DatabaseName AS Target
 FROM 
         [DataLineage].[dbo].[DataEntity] src,
 [DataLineage].[dbo].[DataFlowsTo] df,
 [DataLineage].[dbo].[DataEntity] trgt
 WHERE MATCH(src-(df)->trgt);',
@input_data_1_name =
N'graphdf'
GO

Table Level

The table level image was generated with the following code.

--Table level
EXECUTE
sp_execute_external_script
@language =
N'R',
@script =
N'
    require(igraph)
    g <- graph.data.frame(graphdf)
    V(g)$label.cex <- 2
    png(filename = "/var/opt/mssql/images/TableDataFlow.png", height = 800, width = 1500, res = 100);
    plot(g, vertex.label.family = "sans", vertex.size = 5)
    dev.off()',

@input_data_1 =
N'
 SELECT 
        src.TableName AS Source,
        trgt.TableName AS Target
 FROM 
         [DataLineage].[dbo].[DataEntity] src,
 [DataLineage].[dbo].[DataFlowsTo] df,
 [DataLineage].[dbo].[DataEntity] trgt
 WHERE MATCH(src-(df)->trgt);',

@input_data_1_name =
N'graphdf'
GO

Column Level

The column level image was generated with the following code.

--Column level
EXECUTE
sp_execute_external_script
@language =
N'R',
@script =
N'
    require(igraph)
    g <- graph.data.frame(graphdf)
    V(g)$label.cex <- 2
    png(filename = "/var/opt/mssql/images/ColumnDataFlow.png", height = 2400, width = 3000, res = 50);
    plot(g, vertex.label.family = "sans", vertex.size = 5)
    dev.off()',

@input_data_1 =
N'
 SELECT 
        CONCAT(src.TableName, ''.'', src.ColumnName) AS Source,
        CONCAT(trgt.TableName, ''.'', trgt.ColumnName) AS Target
 FROM 
         [DataLineage].[dbo].[DataEntity] src,
 [DataLineage].[dbo].[DataFlowsTo] df,
 [DataLineage].[dbo].[DataEntity] trgt
 WHERE MATCH(src-(df)->trgt);',

@input_data_1_name =
N'graphdf'
GO

Security

With SQL Server 2019 there is a known issue when writing files to disk using an external script.

igraph png not working on SQL Server 2019 (microsoft.com)

I was able to bypass this issue by writing my PNG files into the docker container in the folder /var/opt/mssql/images/ this folder is then mounted to C:\Temp which makes them accessible from my local host. I mounted the folder when running the docker image using the -v argument

docker run -d -e MSSQL_PID=Developer -e ACCEPT_EULA=Y -e ACCEPT_EULA_ML=Y -e MSSQL_SA_PASSWORD=YourPassword -v C:\Temp:/var/opt/mssql/images -p 1433:1433 mssql-server-mlservices

Summary

I was able to visualize the data by generating PNG files using R and the igraph packages which is very handy for generating documentation that is easily consumable by anyone. The basic concepts explained in this article can be extended and made dynamic so that a user can pick what level to see lineage at as well as enhanced so that a hierarchy can be shown with all levels shown based on user input.

In the next article I will cover how to visualize data in the graph tables using PowerBI.

Hopefully you have found this to be another practical post.

Until next time.

Anthony

How to track data lineage with SQL Server Graph Tables – Part 3 Populate Graph Tables

Featured

In this 3rd article I will show you how to use the procedures that we created in the previous post to populate the graph tables with useful data lineage information.

Data Flow

We will simulate the flow of data from two source systems (Finance and HR) into a data warehouse/business intelligence system as illustrated in the diagram below.

Staging

Most modern BI systems include a staging area, also sometimes referred to as a landing area. Data is collected from various disparate systems and loaded into the staging database with very minimal transformations, typically only data type conversions. The goal is to quickly grab the data from the different source systems and load it into the staging area for further processing so that any performance impact to the source systems is minimized.

To capture the data lineage for this first hop from source system to staging we can use our procedure as follows. 

-- JDE to Staging
EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'Finance',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'JDE',
		@TargetDatabase = 'Staging',
		
		--Table Level
		@SourceTable = 'F0411',
		@TargetTable = 'F0411',

		--Column Level
		@SourceColumnList = 'RPKCO, RPDOC, RPDCT, RPSFX, RPSFXE, RPDCTA, RPAN8, RPPYE, RPSNTO, RPDIVJ',
		@TargetColumnList = 'RPKCO, RPDOC, RPDCT, RPSFX, RPSFXE, RPDCTA, RPAN8, RPPYE, RPSNTO, RPDIVJ',

		@ProcessName = Load F0411';
GO



-- HR to Staging 
EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'HR',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'Payroll',
		@TargetDatabase = 'Staging',
		
		--Table Level
		@SourceTable = 'EMP',
		@TargetTable = 'EMP',

		--Column Level
		@SourceColumnList = 'EMPID, HIREDT, JOBTITLE',
		@TargetColumnList = 'EMPID, HIREDT, JOBTITLE',

		@ProcessName = 'Load EMP';
GO

As you can see from the code above it is very easy to map an input table to an output table using the procedures we created in the previous steps.

EDW

Next, we will capture the data lineage from Staging to the Enterprise Data Warehouse (EDW) 

-- Staging to EDW for Finance tables

EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'Business Intelligence System',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'Staging',
		@TargetDatabase = 'EDW',
		
		--Table Level
		@SourceTable = 'F0411',
		@TargetTable = 'Fact Accounts Payable',

		--Column Level
		@SourceColumnList = 'RPDOC, RPDCT, RPSFX, RPSFXE, RPDCTA, RPAN8, RPPYE, RPSNTO, RPDIVJ, RPDIVJ',
		@TargetColumnList = 'Document, Document Type, Document Pay Item, Pay Item Extension Number, Adjusting Document Type, Address Number, Payee Address Number, 	Approvers Address Number, Invoice Date - Julian, Invoice Date - Gregorian',

		@ProcessName = 'Load Fact Accounts Payable';
GO



-- Staging to EDW for HR tables
EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'Business Intelligence System',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'Staging',
		@TargetDatabase = 'EDW',
		
		--Table Level
		@SourceTable = 'EMP',
		@TargetTable = 'Dim Employee',

		--Column Level
		@SourceColumnList = 'EMPID, HIREDT, HIREDT, JOBTITLE',
		@TargetColumnList = 'Employee Number, Hire Date, Tenure, Job Title',

		@ProcessName = 'Load Dim Employee';
GO

If you have a keen eye you might have noticed that we are mapping some of the fields twice. The field RPDIVJ in the JDE table F0411 is mapped to both Invoice Date – Julian and Invoice Date – Gregorian. The field HIREDT in the HR EMP table is mapped to both the Hire Date and the Tenure columns. In both scenarios this implies some transformation logic between these columns.

Accounts Payable Model

The final step in our example is the process that loads the SQL Server Analysis Services (SSAS) model. 

-- EDW to SSAS Finance
EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'Business Intelligence System',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'EDW',
		@TargetDatabase = 'SSAS Accounts Payable Model',
		
		--Table Level
		@SourceTable = 'Fact Accounts Payable',
		@TargetTable = 'Fact Accounts Payable',

		--Column Level
		@SourceColumnList = 'Document, Document Type, Document Pay Item, Pay Item Extension Number, Adjusting Document Type, Address Number,  Invoice Date - Gregorian',
		@TargetColumnList = 'Document, Document Type, Document Pay Item, Pay Item Extension Number, Adjusting Document Type, Address Number,  Invoice Date',

		@ProcessName = 'Process SSAS Accounts Payable Model'



-- EDW to SSAS HR
EXEC	[dbo].[Create_Data_Lineage]
		
		--System level
		@SourceSystem = 'Business Intelligence System',
		@TargetSystem = 'Business Intelligence System',

		--Database level 
		@SourceDatabase = 'EDW',
		@TargetDatabase = 'SSAS Accounts Payable Model',
		
		--Table Level
		@SourceTable = 'Dim Employee',
		@TargetTable = 'Dim Employee',

		--Column Level
		@SourceColumnList = 'Employee Number, Hire Date, Tenure,  Job Title',
		@TargetColumnList = 'Employee Number, Hire Date, Tenure,  Job Title',

		@ProcessName = 'Process SSAS Accounts Payable Model'


GO

As you can see in the code above the Invoice Date – Gregorian from the Finance system is used to populate the Invoice Date and the Invoice Date – Julian did not make it into the SSAS cube.

Summary

The database procedures make it really easy to capture data lineage, they could even be enhanced to accept and parse JSON or XML inputs so that data lineage could be dropped in and maintained using a web application….can someone say Power App

In the next article I will cover how to query the graph tables as well as visualize the data in order to answer data lineage questions.

Hopefully you have found this to be another practical post.

Until next time.

Anthony

How to track data lineage with SQL Server Graph Tables – Part 2 Create Database Procedures

This is my second post in a series which explains how to use SQL Server Graph Tables to track data lineage.

In my previous post I covered creating the node and edges tables required to store data lineage. In this post I will show you how to create database procedures in order to simplify the process of populating the graph tables. These procedures are deliberately basic so that they are easy to understand but they are also easily extensible and can serve as the starting point for a more sophisticated solution.

Overview

Before diving into the code it is always good to have a conceptual understanding of the process. The flowchart below explains the logic of these procedures.

As you can see in the diagram above, we check if the source or target is new and then based on the condition create the appropriate nodes and edges. In order to avoid duplicating code we will create a procedure for creating the nodes and the edges and then call theses sub processes from a parent process that controls the logic flow.

Create Nodes Procedure

First, we will create a procedure to populate our node table. As you recall from the previous post our node table is called [dbo].[DataEntity].

DROP PROCEDURE IF EXISTS dbo.Create_Nodes
GO

CREATE PROCEDURE dbo.Create_Nodes

    @ColumnList VARCHAR(MAX),
    @Table VARCHAR(MAX),
    @Database VARCHAR(MAX),
    @System VARCHAR(MAX)

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NodeIDs TABLE (ID NVARCHAR(1000));

    --Insert data ino Node table and keep Source IDS so that we can populate the Edge table
    INSERT INTO [dbo].[DataEntity]
    OUTPUT INSERTED.$NODE_ID AS TOID INTO @NodeIDs(ID)
    SELECT
        value AS [ColumnName],
        @Table AS [TableName],
        @Database AS [DatabaseName],
        @System AS [SystemName],
        GETDATE() AS [CreatedDt]

    FROM  
        STRING_SPLIT(@ColumnList, ',');

    SELECT ID FROM @NodeIDs;    
    RETURN 
END

Now that we have a procedure to create our nodes, we will create another procedure to create the edges.

Create Edges Procedure

We need to pass a list of procedure a list of node ids to insert into the [dbo].[DataFlowsTo] table so we will create a user defined type to store and pass this information. 

/* Create a table type in order to pass a list of Node IDs to this procedure*/
DROP TYPE IF EXISTS dbo.NodeIDTableType
CREATE TYPE dbo.NodeIDTableType  AS TABLE  ( ID NVARCHAR(1000) );

GO

After the user defined table type is created we can create the procedure to populate the edge table.

DROP PROCEDURE IF EXISTS dbo.Create_Edges
GO

CREATE PROCEDURE dbo.Create_Edges

    @Source  NodeIDTableType READONLY,
    @Target  NodeIDTableType READONLY,
    @Process VARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   --Get the FROM and TO node ids into a common table exppression and populate the edge table in batch.
   */
   
    WITH CTE (TOID, FROMID) AS
    (
        SELECT 
            A.FROMID,
            B.TOID
        FROM
            (SELECT ID AS FROMID, ROW_NUMBER() OVER(ORDER BY ID ASC) as RowNum FROM @Source) as A
        JOIN 
            (SELECT ID AS TOID, ROW_NUMBER() OVER(ORDER BY ID ASC) as RowNum FROM @Target) as B
        ON A.RowNum = B.RowNum
    )
    
    --Insert Lineage data into DataFlowsTo Edge table
     INSERT INTO [dbo].[DataFlowsTo] ($from_id, $to_id, ProcessName)
     SELECT CTE.TOID, CTE.FROMID, @Process FROM CTE;

    
END

In case you are wondering I used a common table expression (CTE) so that the edge records could be created in bulk rather than one at a time.

Create Data Lineage Procedure

This is the final procedure which checks to see if the source or target is new and calls the procedures above as required. The conditional logic check is premised on the assumption a the combination of system name, database name, table name and column name is unique. 

DROP PROCEDURE IF EXISTS dbo.Create_Data_Lineage
GO
USE [DataLineage]
GO


CREATE PROCEDURE [dbo].[Create_Data_Lineage] 

    --Source data
    @SourceSystem VARCHAR(MAX),
    @SourceDatabase VARCHAR(MAX),
    @SourceTable VARCHAR(MAX),
    @SourceColumnList VARCHAR(MAX),

    --Target data
    @TargetSystem VARCHAR(MAX),
    @TargetDatabase VARCHAR(MAX),
    @TargetTable VARCHAR(MAX),
    @TargetColumnList VARCHAR(MAX),

    @ProcessName VARCHAR(MAX)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        
    --DECLARE @SourceIDs TABLE (ID NVARCHAR(1000));
    --DECLARE @TargetIDs TABLE (ID NVARCHAR(1000));
    
    DECLARE @SourceIDs NodeIDTableType;
    DECLARE @TargetIDs NodeIDTableType;

    
    --Prepare the list of fields by removing any spaces or tabs between the comma seperated list of source and target columns
    SET @SourceColumnList = REPLACE(REPLACE(@SourceColumnList,' ', ''), char(9), '');
    SET @TargetColumnList = REPLACE(REPLACE(@TargetColumnList,' ', ''), char(9), '');

    --Check for existing sources , if found use those id's otherwise create new nodes and use new IDs
    INSERT INTO @SourceIDs
    SELECT 
        $NODE_ID
    FROM 
        [dbo].[DataEntity] DE
    INNER JOIN 
            (SELECT
                value AS [ColumnName],
                @SourceTable AS [TableName],
                @SourceDatabase AS [DatabaseName],
                @SourceSystem AS [SystemName]
            FROM  
                STRING_SPLIT(@SourceColumnList, ',')
            ) SRC
    ON
        DE.[ColumnName] = SRC.[ColumnName]
    AND 
        DE.[TableName] = SRC.[TableName]
    AND
        DE.[DatabaseName] = SRC.[DatabaseName]
    AND
        DE.[SystemName] = SRC.[SystemName];

    --Check for existing  targets, if found use those id's otherwise create new nodes and use new IDs
    INSERT INTO @TargetIDs
    SELECT 
        $NODE_ID
    FROM 
        [dbo].[DataEntity] DE
    INNER JOIN 
            (SELECT
                value AS [ColumnName],
                @TargetTable AS [TableName],
                @TargetDatabase AS [DatabaseName],
                @TargetSystem AS [SystemName]
            FROM  
                STRING_SPLIT(@TargetColumnList, ',')
            ) TRGT
    ON
        DE.[ColumnName] = TRGT.[ColumnName]
    AND 
        DE.[TableName] = TRGT.[TableName]
    AND
        DE.[DatabaseName] = TRGT.[DatabaseName]
    AND
        DE.[SystemName] = TRGT.[SystemName];

    IF (NOT EXISTS (SELECT 1 FROM @SourceIDs)) AND (NOT EXISTS (SELECT 1 FROM @TargetIDs))
        BEGIN

            --Create source nodes
            INSERT @SourceIDs
            EXEC  dbo.CREATE_NODES 
                    @System = @SourceSystem,
                    @Database = @SourceDatabase,
                    @Table = @SourceTable,
                    @ColumnList = @SourceColumnList;
        
            --Create target nodes
            INSERT @TargetIDs
            EXEC  dbo.CREATE_NODES 
                    @System = @TargetSystem,
                    @Database = @TargetDatabase,
                    @Table = @TargetTable,
                    @ColumnList = @TargetColumnList;
        
        
            --Create edges between source and target
            EXEC dbo.Create_Edges @Source = @SourceIDs, @Target = @TargetIDs, @Process = @ProcessName;
        END
    ELSE IF (EXISTS (SELECT 1 FROM @SourceIDs)) AND (NOT EXISTS (SELECT 1 FROM @TargetIDs))
        BEGIN
        
            --create target nodes
            INSERT @TargetIDs
            EXEC  dbo.CREATE_NODES 
                    @System = @TargetSystem,
                    @Database = @TargetDatabase,
                    @Table = @TargetTable,
                    @ColumnList = @TargetColumnList;

            --Create edges between source and target
            EXEC dbo.Create_Edges @Source = @SourceIDs, @Target = @TargetIDs, @Process = @ProcessName;
        END
    ELSE IF (NOT EXISTS (SELECT 1 FROM @SourceIDs)) AND (EXISTS (SELECT 1 FROM @TargetIDs))
        BEGIN
        
            --create source nodes
            INSERT @SourceIDs
            EXEC  dbo.CREATE_NODES 
                    @System = @SourceSystem,
                    @Database = @SourceDatabase,
                    @Table = @SourceTable,
                    @ColumnList = @SourceColumnList;

            --Create edges between source and target
            EXEC dbo.Create_Edges @Source = @SourceIDs, @Target = @TargetIDs, @Process = @ProcessName
        END
    ELSE 
        --No new nodes required
        --Create edges between source and target
        EXEC dbo.Create_Edges @Source = @SourceIDs, @Target = @TargetIDs, @Process = @ProcessName
    

END
GO

Summary

In this article we have created the database procedures to simplify the process of creating the necessary node and edge records to capture data lineage between source and target fields.

In the next article I will cover how to call these procedures and query the graph tables to answer data lineage questions.

Hopefully you have found this to be another practical post.

Until next time.

Anthony

Instant insights, automation and action – Part 4 Register Power BI in Azure Active Directory

This is the fourth post in a series of articles in which I explain how to integrate Power BI, Power Apps, Flow, Azure Machine Learning and Dynamics 365 to rapidly build a functioning system which allows users to analyze, insert, automate and action data.

In the previous article I covered building the Power BI Report.

In this article I will cover how to enable data to be pushed into Power BI use Flow. This is a fast no code solution.


This is a one-time setup that is required in order to use the Power BI connector in MS Flow. If you do not do this step you will see an error screen in MS Flow like the screen clip below.


Prerequisites

In order to complete this tutorial, you will need permission to register applications in your Azure Active Directory tenant.


For more information on the Azure AD Tenant you can click the following link.

https://docs.microsoft.com/en-us/power-bi/developer/create-an-azure-active-directory-tenant

Power BI Development Center

Log onto the Power BI Development Center and enable API features and get the key to register the app in Azure.

Go to the following URL and sign in.

https://dev.powerbi.com/apps


Enter in a meaningful name for your app, I called mine AnthonysPowerBIApp but you can call yours whatever you would like. Choose Native for the Application Type and select Read all datasets and Read and write all datasets for the API Access


Click on Register. A screen like the one below should pop up. Be sure to copy down the Application ID as this is needed to register the application in Azure.


Azure Portal

Next log onto the azure portal using the following URL https://portal.azure.com/#home

Once in the portal admin page navigate to the Azure Active Directory menu blade


Next click on App registrations and select the app that we created using the Power BI Development Center.


You can change settings in the app if you whish to tailor it be clicking on Properties.

Now that the Power BI App has been registered in Azure Active Directory you can use it in various Microsoft cloud services such as Flow.


As you can see in the image above, I no longer get a permission error and I am able to select the workspace, dataset and table.


In the next post we will build out the flow so that data is passed from the Power App to an Azure Machine Learning experiment for scoring and then into the Power BI API Enabled Dataset for real-time analytics.

Hopefully you have found this to be another practical post.

Until next time

Anthony

References

Here is the official documentation from Microsoft on how to register Power BI to push data into it using REST API calls.

https://docs.microsoft.com/en-us/power-bi/developer/overview-of-power-bi-rest-api


Instant insights, automation and action – Part 3 Create the Power BI Report

This is the third post in a series of articles in which I explain how to integrate Power BI, Power Apps, Flow, Azure Machine Learning and Dynamics 365 to rapidly build a functioning system which allows users to analyze, insert, automate and action data.

In the previous article I covered building the Power App. In this article I will cover the Power BI report.

We will build out this system in the following order; Power App, Azure Machine Learning, Power BI and then last MS Flow to connect the components. Before you can begin this tutorial there are some required prerequisites.

Prerequisites

  • Power Apps
  • MS Flow
  • Power BI Pro or Premium
  • Access to Azure Active Directory to register Power BI App
  • Dynamics 365

Create the API Enabled Dataset

Log onto Power BI and create a new app workspace called Customer Segmentation. This step is not required however if you are like me you create a lot of different content so it’s a good habitat to get into so that you can better manage your work.


In case you are wondering the screen clip above is using the new App Workspace experience. Next, we will create a new streaming data set.

On the splash page for the app click Skip at the bottom right corner of the page.


Now select +Create > Streaming dataset.


Select API and click next.


Next create the WholeSaleCustomer dataset.

It will have the following field names and data types

Field Name Data Type
Customer Name Text
Channel Number
Region Number
Fresh Number
Milk Number
Grocery Number
Frozen Number
Detergents_Paper Number
Delicassen Number
Category Number


Click the Create button to generate the dataset.

Next, we will leverage the generated PowerShell script to create some test records in our newly formed dataset. Click on PowerShell and copy the code into Notepad.


We will create three test records by running the PowerShell code below. Modify the code you coped into Notepad so that it looks simlar to the code below. Before you can run this you will need to replace <Your Key> with the key displayed in your Power BI service.

$endpoint = "https://api.powerbi.com/beta/8c17d9d4-2652-4573-8a9c-d5dde0750715/datasets/13b74183-5eb2-480b-ba11-c0af0ecbdd26/rows?
key=<Your Key>

$payload = @{
"Customer Name" ="Test1"
"Channel" =1
"Region" =1
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =0
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
$payload = @{
"Customer Name" ="Test2"
"Channel" =2
"Region" =2
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =1
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
$payload = @{
"Customer Name" ="Test3"
"Channel" =3
"Region" =3
"Fresh" =98.6
"Milk" =98.6
"Grocery" =98.6
"Frozen" =98.6
"Detergents_Paper" =98.6
"Delicassen" =98.6
"Category" =2
}


Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

To do this launch PowerShell in Administrator mode and copy and paste the code into the PowerShell desktop app.


The data set now has three records in it and you can start to use it in Power BI. To do this go to the dataset and click the three dots beside the name of the dataset. This will open a new report with a blank canvas. Add a table and drop all of the fields from the data set into the visual.


As you may notice from the screen shot above the fields Fresh, Milk, Grocery, Frozen, Detergents_Paper and Delicassen are not formatted as currency but should be. Unfortunately, API enabled data sets only have three data types Text, Number and Date and no formatting options so we cannot specify that these fields are currency fields.

Thankfully we can leverage the Report level measures for live connections to Analysis Services tabular models & Power BI service datasets feature that was released in May 2017 to add new measures with the proper currency data type defined.
 Continue reading “Instant insights, automation and action – Part 3 Create the Power BI Report” →

Instant insights, automation and action – Part 2 Create Azure Machine Learning Experiment

This is the second post in a series of articles in which I explain how to integrate Power BI, Power Apps, Flow, Azure Machine Learning and Dynamics 365 to rapidly build a functioning system which allows users to analyze, insert, automate and action data.

In the previous article I covered building the Power App. In this article I will cover the Azure Machine Learning Studio Experiment.

We will build out this system in the following order; Power App, Azure Machine Learning, Power BI and then last MS Flow to connect the components. Before you can begin this tutorial there are some required prerequisites.

Prerequisites

  • Power Apps
  • MS Flow
  • Power BI Pro or Premium
  • Access to Azure Active Directory to register Power BI App
  • Dynamics 365

Build the Azure Machine Learning Experiment

The Azure Machine Learning Studio platform is a powerful cloud service from Microsoft that allows data scientists to rapidly build and deploy machine learning experiments. For the purpose of brevity, we will leverage an existing template from the Azure AI Gallery. The Azure AI Gallery is a great resource for creating and learning about Machine Learning experiments in the Microsoft platform.

Weehyong Tok from Microsoft created an experiment that segments customers based on the dataset Wholesale customers Data Set from UCI Machine Learning Repository which is perfect for our purposes.

You can find the experiment here https://gallery.azure.ai/Experiment/Customer-Segmentation-of-Wholesale-Customers-3 .

Open the experiment in the Azure Machine Learning Studio by clicking on Open in Studio. Be sure to log in using the same account that you used to build the Power App.

This will launch the Azure Machine Learning Studio platform and create an experiment for you based on Weehyong Tok template. You may notice that the experiment has to be updated, click ok.

This is because the Assign to Clusters module has been deprecated and replaced by a new module called Assign Data to Clusters. Thankfully the upgrade takes care of the necessary changes and we can use the experiment as is with out having to modify it.

Click the Run button at the bottom of the page.

Once the experiment has finished running click on the output of the Assign to Cluster module and select Visualize from the drop down menu.

As you can see in the image the data is grouped into clusters.

This experiment uses the K-Means clustering algorithm to assign the data points to groups. As you can see in the image below it currently uses 2 centroids which essentially means that each row will be assigned to 1 of 2 groups based on the distance of the data points in the row to the centroid.

Modify the experiment to determine the optimum number of centroids

Now you may wonder if this is the optimal number of clusters or not. Thankfully we can use an elbow chart to help determine the optimal number of centroids. To do this we will add a Python module to drop some code into our experiment.

Search for the Execute Python Script and drop it onto the canvas of the experiment. Connect the first output (the one on the left) of the Split Data module to the first input of the Execute Python Script module. Your experiment should look as follows.

Now you will need to add the following code to the Execute Python Script module. Replace the generated with the code below.

Python Code

# The script MUST contain a function named azureml_main
# which is the entry point for this module.

# imports up here can be used to 
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from math import sin, cos, sqrt, atan2
from sklearn.cluster import KMeans
from sklearn import metrics
from scipy.spatial.distance import cdist

# The entry point function can contain up to two input arguments:
#   Param<dataframe1>: a pandas.DataFrame
#   Param<dataframe2>: a pandas.DataFrame
def azureml_main(dataframe1 = None, dataframe2 = None):

# Execution logic goes here
#print('Input pandas.DataFrame #1:\r\n\r\n{0}'.format(dataframe1)) #We don't need this, we just want the visual.

colors = ['b','g','r']
markers = ['o','v','s']

distortions = []
centroids = range(1, 10)
for i in centroids:
kmeanModel = KMeans(n_clusters=i).fit(dataframe1)
kmeanModel.fit(dataframe1)
distortions.append(sum(np.min(cdist(dataframe1, kmeanModel.cluster_centers_, 'euclidean'),axis=1))/dataframe1.shape[0])

plt.plot(centroids, distortions, 'bx-')
plt.xlabel('Number of centroids')
plt.ylabel('Distortions')
plt.title('Elbow chart showing the optimal number of centroids')
plt.show()

plt.savefig("elbow.png") #To see the chart in Azure Machine Learning Studio we need to save the image as a png.

# If a zip file is connected to the third input port is connected,
# it is unzipped under ".\Script Bundle". This directory is added
# to sys.path. Therefore, if your zip file contains a Python file
# mymodule.py you can import it using:
# import mymodule

# Return value must be of a sequence of pandas.DataFrame
return dataframe1,

Run the experiment and click on the second output, Python device (Dataset), of the Python Script module and select visualize. You should see something like the image below.

The optimal number of centroids is at the “elbow” of the chart above which looks to be about 5. Based on this insight we will update the algorithm and change the number of centroids to 5. We will also increase the number of iterations to 500 since we have more centroids.

Run the experiment and click on the output of the Assign to Cluster module and select Visualize from the drop down menu. The output should look like the image below.

Next, we will convert this experiment into a Predictive Web Service. At the bottom of the screen select Predictive Web Service > Predictive Web Service [Recommended]

Once the predictive experiment has been setup, we are going to modify it slightly so that it only returns the Assignment field. To do this we need to drop in the Select Columns in Dataset module and place it between the Assign to Clusters module and the Web service output.

Launch the column selector and enter in the Assignments column as the only value to get passed through to the web service output.

Run the experiment and Deploy Web Service.

This concludes the second part of this series. Next, we will build the API enabled dataset in Power BI which will store the data that we will use in the Power BI Reports and Dashboards. Since the dataset is API enabled we can push data into it using Flow.

Hopefully you have found this to be another practical post.

Until next time

Anthony

References

@Python Programming has a good site for understanding the Python code to plot an elbow chart.

https://pythonprogramminglanguage.com/kmeans-elbow-method/ 

 

Instant insights, automation and action – Part 1 Create Power App

In this series of blog posts, I will explain how you can integrate Power BI, Power Apps, Flow, Azure Machine Learning and Dynamics 365 to rapidly build a functioning system which allows users to analyze, insert, automate and action data. The tutorial will be premised on analyzing whole sale customer purchases using the Wholesale customers Data Set from UCI Machine Learning Repository.

The conceptual architecture of the system is illustrated below.

We will build out this system in the following order; Power App, Azure Machine Learning, Power BI and then last MS Flow to connect the components. Before you can begin this tutorial there are some required prerequisites.

Prerequisites

  • Power Apps
  • MS Flow
  • Power BI Pro or Premium
  • Access to Azure Active Directory to register Power BI App
  • Dynamics 365

Build the Power App

First we will build a very simple Power App. The app will allow users to enter in new purchase orders directly in a Power BI dashboard by leveraging the Power Apps custom Visual for Power BI.

Our app will have fields to capture the following data elements:

  • Customer Name
  • Channel
  • Region
  • Amount spent on FRESH produce
  • Amount spent on MILK produce
  • Amount spent on GROCERY produce
  • Amount spent on FROZEN product
  • Amount spent on DETERGENT and PAPER products
  • Amount spent on DELICASSEN products
  • Category number

The app will look as follows when complete.

Log onto Power Apps https://web.powerapps.com/home and select create new blank app. Select portrait layout.

This will open up a blank canvas. Your screen should look similar to the following image below.

Next we will add text input fields for each one of the data entry items listed above.

To do this navigate to Insert > Text > Text input.

Size the input field and enter in the appropriate name for the control, remove the default and add a text hint.

Repeat this for each data entry field.

When finished you should have a text input field for the following data elements:

  • NAME
  • CHANNEL
  • REGION
  • FRESH
  • MILK
  • GROCERY
  • FROZEN
  • DETERGENT
  • DELICASSEN
  • CATEGORY

Your app should now look like the following image.

Next we will add a button. To do this click on Insert > Button. Rename the button to SUBMIT and position it in the bottom right hand corner of the screen.

Your screen should now look as follows.

Save the app and give it an icon. I called mine the Customer Data Entry App.

This concludes the first part of this series. Next, we will build the Azure Machine Learning Studio experiment that we will use to categorize the customer if the customers category number has not been filled out in the app.

Hopefully you have found this to be another practical post.

Until next time

Anthony

References

@ChuckSterling has an excellent series of videos on embedding a Power App in a Power BI Dashboard.

https://www.youtube.com/watch?v=xKTPI2pEl9I

https://www.youtube.com/watch?v=dZb3vzp1WFE&list=WL&index=46&t=706s

@NathanPatrickTaylor also has a great video on integrating Power BI, Power Apps and Flow.

https://www.youtube.com/watch?v=au4a3AEIbKw&index=47&list=WL