SQL Server Magazine

Welcome to Mastering OLAP. This column will help you solve real OLAP problems using Microsoft's new Multidimensional Expression (MDX) language. OLAP enables users to work online interactively to analyze their company's data in multi-dimensional databases, and MDX is a query language for multidimensional databases. This column will be a hands-on tutorial. Each month we'll introduce an MDX tip and present a simple MDX puzzle to help hone your OLAP skills. This knowledge will be indispensable for database administrators' (DBAs') tasks, such as designing a data warehouse. In this first column, we define MDX and discuss basic OLAP concepts and terminology. In following columns, we'll explore the language in more detail. Let's start by discussing the question: What is OLAP?

Defining OLAP
Companies store volumes of valuable company information in OLTP systems and other corporate databases. Decision-makers use this valuable information to support business decisions. The difference between OLTP systems and OLAP is that OLTP systems help users capture the transaction information necessary to run their business operations, but OLAP systems analyze transaction information at an aggregate level to improve the decision-making process.

Traditional OLTP reporting systems produce reams of printed reports to aid in decision-making. These reports provide a lot of data (i.e., pages of numbers in grids), but they don't always convey much information. Traditional reports are usually static and can take days to generate. More important, traditional reports do not allow for what-if analysis. When you find an exception in the data, you usually need to run another report for more information. For example, suppose an OLTP system produces a report that shows sales and margins for all product lines in various European regions. You notice that product line B margins dropped in the past few months. You immediately want more detail about product line B: the cost of goods sold, average selling price, and discounts, for example. However, the OLTP system might take several days to produce a report that contains this information—and that report might lead to another question that requires another report. At this rate, you might need several weeks to find the source of the problem.

Don't fear, OLAP is here! OLAP systems let you view data in multiple ways. Thus, if you need answers sequentially, they are available in seconds rather than days. Also, OLAP systems are interactive, so users can ask follow-up questions and analyze their business data rapidly. In a 10-minute session with an OLAP query tool, a user might generate more than 30 queries to the underlying data source. OLAP systems greatly increase the quality of the information that you can use in decision-making. (For a quick tutorial about the vocabulary of OLAP, see the sidebar, "OLAP Terminology" page 60.)

Defining MDX
MDX adheres to Microsoft's new OLE DB MD data-access specification. (For more information about this specification, go to http://www.microsoft.com/data/oledb/olap.) OLE DB MD is the multidimensional database extension to Microsoft's new OLE DB data-access layer. Microsoft hopes that OLE DB MD and MDX become standards in the data warehousing market, the same way ODBC rules the OLTP and relational database market.

OLAP is the first step toward this goal. Currently, OLAP Services for SQL Server (formerly code-named Plato), Microsoft's new data warehousing extensions, is one of the few OLAP engines that natively speak MDX. In addition, vendors such as the SAS Institute and Applix have added MDX support to their products.

You can understand MDX by comparing it with Transact SQL (T-SQL), SQL Server's native SQL dialect (for more information about T-SQL, see Mike Reilly's "Writing Simple Select Statements," page 71). Front-end application developers use a variety of object models such as ADO and APIs such as ODBC to communicate with back-end SQL Server systems. However, the client system translates the object model and API calls into T-SQL, which SQL Server understands. Similarly, OLAP application developers can use ADO MD, a new set of ADO extensions, and the client system translates the object model or API calls into MDX so that OLAP Services can process them.

MDX and OLAP
Do you need to learn MDX before you run queries against OLAP Services? Absolutely not. Most Microsoft OLAP query tools are visually oriented and generate MDX behind the scenes when you run queries. If you don't know T-SQL, can you use a smart front end, such as Microsoft Access, to program a traditional SQL Server application? Absolutely. Will you get better data in a more effective way if you have a good understanding of T-SQL? Absolutely. The same idea holds true in the OLAP world.

You can use MDX in two basic ways. First, you can specify an entire MDX query: Like an SQL query, an MDX query uses a FROM clause to specify a data source (i.e., a cube), a WHERE clause to filter the data, and a SELECT clause to project the resulting data into rows and columns.

Alternatively, you can use MDX as an expression language. Most OLAP query tools provide a method to create custom numeric expressions. For example, suppose you want to find the percent change between this year's sales data and last year's sales data. You can use an MDX expression to perform this calculation. When you use an MDX expression in this way, the expression is similar to a formula in an Excel spreadsheet. We'll explore MDX's role as an expression language in future columns when we discuss calculated members.

   Prev. page   [1] 2     next page