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

Navigating dimension hierarchies

Earlier in this chapter you learned how to use the CurrentMember function to retrieve the name of the current hierarchy member. MDX allows browsing hierarchies easily using similar functions, PrevMember and NextMember, which are extremely useful for trend analysis. Additionally, you can obtain hierarchy members using relative functions, such as children, ancestors, descendants, and parent, to build sets based on the members of interest. This section will list examples where these functions are particularly beneficial.

How to do it...

Let's get started with navigating dimension hierarchies.

  1. To implement the [Year-over-Year Growth in Reseller Sales Amount] calculated measure, open the sample Adventure Works 2012 database in SQL Server Data Tools (SSDT), navigate to Adventure Works cube's Calculations tab, and enter the expression that will follow. Note that we have two nested IIF functions. The first IIF function uses an ordinal function to determine whether we...

Previous Section
End of Section 7
Next Section