Adatis

Adatis BI Blogs

Power BI Dataflows – Bringing self-service ETL to the Business User

Announced at the recent PASS Summit, Power BI Dataflows has now gone into public preview. Previously, any ETL that users applied via Power Query within Power BI Desktop was only applied to their own dataset. With Dataflows, ETL processes are now classified as a first-class citizen and this update provides centralised self-service data prep as part of the Power BI product so that business users can expose cleaned/schematised entities across the business in a similar way to a Data Warehouse. No longer will the same lightweight ETL processes be occurring across multiple users for the same dataset, and often in different ways. Traditionally, self-service BI is limited to analytic models and reports/dashboards but with Dataflows that is changing as it shifts down a peg.  In this blog, I’ll take a look at the new functionality and give my thoughts. Before we begin, its worth noting that this is an entirely different piece of functionality to that of the similarly named Data Flows in Data Factory which is in private preview. You can tell the difference by the capitalisation of the word “Flow” for Data Factory. I’m not sure what the marketing department would say about this but it is slightly confusing having both been released as news at the same time.   What is a Dataflow? A dataflow is a collection of entities (which are similar to tables) that are created and managed from within the Power BI Service (powerbi.com). This is then stored under the Common Data Model (CDM) in an Azure Data Lake Storage (Gen2) resource as files and folders – no longer as a Tabular Model that we are used to with Datasets.  Dataflows are used to ingest, cleanse, transform, enrich and schematize/model the data in a similar way to that of ETL to create a Data Warehouse. Once created you can use Power BI Desktop to create datasets based off of these entities in the usual manner. While dataflows can be created and edited by both Pro and Premium users, there is a larger set of functionality only available via premium. This includes creating computed entities (calculation aggregates), linking to existing entities in other dataflows, and incremental refreshes.   Creating a Dataflow To go about creating a dataflow, navigate to an App Workspace on the Power BI Service (note that this cannot be My Workspace for obvious reasons, also note this cannot be done in Power BI Desktop). You’ll notice alongside Dashboards, Reports, Workbooks, and Datasets that there is a new artefact labelled Dataflows (Preview). If you have Premium capacity this will need to be enabled via the Admin Portal Capacity Settings. Click the + Create and select Dataflow. Each dataflow has only one owner and only the owner can edit it. To create a dataflow, you can either define new entities which involves connecting to a data source and then mapping to the Common Data Model entities / defining custom entities OR linking to other existing dataflows (see below).   If you are creating new entities, you are provided with a set of familiar data source connectors (see below). This set is not as complete as those through Power BI Desktop but I expect more to appear within time. I suspect there is also more work to do here because some of these connectors documented below, i.e. Excel - ask for a File Path or URL without giving any functionality to go get this through a lookup. This is something we have come to expect as part of Power BI Desktop.   Once you have loaded your data, you will then be provided with a fairly familiar query editor pane (see below). The first thing you will notice is that the options to manipulate and transform your data are limited to that of Power Query as part of Power BI Desktop. Again, I suspect this will mature when the functionality hits GA but for the time being gives some reasonable functionality to do what you need to do to create entities in the form of column/row transformations. You are also provided an option to “map to standard” which allows you to join into the Common Data Model (CDM), essentially some generic templates for entities such as Account/Product/Customer/etc. While I can see how this could be useful for an average user, I think it would still be better to create your own based around your businesses own definition of an Account/Product/Customer/etc. These are then stored as custom entities within the CDM. Once the entities are transformed/schematised how you want them, you save them to the Power BI Service with a name/description. Under the hood, this is then written to a Data Lake as a series of files along with a JSON schema.   Dataflows are then available via Power BI Desktop using Get Data (see below). I believe you will need the November 2018 release or later to see this option. The dataset is then treated in a similar way to everything else you would have seen previously within Power BI Desktop. When selected, you should be able to see all of the workspaces within your BI tenant that you have access to which contain dataflows. Within each dataflow you can see each entity. That’s really it, they’ve kept it simple on purpose and abstract from what’s going on under the hood.   Wait, but what does this mean for the classic Enterprise Data Warehouse? I certainly think from a business perspective it will blur the lines slightly between using traditional ETL processes to create a DW or going down this route, more so at smaller scales. It would be hard pressed to replace an Enterprise level solution, but there is definitely a use case somewhere around departmental level instead of creating a data mart. It will allow users to leverage their data faster than waiting on IT to build out a solution. It really depends on the complexity and maturity of the platform they are intending to develop and its purpose. The fact is sits on ADLS Gen2 is a positive and allows other applications to access the data at a raw level rather than relying on a specific connector. It also allows users of products such as PowerApps to connect into a cleaned CDM, another plus. On the other side, its worth bearing in mind that you will need the Premium version to facilitate incremental refresh which is required by the majority of data warehouse solutions.  The lowest cost of Power BI Premium is that of a single P1 node which costs at the time of writing £3,766/month or £45k/pa – not small by any standards. This is worth taking into consideration. The other fact is that traditional DWHs deal with concepts such as SCD Type II and creating History which Dataflows will not be able to facilitate. You may also have noticed there was no mention of DAX or measures, which still needs to be down at the usual level within Power BI Desktop, not ideal to share KPIs across users. Lastly, there is currently no data lineage, although this has been rumoured to be part of the roadmap.   Conclusion While this feature will be a game changer for a number of end users and projects, I think it will join the set of questions such as “Do I need SSAS or can I use Power BI for my models?” I expect to see this appear at next years conferences! The answer will always be – it depends! As we have seen with some aspects of Power BI, this new functionality also opens up businesses to governance issues with potentially having users create their own version of the truth rather than something which has been thought out and modelled by someone with a certain skillset or authoritative source. This could cause contention between departments. As noted above, I expect the Power BI team to continue to mature the functionality of the product – but for the time being, it certainly opens up another avenue to draw people into the toolset and provide another level of functionality for business and users alike.

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 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

PASS Summit 2017 – Coming Soon to the Power BI Service

I recently attended a Power BI Governance session at Pass 2017 and some new features were demoed in the Service.  I thought I would share these with you. The below have been on the Microsoft roadmap and are not strictly hot off the press. However, we were told to keep an eye on the Power BI blog site (found here) over ‘the next few weeks’ (early December 2017) – which is pretty exciting!  Without further ado, here they are: Workspace Apps ·         Selective Report Publishing.   o   Meaning you can pick and choose what reports are consumed by the end users. o   This is particularly useful if you have a combination of workspace developers and consume only users.  Self-serve analysts may be working on their own reports and they will no longer exposed to everyone within the App. ·         Install Apps automatically for existing or new users.  This can tie back to Security Groups or Distribution Lists. Collaborate with External Users ·         Directly linked to Workspace Apps. ·         Facilitates App and Dashboard sharing with personal work email accounts e.g. Gmail. ·         Uses Azure Active Directory business-to-business (AAD B2B) and incorporates Row Level Security (RLS). o   For more on AAD B2B – click here. -          UPN mapping to bridge on-premise AD to AAD and natively support the external email collaboration. Audit Logs ·         Solution templates for faster, convenient Audit Logging. Examples include: o   Author tracking – which users publish the most reports o   Gateway activity – deleted, changed, reconfigured. o   Report Lifecycle – when reports are modified, deleted and history of these activities. o   Dataset and data source tracking. My personal favourite is the ability to control what reports are packaged into a Workspace App.  This has caused me problems at various clients and made the experience of release management more convoluted and time consuming.  It will certainly please some of my customers! Further Reading I must thank Adam Wilson (Twitter handle below) for delivering a great session at Pass 2017.  All images are taken from his slide deck. ·         Adam Wilson’s Twitter handle - @AdamDWilson ·         Power BI Blog - http://bit.ly/20bcQb4 ·         AAD B2B - http://bit.ly/2mpKD7H ·         Pass 2017 Website - http://bit.ly/2xSbQC0 Contact Me I would be very interested to see what the community’s thoughts are on these announcements.  Feel free to comment or message me on Twitter if you want to discuss anything Power BI. ·         @DataVizWhizz