MSBI(SSIS, SSRS, SSAS) Concepts :

SQL Server 2008 Analysis Services

What Is Microsoft BI?

✔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, write back 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 ✔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)
✔SQL Server 2005 Integration Services
✔Introduction to SQL Server Integration Services

Product History
✔SSIS Package Architecture Overview
✔Development and Management Tools
✔Deploying and Managing SSIS Packages
✔Source Control for SSIS Packages
✔SSIS for DBAs: Using SQL Server Management Studio

The Import and Export Wizard
✔Importing and Exporting Data
✔Working with Packages
✔Database Maintenance Plans
✔Creating Database Maintenance Plans using SSMS
✔Scheduling and Executing Plans
✔Examining Database Maintenance Plan Packages
✔Changes in SQL Server 2005 Service Pack 2
✔Business Intelligence Development Studio

Launching BIDS
✔Project templates
✔The package designer
✔The Toolbox
✔Solution Explorer
✔The Properties window
✔The Variables window
✔The SSIS menu
✔Introduction to Control Flow

Control Flow Overview
✔Precedence Constraints
✔The Execute SQL Task
✔The Bulk Insert Task
✔The File System Task
✔The FTP Task
✔The Send Mail Task
✔Advanced Control Flow

Containers – grouping and looping
✔The Web Service Task
✔The WMI tasks
✔The Analysis Services tasks
✔The Execute Process Task
✔The Execute Package Task
✔Introduction to Data Flow

Data Flow Overview
✔Data Sources
✔Data Destinations
✔Data Transformations
✔The Copy Column Transformation
✔The Derived Column Transformation
✔The Data Conversion Transformation
✔The Conditional Split Transformation
✔The Aggregate Transformation
9.The Sort Transformation
10.Data Viewers
1✔Variables and Configurations

Variables Overview
✔Variable scope
✔SSIS system variables
✔Using variables in control flow
✔Using variables in data flow
✔Using variables to pass information between packages
✔Property expressions
✔Configuration Overview
✔Configuration options
9.Configuration discipline
10.Debugging, Error Handling and Logging

SSIS debugging overview
✔Breakpoints in SSIS
✔SSIS debugging windows
✔Control Flow: The OnError event handler
✔Data Flow: Error data flow
✔Configuring Package Logging
✔Built-in log providers
✔Advanced Data Flow

Revisiting data sources and destinations
✔The Lookup Transformation
✔Getting Fuzzy: The Fuzzy Lookup and Fuzzy Grouping Transformations
✔The Multicast Transformation
✔The Merge and Merge Join Transformations
✔The Data Mining Query Transformation
✔The Data Mining Model Training Destination
✔The Slowly Changing Dimension Transformation
✔Extending SSIS through Custom Code

Introduction to SSIS scripting
✔The SSIS script editor
✔The SSIS object model
✔Script in Control flow: The Script Task
✔Script in Data flow: The Script Component
✔Introduction to SSIS component development
✔SSIS Package Deployment

Configurations and deployment
✔The deployment utility
✔Deployment options
✔Deployment security
✔Executing packages – DTExec and DTExecUI
✔SSIS Package Management

The SSIS Service
✔Managing packages with DTUtil
✔Managing packages with SQL Server Management Studio Scheduling packages with SQL Server Agent
✔SQL Server 2005 Reporting Services
✔Introduction to SQL Server Reporting Services

Tour of Features
✔Reporting Services Architecture
✔Reporting Services Terminology
✔Reporting Services Editions
✔Taking Reporting Services for a Spin
✔Creating Reports

Deconstructing Reporting Services
✔Shared Data Sources
✔Creating Reports from Scratch
✔Calculations and Formatting

Creating Expressions
Using the Global Collections
✔Formatting Items
✔Conditional Formatting
✔Grouping and Sorting

Creating Groups
✔Calculating Totals and Percentages
✔Interactive Sorting
✔Creating Drill-Down Reports
✔Report Parameters

Creating Report Parameters
✔Creating Drop-Down Parameters
✔Multi-Valued Parameters
✔Debugging Parameter Issues
✔Creating Matrix Reports and Charts

Creating a Basic Matrix Report
✔Matrix Subtotals
✔Creating a Basic Chart
✔Exploring the Charting Possibilities
✔Managing Reporting Services

Deploying Reports and Data Sources
✔Exporting Reports and Printing
✔Using the Web-Based Report Manager
✔Using SQL Server Management Studio-based Reporting Services Manager
✔Reporting Services Security

Two Parts to Reporting Services Security
✔Securing Access to Reports
✔Data Source Security
✔Programming Reporting Services

The Many Reporting Services APIs
✔Integrating Reporting Services into Applications using URL Access
✔Using the Report Viewer Controls
✔Advanced Reporting Services Programming

Using the Reporting Services Web Service
✔Working with Custom Assemblies
✔Snapshots and Subscriptions

Caching and Snapshots
✔Creating Standard Subscriptions
✔Creating Data-Driven Subscriptions
✔Managing Subscriptions
✔Ad-Hoc Reporting with Report Builder

The Report Builder Architecture
✔Creating Report Model Projects
✔Running Report Builder