Image courtesy of Shutterstock.
Here’s a real-world story: Data collection and analysis is, ironically, for a data management profession, one of the toughest parts of my job. I regularly pull data from our various databases and packaged application reporting systems for CRM, licensing, and user information.
If you’ve ever worked with a packaged product, such as a CRM system, that includes reporting and querying features, then you know how inflexible they are. It can be infuriatingly difficult to get data out of these systems, systems for which you paid big money, without losing sleep, hair, and peace of mind. Working with our own custom licensing and user information databases is no big deal, of course, since it’s built on a standard relational database and uses SQL. So resorting to regular SQL queries, while time consuming, is effective, convenient, and reusable. At least, it should be no big deal. But then issues start to arise. Non-standard data types might be in use. Some tables are denormalized and contain too many bits of distinct data within a single value. Problems, and frustrations, start to rise.
Enter PowerBI (at www.powerbi.com). PowerBI is a set of add-ins to Excel 2013 which combines the data management features of SQL Server relational databases and SQL Server Analysis Services (SSAS) cubes, with easy and ubiquitous Excel. (See more examples at http://www.microsoft.com/en-us/powerBI/home/discover.aspx).
This morning in literally 30 minutes, I went from a bare-bones install of Excel 2013 to install the PowerBI and PowerPivot add-ons, queried the data within Excel directly from my SQL Server and SSAS cubes, to munging and assessing the data.
My previous method of gathering and assessing this data was to write SELECT statements, which left me unable to collect data from SSAS cubes because I’m no good at MDX. It also left me with lots of post-extraction work to do in which I would have to tinker with results in Excel to make the data readable for the executives. Lots of duplicated effort. Now, I can pull from multiple data sources directly into Excel and manipulate the data there. It even provides easy query builders, both for SQL and MDX, so that you don’t have to write any code. (You can also save the queries separately from the spreadsheets).
The Mentality of the DBA
Okay—I’m convinced of its power. However, my conservative nature as a longtime DBA kicked in at this point. This is a powerful feature that I’d like to institutionalize within my organizations, but there are some significant challenges because so much of this important and useful work is now being done on desktops. Who will make sure these PowerBI spreadsheets are backed up? Who will curate them and ensure they are disseminated within the organization? Who will offer support when questions arise or requests for updates come up?
Microsoft offers solutions for these issues as well, either by integrating your PowerBI solutions with SharePoint collaboration and data management features or by utilizing the cloud-based features of Office365. (Read more about these approaches at www.microsoft.com/en-us/powerBI/home/share-collaborate.aspx). That way, you can effectively create, share, backup, and provision your PowerBI spreadsheets.
There’s no better way to get PowerBI up and running than to start playing with it, just like I did this morning. If you’re interested, I strongly encourage you to take a look at the curriculum Microsoft has posted at the PowerBI team blog, http://blogs.msdn.com/b/powerbi/archive/2014/07/08/getting-started-with-excel-and-power-bi-series.aspx. This site takes you through not only how to use PowerBI, Power Query, and Power Pivot, but also the basics like pivot tables in Excel. So what are you waiting for? Get started!
Kevin Kline, a longtime Microsoft SQL Server MVP, is a founder and former president of PASS and the author of SQL in a Nutshell. Kline tweets at @kekline and blogs at http://kevinekline.com.
Follow Kevin Kline on Twitter and Google.