Azure Data Explorer and Power Apps

by | Sep 22, 2020 | Azure, Technology, Uncategorized | 0 comments

This article is contributed. See the original author and article here.

Azure Data Explorer and Power Apps

Today’s Mission… marry the awesomeness of Azure Data Explorer and Power Apps

 

 

 

Introduction

For this exercise, imagine a customer with the following characteristics:

  • Large and growing collection of streaming data in Azure Data Explorer
  • A desire to build a low code, highly functional app to make use of this data

Objectives

The following Step-by-Step Instructions will lead to successful completion of the following objectives:

  • Instantiate Resources … prepare required Azure resources
  • Create App … demonstrate connection to Azure Data Explorer data parameterization, retrieval, and presentation

Technologies

This write-up assumes you have a Microsoft Azure subscription, Power Platform license and pre-requisite knowledge about the following technologies:

 

 

Caveats

Before we get started, some expectation setting…

  • The instructions below are not a complete path to a production solution… they were prepared with the intention of conveying basic knowledge and providing a foundation that you could tailor to fit your environment, standards, etc.
  • Keep a watchful eye on incurred costs … consider a daily assessment and use of budgets / alerts
  • Azure interface and functionality evolve rapidly; the snips below will become dated over time

Step-by-Step Instructions

Instantiate Resources

First, we will quickly run through creation of the basic resources we will need to complete this exercise. Although you can use existing Azure resources in your subscription, consider creating resources specific to this exercise to provide for future maintenance, cost analysis, reporting, etc.

Resource Group

Create this resource to group related resources, provide for simplified cost accounting and enable bulk housekeeping.

 

 

 

 

On the “Create a resource group” page, enter values for the following form items:

Subscription

Self-explanatory

Resource Group

Enter a name that is meaningful for you (and aligned with your naming standards)

Region

Select a region appropriate for your situation; take into consideration that some regions {e.g. West US and East US} see higher demand than others

 

Review settings on remaining tabs {e.g. Tags}. No additional settings are required for this exercise.

Click the “Review + create” button, validate, and then click the Create button. Allow time for processing.

 

 

Data Explorer

Use the Azure Portal to create an Azure Data Explorer Cluster.

 

 

 

 

On the “Create an Azure Data Explorer Cluster” page, enter values for the following form items:

Subscription

Self-explanatory

Resource Group

Select the resource group created in the prior step

Cluster Name

Enter a name that is meaningful for you (and aligned with your naming standards)

Region

Select the value used during Resource Group creation

Workload

Select “Compute optimized” from the dropdown

Size

Select “Extra Small (2 cores)” from the dropdown

Compute Specifications

This should be auto populated with “Standard_D11_v2” based on the Workload and Size selections

Availability Zones

Confirm default selection, “(none)”

 

Review settings on remaining tabs {e.g. Tags}. No additional settings are required for this exercise.

Click the “Review + create” button, validate, and then click the Create button. Allow time for processing.

 

 

Add Database

Use the Azure Portal to add an Azure Data Explorer Database.

 

 

 

 

In the newly created Data Explorer Cluster, click the “+ Add database” button.

 

On the “Create an Azure Data Explorer Database” popout, enter values for the following form items:

Database Name

Enter a name that is meaningful for you (and aligned with your naming standards)

Retention Period (in days)

Confirm the default value, 3650

Cache Period (in days)

Confirm the default value, 31

 

Click the Create button. Allow time for processing.

 

 

Sample Data

Follow the instructions in the “Quickstart: Ingest sample data into Azure Data Explorer” article (https://docs.microsoft.com/en-us/azure/data-explorer/ingest-sample-data) to populate sample data that we can surface in Power Apps.

 

 

 

 

Review the results so you are familiar with the data for later sections.

 

 

Power Apps

This write-up assumes that you already have a working instance of the Power Platform with necessary licensing. If not, you can get started at https://powerapps.microsoft.com/en-us/

 

 

 

 

If you are already set up, click “Sign In”.

 

 

Create Connection

Navigate to https://make.preview.powerapps.com/

 

 

 

 

Expand Data in the left-hand navigation to and click on Connections in the resulting options.

 

 

 

 

Click the “+ New connection” button.

 

 

Select “Azure Data Explorer…” in the resulting options.

 

 

 

 

Click the Create button on the “Azure Data Explorer” popup. Provide credentials as required.

 

 

 

Good Job!

 

You have successfully completed Objective #1: Instantiate Resources

 

 

Create App

Objective: Demonstrate connection to Azure Data Explorer data parameterization, retrieval, and presentation

 

Navigate to Power Apps and then Apps in the left-hand navigation.

 

 

 

 

Click the “+ New app” button in the menu bar and then Canvas from the resulting dropdown.

 

 

 

 

Click the “Tablet layout” button in the “Blank app” section.

 

 

Add Connector

Click on the Data icon on the left-hand navigation. Expand Connectors and click on “Azure Data Explorer” in the resulting options.

 

 

 

 

You should see a new area called “In your app” with “Azure Data Explorer” now included.

 

 

Advanced Settings

Click File in the menu bar. Click Save in the left-hand navigation.

 

 

 

 

Enter a meaningful name for your app. Click the Save button in the lower-right.

 

 

 

 

 

Click Settings in the resulting left-hand navigation.

 

 

 

 

Click “Advanced settings”, scroll through the resulting options and find “Dynamic schema”. Turn this feature on and restart the app as required.

 

 

Add Dropdown

Click Insert in the menu bar. Click Input in the resulting sub menu bar. Click “Drop down” in the resulting dropdown.

Click on the Advanced tab in the right-hand popout.

 

 

 

 

Populate the Items input box with: [“CALIFORNIA”,”MICHIGAN”]

 

 

With the dropdown still selected, select OnChange from the Property dropdown in the formula bar.

 

 

 

 

Enter the following formula:

ClearCollect(

    Results,

    AzureDataExplorer.listKustoResultsPost(

        “https://adxpadec.westus2.kusto.windows.net“,

        “adxpaded”,

        “StormEvents | where State == ‘” & Dropdown1.SelectedText.Value & “‘ | take 5”

    ).value

)

 

Click the “Capture schema” button. Allow time for processing.

 

 

Add Data Table

Click Insert in the menu bar. Click “Data table” in the resulting sub menu bar. Re-position the data table and consider adding a border for visibility.

 

 

 

 

Click on the Properties tab in the right-hand popout. Select Results from the “Data Source” dropdown.

Click the “Edit fields” link. Click “+ Add field” in the resulting popout. Select desired fields. Click the Add button.

 

 

Confirm Success

Click the “Preview the app” button in the upper-right of the screen.

 

 

 

 

Try the dropdown, scroll through the data table, and confirm successful data retrieval and presentation.

 

 

 

Good Job!

 

You have successfully completed Objective #2: Create App

 

 

Reference

  • Quickstart: Ingest sample data into Azure Data Explorer

https://docs.microsoft.com/en-us/azure/data-explorer/ingest-sample-data

 

  • Authoring formulas with dynamic schema in Power Apps

https://powerapps.microsoft.com/en-us/blog/authoring-formulas-with-dynamic-schema-in-power-apps/

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment