Microsoft Power BI
Course number: CGIMPBI40
The Microsoft BI Certification course is curated by industry professionals to help you master BI concepts such as SSIS, SSRS and SSAS using SQL Server 2016 and SQL Server Data tools. This course is designed to provide insights into different tools in Microsoft BI Suite such as SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services.
What you will learn:
- Analyze data sets and software programs, and you’ll be able to see connections and understand the meaning of data that are presented
- Implement ETL system using SQL server integration services
- Model, process and visualize your data in a lucid manner
- Design enterprise-level reporting and modify data warehousing
- Gain expertise using Microsoft’s tools such as report builder and power view
- Integrate various BI tools to build a complete BI solution
- Solve Industry based Project on Analytics
Prerequisites
- It is recommended that students have experience in any of the RDBMS, ETL, OLAP or reporting tools.
Target Audience
The market for Analytics is growing across the world, and this strong growth pattern translates into a great opportunity for all IT Professionals. This course is recommended for Software or Analytics professionals who have experience using any RDBMS, ETL, OLAP or reporting tools.
Course Outline
Introduction to Data Warehousing and MSBI
- Fundamental concepts of Data Warehousing
- Concepts of Dimensions, Measures, Metadata, Schemas Data Marts and Design approaches
- Normalization and Denormalization and Schema types
- Online Analytical Processing and Transactional Processing concepts
- OLAP Cube
- Types of Slowly Changing Dimensions
- Explain ETL process and various tasks involved in it
- Discuss Business Intelligence concepts
- How BI works with data-warehouse
- Describe MSBI as One-stop Solution
Introduction to SSIS
- Understand the MSBI Architecture, Import and Export wizard
- Understand SSIS Architecture
- Control Flow & its Components (Tasks, Containers, Precedence Constraints)
- Data Flow & its Components (Source and Destination Connections, different types of Transformations)
- Scenarios by combining Control Flow and Data Flow components
- System Variables and User-defined variables
Transformations and Use-Cases
- Data Conversion transformation
- Multicast transformation
- Union all transformation
- Conditional Split transformation
- Merge and Merge Join transformation
- Lookup transformation
- Cached Lookup transformation
- Foreach loop and use-cases
- Advancing Execute SQL Task with Object return type and type of Outputs usage
- Bulk-insert task
- Archival process using dynamic variables and FST
Transactions and Event Handling
- Parameters, Event Handling
- Transaction Options, CheckPoints, Logging options, Log Provider Configure
- Error Output and OnError event
- Package Security: Package Protection Levels
SSIS (Configuration, Deployment and Scheduling)
- Script Component, Package Configurations (5 types)
- Debugging & Troubleshooting
- Executing packages – DTExec and DTExecUI
- Package Deployment
- Package Scheduling using SQL Agent
- Package Performance Tuning
- SSIS Best PracticesUnderstand
- Package Performance Tuning
- Learn SSIS Best Practices
SSAS Cube Design
- Introduction to SASS
- Its Architecture
- Features of SSAS
- Modelling in SSAS
- Tabular Model and Multidimensional Model
- Data Sources, Data Source Views
- Identify the Fact and Dimensions Designing Dimensions
- Dimension Hierarchies
- Designing Measure Groups
- Attribute Relationships
- Storage Modes: MOLAP, ROLAP, HOLAP, Partitions
SSAS Deployment and MDX, DAX
- SSAS Deployment
- Methods of database deployment
- Named Calculations and Named queries
- Aggregation, KPIs, Actions, Perspectives, Translations
- Cube Processing
- Different Processing Options
- Explain MDX and DAX Queries
- Loading Data into DW using SSIS, MDX queries, DAX Queries
- Performance Optimization of cube and Dimension, Best Practices
- Schedule the Cube processing
SSRS Reporting
- SSRS Features
- Reporting Services Architecture & Lifecycle
- Report Parameters and Multi-Valued Cascaded Parameters
- Types of Reports: Parameterized, Linked, Snapshot, Cached, Ad hoc, Clickthrough, Drill through, Sub reports
- Creating Groups, Filters, Expressions, Sort and Interactive Sort, Alternate Row Colors, Repeating Row Headers and Column Headers
- Describe Mobile report publisher
- Exporting Reports to different formats
SSRS: Web Portal
- Web Portal
- Deploying Reports and Data Sources
- Securing Access to Reports (Role based Security)
- Aspects of security
- Data Source Security, Caching Report and Snapshot Report
- Report Subscriptions: Standard, Data-Driven
- Managing Subscriptions, Ad-Hoc Reporting with Report Builder
- Integration of Power BI with SSRS
Integration of SSIS, SSAS and SSRS
- End to End Business Intelligence Business Scenario involving SSIS, SSAS, SSRS and MDX.
- MS BI Best Practices
Live Online