Azure Data Explorer and Power Apps

[RichChapler]

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:

• Azure Data Explorer (https://azure.microsoft.com/en-us/services/data-explorer/)
• Power Apps (https://powerapps.microsoft.com/)

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/