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

About the Reviewers

Dan English is a Microsoft SQL Server MVP and the Business Intelligence Practice Manager at Superior Consulting Services in Minneapolis, MN. He has been developing with Microsoft technologies since 1996 and has focused on data warehousing and business intelligence since 2004. He has presented for the Minnesota SQL Server user group, the Microsoft Minnesota BI user group, and Minnesota TechFuse, along with SQL Server and SharePoint Saturday events. He is also an avid blogger and tweeter.

He holds a Bachelor of Science degree in Business Administration from the Minnesota State University, Mankato. He is an MCITP: Business Intelligence Developer 2005 and 2008 and a Microsoft Certified Technology Specialist (MCTS) for Microsoft SQL Server Business Intelligence—Implementation and Maintenance and also for Microsoft Office SharePoint Server 2007—Configuring.

He and his wife, Molly, live in Minnesota and have two children, Lily and Wyatt.

Stevo Smočilac is an Associate Principal Consultant at Magenic, a Microsoft Gold Certified Partner that specializes in business intelligence solutions.

He has over 12 years' experience working in software development, the last seven of which have focused on designing, implementing, managing, and administrating technical solutions developed using Microsoft SQL Server and the Microsoft Business Intelligence stack. He has been involved in all phases of the BI development lifecycle from envisioning through operational support and is passionate about the field of business intelligence.

He is currently a Virtual Technology Solutions Professional (V-TSP) for business intelligence, a Microsoft Certified IT Professional, and holds a B. Tech degree in Information Technology.

Originally from South Africa, he now resides in the (much colder) northeastern United States with his wife, Talya. When not in front of his computer, he can often be found either playing the guitar or riding his mountain bikes.

Previous Section
Next Chapter