Getting Started with SQL Server 2012 Cube Development

By Simon Lidberg

As a practical tutorial for Analysis Services, get started with developing cubes. "Getting Started with SQL Server 2012 Cube Development" walks you through the basics, working with SSAS to build cubes and get them up and running. Written for SQL Server developers who have not previously worked with Analysis Services. It is assumed that you have experience with relational databases, but no prior knowledge of cube development is required. You need SQL Server 2012 in order to follow along with the exercises in this book.

• Language: English
• Publisher: Packt Publishing
• Release date: Sep 13, 2013
• ISBN: 9781849689519

Book preview

Table of Contents

Getting Started with SQL Server 2012 Cube Development

Credits

About the Author

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. Self-service Business Intelligence, Creating Value from Data

Identifying common user requirements for a BI system

Creating a flexible reporting solution

Getting user value through self-service reporting

Summary

2. Installing SSAS and Preparing for Cube Development

Understanding the BI semantic model architecture

Choosing the deployment mode

Actions

Aggregations

Custom assemblies

Custom rollups

Distinct count

Linked objects

Many-to-many relationships

Parent-child hierarchies

Translations

Writeback

Tool support

Installing Analysis Services 2012 in multidimensional mode

Hands-on steps for installing Analysis Services

Hands-on steps for attaching the sample database

Starting SQL Server Data Tools for the first time

Choosing the correct project

Hands-on steps for creating your first cube project

Navigating the project environment

Solution Explorer

Properties window

Build menu

Hands-on steps for configuring your cube project

Summary

3. Creating Your First Multidimensional Cube

An introduction to data warehousing

Understanding data sources

Creating the data connection

Understanding the data source view

Creating a new data source view

Adding objects to the data source view

Extending the data source view

Understanding dimensions

Creating the Date dimension

Preparing the Date dimension for end users

Renaming attributes

Creating a hierarchy and attribute relationships

Processing the dimension to review the results

Creating the first cube

Creating the Fact Internet Sales cube

Creating additional dimensions and adding them to the cube

Summary

4. Deploying and Processing Cubes

Deploying objects to Analysis Services

Deploying the FirstCube project to the server

Deploying projects using advanced deployment strategies

Processing objects in Analysis Services

Processing the FirstCube project

Using advanced processing options when processing cubes

Scheduling processing

Building a SSIS package to control processing

Troubleshooting processing errors

Partitioning cubes to speed up processing

Adding partitions to the FirstCube cube

Configuring storage modes in Analysis Services

MOLAP

ROLAP

HOLAP

Proactive caching

Adding a ROLAP dimension to the FirstCube cube

Summary

5. Querying Your Cube

Understanding multidimensionality

Writing MDX queries using Management Studio

Connecting to the cube using Management Studio

Understanding the anatomy of an MDX query

The FROM clause

The WHERE clause

The query axis clause

Important query concepts

Unique names

Finding unique names of objects

Tuples

Sets

Calculated members

Named sets

Functions

Using Excel as the query tool against cubes

Connecting Excel to the FirstCube cube

Advanced Excel features

Named sets

Calculated measures

Calculated members

Using Reporting Services together with your cube

Summary

6. Adding Functionality to Your Cube

Adding measures

Adding additional measure groups

Adding dimensions to the FirstCube project

Adding referenced dimensions

Adding many-to-many dimensions

Adding dimensions with parent-child hierarchies

Adding calculations to cubes

Simple calculated measures

Adding advanced calculated measures

Adding calculated members

Time and date calculations

Key Performance Indicators

Adding perspectives to simplify cube browsing

Adding translations to support global implementations

Extending the cube with custom actions

Building budget solutions using writeback

Summary

7. Securing Your Cube Project

Understanding role-based security

Adding users to the fixed server role

Adding custom roles to the database

Securing objects in Analysis Services

Adding dimension security to the FirstCube project

Securing measures

Implementing data security

Testing data security

Enable Visual Totals to restrict what a user can see

Understanding security when a user belongs to several roles

Implementing dynamic data security

Summary

8. Using Aggregations to Performance Optimize a Cube

Understanding cube aggregations

Adding aggregations to improve performance

Running the aggregation wizard

Adding aggregations manually

Using usage-based optimization to optimize the cube

Summary

9. In-memory, the Future

Understanding tabular models

Installing a tabular mode instance

Creating a tabular mode project

Defining the workspace server

Connecting to the data source

Adding tables to the data model

Adding data from other data sources

Working with partitions in tabular models

Creating calculations

Adding calculated columns to the tabular model

Creating calculated measures in the tabular model

Creating advanced calculations

Specifying other types of tables and columns

KPIs

Adding hierarchies

Sorting data based on other columns

Hiding columns and tables from the end users

Creating perspectives

Adding security to your in-memory model

Optimizing the tabular model for performance

Querying tabular models

Summary

10. Cubes in the Larger Context

Using Microsoft frontend tools to query Analysis Services

Developer-focused tools

Using Reporting Services to query your cube

SharePoint PerformancePoint Services

Self-service tools

Using Excel as a self-service tool against Analysis Services cubes

Using Excel PowerPivot to allow user-created models

SharePoint Excel Services

Introducing Power View – an analytical tool

Third-party tools

Summary

Index

Getting Started with SQL Server 2012 Cube Development

---

Getting Started with SQL Server 2012 Cube Development

Copyright © 2013 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: September 2013

Production Reference: 1040913

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-84968-950-2

www.packtpub.com

Cover Image by Suresh Mogre (<[e-mail]>)

Credits

Author

Simon Lidberg

Reviewers

David Loo

Richard Louie

Donabel Santos

Acquisition Editor

James Jones

Lead Technical Editor

Dayan Hyames

Technical Editors

Anusri Ramchandran

Dennis John

Kapil Hemnani

Gaurav Thingalaya

Project Coordinator

Apeksha Chitnis

Proofreader

Mario Cecere

Indexer

Tejal Daruwale

Graphics

Yuvraj Mannari

Production Coordinator

Manu Joseph

Cover Work

Manu Joseph

About the Author

Simon Lidberg is a database veteran, who has worked in the Computer industry since the mid nineties. He has had roles as Consultant, Support Engineer, Escalation Engineer, and Technical Presales Specialist. In the past 15 years, he has worked with databases and ERP Systems at companies such as Digital Equipment, Compaq, and Microsoft.

He has been an expert in Microsoft SQL Server since he started to work with Version 6.5 at Microsoft as a Support Engineer. Since 2006, he has worked with the Microsoft Business Intelligence stack that includes Analysis Services.

Since then, he has helped numerous companies to start using Analysis Services as well as have trained hundreds of people on Microsoft BI.

Simon is also a frequent speaker at conferences such as PASS SQLRally and Microsoft TechDays. He currently blogs at http://blogs.msdn.com/b/querysimon

I wish to thank the people at Packt Publishing who gave me the opportunity to write this book. I have tried to write the book that I felt was missing when I moved into the Business Intelligence space, after having worked with databases for 10 years. I hope that you, as a reader, will find it beneficial and that it will help you know Analysis Services.

I also want to thank the people who have helped me while writing this book, Mikael, Stephen, and all the rest; thanks for your help and inspiration.

Most of all, I wish to thank my wife, Marita, for the support during the work with the book.

About the Reviewers

David Loo is a Senior Software Development Professional with over 25 years of experience in both software development and people management. He is respected for his ability to focus teams on service excellence and for designing and implementing practical process improvements. Always on the lookout for ways to contribute his knowledge and experience of software development, team-building, and development best practices.

Richard Louie is a Business Intelligence developer at Redwood Trust, a residential and commercial mortgage investment firm. He has extensive experience in Oracle and Microsoft SQL for ETL, SSIS, SSRS, SSAS, and VB.NET. Richard is ASQ Green Belt Certified. He is a graduate in Information and Computer Science from the University of California, Irvine.

Donabel Santos is a SQL Server MVP and is the Senior SQL Server Developer/DBA/Trainer at QueryWorks Solutions, a consulting and training company in Vancouver, BC. She has worked with SQL Server since Version 2000 in numerous development, tuning, reporting, and integration projects with ERPs, CRMs, SharePoint, and other custom applications. She holds MCITP certifications for SQL Server 2005/2008, and an MCTS for SharePoint. She is a Microsoft Certified Trainer (MCT), and is also the lead instructor for SQL Server Administration, Development, Tableau, and SSIS courses at British Columbia Institute of Technology (BCIT). Donabel is a proud member of PASS (Professional Association of SQL Server), and a proud BCIT alumna (CST diploma and degree).

Donabel blogs at www.sqlmusings.com and her twitter handle is @sqlbelle. She speaks and presents at SQLSaturday, VANPASS, Vancouver TechFest, and so on. She writes for Packt, Idera, SSWUG, and so on. She is the author of Packt's SQL Server 2012 with PowerShell V3 Cookbook, and a contributing author of Manning's PowerShell Deep Dives.

www.PacktPub.com

Support files, eBooks, discount offers and more

You might want to visit www.PacktPub.com for support files and downloads related to your book.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

http://PacktLib.PacktPub.com

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.

Why Subscribe?

Fully searchable across every book published by Packt

Copy and paste, print and bookmark content

On demand and accessible via web browser

Free Access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.

Instant Updates on New Packt Books

Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.

Preface

Most books about Analysis Services are targeted at people who already work in the BI space and want to become experts. I wanted to write a book that could be read and understood by a novice who wants to become a BI developer. I have made this journey myself; I had worked with database solutions for more than 10 years when I started to work with BI solutions. I had a hard time finding a book that would cover the introduction to multidimensional modeling.

The intent of this book is not to be a complete book on Analysis Services development, but to serve as an introduction that will allow the user to get started. The book also contains links to where a reader can find more in-depth material on the topics covered. This will allow the user to start as a novice and to move into the role of an intermediate Analysis Services developer.

What this book covers

This book is a step-by-step instruction on how to get started with cube development. It takes the reader through the steps of installing and developing a BI solution built on Analysis Services. It contains the following chapters:

Chapter 1, Self-service Business Intelligence, Creating Value from Data, serves as an introduction to Business Intelligence solutions and specifically self-service solutions.

Chapter 2, Installing SSAS and Preparing for Cube Development, discusses the different models available to a user in Analysis Services. It covers the installation of Analysis Services and an introduction to the development environment.

Chapter 3, Creating Your First Multidimensional Cube, starts with an introduction to data warehousing modeling followed by a step-by-step instruction covering the initial development of the first OLAP cube.

Chapter 4, Deploying and Processing Cubes, covers the deployment and processing of cubes that are necessary in cube development. You will learn how you can automate processing of cubes to ensure that they contain the latest information from the data warehouse. You also learn how to partition your cubes to minimize processing times.

Chapter 5, Querying Your Cube, serves as an introduction to MDX, the query language used in Analysis Services. You will also learn how Excel can be used as a query tool against multidimensional cubes.

Chapter 6, Adding Functionality to Your Cube, looks at how you can extend your cube with calculations such as calculated measures and members, key performance indicators (KPIs), time calculations, and actions. It also contains information about how you can use your cube in a multi-lingual environment through the use of translations, and how you can build budgeting solutions using Analysis Services.

Chapter 7, Securing Your Cube Project, discusses how security works in Analysis Services and how a cube can be secured. You will learn how you can implement a dynamic security model using MDX functions.

Chapter 8, Using Aggregations to Performance Optimize a Cube, covers how aggregations can be used in Analysis Services to improve the performance of the BI solution. You will learn how you can create aggregations based on the actual usage of the cube.

Chapter 9, In-memory, the Future, introduces how you can build in-memory models in Analysis Services tabular mode. The chapter discusses measures, hierarchies, security, partitioning as well as more advanced topics such as time calculations and KPIs.

Chapter 10, Cubes in the Larger Context, shows how cubes fit into the larger architecture of a BI solution. You will get an introduction to the available frontend tools from Microsoft as well as the third-party tools that can be used as query tools against cubes.

What you need for this book

To follow the step-by-step instructions in this book you need a computer with the following software:

Windows Vista SP2 or later if you install it on a workstation, or Windows Server 2008 SP2 or later if you install it on a server.

One of the following Microsoft SQL Server 2012 Enterprise, BI, Developer, or Evaluation Editions. You can download the Evaluation Edition at the following link: http://www.microsoft.com/en-us/download/details.aspx?id=29066

Adventure Works Data Warehouse 2012 sample database. You can download the sample using the following link: http://msftdbprodsamples.codeplex.com/downloads/get/165405

Who this book is for

The audience of this book includes SQL Server developers that previously have not worked with Analysis Services, but want to move into the BI space. It is assumed that you have experience with relational databases but no skills in cube development are required.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: We can include other contexts through the use of the include directive.

A block of code is set as follows:

--Query 5.2

SELECT [Measures].[Sales Amount] ON COLUMNS,

  [Product].[Product Hierarchy].[Product Category] ON ROWS

  FROM [Adventure Works DW2012];]

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: clicking on the Next button moves you to the next screen.

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example