Adatis

Adatis BI Blogs

Parsing JSON with null-able properties in Logic Apps

Welcome to the next episode of Logic apps for newbies! JSON is nowadays one of the most common open-standard file format that is used to transmit data in the IT industry. We used it with logic apps a lot in our Data lake Ingestion engine that we created to one of our clients. JSON is used nearly everywhere and that’s why it is so important to know how to deal with it. Logic app’s provides very decent support for this kind of data, but some solutions are not so obvious, even for a person with good programming experience. If you are reading this blog there is a chance that you know what I am talking about. In this blog we are going to discuss: How to Parse JSON in Logic Apps Introduction to Parse Component Generating JSON parsing schema Logic App example What is the problem with JSON that contains nulls Parsing JSON property set to null on string typed property What is the solution to deal with nulls Modifying existing schema to allow nulls Execution that allows nulls to be parsed # How to parse JSON in Logic Apps To explain how to Parse JSON in Logic apps I am going to use an example JSON Object with a single property like below: {   “Name”:”Piotr” }   Introduction to Parse Component So lets focus on Parse JSON component as it is going to do all the Parsing for us. This component is composed of two text boxes and single action link: Content – this takes the JSON that we want to parse as an input (Required) Schema – this defines how our JSON is constructed, so it can be parsed in the correct way and strongly type all properties of the object for the further references (Required). “Use sample payload to generate the schema” action link that is placed below the schema box that could help us generate the schema for the input that we want to pass.   Generating Schema By clicking “Use sample payload to generate the schema” link, a little window like below will pop up and we will be able to generate the Schema based on the input JSON that we want to parse. Logic Apps will generate that schema automatically. This process Is shown on the below picture: If we hit done our component should look like on the picture below  The schema generated, based on our example object that we passed in, would look like the picture above. Now our Parse Component expects the Content object to have single property named “Name” that is of type “String”, but we can define it to be any allowed type like e.g. integer, object, array etc.   Logic App Example Our Example logic app to parse JSON is very simple. It uses two components, and single HTTP trigger (Trigger is not going to be covered in this blog) Those two components are: Compose Example Object (Compose Component) – the main purpose of this component is to define our JSON example so we can feed it to our Parse Example Object that would do the parsing. In a real world application it would be an API call or Reading a file from some storage component that returns JSON dataIn our example, like on the picture below, we would insert our JSON example object from the above text box. Parse Example Object (Parse JSON Component) – this is the sole object that we are interested in. It is going to parse JSON into a Logic App Object (With the properties that we can access in further actions).   if we run our example logic app, and pass in our example object, it would finish successfully like on the picture below. We can see in the OUTPUTS in the picture, that it we received Object with the property “Name” that is set to “Piotr”, which means that parsing was correct. Please do not confuse OUTPUTS on below picture to be JSON Object that we passed in. This is the result of parsing - Logic app represents parsed object in the form of a JSON object. In further actions we would be able to reference the “Name” property of the object we parsed. Now that we understand how to parse JSON in Logic apps, lets see what happens when we try to input objects that do not have a value set. It will be a very common scenario that an API that you use would return objects that’s property is null.   # Parsing object that would contain Null value So lets say our API for some of the calls would return object like below with one of the properties set to null. {   “Name”:null } Let’s see what happens if we try to pass object like that into our Logic App. Our schema still expects string for the property named “Name”. Our logic app fails, showing us in OUTPUS the Error Message: “Invalid type. Expected String but got Null.” So lets see what would be a quick solution for that.   # Solution The solution for successful parsing is to modify the schema to allow nulls.   Modifying Existing schema It can be done in very simple step by modifying type of the object into array of types like shown below:   Execution that allow nulls to be parsed If we do that we will be able to parse nulls and strings like on the picture below: We see that this time Logic app did not thrown an exception and would allow us to deal with this null value within our code. Let’s bear in mind that allowing nulls it is not always the best option. There is also default values, that could be considered, but everything depends on the context your logic app operates in. Thanks for reading and I hope you enjoyed the blog post.   You can download working example from the section below. # Download You can download Logic app with null example working from here

Converting JSON with nested arrays into CSV in Azure Logic Apps by using Array Variable

Recently our company needed to rebuild the internal BI solution.The change was caused by an upgrade of some external APIs that our BI solution uses to collect timesheet data. In the new solution we use Azure Logic Apps to call Timesheet APIs and it returns all required timesheet data in form of JSON objects. One of the problems that we encountered during the work, was how to convert the JSON response objects that the API returns into delimited flat files. The solution that we found is trivial, but not immediately obvious for the person with little experience in Azure Logic Apps, so that’s why I decided to share it with you. This blog is going to explain how we can easily convert complex JSON objects into a flat CSV Table by using Azure Logic App’s. This blog is not going to cover how to use Logic Apps from scratch. I will assume that you have basic knowledge about how this service work, and you can create simple logic flows yourself. I am going to focus more on what components we should link together, and how to solve the problem of flattening JSON objects, rather than on technical details how particular logic components works. If you think that you would require some lectures before, please visit Azure Logic App Documentation website. To start with we would need an example of the JSON object that we will try to convert to CSV table. So lets dive in! The picture below shows a JSON object that we want to convert to a CSV table. The snippet below shows only the first element, but in reality this JSON array would be composed of multiple objects like this. You can find the download link to the full object in the download section on the bottom of this blog. We can decompose the object from above picture as shown below: Please note that everything in red is only for annotation. This object is constructed of multiple levels (Level 0) The root of the object is an Array of Project objects (Each Project object in the Array of Projects Objects have two properties): (Level 1-Project level properties) Project property that describes project name (Level 1-Project level properties) AssignedEmployees array that are assigned to the project (Each Employee in AssignedEmployees array contain also two properties): (Level 2-Employee level properties) EmployeeName that contains Name of the employee (Level 2-Employee level properties) Rates array that stores information about rate for that particular employee on that project (Each Rate in Rates array contain two properties): >(Level 3-Rate level properties) RateAmount >(Level 3-Rate level properties) RateEffectiveDate Lets focus for a moment on the Logic app side and try to present the components that we would use for the  CSV conversion. Logic apps have a Create CSV Table action component that can produce a CSV table based on the JSON array of an object (see screenshot below) Looking at the Text box above, if we pass JSON object reference into “FROM” to above component the output from that component will be a CSV Table, but there are some serious limitations. This component expects a JSON array of simple objects. This means that each object of the array should be composed only from single level of properties, like on the screenshot below: Complex objects are not supported at present, and if we provide an array of objects which we had in our example at the start, everything below “level 1”  i.e. the Employees and Rate information will be treated as a single string, like in the “Outputs” of the “Create CSV table” component on the picture below: The solution is to “flatten” our complex object (multi level) into a flat object (single level) and pass it into the CSV Table component. The rest of the blog is going to be focussed on how to convert the complex object into simple JSON so we can leverage the CSV Table component to create JSON. Solution As we have just discussed above, in order to convert the JSON object into a CSV we would need to flatten the JSON object first. In order to flatten this object, we use the combination of a few Azure logic app components: Our solution is composed of the components listed below: Http trigger (“When a HTTP request is received”) – this will run when the http uri is called (It is not relevant to the solution, we can use any type of trigger). Compose action component – this creates an example array of JSON Project Objects. In a real world example, that would come as a part of the HTTP request, Service bus message, or any other way we can read the JSON data in logic apps. The picture below shows only part of the whole object. You can view the whole object by downloading the example of the file in the Download section at the bottom of this blog. Parse JSON action component– this component parses the JSON array and translates it into Logic Apps Objects, so we can access objects and properties  within our Logic app flow. To generate the validation and parsing schema, we use the option “Use sample payload to generate schema” (at the bottom of the component) and paste an example of the JSON that our application will take. In our case this is going to be the example from the component from the picture above. Initialize Variable action component – This component is going to be our temporary storage for newly created flattened objects. We name it Rates Array and It need to be of type Array. We do not require to set any initial value as we are going to populate this array in further steps. Please do not confuse Rates Array with Array of rates from Initial Json. This is a variable to hold newly created objects. Use Nested For each Loop flow component (For each Project, For each Employee, For Each Rate) in order to iterate through all levels of our object. Compose action component  and Append to Array component - On the lowest level of the array (inside Rate loop) we create a new flatten Rate JSON Object with “Compose” type component (This object contains properties from all levels – Project Level, Employee Level, Rate Level). Next we use Append to Array component and paste the newly created Rate object like on the picture below Create CSV Table component – This component will perform all the heavy lifting for us. It gets the already populated Array of Rate objects as a parameter (From) and converts it into CSV table. Note that RatesArray contains the already flattened rate object. Each property of that object will produce one column in the CSV table.We can use +New Step on the picture above to create additional steps if we would like. For example, save the created CSV into blob storage or data lake. The full logic app will look like on the picture belowNow when we have everything for the logic app in place, lets see the result of the execution of this logic app As we can see in the Output section, the conversion resulted a CSV table being created successfully for our example of the Array of Complex project objects. Please note that the input section of above picture. It takes flattened input objects that we created in the Rates Array variable. Conclusion Logic apps provide a simple way of converting an array of JSON objects into a CSV Table. The  advantage of using logic apps for this type of conversion is that there are inbuilt actions components to perform the task. Moreover, we have different types of triggers available that we can configure this web service with, and chose different varieties of connectors for our input and output data. There are certain constraints and drawbacks to consider before we use this in our workflow. This method is not the most efficient as we nest “for each” in another “for each” etc. The more nesting the worse performance. In addition, Logic apps have execution time limits. E.g. HTTP Request trigger which we used, is limited to 120s of executions, therefore we shouldn’t use this method to convert JSON arrays composed of millions of objects. If we need to convert very large files or files with a very complicated structure it would be probably be more suitable to use other types of services like e.g. Azure Functions instead and perform all the heavy lifting in c#. Example Code If you want to see above working solution in action you will need to: Get Azure Subscription (Free trial if you do not own one) Create Empty Logic app (Empty template) with name of your choice Download logic app working Azure Logic App JSON code from the download section Open code view and replace empty template with the copied code You can run the trigger manually or by calling Logic App http trigger address in browser or by using Postman HTTP GET request to the logic app http address which is specific to your application. Request do not require any body parameters or additional headers. Download Click here to download Logic App JSON code Click here to download complex JSON example Click here to download flatten complex JSON example

Adatis SQLBits Photo Booth

This year at SQLBits Adatis decided to create two technical demos to fit in with the magic theme, these were the Adatis Sorting Hat and the Adatis Photo Booth.My blog post will look at the Adatis Photo Booth which was designed to allow us to print a wizarding student ID for participants; I’ll give an overview of the technology stack that was used and then do some analysis of the data we collected using Power BI.The RequirementThe first place to start when designing a technical solution are the business requirements. In our case the requirements were:The user should have an app running on a tablet that will ask them for some basic details such as name, email address and twitter account before allowing them to take a selfie to be printed on their card.The image of the user should be uploaded to the Azure Cognitive Services Face API to capture their emotions.The final data to be placed on the card should be added to a database, in the required format, for our card printing software to pick up.The solution should require the smallest amount of coding possible.The solution should be fully automated and require the least amount of interaction from the Adati on the stand.The TechnologyTo satisfy our requirements we chose to use Power Apps for the front-end and Logic Apps for the backend making our solution completely serverless.A brief overview of the steps in the process and the tech used at each stage is below:At a high level the technology being used is:Power Apps – allows us to build a cross-platform, front-end application without writing any code.Azure SQL Database – allows us to store the data captured from the application. Power Apps natively integrates with Azure SQL DB.Azure Blob Storage – cheap to use and easy to integrate with our solution. Power Apps doesn’t natively integrate with Blob storage so we quickly created a custom API using the instructions at https://powerapps.microsoft.com/en-us/blog/custom-api-for-image-upload/ to upload our images.Azure Cognitive Services – a set of APIs that allowed us to very easily add some intelligence to our application. The face API was easy to integrate and we were using machine learning to map the emotions of the face within minutes.The Technical ChallengesWhile building the solution we came across a couple of limitations that are useful to raise, these are:There is currently no integration between Power Apps and blob storage meaning you need to roll your own API to allow the images from the camera control to be uploaded. The process is simple and the blog post at https://powerapps.microsoft.com/en-us/blog/custom-api-for-image-upload/ gives you details of what needs to be done.The cognitive services connector for Logic Apps doesn’t currently return the emotion data returned by the API. To get around this we made a web request within the logic app and parsed the JSON returned to extract the attributes we needed.Apart from the API to upload the images no coding was required to get this solution to function making Power Apps and Logic Apps a very good choice when you need to create a solution that needs to be delivered quickly.The DataNow we’ve looked at the technical solution we can use Power BI to look at some of the data we collected at SQL Bits.To analyse the data I connected my Power BI desktop to the Azure SQL Database we were using, I then created some measures and visuals against the data to give the below result:The visuals we have are:Top Left - tree map that look at the count of students by their primary emotion (the emotion that scored highest)Top Middle – % of happy (where primary emotion is happiness) and % of unhappy (where primary emotion is anger, sadness or disgust) over each 3 hour period.Top Right – total number of students we produced cards for, overall % of happy and % unhappy students.Bottom – primary emotion logged over timeAs you can see, we did quite well with over 46% of our students having a primary emotion of happy, with only 13% being unhappy. It seems that over Thursday and Friday we had some unhappy students but on Saturday everybody was happy!ConclusionTo conclude, we created our tech demos for SQLBits so everybody can have some fun on the stands however, as you’ve seen, the demos are backed by the cloud technology that we use all day, everyday. Power Apps and Logic Apps are an excellent set of tools and allow you to build applications quickly and easily while connecting various services together; all while being part of the Azure PaaS and SaaS offering without the headache of provisioning servers. The Cognitive Services APIs also present a great offering and allow you to quickly and easily add some intelligence to your application without needing to roll your own solution.As always, if you have any questions or comments then please let me know.

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!