Blog :: RDP Streamline Solutions

SQL Saturday #86 BI Edition Recap

Unleash The Tablix Tour

While I was on the plane flight home to Sacramento from SQL Saturday #86 BI Edition in Tampa FL, I thought I’d write about some highlights for me about this awesome event that Jose Chinchilla (Twitter | Blog) and an amazing team from the Tampa Bay BI User Group successfully organized. It was a rough trip getting there, but so worth it in the end. Many speakers that came are the “who’s who” in the Microsoft BI world. It felt amazing to be among so many talented folks: Adam Jorgensen, Mark Tabladillo, Rob Collie, Melissa Coates, Joe Celko, Dustin Ryan, Jen Underwood, Sanjay Joshi, Andy Warren and many new faces. Everyone in attendance was great and very nice indeed. One attendee whom I met during lunch gave me the 2nd most important line I’ve ever heard in my life: “Don’t wait too long.” We were talking about family trips cross-country in an RV, but in the moment that this gentlemen said this over lunch, I knew we were speaking about life in general.

Bay Area BI User Group - Unleash the Tablix

The trip to Tampa was a little hectic, but ended up being totally worth it. It started after presenting Unleash the Tablix to the Bay Area BI User Group Thursday night which went great. Fifty people attended and with the help of Mark Ginnebaugh (Twitter|Blog) and Will Brown, we were able to deliver the meeting after a last-minute room switch.

After the presentation, I hopped on a BART train to SFO airport to catch a red-eye to Ft Lauderdale. I underestimated the time it took to get to SFO on BART, and ended up running to the gate while hearing them call my name on the loudspeaker. Once that flight landed in Ft Lauderdale at 6:30AM, I had 45 minutes to get from Terminal 1 to Terminal 2 to switch airlines and catch my flight to Tampa. It was muggy and warm, but I made it with some time to spare. Folks in Florida had warned me about the humidity, and it wasn’t even summer anymore. Phew! I made it to Tampa at 7:30 AM, but had to wait for a shuttle to take me to the rental car company which put me at the pre-con with Adam Jorgensen at 8:45 AM. Amidst running in the humid morning in Ft. Lauderdale… running to my gate at SFO… battling with BART ticket machines, and hearing my name on loudspeaker, I had made it. Victory at last.

The pre-con was packed, and Pragmatic Works delivered as promised. Adam Jorgensen, SSAS Maestro and Vice President of PW, delivered the goods. The BI Bootcamp covered everything related to Analysis Services that you need to know to set it up, tune it, reporting clients, and even more interesting, PowerPivot. I jotted down a great reference for MDX called “MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook” and heard about a great Excel utility called the OLAP PivotTable Extension which exposes the resulting MDX from constructing PivotTables in Excel. I’ve been writing MDX for a year now, but hearing what an SSAS Maestro considers the “greatest MDX book” and “useful utilties” sort of makes you want to go out and buy it or download the stuff right away. The PowerPivot material was also just a pre-cursor to what was to become the highlight of my trip here in Tampa.

After the pre-con, we got together for the speaker dinner at the Tampa Bay Brewing Company in Ybor City. Good food and great company kept me going even though I was feeling tired from lack of sleep and the time difference on the east coast. I soon got my second wind as I caught up with my friends at Pragmatic Works and met a few new people over dinner like Rob Collie, Melissa Coates, and Mark Tabladillo. I called it a night at about 10PM as I was one of the fortunate ones to be first to speak the next day at 8AM.

The following morning, I presented “Unleash the Tablix” to a good size audience at KForce training facility at 8AM. After a couple cups of Dunkin’ Donuts coffee and of course, a donut, I got into my talk in which I describe the differences of Table, Matrix and List plus Visualization tips. The content of my presentation is on another blog post. All in all, there was good interaction with the audience and together we addressed a couple business problems to a very common business process: Purchasing. I was happy with the feedback and look forward to receiving questions and/or comments to any of the content. I also have some additions to make to my blog post, so look out for an update to the post on Twitter.

For the next time slot, I attended a presentation which made a big impact on me personally in my career. I went to Rob Collie’s presentation on PowerPivot: BI with massive data. In it, he showed his approach in using PowerPivot. Rob emphasized the fact that he knows no T-SQL, has never written an SSIS package, and has never setup an SSAS cube in BIDS. Yet, with PowerPivot, he was able to perform the same functions using the native tool he’s used to using: Excel. I was flabergasted! Rob went on to talk about the powerful DAX expression editor, how to setup multiple column keys in the data model, and also the top 4 DAX functions he thinks we should all get to know when using PowerPivot: CALCULATE(), ALL(), COUNTROWS(), and DISTINCT(). What was most impressive about Rob’s demo was the data: he got his hands on tons of NFL stats for the past few years. With one slicer click and a sort change later, he showed statistical analysis showing how Brett Farve was really really good one year and totally slumped the following year.. AND nobody talked about it! I don’t know how true it was, but it sure made for a great demo and grabbed my attention. He did all this with no T-SQL! SSIS! or SSAS! This definitely has me curious about what PowerPivot can do for business users.

Jose Chinchilla thanking sponsors, speakers, and attendeesAnother important take away I had was after lunch (which was a fantastic latin meal i can’t remember the name of) during the “Ask the Expert” Panel discussion when we were discussing SQL Azure. During a time when we have so many options in licensing models, SQL Server Editions, and hardware platforms, I was eager to hear what the experts had to say about the SQL Azure model and where it fits in. Brian Mitchell said part 1 of the great answer which was if you need a database which connects across many mobile devices, then its a good fit. Another Microsoft engineer there said part 2 of the answer which is when you care more about the design of the database rather than the infrastructure to maintain it, then SQL Azure is the best route to go. Both two really excellent points which in summary are if you meet this criteria, then you’re ready for SQL Azure: 1) Mobile 2) Design/Architecture 3)Virtually no maintenance.

The other excellent presentations I caught were:

  1. Reporting on a Cube by Dustin Ryan
  2. Page and Row Compression with Bradley Ball
  3. Dashboards… Which BI Tool to use? by Melissa Coates
  4. BI in the Cloud with Marc Miller
  5. Social Networking with Mark Tabladillo

Every one of these presentations was helpful in raising my level of expertise in the BI stack. Dustin’s talk helped me to realize how Date Range parameters To/From can be set in the Query Designer. Brad’s talk gave me some DBCC commands and considerations for tables that make good candidates for Page and/or Row compression. Melissa’s talk helped me see how flexible SSRS is in terms of visualization and functionality as compared to other Microsoft reporting clients. Marc’s talk helped give me a glimpse of what’s to come with SQL Azure, Reporting Services Azure, and Windows Azure. Mark Taladillo’s talk on Social networking was a lot of fun, and the whole group interacted on topics such as WordPress blogs, LinkedIn profiles, and Twitter feeds. With these talks, I caught a glimpse of how I can further my career as a BI consultant, speaker, and mentor.

The after party for SQL Saturday #86 was back at the Tampa Bay Brewing Company. Even after the event was finished, I still had plenty of opportunity to grow as I got to talk to Todd Davis and James McAuilliffe from Convergence Consulting one-on-one about business and the value consultants bring to the marketplace. We shared how hard work pays off and the phenomenal growth in BI in just recent years. We were all very excited about how the event turned out and the possibilities for upcoming BI Edition SQL Saturdays.

Being the only speaker/attendee from California, I received the same comment a lot: “you came all the way out here for this?” To which I would always respond, “Absolutely, why wouldn’t I?” I was surprised not to see the venue overflowing from people all around the US, but one thing was for certain, there were lots of top-notch companies looking to hire BI developers. So the real question now is, will you be there at the next one? If its BI Edition, and you’re a BI developer in any area of the BI Stack, you should. I really believe that anyone who is a BI professional ought to attend, speak, and come hang out with the incredible talent out at the BI Edition SQL Saturdays. Lots of opportunities to network and get hired. Demand for BI talent is growing at a very healthy pace, and from what I hear, the supply is not meeting that demand. So maybe I’ll see you at the next one. Hope so anyway.

Thanks again to the SQL Saturday #86 team for all the hard work they put into it. To the sponsors of the event, thank you also. Without you guys, this event could not be made possible.

Goodbye Tampa! Hope to see you again soon.Downtown Tampa

Intro to Microsoft Business Intelligence

I wrote an article for SSWUG last month titled An Introduction to Microsoft’s Business Intelligence Stack. Written as a case study, I walk you through how one company started off using Reporting services, and eventually needed more information analyzed with a quick turn-around.

Bottom line…

  • If you need data either consolidated or manipulated (same system or different systems), check out SQL Server Integration Services SSIS. You cannot go wrong because you’ll undoubtedly create more uses for your data.
  • If you have hierarchies in your data like products or organizational structures, or if there’s a large amount of data you need to aggregate over any dimension like time, you MUST start learning SQL Server Analysis Services. You will need to do SSIS before-hand, but you will not believe how much faster reports and web apps run when attached to SSAS cubes. The use of the hierarchies makes data analysts and executives very happy.

Please check out my article and leave any feedback you may have or even a case study of your own you’d like to share.

Fundamentals of the Tablix in SSRS 2008

The Tablix control in SQL Server Reporting Services (SSRS) is the most important control in your report design arsenal. It is a data region which displays your data in 3 different ways. Like Edward Tufte says, “Above all else, display the data.” This control does exactly that. I’ve given a presentation on the different ways a Tablix can be used from a business perspective. You can download the slide deck here: Unleash the Tablix Slide Deck.

A Tablix is not shown in the Toolbox as a single control, but as 3 different starting points: Table, Matrix, and List. Don’t worry about which one you start with because a tablix is easily molded to be a Table, Matrix, List, or combination of these. What you do need to remember are the fundamental differences between each one. It helps a lot to know how to go from one to the other during development. Especially in situations where you’ve done a lot of work with SSRS Expressions and Formatting that you’d like to keep right where they are.

Here are the fundamentals of each “starting point” (click on the images for full-size):




List Demo

Matrix Demo
  • Table is unidirectional. Its a lookup of information. It contains a single Row Group to start, but you create Parent Groups to Group the data and start Prioritizing it for the end-user.
  • List is nothing more than a rectangle in a cell. This allows custom layouts of all other items in the Toolbox.
  • Matrix is bidirectional. You begin with one Row Group and one Column Group. Now you can display intersections of Business Entities like Direct Cost Types and Product Lines, Department and Expense Types, Sales Regions and Company Division.

In addition to the technical terms, as a Report Developer, you should also be thinking about how to visually display the data in a simple, elegant, and efficient way. I’ve found the following guidelines from Stephen Few’s book, Show Me  The Numbers, to be extremely helpful:

  1. Group the data.
  2. Prioritize the data.
  3. Sequence the data.

Great Tablix Tips from around the web:

  • To create stepped reports and Toggle Items (or Drill-Downs as they are popularly called), my buddy Dustin Ryan made a great blog post. Check it out here.
  • Want to repeat the headers of your Tablix? SQLChick.com has a great blog post on what works, and even more importantly, what does not work.

Business Intelligence Is As Business Intelligence Does

If I asked you “What is Business Intelligence” (or BI for short), what’s the first thing that pops into your head? It depends, you might say. That’s true. After all, it depends on what job you’re performing. What job are you performing? What information do you need to do your job? Wouldn’t it be nice to have access to that information anytime, whenever and wherever you are?

If you’re a business owner or executive, Business Intelligence may get you thinking of that Dashboard report or Excel spreadsheet that you constantly check to ensure the status of your Business or Company. Not just the current state of affairs now, but also into the future.

If you Manage projects or Business processes, BI may get you thinking about all those reports you use to manage schedule, budgets, reports, etc. Or worse, the dreaded Forecast.

If you’re a Finance person or Business Analyst, you probably turn out these reports daily. Getting frequent requests for more and more information. Data that may or may not be getting collected, changing your process frequently to suit the needs of the business and or enterprise.

Well, truth be told, BI is in fact what people need to do their jobs efficiently and accurately. BI could take on many forms. In today’s day and age, this usually means using some type of software to collect data, analyze it, and display it in a simple, organized fashion. Typically business users and managers default to using Excel which is excellent. Its simple, powerful, and let’s you play (or analyze) with the data in many possible ways.

In this blog post, I want to expand the idea that BI is more than just the software you’re using, its a constant reflection on yourselves. The big picture of your Companies and Businesses that keep you accountable, honest, and moving forward. Business Owners, Directors, Executives, Engineers, Techs, or Admin people… everyone needs to take a look within and figure out fundamentally… who or what am I? where am I headed? is that the right direction? This insight is the reason BI helps guide business decisions. Once equipped with the knowledge of who, what and where you are, you can move on to bigger and better things… The next big thing… or just plain getting darn good at what you do. Or as Jim Collins titled his best selling book, Going from Good to Great.

BI also has to represent a single form of the truth that is mission-critical to the business. Collins said it in his book: two of the key factors in businesses that went from Good to Great is that 1) they established a culture where the hard facts are openly discussed (in comes Dashboards and KPIs)  and 2) information is turned into information that cannot be ignored. #2 cannot be done without people who understand the business. Understand the vision for a company and what is required to not just meet targets and goals, but to help motivate their teams to accomplish more and bring all they’ve got to the table to exceed expectations.

Lastly, what BI is not. BI is not any particular software platform, computer hardware, or particular consulting firm. The tools you use make no difference in the BI solutions your company needs. What makes for a good BI solution is simple, elegant, and efficient access to data. Stephen Few, a master at Information presentation and design, has a great blog post in which he mentions the opinion of a respected senior executive when asked “What is Business Intelligence”, his answer: Big Software, Hardly Intelligent. How sad, right? That means that consultants like me everywhere in the business of developing BI solutions for companies and businesses need to start reflecting, looking upon themselves, and establishing that their solutions are bigger than the platforms they support. No difference if its Microsoft, Sun, Apple, or whoever. The point is simple, elegant, and scalable.

And that’s all I have to say about that…