Adatis

Adatis BI Blogs

Iconography in Design

In this blog I will go about discussing the importance of design, in particular looking at how icons can help add the finishing touches to a piece of front end development. Whether you’re building a Power BI report, SSRS report, PowerApp, or doing any kind of front end visualisation and design, you’ll know that 50% of the battle is making whatever you’re building jump out of the page. You could have built the most useful report that a user could wish for, but unless it looks good, you’re not going to get any plaudits. Essentially, good design goes a long way to making a success on a piece of development. This is even more important if you’re building the underlying architecture / ETL, and for all our good practice and thoughtfulness in this area as developers, will rarely impress or impact on an end user. Its important to get inspiration for design from somewhere, so before going about designing or even building something, take a look on Google, do some research and try to look for similar things to what you’re doing and see what you think looks good, or not so good. You should build an idea up of how you want to design you’re landing page, report or report headers, etc. within the tool. If you’re in Power BI, take a look at the partner showcase for example. There’s some really good examples to give you ideas – like this one! Microsoft apps such as Power BI or PowerApps go a long way in helping us build the best when it comes to data visualisation, but unfortunately they sometimes rely on developers to go outside the box to finish things off.   Icon Finder Recently I’ve started to use a site called iconfinder.com which has a large pool of useful icons you can use for building out certain corners of apps that the standard MS tooling will not support. The icons are mostly $2 each, but you can get a subscription for £20/month or $10 with a discount code (which you should cancel as soon as you register). Please, don’t jump in if you think you need to use this as a resource. Alternatively, save the icon using Chrome and use it as a placeholder until you are happy to push to production. For non-subscribed users, the icon will always come on a background of faint grey lines. This isn’t too bad as they don’t completely ruin the look and feel of the icon in the development and are good for a placeholder for demos, etc. To get started, just type in your keyword for the type of icon you’re looking for, and then its just a case of wading through the results to find the icon that fits the look and feel your inspired to build against. Sometimes, you’ll get another bit of inspiration off the back of this which you can use as another key word to find even more icons. The site also comes with a very handy icon editor tool, essential Paint Shop Pro on the web. There’s lots of these sites out there but its useful its all integrated into one place at no extra cost. It will load the SVG icon into it automatically if subscribed which then allows you to edit colours or shapes etc. In my instance, I found a nice % complete icon set which would look good on a white background. Unfortunately, I wanted it on a blue header bar, so needed to change it up slightly to fit the look and feel. No problem, took less than a minute to modify and download. Its also worth mentioning that the site does a good job at helping you find a pool of icons which will fit together nicely using the same look and feel, showing you icons from the same icon set. In one instance, I replaced an icon I was looking for to be from the same pool even though the icon wasn’t exactly what I was looking for – because overall it just felt like it fitted together nicer with the other icons on the screen.   Design in Practice As mentioned above, doing some research before you build can really help you create a much better finish. For inspiration for a recent PowerApps design I did a quick search for landing pages on Google, and found a few I liked the look of (below). As long as the general elements you are working to are similar, it really doesn’t matter where the inspiration comes from. In these cases, they were in the form of mobile apps. From these images, I was able to identify the key components which made me bookmark them: I wanted some kind of non-offensive background, possibly semi-transparent, or with overlay. I wanted a title that stands out the page, so white on grey or similar. I wanted a small section for a blurb for the PowerApp. I wanted 2 buttons, and the buttons to stand out. I wanted logos in the top corners. I wanted a nice look and feel for the colour palette. From this, I then produced the following landing page. I found the background on picjumbo.com which turned out to be quite a nice resource for some generic business style artwork, and then added a blurring filter across the top. This still interferes slightly with the buttons / title so I’m not completely happy but satisfied enough that it achieves the look and feel I was looking for. For the title, the range of fonts supplied with PowerApps is rather limited so I could go externally for this too but was happy enough for the time being. The layout also leaves room to shrink the title and add a small blurb if need be. The buttons are made up of a number of icons and fit with the theme for the app. As mentioned above, I also added % complete icons to each page so users were able to understand how far they were along the scoring pages within the app. PowerApps provides sufficient icons for the back/refresh buttons that fit in with the white on blue theme, so I didn’t have to go externally for these. These were placed on the page header next to the logo.     Power BI While this most recent bit of design was focused on PowerApps, I also add small bits into PowerBI during report design. For instance, rather than just have a generic button that can push you to a “details” page which has a table for the row by row breakdown of some aggregated data - I looked for an icon, edited the colour palette slightly and added this to the report. With recent Power BI functionality, I can make the image act as a button and redirect the user to another page. I’ve also used icons in dashboard design where a single visual didn’t really represent the content of the report to drill into. This can also be a good way to go about adding a bit of flavour to a dashboard to mix things up. In my case, it also meant the drill down into the report level was less ambiguous, by asking a question as the title if that’s what the user wants to do. Design is always subjective of course, but its great to use other resources at your disposal to go about building out apps. Depending upon the current estate in which you develop, it also helps them stand out a bit more and add a unique context to the reports/apps within the project. Hopefully this blog has given you a few ideas for your next project!

Embed PowerApps into Power BI Desktop

Microsoft’s January 2018 Power BI Desktop update (blog found here) contains quite a few small visualisation features, but the one that stood out most to me is the ability to import PowerApps as a Custom Visual. My last two blogs (Part 1 and Part 2) demonstrated how to embed a PowerApp into a Power BI Dashboard in the service, but it wasn’t possible to achieve this in Power BI Desktop or a Report.  How things have changed within a month! This article illustrates how quick and easy it now is to plug an existing PowerApp into Power BI Desktop.      PowerApps Custom Visual Like all popular Custom Visuals, you can either Import from file (stored on a local machine) or Import from store. The Microsoft endorsed Custom Visuals are found in the store, which is why I would generally advise importing visuals from there.  Search for “PowerApps” and it will appear as the top result. Click ‘Add’ and the PowerApps Custom Visual is available. NOTE:   The PowerApps Custom Visual is currently in Preview, meaning its reliability cannot be 100% guaranteed.  Always use Preview tools/features/visuals with caution. PowerApp Connection I previously created an ‘SvT Adfjusted’ PowerApp, which will be used for this example.   Firstly, the visual needs to have ‘data’.  It can be an attribute or measure, but doesn’t matter when connecting to an existing PowerApp.  If you haven’t logged into the PowerApps portal, you may be promoted to do so with Power BI Desktop.  Once logged in, you will see the below screen:  If you see the ‘not supported’ error message above, do not worry – this is red herring.  Click ‘OK’ and then navigate to the appropriate Environment within the nested PowerApps portal.  ‘Adjust Target App’ resides in the ‘Callums Blog’ Environment. Click ‘Choose App’, select the PowerApp and click ‘Add’. That’s it, the PowerApp is now embedded and ready to use in Power BI Desktop. It is also possible to create a new PowerApp within Power BI Desktop, which is demonstrated in this video. The look, feel and general experience is the same as what you see in the PowerApps portal, meaning you do not even need to use a web browser to get started. PowerApps Refresh I wander how long Power BI Desktop takes to refresh when a change is made to the embedded PowerApp?  Let’s find out. Before: Principal A ‘Target’ value is changed from 850000 to 950000.   Unfortunately, nothing happens.  Power BI Desktop caches the data sourced from the Azure SQL Database, which is where the PowerApp data is stored.  The only ways to view the change is to click the Power BI ‘Refresh’ button or change the context of the visual interaction within a report.  What I mean by the latter is that you need to force the Tabular engine to write a new DAX query, which in turn, forces a query refresh.  Using the ‘Refresh’ button (below) will always be the simplest refresh method.   After: Published Report The report can now be published to the Power BI Service. The Service contains the same refresh issues as described in Power BI Desktop. Manually refreshing the web page (CTRL-F5) is currently the best approach.  It is worth noting there is between 15-30 seconds delay between a submitting a PowerApp change and viewing the new number in a visual.  This is expected, as there are a few processes (behind the scenes) that must happen first. Web Content vs. Custom Visual There are now two ways of adding a PowerApp into the Power BI Service – but which is better? Web Content -          Advantages o   Easy to dynamically add a ‘Web Content’ tile to a Dashboard. o   More flexibility around the link behavior e.g. open custom link or other report in a new tab. o   The PowerApp sits completely outside of Power BI and does not necessarily need to be seen in a report. -          Disadvantages o   PowerApp embed code is required and may not be available to all users. o   Extra layer of management and maintenance. Custom Visual -          Advantages o   No increase in the Power BI Desktop data model or file size.  The PowerApp is sourced from the cloud and acts as a Live Connection. o   One stop shop where both visuals and the PowerApp reside. o   Less places for an issue to occur.  Security can also be resolved, using Power BI Desktop as a test. o   PowerApp can be pinned as a visual - to an existing or new Dashboard.  This step is far more user friendly and easier to achieve with the Custom Visual. -          Disadvantages o   No ability to use custom links. Whilst Web Content does offer a bit more flexibility and interactivity, I would advise using the Custom Visual wherever possible.  Having one place where everything is managed, makes everyone’s lives far easier.  If you have a good business case for adopting Web Content, then of course, please do so.  References Find other recommended resources below. o   PowerApps Custom Visual Video - http://bit.ly/2Fi3vLY o   Power BI January Update - http://bit.ly/2CSfLVl 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 the PowerApps Custom Visual, feel free to leave a comment below. Twitter:                @DataVizWhizz

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!