Learn MSBI , SSIS , SSRS and SSAS Step by Step
4.3 (1,665 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
10,499 students enrolled

Learn MSBI , SSIS , SSRS and SSAS Step by Step

This course teaches you MSBI from basic level to advanced level . Covers all 3 concepts SSIS , SSAS and SSRS.
Bestseller
4.3 (1,665 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
10,499 students enrolled
Last updated 7/2020
English
English [Auto]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 23 hours on-demand video
  • 2 articles
  • 24 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Should be able to work on professional MSBI projects. Design database using star schema and snow flake ,Create ETL using SSIS , Cubes using SSAS and display reports using SSRS.
  • Learn SSIS Data flow , control flow, error handling, variables , for loop containers, SCD ,Sort , merge , merge joins ,look up , transactions and checkpoints.
  • Displaying report in SSRS, Tabular reporting , Matrix reporting , report parameters , sorting , interactive sorting, expressions, running total , cascading drop downs and importance of RDL files.
  • In SSAS you will learn MDX , tabular , hierarchical dimension , KPI , Calculated members , Aggregations , perspectives , translations , Many to many relationship , reference relationship and processing SSAS cubes at background.
Requirements
  • Basic knowledge of SQL would help.
  • No past knowledge of BI or MSBI needed.
Description

If you are thinking about mastering MSBI then you have reached the ultimate tutorial. Yes , i mean by it :-) .This course teaches MSBI using 43 extensive labs as listed below. It goes in depth in to all the three important pillars of MSBI i.e. SSIS , SSAS and SSRS.

Lab 1 :- MSBI Fundamentals, Data flow, Control Flow, ETL, Dataware house. (SSIS)

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)

Lab11: - 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)

Lab16:- DrillDown and Subreports. (SSRS)

Lab17 :- 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. (SSAS)

Lab 42: - Perspectives & Translations. (SSAS)

Lab 43 :- Tabular Training 1 :- Installation, Xvelocity, Vertipaq, DAX,Creating cubes,measures, KPI, Partition and Translation?

Who this course is for:
  • Developers would want to learn MSBI ( Microsoft SQL Server Business Intelligence)
  • Developers who want to learn SSIS , SSAS and SSRS.
  • If you want to learn how to do ETL with SSIS.
  • if you want to learn Cube making with SSAS.
  • Want to learn SSRS reporting.
Course content
Expand all 46 lectures 22:45:58
+ Introduction
44 lectures 22:45:05

In lab 1 we have covered the below topics :-

Basic fundamental of what is BI.
Difference between data and information.
Importance of Dataware and Datamarts.
Understanding how SSIS , SSAS & SSRS fit in BI cycle.
Installation of MSBI ( SQL Server and SQL Server data tools)
Control flow vs Data flow.
Understanding concepts like connection manager and data viewers.
Figuring our errors and issues in SSIS.


     

Preview 55:33

This lab is a upgrade lab in which we will understand MSBI installation in detail so that you complete the course without issues.

Preview 30:19

In lab 2 we will cover the following topics :-
Using conditional split in SSIS and condition expressions.
Data types in SSIS and conversion in SSIS
Error handling in SSIS
Understanding Fail Component,Redirect Rows and Ignore Failure

Lab 2:- Conditional split, Data conversion and Error handling. (SSIS)
33:30

In Lab 3 we will cover the below topics
Understanding concept of container
For loop and Foreach container
Variables and Parameters and how they differ
Importance of expressions in SSIS.
Debugging variables in SSIS , quick watch and add watch.

Lab 3:- For Loop, Variables, Parameters and Debugging. (SSIS)
33:01

In lab 4 we will cover the following topics :-

Understanding SSIS package and deployment.
Running SSIS package as task.
File copying component.
Understanding SSIS project structure.
Understanding package deployment and project deployment  methods.
Setting global parameters in SSIS packages
Understanding different places where deployment can be done.

Lab 4:- Packaging and Deployment, File component and running SSIS package as a
35:38

What are the different database design techniques for OLAP ?
Why is normalization design not good for OLAP database?
What are dimensions and measures?
Which of the following are dimension and measures?

·       Population

·       Currency code.

·       Age ( Hint all numbers are not measures)

·       Sales Date ( Hint think before you answer)

·       Employee name

What is the difference between star schema and snow flake ?
How to share connection manager across different tasks ?
How to disable and enable Tasks on a package ?
How to execute a package?
If we have more than one package how to run as start up ?

Lab 5: - For dimension, measures, star schema, snow flake, shared connection man
30:04

In lab 6 we have covered the below topics :-

What is SCD and why is the name slowly changing dimensions ?

What is the need of business key in SCD ?

Explain the difference between Fixed, Changing and Historical attributes?

What is the difference between Unicode and Non-Unicode types ?

What is the difference between Type 1 and Type 0 SCD’s ?

What is the difference between DT_STR and DT_WSTR ?

Can destination component do Updates and deletes ?

What is the need of OLEDB Command component ?

What are params and sequence in OLEDB Command ?   

Lab 6:- SCD, Type 0, Type 1, OLEDB Command and Unicode conversions.(SSIS)
47:26

When new fields get added or deleted in database , how do we apply those changes to DTS?

What is the data type for Unicode and non-Unicode in SSIS?

What is the use of the Lookup component?

If records are not found you want the lookup to continue?

Can we make changes to SSIS package in debugging?

How to stop debugging a SSIS program?

How to avoid data conversion component in SSIS?

What is the gain of avoiding data conversion component?

What is the difference between compiled and debug mode in SSIS?

Lab 7:- Lookup, Data conversion optimization and updating SSIS package.(SSIS)
39:36

How can we sort records in SSIS?

What is the difference between Merge and Merge Joins?

Can we use Merge and Merge join with using Sort?

What is the difference between Merge join and Lookup?

Lab 8:- Sort, Merge and Merge Joins.(SSIS)
20:46

What is the need of SSAS?

How does SSAS improve performance in Analysis calculation?

What is stored in SSAS database?

Name the two ways by which you can create SSAS project?

What is the use of DataSource and Dataview?

What is a named query in the data view?

Can data view structure be different from physical database structure?

What is the need of CUBE and how is the structure of CUBE internally?

What are measures?

What is the use of Cube structure tab and the browser tab?

Why do we have latency in SSAS data?

How do we query SSAS database?

Lab 9 :- Creating SSAS Cube. (SSAS)
40:08

What does the time series wizard do?

Tell the importance of Dimension Usage tab in SSAS?

What does reconnect do in the SSAS browser tab?

What is the importance of hierarchical dimensions?

How to use excel to see SSAS Cube data?

Lab 10:- SSAS Time series and Excel display.(SSAS)
25:39

What is the importance of Transactions?

Which component helps us to enable transactions in SSIS?

Are transactions enabled  by default in SSIS?

What is the difference between Required, Supported and Not Supported?

What are checkpoints?

What is written inside the checkpoint file?

Explain Never,IfExists and Always in Checkpoint usage?

What are GUID’s?

When does the checkpoint file get deleted?

Is the checkpoint tracked at row level or component level?

Lab11: - What are Transactions and CheckPoints in SSIS? (SSIS)
25:40

Can SSRS connect to different types of data source?

What is difference between Report server project wizard and Report server template?

Which of the below statements are false: -

-- MDX is query language for SQL Server

-- SQL is Query language for SSAS cubes

Differentiate between Matrix and Tabular report?

Explain the importance of the three section in a report(Page, Group and Details)?

What is the importance of RDL file?

What is the internal format of RDL file?

Explain importance of report data tool bar?

What does dataset and datasource do in report data?

How to create parameters in SSRS?

How can we convert parameter to a dropdown?

How to do sorting and interactive sorting in SSRS?

Differentiate between expression and textbox properties.

How is the IIF syntax of expression written?

How to implement running total on a table?

How to achiever cascading drop down?

Lab12: - Simple SSRS report & implementing Matrix, Tabular, Parameters, Sorting
01:01:14

What is the importance of Data Profiling task?

Can we use data profiling task without SQL Server?

What is format of the profile data sent by data profiling task?

Name some profile request in Data profiling task?

Which tool is used to see the profile data?

Lab 13: - Using Data Profiling task to check data quality. (SSIS)
20:22

In what situation do we need hierarchical dimensions?

To accommodate hierarchical data how should the table design be structured?

What named queries in SSAS data source view?

How do we create dimension hierarchies?

Lab 14:- Hierarchical Dimensions. (SSAS)
25:58

What is the use the Web services?

What is WSDL and SOAP?

In Microsoft how are services created?

How can we consume Webservices in SSIS?

How can we read XMLdata in SSIS?

What is XQuery?

Lab 15:- WebServices and XML Task. (SSIS)
16:38

How can we create grouping in SSRS?

What setting do we need to do in visibility section for drill down effect?

In what scenarios you will use the Subreport component?

How is the parent section and subreport connected?

Lab16:- DrillDown and Subreports. (SSRS)
20:19

What is the need of KPI?

What are the four things needed to define a KPI?

What is the importance of 1,0 and -1 in Trend and Status?

How is the case statement written in KPI?

How can we calculate trend?

Lab17 :- SSAS KPI (Key Performance Indicators). (SSAS)
13:24

What is the concept of Pivot and UnPivot?

Explain Pivot key, set key and values?

What is the importance of sorting in Pivot component?

How to solve problems around duplicate values in Pivot key column?

What is the use of Aggregation transformation?

Lab 18:- Pivot, UnPivot and Aggregation. (SSIS)
18:30

What is the need of calculations?

What is the importance of the default “CALCULATE” script command?

What role does MDX play in SSAS calculations?

Explain the need of associated measure group and Display folder?

How can we format the output of calculated measures?

Who is responsible for look and feel formatting:-

•Cube

•Client software ( Excel or SSRS)

What is the need of Parent function in MDX expression?

When do we get infinity value?

Explain “Visible” property in SSAS calculation tab?

What is the importance of parent hierarchy and member in SSAS calculation?

Explain Aggregate function of SSAS calculation?

Differentiate between Measures and Calculated members?

Lab 19 :- SSAS Calculation.(SSAS)
31:35

What is the purpose of Execute SQL Task?

Which are the three options for SQL Source Type?

How to make execute SQL task dynamic?

Differentiate SQL Parameter VS SSIS parameters

How can stored procedure be executed using execute SQL Task?

Explain importance of resultset property in execute SQL task?

How can foreach loop component loop through output of execute SQL Task?

Lab 20:- SQL Execute Task. (SSIS)
29:31

What is a reference relationship scenario?

What kind of relationship is a regular relationship?

Explain how Materialize option impacts cube performance?

What are the steps to follow for incorporating man to many in SSAS?

What is the importance of intermediate measure table in many to many?

Lab 21:- Reference and Many-to-Many Relationship. (SSAS)
35:40

When should we use Script task?

What does send mail task?

What is the importance of windows authentication in send mail task?

What are the limitation of send mail task?

How to configure the script task component?

Lab 22 :- Script Task and Send Mail Task. (SSIS)
15:20

When should we use Script component?

What is the difference between Script component and Script task?

What is a buffer?

What are input and output columns in source script component?

What is the concept of DLL  and how are they added to project?

What are IO and Generics namespaces for?

What does “AddRow” of the buffer does?

What is the use of Split function?

Explain Pre-execute,Post-execute and Process input rows?

How can we debug the scrip component code?

Lab 23 :- Script component(SSIS)
43:34

In what scenarios will you display data in Bar chart?

What is the best option to display multiple bar charts?

What is the best fit for Gauge graphical display?

What kind of data needs to be displayed in an indicator?

Lab 24 :- Bar chart, Gauge and Indicators.(SSRS)
17:59

What are partitions in SSAS?

How many number of partitions are created by default?

What kind of data is stored in Partitions?

What is the consequence of having no partitions in a cube?

What is the problem during processing large records and how does partition solve the problem?

Can we process dimension independent of fact?

Explain the three processing options in SSAS?

Lab 25:- Partitions in SSAS. (SSAS)
24:49

What work does CDC do in SQL Server?

What is the importance of _CT table in CDC in Sql Server?

How does CDC denote insert,update and deleted states?

Does CDC do incremental or full load?

What are the two different phases in CDC load?

What is the importance of cdc_states table?

How CDC is different from SCD?

Lab 26 :- CDC(Changed Data Capture) in SSIS. (SSIS)
35:46

Explain Additive nature of Measure?

In what scenarios will you use Semiadditive?

Can you give some examples of non-additive?

Does Semi additive apply to all dimension values?

How does the formula impact the types of measures?

Lab 27:- Additive, Semiadditive and non-additive measures in SSAS.(SSAS)
15:47

Does SSIS follow Buffer oriented architecture or row oriented architecture?

Where do we set the default max rows and default buffer size?

Explain the difference between Semi-blocking, Full blocking and no blocking?

How will you calculate your buffer size optimally?

Lab 28:- Buffer Size Tuning (SSIS)
32:31

Explain the concept of Multithreading?

Where do we set number of threads in Control flow and Data flow?

If you increase number of threads does it mean it will increase performance?

What is the importance of partitioning in threading?

How do we do SQL Server data partitioning?

Can you explain some partitioning logic which you can think about?

How to select a particular partition from SQL Server?

Lab 29 :- How to implement Multithreading in SSIS?(SSIS)
39:09

What is XMLA?

How can we get XMLA of cube processing?

What is the use of SQL Server agent?

How can we run XMLA in SQL Server agent?

How to invoke SSAS cube processing from SSIS?

Lab 30:- Processing SSAS cube in background.(SSAS)
20:12

Explain the difference between Synchronous and Asynchronous components?

What is the importance of pipeline execution plan and trees?

How do you identify asynchronous components from Log files?

How can we remove sort asynchronous component?

Is Aggregate component synchronous or asynchronous?

Is Merge, Merge join and Union all semi-blocking or fully blocking?

Is it 100% possible to identify blocking components?

What is the importance of primeoutput filling event?

Lab 31: - Explain Asynchronous, Synchronous, Full, Semi & Non blocking Component
27:11

Is SQL Server data tools  available in development environment?

What is the full form  of RDL and format of RDL file?

Where does SSRS store its reports, name those databases?

Explain the importance of reporting services configuration manager?

What is the importance of IIS in SSRS?

Which are the two websites in SSRS and what role do they play?

Differentiate between reportserver and reportservetempdb databases?

What is the importance of encryption key in SSRS ?

In visual studio which property needs to  be set for deployment?

How to configure IIS Reportserver and Reports website?

Lab 32 :- SSRS Architecture and Deployment (SSRS)
22:24

What is DQS?

Explain the three databases of DQS?

How are these three databases created?

What are domains in DQS?

Explain the usage of term based relation tab?

Where do we specify validations in DQS?

Explain domain values and how are they different from Term based relation?

What is the importance of reference data?

How can we use DQS in SSIS Project?

Lab 33 :- DQS( Data Quality Services ) (SSIS)
43:32

Explain the difference between personal & professional BI?

What is Power Pivot?

Is Power Pivot an excel addin, yes or no?

How can we create relationships in power pivot?

Explain the importance Pivot table?

Differentiate implicit and explicit calculate fields?

What is DAX functions and DAX Queries?

Difference between multidimesion and tabular SSAS projects?

Are tabular instance and multidimesional instances same?

Explain Evaluate in DAX Queries?

Lab 34 :- Explain Tabular Model and Power Pivot (SSAS).
39:44

Differentiate between MDX vs SQL Query structure?

Write a simple MDX query to a single fact with two different dimensions?

What is the importance of .members in MDX?

Explain the importance of “nonempty” function in MDX?

How is the where clause of MDX different from SQL?

How many axis can we have in MDX?

What is axis(0),1,2,3 and 4 called as?

Can we select multiple dimensions on the same axis?

What is the children meant in MDX?

Explain filter function in MDX?

Differentiate between filter and where clause?

Differentiate between ASC VS  BASC?

If we miss any axis in between will it work?

We are getting data in three axes can sql management studio display it?

Can SSRS display Multi dimension data  and what are the limitations?

How to display 2 fact data in MDX?

Explain With measures in MDX?

Explain current and prevmember in MDX?

Differentiate between union, intersect and except?

Lab 35 :- MDX (Multidimensional Expressions) Queries.(SSAS)
54:14

What is Data mining?

Explain the term “training an algorithm”?

In what scenarios will you use time series algorithm?

What type of project you will select for Data mining?

For doing data mining is cube compulsory?

Explain sequence clustering algorithm?

Explain continuous and key time data types?

What is model in Data mining?

What is the query language for data mining?

Explain deviation in time series ?

Your algorithm is not showing proper trend what can be the reason?

Where does data mining model gets deployed?

Can we deploy data mining in tabular model?

Explain predict function of DMX?

Lab 36 :- Data Mining (Fundamentals and Time Series Algorithm).(SSAS)
30:00

Explain how B-Tree structure works in Indexes?

Explain 8 KB page structure?

How does page split happen?

Can page split happen for non-clustered indexes?

How many pages will be create for a record size of 1500 bytes and 20 rows?

Why is the best practice to create indexes as separate entities?

What is the importance of performance counters?

Which performance counter will help us to know page split issues?

How do we resolve the split issue and also get index performance?

How do we drop and create indexes?

Explain the use of execute sql task?

Lab 37 :- Page Split and Performance issues with SSIS.(SSIS)
25:06

How does Aggregation improve performance?

How do you know your MDX queries are using aggregations?

Explain QuerySubcube  and progress event?

In Query Subscube you see 00, 00, 000010 , what does it mean?

How do you know Cache was used during MDX query?

Which are the 2 ways of creating aggregations?

Differentiate between default ,full ,unrestricted and None aggregation usage?

Does aggregation affect storage space?

What does performance gain 30% mean in Aggregation wizard?

Explain the four rules which are applied in default aggregation usage?

Differentiate between Default and Unrestricted?

Put down three best practices you will follow while creating aggregations?

What is the importance of Indexbuildthreshold?

If you want to create aggregation as per work load , what should you use?

Explain QueryOlapLog table and sampling rate concept?

Lab 38 :- Aggregations in SSAS.(SSAS)
49:32

What is the importance of ROLAP , MOLAP and HOLAP?

From where do you set these settings?

MOLAP fetched data from ____ and aggregation from

ROLAP fetched data from ____ and aggregation from

HOLAP fetched data from ____ and aggregation from

When we change OLAP modes does it apply to the existing partitions?

What are notifications in ROLAP, MOLAP and HOLAP?

Which of the OLAP does not have notifications?

Scheduled MOLAP runs every __ hrs.

Medium Latency MOLAP runs every __ hrs.

Low latency MOLAP run every __ hrs

Explain how automatic MOLAP works?

Lab 39 :- ROLAP, MOLAP and HOLAP.(SSAS)
23:58

What is the need of data taps?

Differentiate between data viewer and data taps?

Differentiate between debugging and instrumentation?

Explain the importance of identification string in data taps?

In which path does the data tap dump data?

Lab 40 :- Instrumentation using Data Taps (SSIS).
15:24

What are the different caching modes in Lookup control?

How can you test your caching is working or not (HINT – Profiler)?

Differentiate between NO cache,Full cache and Partial cache?

You have small set of lookup Data which type of cache is good?

You have large set of Data and you want to use cache which mode is good?

Explain Cache transform component?

What is the scope of OLEDB Cache , Is it at Data flow level or Control flow level?

What is benefit of using Cache file?

Lab 41:- Lookup caching modes and Cache Transform. (SSAS)
28:58

What is the importance of perspectives?

How can we create perspectives?

Are perspectives security, if not explain Why?

How can we create perspectives in SSAS?

Is perspective “SIMPLIFICATION” or “SECURITY”?

What is translations in SSAS?

Explain LCID format?

How to create a translation of en-uk?

Translation also translates CUBE data, true and false?

Lab 42: - Perspectives & Translations. (SSAS)
13:33

Installation of Tabular

Xvelocity and Vertique

BISM thought process

Cube creation and processing

Partioning, KPI and translation

Basics of DAX

Displaying tabular data using Excel.

Lab 43 :- Tabular Training 1 :- Installation, Xvelocity, Vertipaq, DAX,Creating
55:51
+ MSBI Common errors in the course
2 lectures 00:53

Can not find SQL Server 2017 / 2014

Can not find SQL Server 2017 / 2014
00:27

Error message says :- You cannot deploy the model because the localhost deployment server is not running in multidimensional mode.

You cannot deploy the model because localhost deployment server is not running
00:25