Course at a glance
Included in these subscriptions:
- Dev & IT Pro Video
- Dev & IT Pro Power Pack
| Release date | 2/23/2007 | |
| Level | Advanced | |
| Runtime | 8h 55m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | Included | |
| Hands-on labs | Included | |
| Sample code | Included | |
| Exams | Included |
Course description
In this course, you will learn how to use Microsoft SQL Server 2005 Analysis Services (SSAS) to design and implement OnLine Analytical Processing (OLAP) cubes and data mining models to support Business Intelligence (BI) solutions. This course includes concepts, procedures and practices based on real-world experience giving both the novice and experienced SQL Server 2005 developer the tools to build data warehousing and decision support system solutions. This course also provides information on end-user tools including Microsoft Excel 2003/2007 and Microsoft SQL Server 2005 Reporting Services. Implementing SSAS solutions have shown to boost data retrieval and report generation from SQL Server by up to 1000%.
Prerequisites
This course does not require any prior experience with Analysis Server 2005. It is assumed that students have working experience with SQL Server 2000 or 2005; basic relational database concepts (e.g., tables, queries, and indexing); data transformation services; Excel 2003; and SharePoint Server 2003.
Meet the expert
Lynn Langit is a trainer and consultant specializing in design and implementation of Business Intelligence solutions using Microsoft Analysis Server. She has been training and working with SSAS since 2000 and also has over 10 years experience as a business manager. This unique combination of technical knowledge and business experience makes her particularly qualified to implement OLAP training. Lynn holds MCT, MCITP (SQL Administration and Development), MCDBA, MSCD, and MSF certifications.
Course outline
Module 1
Business Intelligence (BI) (19:58)
- Introduction (01:28)
- Overview: BI (02:04)
- Defining BI (01:33)
- Why Use OLAP? (02:02)
- Defining UDM (00:52)
- Dimensions / Measures (01:40)
- OLAP Schemas (02:28)
- Build / View a Cube (00:03)
- Tools: BIDS (01:07)
- Tools: SSMS (01:17)
- Demo: AdventureWorksDW (01:47)
- Demo: Create a Diagram (03:13)
- Summary (00:18)
Building a Cube (18:02)
- Introduction (00:28)
- Overview: BIDS (01:10)
- Verify Sample Versions (00:44)
- View Sample Project (01:27)
- Review Sample Project (00:49)
- Implement Sample Cube (00:52)
- Data Source Views (00:37)
- Explore the Data (02:16)
- Deploy the Cube (01:52)
- Pivot Table Interface (00:32)
- Add Measures (02:15)
- View Dimensional Info (03:48)
- View Localizations (00:10)
- View Perspectives (00:37)
- Summary (00:19)
Connecting Using Excel (10:30)
- Introduction (00:28)
- Cubes in Excel (07:16)
- Cubes in SSRS (00:31)
- Other MS Data Clients (01:26)
- Develop Custom Clients (00:24)
- Summary (00:23)
Using Visio in SSAS (18:48)
- Introduction (00:26)
- Select a Modeling Tool (00:41)
- Visio DB Model Diagram (02:53)
- Understand OLAP Modeling (00:47)
- Grain Statement (01:43)
- Star Schema Models (02:08)
- Dimensional Modeling (01:43)
- Dimension Types (02:38)
- Demo: Model Keys (01:07)
- Demo: Model Fact Table (01:38)
- Snowflake Dimension (01:53)
- Add Notes to Model (00:45)
- Summary (00:18)
Module 2
Cube Modeling (26:09)
- Introduction (00:27)
- Overview (02:23)
- Dimensions / Hierarchy (00:43)
- Dimension Editor (01:26)
- Dimension Browser (01:21)
- Fact Table Modeling (02:37)
- Modeling with BIDS (01:01)
- The BIDS Interface (01:11)
- New OLAP Model w/BIDS (07:12)
- Schema Generation Wizard (02:57)
- View the OLAP Schema (02:51)
- Note about ETL (01:37)
- Summary (00:16)
Data Sources (19:09)
- Introduction (00:25)
- Understanding BIDS (01:40)
- Offline vs. Online Mode (01:41)
- Creating Data Sources (01:17)
- Demo: Data Source (03:53)
- View DataSource xml (01:10)
- Creating Data Source Views (02:08)
- Demo: Data Source View (06:32)
- Summary (00:18)
Build a Better Cube (27:42)
- Introduction (00:25)
- Creating a Cube (00:25)
- Create New Cube Wizard (08:19)
- Complete the Wizard (00:10)
- BIDS Interface (00:11)
- Browse a Cube (00:19)
- View Cube XML (00:45)
- Refine Dimensions (00:31)
- Demo: Refine Dimensions (00:35)
- Dimension Editor (00:58)
- View Dimension Structure (00:48)
- Work with Attributes (00:36)
- Work with Hierarchies (01:37)
- Dimensions Properties (03:47)
- Refine Measures (01:29)
- Demo: Refine Measures (02:06)
- Show Measures in GridView (01:12)
- Measures Properties (01:19)
- Work with Data Bus (01:45)
- Summary (00:17)
Module 3
Creating KPIs (12:51)
- Introduction (00:27)
- Create KPIs (00:58)
- How to Implement KPIs (00:56)
- Open KPI Sample Cube (01:04)
- View Included KPIs (00:42)
- View Expressions (00:41)
- New MDX KPI Functions (00:55)
- Use Designer to View KPI (02:54)
- Create Perspectives (00:54)
- Demo: Create Perspectives (02:57)
- Summary (00:19)
Creating Translations (22:56)
- Introduction (00:28)
- Overview: Translations (01:06)
- Demo: Create Translation (02:28)
- Localize Hierarchy (00:46)
- Localize Dimension Metadata (01:27)
- Localize Measure Values (01:38)
- Currency Localization (00:30)
- BI Wizard (04:38)
- Creating Actions (02:06)
- Demo: Create Actions (04:58)
- Customize a Template (00:14)
- View Actions (01:07)
- Modify the View (01:12)
- Summary (00:13)
Work with Data Bus (20:58)
- Introduction (00:25)
- Multiple Fact Tables (02:29)
- Dimension Usage Config (00:29)
- Demo: Data Bus (02:48)
- Adv. Dimension Types (01:37)
- Snowflake Dimensions (02:42)
- Degenerate Dimensions (00:51)
- Parent-Child Dimensions (01:06)
- Many-to-Many Dimensions (00:52)
- Role Playing Dimensions (00:47)
- Writeback Dimensions (01:12)
- Regular Relationship (02:18)
- Fact Relationship (00:33)
- Referenced Relationship (00:45)
- Many-to-many Relationship (00:21)
- Data Mining Relationship (01:19)
- Summary (00:15)
Slow Changing Dimensions (15:12)
- Introduction (00:26)
- Work w/Changing Dimension (03:19)
- ErrorConfiguration Prop. (02:45)
- Create an SSIS package (01:21)
- Slowly Changing Dim. Wiz. (02:32)
- Linked Objects (00:53)
- Business Intelligence Wizard (00:29)
- Demo: BI Wizard (03:08)
- Summary (00:16)
Module 4
Aggregations (15:01)
- Introduction (00:23)
- Basic Storage: MOLAP (00:37)
- SSAS and Metadata (00:55)
- Overview: XMLA (00:59)
- Demo: Examine XLMA (01:21)
- Three Storage Modes (01:13)
- Overview: Aggregations (02:46)
- Demo: View Metadata (01:07)
- Customizing Aggregations (02:25)
- Storage Settings (01:00)
- Aggregation Settings (01:57)
- Summary (00:14)
Profiler (06:30)
- Introduction (00:24)
- Overview: Profiler (01:09)
- Create/Config a Trace (04:41)
- Summary (00:15)
Partitions (20:49)
- Introduction (00:30)
- MOLAP, HOLAP or ROLAP (01:31)
- Using Partitions (00:48)
- ROLAP Dimensions (00:45)
- View a Partition in BIDS (01:53)
- Create New Partition (01:18)
- Change Storage Mode (00:49)
- Use Proactive Caching (01:05)
- Fine Tune Proactive Cache. (00:56)
- Notification Settings (00:39)
- Cube/Dimension Processing (01:43)
- Demo: Storage Settings (01:13)
- Demo: Storage Options (03:36)
- Process a Cube (01:22)
- Dimension Key Errors (00:33)
- Demo: Examine Dimension (01:48)
- Summary (00:11)
MDX Query (23:11)
- Introduction (00:26)
- Overview: MDX (01:24)
- MDX in BIDS (00:52)
- MDX Queries (01:29)
- First MDX Query (06:55)
- MDX Expressions (01:11)
- MDX Syntax Rules (01:10)
- MDX Structure Names (02:34)
- Members, Tuples, and Sets (01:56)
- Add Filter to Query (01:37)
- Add a Function (01:54)
- Add Child Function (01:21)
- Summary (00:15)
Module 5
MDX Functions (19:22)
- Introduction (00:28)
- Common MDX Functions (02:32)
- MDX Queries in Action (01:27)
- Demo: Work with Queries (00:58)
- Types of MDX Templates (00:14)
- Types of MDX Queries (02:40)
- Children Keyword (01:16)
- Members Keyword (00:41)
- CurrentMember Keyword (00:36)
- Filter Keyword (00:58)
- New / Updated Functions (02:02)
- Explore the Base Query (00:45)
- Examine the Results (00:35)
- Crossjoin Keyword (00:58)
- NonEmptyCrossjoin (00:34)
- AllMembers Keyword (00:49)
- Add a Tuple (01:22)
- Summary (00:18)
Calculated Members (17:51)
- Introduction (00:29)
- MDX in BIDS (00:57)
- Calculations Subtab (00:14)
- Overview: Calculated Measure (01:47)
- Why Use Calculated Members (01:28)
- Explore Calculations Subtab (01:30)
- View a Calculated Member (02:07)
- Config. Addl. Properties (01:02)
- Script in Script View (01:05)
- Perspective Designer (00:26)
- View Calculation Properties (00:27)
- Considerations (01:59)
- Overview: Named Sets (01:30)
- Create a Named Set (02:28)
- Summary (00:13)
MDX Scripts (14:53)
- Introduction (00:26)
- Overview: MDX Scripts (01:04)
- Script Organizer (01:29)
- ParallelPeriod Function (00:37)
- Script View (00:22)
- Debug Scripts (02:17)
- Add .NET Assemblies (02:36)
- Why External Assemblies (01:10)
- Demo: Add Assembly (03:12)
- View Sample Project (01:22)
- Summary (00:14)
Security (24:41)
- Introduction (00:28)
- Overview: SSAS Security (01:38)
- Implement Server Security (02:17)
- Surface Area Config Tool (01:25)
- Implement Cube Roles (00:53)
- Securing Dimensions (01:44)
- Add a New Role (00:39)
- BIDS Role Designer (01:34)
- Assign Permissions (03:53)
- Implement XMLA Scripts (01:33)
- SSAS Db Synchronization (00:57)
- SSAS Backup / Restore (01:11)
- Demo: Create XMLA Script (00:19)
- XMLA Templates (00:44)
- Demo: Examine XMLA Script (01:06)
- Db Synch Wizard (02:09)
- Demo: Backup / Restore (01:52)
- Summary (00:11)
Module 6
Scalability (24:57)
- Introduction (00:25)
- Overview: Clustering (01:47)
- Improving Scalability (00:58)
- Surface Area Config. Tool (01:36)
- Partition Wizard (00:51)
- Performance Optimization (00:13)
- Design Efficiently (01:10)
- Retrieve Data Efficiently (01:36)
- Query Efficiently (00:53)
- Aggregate Appropriately (02:07)
- Optimize Attributes (01:18)
- Monitor Intelligently (00:52)
- Work with Profiler (05:37)
- Extract SSAS Events (00:55)
- Optimize Dimensions (01:34)
- Optimize Hierarchies (02:44)
- Summary (00:15)
Data Mining (29:36)
- Introduction (00:27)
- Overview: Data Mining (01:43)
- Data Mining Concepts (04:13)
- Microsoft"s Data Mining (02:01)
- Implementing Algorithms (01:21)
- Data Mining Structures (00:32)
- View a Mining Structure (01:48)
- Mining Structure Props. (02:44)
- Mining Structure Sub-Tab (00:21)
- Microsoft"s Algorithms (03:35)
- Mining Models Sub-tab (00:42)
- Explore Sample Model (01:55)
- Config Algorithm Parameters (01:22)
- Advanced Model Properties (00:32)
- Mining Structure Viewers (04:35)
- Cluster Profiles Viewer (01:23)
- Summary (00:15)
Advanced Data Mining (19:00)
- Introduction (00:25)
- Mining Accuracy Charts (00:56)
- Mining Prediction Viewers (00:34)
- Accuracy Chart Sub-tab (03:52)
- Mining Structure Processing (01:02)
- SSIS and Data Mining (01:10)
- Process a Mining Structure (02:32)
- Design an SSIS Package (02:07)
- Data Mining Clients (01:24)
- Data Mining Ext. (DMX) (00:49)
- View DMX Templates (00:56)
- View DMX Queries (02:01)
- Prediction Join Keyword (00:58)
- Summary (00:08)
Module 7
Excel Pivot Tables (16:21)
- Introduction (00:25)
- Excel 2003 Pivot Tables (00:28)
- Limits of Excel 2003 (01:25)
- Create Pivot Table (03:56)
- Add Dimensions (01:18)
- Pivot Table Toolbar (00:24)
- Pivot Table Refresh (00:23)
- Publish a Pivot Table (02:28)
- Excel 2003 Pivot Charts (01:16)
- Create a Pivot Chart (01:02)
- Pivot Chart Design Surface (01:40)
- Pivot Chart Refresh (00:24)
- Publish Pivot Chart (00:52)
- Summary (00:13)
Reporting Services (19:35)
- Introduction (00:27)
- Overview: SSRS (02:23)
- Internals of SSRS (01:11)
- SSRS Config. Tool (02:48)
- SSRS Config. Manager (02:27)
- Designing Using SSRS (01:03)
- Integration SSRS / SSAS (01:39)
- Build Report with BIDS (02:04)
- Write an MDX Query (04:15)
- Preview the Report (01:01)
- Summary (00:12)
Report Builder (22:13)
- Introduction (00:28)
- Display Reports w/SSRS (01:36)
- Report Builder (01:30)
- OverwriteDataSources Prop. (00:47)
- Deploy the Report (00:28)
- Report Deploy Location (02:32)
- Generate Model (01:12)
- Demo: Report Builder (03:34)
- Save the Report (01:00)
- Scaling SSRS Reports (01:40)
- Report Viewer Controls (00:53)
- Demo: Report Viewer Ctl. (03:36)
- SharePoint 2K3 Web Parts (01:36)
- BSM 2005 (01:01)
- Summary (00:13)
Module 8
Excel 2007 (16:37)
- Introduction (00:27)
- Prerequisites (00:43)
- Office 2007 Ribbon (01:18)
- Demo: Office 2007 Ribbon (02:08)
- Excel 2007 Pivot Tables (00:47)
- Demo: Create Pivot Table (02:39)
- Pivot Table Tools (05:10)
- Excel 2007 Pivot Charts (00:23)
- Demo: Create Pivot Chart (02:44)
- Summary (00:14)
Data Mining Excel 2007 (12:43)
- Introduction (00:28)
- Overview: Data Mining (02:28)
- Overview: Add-in (00:48)
- Overview: Ribbon (00:10)
- Data Preparation Menu (00:28)
- Data Modeling Menu (00:35)
- Accuracy / Validation Menu (00:12)
- Model Usage Menu (00:07)
- Management Menu (00:16)
- Demo: Data Mining Ribbon (00:57)
- View Created Models (02:53)
- Use the Query Function (01:56)
- Create a Mining Model (01:02)
- Summary (00:16)
MOSS Integration (20:03)
- Introduction (00:31)
- Overview: MOSS (01:03)
- Demo: MOSS 2007 (01:08)
- MOSS Structure (01:38)
- Use Web Parts in MOSS (01:39)
- KPI Web Part (03:59)
- Excel Services (01:20)
- Configure Excel Services (01:33)
- Add Trusted File Location (02:21)
- Data Connection Libraries (01:11)
- Report Center (01:53)
- Performance Point Server (01:21)
- Summary (00:20)