Microsoft BI (MSBI) Certification Training: SSIS, SSAS and SSRS

• Learning goal: Understand data warehousing, why ETL runs before data is loaded into the warehouse, and the basics of business intelligence.
• Data warehousing fundamentals
• Dimensions, measures, metadata and schemas
• Data marts and their design
• Normalization, denormalization and schema types
• OLAP and OLTP concepts
• OLAP cubes
• Types of slowly changing dimensions
• The ETL process and its tasks
• Business intelligence concepts and how BI works with data
• Microsoft BI as a complete BI solution
• Install SQL Server
• Install SQL Server Data Tools (SSDT)
• Create simple ETL tasks

• Learning goal: Learn why businesses need data integration tools and get a high-level view of the SSIS architecture.
• MSBI architecture and the Import and Export Wizard
• SSIS architecture
• Control flow and its components: tasks, containers and precedence constraints
• Data flow and its components: sources, destinations and transformations
• Scenarios that combine control flow and data flow
• System and user-defined variables
• Create a control flow task in SSIS
• Build a data flow task that aggregates records
• Create and run a control task that builds an index

• Learning goal: Tell the SSIS transformations apart and use them in real business scenarios.
• Data Conversion transformation
• Multicast transformation
• Union All transformation
• Conditional Split transformation
• Merge and Merge Join transformations
• Lookup and Cached Lookup transformations
• Foreach Loop container and its use cases
• Execute SQL Task with an Object result type
• Bulk Insert Task
• Archiving files with dynamic variables and the File System Task
• Use the Conditional Split transformation
• Repeat a Data Flow Task for multiple files with a Foreach Loop
• Archive data with a Foreach Loop and the File System Task
• Insert and update records with the Slowly Changing Dimension transformation

• Learning goal: Understand how SSIS workflow objects work together to handle transactions, errors and security.
• Parameters and event handling
• Transaction options
• Logging options and log providers
• Checkpoints
• Error outputs and OnError events
• Package security and protection levels
• Create an event handler
• Parameterize packages

• Learning goal: Use advanced SSIS features to add custom .NET code, and configure, deploy and schedule SSIS projects.
• The Script Component and the five types of package configuration
• Debugging, restarting and troubleshooting packages
• Running packages with DTExec and DTExecUI
• Package deployment
• Scheduling packages with SQL Server Agent
• Package performance tuning
• SSIS best practices
• Write custom code with a Script Task
• Send an email from a control flow with a Script Task

• Learning goal: Learn SQL Server Analysis Services (SSAS), its architecture and models, and design dimensions and measure groups.
• Introduction to SSAS and its features
• SSAS architecture
• Tabular and multidimensional models
• Data sources and data source views
• Identifying facts and dimensions
• Dimension design and hierarchies
• Designing measure groups and defining relationships
• Storage modes: MOLAP, HOLAP, ROLAP and partitions
• Create a data source and data source views
• Create dimensions and hierarchies
• Define a cube

• Learning goal: Deploy and process cubes, write MDX and DAX queries, and tune SSAS performance.
• SSAS deployment and database deployment methods
• Named calculations and named queries
• Aggregations, KPIs, translations, actions and perspectives
• Cube processing and processing options
• MDX and DAX queries
• Loading the data warehouse with SSIS and querying it with MDX and DAX
• Cube and dimension performance tuning and best practices
• Scheduling cube processing
• Retrieve tabular data with DAX
• Retrieve cube data with an MDX query
• Schedule cube processing

• Learning goal: Learn SQL Server Reporting Services (SSRS), its architecture and how to design reports.
• SSRS features, architecture and lifecycle
• Report parameters, multi-valued parameters and cascading parameters
• Report types: parameterized, linked, snapshot, click-through, drill-through, subreports, cached and ad hoc
• Groups, filters, alternating row colors, repeating headers and expressions
• Sorting and interactive sorting
• Publishing mobile reports
• Exporting reports to different formats
• Build groups in a table
• Add interactive sorting
• Create an annual sales report
• Export a report to PDF

• Learning goal: Manage, secure and share reports through the SSRS web portal.
• The SSRS web portal
• Deploying reports and data sources
• Role-based access to reports
• Data source security, snapshots and report caching
• Standard and data-driven subscriptions
• Ad hoc reporting with Report Builder
• Integrating Power BI with SSRS
• Assign roles to a user or group
• Create a linked report
• Share the same reports with users across departments
• Deploy and open reports in the web portal

Learning objective: Learning goal: See how SSIS, SSAS and SSRS work together to solve a real business data problem, from data extraction and transformation to loading the data warehouse, modeling and final report delivery.

• A complete BI business scenario using SSIS, SSAS, SSRS and MDX
• Microsoft BI best practices
• Case study: build the full scenario with SSIS, SSAS, SSRS and MDX