Transcript Introduction to Microsoft SQL Server Analysis Services 2008

Vidas Matelis,
Toronto SQL Server User Group
November 13, 2008
Quick info about me
 Microsoft BI Consultant – SQL Server, SSIS, SSAS,




SSRS; over 13 years experience with SQL Server
Microsoft SQL MVP
My website: http://www.ssas-info.com
My blog: http://www.ssas-info.com/VidasMatelisBlog/
18 Microsoft certification exams – MCP, MCSE-NT4,
MCSE-W2K, MCDBA (SQL 2000), MCTS-SQL2005,
MCTS-BI2005, MCTS-BI2008, MCIP-BI Developer
Agenda
 Quick info about SSAS – what it is and why to use it
 SSAS terminology explained – Database, cube,
measures, dimensions, attributes
 We will create POC SSAS DB on existing SQL Server
DB
Short SSAS history and future
 Panorama
 SQL Server 7.0
 SQL Server 2000
 SQL Server 2005
 SQL Server 2008
 SQL Server 2010 - Kilimanjaro and Gemini
About SSAS
 AS is a server-based platform for OLAP and data mining.
 Tools - BIDS, SSMS
 Query language – Multidimensional Expressions or MDX;
for Data Mining - Data Mining Extensions or DMX
 MDX Query:
SELECT [Date].[Calendar].[Calendar Year].Members ON COLUMNS
, [Product].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE ([Measures].[Reseller Sales Amount]
, [Geography].[Geography].[Country].&[Canada]
);
Why use SSAS
 Speed – MOLAP queries are much faster than
relational DB queries (especially summarized data)
Why use SSAS
 Speed – MOLAP queries are much faster than
relational DB queries (especially aggregated data)
 There are many front end tools available that allow
users to build reports themselves
Demo 1 – Terminology
 Database
 Cube


Measure
Dimension
 Hierarchy
 Attributes
How to use SSAS properly
 Extract data from source system(s)
 ETL data into relational data warehouse, conforming
data from different sources, using surrogate keys
 Create SSAS database.
 Choose front end and start building reports.
Building POC using SSAS
 Choose SQL Server database with data YOU KNOW
 Build a fake DW database using views and some tables
 Create SSAS database
 Choose front end and start building reports.
Demo
We will build POC on Adventure Works LT 2008 DB
 We will create new SQL Server DB
 We will create views that define dimensions and fact
table
 We will create SSAS Database
 We will query data from SSAS Database using Excel
Summary
 We learned what SSAS can do for you
 We learned how to build POC using SSAS
What to do next
 Get more info from http://www.ssas-info.com –
articles, papers, webcasts, FAQs
 Build POC using your company data and show it to
your boss
Q&A
 Any questions?