Skip to content

Latest commit

 

History

History
83 lines (52 loc) · 5.2 KB

README.md

File metadata and controls

83 lines (52 loc) · 5.2 KB
Raw

Overall Equipment Effectiveness(OEE) and KPI Calculation Engine

Goal of this sample is to acceleratre deployment of Industrial IoT Transparency Patterns. There is no one size fits all solution, as there are many considerations, please review them before moving your workload to production.

High Level Design

Overall Equipment Effectiveness(OEE) and KPI Calculation Engine

Pre-requisites

Setup SQL Database

  • Create a Single SQL Database using the following az cli commands:

    1. Create a SQL server:
      az sql server create --name iiotsamplesqlserver --resource-group iiotsample --location "West US 2" --admin-user azureuser --admin-password <your password>
    2. Configure the firewall
      az sql server firewall-rule create --resource-group iiosample --server iiotsamplesqlserver -n AllowYourIp --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
    3. Create the database
      az sql db create --resource-group iiotsample --server iiotsamplesqlserver --name iiotsamplesqldb --sample-name AdventureWorksLT --edition GeneralPurpose --compute-model Serverless --family Gen5 --capacity 2

  • Run the sqldb/mes-reporting.sql script to create the MES and OEE Reporting tables, along with some sample data

Setup Synapse Workspace

  • Create a Synapse Workspace with default settings:

    1. Create a Data Lake Storage account
      az storage account create --name iiotsamplestaccount --resource-group iiotsample --location westus2 --sku Standard_RAGRS --kind StorageV2
    2. Create a container
      az storage container create --name iiotsamplefs --account-name iiotsamplestaccount --auth-mode login
    3. Create the Synapse workspace
      az synapse workspace create --name iiotsamplesynapsews --resource-group iiotsample --storage-account iiotsamplestaccount --file-system iiotsamplefs --sql-admin-login-user sqladminuser --sql-admin-login-password <ypur password> --location westus2

  • Create 2 Linked Services in Synapse Workspace:

    1. Download synapse/sqlLinkedService.json and add your SQL password
    2. Download synapse/adxLinkedService.json and add tenantId, servicePrincipalId and servicePrincipalKey related to the Azure Data Explorer created in the prerequisites
    3. Link the SQL Database created above:
      az synapse linked-service create --workspace-name iiotsamplesynapse --name sqllinkedservice --file @"./sqlLinkedService.json"
    4. Link Azure Data Explorer created in the prerequisites:
      az synapse linked-service create --workspace-name iiotsamplessynapse --name adxlinkedservice --file @"./adxLinkedService.json"

  • Upload new Workspace package package/dist/manufacturingmetrics-0.1.0-py3-none-any.whl

  • Create a new Apache Spark Pool:
    az synapse spark pool create --name devtestspark --workspace-name iiotsamplesynapse --resource-group iiotsample --spark-version 2.4 --node-count 3 --node-size Small

  • Upload the package/requirements.txt file, select the workspace package created above and click Apply. Wait until the packages are deployed.

Calculate OEE using Synapse Notebook

  • Open Develop tab, click on + and Import the notebook/CalculateOEE.ipynb

  • Attach the notebook to the spark cluster created above.

  • In first cell, update the values for sqldbLinkedServiceName and kustolinkedServiceName as created above

  • In second cell, update the oeeDate to a date which has telemetry data in Data Explorer.

  • Run both the cells

  • Open SQL Database created above and verify the data in OEE table

Visualize OEE in Power BI

  • Open powerbi/oee.pbix file and change the Data Source settings to connect with the SQL Database created above.

Additional Resources

  • Update and ReBuild package
    • cd package
    • pip install wheel setuptools
    • python setup.py bdist_wheel