Want to get your head around Power Query in both Excel and Power BI Desktop?
I've got a webinar happening for the Power BI team later this month: https://info.microsoft.com/CO-PowerBI-WBNR-FY17-08Aug-25-PowerQuery-PowerBI-Registration.html
It's hard to believe that it's only a year, given how far Power BI has come.
Many of us in the community got together under the lead of Paul Turley and Adam Saxton and created a short video for Power BI's birthday.
So to James Phillips, and all the Power BI team, happy birthday from Down Under, Power BI !
(Good to see some locals in there too )
In recent months, I've been brushing up my R skills. I've had a few areas of interest in this:
* R in Azure Machine Learning
* R in relation to Power BI and general analytics
* R embedded (somewhat) in SQL Server 2016
As a client tool, I've been using RStudio. It's been good and very simple but it's a completely separate environment. So I was excited when I saw there was to be a preview of new R tooling for Visual Studio.
I've been using a pre-release version of R Tools for Visual Studio for a short while but I've already come to quite like it. It's great to have this embedded directly within Visual Studio. I can do everything that I used to do in RStudio but really like the level of Intellisense, etc. that I pick up when I'm working in R Tools for Visual Studio.
So today I was pleased to see the announcement that these tools have gone public. You'll find more info here in today's post from Shahrokh Mortazavi in the Azure Machine Learning blog: https://blogs.technet.microsoft.com/machinelearning/2016/03/09/announcing-r-tools-for-visual-studio-2/
We normally run our Power BI Core Skills class as the 2nd day of the week in our 5 day BI Core Skills.
We've had extra demand for the Power BI day so we've added an extra one in Melbourne on May 10th. Details are here: http://www.sqldownunder.com/Training/Courses/20
Early bird pricing ends April 26th.
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.
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.
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.
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:
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:
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.