Expert Cube Development with SQL Server Analysis Services 2012 Multidimensional电子书

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