SQL Server 2005 Analysis Services

• Learn the core concepts in building solutions using Microsoft Business Intelligence with SSAS.

• Understand OLAP (cube) modeling and data mining.

• See how to develop solutions using Business Intelligence Development Studio (BIDS).

• Understand advanced storage design including MOLAP, ROLAP, and HOLAP.

• Learn the fundamentals of MultiDimensional Expressions (MDX).

• See best practices for SSAS administration using SQL Server Management Studio (SSMS) and XMLA (XML for Analysis).

• Work with OLAP reporting clients including Microsoft Excel and SQL Server Reporting Services.

• See integration with Microsoft Office 2007 and Microsoft SharePoint 2007.

• Core concept – BI is the cube or UDM
• Example cube as seen using Excel pivot table
• MS BI is comprehensive – more than Analysis Services on SQL Server
• Demonstration of SQL Reporting Services with cube as data source

OLAP Modeling

• Modeling source schemas—stars and snowflakes
• Understanding dimensional modeling— Dimensions (Type 1, 2, or 3) or rapidly changing
• Understanding fact (measures) and cube modeling
• Other types of modeling—data mining etc…

Using SSAS in BIDS

• Understanding the development environment
• Creating Data Sources and Data Source Views
• Creating cubes – using the UDM and the Cube Build Wizard
• Refining Dimensions and Measures in BIDS

Intermediate SSAS

• KPIs
• Perspectives
• Translations – cube metadata and currency localization
• Actions – regular, drill-through and reporting

Advanced SSAS

• Using multiple fact tables
• Modeling intermediate fact tables
• Modeling M:M dimensions, Fact (degenerate) dimensions, Role-playing dimensions, writeback dimensions
• Modeling changing dimensions – Dimension Intelligence w/ Wizard
• Using the Add Business Intelligence Wizards – write-back, semi-additive measures, time intelligence, account intelligence

Cube Storage and Aggregation

• Storage topics – basic aggregations, MOLAP



• Advanced Storage Design – MOLAP, ROLAP, HOLAP
• Partitions – relational and Analysis Services partitions
• Customizing Aggregation Design - Processing Design
• Rapidly changing dimensions / ROLAP dimensions
• Welcome to the Real Time – Proactive Caching
• Cube processing options

Beginning MDX

• Basic syntax
• Using the MDX query editor in SQL Server Management Studio
• Most-used Functions & Common tasks
• New MDX functions

Intermediate MDX

• Adding calculated members
• Adding scripts
• Adding named sets
• .NET Assemblies

SSAS Administration

• Best practices – health monitoring
• XMLA scripting (SQL Mgmt Studio)
• Other Documentation methods
• Security – roles and permissions
• Disaster Recovery – backup / restore
• Clustering – high availability

Introduction to Data Mining

• What and why?
• Examples of using each of the 9 algorithms (MS Clustering, MS Decision Trees, Naïve Bayes, MS Sequence Clustering, MS Time Series, MS Association Rules, MS Neural Network)
• Data Mining dimensions
• Data Mining clients
• Processing mining models

Introduction to Reporting Clients

• Excel 2003 Pivot Tables
• SQL RS & Report Builder
• SPS RS web parts & .NET 2.0 report viewer controls
• Business Scorecards 2005 & ProClarity

Future Directions – Integration with Office 12

• SharePoint 12 and AS
• Report Center (type of dashboard) uses KPIs, Reports, Excel Web, Filter
• Excel Services 12 and AS (Web Services)