Getting Started with SQL Server 2012 Cube Development电子书

Getting Started with SQL Server 2012 Cube Development

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