![]()
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 perf...
