Category: SQL Saturday

My Thoughts on SQL Saturday #596 – Denver BI

I had the pleasure of attending SQL Saturday Denver – BI this past weekend. They even let me help out a bit with registration and other volunteer tasks. This SQL Saturday was an experiment of sorts to prove out Steve Jones’s idea of slimmer SQL Saturdays. We had two tracks and 80 – 100 attendees. Steve would like to see each city be able to do 4 SQL Saturdays a year (which is currently against the rules), but keep them slim.

I think it’s great that Steve and Carlos put together the event for about $650 (and I heard it would have been close to $300, but they decided to do a speaker dinner to use up some sponsor money). This should show other organizers that their event doesn’t have to be big and expensive to be considered successful. Everyone had a good time and learned new things, and the venue was nice. They worked with a local university to get the space for free, which is much easier to do when you only need three rooms and a hallway. The quality of speakers was still quite high (Peter Meyers, Melissa Coates, Steve Wake, and others).

Part of the slimmer SQL Saturday is that they didn’t provide lunch. But our venue was within walking distance of several places, and it was nice to take a walk and get whatever food I wanted.

My Concerns and Things I’m Still Pondering

Here’s what I didn’t love or what I need more time to consider compared to other SQL Saturdays:

  • A lot of SQL family didn’t attend because they weren’t speaking and didn’t want to take up a spot for someone else who might be attending for the first time or needs the learning opportunities. For me, SQL Saturdays are about learning and community. I missed some of my SQL people. Having slimmer SQL Saturdays also means that the range of topics isn’t as broad, and there may be less incentive for more experienced people to attend (outside of the community aspect) if most/all of your topics are beginner level.
  • The little things matter to me. I ended up printing session evaluations so that speakers could get feedback and making sure people knew they could submit feedback online. Would the event have been fine without evals? Yes. But do some speakers very much want feedback from the audience, especially when trying out new sessions? Yes. If they had warned the speakers ahead of time, the speakers could have grabbed a few trusted people and asked them to attend their talk and provide feedback, making this a non-issue. I think whatever you can do to make things run smoothly and give people a good experience is usually worth it. Evals fall into that for me, but I fully acknowledge that they do not make or break the event.
  • There is still some overhead associated with planning even a small event. You still have to secure a venue, choose speakers and set the schedule, market the event, and spend your Saturday running the event. This is fine, sometimes even fun. I have organized 5 SQL Saturdays, and enjoyed it. But it is still time-consuming, and doing 4 of them a year makes me feel tired just thinking about it. If you can assemble a team of volunteers where 2 – 3 people plan and execute each event and you rotate duties, that sounds reasonable to me. Not every city has such a good team, though. We are more than just SQL people and lives get busy with personal or even other professional stuff. This needs to be something that isn’t overly burdensome for any one person in order to make it work.
  • Someone else made the comment “If we do these quarterly, what’s the difference between this and user group meetings? You would spend about 8 hours a quarter during the week attending meetings or 8 hours in one day attending a slimmed down SQL Saturday.” I can understand that thought process. I think of SQL Saturdays as a special once/twice a year thing. I don’t know that smaller/more frequent SQL Saturdays are better or worse than the norm, just different. I imagine that each city would find their own way to differentiate the value of SQL Saturday vs the user group.

My Takeaways

I hope this helps prove that a small event can be a great event. Do not feel like a failure just because your event doesn’t have 350 attendees or because you couldn’t get shirts and expensive gifts for the speakers and volunteers. I will admit that there was a bit of pressure to be bigger and better each year that I organized SQL Saturday KC, but that was almost entirely self-imposed. This was a good experience to help me really understand what is essential versus what is nice to have. When it comes down to it, having a slim but well planned SQL Saturday is better than not having one at all. That being said, if your SQL Saturday is large and well-funded and making people happy, don’t change a thing. Slimmer SQL Saturdays provide alternatives for events with smaller markets and/or smaller sponsorship availability.

I am now a fan of letting people get their own lunches, if your venue is in a location that can support it. Lunch at SQL Saturday KC was always expensive and took several volunteers to set up (taking money, placing orders, having food delivered and set up, accommodating dietary restrictions). And there were always people who felt like it wasn’t worth the $10/$15 dollars and wanted to complain to us afterward. As an organizer, I like the idea of skipping the headache of lunch and giving people the freedom to go get what they want. Plus it’s nice to take a walk after sitting in sessions all morning.

I love the partnership with University of Denver, not just use of their space. Part of the agreement made in getting the space at no cost for SQL Saturday Denver – BI  was that there would be sessions that were relevant and accessible for some of the students. Although there are more and more higher education programs focused on BI and data science, I still think the opportunity to get applied learning from the “real world” is valuable for them. I hope to see more SQL Saturdays partner with colleges and universities in the future.

I give this slimmer SQL Saturday two thumbs up and think others should consider it an option. Each event organizer should decide what’s important to them and make it happen. But know that you can have a good event for less than $1000 and minimal time spent planning if that is all you have.

I’m excited about Kansas City SQL Saturday

It’s that time of year again. School is about to start, the weather is getting even hotter, beer festivals are occurring every weekend, and we are busy planning KC SQL Saturday. This is our sixth year (and my fourth year on the organizing committee), and I must say that I am genuinely excited about some of the changes we are making for this year. If you are in the Kansas City area and haven’t been to a SQL Saturday before, I encourage you to attend this year. Registration is free, and lunch is $15.

New Location and New Sponsor

Kansas City SQL Saturday 2015 will be held on October 3rd at Rockhurst University in Arrupe Hall. This brand new building has wonderfully functional and comfortable classrooms with HD projectors (with HDMI hookups!) as well as a theater that seats 500 people. There are also several areas throughout the building for attendees to sit and chat. Parking should be easy as Rockhurst is opening up a garage for us and there is also street parking available nearby.

We are happy to have Rockhurst as a new sponsor, and we think it is a great fit. The Helzberg School of Management has a new MS in Business Intelligence and Analytics program. The program “takes the data science techniques of statistical modeling, computer science and machine learning, and then adds the implementation, business and strategy skills you need to be successful in a corporate environment.” I’m glad that we have a program like this in KC, and glad to partner with them for SQL Saturday.

Moving SQL Saturday to a location just southeast of the Plaza provides a quicker drive for many local attendees and a better experience for those coming in from out of town.

New Recommended Hotel

Moving to the Westport/Plaza area also means we have improved options for hotels. This year our recommended hotel is the 816 Hotel. See the event news on the SQL Saturday KC site for instructions on how to get the discounted rate. 816 Hotel will provide free shuttle service to and from Rockhurst for SQL Saturday. The wi-fi, breakfast, cocktail hour, parking, and gym access are all included in the price of the room. Be sure to book your room by September 2nd to get our group discount rate.

New After-Party

If you aren’t aware SQLKaraoke is a thing. This year we have the VIP room at OffKey Karaoke reserved from 7 – 9pm for our after-party. This gives everyone time to grab some dinner at one of the many restaurants in Westport and then head over to sing or just enjoy the company. Our room holds up to 50 people, so feel free to join us. We’ve paid for the room, but you will need to take care of your own bar bill. If you are staying at the 816 Hotel, OffKey is just a few blocks away, within easy walking distance.

New Ride

If you are a speaker or guest who is flying in to KC to come to our event, you can now get Uber to take you to and from the airport. With the availability of Uber and the shuttle service from 816 Hotel, it’s possible that our out-of-town guests won’t need a rental car at all. We hope this helps keep costs down and make life easier.

Same Great BBQ

We are still having a delicious BBQ lunch at SQL Saturday, and it will still be supplied by Joe’s Kansas City. Vegetarian options will be available from Eden Alley once again as well. Please be sure to let us know if you have special dietary needs when you register for the event.

Also, we are once again doing a BBQ Crawl on the Friday afternoon before SQL Saturday for the speakers and volunteers. We try to hit up 3 places and get samples of their best dishes. This event is getting rather popular to the point that we had a caravan of 10+ cars roaming KC last year on our BBQ Crawl. This year, our new location makes it easier to plan a route that minimizes travel time and keeps people in the Westport area. Details of locations and times will be announced in September, but we usually start around 1:30 or 2pm. If you are a speaker or volunteer, be sure that you are in town and off of work in time to join us. We will have a speaker dinner later Friday evening, but many of us may be eating lighter since we will have been eating BBQ all afternoon.

It’s Not Too Late

If you think the BBQ Crawl sounds awesome, you still have time to submit a session and become a speaker! In addition to gaining your entrance to the BBQ Crawl and other fun, becoming a speaker sharpens your presentation skills, gives you practice explaining technical concepts, and increases your networking opportunities. For tips on picking a topic and writing and abstract see Devin Knight’s blog series and Adam Machanic’s blog postThe call for speakers closes August 4th. I hope to see you at SQL Saturday Kansas City!

Choosing A Mapping Tool in the SQL Server BI Stack

With the addition of the Power BI suite to the SQL Server BI stack, there are now 3 main options for creating data-driven maps. I have been working on a presentation to help you choose which mapping tool is appropriate for your needs on a given project. I gave the presentation for the first time in SQL Saturday Chicago, and I’ll be presenting it again at the first SQL Saturday – BA Edition in Dallas on May 3. I’ll share the highlights of the information I provide in my presentation in this blog post, and you’ll just have to attend a SQL Saturday where I’m speaking to get the rest of the details.

There are 3 main tools between Excel, SQL Server, and SharePoint that can visualize data on maps:

  • Power View
  • Power Maps
  • Reporting Services

There are also a few apps for Excel 2013 that create maps (Bing Maps, Geographic Heat Maps). I’ll explain the Excel apps in a later blog post, and focus on the more mainstream tools for today. Although you can make maps with each of these tools, there are some significant differences in features, time to delivery, and system requirements for each of these tools. The image below summarizes the pros and cons for each of these tools.

Maps OverviewPower View is the easiest to use, but it has the most limited features. The drag and drop interface allows you to build a map in a matter of minutes. But this ease of use comes at the price of limited customization. The only type of graph you get on a map is a bubble chart, which turns into pie charts if you add a category. You can only build one layer of data on your map. Also, fonts and colors are limited to the themes available within Power View. Power View offers great interactivity and cross-filtering capabilities. You can set up your map so it drills down from country to state to city. You can use slicers and other graphs to filter and highlight data on a map. An example of using a map to filter a related table in Power View can be found here. Power View reports are usually visually appealing and lend themselves well for presentations. And if you are using Power View inside of SharePoint, you can export your Power View to a PowerPoint presentation and retain interactivity.

Power Map’s strongest features are a time axis that enables you to watch trends over time, the ability to show multiple layers at once, and the storytelling capabilities that come from scenes and tours. Power Maps have more choices for graphs, including columns, bubbles, heat maps, and region shading. But there is no drill-down capability to see an increased level of detail. Power Map also lacks the ability to filter data.  For example, if you have sales data for three products and you only want to see sales for Product A, Power Map doesn’t offer a way to filter to just Product A. This means that you may have to add some DAX calculations to your Power Pivot model to facilitate your map creation. DAX has its own learning curve, so this increases the time required to build the map if you have to learn DAX and alter your Power Pivot model first. Update: As of Sept  2014 you now have the ability to filter your data in a Power Map. The great thing about Office365 is that you can get monthly updates.  The inconvenient thing about that is I have to go back and update blog posts when this happens. Power Map can facilitate great storytelling with data through the use of scenes and tours. You can look at a particular geography and time period in each scene and combine scenes into a tour, then you can share that tour to take users on your journey to gain insight.

Although Reporting Services isn’t getting much love from Microsoft’s marketing or engineering groups, it is still a very viable option for creating maps. While SSRS can require more technical expertise and time to deliver a finished product, it allows the most customization of the three tools and offers subscriptions (time-based and data driven) and multiple rendering and export formats. It also allows custom shapes, so you aren’t limited to whatever the Bing Maps API can do. This helps with custom geographical regions within a company or its customers. It also means you can map more than just points on a globe; you can map floor plans and other shapes you can capture with SQL Server spatial data types. Where Power Map and Power View don’t necessarily require a lot of infrastructure to share a report, SSRS reports require a Report Server or SharePoint Server to make a report available to users.

I’ve developed the following high-level categories to be used to evaluate the appropriateness of each tool for your map.

Maps Eval CriteriaI used these evaluation categories to summarize my findings for each tool in the table below. You’ll notice that I split Power View into Power View for Excel and Power View for SharePoint. This is because these two different development environments have some different system requirements, data sources, and methods of consumption for the resulting Power View report. Although there are two different modes of Reporting Services (Native and SharePoint Integrated), this difference only affects system requirements so SSRS was kept as a single category.

Maps SummaryFor system requirements, note that Power View works with Excel 2013 Professional Plus and O365 Pro Plus and a few other versions, but you must have an Office 365 Pro Plus subscription to get the GA version of Power Map.

Although the Power Map release notes state that you can use it with a 32-bit processor, large amounts of data or graphics intensive map layers can slow the computer to halt or cause Excel to crash. If you can help it, it’s best to have a 64-bit computer for Power Map development or consumption in Excel.

You’ll notice that the list of data sources for SSRS is fairly long compared to the other development environments, but don’t let that mislead you. I’m just listing direct data sources. Although Power Pivot only takes up one line, it can connect to a multitude of data sources, and Power Query extends those options even further. SSAS Tabular also has many options for data sources.

Also, be aware that bubble sizing in Power View for small values can be misleading. This issue is compounded by the fact that turning on data labels for a Power View map only labels the location and not the amount related to the size of the bubble.

In my presentation at SQL Saturday Chicago, one of the attendees expressed the opinion that Power Maps tend to be very busy and it’s difficult to tell where to look when you first encounter one.  I don’t disagree with that, so I’ll throw in the warning that it can be very easy to make a Power Map that is visually appealing but doesn’t provide any insight into your data. I think there are some good use cases where Power Map is the best tool to deliver insight, but not every map needs a time axis and multiple layers/multiple graph types. Another limitation of Power Map is that is can only be opened from the Excel file that contains it (there’s no way to view it in a web browser). While I expect that to change in the future, this is the current state today. Outside of exporting a tour to video, the development environment and the viewing environment are one and the same. To create a new Power Map or insert an existing Power Map, you must go to the Insert tab in Excel and choose Map. This will open the Power Map window where you can view and update your map.

I also want to point out that SSRS is the only one of the three tools that can make use of SQL Server spatial data types. If you would like to learn more about spatial data types in SQL Server, Hope Foley has a great presentation on the subject and has her slides and scripts posted on her blog. Jason Thomas has some good blog posts on SSRS maps.

I’m Speaking About Geospatial Data Viz and Data Viz Best Practices in April

I will be speaking at two SQL Saturdays in April.  First, I will be at SQLSaturday #297 in Colorado Springs on April 12.  I’ll be presenting my session on The Accidental Report Designer: Data Visualization Best Practices in SSRS.  See my previous post on this subject to understand why I think it is an important message for all data professionals.  In this session, I share guidance on evaluating the effectiveness of data visualizations and a few good tips and demos to take your data visualizations from chaotic and ineffective to professional and powerful.

Next, I will be at SQL Saturday #291 in Chicago on April 26.  I will be presenting a new session to help you Choose Your Geospatial Mapping Adventure. Here’s the abstract:

You have geospatial data and you know that plotting it on a map will deliver insights. Or maybe you keep hearing “geospatial” as a current BI buzzword and you want to be informed about your options when you get to it. The Microsoft BI stack provides several options for visualizing location data. But how do you know which tool will best serve your purpose, or what factors are important in making this decision? We’ll identify factors to consider when choosing a tool to visualize your data in maps such as system requirements, availability and type of location data, required level of technical understanding, and more. You’ll leave the session confident in your ability to choose the best tool within Excel and SQL Server.

The Microsoft BI stack offers several great options for presenting data on a map, but your decision of which tool (Power View Map, Power Map, SSRS map, free mapping apps in Excel) can limit you in features, accessibility, and time to delivery. Some of the tools require more technical skill than others, but you lose some of the ability to customize when you go with the highest ease of use.  The session will be filled with tips, explanations of features and requirements, and demos.

If you will be in the area on the dates of these events, please stop by to participate in my sessions or just say hello.

I’m speaking in January about data visualization

I am excited to have two opportunities to speak in January. The first is the Kansas City SQL Server User Group. I will be speaking at the monthly KCSSUG meeting on January 16th at 2:30pm CST. You can RSVP for the event here. Next I will be speaking at SQL Saturday #271 in Albuquerque. At both events, I will be presenting on The Accidental Report Designer: Data Visualization Best Practices in SSRS.

This presentation is geared towards data professionals who may not see report design as one of their main responsibilities, but who occasionally have to deliver information through reports, dashboards. I think most people have to do this from time to time in their jobs, so anyone could benefit from this information. I am passionate about the content in this presentation because it changed the way I work and has positively impacted my career path. My interest in data visualization started when I read Stephen Few’s book Information Dashboard Design while working on a marketing dashboard at a previous job. I strongly believe that the way you display information to the end user can enhance or render useless any great data model/ETL you have created. The end user of your report is probably missing the point if you aren’t providing useful information in a consumable format.  Poor report design, in addition to being ineffective, can actually mislead your audience. As a data professional, this should concern you.

As an example, let’s look at some crime data for my neighborhood that I pulled from data.kcmo.org. I want to know: what was the most common crime committed in my neighborhood in 2013?
First, take a look at this graph:

crimes donut chartThis is a 3-D exploded donut chart that shows the number of crimes committed by type.

Now let’s try to answer the question with this graph: crimes bar chartThis is a bar chart that shows the types of crimes committed as a percent of total crimes. I think you will agree that you immediately see from this graph that non-aggravated assault is the most common crime, followed closely by stealing from an automobile.  I can easily see the percent of total incidents that each type represents and how each type compares to other types.

Even if your data isn’t saving any lives, you can still learn to make professional data visualizations that effectively communicate information while being visually appealing, which can impress and earn trust from your management, clients, and other audiences. If you would like to learn more, I would love for you to attend my session in KC or Albuquerque. If you can’t make it, feel free to check out the slides on the Presentation page.

I’m speaking at SQL Saturday #190 in Denver

sqlsat190speakerI’m looking forward to speaking at SQL Saturday #190 in Denver, Colorado. This will be my 6th SQL Saturday at which I have spoken. It’s becoming a habit! The title of my session is The Accidental Report Designer: Data Visualization Best Practices in SSRS. Here is the description of my session:

Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!

I’m passionate about data visualization. Data professionals work so hard to gather, integrate, cleanse, aggregate, and deliver data to users. Creating a good report turns that data into actionable information, taking it the last mile. Consequences of bad report design range from failure to provide appropriate business insights to misleading users and causing them to draw incorrect conclusions. As much as I enjoy my job as a data professional, my job does not exist simply for my enjoyment. My goal is to create solutions that provide timely, accurate, usable information to ultimately help businesses cut costs or create new revenue. I’m missing the mark if I allow poor report design to jeopardize my efforts.

Report design is not just for BI professionals. Pretty much everyone (business users, data professionals, developers, managers) has to create a report at some point in their professional lives, whether it is built in Excel using static data or embedded in a web page using .NET and javascript. While there is no one true way to design a report, research (and some common sense) shows that some methods are more effective than others. I frequently encounter two common mistakes. In our strive to make things visually appealing, we often lose sight of the goal of effective communication of the appropriate information, opting for bling over business insight. On the flip side of that, many people default to a wide table full of hundreds of lines of data rather than spend the time to plan our graphs or charts to highlight the important trends and indicators.

When I began learning data visualization best practices, I felt like my eyes had been opened to a facet of my work that I had been unintentionally ignoring. I realized I had been making several common mistakes simply because I did not understand the user experience I was providing through my design choices. If you are a developer, it is important to realize that reports/dashboards/data visualizations require as much planning and thoughtfulness as any other user interface design. It is not enough to slap the graph on the page and move on.

Here are some online resources for those who would like to learn more about report design and data visualization best practices: