Introducing Microsoft Analysis Services

Windows IT Pro

- Advertise

Introducing Microsoft Analysis Services
View the book table of contents

Author:	Teo Lachev
Published:	December 2005
Copyright:	2005
Publisher:	Prologika

1.3.1 Relational Model Features

At this point, some of the OLAP-savvy and perhaps skeptical readers may wonder if the notion of UDM is not too far-fetched. Let’s enumerate some the most prominent characteristics of UDM that justify its bold vision to become the "next stage" for OLAP. We will start with the UDM features that bring it closer to the relational reporting model.

Rich schema
As I mentioned, one area where the classic OLAP falls behind compared to the relational model is loss of data fidelity. The end product of the classic dimensional model could be intuitive user hierarchies but at the cost of losing the ability to browse data from other perspectives. One of the most exciting new features of SSAS 2005 is attribute-based dimensions. With UDM, each column (attribute) from a dimensional table can be exposed as a hierarchy by itself. In fact, the UDM cube space is a product of attribute-based hierarchies, while multilevel hierarchies are optional.

For example, consider a Product dimension table that includes product attributes that are not part of or are not naturally related to the Product Category dimension, such as product name, list price, and color. UDM makes it possible to report off these attributes (see Figure 1.12).

As Figure 1.12 shows, UDM doesn’t force you to use the Product Category dimension when browsing sales data by product. Instead, just like with relational reporting, you can drop the product related attributes side-by-side. To achieve the same effect in SSAS 2000, the cube designer had to create multiple dimensions which led to duplication of definitions and storage.

Flexible schema
One of the trademarks of the relational reporting model is that it enjoys a flexible database schema. Indeed, complex relationships, e.g. one-to-many, many-to-many, outer joins, etc, have been a part of the core relational schema model from its beginning. Similarly, with UDM, you are not confined to star and snowflake schemas anymore. In addition, UDM introduces new dimension roles (referenced, many-to-many, role playing, degenerate, etc) that enable new scenarios. Let’s mention one scenario that UDM makes possible.

With the "classic" dimensional model it has been traditionally difficult to join two fact tables that summarize data at different levels (called grain in the OLAP terminology). For example, the grain of the time dimension in your cube may be days. At the same time, you may also have a sales quota fact table which stores the sales person quotas at a quarter level. Suppose that your requirements call for joining both tables to compare the sales person’s performance and her quota side-by-side.

There were a few techniques in SSAS 2000 to address the multi-grain issue, including parentchild dimensions, inserting "fake" members, using virtual cubes, but none of them presented a clean solution. In contrast, UDM addresses this issue gracefully by simply allowing you to define the grain at which a given dimension joins both fact tables, as shown in Figure 1.13. In this case, I have specified that the time dimension will join the fact table at Calendar Quarter level. It can’t be easier, really!

Low latency
The business requirements of today’s economics mandate ever-shrinking time windows to make data available for reporting and analysis. UDM makes real-time OLAP and building low latency OLAP applications a possibility. There are two techniques to accomplish this. The first technique involves pushing the data directly into the cube (push-mode processing) without updating the underlying data source. For example, you may have a sales cube built on top of a data warehouse database. The processes of extracting, transforming, and loading the order data into the data warehouse may take significant time to execute. Yet, business requirements may dictate new order data to be available for reporting within a few hours. With SSAS 2005, you can build a light-weight data integration package that runs frequently and trickle-feeds the new orders into a cube completely bypassing the data warehouse tables. Unresolved dimensions can be defaulted to unknown values until the "full-blown" data integration package executes.

The second technique is more suitable for cubes built directly on top of OLTP databases. It allows you to put the cube in "auto-pilot" mode by leveraging the new proactive caching feature. When proactive caching is enabled on an SSAS 2005 cube, the cube can detect changes to the underlying data and automatically update its dimensional structures. I will show you how you can implement real-time OLAP in chapter 15.

Simplified management
There are several provisions in SSAS 2005 to simplify the management effort. SSAS 2005 removes the limitation that a cube can have only one fact table. What this means to you is that you can store the entire dimensional model into a single "super-cube". If you worry about what impact this will have on performance and scalability, rest assured that your UDM model scales well. SSAS 2005 gives you the means to scale out large deployments, e.g. by partitioning the cube across multiple servers and load-balancing these servers in a cluster.

To minimize the management and design effort, BI Studio provides a slew of wizards and designers. For example, The Cube Wizard can help you "jumpstart" the cube dimensional model by heuristically examining the relational schema and suggesting measures. As its name suggests, the New Dimension wizard walks you through the process of adding a new dimension. Finally, a brand new .NET-based object model called Analysis Management Objects (AMO) has been introduced to supersede the SSAS 2000 Decision Support Objects (DSO) model and allow developers to implement SSAS management features in their .NET applications.

Detail reporting
You shouldn’t view relational and OLAP reporting as competing but, rather, complementary technologies that address different user needs. Therefore, SSAS 2005 will not be my tool of choice for generating OLTP-based relational reports, such as a common order report (order header with line items). Instead, use SQL Server Reporting Services for your standard reporting needs.

Yet, UDM provides several ways to view detail data. First, you can use a feature called drilltrough to see the underlying rows under a given dimension member or a cube cell. For example, the cube may aggregate sales order data on a daily basis. Yet, users may want to view the individual orders placed on a given day. You can implement this requirement by enabling drillthrough for that cube. This, of course, assumes that the OLAP browser supports this feature.

Second, you can use UDM actions. Considering the above example, if drilldown is not a good fit or is not supported by the OLAP browser, you can implement an action to launch a standard report (e.g. a Reporting Services tabular report) to display the invoices. Once the action is configured, the end-user could right-click on the cube cell in question to launch the report from the dropdown menu.

1.3.2 Dimensional Model Features

While SSAS 2005 comes with exciting new features, its stays close to its roots. The core dimensional concepts are the same. Now let’s discuss briefly how UDM leverages and enhances the dimensional model.

Intuitive reporting
Intuitive end-user oriented reporting has been the hallmark of the dimensional model since its inception. As I mentioned, UDM makes the user experience even richer by enabling reporting scenarios that are not part of the core dimensional model, such as actions, drillthrough, and attribute-based reporting.

High Performance
OLAP user experience is directly correlated to the query performance. As noted, there are two factors that contribute most to the SSAS efficiency — the optimized query engine and the cube multidimensional model. SSAS 2005 brings additional performance enhancements. Most of them are related to the fact that SSAS 2005 cubes are not limited to having one fact table anymore. What this means to you is that you don’t have to use virtual cubes anymore. Another cause of grievance in the past was that SSAS 2000 required all dimensions to be loaded in memory. To address this issue, SSAS 2005 loads dimensions in memory on as-needed basis.

Rich analytics
There are many new features in SSAS 2005 that bring rich business intelligence features to end users. Some of them were available in the previous releases as well, but they were not straightforward to implement. To facilitate defining advanced analytics features in SSAS 2005, the Analysis Services team introduces a brand new Business Intelligence Wizard (see Figure 1.14). For example, one of the common reporting requirements is to compare data over parallel time periods, e.g. sales figures between the first quarters of two consecutive years. As Figure 1.14 shows, the Define time intelligence feature of the Business Intelligence Wizard can help you save time by generating such time-related metrics for you. We will see how to add advanced business intelligence features to UDM in chapter 10.

Powerful calculations
The calculation engine has been completely redesigned in SSAS 2005. You still have to know MDX to define your calculations, but authoring and testing MDX logic is much easier now. Issues that have pestered MDX developers in the past (solve order, pass) have simply disappeared. One welcome enhancement is that all MDX constructs (calculated members, cell calculations, named sets) are centralized in one place (the Calculations tab of the Cube Designer), as shown in Figure 1.15.

In this case, I use the Calculations Script View pane to define several calculated members, named sets and MDX scripts. .NET developers will undoubtedly find many similarities between the Script View and Visual Studio.NET. Similar to working with the Visual Studio.NET IDE, MDX developers can use breakpoints, debug MDX scripts, and see the effect of the executed script. For example, Figure 1.15 shows that I’ve just stepped out of a breakpoint and the Pivot Table has highlighted the effected cell. The SSAS calculation engine is also extensible. Developers can plug in additional programming logic in the form of SSAS stored procedures that can be written in any .NET-compatible language. We will cover MDX programming in part 3 of this book.

Data semantics
Unlike the relational model, which is oblivious to the meaning of data, UDM can be "educated" to understand the data semantics. For example, UDM understands Time dimension and chart of accounts. In the later case, UDM knows how to map the account data to pre-defined categories, such as income, expenses, and taxes, and apply the required calculations. We will see how to implement advanced intelligence features in chapter 5.

1.3.3 UDM Components

At this point, you are probably curious about how UDM is implemented. The physical manifestation of UDM is the new Analysis Services 2005 cube. Therefore, I will use the terms UDM and cubeinterchangeably throughout the rest of the book. As Shrek would undoubtedly attest, just like ogres, UDM has layers. Figure 1.16 shows how you can visualize UDM. As you could notice, I’ve stacked the UDM components in the chronological order they will be typically implemented. To reflect this natural flow, the book is organized in the same way and the numbers shown inside the individual layers represent the chapter(s) where the corresponding layer is discussed.

Data source view (DSV)
UDM is based on a logical data schema that seeks to present the data from the relational data store in a standard and intuitive way. UDM implements the schema in the form of a data source view (DSV). Besides providing the UDM data schema, DSV isolates the cube dimensional model from changes in the underlying relational databases.

Dimensional model
Once DSV is created, the next step will be implementing the cube dimensional model. The end result of this process is the cube definition consisting of measures and dimensions with attribute and/or multilevel hierarchies.

Calculations
Only in rare cases, the dimensional model alone will fully meet your needs. As a UDM designer, you can augment your cube with specific business logic in the form of MDX expressions.

End-user model
As noted, the main design goal of the dimensional model is to provide intuitive end-user reporting and data navigation experience. By "end-user model", we will understand the additional features you can build on top of the dimensional layer to provide even richer data semantics. These features include Key Performance Indicators (KPIs), actions, perspectives, and translations. For example, if the cube will be browsed by international users, dimension levels could be localized by using translations. Or, you can use perspectives to define named subsets of large and complex cubes for easier navigation.

Management settings
At last, the cube is ready for prime time. As a last step, a savvy administrator would configure the cube to meet various operational requirements, including availability, latency, and security. For example, in this stage the cube administrator will configure which users will be able to access the cube, when and how the cube data will be updated, the cube storage model, etc.

1.3.4 To UDM and Beyond

By now, you should be able to understand the UDM goal to unite the best of both worlds (relational and dimensional) and become a bridge between the users and data. One could envision UDM to evolve in time to a point where the other relational and dimensional models are simply not needed and will disappear, as shown in Figure 1.17. When this happens, UDM will be able to serve both relational and dimensional reporting needs. Besides simplicity, having a single model will bring also conformity. Business logic and calculations could be defined in one place. As an added bonus, all reporting clients will be able to benefit from the performance boost they will get from SSAS.

Note: To some extent, SQL Server 2005 and the Microsoft BI platform give you the tools to materialize the "unified" vision of having UDM as a focal point for data analytics. For example, as I will demonstrate in chapters 18 and 19, Reporting Services and the Microsoft Office analytics tools integrate well with UDM. However, as noted before, you shouldn’t expect UDM to address all reporting requirements equally well. That’s why I don’t suggest you quickly throw away your reporting tools in favor of UDM. In my opinion, medium to large-size organizations will benefit most from leveraging UDM as a central repository.

1.4 ANALYSIS SERVICES ARCHITECTURE

Readers who have prior SSAS experience have probably heard the popular saying that all roads to SSAS 2000 go through the PivotTable Service (PTS). PTS was the primary method for interacting with Analysis Services 2000 to perform tasks such as connecting to a cube and retrieving data. It was designed as a client-side component and, as such, it had to be installed on the machine where the client application was installed. PTS helped query performance by providing client-side caching. In many cases, however, PTS was simply getting in the way. For example, PTS wasn’t designed to work with server-based applications. The good news is that the SSAS 2005 architecture is entirely server-based, as shown in Figure 1.18. This enables flexible client integration scenarios, e.g. implementing thin clients that require no installation footprint.

Let’s discuss the SSAS building blocks starting with the Analysis Services server.

1.4.1 Analysis Services Server

At the heart of the SSAS architecture is the Analysis Services server. The SSAS server provides the following main services:

Storage — The Storage Engine is responsible for storing and processing SSAS objects. It also keeps the UDM object definition (called metadata).
Calculations — The Formula Engine handles MDX queries and expressions.
Data mining — The Data Mining Engine processes mining queries and returns prediction results.

As with the previous releases, the SSAS server is implemented as a Windows service called MSMDSRV.EXE written in C++ native code. By default, the setup program installs the Analysis Services Server in C:\Program Files\Microsoft SQL Server\MSSQL.2. You can install more than one instance of SSAS 2005 on a single machine and different versions (e.g. 2000 and 2005) can co-exist side-by-side. Perhaps the most interesting SSAS architectural change is that it embraces XML for Analysis (XMLA) as a native protocol. In fact, you cannot communicate with SSAS in any other way than using XMLA. Given the strategic importance of XMLA, let’s spend some time introducing this protocol.

1.4.2 XML for Analysis (XMLA) Specification

As its name suggests, the XMLA protocol conforms to XML-based grammar called XMLA specification. The purpose of this specification is to standardize the data access between OLAP clients and analytical data providers, such as SSAS. Since its debut in mid-2001, XMLA gained support with more than twenty vendors, including the three founding members — Microsoft, Hyperion, and SAS (see the Resource section). The XMLA specification is managed by the XMLA council (xmla.org). As of the time of this writing, the most current version of the XMLA specification is 1.1. This is the version that is implemented in SSAS 2005. XMLA embraces the SOAP protocol for sending and receiving XMLA messages to a XMLAcapable provider. The actual SOAP grammar is very simple (Figure 1.19). It describes just two methods, Discover and Execute, which every XMLA provider must support.

Discover
An OLAP client calls the Discover method to obtain the metadata that describes OLAP and data mining objects. For example, an OLAP client can ask the SSAS 2005 server to return a list of all cubes defined in an Analysis Services database by invoking the Discover method.

Execute
Execute is an action-oriented method. A client can invoke the Execute method to send either ASSL commands or statements.

Analysis Services Scripting Language (ASSL)
Analysis Services Scripting Language (ASSL) is an XML-based grammar that describes the UDM metadata (DDL grammar) and commands.

Data Definition Language (DDL) grammar — DDL is the internal representation of metadata in Analysis Services 2005. DDL describes the object definition, e.g. a cube definition. You can see the DDL grammar by right-clicking on the object in the BI Studio Solution Explorer and choosing View Code.
Command language grammar — A subset of ASSL defines some action-oriented commands that could be sent to the server, e.g. for processing, altering, or creating objects. For example, each time you process a cube, BI Studio generates an ASSL script that includes a Process ASSL command.

The Execute method can (and most often) is used to send also statements.

Statements
With OLAP, the Execute statements describe MDX queries, while with data mining, they contain DMX queries. The query results are returned as a rowset (for SQL and data mining queries), or in the form of a more complex structure called MDDataSet in the case of OLAP (MDX) queries.

What may be confusing is that both MDX and DMX also define DDL statements. For example, MDX defines a CREATE MEMBER construct to create a new calculated member, while DMX supports a CREATE MINING MODEL to create a data mining model. It is important to understand that these DDL statements have nothing to do with the ASSL DDL grammar although they have ASSL equivalents. In addition, MDX and DMX DDL statements are less flexible than DDL.

XMLA Connectivity Options
SSAS 2005 gives you two connectivity options to send XMLA messages to an Analysis Services server. By default, the client communicates with the server via TCP/IP. However, SSAS can be configured also for HTTP connectivity to enable web-based integration scenarios.

XMLA over TCP/IP
The XMLA over TCP/IP connectivity option is more suitable for intranet deployments. With this option, the SOAP messages are serialized in binary format and sent over TCP/IP to the SSAS server. You don’t need to take any extra steps to configure SSAS to use XMLA over TCP/IP. For example, if you use Office Web Components and set its connection string to use the OLE DB Provider for Analysis Services 9.0, the provider will communicate with SSAS over TCP/IP.

Compared to HTTP connectivity, the XMLA over TCP/IP connectivity option has slightly better performance since no additional layers are introduced between the client and the SSAS server. The tradeoff is that the client has to be able to connect directly to the port the SSAS server is listening to (2383, by default) which may conflict with firewall policies.

XMLA over HTTP
In this case, IIS is used as an intermediary to receive the HTTP requests. To set up SSAS 2005 for HTTP connectivity, you need to set up an IIS virtual root that will host the SSAS XMLA provider (a.k.a. Pump). The purpose of the Pump component is to accept the incoming HTTP requests from IIS and forward them to the SSAS server over TCP/IP. Once the HTTP connectivity is set up, change the connection string to point to the IIS virtual root, e.g. http://<ServerName>/<VRoot>/msmdpump.dll.

Consider the XMLA over HTTP option when you need to connect to SSAS over the Internet or when direct connectivity to SSAS is not an option. For example, security requirements may enforce access to SSAS only over port 80 (HTTP) or 443 (SSL). HTTP connectivity could be a good choice when you cannot install programming libraries, e.g. when you need to implement thin or non-Windows clients, e.g. a Java-based OLAP client running on UNIX box. The XMLA over HTTP connectivity option is described in more details in chapter 16.

1.4.3 SSAS Clients

OLAP clients have several available programming interfaces to connect to SSAS 2005. No matter which connectivity option is chosen, the interface library translates the calls to XMLA. Code samples demonstrating different integration options are provided in chapter 17.

Thin clients
Thanks to its entirely server-based architecture and support of industry-standard protocols (HTTP, XMLA, and SOAP), SSAS 2005 can be integrated with any SOAP-capable client running on any platform with no installation footprint. In this case, the client is responsible for constructing SOAP requests conforming to the XMLA specification and interpreting XMLA responses.

Win32 native clients
C++ clients would typically connect to SSAS 2005 using the OLE DB for Analysis Services. This is how OWC connects to SSAS 2005. The provider you need is OLE DB Provider for Analysis Services 9.0 (Provider=MSOLAP;3 in the connection string). You cannot use an older provider, e.g. version 8.0, because only version 9.0 knows how to translate the OLE DB for Analysis protocol to XMLA. COM-based clients, such as Visual Basic 6.0 clients, can connect to SSAS 2005 by using the ADO MultiDimensional library (ADOMD) which is implemented as a COM wrapper on top of the OLE DB provider.

.NET clients
.NET clients can connect to SSAS 2005 using the ADO MultiDimensional for .NET library (ADOMD.NET). ADOMD.NET doesn’t require the OLE DB Provider for Analysis Services 9.0 to be installed on the client machine. It is implemented as a light-weight managed wrapper on top of XMLA. Interestingly, SSAS provides also a server-side object model in the form of the ADOMD Server library (ADOMD.NET Server) residing inside the Analysis Services server. The main difference between ADOMD Server and ADOMD.NET is that the former doesn’t require the developer to set up a connection with the server explicitly before sending queries or navigating the server objects. Other than that, both libraries provide almost identical set of objects.

For management tasks, .NET developers would use the brand new Analysis Management Objects (AMO) library. With the AMO library, you have access to the full Analysis Services object hierarchy, including servers, databases, data source views, cubes, dimensions, mining models, and roles. Developers would typically use the AMO library to automate routine management tasks, such as database synchronization and processing. AMO supersedes the Decision Support Objects (DSO), the object model of SSAS 2000. DSO is still available for backward compatibly in the form of the DSO9 object library. However, as the documentation states, DSO will be removed in the next version of Microsoft SQL Server and you are strongly encouraged to migrate your management applications to AMO.

1.5 ANALYSIS SERVICES IN ACTION

Let’s demonstrate some of the concepts that we’ve discussed so far in a short hands-on lab. Before we start, let’s introduce an imaginary company, called Adventure Works Cycles. Adventure Works Cycles (or AWC, for short) manufactures and sells bicycles to resellers and individuals in North America, Europe, and Australia. In 2001, its first year of operation, AWC sales accounted for more than ten million dollars. Since then, the AWC business has been growing exponentially to reach the record high of forty million dollars in total sales in 2003. However, the AWC business took a downturn in 2004 and sales fell below the projected figures. Direct sales to customers remain constant, while resales fell almost fifty percent.

1.5.1 Introducing Adventure Works Sales OLAP System (SOS OLAP)

The AWC management has decided to implement a BI reporting solution to get more insight into the company performance and its customers. And, as you probably guessed it, AWC has hired you as an architect to lead the design and implementation of the strategic Adventure Works Sales OLAP System, or SOS, as the AWC information workers affectionately refer to it to emphasize its much awaited arrival. It is worth mentioning that, as useful as our fictitious system could be, it is not meant to serve as a complete solution for sales analytics. Instead, you should view it as a sample whose main objective is to help you learn SSAS 2005.

The current system
After a series of acquisitions, Adventure Works Cycles operates several software systems spanning different technologies. The employee data is stored in an Oracle-based HR system, while the manufacturing data is captured in an IBM mainframe system. The sales ordering data is stored in a SQL Server database 2005 called AdventureWorks.

Note: SQL Server 2005 comes with two sample databases. AdventureWorks simulates an OLTP sales order database, while AdventureWorksDW imitates a data warehouse database that sources its data from the AdventureWorks database. You can find the AdventureWorks OLTP Visio schema in the Database\ AWC folder of the book source code. As you can see by browsing its seventy tables, the AdventureWorks database is inherently more complex than FoodMart or other SQL Server sample databases that you may have encountered in the past.

The sales representatives use a Windows Form intranet application to capture orders placed through the resale channel. Web customers purchase AWC products online through the AWC Intranet website. In both cases, the sales orders are stored in the AdventureWorksOLTP database. A sales order is assigned different status codes as it goes though the order management pipeline, e.g. In Process, Approved, Shipped, or Cancelled. AWC has a cutoff period of one month for the order to be considered finalized (Shipped or Cancelled).

AWC has already built a data warehouse to archive the sales history. Data from relevant systems is extracted, transformed, and loaded in the data warehouse. Shipped sales orders that are older than a month are extracted from the AdventureWorks OLTP system and offloaded to the warehouse. The role of the data warehouse is fulfilled by the AdventureWorksDW sample database, which can be installed by running the SQL Server setup program.

Reporting challenges
Currently, enterprise reporting needs are addressed by running standard reports directly against the warehouse database. This reporting model is characterized by several of the standard reporting deficiencies we enumerated in 1.2.1, including:

Inadequate reporting experience — Business analysts complain that they cannot slice and dice data from different perspectives easily. Different reporting tools are used based on the user skill set, ranging from Excel spreadsheets to high-level reporting tools, such as Reporting Services.
Performance issues — Reports that aggregate large volumes of data take a long time to execute.
Insufficient data analytics — Complex business logic and calculations cannot be easily implemented on top of the data warehouse relational schema. Subsequently, they are often redefined from one report to another and stored as part of the report, instead of in a central repository. In addition, the current reporting model doesn’t support pattern discovery and forecasting.

To address the current report deficiencies, you’ve decided to use SSAS 2005 as an OLAP engine that will power the new SOS system.

The solution
You envision the SOS system to provide three major functional areas — a historical layer, a realtime UDM layer, and a reporting layer.

Historical UDM
The main purpose of the SOS system is to provide fast and uniform access to the data stored in the data warehouse. This objective will be achieved by building a UDM layer in the form of an Analysis Services 2005 cube on top of the warehouse database. The historical UDM layer will serve most of the OLAP requirements and all data mining requirements.

Real-time UDM
To address real-time BI needs for reporting off volatile order data that hasn’t been offloaded to the data warehouse, a real-time (hot) OLAP layer will be built directly on top of the Adventure- Works OLTP system. The real-time UDM layer will be implemented as a second SSAS 2005 cube that will provide a subset of the data analytics feature set of the historical UDM.

Note: We will keep the real-time UDM light-weight on purpose. From a learning perspective, there is no point duplicating the same feature set in both the historical and real-time UDM models. Instead, when implementing the real-time UDM, our focus will be demonstrating UDM features that are particularly relevant to low-latency OLAP solutions, such as data source views and proactive caching. In real life, of course, you can have a more sophisticated and feature-rich real-time layer if required.

The term real-time here means that the cube will pick up changes in the transactional data almost instantaneously, instead of requiring explicit processing.

Reporting layer
Since the AWC business analysts have different reporting needs, you envision leveraging several BI reporting tools for presenting data to the end users, including custom applications, Reporting Services, and Microsoft Office.

1.5.2 Your First OLAP Report

Suppose the Adventure Works business analysts would like to be able to generate interactive sales reports to slice and dice data from different angles. Let’s see how we can address this requirement by using two reporting technologies: standard reporting and OLAP reporting.

Standard reporting

In the absence of an OLAP reporting solution, the most common option is to author standard or ad-hoc reports that submit SELECT SQL statements directly to the OLTP database. These SELECT queries are typically multi-join statements that link several relational tables together to fetch the report data. An example of a SQL SELECT statement that will produce a standard report similar to the interactive report shown in Figure 1.1 is included in SQLQuery.sql file.

There may be several potential issues with generating reports sourced directly from an OLTP database. To start with, the report query may impact the performance of the OLTP database. The query may take long a time to execute. On my machine (HP NW8000, 1.8 GHz Pentium M single CPU, 2 GB RAM) the query in the SQLQuery.sql file takes about three seconds to execute. Not that bad, you may say. Of course, we need to factor in the amount of data processed. In our case, the SalesOrderDetail table in the sample AdventureWorks database has 121,317 order line items. Now, imagine that the same query is fired against a much bigger transactional or warehouse database. Assuming linear regression of performance, the same query will take about 30 seconds to complete if we have ten times more records. I doubt that your users will be willing to wait for that long!

If you would like to empower your end users to generate their own reports in ad-hoc fashion, they have to know quite a bit about the relational (ER) model and SQL. They have to know which tables to join and they have to know how to join them. True, ad-hoc reporting tools may abstract to a certain extent the technicalities of the relational model but they have issues of their own. Finally, standard reports are not interactive. The user cannot drill down data, e.g. double-click on a given year column to see data broken down by quarters.

Deploying the Unified Dimensional Model
Now, let’s see how OLAP and UDM change the reporting experience. We will use Excel reporting capabilities to build a simple reporting solution with SSAS 2005 that will resemble the report shown in Figure 1.1. The report will source its data from an SSAS 2005 cube. To build the cube, we will use the AdventureWorks Analysis Services Project sample that comes with the SQL Server 2005 samples. It includes a sample cube called Adventure Works. The Adventure Works cube draws data from the Adventure Works warehouse database (AdventureWorksDW).

If you have installed the SQL Server 2005 samples (see Appendix A), the project will be located in C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project folder.

Note: Both Standard and Enterprise versions of the project will do fine for our demo. Choose one based on the SSAS version you are running.

Opening an Analysis Services project in BI Studio
If you haven’t deployed the sample AdventureWorks Analysis Services Project sample, follow these steps:

Start SQL Server Business Intelligence Development Studio (found in the Microsoft SQL Server 2005 program group). Readers familiar with Visual Studio.NET will undoubtedly notice that the Business Intelligence Development Studio IDE looks similar. As I’ve mentioned in section 1.1.4, you will use BI Studio as a primary tool to design and maintain UDM.

From the File menu, choose Open, then Project/Solution… and Open the Adventure Works solution (Adventure Works.sln). This solution includes a single project (Adventure Works DW.dwproj). Don’t worry if the concepts of SSAS database and projects are not immediately obvious. It will all become clear in Chapter 2. For the time being, note that the AdventureWorks DW project includes the definitions of all objects in the Adventure Works UDM.

If the Solution Explorer window is not shown, click on the Solution Explorer (View menu) or press Ctrl-Alt-L. The Solution Explorer shows the SSAS objects defined in the Adventure Works DW project in a tree view, as shown in Figure 1.21.

Double-click on the Adventure Works cube to open the Cube Designer. The Cube Designer uses the same colors (blue for dimension tables and yellow for fact tables) as Analysis Manager 2000. Note that the dimension and fact tables are linked to each other, just like relational tables are joined via referential integrity constraints. However, the big difference is that UDM enforces these relationships at a metadata level. As a result, the end user doesn’t have to explicitly join UDM objects. Instead, producing an OLAP report is as easy as dragging and dropping UDM objects using your favorite OLAP client, which could be Microsoft Excel, as we will demonstrate shortly.

In the Solution Explorer, expand the Cube node, right-click on the Adventure Works cube and choose View Code. BI studio shows the definition of the cube described in Analysis Services Scripting Language (ASSL). When you work in project mode (default), changes are persisted locally.

Deploying projects

To propagate changes made in project mode, you need to deploy the project. Back to the Solution Explorer, right-click on the Adventure Works DW project node (not to be confused with the topmost solution node) and choose Properties. Expand the Configuration Properties and click the Deployment node (Figure 1.22).

Verify the server name (enter localhost to deploy to the local server).

Close the Property Pages window. If you haven’t deployed the Adventure Works DW project yet, right-click on the project node and choose Deploy. BI Studio builds and deploys the project. The end result of this process will be the creation of a new SSAS database called Adventure Works DW.

To verify that the deployment process has completed successfully, open SQL Server Management Studio (Microsoft SQL Server 2005 Program group).

In the Object Explorer pane, choose Connect --> Analysis Services to connect to the SSAS server that you deployed the project to.

Expand the Databases folder and check that there is a database named Adventure Works DW.

Expand the Adventure Works DW folder and take some time to familiarize yourself with the database content. For example, expand the Adventure Works cube, then the Measure Groups measures and notice that there are eleven measure groups (with the Enterprise version).<

Building OLAP Report
At this point, the SSAS database is built and its only cube has been processed. Let’s now use Microsoft Excel as a reporting tool to browse the cube data. We will generate a report that shows sales data broken by product and time, as shown in Figure 1.23.

Start Microsoft Excel 2003

Create a new PivotTable report by selecting PivotTable and PivotChart Report from the Data menu.

In Step 1 of the PivotTable and PivotChart Report Wizard, select the "External data source" option since you will be retrieving the data from an SSAS server. Click Next.

In Step 2, click on the Get Data button to configure Microsoft Query. In the Choose Data Source dialog, click on the OLAP Cubes tab. Make sure the New Data Source item is selected. Click OK.

In the Create New Data Source dialog, name the data source Adventure Works. In the "Select an OLAP provider for the database you want to access" dropdown select Microsoft OLE DB Provider for Analysis Services 9.0 (see Figure 1.24). Recall that Win32 clients must use version 9.0 of the OLE DB Provider for Analysis Services to connect to SSAS 2005.

Click the Connect button. On the MultiDimensional Connection dialog, select the Analysis Server radio button, enter the machine name where SSAS 2005 is installed (Server field). Leave the credentials fields blank to use Windows Authentication. Click Next. The Database listbox appear. Select the Adventure Works DW database and click Finish. You are now taken back to the Create New Data Source dialog.

Expand the last dropdown (Figure 1.24) and select the Adventure Works cube. Click OK to close the Create New Data Source dialog and OK to close the Choose Data Source dialog. You are back to the PivotTable and PivotChart Wizard. Click Next to advance to Step 3.

Accept the defaults in Step 3 and click Finish.

A blank pivot report appears in the Excel spreadsheet. A PivotTable Field List pane contains all measures and dimensions defined in the Adventure Works cube.

Note: At this point, you are probably confused by the sheer number of items shown in the PivotTable Field List pane. Most of the items are attribute-based dimensions which are derived directly from columns in the underlying dimension tables. For example, the Color dimension corresponds to the Color column in the DimProduct dimension table. Unfortunately, Excel 2003 was released before SSAS 2005 and it is unaware of the new features. Subsequently, the Field List is not capable of organizing the attribute hierarchies in folders, as the Cube Browser does.

Scroll down the PivotTable Field List pane until you locate the Date.Calendar hierarchy. This dimension represents a natural time hierarchy with Year, Semester, Quarter, Month and Date levels. Drag the Date.Calendar hierarchy to the Drop Column Fields Here area of the pivot report.

Scroll further down the PivotTable Field List pane until you locate the Product Categories dimension. This dimension represents a natural product hierarchy with Category, Subcategory, and Product Name levels. Drag the Product Categories hierarchy to the Drop Row Fields Here area of the pivot report.

Let’s now add some measures to the report. Scroll the PivotTable Field List pane all the way down until you locate the Sales Amount measure. In the pane, measures have a different icon (0110) than dimensions. Drag the Sales Amount measure to the Drop Data Items Here report area. Do the same with the Order Count measure. Although the PivotTable Field lists doesn’t have a special icon for MDX expressions, note that there are many calculated measures we can use in the report, such as Reseller Ratio to All Products, Internet Gross Profit Margin, etc.

If you wish, you can spend some time to pretty up the report by changing format, font, and color settings. At the end, your report may look like the one shown in Figure 1.23.

We are done! Feel free to experiment with the Excel PivotTable report. For example, doubleclick on any member of the Product Categories dimension to drill down sales data to the product subcategory and product name levels. Drag and drop other dimensions and measures. Once the cube is designed and deployed, there are many ways to build interactive reports that provide the needed level of business intelligence.

1.6 SUMMARY

This chapter has been a whirlwind tour of the SSAS 2005 and OLAP technology. By now, you should view SSAS 2005 as a sophisticated server-based platform that provides OLAP and data mining services. Empowered with SSAS 2005, you can build intuitive and efficient BI applications. We’ve seen how SSAS 2005 fits into the Microsoft BI initiative. We’ve emphasized the ambitious goal of SSAS 2005 to converge the relational and dimensional models into a single Unified Dimensional Model.

We’ve also looked at the high-level of the SSAS 2005 architecture and emphasized the fact that XMLA is the native protocol of SSAS 2005. To help readers who have prior SSAS experience, I’ve provided a side-by-side comparison map between versions 2000 and 2005. Finally, we’ve put into practice what we’ve learned by building an interactive Microsoft Excel PivotTable report which sourced its data from SSAS 2005. Having laid the SSAS foundation, we are ready to "drill down" the UDM layers. Let’s start by finding out how we can work with data.

1.7 RESOURCES

Microsoft SSAS home page
(http://shrinkster.com/895) — First stop for the latest on SSAS.

The OLAP Report website
(http://www.olapreport.com/) — The OLAP Report is an independent research resource for organizations buying and implementing OLAP applications.

SQL Server 2005 Features Comparison
(http://shrinkster.com/62q) — Compares side by side the editions of the SQL Server 2005 products.

SSAS and the competition
(http://www.olapreport.com/market.html) — Market share analysis of the top OLAP vendors.

Microsoft Reporting Services in Action Book
(http://www.manning.com/lachev) — Following the report lifecycle, my book teaches you the necessary skills to create, manage, and deliver SSRS reports.

XML for Analysis home page
(http://www.xmla.org/) — Visit to access the latest XML/A specification, FAQ, discussion forum, and samples.

Page: 1, 2

ADS BY GOOGLE