It is with tremendous joy (and a little trepidation) that I announce that I will be doing my very first Pre-Con as part of SQL Saturday 332 in Minnesota in October. I have been presenting for several years now and feel that it is time to take this next big step. Since I love presenting so much, the idea of presenting for a whole day is just awesome. There is a also quite a bit more pressure in this scenario. But that is part of what makes this a great growth experience, stretching myself like I have never done before.
Over the past several months, I have done a lot of client work in Excel dealing with Power Pivot and Pivot Tables. I have also been doing a bit using Power View up in SharePoint, the vast majority of which carries to Excel 2013 as well. I have always been a fan of enabling users to do more with data and learn to be more self-sufficient. My experiences in Excel have reinforced the idea that Excel is a fantastic platform in the Self-Service BI movement. The past several months working with data in Excel have been some of the most fun in my career. So, when I needed a topic for a BI Pre-Con, the choice was easy.
You can find information on all the SQL Saturday 332 Minnesota Pre-cons here. It is an impressive line-up, to be sure. The abstract for mine is below.
Microsoft Excel: The Business Intelligence Platform For The Masses
From gathering and shaping source data through data modeling and visualizations, it is staggering how much you can accomplish in Excel. This Pre-Con will walk you through creating an interesting and powerful BI solution in Microsoft Excel 2013. Whether you are a business user or a technical developer, you will get good value from attending.
1. Power Query
• Using Power Query to gather source data from various sources both on-premise and in the cloud.
• Use various transformations on the Ribbon
• Travel back and forth through time via Query Steps
• The basics of Power Query Formula Language (M)
2. Modeling Data With Power Pivot
• Importing Data from various sources
• Linking Tables to data residing directly in Excel sheets
• The importance of Date Tables
• Best Practices
3. DAX 101
• Introduce DAX syntax beginning with Calculated Columns including the mighty Related function
• The basics of the Calculated Fields (Measures)
• Row context/filter context
• The power of the CALCULATE function
4. Power Pivot/DAX Design Patterns
• Solving real-word problems with Power Pivot
• Many to Many relationships
• Parent-Child Hierarchies
5. Excel Pivot Tables/Charts
• Connecting Excel to data sources like SSAS Cubes, Tabular Models, and the internal Power Pivot model
• Pivot Table basics
• Filtering methods and Slicers
• Conditional formatting
• Pivot Charts
6. Power View
• The basic visualizations (Bars, Columns, Matrix, etc)
• Advanced visualizations (Multiples, Cards, Scatter/Bubble Charts, etc
• Filtering views or the entire report
• Design tips to take great advantage of Power View’s capabilities
I will be focusing on using Excel 2013 on my machine. There is so much to cover that trying to add in Power BI-specifics is just not in the cards. But I think that makes sense as SO many more people have Excel on their machines than are using Power BI right now. And SO few of those people are taking advantage of even a tiny subset of what Excel has to offer. A major goal of this Pre-Con is to help change that.