Expert Cube Development with SSAS Multidimensional Models
eBook - ePub

Expert Cube Development with SSAS Multidimensional Models

Chris Webb, Alberto Ferrari, Marco Russo

Share book
  1. 402 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

Expert Cube Development with SSAS Multidimensional Models

Chris Webb, Alberto Ferrari, Marco Russo

Book details
Book preview
Table of contents
Citations

About This Book

In Detail

Microsoft's SQL Server Analysis Services 2012 Multidimensional is an OLAP server that allows end users to analyze large amounts of business data quickly and easily, using a variety of tools including Excel, and to create their own reports without IT involvement. It is part of the Microsoft SQL Server suite of tools and has been used widely on Business Intelligence projects for the last 15 years; it is now the most commonly used OLAP server in the world.

Expert Cube Development with SSAS Multidimensional Models will teach you tips and tricks for cube design from some of the most experienced Analysis Services experts in the world. Topics covered include designing dimensions and cubes, implementing common MDX calculations, security and performance tuning.

The book takes you through the whole lifecycle of Analysis Services cube development, from how data warehouse design affects your cube, through cube and dimension design to performance tuning and monitoring. Some previous experience of Analysis Services is assumed the focus is on best practices and design patterns.

The book starts off with a discussion of data warehouse design and how it relates to Analysis Services. Following on from that, best practices relating to Analysis Services cube and dimension design are covered, including topics such as optimizing dimension attribute relationships and handling fact tables that contain data at different granularities.

Next, the book looks at using MDX to implement common business calculations, and then moves on to more advanced problems like currency conversion calculations and creating a date tool dimensions.

Finally security, performance tuning, automation of administrative tasks and monitoring usage and performance each get their own chapter and subject such as dynamic security roles, using Profiler to diagnose query performance problems and automating partition creation are also dealt with.

Approach

An easy-to-follow guide full of hands on examples of real-world Analysis Services cube development tasks. Each topic is explained and placed in context, and for the more inquisitive reader, there also more in-depth details of the concepts used.

Who this book is for

If you are an Analysis Services cube designer wishing to learn more advanced topic and best practices for cube design, this book is for you.You are expected to have some prior experience with Analysis Services cube development.

Frequently asked questions

How do I cancel my subscription?
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is Expert Cube Development with SSAS Multidimensional Models an online PDF/ePUB?
Yes, you can access Expert Cube Development with SSAS Multidimensional Models by Chris Webb, Alberto Ferrari, Marco Russo in PDF and/or ePUB format, as well as other popular books in Computer Science & Programming in SQL. We have over one million books available in our catalogue for you to explore.

Information

Year
2014
ISBN
9781849689908
Edition
1

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...

Table of contents