Chapter 22 - Cubes in the Real World

Microsoft® SQL Server™ 2000 Analysis Services offers myriad options for creating cubes, and the flexibility and power to support almost any business case. This chapter presents practical guidelines for designing cubes and dimensions, design fundamentals, and other considerations.

Design Fundamentals

When using any online analytical processing (OLAP) product, including Analysis Services, several common fundamental design considerations arise. Typically, OLAP products are used to present aggregation data efficiently, while lessening the load that large queries and on-the-fly aggregation put on data warehouse and online transaction processing (OLTP) data sources. Calculating and storing aggregation data in a separate area for later retrieval by the OLAP product allows users to retrieve a large amount of information while it reduces the amount of data to be processed. This functionality, while it relieves data warehouses of the potentially large workload of aggregating and responding to such queries by users, presents its own special set of issues.

Two of the most common issues, data explosion and sparsity, are caused by OLAP products' need to establish and access aggregation storage. Although Analysis Services efficiently handles both issues, understanding them can improve your cube design and performance.

Data Explosion

The term data explosion applies to the tendency of cube structures to grow exponentially due to excessive aggregation. In other words, data explosion occurs when, in the interests of improving performance, too many pre-calculated aggregations are generated and stored for a cube. In theory, every non-leaf member in a cube can be pre-calculated such that every aggregation in a cube can be read from disk storage instead of being calculated at run time, increasing query performance. In reality, aggregation storage can increase exponentially, easily outstripping the storage requirements of the fact table upon which the cube is based.

Analysis Manager provides two effective mechanisms, the Storage Design Wizard and the Usage-Based Optimization Wizard, to intelligently manage aggregation storage. The Storage Design Wizard uses mathematical simulations to determine the optimal aggregation storage pattern based on a uniform query distribution, and can modify the simulation to limit either storage requirements or performance gain. The Storage Design Wizard is based on a uniform simulation of usage patterns for a given cube. Consequently, the performance increase is not as much as it would be if the actual usage patterns for a cube were analyzed. The Usage-Based Optimization Wizard can analyze actual usage patterns from a variety of viewpoints and generate aggregations tailored to the actual usage patterns of the cube. By using both the Storage Design Wizard and the Usage-Based Optimization Wizard when designing aggregations, you can minimize data explosion and tailor cube performance to the particular needs of your users.

For more information about aggregation design and impact, see "Aggregations" later in this chapter.

Sparsity

Sparsity is a measurement of the density of data within a cube or dimension. The more empty cells found in a cube, the sparser the cube data. Ideally, a cube should contain few or no empty cells; realistically, cubes will contain empty cells based on the dimensions from which the cube is organized.

Dimension sparsity, similarly, is a measurement of empty members (members with no data associated to them in any fact table) within a dimension. The more empty members found in a dimension, the more likely the dimension is to introduce cube sparsity when employed in a cube.

In traditional OLAP systems, disk storage is allocated for every intersection of members across dimensions in a cube, even if the resulting cell is empty (in other words, it contains no data). In other OLAP products, creating a sparse cube means that disk storage is wasted because of a lack of cube data. Because Analysis Services does not allocate storage for empty cells, disk storage is effectively handled for sparse cubes; in terms of storage, all cubes are completely dense. However, a sparse cube can also indicate a flawed cube design.

The primary cause of cube sparsity is the use of dimensions with sparse or unrelated data. This may seem obvious, but the combination of even marginally sparse dimensions can exponentially increase the sparsity of a cube.

For example, the following diagram indicates three dimension hierarchies used to construct a cube for tracking orders. Each is based on a dimension table that contains the definitions for the members of each dimension. Not all the members of all the dimensions contain data, however; not all the customers in the Customers dimension, for instance, have purchased a given product in the Product dimension, nor have all customers purchased a given product for every member in the Time dimension. For this example, each dimension has five members, three of which have data completely associated with the other dimensions.

For example, Customer 1 in the Customers dimension has purchased Product 2 in the Products dimension, during the period indicated by the Time 1 member in the Time dimension.

By themselves, the dimensions do not appear overly sparse; each dimension has members with relevant fact table data. If these dimensions are used together in a cube, however, the sparsity of the cube increases exponentially with each dimension, because the introduction of each dimension exponentially increases the number of cells within the cube. The following diagram indicates which cells actually contain data.

 

Although three out of five members of each dimension have relevant data, out of a possible total of 125 cells (five times five times five) in the constructed cube (not counting aggregate members), only 27 cells (three times three times three) are actually populated. The other cells represent intersections of empty members, creating empty cells in the cube.

This cube is a theoretical example; in reality, many dimensions are much more sparse than those indicated in the previous diagrams. The previous diagram, for example, assumes that all members with data in each dimension are completely cross-linked. Customer 1 in the Customers dimension has purchased all three Products dimension members in all three periods indicated by the members in the Time dimension. Even this level of density is rare in real-world situations. Unfortunately, sparse dimensions are often unavoidable; dimension structures are a tradeoff between density and granularity. In order to increase granularity, density is reduced as more potentially empty members are added. In order to increase data density, granularity is reduced, as members are often pre-aggregated into the underlying dimension table.

Dimensions with unrelated data can also greatly increase cube sparsity, especially if the dimensions are included as part of an associative relationship. For example, a business case is designed to compare the sales from retail customers with the sales from vendors, so a cube with three dimensions representing sales, customers, and vendors is created. The Customers dimension organizes retail customers by location, the Vendors dimension organizes vendors by sales region and vendor type, and the Orders dimension organizes order quantities by date. Both the Customers and Vendors dimension share elements with the Orders dimension, but not with each other. Because customers and vendors do not directly relate, from the viewpoint of the underlying data source, the result is a very sparse cube. In this case, it is easier to construct two cubes, one for vendors and one for customers, which share the Orders dimension. A number of techniques can be used to correlate the data across two (or more) cubes without requiring that a very sparse cube be created.

Query performance can also be adversely affected by cube sparsity. However, Analysis Services manages the storage and access to multidimensional data so efficiently that the impact is negligible. For example, a very sparse cube can take slightly longer to resolve calculated members and calculated cells, and MDX functions involving empty cells, such as CoalesceEmpty or NonEmptyCrossjoin, take slightly longer to process because of the large volume of empty cells that must be considered by such functions. Only very sparse, very large cubes will be affected on performance – for all practical purposes, cube sparsity provides a negligible performance impact in Analysis Services.

The best way to prevent sparsity is to review the design of shared dimensions, and ensure that the appropriate balance of density and granularity is maintained. For more information on dimension design, see "Designing Dimensions" later in this chapter.

Designing Dimensions

The design of dimensions in Analysis Services is central to cube design; after all, a cube is a representation of aggregated fact table data organized along dimensions.

The key influence on the design of dimensions is the structure of the underlying dimension data. Companies that employ an OLTP strategy complex enough to require an OLAP solution often have an established data warehouse, whose fact and dimension tables can be used to create dimensions and cubes in Analysis Services. Although it is possible to derive the required dimension design and the resulting underlying dimension and fact table structure by starting with the end result of the desired cube and working backwards, is not recommended for companies that have established data warehouses. This approach is better suited to solutions tailored to a small but specific set of business requirements, or used as part of a comprehensive data warehousing strategy, to better identify data warehouse business requirements.

Initial Design Questions

Two questions must be asked when designing dimensions; the answers directly influence the rest of the dimension design. Should a star schema or a snowflake schema be used, and, should dimensions be shared or private?

Star Schema or Snowflake Schema?

When designing a dimension, the first major decision is based not on the requirements of the dimension, but on the structure of the underlying dimension tables from which the dimension is to be based. OLTP data sources typically use an entity-relationship (E-R) schema technique for modeling transactional data, because OLTP transactions usually involve a large number of transactions of small amounts of data. Data warehouses, on the other hand, usually involve fewer transactions of very large amounts of data, so E-R schema technique is not as efficient. Instead, data warehouses generally employ one of two approaches towards schema design, referred to as star schema or snowflake schema.

The following features characterize a star schema.

• One or more dimension tables, each with a generated primary key, which contain one or more descriptive fields used to determine level attributes for each dimension. 

• A single fact table, containing additive and numeric fields, that employs a compound primary key. This key is constructed from the generated key of each dimension used by the fact table. 

One table is used for each dimension, and that table contains all of the information needed to define a particular dimension. Most data warehouses are constructed from multiple, overlapping star schemas.

The following diagram illustrates a typical star schema.

The following features characterize a snowflake schema:

• One or more dimension tables, each with a generated primary key, which contain one or more descriptive fields used to determine some level attributes for each dimension. In addition, a dimension table may contain a join to one or more other tables that supply additional level attributes for each dimension. 

• A single fact table, containing additive and numeric fields, that employs a compound primary key. This key is constructed from the generated key of each dimension used by the fact table. 

In a snowflake schema, more than one table is required to completely define the relationship of a dimension to a fact table. For example, the Product table uses the Product_Class table in the FoodMart 2000 sample database to provide additional level attribute information, such as product department and product subcategory, about the Product dimension. The Product dimension employs a snowflake schema, because more than one table is required in order to completely describe the dimension.

The following diagram illustrates a typical snowflake schema:

The most common reason for using a snowflake schema is to reduce storage requirements for large dimension tables. Low cardinality fields are removed from a dimension table, stored in a separate table with a primary key, and joined back to the dimension table. This can considerably reduce storage for a dimension table that includes members, but can also introduce complexity.

Another reason for using a snowflake schema is to supply information, not about the level attributes of a dimension, but the secondary attributes of a level. For example, the County table contains basic demographic information, such as name, state, region, and so on, for a given county, as well as sales and marketing aggregation data for that county. The Customers table has a join to the County table, but the information supplied by that join is not needed directly to describe a customer. However, it can provide additional information, in the form of member properties, to the County level or, in the form of additional levels, to the Customers dimension. While the County table is not required to describe the Customers dimension completely, using a snowflake schema allows additional information to be added to the Customers dimension that would otherwise require a separate dimension.

Data warehouses and other data sources suitable for OLAP are usually constructed by using overlapping star or snowflake schemas. For example, because the Customers dimension table would serve for the Orders fact table and the Returns fact table, the star schema representing orders and the star schema representing returns would both contain the Customers dimension. Other forms of database modeling, such as E-R modeling, can also be used, but are not recommended. Although it is possible to create dimensions and cubes directly from OLTP data sources, for better performance, using a separate data source derived from the OLTP data source is recommended. Because of the high transactional demand placed on OLTP databases, OLTP database systems are aggressively optimized for a very high number of insert/update transactions. Data warehouses, on the other hand, are usually bulk-loaded during off hours and optimized with an emphasis on retrieval speed and efficiency. Analysis Services can load an OLTP database system with requests that require high volumes of data, especially during cube and dimension processing.

Dimensions in Analysis Services can be optimized based on the type of schema used for the underlying dimension tables. In general, snowflake schemas should be used only when necessary, as the additional joins impact query performance, especially with ROLAP dimensions or dimensions that store few or no aggregations.

For more information about using star and snowflake schemas in an overall data warehousing strategy, including performance tuning and schema optimization, see Chapter 20, "RDBMS Performance Tuning Guide for Data Warehousing."

Shared or Private?

The next decision to make when designing a dimension is whether to share it for use in multiple cubes. Typically, you will create shared dimensions to deal with almost every business case — the more conformed the dimension, the more useful it is, and therefore the more likely you will use it in multiple cubes. In rare situations, though, private dimensions are quite useful when dealing with certain business cases.

The purpose behind using a private dimension is to restrict its usage in other cubes. Unlike shared dimensions, private dimensions cannot be used in multiple cubes, nor can a private dimension be the source for creating a virtual dimension. Although usage restrictions can be accomplished with dimension security, you may want to restrict the use of a dimension because of its specificity or sparsity. For example, a dimension that relates specifically to a small subset of inventory may not be suitable for general use, but is appropriate for providing reporting for a specific client or vendor. In this business case, a private dimension may be more appropriate than a shared dimension.

Private dimensions offer an advantage in terms of administration: private dimensions are always processed when the cube they reside in is processed.

In addition, private dimensions offer a hard level of security for sensitive data; because private dimensions cannot be used in other cubes, correlating sensitive data can be controlled. Although dimension and cell security features can be utilized to enforce security, a private dimension provides a straightforward and solid barrier against unauthorized usage of data.

Shared dimensions offer a performance advantage because shared dimensions make more efficient use of memory; the members of a shared dimension are loaded once, then cached. This allows for faster response times from queries that involve shared dimensions.

You should consider the question of making shared or private dimensions carefully. Although a previously shared dimension can be made private, the reverse is not true; once a dimension becomes private, it can never be shared. Unless your business requirements indicate that a dimension should not be used in multiple cubes, shared dimensions are usually recommended.

Design Guidelines

• Treat a dimension as an object interface. 

  Approach the design of dimensions in a manner similar to the design of objects in an object-oriented programming language. A dimension can be thought of as an interface to a cube, with the structure of the dimension used to provide attributes to a cube.

  Shared dimensions are intended for use in more than one cube; the decision to include levels, members, member properties, and so on can be approached in the same way that the decision to include attributes in an object interface is approached. As with any other interface, polymorphism is inversely proportional to the specificity of its attributes - the more numerous and specific the attributes of an interface, the fewer the number of objects that can use it. Abstraction mechanisms, such as virtual dimensions and dimension hierarchies, can exploit a well-designed regular dimension to provide additional complexity as required. 

  You should design shared dimensions independently of the design of a specific cube, for reasons similar to those used when designing an interface implemented by more than one object. A private dimension, on the other hand, acts as a private interface, and as such should be designed for a specific cube. 

Dimension Varieties

The power and flexibility of dimensions has been greatly expanded in Microsoft® SQL Server™ 2000 Analysis Services, to provide support for complex OLAP applications. Many of the new dimension varieties and characteristics are interrelated; cube performance can be affected not just by a dimension variety, but also by the combination of characteristics used in a dimension.

SQL Server Books Online details the combinations of dimension characteristics that can be used within each dimension variety. Practical application of dimension varieties and characteristics depends, however, almost entirely on the business requirements for the dimension and the cubes for which the dimension is created.

Changing Dimensions

Changing dimensions are dimensions optimized for frequent changes to either data or structure. Unlike other dimensions, changing dimensions do not need to be fully processed when certain types of changes are made to the underlying dimension data or dimension structure. Because fully processing a dimension also requires fully processing the cubes to which the dimension is associated, changing dimensions can increase the availability of cubes whose data changes frequently.

Changing dimensions can also take advantage of lazy processing in cubes, further increasing the availability of data for users accessing cubes that include the dimension.

Changing dimensions should be used when immediate user availability to aggregated data is needed or when member and hierarchy flexibility is required. Although changing dimensions do allow a great deal of flexibility in terms of both data and structure, the tradeoff is performance. Typically, queries involving changing dimensions take much longer than queries involving regular dimensions.

Design Guidelines

• Use sparingly, and only where appropriate. 

  Changing dimensions are best used for medium-latency to low-latency OLAP data. Most dimensions contain high-latency OLAP data, in which the time taken to reflect changes in underlying dimension data can be measured in days, weeks or months. At the other end of the scale, real-time OLAP demonstrates low-latency OLAP data, in which immediate changes to underlying dimension data are reflected as they happen. Changing dimensions occupy the middle to low end of this scale, including real-time OLAP. 

  Changing dimensions, by definition, reflect frequent unpredictable changes to the data and structure of a dimension. However, not all changes to the underlying dimension data need to be quickly reflected to users - consider the business requirements of such data first. If changes do not need to be made available to users quickly, avoid using a changing dimension. 

  A good rule to use is:

  • If the granularity of changes to a dimension is finer than the granularity of processing for a dimension or for the cubes that include the dimension, and the data needs to be reflected in cubes that include the dimension, consider a changing dimension.

  For example, if you plan on processing the entire database once each week, but you expect daily hierarchical changes to a particular dimension that should be shown to users, a changing dimension may be appropriate. Conversely, you may want to increase your processing frequency for this particular dimension to once each day and avoid using a changing dimension, increasing query performance and decreasing latency for your users. 

Virtual Dimensions

A virtual dimension is a type of changing dimension that allows you to use an existing regular dimension to obtain additional information. For example, a virtual dimension based on the member properties of a regular dimension can provide a different view on existing dimension data without the cost of additional disk storage.

Virtual dimensions are fully indexed, improving query performance over previous versions of Analysis Services. However, because virtual dimensions do not store source data or aggregations, queries involving virtual dimensions may not perform as well as queries against regular dimensions.

By using virtual dimensions based on member properties you can fully exploit the benefits of member properties. In terms of overall design, it is more efficient to use virtual dimensions that are based on regular dimensions but are specifically targeted for a subset of dimension data represented by member properties, than it is to use multiple regular dimensions based on the same underlying dimension data.

Design Guidelines

• Use to exploit additional data offered by regular dimensions. 

  The greatest value of virtual dimensions is the ability to expose existing additional regular dimension data, such as member properties, in a different hierarchical framework; this is especially useful when used with regular dimensions that represent multiple yet similar business entities.

• Do not use as a replacement for dimension hierarchies. 

  Avoid using virtual dimensions just to represent a regular dimension with a different hierarchy; better performance and easier administration is gained by using multiple hierarchies for a dimension. Dimension hierarchies are basically regular dimensions with aggregation and data shared with a base regular dimension, organized into a different hierarchical structure. Virtual dimensions can be used for this purpose, but they do not perform as well. For more information on the use of multiple hierarchies, see "Dimension Hierarchies" later in this chapter. 

Parent-Child Dimensions

A parent-child dimension is a special dimension designed to support self-referencing dimension tables in data warehouses. In regular dimensions, the level structure of the hierarchy depends on the number of columns chosen from the underlying dimension tables and the number of columns that are mapped to a level in the dimension. Each column used for a regular dimension represents a single business entity in a dimension; for example, the members of a Country level in a geographic dimension can represent countries.

This is not true of parent-child dimensions. In such a dimension, two columns from the underlying dimension table define relationships among the members of the dimension. One column supplies the unique identifier of the referenced member, and the other column supplies the unique identifier of the parent for a referenced member.

By its very nature, such a self-referencing dimension table can produce an unbalanced hierarchy. For example, the following table lists the employees of an organization.

Employee ID	Name	Parent Employee ID
1	Bill
2	Ralph	1
3	Jane	2
4	Tom	3
5	Beth	4
6	Elaine	5

In this example, not all records have a parent employee ID – one record must be at the top. Analysis Services supports four ways to determine a topmost member, or root member, through the Root Member If property of a dimension. For more information on the settings for the Root Member If property, see SQL Server Books Online.

A parent-child dimension can also support ragged hierarchies. To do this, the Skipped Levels Column property must contain the name of a column in the dimension table that stores the number of skipped levels between the referenced member and its parent member.

There are other benefits to parent-child dimensions, such as the ability to write-enable a parent-child dimension. Virtual dimensions and regular dimensions based on ROLAP partitions are also considered changing dimensions, but that encompasses the possibility of changes to underlying dimension data by other mechanisms. Write-enabling a parent-child dimension allows you to change the hierarchy or properties of members in the dimension directly, using Analysis Services as the primary mechanism, instead of indirectly changing the dimension hierarchy by changing the underlying dimension table data, using the relational database server as the primary mechanism. Unlike write-enabled cubes, which use a special table to maintain changes to the cube, parent-child dimensions write directly to the underlying dimension table. This feature is useful in rapid development scenarios or when an established data warehouse does not exist.

Parent-child dimensions have some limitations. Parent-child dimensions cannot support huge dimensions (ten million members or more), because parent-child dimensions require MOLAP storage. Because of this storage requirement, parent-child dimensions cannot support real-time OLAP.

Design Guidelines

• Use only where absolutely necessary. 

  Parent-child dimensions offer some very powerful features, but at a high cost in performance and ease of administration. A parent-child dimension is used only for business scenarios that demand an unbalanced hierarchy; avoid using them in any other situation. 

Dimension Characteristics

Dimension characteristics help determine the behavior and organization of a dimension by supplying additional functionality to each variety of dimension. Multiple hierarchies, member properties, even storage and processing options influence the performance of an individual dimension and the performance of the cubes to which the dimension is associated. Because of this potential effect on cubes, understanding the complex interaction of dimension characteristics and varieties should be a priority when designing cubes.

Dimension Hierarchies

Using hierarchies within dimensions properly can greatly increase dimension performance and allow you to represent business data more efficiently. A dimension can represent three types of hierarchies: balanced, unbalanced, and ragged.

Typically, the underlying dimension data and table structure will determine the hierarchy used by a given dimension. If a specific hierarchy is desired, the underlying dimension data can be represented by a database view.

Balanced Hierarchies

A balanced hierarchy contains no skipped or empty levels. All branches of the dimension hierarchy tree descend through all existing levels, and each level has at least one member. All members at a given level have the same number of ancestors. This type of hierarchy is usually represented in time dimensions or in highly conformed dimensions that represent a specific business entity, such as a product or an order. Most dimensions employ a balanced hierarchy.

Unbalanced Hierarchies

An unbalanced hierarchy, in contrast to a balanced hierarchy, can contain skipped or empty levels, and levels that contain no members can exist. Similarly, members at a given level may have different numbers of ancestors. Branches of an unbalanced hierarchy may not descend through all existing levels.

Unbalanced hierarchies can be used in dimensions that represent organizational or accounting data, or for dimensions with unclear level demarcation. Because of the inferential nature of unbalanced hierarchies, only parent-child dimensions can support unbalanced hierarchies.

Ragged Hierarchies

The only conceptual difference between a ragged and an unbalanced hierarchy is the fact that branches of a ragged hierarchy descend through all existing levels, even levels that do not contain data. Unlike an unbalanced hierarchy, a placeholder member is automatically created to support the subordinate levels for levels without data, ensuring that all branches descend through all levels. The placeholder also ensures that all members at a given level have the same number of ancestors. The representation of placeholder members is managed through the use of the Hide Member If property in the Dimension Editor. For more information about placeholders and the Hide Member If property, see SQL Server Books Online.

Geography-based dimensions, especially those that use international data, or dimensions that represent multiple yet similar business entities are excellent candidates for ragged hierarchies.

Multiple Hierarchies In A Single Dimension

You can provide different views of existing dimension data by creating multiple hierarchies for a single dimension. Analysis Services can create aggregations that are optimized on the common members and levels of all the hierarchies for a given dimension. The benefit to this is that all of the hierarchies of a given dimension use the same source data for aggregation purposes and use as much of the aggregated data as possible. This can save disk storage, especially in the case of very large and huge dimensions, as well as increase performance more than creating separate regular dimensions with a different hierarchy in each one.

Adding a dimension hierarchy to a dimension is not unlike creating a view for a table in a relational database. While the table contains the source data, the view offers a different way of examining the source data. Although at first glance this functionality appears to overlap with the functionality of virtual dimensions, there are differences. Unlike a virtual dimension, a dimension hierarchy can store aggregations as needed, providing additional performance gain when querying a cube. Also, most client tools understand the concept of dimensions with multiple hierarchies, and can use this functionality to full effect.

Dimensions that store highly conformed business information are likely candidates for the use of multiple hierarchies, such as customer or order information, to prevent duplicate aggregations. Time dimensions are also excellent candidates for the use of multiple hierarchies, especially for financial applications.

Design Guidelines

• Know your dimension data. 

  Understanding how the underlying dimension data is structured and stored will all but determine the hierarchical structures needed by the dimension, as well as the dimension type itself. For example, if your dimension data is used to illustrate the management organization of a particular company, you will most likely need to employ an unbalanced hierarchy, which also means you will need to construct a parent-child dimension, which in turn determines the value of several other properties in a dimension. 

  If the properties of a particular dimension type are desired, you may need to construct a view to base the dimension on, in order to confirm the underlying dimension data to an appropriate hierarchy. 

• Use multiple hierarchies instead of many levels. 

  Rather than constructing a regular dimension with ten levels and a dozen or so member properties to organize business entities, such as products, with many different categorization attributes, use multiple hierarchies to group such business entities in different, but related, views of aggregation. Time dimensions are also prime candidates for this approach; create different hierarchies to represent different views of time periods, such as fiscal year and calendar year, of the same dimension data, instead of attempting to combine both views into a single hierarchy. 

  This approach should be balanced with the use of virtual dimensions to supply additional views of aggregation. For more information about the use of virtual dimensions, see "Virtual Dimensions" later in this chapter. 

Levels and Members

The layout and data associated with members directly influences the processing performance, query performance, and resources needed for a dimension.

Member Keys

The member key is the most important property of a member when it comes to processing performance. During dimension processing the server checks each member against the index of the dimension. If the member key does not exist in the index, the server inserts the new member key into the index for the dimension, which is also sorted by the member key.

Based on the cardinality of the dimension, hierarchies in which the count of members for a given parent in a level varies widely may be slower to process than hierarchies in which the count of members for each parent is more balanced. When the dimension is queried, groups of members are sent to the client based on parentage. A large number of members for a single parent, then, can degrade performance, as large blocks of data must be transferred to the client.

Design Guidelines

• Include as few levels as possible. 

  Reducing complexity is one of the primary reasons OLAP technology is used. When you design dimensions, your view should be on balancing complexity and ease of use. Too many levels in a dimension can make it too complex to easily reference in an MDX query, and it can impact performance and increase data explosion because each level above the last level contains aggregate members. Instead, if an alternate view on dimension data is needed, consider using a different hierarchy or a virtual dimension for the dimension in question. 

• Avoid data type coercion for member keys. 

  The size and data type of the member key value itself can affect processing and query performance. Avoid creating member keys with a data type different from that of the value actually stored in the underlying dimension table; coercion of data types takes time during processing. If data type coercion is necessary, create a view or table on the relational database that converts the data type as needed. 

• Try to use integer member key values. 

  When you choose a data type for a member key, remember that a numeric type (such as a 32-bit long integer) performs much better than a string, especially if the string for the member key is different than the member name. If a string is necessary, use a string with a data length as small as possible. A 4-byte numeric, such as a long integer, performs much better than an 8-byte numeric, such as a double precision number. 

  If a string must be used as a member key to satisfy business requirements, one method to improve performance is to change the compare mode of the server. Binary comparison performs best, followed by case sensitive comparison and case insensitive comparison. Before you consider this, though, first try all other options; changing the compare mode of the server affects all string comparisons, and so may affect query performance in unexpected ways. 

• Use unique member keys and names wherever possible. 

  Unique member keys allow the Analysis server to use the identifier supplied by the underlying relational database, increasing query and processing performance. For more information about the performance benefits of unique member keys, see "Dimension Storage and Processing" later in this chapter. 

  Similarly, unique member names in a given dimension allow for faster addressing when MDX queries are run against the Analysis server where the cube with that dimension resides. 

Member Properties

Initially, member properties in Analysis Services seem like an interesting, useful idea because they supply a useful mechanism for storing additional attributes of members in a given level of a dimension.

However, these extra attributes also allow you to create a virtual dimension with a hierarchy completely different from that of the regular dimension on which it is based, yet still take advantage of shared aggregations and storage. Member properties can also provide an easy mechanism for custom rollups, specialized calculations, and multiple language implementations, without the difficulties associated with pre-aggregation or data transformation on the relational database.

Design Guidelines

• Include as few as possible, as high in the hierarchy as possible. 

  Because member properties are cached along with other dimension data, retrieving member properties can be extremely fast. Conversely, because member properties place additional load on the server-side cache, they should be used only as needed if performance is a priority. A good approach to using member properties is to include only the minimum needed at the highest level possible to satisfy business requirements. For example, in a geographical dimension with levels based on Country, State, County, and Postal Code, a member property for sales regions can be created on either the County or Postal Code level. Creating it on the County level will save disk storage and cache space, which increases query performance. 

Real-time OLAP

Any dimension that is based on a Microsoft® SQL Server™ 2000 data source and that uses ROLAP storage can take advantage of real-time OLAP, also referred to as real-time updates. Real-time OLAP is on-the-fly aggregation taken one step further: the underlying data source provides notification of changes to the fact table of a dimension. This allows the dimension to aggregate in real-time. SQL Server indexed views are used as the underlying dimension and fact tables to support real-time OLAP, to take advantage of the aggregation storage and performance benefits offered by indexed views.

For more information about real-time OLAP, see "Real-Time Cubes" later in this chapter.

Design Guidelines

• Use this feature sparingly. 

  Although real-time OLAP is a powerful and exciting new feature for Analysis Services, most business scenarios do not require real-time OLAP. Real-time OLAP places real-time load on both the Analysis server and the underlying relational database, and should be used only if there is a very strong business requirement for low-latency OLAP data. 

• Design the dimension around real-time OLAP, not with it. 

  The decision to use real-time OLAP should be a central issue in designing a dimension; do not design a dimension and then later decide to include real-time OLAP. The structure and use of a dimension is heavily influenced by the decision to use real-time OLAP, which affects dimension processing, dimension data latency, dimension storage requirements, and other factors. If real-time OLAP is needed, consider the ramifications and make your decision early in the design process of the dimension. 

• Familiarize yourself with indexed views. 

  A new feature of SQL Server 2000, indexed views provide the speed and power of indexes with the abstraction, flexibility, and performance benefits of views. The requirements of an indexed view, however, should be considered when using real-time OLAP. Some data warehouses may not be able to exploit indexed views; query time increases because aggregations cannot be stored. 

Dimension Security

Dimension security should be not considered as a final step, applied to a fully designed dimension after construction. Instead, dimension security should be considered an integral part of the overall design process for a dimension; dimension security alters the way a dimension is employed and viewed by users.

Design Guidelines

When designing dimension security, keep these considerations in mind.

• Enable visual totals for all visible parent levels. 

  By default, visual totals are disabled for all visible parent levels. This means that, although you may have hidden several levels and members, the aggregate value for visible parent levels still includes the hidden data; users may be able to deduce secure information from the visible parent levels. Enabling visual totals allows you to display to the user the aggregate values of visible parent levels computed with only the subordinate levels and members visible to the user. 

• Use a layered approach to roles. 

  Dimension security is directly attached to roles. A user can be assigned to multiple roles, as is common in business scenarios, with a layered approach to various permissions. Therefore, dimension security should have a layered approach to various permissions. Avoid using the All Users role as a blanket security tool; creating very specific, encapsulated roles that provide access to specific layers of dimension data will help, not only in the security of a particular dimension, but in all of the cubes that use that dimension. It will also ease administration, as there are typically users that do not fit into a single, job-oriented role; using multiple roles to encapsulate specific business requirements allows you, as an administrator, the ability to match roles to specific job actions, not just to the job. 

Dimension Storage and Processing

Dimension Storage

The storage mode used by a dimension directly affects not just the query and processing performance of the dimension, but also the results obtained by querying the dimension.

Typically, MOLAP is the recommended storage mode for dimensions. Although ROLAP may reduce disk storage size, it places additional load on the underlying relational database, and can provide misleading information if the underlying dimension data changes even infrequently.

Dimension Processing

The design of a dimension influences processing performance. The impact on processing speed, query performance, and storage increases as the number of levels and members within a dimension increase. More disk storage is allocated not only for the data, but also for the index files needed by the dimension, and this affects not just the dimension, but also any cube that uses the dimension.

When designing dimensions, do not include members or levels that are not needed. As dimension information is cached on both server and client, this design change can noticeably improve not only server response, but also client application speed.

While unique members minimally affect processing performance for dimensions, the proper (or improper) use of unique members can greatly affect the processing and query performance of cubes a dimension is associated with, especially based on the storage mode of the partition involved. For more information about the effect of unique members on cube processing and querying, see "Cube Storage and Processing" later in this chapter.

Design Guidelines

• Use MOLAP for dimension storage as often as possible. 

  MOLAP dimensions use a multidimensional storage area separate from the underlying dimension data, while ROLAP dimensions directly use the underlying dimension data. Hence, if a change to the underlying dimension data is made, a ROLAP dimension may provide inconsistent information. ROLAP is only necessary for dimensions with ten million or more members, or for dimensions that support real-time OLAP. Avoid using ROLAP unless your dimension fits these criteria. 

• Use unique member keys whenever possible. 

  The proper use of unique members, especially at the lowest level of the dimension, helps in removing unneeded joins when processing a dimension, or for processing a partition using the dimension in a cube. A shared dimension, for example, that meets the following requirements for the members of the lowest level of the dimension does not require a join to be made when processing a partition that uses the dimension:

  • The values in the member key column are unique. 

  • A one-to-one relationship exists between the member key column and the member name column. 

  • There is only one join between the underlying fact table for the partition and the dimension tables of the dimension, based on the member key column. 

• Ensure that all columns involved in joining the underlying dimension table with fact tables are indexed. 

  If joins are required to process a dimension, especially after the dimension has been added to a cube, indexing all involved columns will speed the access and retrieval time from the underlying dimension table. 

Designing Cubes

The design of a cube, as indicated in the previous section, is heavily influenced by the design of its constituent dimensions. However, there is more to cube design in Analysis Services than dimension design and implementation. In Analysis Services, cubes can use calculated members, calculated cells, named sets, actions, custom rollup formulas, and other features to make a cube more than the sum of its dimensions. Special cube varieties, such as virtual or linked cubes, can be created to take advantage of existing regular cubes, allowing an atomic approach to designing regular cubes.

Your cube design should take into account the effects of cube sparsity and data explosion.

Design Guidelines

• Treat cubes as objects. 

  When designing regular cubes, approach the design process as if you were designing an object in an object-oriented programming language. Dimensions serve as interfaces to a cube, providing attributes that the cube can exploit to reveal fact table data. Encapsulate cubes the same way you would encapsulate any other object, ensuring that it reveals only the data pertinent to the purpose of the object. A cube for inventory information, for example, should not contain order information. If correlation between inventory and order information is required, build two cubes and join them across one or more common dimensions in a virtual cube, with the common dimensions serving as a pseudo-polymorphic interface between the two cubes. 

• Use well-designed dimensions.

  Construct dimensions with the business purpose for the dimension as the focus; very often, dimensions are constructed that mirror exactly the underlying dimension table structure of an existing data source. While this is generally a good practice, it can be advantageous to pair similar dimensions together into parent-child dimensions in order to decrease dimension sparsity. 

  Remember, too, that Analysis Services is a multidimensional presentation tool, used to solve business problems. Traditional E-R modeling techniques typically used for online transaction processing (OLTP) databases may not produce well-designed dimensions. Other techniques, such as dimensional modeling, should be employed to ensure low dimension sparsity.

• Use sparse and unrelated dimensions cautiously. 

  Sparse dimensions are common and sometimes required in order to satisfy a specific business requirement. Too many sparse dimensions included in the same cube can degrade performance when using complex MDX queries. As a rule, if you have included five or more sparse dimensions in your cube, you can probably redesign the cube into two smaller cubes with at least one shared dimension. A virtual cube can later be used if correlation between the two cubes is required. 

Cube Varieties

In contrast to dimensions, the basic functionality of cubes does not vary as much among cube varieties. For example, the primary difference between a regular cube and a distributed partitioned cube has nothing to do with the direct functionality of the cube, but the retrieval of the underlying data for the cube. Similarly, virtual cubes and linked cubes are views of regular cubes, whereas real-time cubes are regular cubes using ROLAP partitions that support real-time OLAP.

Regular Cubes

The best way to define a regular cube is by exclusion. SQL Server Books Online describes a regular cube as ". . . a cube that is not a linked cube, virtual cube, or local cube." Most business requirements are satisfied with regular cubes, and will be the most commonly created cube type in SQL Server 2000 Analysis Services. Indeed, virtual cubes and linked cubes require underlying regular cubes, while write-enabled cubes, real-time cubes, and distributed partitioned cubes are regular cubes with special storage support.

When designing enterprise solutions that include Analysis Services, keep in mind that regular cubes form the basis of all Analysis Services applications. Design regular cubes first, before you plan other cubes, and approach the design process in a fashion similar to the approach used to develop objects and interfaces in an object-oriented programming language. Cubes should be designed to satisfy a specific business purpose, and should only include information relevant to that business purpose. While the temptation of creating a few super-cubes to satisfy every business requirement is strong, performance and maintainability will suffer as a result. Other cube varieties, such as virtual cubes, become more effective when cubes are designed to satisfy single business purposes.

Design Guidelines

• Always design cubes with a specific and encapsulated business focus in mind.

  Use the minimum number of dimensions required when constructing a cube designed to resolve a specific business case. When considering cube sparsity, it is often easier to construct two smaller, dense cubes and use a virtual cube or other mechanism to correlate data than to build one large, sparse cube. A good guideline to follow is the assumption that the disk storage of a fully aggregated cube will at least double with each added dimension, provided the density of data for each dimension is about the same. 

Virtual Cubes

One of the most powerful features in Analysis Services is the ability to create virtual cubes - a single cube based on one or more regular cubes that share at least one common dimension. A good way to look at a virtual cube is as a view to a table in a relational database. A virtual cube gives you the ability to construct cubes in an atomic fashion, constructing individual regular cubes for specific business purposes, and then using virtual cubes to correlate data across business purposes. For example, the FoodMart 2000 database uses the Sales cube to encapsulate aggregations relating to sales orders, while the Warehouse cube is used to encapsulate aggregations relating to inventory. The Sales and Warehouse virtual cube is then used to correlate incoming inventory against outgoing sales orders, eliminating duplication of aggregates across common dimensions and providing additional business intelligence.

However, virtual cubes are at their most effective only when at least two or three common dimensions exist in the underlying regular cubes. Because all dimensions used by all source cubes are included in the virtual cube, cube sparsity can become an issue if dissimilar cubes are used to create a virtual cube. For example, a cube with five dimensions and a cube with four dimensions, which both share only a single dimension, are used as the underlying cubes for a virtual cube. Assuming the other dimensions used in both cubes are dissimilar (that is, they share no underlying data in dimension or fact tables), the resulting virtual cube now has eight dimensions (since both cubes share a dimension), seven of which have little intersecting data. This is an extremely sparse cube by any measurement. Although Analysis Services minimizes performance issues when dealing with sparse cubes, this design is not the most efficient use of resources, and querying it could take longer than necessary due to the sheer volume of empty cells that need to be negotiated.

If you are considering constructing a virtual cube to resolve a single member or process a single MDX formula, other approaches may be more useful and require less maintenance. For example, you can meet these needs by using the MDX LookupCube function to create a calculated member in one of the regular cubes that uses the LookupCube function to retrieve information from a different regular cube.

In general, virtual cubes should be used when correlating many values across two or more congruent dimensions is required, or when most of the values along a single congruent dimension are needed, while the LookupCube function should be employed when a specific subset of values or members from another cube are required.

Linked Cubes

Linked cubes are an easy way for you to alias cubes from remote servers on a local server. The local server contains the meta data for the linked cube, while the remote server maintains the actual data and underlying fact table and dimension table data for the underlying regular cube. For example, a server in the central office of a health care corporation can link to cubes created at individual health care sites, and then a set of virtual cubes can be created that are based on these linked cubes to correlate data across regions.

From a business perspective, linked cubes remove the repetition of regional data and can allow access to non-conformed data sets without the construction of additional regular cubes. Another benefit is the ability to create marketable OLAP data, as access to a cube can be fully controlled.

Because both local and remote server caches are used when retrieving data from a linked cube, query performance involving linked cubes is increased in comparison to a user directly accessing a cube on a remote server, depending on network latency. Virtual cubes created from multiple regular and linked cubes allow for distribution of load across multiple servers, also improving performance.

Distributed Partitioned Cubes

The term distributed partitioned cube refers to any cube for which at least one of its partitions resides on a server other than the server used to store the meta data for the cube. For example, although server \\SERVER1 stores the Budget cube and the partition that represents the data slice for the current year, a remote partition on server SERVER2 also exists for this cube that stores the data slices for the previous five years. A third server, SERVER3, provides storage for a remote partition that stores the data slices for older data.

The primary benefit of distributed partitioned cubes is the ability to leverage mass storage servers on an existing network. In the previous example, the server \\SERVER1 is optimized for performance, not storage, but \\SERVER2 and \\SERVER3 may access RAID arrays that can support the high storage requirements of the historical data stored by the Budget cube. Because all meta data for distributed partitions resides on the server that stores the cube definition, remote management of servers that support distributed partitions is unnecessary. Synchronization of the distributed partition structure is propagated to other servers automatically.

Also, most of the processing and query performance is delegated to the server that stores the remote partition. Because of this feature, you can distribute the process and query load of a large cube across multiple servers, improving performance. The handling of this distribution is transparent; Analysis Services handles all of the details involved with synchronization, latency, and so on.

The primary drawback of distributed partitioned cubes is that remote partitions cannot be processed in parallel, nor can a remote partition be archived or restored.

Real-Time Cubes

Unlike dimensions, cubes have to satisfy more requirements in order to use real-time OLAP. Because of the notification mechanism used by Analysis Services, the underlying data for a cube must be based on a SQL Server 2000 relational database. The cube must use ROLAP partitions; however, only those partitions that require real-time OLAP need to use ROLAP storage. The ROLAP partition must either contain no aggregations, so that all aggregations are calculated on the fly, or must use a SQL Server 2000 indexed view to supply the underlying fact table data.

Both options supply the same basic functionality. Although using a ROLAP partition with no aggregations forces Analysis Services to compute and cache necessary aggregations from the fact table, using a SQL Server 2000 indexed view allows pre-computed aggregations to be stored in the index of the view, reducing computations during query time. For more information about the general performance increase provided by indexed views, refer to the white paper, "Improving Performance with SQL Server 2000 Indexed Views," available in the MSDN® Library. You should use partitions based on indexed views in preference to partitions without stored aggregations, as performance is somewhat better.

Because remote partitions cannot be used to support real-time OLAP for cubes, during the design phase you need to consider the physical architecture of the Analysis server. Typically, real-time OLAP is used for Internet and intranet-based applications desiring live information, although any client application needing low-latency OLAP data can use real-time OLAP. Real-time OLAP is not recommended as a convenient way to circumvent the implementation of a data warehouse; the necessary query load can impact the performance of an OLTP data source.

Real-time OLAP for cubes is best used when predictable latency periods can be enforced. For example, an online financial application can replicate data from an OLTP database to a data mart for a specific business purpose at set times—every 20 minutes or so. The notification mechanism for real-time OLAP handles the rest, triggering the indexing and aggregation process and refreshing the server cache.

Offline Cubes

Microsoft PivotTable™ Service provides the ability to create offline cubes (also referred to as local cubes). An offline cube is a cube constructed on the client workstation, as opposed to a cube constructed on an Analysis server.

Caching and Cubes

While many traditional OLAP products offer server-side caching of frequently accessed multidimensional data, Analysis Services also offers client-side caching. The client-side cache is managed by an algorithm that uses the frequency of access for a given query to determine which query datasets are replaced.

The client-side cache can be a benefit for cubes that use well structured and highly utilized shared dimensions. If the client-side cache can provide all of the data needed to handle a request, no network activity takes place; consequently, the response speed is much faster. The server-side cache is available to all users of a given Analysis server, so fulfilling queries that involve such shared dimensions, even if the dimension is used in different cubes queried by different users, is also much faster. The server-side cache can respond and populate the client-side cache, all without requiring direct retrieval from the cube or dimension.

Cube Characteristics

Although described as separate objects in SQL Server Books Online, cube characteristics such as partitions, aggregations, and measures can add functionality to the cube varieties discussed earlier in this chapter. Used properly, such cube characteristics can dramatically increase the performance and flexibility of cubes.

Partitions

The organization and storage method of partitions for a cube can greatly influence the query and processing performance of the cube. The use of multiple partitions greatly speeds query and processing performance for cubes, provided that the partitions are properly organized and use an appropriate storage mechanism.

Partition Organization

The first decision about using multiple partitions is typically based on the organization of the underlying fact table and dimension table data associated with a cube.

For example, underlying fact table data that is infrequently accessed (closed accounts, obsolete product lines, and so on) can be partitioned in such a way as to take advantage of storage optimizations, while frequently accessed fact table data for the same cube can be partitioned to take advantage of performance optimizations.

Creating such data slices, however, requires forethought. Because queries can retrieve data in parallel from multiple partitions, data slices should be created from commonly used delineations. Determine such delineations by separating data that will not be queried as a whole or combined in order to fulfill a query. For example, a financial cube containing accounting information is organized on a Time dimension that uses Year, Quarter, and Week levels. Most queries against this cube analyze data across weeks and quarters, but rarely across years. This cube can then be organized with a single partition for each year to improve query speed.

When processing a cube with multiple partitions, the server can also optimize processing by creating segment map and data files that involve only those dimensions and levels relevant to a particular partition, generating smaller and more efficient partitions.

Partition Storage

Although partition data can be organized as needed to satisfy business requirements, there are restrictions on which storage formats can be used based on other features used by the cube or associated dimensions.

Each partition storage mode influences the processing performance, query performance, and storage requirements of a partition. If performance is required, MOLAP or HOLAP storage modes are recommended. When queried, MOLAP performs better than HOLAP; when processed, both storage modes perform equally well. If storage is the primary concern, HOLAP partitions are recommended because, unlike MOLAP, HOLAP does not duplicate fact table data. ROLAP partitions must be used to support specific functionality such as real-time OLAP, but in all other cases ROLAP partitions are recommended only if resources are unavailable to support the other storage modes, or functionality specific to the underlying RDBMS is necessary for management purposes.

Record Size

Cube processing and query performance is directly affected by the record size of the partitions for the cube. When processing a cube, the record size impacts file size and load times, while retrieval and transfer speeds are directly affected when querying a cube.

You can estimate the size of a record by using the following calculation**(1)** 

Size = (2*levels*Cpath)+(4*measures4*Cdata4)+(8*measures8*Cdata8)

The elements of the formula are explained below:

levels – For fact data, the total number of levels in the cube (excluding the All level). For aggregation data, the total number of levels used in the aggregation (also excluding the All level).

Cpath – The coefficient of compression for paths in the cube. The value for this variable can range from 0.11 to 1.0, but is typically close to 0.25.

measures4 – The number of 4 byte measures in the cube.

Cdata4 – The coefficient of compression for 4 byte data in the cube. The value for this variable typically ranges from 0.6 to 0.8.

measures8 – The number of 8 byte measures in the cube.

Cdata8 – The coefficient of compression for 8 byte data in the cube. The value for this variable typically ranges from 0.4 to 0.5.

Minimizing record size has a direct impact on the performance of large cubes. Record size can be reduced, for example, by the use of 4-byte measures, such as long integers. Less raw disk storage is needed, and the compression coefficient for 4-byte measures is typically better than that of 8-byte measures.

________________________________

Partition Processing

The processing performance of a partition depends not just on the optimization of the partition design, but the optimization of data retrieval from the underlying fact and dimension tables used by a partition.

Aggregations

Designing aggregation storage is essentially a tradeoff between disk storage and CPU time; more aggregations reduce CPU time, but increase disk storage requirements. Due to the nature of multidimensional structures, the disk storage can increase exponentially. For example, using the Storage Design Wizard on the FoodMart 2000 database to increase the performance of the MOLAP partition for the Sales cube by 50 percent adds 91 aggregations, involving a total disk storage of approximately 68.987 megabytes (MB). By comparison, increasing the performance gain to 100 percent adds 1036 aggregations, involving total disk storage of approximately 1290.089 MB. In other words, if the approximate size of the data stored in the fact table used by the Sales cube is 3.64 MB (44 bytes per record, 86837 records), then the first option provides a data explosion factor of 2.06, while the second option causes a data explosion factor of 354.42.

The percentage used in deciding performance gain represents the percentage of queries that can be answered from pre-calculated aggregation data, rather than by calculations performed on underlying fact table data (also referred to as on-the-fly aggregations).

The performance gains provided by the increase in pre-calculated aggregations, however, can offset the additional resource demand for disk storage. For example, a sample query which takes approximately 3.9 seconds when run against the Sales cube after the partition has been optimized for 50 percent performance gain, takes only 2.2 seconds after the partition has been optimized for 100 percent performance gain.

Administration of a cube with a high number of pre-calculated aggregations can be adversely affected, however, because the processing time needed to refresh a cube increases with the number of aggregations. Most cubes become unavailable during processing, so increased number of pre-calculated aggregations could mean increased unavailability to users. Scheduling cube processing with Data Transformation Services (DTS) during off hours is one way to deal with this problem, however, and this is typically approached as an administrative task rather than a design issue.

There is also a point at which using pre-calculated aggregations can actually slow query performance, because it takes longer to retrieve the aggregations from disk storage than it does to calculate them on the fly from the underlying fact table data. A number of factors, such as the disk storage retrieval speed, processor speed, number of aggregations, density of data in the cube, influences the point at which the number of pre-calculated aggregations slow performance. Case-by-case experimentation can determine the optimum performance gain. Typically, the curve of performance gain and disk storage tends to flatten out after a performance gain of 70 to 80 percent. For example, reducing the performance gain on the Sales cube to 90 percent reduces the number of aggregations to 262, requiring only 319.477 MB of disk storage, and the sample query used earlier takes about 3.0 seconds to complete execution.

The storage mode of the partition the aggregation is associated with can also affect aggregation design and performance. Analysis Services compresses multidimensional storage, so aggregations stored in MOLAP or HOLAP partitions typically require less disk storage than ROLAP partitions, and ROLAP partitions are generally slower in retrieving aggregations than MOLAP partitions. Because ROLAP partitions do not store a copy of the source data, they should be used when archival storage is required to deal with historical queries, or if real-time OLAP is required. For more information about cube storage, see "Cube Storage" later in this chapter.

Design Guidelines

A balance between disk storage and processing time can be achieved with a few storage design guidelines.

• Know who uses the cube and why they use it. 

  Knowing the intended users and business requirements of a cube can help you determine the right balance of stored and on-the-fly aggregations. For example, if the intended users of a given cube are limited to a specific group and the cube is tailored to resolve a specific business case, then initial storage design should involve usage-based optimization. Design the cube without designing storage, so all aggregations are calculated on the fly, and allow your user base to access the cube for a predetermined period; at least a week is advised. Then use the Usage-Based Optimization Wizard to design storage based on the queries of the previous week. 

  If, on the other hand, the intended group of users is more open, or if the business requirements for the cube are loosely defined, the Storage Design Wizard can be used to set a low number of initial aggregations, typically using a 20 percent to 25 percent performance gain, then the Usage-Based Optimization Wizard can be used later to raise the number of stored aggregations as needed. 

• Know what data is used when. 

  Limit the number of pre-calculated aggregations stored for infrequently used dimensions. This is especially true for data in ROLAP partitions, because of the relatively large amount of disk storage required for such partitions. 

Measures

From an administrative point of view, cube measures provide instruction to the cube about how to aggregate fact table data. A new method of aggregation, DISTINCT COUNT, has been added to cube measures.

Distinct count measures, a new feature for Analysis Services, allow users to analyze many-to-many relationships between business entities. You should use distinct count measures with care, however; improper use can be disastrous for a cube.

When a distinct count measure is added to a cube, the entire cube is treated as if all measures are non-additive. This means that there is no way to directly infer the value of an aggregate member from the values of immediately subordinate lower level members. For example, a music store has 100 customers, some of whom buy more than one type of product, such as cassettes, albums, and compact discs. The following MDX query represents a typical query executed against a cube with a Customers and a Products dimension, with unit sales for each product and customer represented as a SUM aggregated measure and the distinct count of customers purchasing a particular product represented as a DISTINCT COUNT measure.

SELECT
{ [Unit Sales], [Distinct Customers] } ON COLUMNS,
[Products].MEMBERS ON ROWS
FROM SalesCube

The following table represents the results returned by such an MDX query.

Product Type	Unit Sales	Distinct Customers
All products	33025	100
Audio and video equipment	30900	90
CD players	6700	60
Cassette players	1100	40
DVD players	17500	55
VHS players	5600	50
Audio and video media	2125	50
CDs	600	45
DVDs	600	30
Cassettes	175	12
VHS videotapes	750	61

The Unit Sales column correctly aggregates from level to level; the All Products level is the sum of its two subordinate levels, Audio and video equipment and Audio and video media. Each of the subordinate levels, in turn, accurately represents the aggregate of their subordinate levels.

The Distinct Customers column, however, does not correctly aggregate from level to level, because customers can purchase more than one product. A single customer, for example, may have purchased both a CD player and a DVD player, which means that a single customer is represented in both the CD players and DVD players level. Hence, the subordinate levels cannot be added up to represent a distinct count of customers at higher levels. The All products level, for example, would usually add up to 140 if its two subordinate levels were added together, a clearly incorrect total as only 100 customers actually exist in the cube.

This example represents the root of the problem for cubes that depend upon aggregation of immediately subordinate members to supply information. For this reason, SQL Server Books Online recommends that each required distinct count measure be housed in its own cube. Virtual cubes can be used to link such non-additive cubes to regular cubes for correlation.

Certain features cannot be supported for cubes that use distinct count measures. Any functionality that requires a dynamically created member will fail, returning an error value for each affected cell. The features that are affected are:

• Visual totals, including the VisualTotals MDX function 

• Custom unary operators 

• Custom rollup formulas 

Design Guidelines

• Use the smallest data type possible for measures 

  Cubes perform best with measures that use 4-byte values, such as long integers. While measures support larger values, disk storage and retrieval performance is affected. Similarly, more memory is required in both server-side and client-side caches for 8-byte members, such as double precision numbers. 

Calculated Cells

Although calculated members are extremely powerful, they have a significant limitation. Calculated members must work on all members of a given level in the dimension in which they are created. There are elaborate, and unwieldy, ways of working around this, but they suffer from the requirement of data knowledge – the formula needs specifics about the data in order to function correctly.

Another limitation is that calculated members are not recursive; a calculated member can only be calculated once, and all calculated members are handled on the same calculation pass, based on their solve order.

Calculated cells, however, can handle specific subsets of cells within a cube, referred to as subcubes, and can be calculated iteratively in multiple calculation passes. Calculated cells can also be calculated after all calculated members are calculated, and can be applied selectively based on role security.

Design Guidelines

• Use only where absolutely necessary. 

  Calculated cells can duplicate other types of functionality in Analysis Services, such as calculated members or custom unary operators. Calculated cells, however, do not perform as well as these other features, because although the calculation condition is processed whenever the cube is processed, the calculation formula is calculated only when the cube is queried.

  You should use calculated cells only if the desired results cannot be efficiently achieved through the use of other features in Analysis Services. 

Actions

Actions provide a way to extend client applications by supplying standardized functionality, such as execution of supplemental programs or the display of HTML pages, without having to change a client application to supply such functionality.

This feature is specifically for client applications – the Analysis server simply makes actions available to client applications, but does not actually perform such actions. Hence, actions are useful only if a client application supports their display and execution. For example, the Cube Browser in Analysis Services fully supports the display and execution of actions, and can be used to test actions before release. The statement stored by the action is parsed by the MDX parser, so that dynamically constructed actions based on cube information can be created; the action type determines the behavior the client should perform using the parsed action statement.

Actions can be used in a variety of ways within client applications. For example, the underlying SQL Server 2000 dimension table for a dimension that represents retail items contains a column that stores the picture of the retail item. Instead of including the large binary column in the dimension as a member property, forcing a large amount of data into the server-side cache and significantly impacting performance, you can construct an action that, if selected from the client application, calls a dynamically constructed URL, using SQL-DMO, to access the picture. Not only does this prevent the server-side cache from filling up with picture data, but the action limits network impact by loading only picture information for the item on demand.

Design Guidelines

• Be aware of security interaction. 

  The availability of actions depends directly on the availability of the target to which the action is attached. For example, if an action is targeted for a level in a dimension that is secured through dimension security, only those users who can view the level will be able to use the action. Combined with dimension and cell security, actions can change the functionality of a client application for groups of users, an excellent way of handling multi-level functionality.

• Be aware of safety implications. 

  It is possible to create potentially damaging actions, especially for client applications that take advantage of general actions, such as command execution of HTML display. As an Analysis Services administrator, you should never create actions that execute unsafe or unknown applications, nor should you create URL actions to external Web pages unless you can verify the content of the Web page as safe. 

Named Sets

Named sets are a convenient way to construct subsets from a cube based on business requirements. Rather than having to copy the same large set expression in each MDX query that uses it, the set can be constructed once, named, and referred to directly in MDX queries. There is no appreciable impact on performance when using named sets.

One benefit of using named sets in MDX queries is the ability to provide an abstract reference to a specified set. The set itself can be changed to reflect changes in the structure of the cube, without necessarily having to change MDX queries that use the named set. This level of abstraction can be used for a variety of business requirements, such as sets of products or inventory, groups of accounts, and so on.

Design Guidelines

• Use wherever possible. 

  The use of named sets to replace lengthy, commonly used set expressions is highly recommended. There is no appreciable performance impact one way or another, and this feature makes administration of Analysis servers easier by providing a useful level of abstraction for client applications and MDX queries in general. 

Cell Security

Cell security is designed to complement dimension security as it applies to a cube. While dimension security is extremely flexible, the granularity stops at a single member. In a cube, that can affect quite a few cells. The granularity for cell security, on the other hand, stops at a single cell; you have very fine control over who sees what in a given cube.

The different cell security permissions can be difficult to understand and effectively implement, though. An excellent discussion on cell security can be found in the white paper, "Microsoft SQL Server OLAP Services Cell-Level Security," posted on the MSDN® Online 1050403314Library. You should become familiar not just with cell security, but the interaction of dimension security and cell security. In particular, focus on the order of evaluation of both dimension and cell security, as it can be the primary cause for confusion when using client applications to access secured cubes and dimensions.

Design Guidelines

• Do not use as a replacement for dimension security. 

  You should regard dimension security as a broad brush: General security requirements can be enforced as part of a blanket security policy for all cubes. Cell security should be viewed as a fine brush that provides you with an additional mechanism to fine-tune security for a particular cube, and is designed to complement dimension security. Because of all of the steps needed to check cell security, query performance is adversely affected. You should use dimension security in preference to cell security, and use cell security only where appropriate. 

Cube Storage and Processing

Unlike dimensions, the storage and processing requirements for cubes are influenced by a number of factors.

Cube Storage

Cube storage is handled by the partitions associated with a cube, which store the aggregations associated with a cube. Fore information about partition design and processing, and about aggregations, see "Partitions" and "Aggregations" earlier in this chapter.

Cube Processing

Several additional factors can affect the processing speed of cubes. Measure size, for example, can make a difference in very large cubes.

Schema Optimization

When a cube is processed, a number of SQL queries are executed against the underlying relational data to retrieve fact and dimension data. These queries are automatically constructed using basic rules stored in the meta data of the cube.

The cube uses the dimension table information, such as the Source Table and Member Key Column properties, to construct joins between the fact table and involved dimension tables. While this information practically guarantees that the safest query possible will be executed to retrieve the appropriate fact table data, it does not guarantee that the fastest query possible will be constructed.

For example, the SQL SELECT statement constructed by Analysis Services for the first partition of the Warehouse cube in the FoodMart 2000 database resembles the following:

SELECT
store.store_id, 
time_by_day.quarter, 
time_by_day.month_of_year, 
warehouse.warehouse_id, 
product.product_id, 
inventory_fact_1997.store_invoice, 
inventory_fact_1997.supply_time, 
inventory_fact_1997.warehouse_cost, 
inventory_fact_1997.warehouse_sales, 
inventory_fact_1997.units_shipped, 
inventory_fact_1997.units_ordered, 
inventory_fact_1997.warehouse_sales-inventory_fact_1997.warehouse_Cost 
FROM 
inventory_fact_1997, 
time_by_day, 
store, 
warehouse, 
product 
WHERE 
(time_by_day.the_year=?) AND
(inventory_fact_1997.time_id=time_by_day.time_id) AND
(inventory_fact_1997.store_id=store.store_id) AND 
(inventory_fact_1997.warehouse_id=warehouse.warehouse_id) AND
(inventory_fact_1997.product_id=product.product_id)

While this SQL accurately represents the data used by the first partition of the Warehouse cube, it is not necessarily the most efficient way of retrieving such data. The Cube Editor offers a menu option for schema optimization, Optimize Schema, for dimensions that meet the following criteria:

• The values in the member key column for the dimension are unique. 

• A one-to-one relationship exists between the member key column and the member name column. 

• There is only one join between the underlying fact table for the partition and the dimension tables of the dimension, based on the member key column. 

Of the four dimensions represented in the WHERE clause, Time, Store, Product, and Customer, the last three dimensions meet the criteria needed to optimize the schema. The joins to those three dimension tables are unnecessary, because the information needed to identify the member key columns is contained within the fact table.

If the Optimize Schema command is executed on the Warehouse cube in FoodMart 2000, the following message appears.

The following MemberKeyColumn properties have been updated:
Level: Store Name: "store"."store_id" -> "inventory_fact_1997"."store_id"
Level: Product Name: "product"."product_id"->"inventory_fact_1997"."product_id"
Level: Warehouse Name: "warehouse"."warehouse_id"-> "inventory_fact_1997"."customer_id"

The Optimize Schema command replaced the references from the original member key columns in the last three dimensions to the corresponding foreign key columns in the fact table. When the Warehouse cube is processed, the SQL SELECT statement used to retrieve fact table data for the first partition now resembles the following:

SELECT
inventory_fact_1997.store_id, 
time_by_day.quarter, 
time_by_day.month_of_year, 
inventory_fact_1997.warehouse_id, 
inventory_fact_1997.product_id, 
inventory_fact_1997.store_invoice, 
inventory_fact_1997.supply_time, 
inventory_fact_1997.warehouse_cost, 
inventory_fact_1997.warehouse_sales, 
inventory_fact_1997.units_shipped, 
inventory_fact_1997.units_ordered, 
inventory_fact_1997.warehouse_sales-inventory_fact_1997.warehouse_Cost 
FROM 
inventory_fact_1997, 
time_by_day 
WHERE 
(time_by_day.the_year=?) AND 
(inventory_fact_1997.time_id=time_by_day.time_id)

The resulting SQL SELECT statement now contains only one join in the WHERE clause, offering a significant performance increase without disturbing the information retrieved from the underlying fact table.

When designing cubes optimized for processing performance, remember that private and virtual dimensions cannot be optimized. While this does not greatly affect the performance of virtual dimensions, private dimensions should be used only if necessary to satisfy a business requirement for a particular cube. For more information on private dimensions, see "Designing Dimensions" earlier in this chapter.

Measure Size

The data size of cube measures has a direct impact on cube processing. Use the smallest possible data type to represent a cube measure. The server does not check for overflow when coercing data types, however, so check the underlying fact table data before attempting to coerce measures to a smaller data type.

Design Guidelines

• Use Optimize Schema whenever possible. 

  As mentioned earlier, schema optimization can give a great performance boost when querying and processing a cube. The Optimize Schema menu item in the Tools menu of the Cube Editor handles the schema analysis process automatically, once selected, but you must initiate the process for it to happen. 

  To take advantage of schema optimization, dimensions must meet certain requirements, most of which are based on the uniqueness of member keys. For more information on the requirements of schema optimization, see "Schema Optimization" earlier in this chapter. 

• Use only the processing option needed to get the job done. 

  Full processing of cubes is not always required; be aware of the differences in processing options, and use only the appropriate processing option. Full processing takes much more time, and renders the cube unavailable longer, than other processing options, so use it only when necessary. 

1 Alexander Berger and Ashvini Sharma, OLAP Services: Performance Implications of the Architecture. (Microsoft Corporation, June 1999)