BI-NSIGHT – Power BI (Desktop Update for August, R Showcase, Content Pack Certification Program Overview, Power BI Publisher for Excel Update) – OkViz (Sparkline Visual)

Here is another week of BI news and information.

Power BI – Desktop Update for August

This month there is another great list of updates in Power BI Desktop. As I have done previously I will highlight what I personally think are great updates.

Report View Updates

There have been quite a few updates in the report view.

The ability be able to drill down on a line chart is great. I think that this is a welcome addition due to the fact that sometimes the line charts are the best visual and in the past this had to be modified to a bar chart in order to achieve this. Along with this they have also made the bar chart continuous on the axis when a date is selected.

They have also made inline hierarchies generally available, which is great when you want to create an on the fly hierarchy.

The matrix tables have also had some additions added so that you can use better styles.

And finally the ability to use your own custom color formatting for KPI visuals. As sometimes you want it to be a specific color to compliment your data.


This is a totally new area that Power BI has created (as you can see in the first image above), and when I have been browsing other vendors in the visual space, this is where currently Power BI is still a bit behind. But with this addition I can soon see there will be a whole host of additions and improvements.

They have started out with some default analytics, but these are already going to help showcase or easily show trends within your data. And I have no doubt in future releases there will be a lot more analytics built into Power BI. There are a whole host of options when you got into any of the current options available.

Data Connectors

They have added additional data connectors for Snowflake, as well as making changes and improvements to the Impala, Web and SAP BW connectors.

What I want to highlight is the web connector, (again in the second image above) is that they have now made it very easy to navigate and make sure that the table that you want to select is the one that has the data that you want. They have made it as easy as to just select the table that you want.

Query Editing Improvements

Here they have updated the way the Merge/Append works to give you additional options.


Finally, they have enabled the functionality for Auto-Recovery of your Power BI Desktop files. I have used this in the past in the other Office suite (Word, Excel, etc) and this is a really handy feature to have. They have also provided the ability to set how frequently you want it to create an auto-recovery file.

You can find all the details here: Power BI Desktop August Feature Summary

Power BI – R Showcase

As I am sure a lot of people in the BI space know, currently R is gaining significant traction. This is partly due to the uses that it can be used for in the Data Scientist and visualizations. As well as now being part of SQL Server 2016 and Power BI.

It is great to see that they now have a showcase in Power BI, in which you can see the use cases.

You can find the blog post here: Inspire and Get Inspired with the R Showcase

Power BI – Content Pack Certification Program Overview

For organizations who are looking to create content packs that can be used within the Power BI Service, Microsoft have released a blog post explaining on how to go about this.

There do seem to be quite a few steps, but when I had a quick read it appears to be more of a process to get it done, and not very difficult.

You can find the details here: Content Pack Certification Program Overview

Power BI – Publisher for Excel August Update

There is an update for the Power BI publisher for Excel. This enables you to connect directly from Excel to your Power BI datasets and reports.

In this update they have given the ability to connect to read-only groups, as well as reports and datasets that have been shared with you.

You can find the information as well as the download link here: Power BI publisher for Excel – August update

OkViz – Sparkline Visual

The guys from OkViz have created another visual called Sparkline. And they have expanded the default options that you would traditionally have with a Sparkline chart.

I am sure that this will be used in a lot of reports and dashboards where it fits the requirement.

There is quite a bit of information and you can find it here: Introducing Sparkline for Power BI

Power BI – Creating Dashboard Items that show MTD (Month to Date), or WTD (Week to Date), or YTD (Year to Date) dynamically changing based on your current period

I have been working quite a lot with Power BI, and one of the great features is Q&A

So what if you wanted to create a dashboard item that would always show you your sales for the current month (MTD), or current week (WTD) or current Year (YTD)? And always show the correct current period, without you having to go and change it manually?

But you do not want to have to try and do this in your reporting layer, as this would affect all your reports, but want it to be used as it should as a dashboard?

What it looks like once finished.

As you can see below in my Q&A this is very easy to do. And the image below shows what it looks like once I have made a few changes to the initial Q&A question.

As you can see Power BI is context aware for dates, and it knows that we are in the month of August, and it is displaying our data from 01 Aug 2016 – 23 Aug 2016 (Todays date)

How to do it

In order to get the same visual as shown above I did the following:

First I typed it into Q&A and I got the following screen:

Next what I did was to click on the Visualizations, Filters and Fields on the right hand side to expand them.

I then clicked on the Visualizations and selected the Area Chart as shown below in the Visualizations section

What this did was then show me the following in the Canvas

The final step was for me to go into the Fields list and select a Date value from my Date table. And for my example I just chose the actual date. But you can choose any option you want to visualize.

NOTE: When you add in the Date, by default it will create the hierarchy in your Visualizations Axis as shown below.

I then clicked on the down arrow and selected Date

Once that is done you then get the same visual as first shown.

Other Examples

Likewise, if I change this to look at YTD (Year to Date) I change it to “This Year” and complete the steps above.

NOTE: Another thing to remember is that you can modify any of the Visualization
settings as you would with any Visual. As you can see above I changed the colour to Grey.

And finally I also created a dashboard item for Last Year

Tip – How to get the Dashboard Item from Q&A to link to your report

A quick tip is that if you still want your dashboard item you created above to go to your underlying report and NOT to your Q&A question once you have pinned your dashboard item complete the following.

First go into your report and click on the sheet that you want it to be linked to.

Now if you look in the URL you will see the URL as well as the ReportSection as shown below.

Now take that entire link, go into your dashboard item, click on the ellipses and then click on Tile Details

Then on in the tile details click on the Set Custom Link

And the paste in your URL from the steps above as well as ensure the option to “Open custom link in the same tab? Is set to No

Now when you click on the dashboard item it will take you to the correct sheet on your report.

Final notes

A final note is that it does appear that you can use “This year“, “Last Year“, “Last Two Years” etc. on any date field and it will apply.

Power BI – (Twitter Solution Template, Real-time Data, Embedded GA Pricing Update, On-Premise Data Gateway Update, Monitor On-Premise Gateway) – SQL Server 2016 (SSDT Update, SSMS Update)

Here is this week’s BI updates

Power BI – Twitter Solution Template

This is the second solution template offered by the Power BI team and this time I think they have done a fantastic job. As I know of many companies who are active on Twitter and currently either do not have the resources or the time to be able to complete not only analysis of your Twitter tweets, but also the sentiment analysis.

With this solution template it allows you to very quickly and easily get this up and running. Yes you will need to have an active Azure Subscription, as well as Power BI Pro, but I think that for the larger or even smaller organizations they have already got this set up.

You can find the blog post detailing more information and how to get it up and running here: Announcing the brand & campaign management solution template for Twitter

Below is the current pricing model for the Azure related instances:

And here is the link: Pricing Information for the Twitter Template

Power BI – Real-time data

The guys from Microsoft Power BI have released another blog post where they explain how to get started with streaming data into Power BI on a dashboard.

You can then also follow this blog post by Reza Rad where he explains how to set it up and test to see how it works (Monitor Real-time Data with Power BI Dashboards)

Which you can then use to try and implement the streaming data.

Here is the blog post: Real-time in no time with Power BI

Power BI – Embedded GA Pricing Update

The Microsoft Power BI team has released an update and simplified way to understand how the Embedded pricing works.

As you can see from the above it is very simple. As well as in the blog post they also detail exactly what makes up a report session. And it is very clear to understand and see how potentially there will be a cost associated to this.

You can find the additional details here: Power BI Embedded GA pricing update

Power BI – On-Premise Data Gateway Update

Another monthly update from the Power BI team for the On-Premise data gateway. And in this months release is the ability to refresh ODBC connections.

This is something that was requested and not only that, with ODBC connections your options are almost limitless in terms of what you can connect to.

You can find the blog post details here: On-Premises Data Gateway August update

Power BI – Monitor On-Premise Gateway

This is a great blog post by Brett Powell (InsightQuest) on how to monitor the On-Premise Gateway in terms of what is being used, and what connections are being made when.

This also means that you can combine this with the other performance counter values to see how this could potentially affect the performance of your server when the data refresh is happening.

You can find his white paper here: MONITOR ON-PREMISES DATA GATEWAYS

SQL Server 2016 – SSDT Update

It is great to see that they are also updating SSDT with regular updates.

In this release there are updates to the Analysis Services Tabular Model Explorer, which gives you a better view of the objects in your tabular model.

There are also updates for the Always Encrypted.

As well as fixes and improvements.

You can find the blog post here: Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

SQL Server 2016 – SSMS Update

Here is the monthly update for SQL Server Management studio.

There are a whole host of fixes and additions in the changelog.

You can find the details here: Download SQL Server Management Studio (SSMS)

Power BI – How to resolve “Q&A is not available for any of the datasets on this dashboard”

I was recently working on a Power BI project and I had uploaded my Power BI desktop file to the Power BI Service. I had then added an item to a newly created dashboard.

Determining why I could not use Q&A

When I went into the dashboard I did not see the option for the Q&A box as shown below, which I am typically used to seeing.

I then went into the Settings and clicked on the Dashboards tab, and then selected my associated dashboard above.

And this is what I saw when I tried to enable the Show the Q&A search box on this dashboard was the following: Q&A is not available for any of the datasets used on this dashboard.

So I went searching for quite a bit to try and understand how I can get this error resolved.

After broadening my search, I finally found the solution to my problem.

Problem Solved!

What I had done, in my Power BI Desktop file was to create a few Security Roles and published this to the service.

From my understanding currently Q&A does not support Row Level Security, so when it detects this it will disable the Q&A and when you try and enable it, it will give you the message as shown above.

To get Q&A working all that I did was to remove my Security Roles and re-publish the file.

And hey presto I got Q&A back!

Automating running PowerShell script which required Administrator elevation via SSIS

UPDATE (08 AUG 2016): I have updated this to use a SQL Server Agent Job instead of the Task Scheduler which proved to not be reliable.

What we are going to explain below is the following via an example.

Example – We are required to run a PowerShell script which will snapshot a database using Xtreme IO from EMC. In order for this to run it has to be run as Administrator due to it having to drop and re-create the disk drives in the Operating System layer.

So in order to complete this we have to complete the following below but use a simple PowerShell script which will get all the FileNames from a Folder.

  • Create a BAT file which can execute the PowerShell script.
  • Create a Scheduled Task which in turn will call the BAT file.
  • Create an SSIS Package which will call the Scheduled Task

NOTE: The reason that we did this above, was to ensure that we could automate the snapshotting of the database using SSIS. This would enable us to get a snapshot of the data on a required schedule.

Changing the UAC

Below is a quick step to disable the UAC, as I think that this might affect the later steps when the BAT file is trying to run the PowerShell script.

NOTE: You will need to be an Administrator on the Server to turn this off.

  1. Go into the Control Panel.
  2. Then search for UAC.
  3. Then click on the Change User Account Control settings as shown below:
  4. Change it to Never Notify as shown below:
  5. Then click
  6. It will ask you one last time if you want to make the change to the Computer.
    1. Click on Yes

Creating the BAT File which can execute the PowerShell script

In the steps below we are going to explain how we create the BAT file which executes the PowerShell script. And also show that at first we can run the PowerShell script manually to ensure that it works.

Testing the PowerShell Script

  1. The first thing to test is to ensure that the PowerShell script actually runs and completes as it should.
  2. Click on Start and then type in PowerShell and from the search results right click on Windows PowerShell ISE and select Run as Administrator
  3. Now when the PowerShell ISE opens you can see from the Window at the top that you are running as Administrator
  4. If you want to follow our example you can then copy and paste the following PowerShell script

    ‘C:\Program Files\Microsoft SQL Server\MSSQL11.PRODBI\MSSQL’
    ‘C:\Program Files\Program Files.csv’

    1. What we are doing above is to get the File Names for all the files and folders under Program Files and then our SQL Server MSSQL Folder.
    2. And then export it to a CSV.
    3. As with our example once run you should see the CSV file as shown below:
  5. Now we know that our script runs we can now save it to a location on our Server.
  6. As with our example we saved it to a new folder on the C:Drive
  7. We will now use this in the next steps to create the BAT File.

Creating the BAT file which will run the PowerShell script.

In the steps below we will be creating the BAT file which will run the PowerShell script.

  1. Open up Notepad or Notepad++ and copy in the following, which will be explained below:
    1. c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe
      -ExecutionPolicy ByPass -command
      “. ‘C:\XIOSnapshot\Export-MSSQL-Files.ps1′”
    2. What we are doing above is to first put in the full location of PowerShell highlighted in GREEN
    3. Next we are putting in some parameters for PowerShell, which is to bypass the execution policy and the command that is to be run highlighted in PURPLE
    4. And then the final part is where we are calling the PowerShell script which we created above. It is highlighted in RED
      1. NOTE: In order to run the command, we have to put it all in double quotes, as well as it has to have a period in order for it to be executed.
    5. Now we can save this BAT File in the same location with a name.
    6. As per our example we saved it with the following:
  2. Now what we will need to do is to run the BAT file from the command line to test that it all works.
  3. You can now open command prompt and run the bat file as we have shown with our example below:
    1. NOTE: You can see that we are not logged into the Command prompt as Administrator.
    2. We can also see that it completed successfully.
    3. And finally if we look on the file system we can see the file got created.
  4. One final thing to note, is that this will run when we execute the BAT file as we have administrator privileges.

Using SQL Server Agent Job to run BAT Files

Below is the process which will actually enable you to run your BAT files under the correct CMD.EXE Context

  1. In order to get this to work, we had to use a combination of SQL Server Agent steps and SSIS steps, which is explained below
    1. First we then needed to use our BAT File to run the PowerShell script which would snap the data.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      2. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
      3. And then we put in our BAT file which calls the PowerShell script which we created earlier.
    2. Next we then ran the BAT file which modified the folder permissions.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      2. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
  2. You can then schedule your SQL Server Agent job and as I tested it did run successfully.

BI-NSIGHT – Power BI (Desktop & Service Update, Amazon Redshift Connector, Data Driven Alerts Deep Dive, Impala Connector) – OkViz (PBIX Tools)

As it is now becoming tradition at month end is when we get the release of the Power BI Desktop and Power BI Service, as well as other related BI things happening in the past week.

Power BI – Desktop Update

So this latest release has a whole host of updates, and as before I will highlight what I think is most relevant.

Report View Updates

With the report view they now have put in more features so that you can use predefined table styles.

As well as now having the ability to use a ShapeMap as custom maps. You can find another blog post further below which will explain how this can be used.

Data Connectivity

They have added 2 new data connectors and by far the Amazon Redshift is one that has been requested a lot, and having existing work colleagues and friends who use Amazon this is a very welcome data connector. And from what I can gather Power BI in terms of visuals and BI is well ahead of Amazon, so in my opinion this can further enhance people to use Power BI.

They have also made improvements for CSV and Text with fixed width delimited files.

Query Editor Improvements

In the Query Editor or Power Query they have now added support to run R scripts within the query editor. I have no doubt that this will be a very welcome addition to people who use R.

Another feature which I was sure would come would be the ability to now use a List Query as Allowed Values for a parameter. This now means that you can generate a dynamic list, which can then be used for your parameters.

You can find all the details here: Power BI Desktop July feature summary

Power BI – Service Update

As previously with the Desktop updates, below I will go through the Service updates in terms what I think is most relevant to highlight.

Data Driven Alerts

This is something that is a very welcome addition and it will be used by myself as what it means is that we can be more proactive in terms of knowing what is happening with our data.

The thing to note is that this currently can only be done on Card Tiles with numeric values and gauges. And this is a really powerful feature to have.

Row Level Security (RLS)

It is great to see that now RLS has been moved from Preview status into production.

Along with this all of this is now configured in the Power BI Desktop and you can only test to see how it will work in the Power BI service.

Also it is great to see that this now applies to the Analyze in Excel feature, which in my opinion where Power BI is soon to become a SSAS Tabular PaaS

Data Classification

This is another great feature in terms of being able to classify your data so that the people using the data can understand the businesses data policies.

Analyze in Excel

They have made some great updates to the Analyze in Excel which is now the ability to connect directly to your Analysis Services On-Premise data.

As well as giving more control in terms of how this can be enabled or disabled for the administrator of Power BI.

You can read about all the updates here: Power BI July Update for Service and Mobile

Power BI – Using Amazon Redshift to build Power BI Reports

Microsoft have released a blog post on how to connect and use data from Amazon Redshift.

You can find the details here: Building Power BI Reports on top of Amazon Redshift data

Power BI – Use Any Map

This is a great post by Kasper De Jonge and how to now use any map or shape file using Power BI.

He goes into great details in terms of how to achieve this, as well as how to take any shape file and get it into the required format for Power BI.

You can follow what Kasper did here: Use any map with Power BI

Power BI – Data Driven Alerts Deep Dive

This is a great post by the people from Microsoft with regards to more information in how the Data Driven Alerts work, as well as how they function on the mobile app.

You can find more information here: Always be in the know: a deep dive on data driven alerts

Power BI – Impala Connector

Microsoft have released another connector for Impala, and they have a blog post in how to connect and use it.

You can find more details here: Power BI enables connectivity to Impala

OkViz – PBIX Tools

This is something from the wonderful guys at SQLBI and even though this is a small start in terms of currently being able to remove all unused custom visuals, there will be a whole host of additional tools that can be used with PBIX files over time.

And I am sure that there will also be the option to give ideas for future enhancements.

You can find the details here: PBIX Tools