I just wrapped up another teaching engagement, teaching BI tools to a group of analyst and administrators. I really enjoy the opportunity to teach. What I realized is that I learn a lot from the students I teach. Each teaching opportunity that I have allows me to broaden what I know about these products and how they can be used. And, each class I teach helps me learn WHAT to teach.

This was my fourth time teaching BI tools. In each class I have slightly modified what I teach and how I present this information. I’m teaching PowerPivot, Power View, and Performance Point – all Microsoft BI tools. Each of my customers is still on older versions of software (Excel 2010, SharePoint 2010), so I have to teach with some of the limitations of the software.

So what have I learned teaching these classes?? Here’s my top 5 things (in no particular order):

• Someone marketing at Microsoft needs to do a better job of describing WHAT these tools do, or more importantly what they SHOULDN’T do (i.e., they are not ad-hoc query tools!).
• Despite the advertising and marketing, you do need to understand how data is modeled in order to use the tools.
• IT departments today seem to be struggling under the demand for data. While these tools are marketed to free the IT department up from data requests by letting the users have access to “their” data, in reality it is adding a burden to many departments because data is not in a format easily consumed by business customers.
• The BI tools market in general is still very young and the support tools that we have in the SQL world don’t exists in the BI world. They are also not easy to support because of the many connecting pieces (SharePoint, PowerPivot Services, Excel Services, Visio Services, Performance Point Services) – it is difficult to manage and understand how to troubleshoot with this many moving pieces!!
• The phrase “the right tool for the right job” seems to not be well understood in the BI space. I see too many people trying to use BI tools to 1) replace IT systems, 2) bypass IT rules, 3) run in place of better tools for ad-hoc query, and 4) be the “end all” end user tool to support access to all types of data.

The end result of this is that I’m slightly changing how I teach for these tools. One critical thing is to make sure my customers understand data modeling – i.e. how to build a star schema that works with BI tools. Being able to do this effectively is critical to the success (and performance!) when using these BI tools. For my last class, I actually drew out an empty star schema, and showed them how it should look. We discussed what should be in a fact table (Keys to dimensions, and Measures), and what should be in a Dimension table (labels, text, and hierarchies). I gave them a guideline: When bringing in your data and making it work in a star schema, I reminded them to place every field in the model by asking this: Where will it show up on a Pivot table? Will it be a Column or Row Label (if yes, then probably goes in a dimension)? Will it be a filter to limit rows (if yes, then probably goes into a dimension, maybe a hierarchy)? Will I use it to drill up or down (if yes then hierarchy in a dimension)? And finally, is it a number that will be aggregated and put in the Values section (if yes then it’s most likely a fact)?

While this is not a complete design guide, it seemed to help my students understand how they might easily bring data into for BI tools.

In this latest class, my class struggled with how to define calculations and measures. Specifically they struggled with when to calculate data in columns vs when to put a calculation in a measure. So now I’m going to be adding some instruction to help with that issue. For example, if the calculation has any field that need to be evaluated at a row level prior to the final aggregation, then the calculation needs to be in a column so that the individual rows can be evaluated. Those values can then be aggregated. Most importantly, will the calculation be changed if the filters on the rows changes? If it is, then it’s a measure and belongs in the measure section.

I’ve learned a lot of other things from my students, they are usually pretty sharp and challenge me constantly to keep my skills sharp because they ask such great questions. I really enjoy the challenge of teaching these tools, and I love learning about what everyone does with them. Hopefully I’ll be doing a lot more of this in 2014!!

Today’s Picture of the Day: I recently returned from a trip to Italy. This image is of Civita di Bagnoregio in the Tuscany region of Italy. It was a magical place.

From Civita di Bagnoregio