Adatis

Adatis BI Blogs

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 Logic App 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 product 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

One-way Domain Trust and Power BI

I ran into a problem setting up on-premises data gateways on a client recently, whereby they had two domains but with a one-way trust. The result was that when authenticating within the Power BI Service to retrieve data from on-premises data sources in the untrusted domain it would throw an error. At this point it is worth spending some time explaining the architecture.The ArchitectureThe architecture might be familiar to many who use Power BI and the on-premises data gateway, with a little caveat. Domain 1 is the main domain. Domain 2 is the secondary domain and trusts Domain 1. Domain 1, on the other hand, doesn’t trust Domain 2.A user in Domain 1 can access data sources in both Domain 1 and Domain 2. They can create their Power BI reports with a live connection or direct query and publish them to the Power BI Service. In order to use the reports in the service, on-premises data gateways need to be established to provide a messaging service between on-premises and the cloud. In this example, each domain has a domain controller, a tabular server and an on-premises data gateway for each tabular server.The ProblemWhen a user logged-on to the Power BI Service tries to access data from Domain 2, their credentials are passed down to the on-premises data gateway, checked against the domain controller in Domain 2 and returns an error to the Power BI Service. What I think happens is that the user (User.One@Domain1.com) will have their credentials passed down through the on-premises data gateway to the domain controller in Domain 2. Either the domain controller will not be able to find the user, it is the untrusted domain, and will not be able to pass the short name (DOMAIN1\USERONE) to the tabular server, or it tries to check with the domain controller in Domain 1 and encounters the dreaded Kerberos and cannot perform a double hop to return the short name. Either way, the result is the same in that the short name cannot be passed to the tabular server. The SolutionAs you can imagine, there are a few solutions to the problem. If it is a Kerberos related issue, then Kerberos will have to be configured separatelyMake Domain 2 a trusted domainUser mapping in Power BI ServiceThis latter approach is the one I opted for because it was guaranteed to work and would not change the current domain and network configuration.In the gateways settings in the Power BI Service, I went to the Users tab under my data source and clicked on Map user names. In there I mapped users in Domain 1 to users in Domain 2.If you have a large number of users, individual mapping might not be preferable or feasible, which is why you can replace the Domain names in part of the user string, as in example 3. This, however, does rely upon users in Domain 1 having an equivalent account in Domain 2. This is not always the case, for which the wildcard to service account would work, as shown in example 4.

Considerations for Creating a Power BI Enterprise Report Deck

Creating or re-creating an Enterprise report deck in Power BI should be reasonably straight forward given a specification, but there are a number of considerations which need to be made when building something of this nature. In the following blog post, I will detail some of these, and the route I would suggest taking. The contents of this blog revolve around more tabular reports than chart visuals, but the same themes can apply.   Fonts I think it goes without saying to keep the font consistent across both a single report, and a report deck.  The default for Power BI is Segoe UI which for the most part is pleasant, just be careful not to flick between this and Segoe UI Light as this can cause discrepancies. It is however the font size that will cause you more of an issue. The first consideration is to set a minimum size. As report developers we want the report to look as tidy as possible, and usually this means fitting everything on 1 page. The easiest way to do this is to set the font smaller if you are having space issues – but this does not always translate so well to the end user. Depending on the device, they may consider the minimum Power BI lets you set (size 8) as too small for consumption on something such as an iPad – so this is worth checking first. The second consideration is to set the font size for different elements of the report, i.e. row level data at something like a 10, and header level elements at a 12. Anything else that exists such as filter elements should be set the same as the header levels. I would usually set titles a number of points above this, at something like an 18. In general, having varying levels of font size on a single report between elements will look inconsistent so the aim here is consistency! The third consideration if possible is to keep the font size the same across all the reports within the report deck for the same type of element. Again, this adds a consistent feel to the deck. If one report has more rows than another, in my opinion its still better to use the same font size across both, rather than filling the page on both using varying sizes. The last consideration is to be careful when mixing text from a textbox and a card together in the same area of the report. Unfortunately Power BI does not currently support expressions like SSRS does, thus a textbox is for static text only. Dynamic text can only be created through a measure and assigned to a card. However having both elements side-by-side with one another does not give the expected result. The font size of the font in a text box is not the same as a card; a card size 10 is roughly equivalent to a text box size 13 (although you can only set 12-14), thus leaving you with some inconsistent fonts between elements. My suggestion is to create measures referring to elements on the report, and use them for both static/dynamic text, thus every textbox will be a card visual and have that consistent look and feel. If you only need static text, stick to text boxes.   Objects The next consideration is around the number of objects on a report – keep it simple.  Avoid building a giant monolithic report, the more objects you use, the slower the report will perform on PBI service, iPad’s and even to develop.  This is especially true for tables/matrices which will each need to fire off separate DAX queries to return the data elements. Too many objects also has knock on effects for exporting to PowerPoint as objects will overlap with one another more which may not be as much of a case within PBI service but will affect other apps. You can use the selection pane (in the view tab) so move objects above/below one another which will bring forward/push back the elements.   Alignment Another scenario which I have come across is that sometimes it is necessary to include a column header in between the header for a measure and the actual data – for instance to specify the currency or unit. There are 2 options available; the first is to set the headers of the table as white text and insert text boxes over their position. While this achieves the goal, the final look and feel is not ideal as a large proportion of time can be spent aligning the columns with the text in the text boxes, and even then it can still be pixels out of alignment. Adding/removing measures then means you have to go through the same process again as everything shifts left/right. Fortunately, in the November release of Power BI, they have added the ability to align data within the tables better. A better approach for this scenario is to rename the measures within the table visual to whichever unit you want to show for that column. The downside of this is for a developer you will then need to hover the measures to see where the original measure came from, a small annoyance which is compensated by the amount of time saved trying to do alignment within a table. Also, this means less objects in the report, and less objects will generally create a quicker, more manageable report. For anyone particularly new to Power BI, you an use the arrow keys to move around a single element pixel by pixel, to help with alignment. There’s also options on the format tab. I’m still waiting for the ability to pixel nudge multiple elements when selected together!   Colour Hopefully you should be guided in terms of colour by a corporate colour scheme. This often comprises of a set of 4 or so main RGB values to use, complimented by a further set of colours. Pick 1 (or even 2) of these from the main set of colours and use that for the report framework, either the border/frame, or report header/footer, and then use another colour for the table headers, or two if the report requires double table headers. Again, consistency is the key across reports within the report deck. If using double headers for the columns, consider using the colours as background behind the header text rather than colouring in the text in the corporate colour. Consider white text on the darker backgrounds.   Parameter Selection Most reports will contain some kind of slicer visual, to allow the user to change the context of the data – usually by period. As part of the report build, you’ll need to assess where the best position for this is on the report and to keep it consistent between reports within the deck. If your reports will require the real estate going across the page (i.e. wide tables), then consider the top of the report, else if they need the real estate going down the page (i.e. long tables), consider the right hand side. I think by default I would build it at the top, alongside any logos / titles. If you require multiple slicers, maybe move these to the side and build a panel to group them together. Another little hidden option is that of switching the slicer visual between List/Dropdown (circled red below). For some reason, list is the default but most users will agree that the dropdown is neater, and saves space. I’m not sure why this appears here rather than in the standard visual configuration tab, maybe they will move it soon? The dropdown slicer visual still has some issues which I hope will be fixed soon such as not de-selecting the dropdown after a selection has been made. Another click is required outside of the dropdown to hide the options. This is not the best for the end users, and there seems to be no viable alternative.   Header Logic Swapping Unfortunately as I mentioned previously, Power BI does not support expressions within tables, and therefore switching context based on a parameter is not easy to achieve. This is possible but it needs to be done entirely within DAX. To keep the DAX measures for this simple, consider specifying the position on the report as the name of the measure. Then within the definition of the measure, reference other created measures and keep the statement simple, allowing anyone debugging the report to trace the switching logic easily. Also use a DAX formatter such as this to make the DAX as readable as possible. It would be nice for this to be included within Power BI, hopefully it will soon! I’ve included an example DAX statement below to provide this functionality.   New Measure = IF ( HASONEVALUE('Dim'[Value]), SWITCH( VALUES('Dim'[Value]), "Comments describing the logic definition", "", "Value", [Measure], [AlternativeMeasure] ), BLANK () )   Template What does all of this lead to? The answer is a template for the report deck. As well as having guidelines for the above items which make up a report, its also good to build a physical .pbix template for your suite of reports. This way, you are not starting from scratch for each report,and you will get a more consistent feel down to the pixel level of where the objects are. Don’t over complicate the template, but leave enough elements on it to save you re-creating them each time you build a new report. I would generally avoid copying from an existing report each time to then build another report, as this will sometimes include elements like PBI defined measures, which you do not want to carry between reports. Instead define a template which you take from each time.   Conclusion Once decided on a number of these points, it is worth gaining a consensus from the product owner over whether this is acceptable to use moving forward. Do not get to the end of the deck, and demonstrate your decisions across the report set, this will leave you with far too much re-work. Instead aim to deliver maybe one of the more complex reports with a number of the items above, and then apply those decisions to the rest of the report deck.

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

Power BI Solution Templates: Easy API analytics in the cloud.

SummaryEarlier this year, an article published by Forbes Online highlighted API’s as a potential driver to the digital economy. Companies like Salesforce.com generate up to 50% of their annual revenue through APIs. I won’t go into the nitty gritty of what an API is or how it works, but here is an article by the Guardian to help with that.Microsoft recently released new Power BI Template Solutions that allow ‘Newbie’ Power BI users the ability to create analytical dashboards to monitor the activity behind your company's API’s, in a fast and simple way. This blog will hopefully show you how.What is a Power BI solution template?A Power BI template is basically an incredibly simple way to quickly create enterprise ready analytical dashboards.You can use one of the currently available solution templates and have it up and running in around 30 minutes so that you and your team can quickly move onto more important tasks with the insights you’ve gained.Why use a Microsoft Power BI Solution Template?Here are a few reason as to why it may be worth your while to look into using a Power BI solution template:Quick and easy to get up and running.Several different templates to choose from with more to come.A lot of the templates are also free, however you will likely need to be set up with various Microsoft accounts in order to fully utilise some solutions.Customise your dashboard to better tell your story.Scale up as and when you need it.How do I deploy an API management solution template?Before we start, you will need to have a few things to hand.An Azure API Management instance (Link to the Microsoft Site to create an API Management Service)Power BI Desktop (latest version)Power BI Pro (For sharing with your organization)You will also need an application to monitor.Let’s start by going to Microsoft Power BI and navigate to the solutions tab. On the apps page, look for the ‘Azure API Management Analytics’ template and select ‘Get It Now’. You will be asked to sign into your Microsoft account after which you’ll be directed to the template start page.There are 7 to be follow to build your solution.Step 1 - Introduction and logonHere you will be shown the overall architecture of the API management solution.Features of the architecture:Stream API Request/Response data from API Management into Azure SQLProcess API data with Azure ML & Azure FunctionsConnect to Azure SQL and import data into Power BIAt the bottom of the page, sign into your azure account.Step 2 -  Connect to your API management service. The drop down list should list the API management service you have either already created in Azure or you can use the above example of the API calculator if you need.Step 3 - Where to store the API information.You now need to connect to a Azure SQL instance. You can either decide to create a new instance or if you have one created already, you can choose to connect to it now.The above example creates a new SQL DB instance on azure. Fill out the necessary details and check that the server name has not already been used elsewhere.Step 4 - Azure Analysis Services.Azure Analysis Services a fully managed platform as a service (PaaS), integrated with Azure data platform services. We won’t need AAS to continue so we can skip to step 8.Step 8 - Verify your template selection.Confirm you have everything setup correctly.And lastly you simply need to deploy. Step 9 - Deployment and template solution.Using the above standard setup, the whole process took about 30 mins to deploy, after which a .pbix file is created.What have you actually deployed?What does ‘deploy’ actually mean? Below is a list of the resources that are automatically generated for the solution.Azure Event HubAzure Stream AnalyticsAzure SQL (or you can use an existing instance)Azure Analysis Services (optional, additional cost, for high-scale deployments)4 Logic AppsFunction App containing 3 FunctionsAzure Machine Learning Web ServiceAll the above resources will be created under your azure account and charged to that account. This is where the template really comes into its own. All these resources are set up without you having to do anything more than sorting out a few naming conventions.What does an API management solution look like?Now that you’re all done setting up all the resources needed and you have your .pbix file ready. Open up Power BI Desktop and get your new template going.On opening up the template, you will see a host of tabs. Start by going to the ‘Cover Page’ tab and here you will be told to edit your credentials. Editing your template credentials will connect you to the Azure DB resources you have just set up. You should shortly begin to see all the API data that you have. Bear in mind this currently means seeing everything you have from the point at which all your resources were set up. There is currently no way of getting historical data.Hopefully, getting here should be pretty simple. Let’s now go through the reports available.At a Glance.A summary page of your APIs and their usage.API Calls.How long does it takes your APIs to respond. Where are these calls coming from and more specific data for you choosen APIErrors.As you can imagine, pretty important page and one you probably want to keep a close eye on.Call Frequency.This page is useful to see how often your API’s are being called. You can see when and where the loads are on your APIs. This page allows you to see when your applications may be busy and where that traffic is coming come. This is also a good page to check to see if you have Bots making continuous calls to (or spamming) your API.Relationships.This page looks at how calls to your API are related, ie there may be two calls that are constantly happening at the same time. Maybe merging these two could optimize performance of your API….Technorati Tags: Power BI,Template,Solution,Microsoft,API,management,easy,how toWhat next….. Customisable?As you can see there is a lot of information there for you to work with and all created for you through this simple to use template.Now if you know a bit of Power BI, then amend the dashboard as needed. The queries behind everything can also be amended. If you are looking at more fields that you may want to report on, then that will require a bit more work. You will need to make sure the data is captured and stored in Azure SQL DB. You will then need to update the AllRequestData to return that field. Update your model, modify a report control, add the field to Stream Analytics request query and finally modify the Global Policy. So, it can be done, but may need a little bit more know how before you start.A few things to be aware of.Currently, there is no way of including historical data so all you can see is what has happened from the moment the template is deployed.When I set up my template, I tried to set everything up as simple as possible, ie as cheap as possible and I would roughly say that it would cost $9 to $10 a day to keep all resources up and running.Currently the template does not support importing historical data. You will only see data from the point your resources were created.You will need Power BI desktop to use your template.

Generating Usage Statistics from a SSAS Tabular Cube

Once you have users accessing your cube it’s almost inevitable at some point that someone will ask you to generate usage statistics from it, and there are a number of methods to achieve this. In this quick blog post, I’ll detail them and my experiences with each, and then use this data to create a PBI report.   Native Tabular Properties The first method is natively through the tabular cube properties. This also has the added bonus (read impact) that it will optimise future aggregations based on usage – in fact that’s its main purpose. This can be done by setting the CreateQueryLogTable to true, setting up the QueryLogConnectionString (to point to the DB where the usage table requires hosting), setting the QueryLogSamping rate (10 means every 10th query will be logged), and finally the name of the QueryLog table. Advantages of this method is that its very easy to setup with limited knowledge required and it could potentially improve performance if you have an environment where users submit repetitive queries. Unfortunately there are also a number of disadvantages which led me to find other methods. Firstly, it creates a degree of overhead on the cube if its sampling too often; we actually had visible performance related complaints once we turned it on – either through the sampling or change to the “optimised” aggregations. Depending on the sampling rate, you could also find that users who rarely use the cube are not picked up as part of the stats.  As well as this any changes to the cube structure will cause the logging table to be reset. The table is also limited in terms of what it actually logs (as you can see below) – useful if you just want just the user and timestamp info but not much else, and no real ability to configure.   AS Trace To that extent, I looked for other tools to do the same task but better and I found AS Trace. Originally built for SQL Server 2012, it works fine on 2014 – and provides you the ability to run a trace against the cube activities (and log to a table) exactly like the SQL profiler but without the overhead of the GUI which adds unnecessary memory/processor power. It also runs as a windows service allowing it to restart automatically when the server reboots. If this is the case, the tool also logs the existing data to a History table and truncates the logging table. Exactly what I was after. The tool collects information based on a preconfigured Analysis Services Profiler template, which can be optimised depending on which events you are interested in. I initially ran it using most events selected, and with a limited user set it was generating in the region of 25,000 rows a day. This was clearly not maintainable for a long period of time. I then used the following blog post to understand what each event of the profiler was giving me and then just created a lightweight trace definition file to give me what I wanted. I limited it to Query Begin, Query End (for DAX/MDX statements) and Audit Logon/Logout (for session data). The setup is very straight forward, just run the install.bat as an escalated privileged account, and check it installs the service correctly. Next, add your SSAS service account to the Logon of the service, make sure the account has “Log on as Service” and membership to the database you are writing to in the form of DDL and DML access, i.e. able to create tables, write to tables – and lastly admin rights to the instance of SSAS you intend to use. Next, configure the ASTrace.exe.config file with the parameters you want the tool to use. This includes the location of the cube (can handle multiple cubes), the location of the trace definition file, the location of the DB instance and table you want to log to and lastly whether you want to preserve history on restart. The only thing I couldn’t do here, is set the schema of the table it was using to log to, which defaults to dbo. All that’s left is to start the service, and check the log file to see if it has created any errors on start-up. If not, the table should be created correctly and awaiting input. I also saw another method while researching using Extended Events (XEvents) but did not implement this once AS Trace provided me with the information I needed.   View / Power BI Report I initially used the data to run a limited set of queries to extract total users, and total queries for a given time period. This was useful to a degree but from the data collected I realised I could be doing so much more. This lead me to do some analysis across the type of metrics being logged, and allowed me to create a view on top of the tables of what I thought might be useful on a report. I removed all the redundant columns it was tracking, and created some friendly names for the EventSubclass, and other columns. I used the PATINDEX function to check the query statement for existence of some important values – while not an exact science, it would give me a good picture of the split between certain user groups and KPIs being run. I’ve included the view definition below. I ended up limiting the data to EventClass 10 as this seemed to capture all the necessary data. The only downside I have seen so far is that users querying through the Power BI web service are anonymised under the service account name. I’m currently looking into options to resolve this which I’ve seen as configuration options on Power BI – to allow through the username as long as it can be matched at the other end. SELECT RowNumber AS ID, SPID AS SessionID, CurrentTime AS DateQueried, NTUserName AS Username, CASE EventSubClass WHEN 0 THEN 'MDX Query (Excel)' WHEN 3 THEN 'DAX Query (Power BI)' WHEN 1 THEN 'METADATA Query' END AS QueryType, CASE Success WHEN 1 THEN 'Successful Query' ELSE 'Query Error' END AS SuccessfulQuery, CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(18,3),CPUTime)/1000) AS CPUTimeSec, CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(18,3),Duration)/1000) AS DurationSec, TextData AS Query, CASE PATINDEX('%Mexico%',TextData) WHEN 0 THEN 0 ELSE 1 END AS MexicoMarket, CASE PATINDEX('%Colombia%',TextData) WHEN 0 THEN 0 ELSE 1 END AS ColombiaMarket, CASE PATINDEX('%CS4%',TextData) WHEN 0 THEN 0 ELSE 1 END AS CS4, ServerName FROM [dbo].[ASTraceTable] WHERE EventClass = 10 Once I had the view, creating the report was relatively straight forward, and can be seen below. I included metrics for number of queries by user (blurred out) which also doubled as a filter, the % split of queries for things such as Excel/Power BI, a measure of queries by timeframe, a logarithmic scaled display for queries by query duration, and lastly a split of queries by KPI. I intend to tweak these once I receive more data from the trace, but was relatively happy with the information that they were providing. Please let me know if you have any comments.

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!

Connecting Power BI to Hive

On a recent project I was tasked with importing data into Power BI from a Hive table. For those of you who are new to Azure or Big Data, Hive is a data warehousing infrastructure for Hadoop which sits in the HDInsight stack on Azure. The primary purpose of Hive is to provide data summarisation, query and analysis for big data sets. In this blog I’m going to take you through the steps and note any Gotchas so that you can connect to Hive using Power BI. Connecting to HiveAs Hive is part of the Azure HDInsight stack it would be tempting to select the HDInsight or Hadoop connector when you’re getting data. However, note HDFS in brackets beside the Azure HDInsight and Hadoop File options as this means that you’ll be connecting to the underlying data store, which can be Azure Data Lake Store or Azure Blob Storage – both of which use HDFS architectures. But this doesn’t help when you want to access a Hive table. In order to access a Hive table you will first of all need to install the Hive ODBC driver from Microsoft. Once you’ve downloaded and installed the driver you’ll be able to make your connection to Hive using the ODBC connector in PowerBI.You will need to input a connection string to connect even though it says optional. The format of the connection string is as follows:Driver={Microsoft Hive ODBC Driver};Host=hdinsightclustername.azurehdinsight.net;Port=443;Schema=default; RowsFetchedPerBlock=10000; HiveServerType=2; AuthMech=6; DefaultStringColumnLength=200;One the next screen you’ll be asked to enter a username and password. The credentials used here are not what you use to access Azure but the credentials you created when you set up the HDInsight cluster and use to login to the cluster. Click connect and you’ll be able to pull through the tables you need into Power BI. Or, if you want to be selective in what is returned, you can write a HiveQL query in the ODBC dialog. It’s also worth noting that at the moment it’s only possible to do an import of Hive Data in Power BI and not perform Direct Query, so if your data set is huge you’ll want to summarise the data or be really selective in what is returned first.

Data Data Revolution – The Results

This blog will take you through the Power BI Dashboard, Data Data Revolution – The Results, which is the product of the data collected from the demo presented in the last SQLBits conference (for further details, please check my previous blog http://blogs.adatis.co.uk/josemendes/post/Data-Data-Revolution). This dashboard provides a breakdown on the player’s preferences and performance split by different indicators. In the following video, I’ll show some of the possible conclusions we can gather from the analysis of the data.

Data Data Revolution

Following the DISCO theme, Adatis decided to present all the SQLBits attendees with a challenge based on the game Dance Dance Revolution. At the end of the game, the players were presented with two Power BI dashboards, one that streamed the data in near real time and the other representing historical data. This blog will detail the different components used in the demo.        (High Level Architecture)   The starting point The first requirement was to have a game that could run on a laptop and store the output data in a file. Based on the theme of the conference, we chose the game Stepmania 5 (https://www.stepmania.com/download/). After understanding how it worked and what type of details we wanted to capture, we adapted the program so it was possible to save the output in a TXT file every time a key was pressed. Following is an example of how the data was structured. {"Player": "0", "Row": "768", "Direction": "Left", "NoteType": "Tap", "Rating": "OKAY", "Health": "Alive", "Combo": "0", "Score": "0", "Artist": "Katrina feat. Sunseaker", "Song": "1 - Walking On Sunshine", "Difficulty": "Easy"}   Capturing player details To complement the game output, we decided to create an MVC application that had two functions, capturing the player details in an Azure SQL DB, and, upload a new Game ID along with the player details to a reference BLOB stored in an Azure Storage Container.   Sending the data to an Event Hub Since we wanted to stream the data in near real time, we needed an application that could read the data from the output file as soon as it was updated. To achieve this, we built a C# application that was sending the data to an Event Hub. To make sure we didn’t upload duplicate data, we implemented a logic that compared the last row with the previous one. If they were different, the row was uploaded and if not, the program would wait for the next input.   Distributing the data To distribute the data between the Azure SQL DB and the Power BI dataset, we used two separate Stream Analytics Jobs. The first job was using the Event Hub and the reference BLOB as inputs and the Azure SQL DB as output, while the second job was using the same inputs but having a Power BI dataset as an output. Due to the dataset limitations, we ensured that all the formatting was applied in the Stream Analytics Query (eg. cast between varchar and bigint, naming conventions, …).   Power BI streaming datasets In this scenario, the streaming datasets only work properly when created by the Stream Analytics Job. Any of the following actions invalidates the connection between the jobs and the dataset: · Create the dataset in Power BI · Change column names · Change column types · Disable the option Historic data analysis When the dataset crashes, the only solution to fix the issue is to delete and re-create it. As a result, all the linked reports and dashboards are deleted.   Representing the data By the time the demo was built, the connectivity of live datasets to the Power BI Desktop was not available, which means the live streaming dashboard was built using the online interface. It is important to note that it is impossible to pin an entire page as a dashboard when using live datasets since it won’t refresh as soon as the data is transmitted. Instead, each individual element must be pinned to the dashboard, adding some visual limitations.   The performance of the players could be followed by checking the dashboard streaming the results in near real time. The use of the word near was used several times in the blog because the streaming is limited not only by the internet connection but also by the Power BI concurrency and throughput constraints, meaning the results were not immediately refreshed. The second report was built using Power BI Desktop and was connected to the Azure SQL DB. At the end of the game, the players could obtain the following information: · Who was the winner · How did they perform during the game · The number of hits for each rating · Which direction they were more proficient

Power BI Mobile Feature Review

In March Microsoft released Deep Touch integration for iOS amongst several other improvements to the Power BI Mobile application. This blog will look at a few of those features and examine some of the areas that still need work. So far it seems Microsoft are doing a pretty good job of developing Power BI for the mobile platform and this is most apparent when they exploit some of the built-in functionality that make mobiles so handy! One of the best features of the latest iOS is 3D Touch integration and Power BI has fully grasped this bull by the horns. Using a Deep Touch, you can launch a pop-up menu offering some of the most useful features such as search and notifications but also quick access to your recently accessed dashboards.     Another big issue that the Power BI team have tackled head on is how to make rich visualisations mobile optimised. For this they have two solutions, the first being the desktop and mobile view options within Power BI desktop. Desktop view of Report Mobile view of Report  The mobile view essentially de-constructs your report and lets you drag and drop your visualisations into the mobile template. By default, this view will always be displayed when viewing the report on a mobile device unless you rotate the device into landscape mode in which case the desktop version loads. I have mixed feelings about this feature. On the one hand, I like that I can see both views but if the report were to remain in mobile view but expand to fill the horizontal space as well, this could open up a lot more opportunities for mobile reporting.  However, despite gaining some control in how the report looks on a mobile device there are some pretty major limitations for the time being. Firstly, you cannot specify different properties, such as text size, for the desktop and mobile view. This means that you would need to consider both views when creating a report that will be both mobile and desktop otherwise your visual will be sacrificed as seen above in the deliveries and fragile items cards. Another drawback is that each visual element has to be snapped to the prescribed grid and this includes the transparent grab handle/border that is used to select the visual. This border is half a grid square tall in the mobile view which means that you get a lot of white space, something you want to reduce in a mobile view.  Finally, visuals cannot be overlaid. Where I have circles around some of my cards in the desktop view, this is not possible in the mobile view.  Whilst you can add an image or logo you could not make use of any backgrounds whether they be an image or just a flat colour. Thankfully, all custom visuals will work in mobile view and any cross filtering, auto play or auto zoom features (maps) are preserved perfectly. Microsoft’s second solution is focussed around dashboards. From within the Power BI service you can arrange your pinned visuals into a mobile dashboard by switching the view as shown below.   However, the best part is that it if you access a dashboard that doesn’t already have a mobile view then the app will automatically optimise the visuals into a mobile view so you don’t have to! One of Power BI’s most notable features is Q&A – a method to query your data using natural language. Using a recognisable messenger format this feature is really well replicated in the mobile app and adds another layer of accessibility for non-techie, non-deskbound users.     A relatively new feature to Power BI is the ability to add custom links to a dashboard visual. This can be utilised quite nicely in the mobile app as it will make use of the deep linking technology in iOS so that I can launch a relevant app instead of just a web page. Here I have set a google maps URL as the custom URL for the map visual. Clicking on this in the mobile app launches my google maps app, not a webpage!    Overall I can see that the Power BI mobile team have not tried to just recreate the desktop version but have embraced the mobile platform and have made use of existing features within iOS to present a feature rich app that has a familiar feel to it. Whilst there are some limitations, my next blog will look at how to create a mobile optimised report right from the start so that your users can benefit from mobile BI straight away!

Hierarchy Navigation In Power BI

Until recently, the overall functionality of the Matrix Visual in Power BI has been limited. However, this all changed when Microsoft announced the Power BI March update, which gives users access to the Matrix Preview. This can currently be used alongside the old matrix. In this blog, I will be comparing the old Matrix and the new Matrix Preview. The updates featured in the latter are as follows: Performance improvements Drilling into hierarchies Stepped layout Cross highlighting and filtering from matrix headers and cell contents This article will only focus on the drilling into hierarchies’ functionality. Click here to find more information around the Matrix updates, along with the extra features not covered in the blog. Hierarchy Drill Through One of the visible changes in the new matrix preview it is the ability to show all the headers collapsed by default making the user experience easier when dealing with large datasets. The image below shows this new feature compared to the old and new Matrix. There is also the capability to show both levels of the hierarchy simultaneously , which is again done using the hierarchy navigation buttons as illustrated in the image below. You can also drill up and down on individual columns using the right click function as shown in the image below. The benefit of this is that it gives the user a more detailed drill down of a desired column. Further drill down options are available, for example, the ability to drill down on row category headers. In normal mode (without drill mode turned on), other datasets in other row category headers will be faintly visible. By turning on the drill down mode it allows users to works on a specific category row header in isolation. The following images show the differences in the two views. Conclusion The Matrix Preview has brought about interesting and useful tools making it more interactive. The ability to be able to drill up and down within a report particularly stands out for me. It is also worth mentioning that other features, not covered in this blog give users increased customisation when working on reports – showing how impressive the Matrix Preview is.  April`s Power BI update includes more features for the Matrix Preview. My next blog will be looking at the following two features added for Matrix Preview: Rename axis titles New matrix visual enhancements: column sorting, column resizing, and word wrap   Further Reading (Power BI Blogs) https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-feature-summary/#matrix https://powerpivotpro.com/2017/03/two-great-new-power-bi-features/

IoT Hub, Device Explorer, Stream Analytics, Visual Studio 2015 and Power BI

As we saw in my previous blog, the IoT Hub allow us to collect millions of telemetry data and establish bi-directional communication between the devices, however, more than quantity, what we need is valuable insights that will lead to smart decisions. But how can we do that? Collecting the data There are thousands of sensors we can use, depending on the purpose. If we check the Microsoft documentation we will find tutorials for the Raspberry Pi, Arduino, Intel Edison or even simulators created with .Net, Java or Node. The first step is always the creation of the IoT Hub on the Azure Portal. Next, we have to add the devices, which can either be done using C# and the IoT Hub Extension for VS 2015 or the Device Explorer. This last tool, provided by Microsoft, can easily register new devices in the IoT Hub and check the communication between the device and the cloud. Once the devices are properly configured we will need to store the data, which can be done using a SQL Azure Database.   Represent the data Now that we collected the data, we want to be able to represent it. One of the best ways to do that, is by creating some Power BI reports and dashboards, which will be populated via Stream Analytics. A good example of a similar architecture and example dashboards can be found on Piotr’s blog Using Azure Machine Learning and Power BI to Predict Sporting Behaviour. Note that on his example, he used Event Hubs instead of the IoT Hub.   Insights and actions Let’s imagine a transportation company is collecting the telemetry from a food truck equipped with speed, location, temperature and breaking sensors. In order to assist their delivery process, they have a report being refreshed with real time data that triggers some alerts when certain values are reached. One of the operators received an alert from the temperature sensor, and after checking the dashboard he realizes the temperature is too high and it will affect the quality of the products being transported. Instead of calling the driver and make him aware of the situation, because the sensors are connected to an IoT Hub, he can simply send a command to the sensor and reduce the temperature.   More info: https://github.com/Azure/azure-iot-sdks/commit/ed5b6e9b16c6a16be361436d3ecb7b3f8772e943?short_path=636ff09 https://github.com/Azure/connectthedots https://sandervandevelde.wordpress.com/2016/02/26/iot-hub-now-available-in-europe/ https://powerbi.microsoft.com/en-us/blog/monitor-your-iot-sensors-using-power-bi/ https://blogs.msdn.microsoft.com/mvpawardprogram/2016/12/06/real-time-temperature-webapp/

Power BI Streaming Datasets–An Alternative PowerShell Push Script

I attended the London Power BI Meetup last night. Guest speaker was Peter Myers On the topic of "Delivering Real-Time Power BI Dashboards With Power BI." It was a great session. Peter showed off three mechanisms for streaming data to a real time dashboard: The Power BI Rest API Azure Stream Analytics Streaming Datasets We've done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I'd never got round to looking at them in depth. Now, having seen them in action I wish I had - they are much quicker to set up than the other two options and require little to no development effort to get going - pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost. You can find out more about Streaming Datasets and how to set them up here: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-real-time-streaming/ If you create a new Streaming Dataset using 'API' as the source, Power BI will provide you with an example PowerShell script to send a single row of data into the dataset.  To extend this, I've hacked together a PowerShell script and that loops and sends 'random' data to the dataset. If you create a Streaming Dataset that matches the schema below, the PowerShell script further below will work immediately (subject to you replacing the endpoint information). If you create a different target streaming dataset you can easily modify the PowerShell script to continually push data into that dataset too. I’ve shared this here, mainly as a repository for me, when I need it, but hopefully to benefit others too. Streaming Dataset Schema Alternative PowerShell Script Just remember to copy the Power BI end point to the relevant location in the script. You can find the end point (or Push URL) for the Dataset by navigating to the API Info area within the Streaming Dataset management page within the Power BI Service: # Initialise Stream $sleepDuration = 1 #PowerBI seldom updates realtime dashboards faster than once per second. $eventsToSend = 500 #Change this to determine how many events are part of the stream $endpoint = "[INSERT YOUR ENDPOINT HERE]" # Initialise the Payload $payload = @{EventDate = '' ; EventValue = 0; EventSource = ''} # Initialise Event Sources $eventSource = @('Source1', 'Source2', 'Source3') # Iterate until $eventsToSend events have been sent $index = 1 do { # Update payload $payload.EventDate = Get-Date -format s $source = Get-Random -Minimum 0 -Maximum 3 $payload.EventSource = $eventSource[$source] $value = Get-Random -Minimum 0.00 -Maximum 101.00 $payload.EventValue = $value # Send the event Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload)) # Report what has been sent "`nEvent {0}" -f $index $payload # Sleep for a second Start-Sleep $sleepDuration # Ready for the next iteration $index++ } While ($index -le $eventsToSend) # Finished "`n{0} Events Sent" -f $eventsToSend

PowerBI Optimisation P3– Extracting and Source Controlling PowerBI Data Models

Source Control – once seen as “something proper developers do” – has been an integral part of the way business intelligence developers work for a long time now. The very idea of building a report, data model or database without applying some kind of source control actually pains me slightly. However, there has been a push for “Self-Serve” reporting tools to strip out anything that looks remotely like a technical barrier for business users - This includes the ability to properly track changes to code. We find ourselves in a very familiar situation – versions of PowerBI desktop files are controlled by including version numbers in file names. I’ve seen several copies of “Finance Dashboard v1.2.pbix”. This is obviously dangerous – who’s to say that someone didn’t open up the file, edit it and forget to increment the name. Once a file has been shared, there’s no controlling what changes happen at that point. If this happened to an SSIS package, for example, we would still be able to perform a code comparison. This would highlight differences between the two packages so we could accurately see what caused the changes. This is not currently possible with PBIX files in their entirety. We can, however, compare the data model behind the file. This allows us to check for changes in business logic, amendments to DAX calculations, additions of new fields etc. If the performance of two PBIX files different drastically even if they were meant to be the same “version”, then this is a reasonable starting point! Extracting the Data Model from a PowerBI PBIX File Firstly, we need to extract the JSON that describes the Tabular Model embedded model (technically, this is TMSL, the tabular model scripting language, but it’s still JSON…) We can do this by connecting to the model via SSMS. I’ve talked about the steps required to do this here. So, assuming you have found your temporary SSAS port and connected via SSMS, you should see something like this: As we would with any other Tabular model, you can right-click and script out the database as so: If we do this to a new query window, you’ll see the various JSON objects that describe your PowerBI model: This script contains the details for all tables, attributes, DAX measures etc required for your data model. Comparing PowerBI Data Models What if someone has been using a specific version of my PowerBI desktop file, but they’ve modified it and it has stopped working? For a Tabular model, I’d compare the model definition to source control which will automatically highlight any changes. Now that we can script out our PowerBI model, we can apply the same principles. Say, for example, I make a couple of changes to my sample PowerBI report and want to figure out how it has changed compared to a baseline script I exported previously. The easiest option is to use a tool like Textpad – here you can compare two text documents and it will highlight any differences it finds between the two. For example, I changed the name of a table and removed a column, the text comparison highlights this change as below: I can now be confident that if someone sends me a PBIX file, I can check to see if there are any data model changes without having to manually eyeball the two side by side. This alone is a huge leap forward in manageability of models. The next step would be to add this file to an actual Source Control provider, such as Visual Studio Team Services. This tool is free for the first 5 users and can be used with Visual Studio 2015 Community Edition – which is also free! Essentially you would add this exported script to your source control directory each time you updated the model. By checking in your new model, you can compare previous versions, much like with the TextPad editor above. Final Thoughts In the end, this isn’t real, true Source Control. If you make a mistake, you can only view what the previous configuration was, you cannot roll back code directly into your PowerBI model. It is, however, a step towards managing PowerBI with a bit more discipline and rigour. I don’t see this as a huge drawback as rumours on the wind are hinting at larger steps in this direction coming with future releases. Let’s hope we’re not having to work around these problems for much longer!    

PowerBI Optimisation P2–What’s using all my memory?

If you're a regular user of PowerBI, you're probably aware of the size limitations around datasets and it's very likely you've hit them more than once whilst writing reports on top of large datasets. It's difficult to see where size savings can be made directly through PowerBI, but we can use traditional tabular optimisation techniques to help us! For those not in the know, a single dataset can be up to 1Gb in size, with excel files limited to 250mb. Each user also has a storage limit as follows: Free users have a maximum 1 GB data capacity. Pro users of Power BI Pro have 10 GB maximum capacity. Pro users can create groups, with a maximum 10 GB data capacity each. For more information about the limits themselves and how to view your current usage, there's PowerBI blog about it here: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-manage-your-data-storage-in-power-bi/ But what if you're hitting that 1Gb data limit? There's very little within PowerBI itself to help you understand which tables are the largest, where you could make some savings, or generally anything about your model itself. The answer is to connect to the model via SSMS and take advantage of the Tabular system views, as described here. What determines Tabular model size? It’s worth discussing this briefly before going into the details. Put very simply, the XVelocity engine used by the tabular model will hold more data if there are more unique values for a column column. The key to avoiding large models is, therefore, to avoid columns with huge numbers of lots of distinct values. Text fields will generally be pretty bad for this, although there are common design patterns to avoid the worst offenders. A simple example is to look at a DateTime column – this combination of date and time means that each minute of each day is a unique value. Even if we ignore seconds, we’re adding 1140 new, distinct records for every day within the system. If we split this into two fields, a date and a time, this problem goes away. Each new date adds just a single record, whilst we will never have any new hours and minute combinations, so that’s a controllable field. There are a few techniques to avoid these problems if you find them, I’d advise heading over to Russo & Ferrari for some general tips here and some more detailed techniques here. Accessing Memory Usage Data So - following the above instructions, connect to your data model and open a new DMX query: Here you can use SQL syntax to query several DMVs behind the model - not all of them will be relevant in the cut-down tabular instance that PowerBI uses but there is one in particular that will help us manage our model size - DISCOVER_OBJECT_MEMORY_USAGE. Admittedly, on it’s own this is pretty incomprehensible. We can filter down the results slightly into something that makes a little sense, but you’ll generally get a big list of model entities with numbers against them – OK as a starter but not great as an actual model optimisation tool: Stopping here we would at least have a hit-list of the worst-offending columns and we can use this to start tackling our model. But there are much better ways to approach this problem! Tabular Memory Reports There are several free tools made available within the SSAS community for people to analyse their current SSAS memory usage. These tools simply query this same data but apply a bit of data modelling and make the data much more accessible. For straight tabular, I would tend to use Kasper de Jonge’s old excel spread, which pulls in data quite reliably, however there is an updated PowerBI Model found here. However, this doesn’t play nicely with the PowerBI flavour of tabular just yet, so I would advise using the SQLBI.com Vertipaq Analyser. Following their instructions and pointing it at my temporary tabular instance, we can refresh successfully and use their categorisations to explore the model. I’ve added some conditional formatting to help see where the issues are. I can see, for example, which of the tables in my model are the worst offenders, and what’s causing it: Interestingly the Customer dimension is pretty huge in my example. It has a lot less data than my fact but the dictionaries required are pretty hefty. Dictionaries are built using string lookups and are heavily affected by high volumes of unique values – so I can presume I’ve got some pretty big text strings in this dimension. Looking at the Column breakdown, I can see where the offenders are: This tells a slightly different story – my main offenders are from one of the hidden date dimension tables (A sign that relying on PowerBI’s inbuilt date functionality can be a memory drain) and the Sales Order Number – a unique identifier for my fact, obviously this is going to have a large number of distinct values. The other columns we can do more about – Email address is the next offender. We can assume each customer, of all 18,000 will have a unique email address. However, it’s very rare that we would want to do analysis on the email address specifically, this is a good candidate to remove from the model. At the very least, we could consider keeping only the domain which will yield much fewer unique values.   Hopefully the above will help you move forward in reducing your PowerBI data model size – I’ll be posting about Performance Analysis & Source Control over the next couple of days.

PowerBI Optimisation 1 – Connecting Via Management Studio

I recently gave a talk to the London PowerBI UserGroup and I kicked things off with a confession - "I don't do much report building in PowerBI". Perhaps an odd way to qualify myself to speak to that particular audience. But I am, however, a cloud solution architect - I spend my time designing large scalable cloud systems to process vast amounts of data and PowerBI is a common tool used on top of these systems. Why then, do we accept the lack of controls available within PowerBI? Given any other end-user system I'd want to know about performance bottlenecks, about data model efficiency and, more than anything, I'd want it in source control. First and foremost, the talk is available here. The key to it all, is realising that PowerBI Desktop, when running, starts a SQL Server Analysis Services processes in the background. It doesn't just use the same engine as Tabular, it literally runs tabular in the background without telling you. Open up a PowerBI Desktop file and, after you've seen the "initialising model…" window, you'll see this process in the background - one for each PBID session. So - if the model is using Tabular in the background, we must be able to actually connect to the model! First - Find your Temporary SSAS Port There are two straight forward ways we can achieve this: 1. By far the easiest, is to open up DaxStudio if you have it installed. When you open DaxStudio, it gives you a Connect window, which lists all of the PowerBI processes you have running in the background, as well as any Tabular services: When you connect to a PBI file here, you'll see the Port listed In this case, my port is 5524 -be aware that this will change every time you open PowerBI Desktop, so you can't hardcode anything looking for your "powerbi port". 2. Alternatively, you can find the "msmdsrv.port.txt" file related to your specific instance. Take a look in your user appdata folder, you should find a Microsoft/Power BI Desktop/ folder with some analysis services details: C:\Users\<YourUser>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\ You'll see an instance for each of your PBI Desktop instances, I've only got one at the moment: Inside this folder, in another folder called "Data", you'll find the file we're looking for: Opening this file, we see: Pretty straight forward, and no DAX required. Obviously if you have multiple instances, you'll need to figure out which of these relates to the instance you're after. Connect via SSMS Now that we know our port, we can simply open up management studio, connect to analysis services and enter "localhost:" and the port number from earlier.   Once connected, you'll see a model connection - each PBIX file will have a GUID for this instance, but you can drill down and see the objects underneath, exactly as you would with a Tabular model: You can now write queries, browse the model and basically treat it as a Tabular instance. The Database itself will use a generated GUID, and several internal tables will do the same - you can see above that a hidden data table has been created for every datekey included in my model. We'll discuss the applications of this in my next post - namely how this unlocks performance tuning, monitoring and source control.

Power BI Maps Handling Duplicate City Names

The Bing map engine behind the map visualisation in Power BI is very intuitive allowing users to provide textual data such as City or Country or Postcode to map metrics, instead of just latitude and longitude as most other applications do. However one thing which is not immediately obvious is how to get around the issue of duplicate City/Town names. In this blog I will explain how to map your metrics when your data source contains duplicate cities/towns. To start with we have a simple data set with quarterly sales for 6 different cities based in 5 different states which is being loaded from a CSV into Power BI. Straight away you can see that we only have 2 distinct city names.   As soon as we try to map the sales data by city, we get an obvious problem all of the Bristol sales are being assigned to Bristol, England, while the Georgetown sales are appearing in Guyana. Adding state to the Location field does nothing to help the problem as Power BI only reads a single input in the Location field. So the solution is to create a new column containing both City and State data. To do this you need to complete the following steps: 1. Click “Edit Queries” 2. Select the data source in question. 3. Select the two or more columns which contain the data we want to merge eg: City and State      -If additional geographical data is available such as Country then this can be included in the merged column. 4. Navigate to the "Add Columns" menu and select "Merge Columns" 5. Choose the separator value and name the new column For simplicity I have just called this “Merged” and separated the values using only a space. Once the new column has been created it can be dropped into the Location field of the map visualization. As you can see from the screenshot below I now have 6 data points, showing all three variations of Bristol, and all three variations of Georgetown. One final tip, is to ensure you have set the Data Category value for the column in question.  In this case I have set the Data Category to City to help Bing identify the type of data I believe I am providing it. The only problem with this, is if you set the Data Category value incorrectly no data will be displayed as shown in this final screenshot where I have changed the Data Category to “Continent”

Setting Up The Power BI Analysis Services Connector

The Power BI Analysis Services Connector is used in order to expose a Tabular model to Power BI allowing end users to consume data from the model directly for building of reports and ad-hoc analysis. The setup of the connector is very straightforward however you will should bear the following in mind - Only Tabular models are supported with the connectors – you will not be able to use this to enable reporting from a multidimensional database. - The Analysis Services Connector performs best if it is hosted on the same server that hosts the Tabular model. - The speed of the internet connection between the server running the Analysis Server Connector and the Power BI service is crucial to performance. - You can’t run the Analysis Services Connector on a server also running either the Data Management Gateway or the Power BI Personal Gateway. Installation Steps 1. Download the connector from http://www.microsoft.com/en-us/download/details.aspx?id=45333 2. Run the Analysis Services Connector Setup Program on the machine hosting the Tabular model. 3. Once the installation has completed you will be given the option to launch the connector     4. Enter the login details required to connect to Power BI 5. If the details are correct you will see the below screen: 6. Enter the account details required to connect to the Tabular Instance – clearly this needs to be an account with access to read from the Tabular model. 7. Give the connection a suitable name and a friendly error message to be displayed to users in the case that the connection fails This should complete the wizard, the next step is to log onto the Power BI site – if all has gone well you should see the model as per the below.