PASS Summit 2012: Encourage, Energize, Empower Part 1

Part 1: Encourage

I’ve stolen the title of this series of posts from the motto on the swag for the Women in Technology Luncheon from PASS Summit 2012, as I think it perfectly summarizes my experience at this conference. It was my second PASS Summit (first was in 2010), and I was thrilled to be able to attend again this year. Thanks to the PASS Chapter Leader scholarship and to Trek Bicycle Corporation for sponsoring my attendance!

While I’ve worked with the Reporting Services end of the Microsoft BI stack for a number of years, it was encouraging as new BI professional to see the variety of business intelligence content presented. It’s good to see that the field I’ve jumped into with both feet is growing by leaps and bounds. Some BI content was introductory, some soared well over my head, and some was just plain fun. PowerPivot, Power View, and tabular models appeared many times in the sessions I attended, and for me the repetition had a purpose. I wanted to hear the different perspectives each presenter had on the topic, I was also going to take advantage of repetition to see if I could get stuff to stick in my head. Thank goodness for OneNote or I’d never have been able to keep track of it all. Here’s a rundown of some of my favorite BI topic sessions.

PowerPivot with NASA Data by Pragmatic Works’ Mark Stacey

PowerPivot essentially leverages columnstore indexes to improve performance in large datasets because data that is similar is automatically stored next to each other. This also means you get better compression. Think about the row in a table of customer data vs. what might appear in that table’s columns. One row has names, addresses, and demographic information. That row ends up with a really diverse set of data from a pattern perspective. However, look at the columns: you might see the same cities, states, or even names repeated very often. It’s pretty easy to go find all the Wisconsin listings if you’re looking at it from a column perspective.

The xVelocity/Vertipaq engine used in SSAS tabular models is same as what’s used in  PowerPivot, which means if your PowerPivot solution is getting too big for its britches, you can pretty easily convert it to a SSAS tabular model (assuming you have somewhere to host that SSAS tabular model)

If you want more interesting data to sandbox with than plain old AdventureWorks, check out NASA and other space-related data:

• National Space Science Data Center: http://nssdc.gsfc.nasa.gov
• Earth Observing System (EOS): http://eos.nasa.gov
• Sloan Digital Sky Survey (SDSS): http://cas.sdss.org/dr7/en

Mobile Business Intelligence by Jen Stirrup

Jen Stirrup gave us a lovely overview of what you need to consider when you’re building data visualizations for a mobile consumer. For example, line/bar/scatter charts work well in a compressed space, but remember that zooming is inconsistent across devices. Don’t expect that a bar chart with 24 bars is going to be easy to zoom in on for every user. The content she presented that really resonated with me was about data visualizations in general, from the judicious use of color and space to learning more about how we humans perceive data that’s presented. She referred us to other great minds in the data visualization space, so I have some reading to do.

• Stephen Few http://www.perceptualedge.com/blog/
• Edward Tufte http://www.edwardtufte.com/tufte/books_vdqi
• Howard Dresner http://howarddresner.com/
• Ben Schneiderman http://www.cs.umd.edu/~ben/

Another excellent point from Ms. Stirrup: work is now a thing you do, regardless of where you are. No longer is work just a place you go to do a specific task. We now engage with our work through many devices and in many places. Ask a coffee shop or an airport what their power bill looked like before laptops, tablets, and smartphones and you’ll know what I mean.

Finally, business questions are either puzzles or mysteries (originally presented by of Malcolm Gladwell of the New York Times).

• Puzzles: defined question with a defined answer. SSRS, Performance Point, and PowerPivot are great for tackling puzzles.
• Mysteries: we don’t know what we don’t know, we want access to everything. Mysteries require tools that allow easy exploration such as PowerView, PowerPivot (sometimes), or the simple Excel pivot table hooked to a rich SSAS cube.

BI Tabular Model Best Practices by Carlos Bossy

I really appreciated how direct this session was, especially because it made note-taking so easy. Here are some relevant bullets from my notes.

• Excel will still be the defacto standard for many business users. Embrace it, don’t fight it.
• Tabular models lower the barrier to entry for SSAS
• Make sure the users are in a position to get the right answers
• Use tabular model as a foundation for self service
• Not necessarily an either/or proposition for all SSAS use, though will require different SSAS instance
• Develop these models with your EXISTING data sources, such as an “extract table. It’s still worth making sure you have a good model to feed it, but don’t let perfect be the enemy of good
• The goal of these models is to promote “ad hoc” capabilities, such drag and drop and drop reportability.
• Best Practices
  • Relationships within the data are the most important — key to getting the right answers
  • Measures need to be built for the users if this model is going to be useful.
  • Hierarchies make the models easier to use
  • Calculated columns can be helpful, but might not be applicable in some cases. Depends on what shape your data is in

Last but not least, if your data is truly junky, a tabular model isn’t going to save you from that. I know, I know, sad panda.

Windows Azure SQL Reporting: What It Is and Why You Should Care by Stacia Misner

Stacia Misner is widely considered a luminary in the SSRS community, so when she says I should care about something, I snap to attention. Stacia delivered in this session, and we had the bonus of having someone from Microsoft right in the room to add specifics for this work-in-progress product.

Why to Use Azure SQL Reporting

• Easier to set up and deploy (when compared w/full SSRS)
• Easier to manage
• High availability (it’s in the cloud baby!)
• If you’re a small company that doesn’t have a big IT staff
• If you’re a large company that needs to provide access for mobile workers accessing corporate data via a report. Put some data in the cloud, reports in the cloud, keep largest chunk of data safe behind a firewall.
• Limited term project – set up and teardown is easier than provisioning physical resources

What You Will Have to Live Without (For Now)

• Subscriptions – this will be a deal breaker for some I’ll bet
• Snapshots and report data caching – keep those cloud reports nice and concise or they’ll plummet to the ground
• Reports that use external images – the cloud can’t get to your network drives
• Extensions/custom code in report/custom assemblies – the cloud doesn’t want your weird code, it doesn’t know where it’s been
• SharePoint integration – I will also bet this will change, SharePoint is too key to Microsoft’s portfolio

Inside DAX Query Plans by Marco Russo

I will be completely honest that a good majority of this session soared over my head. I’m not experienced with DAX (heck, I’m not even experienced with MDX), but I did glean some information from his presentation before he went where I couldn’t follow. This wasn’t the presenter’s fault, I knew going into this that it was probably too advanced for me. I jumped into the deep end and wasn’t surprised when I began to flounder. I would highly recommend him for those more experienced with DAX. If you’d like a review of an excellent book he co-authored, check out Jennifer Salvo’s recent blog post. Here are some items that did sink in to my inexperienced brain.

• Both DAX & MDX formula engines can query a tabular model, both use same storage engine (Vertipaq). Events therefore show up in SQL Profiler as Vertipaq
• Both DAX and MDX engines are single thread, but Vertipaq storage engine is multiple thread.
• To optimize a DAX query, you can ONLY optimize the query. No indexes, data model changes are of limited help
• There’s an excellent and free tool on CodePlex called DAX Studio. From what I saw in this session, it makes working with DAX much easier. Marco Russo also put a call out for people good with UI design, as they’d like to make this tool a little more slick from a UI perspective.

Extending SSIS 2012 Reports with Analytics: Patrick LeBlanc & Julie Koesmarno

After seeing this solution demonstrated, I can’t wait for it to show up on CodePlex. Patrick and Julie have taken the rich metadata available from a number of the SSIS 2012 catalog database views and transformed it into dashboard/data-mining format. If you want a better way to get a view into how your SSIS packages are performing – especially if they’re distributed across servers – keep an eye out for this on CodePlex. Both Patrick and Julie are strong presenters that bring good energy to their content, so if you ever have the opportunity to catch either of them, do so.

BI Power Hour with Matt Masson, Chuck Heinzelman, Matthew Roche, Patrick LeBlanc, Peter Myers, and Sean Boon

This was the last session I attended on day 2 of the Summit, and it was pure awesome. Its reputation clearly preceded it, as they packed a huge room. I laughed until I cried at the antics and “stupid BI tricks” from some of the top folks from Microsoft. Picture an improv comedy group crossed with “How to embarrass your friends and coworkers via the Microsoft BI stack”, with projectile swag thrown in for good measure. I can’t wait to see this troupe again at some future PASS event. It was the perfect way to unwind and even learn a bit after an intense day of sessions.

I am so grateful to have learned from some of the best minds in the SQL Server community, and can’t wait for my next PASS event. If you missed Summit this year, get a great taste of what it’s like at your nearest PASS Chapter or SQL Saturday ASAP!