Business intelligence has become much more mainstream than ever before in recent years. This is happening perhaps because it represents one of the last mountains left to climb in classic information technology.

The idea behind business intelligence is that organizations have the ability to gather huge amounts of information about their operations that when viewed in detail are often meaningless—the classic problem of not being able to see the forest for the trees. If you can collate that data and browse it in an ordered way, insights are there to be found in any business.

By employing the tools and practices of business intelligence, a retailer might discover that their sales of clocks increase shortly after a drought, or that unfinished furniture is more in demand in stores closest to the coast during summer months. There might be reasons behind the things that are discovered, but the real value in the data is the ability to exploit it and maximize positives or minimize negatives based on the nature of the insight.

Business intelligence is a great example of the adage “knowledge is power” put into practice. When I first heard it in conversation, business intelligence was the term used to describe the business-shaping data that could be gleaned by leveraging a data warehouse, as that was the primary tool for this kind of introspection back in those days. Data warehousing is about gathering all information about every aspect of a corporation’s operations into a single place and aggregating it across many dimensions.

This requires a special capability in the database server that Microsoft has supplied with its Analysis Services. This kind of data processing differs from most line-of-business systems. We refer to typical business systems as Online Transaction Processing (OLTP), but Analysis Services supports Online Analytical Processing (OLAP). OLTP uses database tables and lets the user read and write data as part of running the business. OLAP is the after-action review that figures out what is driving or holding back the business.

Rather than classic relational tables, Microsoft SQL Server Analysis Services provides the ability to shape this data into multi-dimensional cubes. To wrangle these cubes, Microsoft SQL Server Analysis Services leverages a special form of SQL syntax known as Multi-Dimensional Expressions, or MDX for short. MDX provides a language capable of navigating the OLAP database cubes.

About Patrick Hynds