BI-NSIGHT – Power BI (Embedded Pricing) – SQL Server 2016 (CU1, Mobile Report Publisher for July) – Excel (Power Query Update)

This week has been a good week in the BI domain.

Power BI – Embedded Pricing

As you can see from above they now have released what the pricing model will be for Power BI Embedded.

You can find the link below to view what your pricing will be based on your data center you use.

Power BI Embedded Pricing

SQL Server 2016 – CU 1

This is the first release since the launch of SQL Server 2016 and the list is quite extensive in terms of the fixes.

You can view all the fixes in the link below, but I would recommend testing and then deploying as required.

Here are the details here: Cumulative Update 1 for SQL Server 2016

SQL Server 2016 – Mobile Report Publisher Update

It is great to see how Microsoft has evolved in terms of releasing updates to their product lines in a much for frequent and consistent manner. As is this case with the Mobile Report Publisher.

This update has a lot of bug fixes and optimizations. They do recommend also installing SQL Server CU1 which is in the blog post above, to ensure that the runtimes are aligned.

You can find the details here: Mobile Report Publisher update for July now available!

Excel – Power Query Update

There has been an update for Power Query or Get and Transform in Excel.

There are a whole host of updates and it is great to see that a lot of the features first appear in Power BI but quickly make their way to Excel also. And it appears that some of these features are not yet in Power BI, but I would expect them to be there soon.

Here are a few of the updates that I find relevant.

There are a whole host of new connectors and improvements for existing connectors.

They have also put in the Inline input controls for functions which makes it a lot easier to use and interact with the functions.

The support re-order the steps which is sometimes vital to get the required output.

And finally the option to get new data from within the query pane you can easily do this which makes it easier to add new data.

You can find all the details here: July 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

BI-NSIGHT – Power BI (Dataset API Updates) – Excel (Connect to Power BI from Excel)

Here are another week’s updates in the world of BI. Not a significant week!

Power BI – Dataset API Updates

If you currently use the Dataset API they have released a whole host of changes and additions to the API. I am sure that some of them will be very welcome and I am sure that also some of them have been requested features.

You find the details here: New features for the Power BI Dataset API

Excel – Connect to Power BI directly from Excel

This is another great addition to the Power BI story. As we know that a lot of people love to use Excel and now having the Power BI add-in means that it is very easy to connect to existing Power BI data models in the cloud.

And I have no doubt that as the Row Level security model matures, this in my mind will become the SSAS Tabular as a PaaS. It is getting really close to getting there.

You can find all the details here: Connect and analyze Power BI data directly from Excel, with the July update of Power BI publisher for Excel

BI-NSIGHT – Power BI (New On-Premise Gateway, Data Stories Gallery, Embedded GA coming soon – Publish to Web GA, New Visuals,) – SQL Server (R Client, Migration Assistant for Datazen) – Excel (Faster Pivot Tables in Excel 2016) – Azure (Azure SQL Data Warehouse GA)

There sure has been a whole host of updates and additions in the world of BI in the past week. So here they are below.

Power BI – New On-Premise Gateway

This is another great update that I did not see coming. But it does make total sense. As well as great to have a single point in which you can leverage Power BI, Power Apps, Microsoft Flow and Azure Logic Apps.

I already have been using it for Power BI and it has made the connection to On-Premise data so easy to gain access to and leverage existing investments.

You can find all the details here: Gateways July update – going beyond Power BI

Power BI – Data Stories Gallery

This is a great incentive to see your data as a story. I also think that this will give some great insights into what people are doing and using Power BI to not only tell a story but to also leverage what they have done and how they have utilized Power BI.

You can find all the information here: Share inspiration in the new Data Stories Gallery (and get a t-shirt!)

Power BI – Embedded General Availability Coming soon.

For the ISV’s and companies that are looking to leverage Power BI into their applications it is great to see that it will be Generally available on 11 July 2016.

This will also bring with it all the capabilities that you would expect, as well as I have no doubt some additional features further down the road.

You can find out about the announcement here: Power BI Embedded General Availability coming soon!

Power BI – Publish to Web Generally Available

This is a feature of Power BI that I think really goes a long way to get people interested and using Power BI. The publish to web feature is so easy to use to get it out there. As well as being included in the Power BI license cost, means that when you want to share this, it can be done.

And I am sure that more features will be added further down the line.

You can find all the details here: Power BI publish to web General Availability

Power BI – New Visuals

We seem to be getting some additional visuals coming from the Microsoft Research team which is great to see.

The Strippets Browser can be used to view a collection of documents or news stream. There appears to be two ways to interact with this visual. The first is where you can view the information as a thumbnail, and if you click on the thumbnail it will provide you with more information. Second is the option to use the outlines view, where it strips it all down to create a visual cross-section of a document collection.

The Cluster Map is where you can view the topics in a document collection in an expressive image mosaic.

The Facet Key shows the most frequent entities and enables you to systematically analyze them.

You can find all the details here: New Power BI Custom Visuals Enable Browsing and Analyzing Collections of Text

SQL Server – R Client

It is great to see that Microsoft have finally released their own version of the R Client. This is to compliment their existing investments with R in SQL Server 2016. As well as it being open source, community-supported I have no doubt that this will be the go to R Client in the near future.

You can find the details here: Introducing the free Microsoft R Client

SQL Server – Migration Assistant for Datazen

This is a migration assistant for customers who currently have got a Datazen Server and Content and want to migrate it to SQL Server 2016.

You can find all the related information here: SQL Server Migration Assistant for Datazen Preview now available

Excel – Faster Pivot Tables in Excel 2016

It is nice to see that they have not forgotten about OLAP servers, as this could well apply to both the multidimensional and tabular OLAP models.

And who does not like things that are faster? And it is great to see these additions in improved query efficiency, reducing the number of queries and smarter caches.

You can find the details here: Faster OLAP PivotTables in Excel 2016

Azure – SQL Data Warehouse Generally Available

Just a quick note that the Azure SQL Data Warehouse is now Generally Available.

The blog post does list all of the capabilities and not only that but it appears to also make use of the premium storage in the data centres which means that you can scale it even higher in terms of DWU.

You can find the blog post here: Announcing the general availability of Azure SQL Data Warehouse, an elastic, parallel, columnar data warehouse as a service.

BI-NSIGHT – Power BI (Desktop Update, R for the masses, Barcode Scanner, New Visuals, Forecasting Features, Xero Content Pack)

It has been an interesting week with some great thing.

Power BI – Desktop Update for July

As I expected today 01 July 2016, there has been an amazing release in the Power BI Desktop and below are some of my selected highlights.

In the Report View section, it is great to see that they have created a new Shape map (which is still in preview) which will give the ability to better use the functionality of the maps.

Also the ability to search in the slicers is a very welcome addition as often there can be a lot of slicers and to try and list them on the screen can be a challenge. As well as to find the slicer value you are after. With it being searchable this will allow you to use less screen real estate as well as to also quickly find the values you are after.

The configurable line chart labels are also welcome as this will enable to create a line chart to the desired effect.

Next is the Data Access section, and it is great to see that there have been some enhancements and additions to the Power BI desktop in terms of the configuration of the Row Level Security. It is great to see that they have made it easy to use and configure. And as with what we have had in the past, to have the ability to test it as the specific role. This goes a long way to ensure that it is bringing back the expected results for the role.

In the Data Connectors it is good to see that they have added the connector for Azure Enterprise as well as enhanced some of the existing data connectors.

Under the Query Experience there are quite a few changes, and this is to be expected as this is where we get some real value in Power BI. With regards to the Templates having the option to Load or to Edit is a great choice, so that you can either just go ahead and load your data or edit potential parameters before loading.

They have also made some changes with the parameters and data transformations which is great to see in terms of allowing the parameter specification, and the creation of a new parameter directly in context. As well as making it easier to work with Date/Time by extracting Week Day and Month Name from a Date/Time column. As well as having the ability to merge into Date/Time columns.

You can find all the details relating to the update here: Power BI Desktop June update

Power BI – R for the masses

Once again Power BI just keeps on giving what the users and customers are asking for. I am personally seeing a massive uptake in R. Currently I do not use it myself but from all the demo’s and examples there is a great wealth of information and insights that can be delivered when using R.

Now that this this part of the Power BI service, I have no doubt that there will be a lot more people interested in leveraging the entire Power BI stack.

You can find more information here: R for the masses with Power BI

Power BI – Barcode Scanner

It is great to see that they have now added the capability to use the barcode scanner within the Power BI Mobile app, to be used to automatically filter your reports.

I think that there are a whole host of use cases for this. And it can be used in retail, as well as warehousing and I have no doubt a whole host of other area’s in the business that I cannot currently think of.

Read and watch the video in the blog post to see it in action: Unlock retail intelligence with the Power BI app Barcode scanner

Power BI – New Visuals

It is great to see that there are some great new visuals for Power BI.

I have no doubt that they will be very useful in future or existing projects.

You can find them here: Welcome to Power BI custom visuals

Power BI – Forecasting Features

It is great to see that the Power BI team has not only started looking at forecasting and other analytical capabilities, but also given a rough timeline when to expect it. This is really going to be great.

Power BI – Xero Content Pack

Here is another great content pack for people who use Xero for their online accounting software needs.

It provides some great out of the box dashboards and reports to view your data. And to give you a better understanding of where you are at.

You can find more information here: Explore and Analyze your Xero data with Power BI

Power BI Report – SSRS (SQL Server Reporting Services) Report Performance

Have you ever wondered about how your SSRS reports performance was?

  • How many people are using the reports?
  • How fast are the reports being rendered?
  • Are people running reports, or running them to export them?
  • What is the size of the reports?

In this blog post today I am going to give a brief overview of what I did to create the SSRS Report Performance and then how to use it after you have downloaded the Power BI Template File.

Overview for SSRS Report Performance

I had a requirement where we were looking for not only the usage of our SSRS reports, but also the performance and size of the SSRS reports.

So I created it in Power BI, so that this could be seen by the required people in the organisation.

To see the details of the query that I wrote you can go into the Edit Queries and see the TSQL Query that I wrote to extract the required data.

Further in the blog post I will explain how to easily take this file and run it against your own SSRS Reporting database.

Finally, this is the first draft for this report, so any suggestions, changes or additions will be welcome. Please leave me a comment.

Below is an overview of what each Page does.

Report Usage

As you can see above, this shows details around reports usage, who has been running the reports. As well as the execution times.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Reports Performance

With this report we are looking for the report performance. This enabled us to view where the time was being taken for our reports that were being rendered.

I have used the Report Format, as a means to filter the reports.

As well as providing a legend with each report format type, so that you can easily see which one is taking the longest average time to complete.

I have also included the average times for the three area’s when a report is run. As this can help when trying to identify a specific report and where it is taking the longest. So by clicking on a report name at the top, it can then filter this below.

We have noticed that when people are exporting the reports to CSV or Excel that the majority of the time is spent in the processing of the report.

Report Format

What this page does is to show the Report Format that is being run and used over time, as well as reports usage.

This enables us to get an understanding of what reports the users are using, or are they running a report, to just extract the data? Or running mobile reports more than the standard SSRS reports?

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Report Size

The final report, is the report size. And this is simply to see which reports are the largest. And to see the report size over time.

A thing to note, is that the current screenshots are from SQL Server 2016, and I currently can identify which is a mobile report, but I cannot identify the name of the report.

I have also put in a Report Name Filter (using the Smart Filter by Okviz.com) in order to filter for reports. As well as using calendar slicers to filter for specific periods.

Download of Power BI – SSRS Reports Performance Template

You can download the Power BI – SSRS Reports Performance Template File here:

SSRS Report Performance.pbit

How to use Power BI Template File for SSRS Reports Performance

Below are the steps to complete once you have downloaded the SSRS Reports Performance template file.

Changing the Parameters

Once you open the file you will see a screen with the following parameters as shown below:

As you can see above we have got 3 parameters to enter.

  • The first is the SSRS SQL Server Name. EG: SQLServer\InstanceName
  • The second is the Report Server Database name. EG: ReportServer
  • The final parameter is a Future Scope with the Report Speed. You can leave this as the default for now.

Click Load to load your SSRS Reports Performance analytics reports.

Uploading to the Power BI Service – Enterprise Gateway Connection

If you want to use the Enterprise Gateway connection, you must ensure that you have got it installed and configured in both the Power BI service, and on your Server that has access to your SQL Server.

NOTE: I have found that it works as expected when both the SSRS SQL Server Name and the name that you configured in the Power BI Service for the Enterprise Gateway are identical.

Now you can create a scheduled refresh and share this with users in your organisation.

Keeping history for SSRS ReportServer Database

Just as a final note, currently the SSRS ReportServer database only keeps 3 months’ worth of history.

So if you want to keep the entire history I would recommend that you extract this data into another table. After which you can then modify the query to look at your historical table. If you do require assistance with this please let me know.