What Is Analysis Services and OLAP?

Because OLAP is at the heart of Analysis Services (formerly known as OLAP Services component), it is best to understand what it is and how it solves the requirements of decision makers in a business. As you might already know, data warehousing requirements typically include all the capability needed to report on a business's transactional history, such as with sales history. This transactional history is often organized into subject areas and tiers of aggregated information that can support some online querying and usually much more batch reporting. Data warehouses and data marts typically extract data out of Online Transaction Processing (OLTP) systems and serve it up to these end users and reporting systems. In general, these are all called decision support systems (DSS), and the latency of this data is determined by the business requirements they must support.

OLAP falls squarely into the realm of DSS and what is called business intelligence. The purpose of OLAP is to provide for a "mostly" online reporting environment that can support various end user reporting requirements. Typically, OLAP representations are that of OLAP cubes. A cube is a multidimensional representation of basic business facts that can be accessed easily and quickly to provide you with the specific information you need to make a critical decision. It is useful to note that a cube can comprise from 1 to N dimensions. However, remember that the business facts represented in the cube must exist for all of the dimensions being defined for the fact.

Figure 42.1 illustrates a Sales_Units historical business fact that is the intersection of time, product, and geography dimensional data. For a particular point in time (February 2001), with a particular product (IBM laptop model 451D), and in a particular country (France), the sales units were 996 units. You can see easily how many of these laptop computers were sold in France in February 2001.

Figure 42.1. Multidimensional representation of business facts.

Basically, you are looking at business facts via well-defined and organized dimensions (time, product, and geography dimensions in this example). Note that each of these dimensions is further organized into hierarchical representations that correspond to the way data is looked at from the business point of view. This will provide for the capability to "drill-down" into the next level from a higher, broader level (like drilling down into a specific country's data within a region).

Microsoft Analysis Services (AS) directly supports this and other types of data warehousing capability. In addition, AS allows a designer to implement OLAP cubes using a variety of physical storage techniques that are directly tied to data aggregation requirements and other performance considerations. You can easily access any OLAP cube built with AS via the Pivot Table Service, you can write custom client applications using Multidimensional Extensions (MDX) with OLE DB for OLAP or ActiveX Data Objects Multidimensional (ADO MD), and you can use a number of third-party OLE DB for OLAP-compliant tools. MDX is the multidimensional expression in SQL that enables you to formulate complex multidimensional queries.

The data mining capabilities that are part of AS provide a new avenue for organized data discovery. And, as is always the case with any object built by Microsoft, all OLAP object definitions are externalized into the Microsoft metadata repository (via Unified Modeling Language, or UML). It is also worthy to note that many of the leading OLAP and statistical analysis software vendors have joined the Microsoft Data Warehousing alliance and are building front-end analysis and presentation tools for Microsoft AS.

This chapter takes you through the major components of AS, discusses a mini- methodology for OLAP cube design, and leads you through the creation and management of a robust OLAP cube that can easily be used to meet a company's DSS needs.