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.

BI-Roundup – Power BI (Service & Mobile Update for March – Power BI is Hiring – Edit the Linguistic Schema for Q&A)

Here is the weekly BI-RoundUp.

Power BI – Service & Mobile Update for March

There were quite a few updates in the Power BI Service for March which was awesome.

There are now persistent filters, so that users can keep their own filters that they select on each report, which now works on both the Web and Mobile App

Organizational Custom Visuals are now Generally available.

Power BI has another certification which is HITRUST CSF certification

There is now also the mixed reality app using the Halo

The March Gateway update is available, which now not only includes the number, but it also shows the Month of the current update you have installed.

And finally, there is B2B support on the mobile app, so if you have an app shared with you from another tenant you can now see it in the mobile app.

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

Power BI – Is Hiring

As you can see above the Power BI team is hiring. And quite a few roles at that, which I personally think is fantastic because it means that we can get more features quicker.

If you are interested you can find all the information here: Power BI is hiring!

Power BI – Edit the Linguistic Schema

There is a comprehensive blog post from Will Thompson in which he goes into great detail on how to edit the Linguistic Schema and enable your Q&A to answer more questions with additional synonyms and phrases.

All the details can be found here: Editing Q&A linguistic schemas

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.

BI-RoundUp – Power BI (April Desktop Update)

Here is the weekly BI-Roundup, and with the release of the April Power BI Desktop update, there are always some new features to explore.

Power BI – April Desktop Update

Reporting

As with the image above there are some additional options for Q&A Explorer, which will enable your end users to be able to explore Q&A with some questions already added into the Power BI Model.

In order to enable this, you can use the new Button feature and select the Q&A button

You can also set additional synonyms for report page details under the page information.

 

 

 

 

A new capability that was added to Power BI was buttons that can now trigger certain events. As an example, if you use the Q&A button this will bring up the Q&A Explorer. There are also a whole host of additional options on how you can format and control the trigger buttons.

As shown above, you now can control the chart line styles for Combo charts, which has been previously enabled for other chart types.

There has also been sort improvements for visuals which is based on the following conditions.

  • If in your visual, it has a Sort-By column configured it will use this by default.
  • If you do not have the Sort-By but have a Date column, it will then sort by the Date column
  • Lastly if you have neither of the above it will sort by the measure descending.

Analytics

What the Linguistic Schema is, is a YAML format in which you can add phrasing, synonyms or customize the language for Q&A. There will be more details going into depth with regards to the linguistic schema.

There is a new DAX funcation called COMBINEDVALUES, and what this does is to enable you to concatenate multiple columns together. Because it is a DAX function it means that this be a measure. As a result when you are using DirectQuery it will be able to better translate this back to the source system.

Likewise, the COMBINEDVALUES function can make it easier to combine multiple values together in a measure format.

Custom Visuals

Organizational Custom Visuals are now generally available.

Overview by CloudScope custom visual

Icon Map by Altius

Hexbin Scatterplot

Data Connectivity

Adobe Analytics Connector is now Generally available

You can now specify a Custom Port when connecting to SAP HANA

There is a new connector for Microsoft Dynamics 365 Business Central data source.

Here is a link to the actual blog post if you want to get more details: Power BI Desktop April Feature Summary

BI-RoundUp – Power BI (Business Applications Summit – HITRUST CSF certification)

Here is the weekly BI RoundUp, which I feel is the quiet before the awesome Power BI storm which will be hitting us next week.

Power BI – Business Applications Summit

Finally they have announced the dates for the Microsoft Business Applications Summit, there is not much more details as it currently stands. All that is available is to sign up for updates which I have already done.

You can find the details here: Microsoft Business Applications Summit

Power BI – HITRUST CSF certification

If you have data or work in areas where certifications are required, the Power BI team has been acknowledged with another certification.

This time it is the HITRUST CSF certification, which provides a highly prescriptive framework for managing a diverse set of federal and state regulations, standards, and frameworks.

You can read all about it, as well as additional details here: Microsoft Power BI achieves HITRUST CSF certification

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.

BI-RoundUp – Power BI Spring Roadmap (SSRS in the Cloud, Common Data Service for Analytics, Incremental Refresh & more) – Power BI Report Server Update – Building the Opioid Power BI Report

After last week, it appears that the Power BI team is still very busy, and after the announcement yesterday and the download of the Spring Roadmap there is a whole host of amazing things coming in the next few months!

Power BI Spring Roadmap

I will be going through the Power BI specific sections below to make it easier to understand what is coming where.

Power BI Desktop Announcements

All the details in the roadmap have almost been released, and as you can see from above Incremental refresh is coming to Power BI. Yes, it will currently start off in Power BI premium which is understandable as with current licensing for SSAS Tabular, if you want to partition your data (Which is what the incremental refresh is doing) you require an Enterprise License.

With that being said for large organizations this is going to be incredible to have their data updating in a fraction of the time.

Next is new is the Control over linguistic schema. Using the linguistic schema, the user can control the Q&A with phrasing and synonyms. Which translates to more options to people to ask Q&A questions and get the right answers.

Power BI Service Announcements

What I am showing in the image above is an SSRS (SQL Server Reporting Services) report being in the Power BI Service. That is really incredible considering people have been asking when SSRS will come to the cloud.

Now all the reporting requirements for an organization can be accessed from one central location. And not only that but with SSRS RDL reports there will be the capability to create pixel perfect reporting. This is really exciting.

A new capability is coming where you can pin items from Apps to your own dashboard. This will mean that you can create your own dashboard with items from different App sources.

There are also going to be more performance monitoring metrics for Power BI Premium so that people who run Power BI Premium are able to understand how it is performing and if there are any areas of concern.

Along with this as previously announced P4 and P5 Power BI Premium Capacities

Data source improvements where it will be easier to administer Gateways as well as being able to understand what is running where with regards to the multiple data sources.

There is also going to be some query acceleration for DirectQuery datasets that are very large in size. This appears to be done by building in-memory aggregations ont op of data sources such as     Spark and Azure SQL Data Warehouse. Which will then return the results almost instantly.

It looks like there is going to be some AI integrated into receiving the smart alerts. Which will assist in trying to deliver the right alerts to the end users. As well as the option to customize the alerts.

When the Power BI report is in Full screen mode it will cycle through the pages, which is great for when reports are being displayed on large monitors in hallways or shared spaces.

There is the move away from having the dependency of Office 365 groups within Power BI. In the new move it will all be managed within Power BI.

This will mean that assigning workspace roles to individual users, Azure AD security groups, and Office 365 modern groups will be controlled and live within Power BI.

What will be coming to Power BI Premium is the ability to be able to create report snapshots, and then decide via the multiple options on how to process and deliver the reports.

The administrator of the App Workspace or people can subscribe other users for email subscriptions.

That this essentially is, is a central place to store all your common data in your business. For example a list of all your customers are stored in the Common Data Servers for Analytics (CDSA). Then when a user wants to use Customer details they do not have to go and find the information and bring it into their dataset, they can leverage straight out of the CDSA.

The data can be ingested using the very familiar Power Query experience, as well as consuming the Get Data experience, which will also have the capability to connect via the On-Premise data gateway.

All this will sit on Azure storage, which can be added to with your existing Azure investments.

There will be a host of options in terms of how apps can connect and leverage the CDSA. This can be from Microsoft, from 3rd party providers or even potentially your own in-house created Apps.

All these details as well as more details can be found here: Accelerating digital transformation with the spring 2018 release for Dynamics 365 and Business Application Platform

Power Report Server – March Update

As you can see above the Power BI Report server has been updated with all the latest features from Power BI Desktop. This is incredible considering that some of these features were only released this month.

All the details are in the blog post: Power BI Report Server Update – March 2018

Power BI – Building the Opioid Power BI Report

Will Thompson from the Power BI team, goes into a very detailed blog post on how he created the Opioid Power report for the Gartner Bake off. I have put this aside as I still want to go through all the options and features and see how he managed to build such an awesome report.

You can find the blog post here, as well as the option to download the PBIX: Building our report on the opioid crisis for the Gartner bakeoff

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