Archive for TechEd 2014

Query Plan Analysis

Matt's curated best self serve BI content

Query Plan Analysis: The 5 Culprits that Cause 95% of Your Performance Headaches

I am fairly new to SQL Server, and I am by no means an expert. I learnt my database ‘skills’ using MS Access. In a recent engagement with a client, I had a need for a tool that could handle > 10GB of data, so I am now using (and appreciating the power of) SQL server. This session from Adam Machanic (great name for a DBA!) gives an excellent overview of the SQL Server Query Plan Analysis tools. It is useful for people like me who don’t know a lot about how this feature works, and the material is probably even more meaningful for experienced DBAs that really understand the content and can leverage the tips he is explaining. Having said that, if you do not write queries using SQL server, and are not interested in finding out about how SQL server is different to Access, then there is nothing for you in this session.

Top 5 culprits

Time Stamp Description
0:00 Intros
3:00 Adam’s contact details.,
3:55 Introduction to Query Plan
5:30 Demo of how Query Plan works and key concepts
18:00 Introduction and description of the 5 culprits, plus one bonus
26:35 Demos of them all

Here is the full video

Here is the link to see all the BI Track content.

Deep Dive into Power Query M Language

Matt's curated best self serve BI content

In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the way, which came out in a light sarcastic way at times, but I think this reflects the state of maturity of this tool. Not too many people have deep knowledge since it is so new. I am waiting for Chris Webb to release his new book so I can get in some late night reading. Here is the agenda – mainly demos which is good.

M language agenda

and here is the relevant content

Time Stamp Description
0:00 Intros
1:55 Demo/Overview of the Power Query product, tab etc. Not too much new here.
2:50 A good lesson about the SQL Statement window. Don’t use it.
3:15 A great 2 min overview of what Power Query does, how the formula bar works, how to access the advanced editor, and good advice to use the UI first, then edit formulae.
5:00 Intro to M Language, why Microsoft selected it, why DAX is not up to the job.
7:30 Introduction to the concept of “Query Folding” – how Power Query pushes the work back to the database when it can. Good to know.
12:15 Some information about Library Functions. Here is a link with a PDF download that has a formula library reference that they talk to on this slide.
13:30 Demo time.

  • Explains the new features to change the default load behaviour to Excel and/or data model.
  • How to get inline help for the M language
  • Shows making changes to the advanced editor to bring in a 3 way join of tables from a DB.
17:45 Language Flow explanation – how the language passes on transformations from one step to another.
19:45 Type System. Explains the different data types, and how to define them, with a demo
29:30 Demo: Import and combine multiple CSV files into one table
32:40 Demo: Import a single file (Excel), edit the query to create a function, then import multiple versions of the same file and combine them. Matt shows you how to edit the regular query to a function.
41:30 Demo: Download complex data from Web. Good example of the breadth of the language using functions to download data.
48:15 Demo: Another web demo downloading multiple pages using a function.
55:40 An example to extract data from Binary objects. Far too deep for me!
61:00 Tips and Tricks. Worth watching. #shared in the formula bar will give you a list of functions and keywords.
64:10 Links Page
Language Reference:
Library Referenece:
Office Help:

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

Solving Complex Business Problems with DAX – TechEd 2014

Matt's curated best self serve BI content

This is a continuation of my series on “best of” from TechEd 2014. This session by Kasper de Jonge makes my best of list. It is quite technical in places and digs in deep. I particularly like when he talks about “evaluation context”. Erik explains that there are 2 types of context (row context and filter context) and why it matters. The other thing really interesting in this session is that Erik describes how the development team uses (what he calls) ‘syntax sugar’ to create new functions that the user sees, but under the hood DAX is actually using a more complex calculation using other existing functions.



Time Stamp Description
0:00 Introductions and background
2:28 Agenda
3:40 What is evaluation context? A great bit of information at the 20 min mark about how “nested” measures are calculated – innermost
6:00 Demo: Running Totals
21:00 Demo: Tool Table (disconnected table example). He uses Values() to detect the “type” of data, and then uses a switch statement to select which measure to display in the pivot table, and explains how to prevent errors in Grand Total columns.
35:00 Demo: Time Intelligence example where you can create a “current month” calculated column that automatically updates with each new month, meaning you don’t have to change your pivot table each month.
40:00 Demo: Several row context examples for both columns and measures. He explains how using ‘calculate’ in a calc column will give you filter context, and now row context
48:00 Demo: Time Intelligence year on year growth.
57:30 Demo: Time Intelligence with a 445 calendar.
63:40 Q&A

Here is the full session

Self Service BI from TechEd 2014 cont…

Those that didn't make my list

This post is a continuation of my last post where I started to provide a curated view of content from TechEd 2014 from the perspective of a Self Service BI professional.  Inevitably if I am to share with you the best of the sessions, there are going to be some that don’t make my list.  Rather than ignore those sessions on my blog, I have decided to create a list of the sessions that I have viewed that don’t make my “best of” list.  Rather than post each time I find one of these, I will instead come back to this post and edit it with the latest “didn’t make it” sessions.

Session Why it didn’t make my “best of” list.
All you need to know about Microsoft Power BI for Office 365 – Michael Tejedor When Michael starts this session, his first comment was that the session title had changed from his brief.  The session is really a “Power BI Overview”, and for this reason it doesn’t make my list.  It was VERY high level, more like a show and tell rather than a learning experience.  If you have never seen Power BI for Office 365 or Excel, then this is a good intro session.  If you already know what these tools are, there won’t be much for you in this session.
Interactive Data Visualization with Power View – Lukasz Pawlowski I watched this session and went in with very low expectations. My expectations were met. You see I have a low opinion of the current (and every previous) release of Power View. I am not saying Microsoft wont get there, but I think it is a long way off where it needs to be if it wants to be competitive with products like Tableau and native Excel – yes I think native Excel is better.Having said that, if you know nothing about Power View, this demo will show you what it can do. The biggest repeating disappointment I have with Power View presentations is that they are all a “dog and pony show”. They show tricks with their toys, but I simply can’t see the business value in the demo’s. Take my advice Microsoft – develop better, more meaningful demos of real business scenarios with your Power View demos.
BI Power Hour I actually think this would have been quite fun to attend if you were at the conference.  It was presented by an awesome bunch of MS BI gurus, but somehow watching a slide version after the conference didn’t really deliver the same experience.  It doesn’t make my list.

Here is a link to all the BI Track sessions at TechEd 2014.

A smorgasbord of Microsoft Self Serve BI at TechEd 2014

Matt's curated best self serve BI content

Last week in Houston Texas, Microsoft held TechEd 2014 (North America, 12-15 May). I was browsing the videos from this conference and wanted to share the wealth of material that is available for anyone to view. If you just filter on the “Data Platform and Business Intelligence stream“, there are over 40 sessions focussed on everything from Power Query, PowerPivot, SQL Server and more.

One of the things I really love about my new career as a freelance Self Service BI expert is that I can consume content like this, and actually get value that helps me be successful in my job. I plan to watch a lot of these videos over the coming weeks, and I will share back here a brief summary of those that I find most useful.

What’s New in Power Query for Excel

Let me start with this beauty of a session. It runs for 1 hour 15 mins and it gives an excellent overview of why Microsoft has decided to build Power Query, as well as a number of excellent demos of the power of this great new tool. Towards the end of the video (at the 45 min mark) they start to show some of the new features that will be released in the near future. If you even wanted to answer the question “What is Power Query and why should I care?”, then this is the video for you.

Here are the highlight points in case you don’t want to watch it all, with the video below.

Video Topics
Time Description
0:00 Intros
2:20 Why they have built Power Query
11:15 Demo – pull data from web search transform and produce a Power Map chart
17:50 Demo – pull data from web URL transform and produce a Power View chart
23:20 Demo – data mashup pulling data from different data sources combining and producing a Power View time series chart
32:10 Demo – multi dimensional data demo (Business Objects Universe)
40:30 Demo – pull data from web page URL where data is spread over multiple pages. Uses advanced Power Query technique using a function written in the "M" language.
45:25 The start of all new stuff not currently released.
47:30 Demo – demo of new tool to "transpose" data direct in Power Query
49:30 Demo – demo connecting to Microsoft Exchange to analyse emails over time (very interesting) – already available.
60:20 Demo – recent data sources feature that keeps a list of previous data sources
64:00 Q&A

Here is the link to all the videos: