Manage Learn to apply best practices and optimize your operations.

Baffled by decision-support systems for SQL Server? You’re not alone

Many looking into decision-support systems for SQL Server are overwhelmed with choices -- BI system? Data warehouse? In-memory analytics? Get expert advice on what is what.

I’ve recently been working with a number of small and medium-sized businesses preparing to jump aboard the decision-support bandwagon and purchase a system for SQL Server. Several of them have been asking me questions that reveal the enormous amount of confusion out there, such as “Do we need a data warehouse, or should we buy a business intelligence system?” That’s a question best answered by clearly defining some of the key terms the industry throws around.

Business intelligence (BI) is another name for decision support. In essence, BI refers to the business-level practice of gathering information in such a way that management and decision makers within the organization can quickly get the answers to key business questions. In other words, BI is a generic name for an entire functionality set as well as the supporting technologies.

A data warehouse is one possible component of a BI system. The idea is that most companies store data in many different places: customer databases, order databases, shipping databases and more. These systems can be slow to query individually, and answering key business questions may require data from several sources to be combined in unique ways. A data warehouse basically copies all of that data and puts it in one place, making it easier and faster to query and combine data.

In-memory analytics achieves more or less the same thing as a data warehouse, but it doesn’t require as much planning. Whereas the design of a data warehouse requires you to think in advance about how you want to combine data, in-memory analytics can retrieve and combine data on the fly. As the name suggests, these analytics take place in the memory of a server, meaning the server typically needs relatively massive amounts of memory and processing power. The idea of data warehouses was created before this amount of memory and processing power was readily available.

A BI system is a software package that implements a data warehouse, in-memory analytics, or both, along with a front-end system that allows users to interact with the system. That front end usually incorporates dashboards, reports, scorecards and other end-user elements.

You’re never going to have just a data warehouse, although you may well have a BI system that utilizes only a data warehouse. These days, it’s more likely that you’ll have a BI system that leverages a data warehouse and in-memory analytics, since the two technologies are complementary. The data warehouse can make it easy to quickly get standardized answers to predictable questions, while in-memory analytics serves as a kind of “ad hoc backup,” providing answers to questions that couldn’t be foreseen when the data warehouse was constructed. An in-memory analytics system can also draw data from the data warehouse as well as other sources.

The real moral here is to not focus at all on the underlying technologies. If you’re considering a BI system, look at its business-level features first and don’t worry so much about how the solution delivers those features. Whether it uses a data warehouse, in-memory analytics or even magic under the hood, so long as it’s able to provide the capabilities your business needs, you’re covered.

Don Jones
is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.