Dan English
Principal Consultant – Business Intelligence Architect
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
www.magenic.com