Bridging the BI Gap

After the past couple of SQL Server releases, there’s little doubt about the importance of business intelligence (BI) for businesses. SQL Server 2008 R2 introduced PowerPivot and the concept of managed self-service BI. SQL Server 2012 extended this farther with the introduction of Power View, a data navigation and visualization tool.

BI can be a vital tool to help businesses be more efficient and more competitive by allowing staff to make faster and better decisions. But what is it about BI and data warehousing technology that enables better decision making? BI databases and OLAP cubes are generally composed of data aggregations that are derived from large relational datasets. This has two important benefits for decision making. First, because the data that's vital to your business's operation is collected over time, the data in BI databases lets you see trends and relationships that you couldn’t see with just relational queries. Second, because the data is aggregated, the ability to perform ad-hoc decision support queries is light years faster than generating similar reports from relational data sources. A report or query generated from a relational database might need to process millions of rows to come up with the same results as an OLAP query that only needed to access a handful of cells.

Related: Three Mistakes to Avoid in your Business Intelligence Project

Over time, SQL Server Pro's Instant Polls and reader surveys have shown that our readers are split in their use of BI. About a third of our readers are BI experts with five or more years of experience in deploying BI projects. However, the majority aren’t so immersed in BI. The remaining two thirds of our readers are roughly split between organizations that are just beginning to deploy BI projects and those that have no plans to deploy BI or consider BI a future objective.

Of course the biggest hurdle in adopting BI is learning new technologies. The familiar third normal form (3NF) used in relational databases isn’t the design standard for BI and data warehousing. BI implementations use new designs, such as star schema and snowflake schema, which are foreign to most OLTP guys. BI doesn’t just happen out of nowhere. To get started with BI, you need a project. The type of project depends on your organization's future plans with regard to BI. For organizations that are looking forward to imminent BI deployments, the fastest way to get up to speed is by bringing in external BI consulting expertise. This can help infuse real-world experience into your organization’s efforts, as well as help quickly spread BI knowledge to your other team members. For organizations in which BI is a future objective but there are no immediate plans, the best place to get started is with low-profile pilot projects where you can begin developing BI expertise in-house. For instance, one possibility along these lines might be building a performance data warehouse using SQL Server’s own dynamic management views (DMVs).

In addition, there’s the server aspect to consider. If you’re just getting started, the best approach is to conduct your pilot on existing hardware or on virtual machines (VMs) in your own infrastructure. However, if you later want to move those pilots into production or if you’re planning for a production BI implementation, you’re likely to discover that the configurations and tunings that you’ve used for your OLTP relational database systems don’t work so well for analytic BI and data warehousing systems. The primarily reason is that the nature of the workloads is very different. OLTP workloads generate the operational data used in BI implementations; therefore, there's a significantly higher number of write operations. Analytic and data warehousing are just the opposite. They involve queries and reports in which the bulk of the work is read operations. Unfortunately, many organizations­—and especially those organizations just attempting to get BI projects off the ground—don’t have a lot of experience in designing scalable systems that will handle BI and analytic workloads. That’s where fast track architectures come in. Microsoft, working in conjunction with hardware vendors such as HP, DELL, and IBM, has created a number of different reference architectures whereby you can leverage the expertise from both Microsoft and the various hardware vendors to ensure that your systems are properly configured for BI workloads.

SQL Server has always been a leader in the BI space. Since the release of SQL Server 7, there's been support for BI implementations in SQL Server Standard and above. Most organizations already have SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSRS), and SQL Server Reporting Services (SSRS), and now there’s new and exciting tools such as PowerPivot and Power View. Each release has continued to add to the SQL Server BI toolset, and it definitely makes a lot of sense to put these tools to work—especially if you already have them.