BI-RoundUp – Power BI (Drill up, Drill Down Mobile App – USA Facts on Financial Data)

Here is the weekly BI-RoundUp, not too much going on this week, which gives me a little time to breathe!

Power BI – Drill Up, Drill Down on Mobile App

The Power BI Mobile App has been updated to so that now it is a lot easier to drill up or drill down on the visuals on the mobile platform.

Next on the backlog is the drill through which I am interested to see how that will work.

You can find the details here: Drill Down & Up in Power BI Mobile apps

Power BI – USA Facts on Financial Data

As you can see above there is a blog post that relates to providing a comprehensive view of the combined US federal, state and local governments’ revenues and expenditures.

There is quite a lot of details in this report and if you are interested in more details please click on the link to the blog post below.

USAFacts breaks down government financial data with Power BI

Azure Analysis Services or SSAS Tabular 2017 – How to put in Custom TSQL Server Queries using the SQL Server Window

I have recently been working on SSAS Tabular 2017 for the current customer I am consulting at.

Along with this I am starting to see that Power BI Premium features are appearing to lean more towards SSAS, so I personally feel that getting into SSAS Tabular 2017 is a great way to understand and work with a similar version of Power BI Premium, which from what I can personally gather is leaning more towards Azure Analysis Services. This post also will apply to Azure Analysis Services.

Enough waffling, what this blog post will cover is how to use a Custom TSQL Server Query in SSAS Tabular 2017 and leveraging the SQL Server Database Window as shown below.

Example

In this example I am going to complete the following:

  • Connect to a SQL Server Database in SSAS Tabular
  • Create a New Expression using the Advanced Editor
  • Edit the Expression using the SQL Server Database window to edit my TSQL Query
  • Add the table to the Model
  • Creating the Data Source Connection in SSAS Tabular

I have got my SSAS Tabular Project created.

Next, I connect to my SQL Server Database as shown below.

  • I right click on Data Sources and select Import From Data Source…
  • I then select SQL Server database from the Get Data window
    • Then click Connect.
  • I then put in my SQL Server details
    • Then I click Ok
  • I then get prompted for the Credentials to use
    • NOTE: I personally set up the SSAS Tabular Service account, to use a domain account. And this same account then has access to the required data sources.
    • I then click Connect
  • Now I have created my data source connection.

Creating a new Expression

In the steps below, I am going to show how to create a new expression using the Advanced Editor, which will then enable me to use the SQL Server Database Window.

  • I right clicked on Expressions and then clicked on Edit Expressions
  • Once the Query Editor Window opens, I then clicked on Query, New Query and selected Blank Query
  • On my table called Query1, in the Query Editor, I click on View and then selected Advanced Editor
  • I now put in the following where it says Source
    • CODE: Sql.Database(“mydatabaseserver”, “mydatabase”, [Query=” Select * from MyTable
      “, CreateNavigationProperties=false])
    • What the Sql.Database represents is the following:
      • “mydatabaseserver” – Change this to your SQL Server Database Name
      • “mydatabase” – Change this to your Database Name
      • The TSQL Query
        is in the section highlighted in Blue above.
    • NOTE: Ensure that in the Query section above it is a valid query to any table in your database.
  • Once that is done you should then I then saw my table loaded in the Query Editor

Editing your TSQL Query using the SQL Server Database

Now that I have created the expression, I can now edit the TSQL Query using the Window as shown below.

  • On my table under the Applied Steps I can click on the cog next to Source
  • And this now brings up the SQL Server database window, where I can now enter my TSQL Query, or view the query I created previously.
    • I then clicked Ok

Adding the table to the data model

One of the things that I had to learn when using SSAS Tabular with the Visual Studio Editor is that I NEVER know how to load an expression into the data model.

This did cause me some frustration, until I realized how easy it is. So, this is for people who have been looking for it before.

  • Right click on the table, as with my example was Query1 and select Create New Table
  • Once that is done I then clicked on Home and then Close & Update
  • This will then load the table to the data model.
  • So now I have got my data loaded from my TSQL syntax into my data model.

Conclusion

As I have demonstrated above, there is an alternative way in which you can put in Custom Queries when using SSAS Tabular 2017.

If there are any questions or comments please leave them in the section below.

How I keep ontop of Power BI Support Issues

When working with Power BI on a daily basis, as well as chatting to various customers or users, one thing that I have missed in the past was to be aware of any Power BI Support Issues that are happening.

So what I did was to build a Power BI Support Issues page, which I refresh daily, and then using the Subscribe option, this allows me to get an updated view of any Power BI Support issues, so that I am always aware.

Using Power BI Desktop with the improved Web page analytics I created my own report which I then uploaded to the Power BI Service.

After which I then put it as a publish to web, so that I could view it on my website.

Not only that, but I have also enabled the Email Subscription, which will enable me to get it into my Inbox Daily.

And once I click Save and Close I get the confirmation that the subscription will be emailed the next time the data is refreshed.

 

 

 

This is my Power BI Support page that I get every day, which keeps me informed of any issues that I need to be aware of.

Here is a link to the PBIX file if you wish to create the same page for your Power BI Tenant

Power BI Support Issues.pbix

As always if there are any comments or suggestions please leave them in the comments below.

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev, Test & Prod)

As I am sure a lot of people noticed if you now go into the settings of your dataset there is an additional option which says Parameters.

What I wanted to test out was to see if I could change the connection strings using the Parameters in the Power BI Service.

The advantages of this working in my opinion is the following:

  • I would be able to change the underlying data source connection without having to redeploy my Power BI desktop file.
    • This would work particularly well if I am deploying reports from Dev (Development environment) to Test (Testing environment) to Prod (Production environment)
  • If I was looking for disaster recovery options, this too could work. I would need to ensure that I have an On-Premise Gateway configured for my Disaster recovery servers, and once again I could change the connection strings.

One thing to take note of, is that this currently only works on Imported data sources and not DirectQuery or LiveConnection

Data Preparation

What I did in order to test the changing of the connection strings is I created 2 Sample Azure SQL databases.

Not only was this very quick to complete, it was also extremely cheap with it costing $6.99 AUD per month for me test my solution. As part of the installation I installed the Adventure Works databases

Then in order to ensure that I was looking at the different servers, I updated the Products table so that for the Colour Black it was changed to “Black-Svr1_DW” and “Black_Svr2_DW”

Creating the Power BI Report

I created my Power BI Report, where I first connected to the Azure SQL Database and imported the SalesLT.Products table from Server1_DW

NOTE: As you can see above my colour has the name of “Black-Svr1_DW”

I then created a second table, which runs the TSQL command @@ServerName, this is to ensure that not only could I get back the relevant server name, but that I am indeed not masking the results for this blog post (Honesty is the BEST policy )

Whilst in the Power Query Editor I then created 2 Parameters, one for the Server Name and another for the Database Name

  • I then put in the default values, in order to connect to the correct Azure SQL Database.
  • Next, I went to my initial Query and clicked on the Source Gear Icon
  • Next in the SQL Server Database Window I clicked on the drop down next to server and selected Parameter
    • I selected Server Name
  • And likewise, I did the same for Database and selected Database Name
  • So that once complete I had both parameters selected.
  • I then clicked Ok, and my data loaded.
    • I did the above for my Server Details query also.
  • I then imported my data into my Power BI Desktop file.
  • My final step was to load the data and create a report, in which I could see the Server Details from the @@Servername output, as well as the SalesLT.Product table details as shown below.
  • I then uploaded my PBIX file to the Power BI Service

Modifying the Parameters in the Power BI Service

I now wanted to modify the parameters in the Power BI Service and see if they would work as expected.

  • Now that I had my report uploaded to the Power BI Service, I went into Settings and then clicked on Datasets.
  • I then clicked on the arrow next to Parameters and modified the parameters so that it would use Server2_DW
    • And then clicked Apply
  • I was then prompted to update my credentials to the new database
    • I clicked on Edit credentials and put in valid credentials for Server2_DW
  • I got confirmation that my credentials had updated successfully.

Testing the updated parameters

The final step was to refresh the data and see if it now was getting data from Server2_DW

  • I went to the dataset, clicked on the breadcrumbs and selected “Refresh Now”
  • Because I am using Azure SQL Database, the refresh happens really quickly.
  • I was very excited to see that it now is indeed getting the data from Server2_DW

Conclusion

As I have demonstrated there now is the flexibility within the Power BI Service to leverage the parameters in order to quickly and easily change data source connections to underlying datasets.

If there are any questions or comments please leave them in the section below.

Why are my Power BI Persistent Filters Not Working – Quick Tip

I was recently testing out the new Persistent Filters in Power BI and initially it would work with some reports and not with other reports. I even looked at imported data models, vs DirectQuery/Live Connection reports and I could figure it out.

So, I reached out to the very helpful people in the Power BI team, and they quickly showed and explained to me as why it was not working.

As explained in their blog post, it was my report having Custom Visuals.

For my report where I could not get the Persistent Filters to work, the best way for me to understand why it was not working, is to hover over the “Reset to default” item in the report.

As you can see below, it gives a clear indication that there are Custom visuals that are not currently supported.

Whilst on another report where I have no Custom visuals, when I hovered over the “Reset to default” I got the following Tooltip

Which indicates that I will be able to use the Persistent Filters due to the Tooltip letting me know I am using the authors default view. And no indication of any custom visuals.

Future Updates for Persistent Filters

As indicated in the comments section of the blog post the Power BI team is working to get Custom Visuals supported with Custom Visuals. Which I am certain will be sooner than later.

In my particular report I was using the Chiclet Slicer from Microsoft, and in my mind, I thought that this was one of the default slicers of which it is not.

As with my current testing as far as I can tell as at 19 Mar 2018, this is the only Slicer that is currently supported.

As always if there are any comments of questions, please leave it in the comments section below.

BI RoundUp – Power BI (March Desktop Update, Share Content with anyone via email, Persistent Filters, Mixed Reality App, Service & Mobile Summary for Feb, Data & BI Summit)

After getting back from the MVP Summit in Bellevue, which was an incredible experience the Power BI team has been extremely busy getting some great new features out the door.

Which means there is quite a lot to cover in this week’s BI-RoundUp.

Power BI – March Desktop Update

Once again, I must repeat what I have said before, the Power BI team has once again released some amazing new features. Which I think is continuing to make Power BI such a compelling and amazing product to not only use, but to show potential customers.

As I do with every month’s update, I will highlight the features which I feel are worth mentioning.

Reporting

Without a doubt the Report Tooltips is a game changer. Not only does it give an almost drill through type of feature, but it is context aware. So, when you enable the tooltip it will take into account all the filters that are being applied and apply that to the tooltip. This allows users to gain more insights without having to navigate to another page.

Bookmarking is now generally available, which means that it is now fully supported. As well as you will no longer need to go into the Preview Features in Power BI desktop to enable the feature.

Additional formatting improvements for this month is for the table and matrix, where you can now modify the display units to show it in a more meaningful way. As well as you can set the precision in terms of decimal points. One thing to note is that it will override the settings that were defined in your model.

You can now turn off the Visual headers for the entire report. This is particularly useful when you want to embed the reports. This will remove the Context Menu’s, the Focus Mode, drill down, and the option to pin the visuals. This could potentially also make the pages a read only style report.

When you are now adding in new visuals into your Power BI report, it will now look at the existing visuals and place it where it thinks it should logically go. This is great if there are already a few visuals where it can save you some time placing the visual where you wanted it to go.

Custom Visuals

There are a whole host of new Custom Visuals which are detailed below.


Mapbox visuals which have a lot of amazing options for viewing maps

User List by CloudScope

Timeline by CloudScope – Similar to a Twitter like feel

KPI Chart by Akvelon

R DataTable

Outliers Detection

Data Insights by MAQ Software

Dumbbell Chart by MAQ Software

Clustering using OPTICS by MAQ Software

Data Connectivity

There have been improvements to the SAP HANA connector.

Both the SAP BW Direct Query and Azure Analysis Services are now generally available.

Other

The error reporting has now been integrated with Windows Error reporting. What this means is that it now will contain more information. You can also view all your previous errors. As well as if you do not have the time to send the error report right away you can send it later.

You can find all the detailed information in the blog post: Power BI Desktop March Feature Summary

Power BI – Share Content with anyone via email

You can now Share your Dashboards, Reports or Apps with any email address including Outlook.com (Live.com, Hotmail.com) and Gmail. This is really great when you have users or organizations that use the public hosted email addresses.

One word of caution, is to ensure that only the people who are required to send emails externally have the requirements. And other people that do not require it, have it disabled.

You can find all the details on how to share here: Share your Power BI content with anyone by email

Power BI – Persistent Filters

One of the time-consuming tasks in the past was, when you went back to a report, if there were multiple slicers and filters, you would have to reset them each and every time you went back to the report.

This is now a thing of the past with persistent filters.

Persistent filters will now remember your Filters, Slicers, Drill downs and sort order for your reports. So, when you go back to the report it will remember them.

This is a great feature and will make the reporting and analytics experience that much more enjoyable and quick to gain the insights you require.

All the details are here: Announcing Persistent Filters in the Power BI Service

Power BI – Mixed Reality App

With the use of the HaloLens and the Mixed Reality App there now is the capability to be able to Pin Power BI reports to physical locations or to have it tagged within the App.

What this means is that if there is a user on the factory floor, they can have a Power BI report pinned into the machine, so without having to look at their phone or open a laptop they can gain insights as they are moving around.

I think that this opens a whole new avenue and the things that you can imagine are almost endless.

The blog post details are here: Power BI for Mixed Reality app now available in Preview

Power BI – Service & Mobile Summary for Feb

Whilst some of the new features have been covered before here is a quick overview.

There is now the facility to automatically install apps.

A new feature that I was not aware of is now additional
Power BI Premium Capacities for P4 and P5, currently only available in West US and Southeast Australia.

Organizational Custom Visuals can now be controlled by the Power BI Admin

The February update to the On-Premise Data Gateway which has the latest Mashup Engine, and can also refresh data sources from both On-Premise and Cloud.

And finally, updates to the mobile app where you can now share from the Mobile App, you can drag your finger across your screen to get the tooltips and improved hyperlinks

All the details are here: Power BI Service and Mobile February Feature Summary

Data & BI Summit in Ireland

The Data & BI Summit in Ireland will be happening from 24 – 26 April 2018.

They have recently announced the keynote speaker John Doyle, Senior Director of Product Marketing on the Cloud & Enterprise team at Microsoft.

This looks to be a great conference and would be well worth attending if you are able to.

All the Summit details are in this blog post: Data & BI Summit announces keynote: The Next Era of Analytics with Microsoft Power BI

Power BI InfoGraphic Update – Mar 2018

There have recently been some changes to Power BI with regards to the new features which I will detail below. Which I also have updated in my InfoGraphic

In the latest version of both Power BI Desktop and the Power BI Service, there now is the new capability to “Ask a question” and Sync Slicers.

You can find more details with regards to these two new features below.

Ask a Question

Sync Slicer

As always you can get the updated copy of the complete InfoGraphic here: Power BI InfoGraphic

If there are any questions or comments please leave them in the section below.

BI-RoundUp – Power BI (Microsoft Leader in Analytics & BI – Push Apps to Users, On-Premise Data Gateway Update, Developer Update)

It is already the start of March, where has this year gone?

I always look forward to the first week of every month when the imminent Power BI Desktop release is about to happen. But with that being said there are some updates already.

Let’s dig in

Microsoft Leader in Analytics & BI

As you can see above, Microsoft is clearly ahead in the Completeness of Vision and I would say an extremely close second in the Ability to Execute.

This is why I love working in the Power BI space, continually innovating as well as watching the competition.

All the details with regards to the Gartner Magic Quadrant are here: Gartner recognizes Microsoft as a leader in Analytics and BI platforms for 11 consecutive years

Power BI – The ability to push Apps to users

One request that I have seen both in the Power BI Community as well as from users that I engage with, was the capability to be able to push an app to users.

This has now been delivered!

I know that this will make the data exploration, as well as data analysis easier and seamless for users.

You can find more details here: Automatically install apps

Power BI – On-Premise Data Gateway Update

The new feature which now allows for the combination of cloud and on-premise data sources to be refreshed is a game changer. In the past there had to be a work around, or to try and manually bring data down from the web. Now this can easily be achieved with the latest version of the On-Premise Data Gateway.

Along with this there is also Improvements for DirectQuery over SAP HANA.

As well as this version gets the February 2018 Mashup Engine version.

All the details are here: On-premises data gateway February update is now available

Power BI – Developer Update for Feb

As you can see from above there is always a lot of work that goes into the Power BI Development space.

Under Embedding capabilities, it is great to see even faster loading times, mobile layout support for when an embedded app is being viewed on a mobile device. As well as visual-level filters which allow visuals to be filtered via the API.

Under Automation & Life-cycle, you now can use the Custom Data function in Azure Analysis services, Power BI Premium capacity now also has an API which also allows you to clone a workspace, connect to a customer’s data source and assigning a customers cloned workspace to dedicated capacity.

Along with this having the Query Parameters in the API means that you can now change the connection string for your report, change a query parameter and measure parameters. Also more data source connections.

Under Power BI Embedded is now generally available for the Government Cloud Community. And finally, you can now use the Azure Monitor Resource Metrics, which will allow you to see in real-time data on the status or load of your Power BI Resources. This is great if you want to understand and view how your resources are being used.

All the details are here: Power BI Developer community February update

How to paste a screenshot/image into the Power BI Community Forum

In the past when I was answering questions I always had to get a screenshot, save it into an image and then upload the image.

That was a real pain in the ……!

I have to give full credit to fellow MVP Wyn Hopkins who made this discovery, you can find him on Twitter or LinkedIn

How to paste a screenshot/image

  • The first thing I do is to make sure I am signed into the Power BI Community.
  • Once I am signed in and about to answer a question I will have the option to upload a photo as shown below.
  • Next, I make sure that I have got my screenshot into my clipboard
    • Quick Tip: I use OneNote for my daily tasks and use the OneNote Screen clipping tool. The biggest reason is that when using the Screen clipping tool, you do not lose any context menu’s that you have on your screen. It is really useful.
  • I then click on Photos which then displays the following screen shown below.
  • I first click on the area for an image above, just to make sure it has got focus.
  • I then press CTRL + V (Paste) and then my screenshot is then pasted into the window, once again as shown below.
  • And I then click Done
  • Once it is uploaded I can then see it in my post

I do hope that this tip will assist people in the Power BI Community, and help them upload more images, which often really helps assist people trying to answer questions.

And finally, here is an animated GIF showing how it all works.