SQL Down Under–Show 65–Jen Underwood and Power BI

Hi Folks,

We've taken a fairly long break but now back into the swing for SQL Server 2016 and Power BI.

The first of the new series of shows is a show on the current state of Power BI with Jen Underwood.

You'll find it here: http://www.sqldownunder.com/Podcasts

or subscribe download like any other podcast. Feed link is: http://www.sqldownunder.com/SQLDownUnderMP3Feed.xml.




FIX: Internal service error when refreshing Personal Gateway for Power BI

We recently started working with the new Personal Data Management Gateway for Power BI. Overall, we really like it but the error messages in most of Power BI have left much to be desired.

One error that we were encountering made us feel like the service was flaky as it seemed to happen randomly. When we tried to refresh a dataset, we got this error:




The Power BI team came to the rescue and worked out what was happening. Turns out that you cannot currently refresh more than once every 5 minutes. That also includes within 5 minutes of your initial upload. Unfortunately, this is the error returned when you attempt it.

Apparently this 5 minute limit is going to be removed soon and hopefully that will be one less error we might see.

Power BI Designer March Update includes Google Analytics Connector

The Power BI team have released details of their March update to the standalone Power BI designer.

You'll find details of the update here: http://blogs.msdn.com/b/powerbi/archive/2015/03/26/7-new-updates-to-the-power-bi-designer-preview-march-2015.aspx

The first thing I noticed is just how much faster the tool operates. The blog post mentioned performance enhancements but I really, really noticed them.

One particular enhancement  that I wanted to call out was the additional of a connector for Google Analytics. I've been trying that this morning and have found it really easy to use. All the standard categories appear as available sets of data:


I've picked those that I'm interested in:


Then added them to a report:



Very easy. And it's clear which type of device/operating system we need to continue to ensure the best experience on at SQL Down Under.

Powershell Invoke-Sqlcmd –Hostname Parameter sets the Application Name instead of the Host Name

Two of the parameters in SQL Server connections are the Application Name and the Host Name. You can see these in SQL Server if you execute the following command:


I've always been a fan of having applications identify themselves in their connection strings. It makes tasks like tracing much easier. The tools supplied with SQL Server do a reasonable job of that as you can see above. But many other tools don't do such a good job.

I was working at a site today where they are using Powershell to execute commands for monitoring. I noticed that the Powershell commands did not set the Application Name in the connection string when using Invoke-Sqlcmd. Note the following example:


I then tried to work out how to set the Application Name. When I checked the documentation for Invoke-Sqlcmd, it shows that the Hostname is set via the SQLCMD option –H, by using the –Hostname parameter.


However, note that if you use the –Hostname option, it actually sets the Application Name and does not set the Host Name:


Partner events for SQL Server 2014 and Power BI

Over the last year, I've delivered a number of partner enablement events for Microsoft. These events are low cost training sessions that run for three days. Days 1 and 2 cover SQL Server 2014 content, mostly regarding in-memory OLTP, clustered columnstore indexes, and Azure integration with hybrid systems. Day 3 covers the full Power BI stack.

We're pleased to be running another set of these around the country:

Melbourne: November 5th to 7th

Perth: November 24th to 26th

Sydney: December 8th to 10th

I'd love to see many of you there. I'm looking forward to delivering them. To find out more, follow these links:

Customers: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032594188&Culture=en-AU&community=0

Partners: https://training.partner.microsoft.com/learning/app/management/registrationex/LMS_Registration.aspx?UserMode=0&Mode=0&ActivityID=878275

The Power Query story keeps getting better

It seems like anyone that's talked to me this year has heard me going on and on about how Power Query is just the nicest new bit of tech that's come from Microsoft lately. We cover it as part of our one-day Power BI Core Skills training class and as part of day 2 in our BI Core Skills classes that we recently updated for SQL Server 2014. Out of all the technologies that people see during that week, Power Query is the one that seems to promote the biggest discussions.

It's also a product that keeps getting improved constantly. Another new update for Power Query is out and you can find it here: http://www.microsoft.com/en-us/download/details.aspx?id=39379&WT.mc_id=Blog_PBI_Announce_DI

Here is a video that shows what's been updated: https://www.youtube.com/watch?v=Q9boOzu298Q

The blog post from the team that shows what's changed is here: http://blogs.msdn.com/b/powerbi/archive/2014/06/26/6-new-updates-in-power-query-june-2014.aspx

For me, the one big thing that Power Query is now missing is a corporate story beyond Excel/Power BI. The queries that you create in Power Query are actually M language queries. This is a very capable language (unrelated to what the industry used to call the M language), that Microsoft have documented here: http://go.microsoft.com/fwlink/?LinkID=320633

I would so love to be able to take the queries that I generate in Excel and paste them into an SSIS data source, or use them as a data source in SSAS tabular. Once those types of options appear (surely they must), this is going to be even more of a killer application.

SQL Down Under show 53 with Grant Paisley – PowerPivot, Power View, SSAS Tabular and Multidimensional

I promised there would be a bunch of shows this year. The second one is now published.

In this show, SQL Server MVP Grant Paisley describes PowerPivot, Power View, Analysis Services Tabular vs Multidimensional and shares the lessons he's learned when working with these tools.

The show is here: http://www.sqldownunder.com/Resources/Podcast.aspx


CTP for Power View and SSAS Multidimensional Cubes

When Power View appeared, one of the big outcries was "but what about connecting to existing cubes!".

Great to see that the SQL Server team have addressed that. A CTP that allows connecting Power View to SSAS Multidimensional cubes is now available:


Help the team get this out the door by trying it and providing feedback.

Setting default values for slicers in PowerPivot

I've been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That's as good as a default value for me. Thanks José!

Hope this helps someone else.

Book Review: Microsoft PowerPivot for Excel 2010: Give Your Data Meaning

I'm loving my Kindle. I seem to be getting through books so much faster. One book that I recently read was Book Review: Microsoft PowerPivot for Excel 2010: Give Your Data Meaning by Marco Russo and Alberto Ferrari.

I really liked this book. It provided quite good coverage of PowerPivot use in Excel 2010 and also spent some time mapping the use of PowerPivot to organizational requirements. Marco and Alberto provided more coverage of DAX (Data Analysis Expressions) than I have seen anywhere else, particularly in relation to the CALCULATE verb.

If I have any criticism of the book, it's probably just the order of the chapters. I can imagine that many people won't want to delve so deeply into DAX and may stop reading before they get to the later chapters. I'd like to have seen much of the DAX material at the back of the book as a type of "advanced DAX topics" section, given that the remainder of the book doesn't really depend upon it.

I was left feeling that there's a need for another type of DAX book, much like the book that Art Tennick wrote for MDX: Practical MDX Queries: For Microsoft SQL Server Analysis Services 2008. In that book, Art provides a large number of "recipes" for how to achieve common tasks with MDX. I'm sure that's also needed for DAX.

Anyway, Marco & Alberto's book is definitely recommended.I'd give it 8 out of 10. (And a big thumbs up to the publisher for making a Kindle version available too).