Do you use SQL Server business intelligence (BI)? SQL Server Magazine Instant Polls over the years have revealed a big divide in business intelligence (BI) usage among our readers. These unscientific surveys indicate that about one-third of our readers are BI experts, with 3 to 5 years worth of BI experience. For the remaining two-thirds BI is unfamiliar territory. They’re aware that BI is built into SQL Server and usually intend to use it. But they’ve never gotten started, despite the fact that SQL Server’s BI capabilities have been around for more than 10 years (OLAP Services was first introduced with SQL Server 7 in 1998). So what’s so hard about BI that makes it difficult to adopt? There are a number of factors but I think you can sum it up with the three Ts: terminology, technology, and tools.
The first hurdle to get past is terminology. BI is rife with unfamiliar terms such as cubes, dimensions, and measures—to say nothing of specialized acronyms including UDM, KPI, OLAP, MOLAP, and HOLAP. To start learning the terminology Microsoft SQL Server Books Online provides a decent glossary. In addition, a pending update to www.sqlmag.com will include a wiki that defines all BI and standard relational database terms and acronyms.
The technology behind BI is also foreign. Most of us learned how to normalize data, but with BI data warehouses our cherished third normal form normalization goes by the board. Data warehouses want unfamiliar messy looking things like star and snowflake schemas. Although they seem strange at first, you’ll soon appreciate the way these BI technologies are optimized for reporting. You can find a deep understanding of BI concepts and technologies in Michelle Poolet’s Solutions by Design column. For example, check out her article, Discover the Star Schema, July 2007, InstantDoc ID 96112.
BI tools are different from relational tools. Instead of T-SQL you have MDX. Instead of ADO you have ADOMD. Instead of SSMS you have the BIDS. If you start to work with BI you see the similarities to relational tools, but at first all these things can seem quite foreign. Do you remember Russ Whitney’s “Rock Solid MDX” column in SQLMag a few years back? We were ahead of the curve on that one, and it’s worth exploring still. Get started learning about the basics of building MDX from a T-SQL perspective at Rock Solid MDX, April 2003, InstantDoc ID 38005. If you want to learn more about ADOMD.NET from the ADO.NET programmer’s point of view, take a look at William Sheldon’s Building a Custom Application with ADOMD.NET, October 2008, InstantDoc ID 99908.
Overcoming the BI Barriers
What can you do to get started using SQL Server’s built-in BI in your organization? A major barrier to getting started is that typically BI projects are big, bet-the-business endeavors that promise to revolutionize critical processes. Sometimes the projects are so weighty that they never get off the ground.
Why not start with what you know, instead. One of the best suggestions for a BI starter project came from Donald Farmer when Sheila Molnar and I spoke with him recently in Redmond. Donald suggested applying the BI tools to SQL Server’s own dynamic management views to better understand the performance characteristics of your server. This approach has a couple of advantages. First, the data is readily present in all SQL Server systems. And what DBA doesn’t want more insight into the performance characteristics of his or her system? Second, you’re already knowledgeable about the type of information that you need to see. Hence, you can be your own business expert. If you have other ideas for getting started with BI drop me a line at firstname.lastname@example.org or email@example.com.