By: Jason E. Bacani
Business Intelligence Consultant
Two Saturdays ago (December 10th), I attended the latest SQL Saturday in Atlanta. This particular edition was the Business Intelligence (BI) edition, which is always held in late fall/early winter. And needless to say, this past Saturday started off as a chilly morning.
By my count, a few hundred people were at the event, all eager to download free, useful knowledge into their brains. SQL Saturdays are always insightful. I feel attendees should make the most of them by attending as many sessions as possible in order to learn new information or to bolster their current skill sets.
So with this post, I’m going to highlight the sessions I attended, starting bright and early from 9 AM all the way to 5 pm…on a Saturday!
1. Data Storytelling Using Power BI
Speaker: Jason Thomas
Twitter: @SQLJason (I knew someone had @SQLJason already!)
For the first session, it was refreshing and enlightening to hear Jason’s take on using reports to relay a story. In his example, from the earliest histories of mankind, stories are used to make sense of the world. In the primitive world, cave paintings were the first versions of ‘reports.’ And today, Power BI now offers us the story telling medium. In contrast to conventional reports, data story telling explains the “why” behind the story. Jason went on to showcase three demos: one on historical election results (not including the recent 2016 election), a second on Steph Curry and his amazing 2015-2016 season, and a last demo based on data metrics on an individual simply know as Will. Unfortunately no materials were posted to the SQL Saturday site by Jason, but his demos are showcased on his website. On a final note, some technical takeaways were the following tools:
- Synoptic Designer for Power BI – https://synoptic.design/ – This tool was used by Jason to make some of the custom graphical elements in his Power PI reports, such as the hexagonal representation of the United States used in his election results demo.
- Canva.com – https://www.canva.com/ – An excellent website where registered users can leverage several design presentations, social media graphics, and more with thousands of beautiful layouts already available for use.
- ColorBrewer – http://colorbrewer2.org/ – A handy site for determining best colors to apply for mapping and geospatial reporting.
- Instant Eyedropper – http://instant-eyedropper.com/ – A downloadable program that allows the user to quickly identify the HTML color code for any pixel on the screen. Good for matching colors you may use from templates found in Canva.com above.
I’ve reached out to Jason hoping to get a PDF copy of his slide deck. No response yet, but if I do get it, and if I am allowed to, I’ll be sure to share!
2. Deploying Power BI: A Blueprint for Implementation Success
Speaker: Javier Guillen
I must admit, the second session was rough for me, largely thanks to the exponentially dwindling effect of my first cup of coffee. Javier (the speaker) did not post his materials, but this link [ Blue Granite – Power BI ] from his company effectively covers what he spoke about. One of the main points from Javier’s presentation was that when rolling out a Power BI deployment plan, a company’s best case scenario for implementation success hinges on the existence of “shadow IT.” The “shadow IT” group truly champions the adoption of Power BI, as the group works together with the other departments of a business and IT, and not exclusive to either.
3. Tips and Tricks for the SSIS Catalog
Speaker: Jonathan Buller
This was one of the most valuable sessions I attended and, more than likely, this topic will be revisited by me in a future post. Admittedly, I am still a novice executing packages within SSISDB. Jonathan provided invaluable insight and illustrative examples on what to look for and the script examples he provided included scripts he has used time and time again to manage deploying SSIS via the Project Deployment model. Sure, SSMS has wizards and such, but what if you can use TSQL to manage the deployment scripts? More to come on this topic (I am setting myself up, mind you).
4. Power BI: Data Refresh and On-Premises Connectivity
Speaker: Patrick LeBlanc
You will always learn something from Patrick; you will never be bored by Patrick; and you will always be engaged in what Patrick is speaking about. That said, my ‘fandom’ of Patrick within the SQL Server Online Community goes all the way back to late 2010/early 2011. I lost touch with Patrick when he went from hosting SQLLunch.com lunch and learn sessions to a full time gig with Microsoft. Yes, he’s in the big leagues now. But he’s back showcasing Power BI. In particular, today’s session on connectivity highlighted some pros and cons as well as some caveats on how Power BI connects to its data source, whether direct query or otherwise. Content moves at a fast past with Patrick, which adds to his natural charm and charisma.
5. Advanced MDX Scripting: Scope Statements and Time Intelligence
Speaker: Shabnam Watson
With the final session of the day, I decided to go out of my comfort zone and tackle some MDX (Multidimensional Expressions). Now don’t get me wrong, I’m a BI Professional dedicated to the BI Stack offered by Microsoft comprising of Integration Services, Reporting Services, and Analysis Services. But Analysis Services is where I’m not at my strongest, so any opportunity to review SSAS, MDX, or any other aspect of Analysis Services is welcomed. With this final session, however, our dear Key2 Consulting colleague, Shabnam Watson, was presenting. For those who may not have heard, Shabnam participated at this year’s PASS Summit’s Speaker Idol, where she delivered a 5 minute session at Summit in Seattle, WA, for a chance to win a speaker spot at next year’s Summit (http://www.sqlpass.org/summit/2016). A seasoned speaker, Shabnam presented on advanced MDX, which I knew very little about, but have the following notes to help further my learning:
- A tuple in MDX is akin to a TSQL where clause
- Numeric columns from Fact tables can become measures in cubes; it’s at an aggregate level, unlike TSQL ‘select *’
- When possible, use physical measures versus calculated measures
- Columns from dimension tables become attributes in cube
- Returning to tuples: A tuple is a measure, with conditions via members applied, often in the form: Dimenssion.Hierarchy.Level.Member
- Leverage User Defined Hierarchies, they are your friends
- Scope statement, think of it a overwrite or as a sub cube
- A lesson learned: with scope, the overwrite in a calculated measure vs physical measure behaves differently
- Use of a ‘utility dimension’ is also known as a ‘hanger dimension’
- Hanger dimension is useful for creating calculations that apply to all other calculations in a cube. A common use case of a Hanger dimension is to provide Time Intelligence functions to end users / report writers
Whew. That’s a lot for a Saturday of free training. SQL Saturdays, in my humble opinion, are what the attendee makes of them. They’re free, yes. But they’re tremendous opportunities for learning. Many thanks for reading and be sure to follow us on Twitter and LinkedIn for more business intelligence/analytics insight and news!