Book Image

SQL Server Analysis Services 2012 Cube Development Cookbook

Book Image

SQL Server Analysis Services 2012 Cube Development Cookbook

Overview of this book

Microsoft SQL Server is a relational database management system. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications. SQL Server Analysis Services adds OLAP and data mining capabilities for SQL Server databases. OLAP (online analytical processing) is a technique for analyzing business data for effective business intelligence. This practical guide teaches you how to build business intelligence solutions using Microsoft’s core product – SQL Server Analysis Services. The book covers the traditional multi-dimensional model which has been around for over a decade as well as the tabular model introduced with SQL Server 2012. Starting with comparing MultiDimensional and tabular models – discussing the values and limitations of each, you will then cover the essential techniques for building dimensions and cubes. Following on from this, you will be introduced to more advanced topics, such as designing partitions and aggregations, implementing security, and synchronizing databases for solutions serving many users. The book also covers administrative material, such as database backups, server configuration options, and monitoring and tuning performance. We also provide a primer on MultiDimensional eXpressions (MDX) as well as Data Analysis expressions (DAX) languages. This book provides you with data cube development techniques, and also the ongoing monitoring and tuning for Analysis Services.

Related Content you might be interested in

Current Title:

Small book image
SQL Server Analysis Services 2012 Cube Development Cookbook
Dec, 2013 | 340 pages
No titles found
Table of Contents (19 chapters)
SQL Server Analysis Services 2012 Cube Development Cookbook
SQL Server Analysis Services 2012 Cube Development Cookbook
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Introduction to Multidimensional Data Model Design
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
Defining Analysis Services Dimensions
Introduction
Defining data sources
Defining data source views
Defining entity relationships in DSV
Extending data source views
Creating named calculations and queries
Creating simple dimensions
Building dimension hierarchies
Setting essential attribute properties
Browsing dimension data
Sorting the attributes
Customizing advanced attribute properties
Creating parent-child dimensions
Creating the date and time dimensions
3
Creating Analysis Services Cubes
Creating Analysis Services Cubes
Introduction
Defining measure groups and measures
Setting measure properties
Browsing the cube data
Dimension usage with measure group
Examining cube file structures
Partitioning strategies
Defining partition slice
Merging partitions
Defining aggregation designs
Distinct count measure groups
Enabling write-back feature
Deployment options
4
Extending and Customizing Cubes
Extending and Customizing Cubes
Introduction
Defining calculated measures
Defining named sets
Defining drillthrough actions
Defining URL actions
Defining reporting actions
Defining key performance indicators
Defining perspectives
Defining translations
Defining measure expressions
5
Optimizing Dimension and Cube Processing
Optimizing Dimension and Cube Processing
Introduction
Understanding dimension processing options
Learning about basic dimension processing
Learning advanced dimension processing options
Using out-of-line bindings for dimension processing
Dealing with partition processing options
Using SQL Server Integration Services to process Analysis Services objects
Monitoring and tuning processing performance
6
MDX
MDX
Introduction
Returning data on the query axes
Limiting the query output
Sorting the query output
Defining query level calculations and named sets
Navigating dimension hierarchies
Working with the Time dimensions
MDX script's functionality
Monitoring and tuning MDX queries
7
Analysis Services Security
Analysis Services Security
Introduction
Managing instance-level administrative security
Managing database-level security
Managing cube-level security
Managing dimension hierarchy-level security
Implementing dynamic dimension security
Implementing cell-level security
8
Administering and Monitoring Analysis Services
Administering and Monitoring Analysis Services
Introduction
SSAS instance configuration options
Creating and dropping databases
Monitoring SSAS instance using Activity Viewer
Monitoring SSAS instance using DMVs
Cancelling a session
Checking whether cubes are accessible
Checking SSAS object sizes programmatically
Backup and restore
Synchronizing databases
Detaching and attaching databases
9
Using Tabular Models
Using Tabular Models
Introduction
Creating a Tabular Model
Working with data sources and loading data
Modeling the data
Creating a hierarchy
Creating a calculated measure
Creating a calculated column
Creating a KPI
Analyzing your model in Excel
Deploying Tabular Models
Scripting Tabular Models using XMLA
Processing Tabular Models
Partitioning Tabular Models
Implementing perspectives
Implementing security in Tabular Models
Automating Tabular Model processing
10
DAX Calculations and Queries
DAX Calculations and Queries
Introduction
Combining tables using calculated columns
Adding a calculated column
Creating measures
Testing a Tabular Model in Excel
Using the CALCULATE function
Querying a Tabular Model
11
Performance Tuning and Troubleshooting Tabular Models
Performance Tuning and Troubleshooting Tabular Models
Introduction
Understanding usability limits
Optimizing and managing a model's design
Diagnosing performance issues
Using performance tools
Investigating query performance with SQL Server Profiler
Miscellaneous Analysis Services Topics
Miscellaneous Analysis Services Topics
Working with non-SQL Server data sources
Common yet confusing SSAS errors
Dimension properties
Performance considerations for many-to-many dimension relationships
DirectQuery with Tabular Models
Index
Index

Monitoring and tuning MDX queries

The best monitoring tool for MDX queries is SQL Server Profiler, which you could launch by navigating to Start | All Programs | SQL Server 2012 | Performance Tools | SQL Server Profiler. Profiler records various phases of the query execution, including the retrieval of data from partitions or dimensions, querying aggregations or memory cache as well as displaying the results to the application. Based on the Profiler output, we can surmise whether SSAS spends most of its execution time retrieving data from the storage engine or in the formula engine, meaning deriving calculated values after the data retrieval is complete.

How to do it...

Follow these steps for monitoring and tuning MDX queries.

  1. Once the Profiler is open, go to File | New Trace and connect to your Analysis Services instance. Profiler allows choosing numerous events and columns that you could include in your trace. Ensure that the Show all Events checkbox is checked, then choose the following...

Previous Section
End of Chapter 6
Next Chapter