Chapter 6: Cube Design

In Chapter 5 you learned to create dimensions using the Dimension Wizard and to refine and enhance dimensions using the Dimension Designer. Dimensions eventually need to be part of your UDM for you to analyze data across various dimension members. In previous chapters, you read about the Unified Dimensional Model (UDM). Now, prepare yourself for significantly more detail because all the fact and dimension tables you see when you're looking at a DSV in the Cube Designer comprise the UDM. Yes, the UDM is more than a multiple data-source cube on steroids, but to make it as clear as possible, think of the UDM as a cube for now. In this chapter you learn how to create cubes using the Cube Wizard and enhance the cube using the Cube Designer. You learn to add calculations to your cube that facilitate in effective data analysis followed by analyzing the cube data itself in the Cube Designer.

The Unified Dimensional Model

To generate profits for a business, key strategic decisions need to be made based on likely factors such as having the right business model, targeting the right consumer group, pricing the product correctly, and marketing through optimal channels. To make the right decisions and achieve targeted growth you need to analyze data. The data can be past sales, expected sales, or even information from competitors. The phrase "Knowledge is power" is very fitting here because in the world of business, analyzing and comparing current sales against the expected sales helps executives make decisions directly aligned with the goals of the company. Such sales information is typically stored in a distributed fashion and must be collected from various sources. Executives making the business decisions typically do not have the capability to access the raw sales data as formed from various locations and subsequently optimized for use. The decision-makers typically rely on the aggregated data, which is easy to understand and which facilitates the decision-making process. Presenting aggregated data to the decision-makers quickly is a key challenge for business intelligence providers. Analysis Services 2005 enables you to design a model that bridges the gap between the raw data and the information content that can be used for forming business decisions. This model, designed through Analysis Services 2005, is called the Unified Dimensional Model (UDM).

The UDM is central to your Analysis Services database architecture. UDM is your friend because it helps you narrow the gap between end users and the data. Analysis Services provides you with several features that help you design a unified model that will serve the needs of end users. UDM, as the name suggests, provides you with a way to encapsulate access to multiple heterogeneous data sources into a single model. The UDM buffers you from the difficulties of managing the integration of various data sources so you can build your model easily. The UDM provides you with the best of OLAP and relational worlds, exposing rich data and metadata for exploration and analysis.

Figure 6-1 (originally shown in Chapter 2, but reprinted here for your convenience) shows you the architecture of the Unified Dimensional Model that has been created using Analysis Services 2005. As shown in the figure, the UDM helps you to integrate data from various data sources such as Oracle, SQL Server, DB2, Teradata, and flat files all into a single model that merges the underlying schemas into a single schema. The end users do not necessarily have to view the entire schema of the UDM. Instead, they can view sections of the UDM relevant to their needs through the functionality provided by Analysis Services 2005 called perspectives.

Figure 6-1

In the OLAP world, data analyzed by end users is often historical data that might be a few days, months or even years old. However, the responses to the OLAP queries are typically returned within a few seconds. In the relational world the end users have instant access to the raw data but the responses to queries can take much longer, on the order of minutes. As mentioned earlier, the UDM merges the best of both the OLAP and relational worlds and provides the end users with real-time data with the query performance of the OLAP world. The UDM is able to provide the query performance of the OLAP world with the help of a feature in Analysis Services 2005 that creates a cache of the relational data source that also aggregates the data on an instance of Analysis Services. During the time the cache is being built, the UDM retrieves the data directly from the data sources. As soon as the cache is available, the results are retrieved from the cache in response to relevant queries. Whenever there is a change in the underlying data source, the UDM receives a notification and appropriate updates are made to the cache based on the settings defined for cache updates.

The UDM also provides rich, high-end analytic support through which complex business calculations can be exploited. Such complex calculations can be extremely difficult to formulate in the relational world at the data-source level. Even if such calculations are defined on the relational data source; query responses to OLAP style queries from relational data source might be really slow as compared to the responses from Analysis Services. UDM natively interfaces to end-user clients through the XML for Analysis standard which allows client tools to use XMCA to retrieve data from Analysis service. Client tools such as Office Web Components (OWC) and Excel pivot tables allow the end users to create ad-hoc queries for data analysis. In addition to that, UDM supports rich analytic features such as Key Performance Indicators (KPIs), Actions and Translations that help surface the status of your business at any given time so that appropriate actions can be taken.

The UDM provides an efficient interface for detail-level reporting through the dimension attributes that are common in the relational world. In addition to that the UDM is easily understandable by a relational user. The ability to transform the UDM catered towards end user's views and the ability to perform ad-hoc queries on aggregated data to detail-level data make the UDM a powerful construct indeed. The UDM also allows you to design the model in the end user's language, which is needed in a global market.