Adatis

Adatis BI Blogs

Using ADF V2 Activities in Logic Apps

Logic Apps recently introduced the ability to connect to an Azure Data Factory V2 instance and perform a number of actions including cancelling a pipeline, invoking a pipeline and also interrogating the service for information about a particular pipeline run. This blog will focus on how to utilise the full potential of ADF V2 via a Logic App connector when calling a pipeline. In its current form there is no way to provide input parameters to ADF from Logic Apps, thereby hamstringing one of the best features about ADF V2! Later in this blog I will show how to get around that. Despite this there are a number of reasons why a Logic App calling an ADF V2 pipeline with a simple activity is a great thing and I have gone in to some detail below. Event Driven ExecutionThe Azure Data Factory V2 team are now starting to bring in event driven triggers but currently this is limited only to the creation or modification blobs in a blob storage account. This is a good start but pales in comparison to the overwhelming number of events that can trigger a Logic App, notably things like Service Bus, Event Grid, HTTP calls etc etc… the list goes on! More info on Logic App connectors here. By utilising Logic Apps as a wrapper for your ADF V2 pipelines you can open up a huge amount of opportunities to diversify what triggers a pipeline run. Simplifying Loops, Conditionals and Failure PathsIn addition to event driven triggers, the ADF team have also brought in an IF activity and a number of looping activities which are really useful in a lot of scenarios. However, This kind of logic can be simplified when its built in Logic Apps. The visual editor in Logic Apps makes understanding the flow of a loop or an IF seem much simpler to those maybe not so familiar with the business logic that's been implemented. Additionally failure paths can be handled much more efficiently and clearly with many more options for logging or notifying of failure right out the box – e.g. the Send Email activity. Heavy lifting of DataLogic Apps, for all its benefits, is definitely not a heavy lifter of data. They really excel at lightweight messaging and orchestration whereas Data Factory is great at moving big chunks of data when using the Copy activity. When you pair these two together you get something that resembles SSIS Control Flow (Logic Apps) and SSIS Data Flow (Data Factory). Now that they can be closely and easily integrated it makes orchestrating the logical flow and movement of data in the cloud much simpler. So now we know that pairing Logic Apps and Data Factory V2 is a great idea, lets look at how to do it. Understandably there are no triggers from Data Factory at this point so you will need to trigger your Logic App in any one of the million ways that are available. Once you have your trigger sorted you can search for the Data Factory connecter and choose the action you need. You will then need to connect to the Data Factory service as below by logging into your Azure tenant. Once logged in you will need to supply a few details to locate the Data Factory instance and pipeline as below.Now you have completed this you can trigger your Logic App and you will see that your Data Factory pipeline will be invoked as normal. However, by using only this method we are depriving ourselves of the ability to derive parameter values outside of Data Factory and pass them in at execution time. Currently there is no where to supply the input data! This is where the blog gets a bit hacky and we will venture into the Code View of the Logic App. [Side note: you can usually get around a lot of tricky situations by manipulating the code in logic apps and not just relying on what the UI gives you] Click the “Code View” button in the designer and then locate your Data Factory V2 activity definition in the code page. It will look something like this (I have whited out my subscription id)In order to pass in data to this Data Factory pipeline, all we need to do is add a “body” attribute within the “inputs” object like the below image. We can then reference any of the variables called out in the Logic App or any other value that might be available to us. In case you’re wondering, these input values need to configured as input parameters to the Data Factory Pipeline you will be calling. More on how to do that hereOnce you have updated your code accordingly then save and run your logic app and you should be able to see the pipeline invoked with your inputs in the Data Factory service as below.In conclusion, the marriage of Logic Apps and Data Factory is a happy and harmonious one with many benefits to be exploited. Now armed with the key to utilise input parameters for pipelines called from Logic Apps you can cater for a vast amount of data integration scenarios that require complex but clear logic and heavy lifting of data. Any questions or comments please supply below or catch me on twitter @MattTheHow.

Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences. In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed. I have broken the blog into 2 parts: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Solution I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard. Create Sample Tables and Data in Azure SQL Database Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.             Note:     Azure SQL tables require a Primary Key column to communicate with Flow and consume CDS data. Create PowerApp Create an Environment within the PowerApps service, adding two new Connections:   1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside. 2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.   The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.     This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.     Publish the App and test and change the ‘Target’ values to test. Create Flow trigger Navigate to https://emea.flow.microsoft.com/en-us/ and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.     Select the PowerApp CDS Entity (Adjusted Target) as source.     Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.       Create Power BI Report Create a Power BI Desktop report and connect to the Azure SQL Database. Set up the one to one relationship on ‘PrincipalID’, between the tables. Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App. Embed Power App into Dashboard Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].  <iframe width="98%" height="98%" src="https://web.powerapps.com/webplayer/iframeapp?hideNavBar=true&source=powerbi&screenColor=rgba(165,34,55,1)&appId=/providers/Microsoft.PowerApps/apps/AppID]   The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.       Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!     Further Reading Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below. o   Matt How’s Blog - http://bit.ly/2CpbTYI o   Embed PowerApps into Power BI - http://bit.ly/2ywgsNX o   PowerApps - http://bit.ly/2Brjys4 o   Flow - http://bit.ly/2CoL2vW o   Common Data Service - http://bit.ly/2CnXXhv Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 2

Part 2 of this blog focuses on my experiences with PowerApps, Flow and Power BI. Part 1 was more of a demo and ‘How to’ guide, but when I read an article online, I always find known limitations, challenges or workarounds as the most interesting takeaways. Without further ado, here are my findings.   A summary of both blogs below: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Benefits -          Easy to get started. Rolling out Power Apps, Flow and Azure databases into production of course needs careful thought, but for Proof of Concept’s, Flow (2,000 runs per month) and PowerApps (for Office 365 users) are free to use. Links to the price breakdowns are provided in the Further Reading section below. -          There are a range of Wizards, Templates and GUI’s. All the tools used offer great templates for moving or inputting data and the fact barely any code is needed, makes it simple for business users. Following a couple of YouTube tutorials on each technology will get people up to speed very quickly. -          Azure technologies provide seamless integration between Microsoft tools. Whilst there are some other well-known, reputable cloud service providers around, using one product is always going to produce a slicker solution. Having less configuration steps means less chance of human error. -          Customisable features of PowerApps give the ability to mask, validate and format the PowerApp screens. It also makes the user entry a more pleasant experience, as the forms are more intuitive. Limitations -          You can only embed PowerApps into a Dashboard – as a Tile. I am not sure if moving PowerApps into a Power BI Report is on the roadmap, but I would be surprised if it was never supported. -          Power BI Dashboards are cached and not entirely real time. You can change the cache settings to 15 minutes, but the best way to ensure your visuals contain the latest Power App data is to manually refresh your page in the browser. Reports do update automatically, which makes it even more frustrating. -          Common Data Service (CDS) is a preview Data Connector in Power BI. As a result, you need to either have your environment set as ‘America’ and/or been given the beta by Microsoft. If I had access to this connector, there would have been no need to have the Azure SQL Database or Flow trigger. Milinda Vitharana’s blog shows how to enable CDS Power BI Integration. -          If you wanted to use an on-premise database instead of an Azure database, an additional step is needed. A Data Gateway (link here) must be installed to move the Power App data back into the SQL database. Therefore, I would always recommend (where possible) using PaaS or other cloud services, as they talk to each other natively. -          The error handling within the PowerApps is still quite limited. If Flow fails when updating data between PowerApps and Azure SQL Database, nothing is captured within the form itself. An Admin would need to check the Flow job or set up email alerts for user’s peace of mind.     Conclusion The initial signs look promising for Power BI and PowerApps integration. I managed to create an Actual vs Target Proof of Concept in just a matter of hours, without any real coding. There are still quite a few drawbacks and hoops to jump through to bring everything into a Power BI Dashboard, but I can only see things getting easier from this point. There are other use cases for embedding a PowerApp into Power BI, such as monitoring live sales and re-ordering stock within a PowerApp or updating product descriptions that automatically updates the Dashboard attributes. Giving someone the ability to directly interact with a Dashboard and make instant business decisions is priceless in today’s fast paced world. Further Reading Find other recommended resources below. o   PowerApps Pricing - http://bit.ly/2j5sN69 o   Flow Pricing - http://bit.ly/2kw0MFr o   Milinda Vitharana’s blog - http://bit.ly/2BfkywQ Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request. Twitter:            @DataVizWhizz

How to Find Your Next Job with Power Apps and Flow

Both PowerApps and Flow exist within the Office 365 suite and bring enormous amounts of possibilities to mildly technical business users. No longer will Dan in IT who knows a bit of VBA be hassled to write a dodgy macro that puts some data in a database. Not only that, business users can now reach out to literally hundreds of other services that come connected straight out of the box! In this blog, I’m going to demonstrate a way we can use PowerApps to put a professional and mobile ready interface onto a Flow, allowing us to query an API and present the results back using Power BI.   Creating a PowerApp You can create a PowerApp in either the Web Portal or using PowerApps Studio (https://powerapps.microsoft.com/en-us/downloads/). I personally prefer to use Studio but both work the same, and actually all connections, Flows and custom APIs are managed through a web portal. If you have ever developed with Windows Forms then PowerApps will feel very comfortable. There isn’t a toolbox as such but you can easily drag and drop controls from the ribbon bar and all the properties live on the right-hand side. It also holds some similarities with Apples xCode in the sense that you can see all your Screens (Scenes in xCode) on the left. 1. Ribbon Bar: Here you can drag and drop a wide range of controls, galleries and media APIs onto the App design screen 2. Preview App: This button will run your App/debug. You can also use F5 3. Screen Viewer: Here you can see all the screens that make up your App 4. App Design Surface 5. Properties Window: Configure properties about the controls within your App   The Common Data Service Because we are looking at this from an Office 365 perspective we can make use of the Common Data Service, but we could also choose from any other relational data store including Oracle, MySql, SQL Server, SharePoint etc. As it says on the tin, the CDS is a generic, cloud hosted database that gives users the ability to create their own datastores and then share those throughout the organisation using AD. It also integrates very nicely with PowerApps and Flow meaning we can avoid any SQL DDL or Stored Procedures. Out of the box you get a range of standard tables that cover off a variety of business needs but you can also create custom entities that can tailor the CDS to your specific needs. Here’s an example of an entity I created in CDS to use as the main datastore for my App. 1. Ribbon Bar: New fields, Import/Export, Settings and Delete 2. Tab Bar: Fields and Keys. Preview Data within table 3. Custom Fields: Showing data types, Nullability and Cardinality 4. Standard Fields: Audit fields e.g. Created by / Created on   Developing a PowerApp One of the best features of PowerApps is that it is very smart with metadata, we simply need to point it at a table and PowerApps can use that to make decisions on how to construct your App in a way that suits the C.R.U.D. needs of your datastore. By creating the app from the custom CDS entity, PowerApps will know that you need a browse screen, a details screen and a new/edit record screen. Better yet, PowerApps will create and populate a form control with all of the custom fields ready to be populated. Based on the fields configuration it can auto create mandatory flags, error handling and hint text. You may question whether PowerApps has some limitations due to not having a code editor, whilst I’m sure some will find this to be true, I am yet to be disappointed. Instead of code, PowerApps uses Excel like functions and context variables which will feel very intuitive to any excel user. Context variables get stored at App level and can be called and updated from anywhere within your App. When creating the App, you can choose from a range of controls including Power BI tiles, Star Ratings, PDF viewers, Import/Export, the list goes on. Additionally, the gallery options mean you can display data or images in a real variety of ways. Above all that though is the integration with the devices media capabilities that make PowerApps a really cool product for non-coders. With PowerApps you can take and save pictures, Play and record video/audio and even scan barcodes. I’ve made a few basic changes to my App that you can see below but even if you hit F5 and previewed your app straight after creating it, you could successfully view, edit and input data to the database. So far I have written no code and Dan in IT is now free to go back to work. 1. Quick Actions: PowerApps has automatically created these quick actions to submit or close the form 2. Mandatory Indicator: Depending on the “Required” Property in the CDS 3. Text Box: In New mode will be blank, In Edit mode will show data. Can also show hint text and error messages if input is invalid. 4. Star Rating Control: I swapped a standard integer input with a star rating to make the App more user friendly.   Creating a Flow By default a newly built app is configured to write data back to the datastore by using a SubmitForm() function. These functions are handy for a lot of things as they take care of resetting the form after submission but also setting the form to Edit or New mode. If we want to do anything more than this – avoiding code – then we need to start looking at Flow. Flow can do an awful lot – just look at the pre-built templates for some ideas, but I’m going to use it to call the Glassdoor API to get job progression information. To create a Flow, you need to start with a trigger. The same goes for Logic Apps only, with Flow, you can trigger the process from a button press within your PowerApp. From then on you can create either actions, loops, branches, conditional logic and constraints in order to connect up any number of systems. 1. Trigger: Trigger point that is called from PowerApps 2. Initialize Variable: Passes a parameter from PowerApps into a variable to be used within the Flow 3. HTTP: Uses HTTP GET method to call the Glassdoor Job Progression API 4. Parse JSON: Parses the JSON response from Glassdoor and provides results in the form of variables 5. Email on Failure: By using the Run After feature I have configured an email notification if the Glassdoor API call fails 6. For Each Loop: Iterates over the JSON results and writes each set of variables to the database. At the moment I am using SQL so I can feed Power BI, the PowerApps team are working on deploying the CDS connector for Power BI to the UK in the coming months The formula that is used to call the Flow from PowerApps look like this: GetFutureJobs.Run(Occupation); Navigate(Results, ScreenTransition.None, {CurrentJob: Occupation}) In here there are 2 functions. The first (GetFutureJobs.Run(Occupation)) is the function to execute a Flow. Anything within the brackets will be passed into the Flow and can be used at any point within your process. In this case I pass in the users current job and use that to search Glassdoor for potential next jobs. Next is the Navigate function. This is a common occurrence in PowerApps and is used to take the user to the results screen. The first parameter is the target screen, Results. The second tells PowerApps how to transition between screens and the final array (the bit between these {}) is a list of parameters that can be passed into the next screen.   Implementing a Power BI tile The final step for my App is to analyse the results from Glassdoor using a Power BI tile. By creating a simple report and dashboard my PowerApp now has a fully functioning Power BI tile that will refresh on the same schedule as the main Power BI report within the service.   Hopefully from this blog you can see how powerful these two services can be when paired together but also how accessible these tools are now. The fact that I can have a working mobile app within minutes is somewhat revolutionary. I can certainly see a load of opportunities for these to be used and I encourage anyone reading this to have a play and unleash the POWER!