SQL Server Analysis Services 2012 Cube Development Cookbook电子书

SQL Server Analysis Services 2012 Cube Development Cookbook

Table of Contents

SQL Server Analysis Services 2012 Cube Development Cookbook

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

Errata

Piracy

Questions

1. Introduction to Multidimensional Data Model Design

Introduction

The business value of Business Intelligence

Challenges and barriers of effective BI

Overcoming BI challenges and barriers

Choosing multidimensional or Tabular Models

Star- or Snowflake-relational schema

A sample scenario for choosing the Snowflake schema

2. Defining Analysis Services Dimensions

Introduction

Defining data sources

How to do it...

How it works...

There's more...

Defining data source views

Getting started

How to do it...

Defining entity relationships in DSV

How to do it...

Extending data source views

How to do it...

Creating named calculations and queries

How to do it...

Creating simple dimensions

Getting ready

How to do it...

How it works...

Building dimension hierarchies

How to do it...

There's more

Setting dimension properties

Setting essential attribute properties

How to do it...

Browsing dimension data

Getting ready

How to do it...

How it works...

Sorting the attributes

How to do it...

Customizing advanced attribute properties

How to do it...

How it works...

Creating parent-child dimensions

How to do it...

How it works...

There's more...

Creating the date and time dimensions

How to do it...

There's more...

3. Creating Analysis Services Cubes

Introduction

Defining measure groups and measures

Getting ready

How to do it...

Setting measure properties

How to do it...

There's more...

Browsing the cube data

How to do it...

Dimension usage with measure group

How to do it...

Examining cube file structures

How to do it...

There's more...

Partitioning strategies

How to do it...

Creating partitions through the wizard

Creating partitions through AMO

Partition storage mode options

Defining partition slice

How to do it...

Merging partitions

How to do it...

Defining aggregation designs

Using BIDS Helper for customizing aggregations

How to do it...

There's more...

Distinct count measure groups

How to do it...

There's more...

Enabling write-back feature

How to do it...

Deployment options

How to do it...

4. Extending and Customizing Cubes

Introduction

Defining calculated measures

Getting ready

How to do it...

Defining named sets

How to do it...

Defining drillthrough actions

How to do it...

How it works...

Defining URL actions

How to do it...

Defining reporting actions

How to do it...

Defining key performance indicators

How to do it...

Defining perspectives

How to do it...

Defining translations

How to do it...

Defining measure expressions

How to do it...

5. Optimizing Dimension and Cube Processing

Introduction

Understanding dimension processing options

How it works...

Learning about basic dimension processing

Getting ready

How to do it...

How it works...

Learning advanced dimension processing options

How to do it...

Using out-of-line bindings for dimension processing

How to do it...

Dealing with partition processing options

How to do it...

How it works...

Using SQL Server Integration Services to process Analysis Services objects

How to do it...

Monitoring and tuning processing performance

How to do it...

6. MDX

Introduction

Returning data on the query axes

Getting ready

How to do it...

Limiting the query output

How to do it...

Sorting the query output

How to do it...

Defining query level calculations and named sets

How to do it...

Navigating dimension hierarchies

How to do it...

Working with the Time dimensions

How to do it...

MDX script's functionality

How to do it...

Monitoring and tuning MDX queries

How to do it...

There's more...

7. Analysis Services Security

Introduction

Managing instance-level administrative security

How to do it...

Managing database-level security

How to do it...

Managing cube-level security

How to do it...

Managing dimension hierarchy-level security

How to do it...

How it works...

Implementing dynamic dimension security

How to do it...

There's more...

Implementing cell-level security

How to do it...

There's more...

8. Administering and Monitoring Analysis Services

Introduction

SSAS instance configuration options

How to do it...

How it works...

There's more...

Creating and dropping databases

Getting ready

How to do it...

Monitoring SSAS instance using Activity Viewer

How to do it...

How it works...

Monitoring SSAS instance using DMVs

How to do it...

How it works...

There's more...

Cancelling a session

How to do it...

Checking whether cubes are accessible

How to do it...

Checking SSAS object sizes programmatically

How to do it...

There's more...

Scaling out SSAS solutions

Backup and restore

How to do it...

How it works...

Synchronizing databases

How to do it...

How it works...

Detaching and attaching databases

How to do it...

How it works...

9. Using Tabular Models

Introduction

Creating a Tabular Model

Getting ready

How to do it…

There's more…

Working with data sources and loading data

How to do it…

There's more…

Modeling the data

Getting ready

How to do it…

How it works…

There's more…

See also

Creating a hierarchy

How to do it…

There's more…

Creating a calculated measure

Getting ready

How to do it…

See also

Creating a calculated column

How to do it…

There's more…

See also

Creating a KPI

Getting ready

How to do it…

There's more…

See also

Analyzing your model in Excel

How to do it…

There's more…

See also

Deploying Tabular Models

Getting ready

How to do it…

How it works…

There's more…

See also

Scripting Tabular Models using XMLA

Getting ready

How to do it…

There's more…

Processing Tabular Models

How to do it…

How it works…

There's more…

See also

Partitioning Tabular Models

Getting ready

How to do it…

There's more…

See also

Implementing perspectives

Getting ready

How to do it…

There's more…

See also

Implementing security in Tabular Models

Getting ready

How to do it…

How it works…

There's more…

Automating Tabular Model processing

Getting ready

How to do it…

There's more…

See also

10. DAX Calculations and Queries

Introduction

Combining tables using calculated columns

Getting ready

How to do it…

There's more…

Adding a calculated column

Getting ready

How to do it…

Creating measures

Getting ready

How to do it…

Creating AutoSum measures

Creating calculated measures

Testing a Tabular Model in Excel

How to do it…

Understanding Row Context

Understanding Filter Context

Using the CALCULATE function

How to do it…

Test the Measures in Excel

Working with Time Intelligence

Querying a Tabular Model

Getting ready

How to do it…

There's more…

See also

11. Performance Tuning and Troubleshooting Tabular Models

Introduction

Understanding usability limits

Getting ready

How to do it…

There's more…

Optimizing and managing a model's design

Managing memory usage

Diagnosing performance issues

Processing and query-related performance

Using performance tools

Task Manager and Resource Monitor

Dynamic Management Views

BISM server memory report

Investigating query performance with SQL Server Profiler

How to do it…

There's more…

See also

A. Miscellaneous Analysis Services Topics

Working with non-SQL Server data sources

SSAS data structures

Transaction isolation levels

Processing performance issues

Common yet confusing SSAS errors

Binding is too small

Attribute key cannot be found

Undefined column name

DSV does not contain definition for an xyz column

Operation is cancelled

File is corrupted

Error encountered in the transport layer

Internal error or unexpected exception

Deadlock

Dimension properties

See also

Performance considerations for many-to-many dimension relationships

DirectQuery with Tabular Models

Data sources

Security

Design limitations

Client restrictions

Index