learnex

 
MSBI
  • Course Content 

    • 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, 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)



    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
          • The Sort Transformation
          • Data Viewers
        • 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
          • Configuration discipline

         

        • 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 

        • Introducing 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