SQL Server Analysis Services 2012 Cube Development Cookbook

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.

SQL Server Analysis Services 2012 Cube Development Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

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

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

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

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

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

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

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

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

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

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

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

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

Customer Reviews

5 star

4 star

3 star

2 star

1 star

About the Authors

Baya Dewald has been working with Microsoft SQL Server for over 15 years and with Analysis Services since its debut in 1999. He has architected, developed, and administered databases with millions of users and cubes measured in terabytes. Throughout his career, he has held various certifications, including Microsoft Certified Solution Developer, Microsoft Certified Systems Engineer, and Microsoft Certified Database Administrator. He has managed DBA teams and published over 100 technical articles in SQL Server Magazine, SQL Professional, Tech Target, and InformIT. He has also contributed to several technical books as a co-author and technical reviewer. He can be reached at <[email protected]>.

Paul Turley is a mentor with SolidQ and is a Microsoft SQL Server MVP. He consults, writes, speaks, teaches, and blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. He is the director of the Oregon SQL PASS chapter and user group, and the lead author of Professional SQL Server 2012 Reporting Services and 11 other books by Wrox and Microsoft Press. Paul blogs at SqlServerBiBlog.com.

Steve Hughes is a Practice Lead at Magenic. In his current role, he develops strategy and helps guide data, business intelligence, collaboration, and data integration development using Microsoft technologies, including SQL Server, SharePoint, and BizTalk. He continues to deliver data and business intelligence solutions using these platforms. He has been working with technology for over 15 years with much of that time spent on creating business intelligence solutions. He is passionate about using data effectively and helping customers understand that data is valuable and profitable. He also has experience working with a large software company as a service vendor and there received awards for customer satisfaction and engineering excellence.

In 2010, he received the Consultant of the Year honor at Magenic. He has delivered multiple presentations on SQL server, data architecture, and business intelligence over the years. Steve can often be found at Professional Association for SQL Server (PASS) events where he serves as a regional mentor and is active with the Minnesota SQL Server User Group (MNPASS). He shares his insights from the field on his blog at http://dataonwheels.wordpress.com.

I want to thank my wife, Sheila, and our four children—Kristyna, Alex, Andrew, and Mikayla—for their patience as I worked on this project. Their support has been awesome in this and my other endeavors. I also want to thank the team at Packt Publishing and the reviewers for their guidance throughout the writing of this book as it has allowed us to put together a better product for all of you. Finally, I dedicate all my efforts to my father-in-law, Ed Jankowski, without whom I would have never been involved in software at all. We all  miss you, Ed.

SQL Server Analysis Services 2012 Cube Development Cookbook

SQL Server Analysis Services 2012 Cube Development Cookbook

Overview of this book

Related Content you might be interested in

Current Title:

SQL Server Analysis Services 2012 Cube Development Cookbook

About the Authors