Learn MSBI STEP BY STEP, MSBI Interview Q&A, SQL Step by Step and SQL Interview Q&A Tutorial
43 MSBI Labs and MSBI 2019 Live training Recordings (Subscription Validity For 8 Months)
What you'll learn :-
This package will cover syllabus of SSIS, SSAS and SSRS components with complete hands-on practicals using latest MSBI(SSDT) and database(SQL Server) software versions.
Added advantage with this package learners will also get covering code walkthrough practically pre-recorded video series of "Learn MSBI Step by Step" which include essentials theory/concepts, fundamentals and practical's for people who will be working on projects and preparing for their upcoming interviews. This course starts right from scratch beginning with installation do's and don'ts, New projects and so on.... moving to advanced level so that the learner can independently work upon projects on their own also job seekers can get prepared them selves with these materials for their upcoming interviews.
Course Content
- Lab 1 :- MSBI Fundamentals, Data flow, Control Flow, ETL, Dataware house. (SSIS)
- Lab 1.1 MSBI Installation in Details (Upgrade Lab)
- Lab 2:- Conditional split, Data conversion and Error handling. (SSIS)
- Lab 3:- For Loop, Variables, Parameters and Debugging. (SSIS)
- Lab 4:- Packaging and Deployment, File component and running SSIS package as a task.(SSIS)
- Lab 5: - For dimension, measures, star schema, snow flake, shared connection managers & packages tasks.(SSIS)
- Lab 6:- SCD, Type 0, Type 1, OLEDB Command and Unicode conversions.(SSIS)
- Lab 7:- Lookup, Data conversion optimization and updating SSIS package.(SSIS)
- Lab 8:- Sort, Merge and Merge Joins.(SSIS)
- Lab 9 :- Creating SSAS Cube. (SSAS)
- Lab 10:- SSAS Time series and Excel display.(SSAS)
- Lab 11: - What are Transactions and CheckPoints in SSIS? (SSIS)
- Lab12: - Simple SSRS report & implementing Matrix, Tabular, Parameters, Sorting, Expressions. (SSRS)
- Lab 13:- Using Data Profiling task to check data quality. (SSIS)
- Lab 14:- Hierarchical Dimensions. (SSAS)
- Lab 15:- WebServices and XML Task. (SSIS)
- Lab 16:- DrillDown and Subreports. (SSRS)
- Lab 17 :- SSAS KPI (Key Performance Indicators). (SSAS)
- Lab 18:- Pivot, UnPivot and Aggregation. (SSIS)
- Lab 19 :- SSAS Calculation.(SSAS)
- Lab 20:- SQL Execute Task. (SSIS)
- Lab 21:- Reference and Many-to-Many Relationship. (SSAS)
- Lab 22 :- Script Task and Send Mail Task. (SSIS)
- Lab 23 :- Script component(SSIS)
- Lab 24 :- Bar chart, Gauge and Indicators.(SSRS)
- Lab 25:- Partitions in SSAS. (SSAS)
- Lab 26 :- CDC(Changed Data Capture) in SSIS. (SSIS)
- Lab 27:- Additive, Semiadditive and non-additive measures in SSAS.(SSAS)
- Lab 28:- Buffer Size Tuning (SSIS)
- Lab 29 :- How to implement Multithreading in SSIS?(SSIS)
- Lab 30:- Processing SSAS cube in background.(SSAS)
- Lab 31 :- Explain Asynchronous, Synchronous, Full, Semi and Non blocking Components. (SSIS)
- Lab 32 :- SSRS Architecture and Deployment (SSRS)
- Lab 33 :- DQS( Data Quality Services ) (SSIS)
- Lab 34 :- Explain Tabular Model and Power Pivot (SSAS).
- Lab 35 :- MDX (Multidimensional Expressions) Queries.(SSAS)
- Lab 36 :- Data Mining (Fundamentals and Time Series Algorithm).(SSAS)
- Lab 37 :- Page Split and Performance issues with SSIS.(SSIS)
- Lab 38 :- Aggregations in SSAS.(SSAS)
- Lab 39 :- ROLAP, MOLAP and HOLAP.(SSAS)
- Lab 40 :- Instrumentation using Data Taps (SSIS).
- Lab 41:- Lookup caching modes and Cache Transform.
- Lab 42: - Perspectives & Translations. (SSAS)
- Lab 43 :- Tabular Training 1 :- Installation, Xvelocity, Vertipaq, DAX,Creating cubes,measures, KPI, Partition and Translation?
- MSBI 2019 Installation* SSIS Architecture
- ETL Process and understanding Conditional Split, Derived Component, Union All and Data Conversion Components
- SSIS Error Handling using Redirect Row and EventHandler : OnError : using SQL Execute Task, Multiple PAckages set as start up object.
- ForLoop, ForEachLoop, Sequence Container, Script Task, Execute SQL Task, Variables, Debugging, Break Point.
- Send Mail Through Send Script Task
- Transaction and CheckPoints.
- File System Task, Package Deployment, Scheduling SSIS Package From SQL Server Agent.
- Understand Sort and Types of Joins in SSIS and Insert Update Data using Merge Join. Incremental Load
- Understand OLAP, Star Schema, SnowFlake, Dimension and Facts and SSIS SCD and Lookup.
- Adding SSAS cube with Time Series to Star Schema with Excel Display.
- SSRS Reports (Matrix Tabular, Expression Parameters), SSRS DrillDown and Subreports.
- SSAS Hierarchical Dimensions, KPI, Calculations and Many-to-Many Relationship.
- MDX (Multidimensional Expressions) Queries.
- SSRS Bar Chart, Gauge and Indicators.
- Question 1 - What is ETL Process with example?
- Question 2 - Difference between control flow and data flow in SSIS?
- Question 3 - Explain the architecture of SSIS?
- Question 4 - Explain the types of Perfmon counters for SSIS?
- Question 5 - Explain the types of Cache Mode in SSIS Lookup component?
- Question 6: SSIS Variables vs Parameters?
- Question 7: How to Schedule SQL Server Integration Services (SSIS) Package?
- Question 8: How to pass variable as a parameter in Execute SQL Task SSIS?
- Question 9: How to send mails in SSIS?
- Question 10: SSIS Pivot and Unpivot?
- Question 11: What is Aggregation in SSAS?
- Question 12: What are Partitions in SSAS?
- Question 13: Explain Types of SSAS Storage Modes?
- Question 14: SSAS Relationships Types?
- Question 15: Named calculation vs Named query in SSAS?
- Question 16: What are the different data regions in SSRS?
- Question 17: How to Create Multi Valued Parameterized Report in SSRS?
- Question 18: Explain the different types of reports in SSRS?
- Question 19: How to send Automatic Report Delivery in SSRS?
- Question 20: Mention some of the open source softwares which are an alternative to SQL Server Reporting Services (SSRS)?
- Question 21 - How to do Error Handling in SSIS.
- Question 22 - Types of Containers in SSIS.
- Question 23 - How to do SSIS Package Deployment to SQL Server?
- Question 24 - What are Transactions?
- Question 25 - What are Checkpoints?
- Question 26 : What is Logging in SSIS?
- Question 27 : How to create File System Deployment?
- Question 28 : Difference between Merge and Union All?
- Question 29 : What is the Data Profiling task?
- Question 30 : What is Precedence Constraints?
- Question 31 : What is Merge Join and explain its different types of Joins?
- Question 32 : Difference between OLTP & OLAP.
- Question 33 : What is Fact, Dimension, Cube, Star Schema and Snow Flake?
- Question 34 : How to create a Cube in SSAS?
- Question 35 : What is SCD?
- Question 36 :- What is CDC?
- Question 37 :- Question 22 :- What is SSRS, what is Data Region, can embed/display SSRS Reports in any application, File extension and Code behind of SSRS Report.
- Question 38 :- How to Deploy SSRS reports?
- Question 39 :- Difference between Tabular and Matrix Report.
- Question 40 :- What is Page Break in SSRS?
- Question 41 :- Difference between Drilldown Report & Sub Report.
- Question 42 :- How to Create Cascade Parameters in SSRS Report ?
- Question 43 :- How to define clickable areas in SSRS Report?
- Lab 1: - Basic Fundamentals Database, Tables, rows and columns.
- Lab 2: - Primary key, foreign key, referential integrity and constraints.
- Lab 3: - Database Normalization (1st, 2nd and 3rd normal forms).
- Lab 4: - SQL basics(Select, Insert, Update and Delete).
- Lab 5: - DDL (Data definition language) Queries.
- Lab 6: - Transactions, Locks and Isolation level in SQL Server.
- Lab 7: - ISNULL and Coalesce functions.
- Lab 8: - Row_Number, Partition, Rank and DenseRank.
- Lab 9: - Triggers, inserted and deleted tables.
- Lab 10: - Instead of and after triggers.
- Lab 11: - Denormalization, OLTP and OLAP.
- Lab 12: - Understanding Star schema and Snow flake design.
- Lab 13: - SQL Server 8 kb pages.
- Lab 14: - Index and performances.
- Lab 15: - Page Split and indexes.
- Lab 16:- Clustered vs non-clustered.
- Lab 17: - Stored procedures and their importance.
- Lab 18: - Change Data Capture.
- Lab 19: - Explain Columnstore Indexes?
- Lab 20: - SQL Server agent.
- Lab 21: - How can we implement Pivot & Unpivot in SQL Server?
- Basics:- Query plan, Logical operators and Logical reads
- Point 1:- Unique keys improve table scan performance.
- Point 2:- Choose Table scan for small & Seek scan for large records.
- Point 3:- Use Covering index to reduce RID (Row Identifier) lookup.
- Point 4:- Keep index size as small as possible.
- Point 5:- use numeric as compared to text data type.
- Point 6:- use indexed view for aggregated SQL Queries.
- Point 7:- Database Engine tuning advisor is your best friend.
- Part 1 - Profiler, Tuning advisor, Clustered/Non-clustered indexes,DBCC command & Reading SQL Plan, OLTP & OLAP.
- Part 2 - Estimated vs Actual plan/rows, SQL Plan revised, Table/Index scan, Indexes seek, Physical/Logical opertn, Execution mode, columnstore, Rebinds, Rewinds, Nested loop, Hash/Merge join.
- Part 3 - Interleaved Execution, Fragmentation, Performance counters, Page splits and Unused Indexes.
- Part 4 - Extended events, RID lookup and Covering indexes.
- Part 5 - Indexed views, Page splits & unused indexes, Dead locks, Query store Parameter sniffing Statistics.
- Part 6 - Inline vs Stored procedure SQL, Cache miss, Cache hit, Parameter sniffing and Query store.
- Part 7 - Partitioning, Wait stats, Resource Governor, Inmemory Tables.
- Question 1 :- Explain normalization ?
- Question 2 :- How to implement normalization ?
- Question 3 :- What is denormalization ?
- Question 4 :- Explain OLTP vs OLAP ?
- Question 5 :- Explain 1st,2nd and 3rd Normal form ?
- Question 6 :- Primary Key vs Unique key ?
- Question 7 :- Differentiate between Char vs Varchar ?
- Question 8 :- Differentiate between Char vs NChar ?
- Question 9 :- Whats the size of Char vs NChar ?
- Question 10 :- What is the use of Index ?
- Question 11 :- How does it make search faster?
- Question 12 :- What are the two types of Indexes ?
- Question 13 :- Clustered vs Non-Clustered index?
- Question 14 :- Function vs Stored Procedures?
- Question 15 :- What are triggers and why do you need it ?
- Question 16 :- What are types of triggers ?
- Question 17 :- Differentiate between After trigger vs Instead Of ?
- Question 18 :- What is need of Identity ?
- Question 19 :- Explain transactions and how to implement it ?
- Question 20 :- What are inner joins ?
- Question 21 :- Explain Left join ?
- Question 22 :- Explain Right join ?
- Question 23 :- Explain Full outer joins ?
- Question 24 :- Explain Cross joins ?
- Question 25 :- Why do we need UNION ?
- Question 26 :- Differentiate between Union vs Union All ?
- Question 27 :- Can we have unequal columns in Union?
- Question 28 :- Can column have different data types in Union ?
- Question 29 :- Which Aggregate function have you used ?
- Question 30 :- When to use Group by ?
- Question 31 :- Can we select column which is not part of group by ?
- Question 32 :- What is having clause ?
- Question 33 :- Having clause vs Where clause?
- Question 34 :- How can we sort records ?
- Question 35 :- Whats the default sort ?
- Question 36 :- How can we remove duplicates ?
- Question 37 :- Select the first top X records ?
- Question 38 :- How to handle NULLS ?
- Question 39 :- What is use of wild cards ?
- Question 40 :- What is the use of Alias ?
- Question 41 :- How to write a case statement ?
- Question 42 :- What is self reference tables ?
- Question 43 :- What is self join ?
- Question 44 :- Explain the between clause ?
- Question 45 :- Explain SubQuery ?
- Question 46 :- Can inner Subquery return multiple results ?
- Question 47 :- What is Co-related Query ?
- Question 48 :- Differentiate between Joins and SubQuery ?
- Question 49 :- Performance Joins vs SubQuery?
- Question 50 :- Find NTH Highest Salary in SQL.
- Question 51 :- Select the top nth highest salary using correlated Queries?
- Question 52 :- Select top nth using using TSQL
- Question 53 :- Performance comparison of all the methods.
- SQL Server interview questions with answers PDF.
World-class training and development programs developed by top teachers
Whats Included
- World-class training teacher
- Bench has zero learning curve
- We handle the rest.