Dan English

Principal Consultant – Business Intelligence Architect

DanE@magenic.com

http://denglishbi.spaces.live.com

http://twitter.com/denglishbi

SSAS Design Best Practices and

Incremental Processing

Who am I?

Dan English

http://denglishbi.spaces.live.com/

Developing with Microsoft technologies for over 12 years

Over 5 years experience with Data Warehousing and Business Intelligence

Architect and develop dashboard solutions for enterprise reporting and monitoring

Experienced in ETL and Analysis Services development, requirements gathering, and data modeling

Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technology Specialist (MCTS)

PASSMN 2009/2010 – Executive Board Chair (President)

Twitter – http://twitter.com/denglishbi

YouTube Videos - http://youtube.com/user/denglishbi

Who is Magenic? Founded in 1995, Magenic is a technical

consulting firm focused exclusively on Microsoft technologies and has designed and delivered more than 500 Microsoft-based applications

Headquartered in Minneapolis, with offices in Chicago, Boston, Atlanta and San Francisco

2005 Microsoft Partner of the Year, Custom Development Solutions – Technical Innovation

2007 Microsoft Partner of the Year Finalist, Data Management

Microsoft Gold Certified Partner and National Systems Integrator

Over 200 consultants

Quick Audience Poll

How many are currently using Analysis Services?

How many are considering Analysis Services?

What are you using Analysis Services for and how?

Anyone currently looking at PowerPivot?

Today’s Agenda

• Microsoft Business Intelligence Overview

• Overview of Analysis Services

• AMO Warnings

• Dimension Designs / Demos

• Cube Designs / Demos

• Incremental Processing

• PowerPivot Comparison - quick mention

• Questions

Microsoft Business Intelligence Overview Business User Experience

•Analysis Services

•Reporting Services

•Integration Services

•Master Data Services

•Data Mining

•Data Warehousing

•Dashboards & Scorecards

•Excel Services

•Web based forms & workflow

•Collaboration

•Search

•Content Management

•LOB data integration

•Self-Service access & insight

•Data exploration & analysis

•Predictive analysis

•Data visualization

•Contextual visualization

Business Collaboration Platform

Data Infrastructure & BI Platform

BI Maturity Model

By Wayne Eckerson, Director of Research, TDWI

SSAS Overview

In North America in 2003 there were

$21,935,649 in Bike Sales and 9,975

Bikes Sold

Data mart OLAP

Engine Source

Data

• OLAP Database

• Slice-and-dice

• Drilldown / cross-drill

• Aggregated values

AMO Warnings - Best Practice Alerts

SQL Server Best

Practice

Analyzer alerts

embedded –

database or

object level

Dimension Designs

• Define only required attributes – add more later as needed

• Create user-defined hierarchies – navigation paths

• Create attribute relationships – optimize storage and define integrity

• Define proper key columns for attributes – preferably numeric

• Use BIDS Helper – Dimension Health Check

• Set Attribute Relationship Type appropriately – flexible or rigid

• Avoid High Cardinality attributes as hierarchies – most likely member

properties

• Set Order By appropriately – name, key, related attribute

• Set dimension and attribute Types appropriately – Account, Time, etc.

• Set attribute Instance Selection appropriately – needed for Report

Models

Dimension Designs

In SSAS 2008 there is a

new attribute relationships

tab in the dimension

designer which provides an

easy to understand

interface and diagram.

Best Practice Alerts / Dimension Designs

Demos

Cube / Calculation Designs

• Reuse dimensions multiple times instead of duplicating (role playing) –

lower storage costs and maintenance

• Use proper numeric data types – reduce storage costs

• Split measure groups into separate cubes if unrelated – avoid confusion

and improve query performance

• Place distinct count measures in separate measure groups – different

aggregations

• Set IgnoreUnrelatedDimensions on measure group appropriately

• Remove simple calculations like addition or subtraction (if possible) –

move to ETL, DSV, or a Measure

• Add a default NULL measure to cube – improve performance, reduce

unnecessary querying

• Group measures / calculations with proper measure groups and folders

• Provide proper formatting on all measures and calculations – currency,

standard, decimals, percentage, etc.

Cube Partitions / Aggregation Designs

• Create partitions in measure groups with more than 20MM rows

• Combine partitions that are too small to improve performance – don’t

create unnecessary partitions

• Don’t create too many aggregations – can have negative impact on

queries

• Enable Query Logging for UBO

• Manual aggregations 20 to 30% gain, UBO 70 to 80% gain (actual

usage)

• Set member and row counts for aggregation design algorithm

• Evaluate whether rigid or flexible attribute relationships are being used

in aggregation designs – flexible ones will be dropped if there are

changes

Cube Design Demos

Demos

Demo Screenshots – Cube Designs

CREATE MEMBER

CURRENTCUBE.MEASURES.UseAsDefaultMeasure

AS NULL,

VISIBLE = 0;

SSAS Incremental Processing

Reasons for implementing:

• Data volumes are extremely large

• Reduce

• End-user down time

• Processing time

• Impact on source

• Impact on processing server

• More frequent loads – every X hours instead of nightly

SSAS Incremental Processing

SSAS Incremental Processing

PowerPivot – Excel 2010 Add-in

Self-service analysis

delivered thru Excel

2010

Work with massive

amounts of data

PowerPivot – a few tidbits

What you do get…

• OLAP engine (in-memory cube – Vertipaq)

• DAX functions – Excel like with intellisense

• Excel user interface

• PivotTables and Charts

What you don’t get…

• Dynamic user level security

• Hierarchy support and parent\child

• Attribute properties and cube actions

• Robust Enterprise OLAP Solution

Resources Microsoft BI Site

http://www.microsoft.com/bi

Microsoft BI Resource Center

http://technet.microsoft.com/bi

William E. Pearson, III DB Journal Tutorials

http://www.databasejournal.com/article.php/1459531

SSAS Multi-Dimensional SQL Developer Center

http://technet.microsoft.com/en-us/sqlserver/cc510300.aspx

Channel9 MSDN BI Screencasts

http://channel9.msdn.com/Showforum.aspx?forumid=38&tagid=277

SQL Server Best Practices

http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

Microsoft Virtual Labs (TechNet and MSDN)

http://www.microsoft.com/events/vlabs/default.mspx

Microsoft BI Virtual Labs

http://denglishbi.spaces.live.com/blog/cns!CD3E77E793DF6178!349.entry

Magenic Blogs

http://blog.magenic.com/blogs

Questions

Thank you

http://denglishbi.spaces.live.com

http://twitter.com/denglishbi

info@magenic.com

www.magenic.com