Expert Cube Development With SSAS Multidimensional Models by Webb, Chris -- Read -- Imperial Library of Trantor

Index

Expert Cube Development with SSAS Multidimensional Models

Table of Contents Expert Cube Development with SSAS Multidimensional Models Credits About the Authors About the Reviewers www.PacktPub.com

Support files, eBooks, discount offers and more

Why subscribe? Free access for Packt account holders Instant updates on new Packt books

Preface

What this book covers What you need for this book Who this book is for Conventions Reader feedback Customer support

Downloading the example code and database for the book Downloading the color images of this book Errata Piracy Questions

1. Designing the Data Warehouse for Analysis Services

The source database

The OLTP database The data warehouse The data mart

Data modeling for Analysis Services

Fact tables and dimension tables Star schemas and snowflake schemas Junk dimensions Degenerate dimensions Slowly Changing Dimensions Bridge tables or factless fact tables Snapshot and transaction fact tables Updating fact and dimension tables Natural and surrogate keys Unknown Members, key errors, and NULLability

Physical database design for Analysis Services

Multiple data sources Data types and Analysis Services SQL queries generated during cube processing

Dimension processing Dimensions with joined tables Reference dimensions Fact dimensions Distinct Count measures Indexes in the data mart

Dimension tables Fact tables

Usage of schemas Naming conventions Views versus the Data Source View

Summary

2. Building Basic Dimensions and Cubes

Multidimensional and Tabular models Choosing an edition of Analysis Services Setting up a new Analysis Services project Creating data sources Creating Data Source Views Designing simple dimensions

Using the New Dimension wizard Using the Dimension Editor

Adding new attributes Configuring a Time dimension Creating user hierarchies Configuring attribute relationships

Building a simple cube

Using the New Cube wizard Project deployment Database processing

Summary

3. Designing More Complex Dimensions

Grouping and banding

Grouping Banding

Modeling Slowly Changing Dimensions

Type I SCDs Type II SCDs

Modeling attribute relationships on a Type II SCD Handling member status

Type III SCDs

Modeling junk dimensions Modeling ragged hierarchies

Modeling parent/child hierarchies Ragged hierarchies with HideMemberIf

Summary

4. Measures and Measure Groups

Measures and aggregation

Useful properties of measures

FormatString DisplayFolders

Built-in measure aggregation types

Basic aggregation types DistinctCount None Semi-additive aggregation types ByAccount

Dimension calculations

Unary operators and weights Custom Member Formulas

Non-aggregatable values

Measure groups

Creating multiple measure groups Creating measure groups from dimension tables

MDX formulas versus pre-calculating values

Handling different dimensionality Handling different granularities Non-aggregatable measures – a different approach Using linked dimensions and measure groups Role-playing dimensions

Dimension/measure group relationships

Fact relationships Referenced relationships Data mining relationships

Summary

5. Handling Transactional-Level Data

Details about transactional data Drillthrough

Actions Drillthrough actions

Drillthrough columns order Drillthrough and calculated members

Drillthrough modeling

Drillthrough using a transaction detail dimension Drillthrough with ROLAP dimensions Drillthrough on alternate fact table Drillthrough recap

Many-to-many dimension relationships

Implementing a many-to-many dimension relationship Advanced modeling with many-to-many relationships Performance issues

Summary

6. Adding Calculations to the Cube

Different kinds of calculated members Common calculations

Simple calculations Referencing cell values Aggregating members Year-to-date calculations Ratios over a hierarchy Previous period growths Same period previous year Moving averages Ranks Formatting calculated measures

Calculation dimensions

Implementing a simple calculation dimension The Time Intelligence wizard

Attribute overwrite Limitations of calculated members

Calculation dimension best practices

Named sets

Regular named sets Dynamic named sets

Summary

7. Adding Currency Conversion

Introduction to currency conversion

Data collected in a single currency Data collected in a multiple currencies

Where to perform currency conversion The Add Business Intelligence wizard

Concepts and prerequisites How to use the Add Business Intelligence wizard

Data collected in a single currency with reporting in multiple currencies Data collected in multiple currencies with reporting in a single currency Data stored in multiple currencies with reporting in multiple currencies

Measure expressions

DirectSlice property Writeback

Summary

8. Query Performance Tuning

Understanding how Analysis Services processes queries Performance tuning methodology Designing for performance Performance-specific design features

Partitions

Why partition? Building partitions Planning a partitioning strategy Unexpected Partition scans

Aggregations

Creating an initial aggregation design Usage-Based Optimization Monitoring partition and aggregation usage Building aggregations manually Common aggregation design issues

MDX calculation performance

Diagnosing Formula Engine performance problems Calculation performance tuning

Tuning algorithms used in MDX

Using Named Sets to avoid recalculating Set Expressions

Using calculated members to cache numeric values Tuning the implementation of MDX

Caching

Formula cache scopes Other scenarios that restrict caching Cache warming

The CREATE CACHE statement Running batches of queries

Scale-up and Scale-out Summary

9. Securing the Cube

Sample security requirements Analysis Services security features

Roles and role membership Securable objects Creating roles Membership of multiple roles Testing roles

Administrative security Data security

Granting Read Access to Cubes Cell security Dimension security

Visual Totals Restricting access to Dimension Members Applying security to Measures

Dynamic security

Dynamic dimension security Dynamic security with stored procedures

Dimension security and parent/child hierarchies

Dynamic cell security Accessing Analysis Services from outside a domain Managing security Security and query performance

Cell security Dimension security Dynamic security

Summary

10. Going in Production

Making changes to a cube in production Managing partitions

Relational versus Analysis Services partitioning Building a template partition Generating partitions in Integration Services

Managing processing

Dimension processing Partition processing Lazy Aggregations Processing reference dimensions Handling processing errors Managing processing with Integration Services Push-mode processing Proactive caching

SSAS Data Directory maintenance Performing database backup Copying databases between servers Summary

11. Monitoring Cube Performance and Usage

Analysis Services and the operating system

Resources shared by the operating system

CPU Memory I/O Operations

Tools to monitor resource consumption

Windows Task Manager Performance counters Resource Monitor

Analysis Services memory management

Memory differences between 32 bit and 64 bit Controlling the Analysis Services Memory Manager Out of memory conditions in Analysis Services

Sharing SQL Server and Analysis Services on the same machine

Monitoring processing performance

Monitoring processing with trace data

SQL Server Profiler ASTrace XMLA Flight Recorder

Monitoring processing with Performance Monitor counters Monitoring processing with Dynamic Management Views

Monitoring query performance

Monitoring queries with trace data Monitoring queries with Performance Monitor counters Monitoring queries with Dynamic Management Views

Monitoring usage

Monitoring usage with trace data Monitoring usage with Performance Monitor counters Monitoring usage with Dynamic Management Views Activity Viewer

Building a complete monitoring solution Summary

A. DAX Query Support

Implementation details

Mapping Multidimensional objects to Tabular concepts Unsupported features New functionality in Analysis Services

Connecting Power View to a Multidimensional model Running DAX queries against a Multidimensional model

Executing DAX queries DAX queries and attributes

Index

← Prev
Back
Next →

← Prev
Back
Next →