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

Dimension properties

The following table summarizes most frequently used dimension properties:

Dimension property name

Explanation

AttributeAllMemberName

Specifies the name of the top level of each attribute hierarchy. For example, if you set this property for promotion's dimension to All Adventure Works Promotions, each attribute hierarchy will show All Adventure Works Promotions as its top level. The default value is All.

This property does not apply to user-defined hierarchies. You can set the ALLMemberName property for each user-defined hierarchy to override its default.

StorageMode

The default value is MOLAP (Multi-dimensional OLAP), which stores dimension data in Microsoft's proprietary multidimensional storage format. The other option is ROLAP, which leaves data in the relational data source. If the storage option is ROLAP, MDX queries are resolved by sending the necessary SELECT statements to the relational source and retrieving data at query time. With previous releases of the software, very large dimensions had to use the ROLAP mode because of the string store data limit—see the StringStoresCompatibilityLevel property. The MOLAP mode generally outperforms ROLAP and is hence preferred. If you find that dimension processing time is exceedingly long and delivering data to users is more important than query performance, you could try using ROLAP.

Note: SSDT shows an InMemory option in a drop-down box for this property; however, the InMemory option is only available for the Tabular Model, discussed in this book.

ErrorConfiguration

It defines how Analysis Services should handle processing errors. Refer to Chapter 5, Optimizing Dimension and Cube Processing, for more information.

ProcessingGroup

The default value is ByAttribute, which sends a separate SELECT statement to the relational source for each attribute during dimension processing. Additionally, ByAttribute includes a DISTINCT keyword to ensure that we only return unique attribute values. The ByTable option sends a single SELECT statement for the entire dimension without using the DISTINCT keyword. Refer to Chapter 5, Optimizing Dimension and Cube Processing, for more information.

ProcessingMode

The default value is regular. The other option is lazy aggregations, which allows building indexes using the background thread, thereby shortening dimension-processing time. Refer to Chapter 5, Optimizing Dimension and Cube Processing, for more information.

StringStoresCompatibilityLevel

In Analysis Services versions prior to 2012, dimension string store files were limited to 4 GB. If your MOLAP dimension string store file exceeded the 4 GB limit, processing would immediately fail and report an error similar to: "File system error: A FileStore error from WriteFile Occurred. Physical file:\\?\N:\Analysis Services\Data\database name\...\2.string.data. Logical File:.. Errors in the OLAP Storage Engine: An error occurred while processing an XYZ object". One of the most anticipated fixes in 2012 is the StringStoresCompatibilityLevel option, which removes the 4 GB limit if it is set to 1100. If you restore a database from a backup file collected using a previous Analysis Services version, this property defaults to 1050. If the database was created using the 2012 version, the compatibility level is set to 1100 by default.

MDXMissingMemberMode

It controls how Analysis Services handles queries, which reference members that do not exist in the dimension. Many reports based on MDX queries explicitly reference dimension members. Some dimension members that once existed could have been removed. By default, Analysis Services 2012 simply treats such data as null—so the report will return nothing for missing members. The other option is Error—if you use this value, Analysis Services will report an error any time the MDX query references a missing member.

UnknownMember

This property controls how Analysis Services behaves if it does not find the expected dimension member during processing. The record could be ignored, converted to an "unknown" member, or hidden. The available property values are None (default), Visible, Hidden, or AutomaticNull. An unknown member generally exposes an issue within your data source. If you prefer to not display such rows at all, set this property to Hidden. If you prefer to identify issues (perhaps during quality assurance testing), make UnknownMember visible. By default the unknown members show up as unknown, but you can change the UnknownMemberName dimension property if you prefer to display it as something different, for example, "unspecified" or "undetermined". The value of None indicates that an unknown member is not used.

See also

Discussing each dimension property is beyond the scope of this book—you can refer to product documentation for properties not mentioned in the preceding table.

Previous Section
End of Section
Next Section