SQL Server^® Analysis Services: Hands-On

Building and Modifying an OLAP Cube

Designing a Unified Dimension Model (UDM)

• Identifying measures and their suitable granularities
• Adding new measure groups and creating custom measures

Creating dimensions

• Implementing a Star and Snowflake Schema
• Identifying role-play dimensions
• Adding dimension attributes and properties
• Configuring multilanguage support

Extending the Cube with Hierarchies

Creating hierarchies

• Building natural hierarchies and creating attribute relationships
• Distinguishing between ragged, balanced and unbalanced hierarchies
• Discretising attribute values with the Clusters and Equal Areas algorithms

Parent-child relationships

• Defining parent and key attributes
• Generating level captions with the Naming Template feature
• Removing repeated entries with the MembersWithData property

Exploiting Advanced Dimension Relationships

Storing dimension data in fact tables

• Building a degenerate dimension
• Configuring fact relationships

Saving space with referenced dimension relationships

• Identifying candidates for referenced relationships
• Utilising the Dimension Usage tab to configure referenced relationships

Including dimensions with many-to-many relationships

• Implementing intermediate measure groups and dimensions
• Reporting on many-to-many dimensions without double counting

Designing Optimal Cubes

Assembling cube components

• Selecting the appropriate fact tables
• Adding cube dimensions
• Distinguishing between additive, semiadditive and nonadditive measures
• Simplifying cubes with perspectives

Managing Cubes

Designing storage and aggregations

• Choosing between ROLAP, MOLAP and HOLAP
• Partitioning cubes for improved performance
• Designing aggregations with the Aggregation Design Wizard
• Leveraging the Usage-Based Optimisation Wizard

Automating processing and deployment

• Exploiting XMLA scripts and SSIS
• Refreshing cubes with Proactive Caching
• Deploying cubes easily through the enterprise

Performing Advanced Analysis with MDX

Retrieving data with MDX

• Defining tuples, sets and calculated members
• Querying cubes with MDX
• Utilising set functions

Monitoring business performance with KPIs

• Building goal, status and trend expressions
• Using PARALLELPERIOD to compare with past time periods
• Simplifying KPI definitions using the KPIValue and KPIGoal functions

Enhancing cubes with MDX

• Adding runtime calculations to the cube
• Comparing MDX calculations with DSV calculated columns
• Adding drill-through and URL actions

Gaining Business Advantage with Data Mining

Determining the correct model

• Identifying business tasks for data mining
• Training and testing data mining algorithms
• Comparing algorithms with the accuracy chart and classification matrix
• Optimising returns with the Profit Chart

Performing real-world predictions

• Classifying with the Decision Trees, Neural Network and Naive Bayes algorithms
• Predicting with the Time Series algorithm

Deploying models

• Predicting new cases with algorithms
• Utilising DMX to perform batch and singleton predictions
• Exploring results with data mining viewers