Microsoft Advanced SSAS - SQL Server 2005 Analysis Services (Business Intelligence, BI, SSAS) UK Classes & Lessons (Microsoft)

About this class

This course has been written to give delegates with existing skills in Data Warehousing with SQL Server a framework of best practises. Delegates will gain a deep understanding of Cubes, Star Schemas, and Data Mining, using SQL Server 2005 Analysis Services (SSAS).

Our hands-on Advanced SSAS training course has been developed for real-world, commercial scenarios by our expert instructors.

This training may be available onsite; please contact us if you are interested.

Who will the lesson benefit?

Existing Database specialists who need to get more out of SQL Server 2005 Analysis Services for Data Warehousing and Data Mining.

What delegates will learn

By the end of this course you will gain:

Cube design best practises and security
A deep understanding of MOLAP / ROLAP / HOLAP
MDX Queries and Scripts
Learn how to use XMLA to create, process and back-up Cubes
Effective Data Mining

What prerequisites are required

You should be able to understand table, column, data type design, and write SQL Select statements within any relational database; also to write SQL Server stored procedures. You should have experience of populating a Star Schema; and of designing, building and deploying simple SSAS cubes with the Cube Wizard.

Contents of this class

Using SSIS with SSAS

Star schema stored procedures in an SSIS package
SSAS cube processing in SSIS
Deploying and scheduling an SSIS package

Using SSRS with SSAS

Introduction to SSRS report wizard
Generating MDX queries to report on a cube
Generating DMX queries to report on a data mining model

Cube design

Creating a data source
Creating a data source view
Creating simple dimensions and measures

Dimension design

Building standard dimensions
Time dimensions
Role-playing dimensions
Parent-child dimensions

Measure group design

Adding measures
Configuring dimension usage (for multiple fact table cubes)
Aggregation functions
Calculated measures
Measure formatting

Aggregation design - MOLAP/ROLAP/HOLAP

MOLAP/ROLAP/HOLAP
Degree of aggregation
Optimising aggregations
Query log

Hierarchies design

Attribute hierarchies
Natural hierarchies
Report hierarchies
Multiple hierarchy dimensions

MDX language

Creating calculated members and measures
MDX queries introduction
MDX scripts
KPIs (key performance indicators)
SSAS stored procedures overview

Localising cubes

Currency conversion
Local language cube captions
Local language cube dimension members

Folders and perspectives

Organising large cubes into folders
Segmenting large cubes with perspectives

Cube actions

Drill through (as opposed to drill down)
Displaying reports directly from cube in a pivot table
Writing values back into the cube

Cube security

Dimension security
Cell-level security
Administration and user roles

Cube storage and processing

More on MOLAP/ROLAP/HOLAP
Creating partitions
Incrementally updating partitions
Pro-active caching (real-time cubes!)
Slowly changing dimensions

Cube deployment and administration

Backing up and restoring cubes
Deployment and processing options

XMLA and AMO languages

Introduction to XMLA
XMLA to create cubes
XMLA to back up cubes
XMLA to process cubes
AMO to control cubes

Data Mining

Overview of data mining
Building mining models
Processing mining models