Learn MSBI , SSIS , SSRS and SSAS Step by Step
- 23 hours on-demand video
- 2 articles
- 24 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.
Try Udemy for Business- 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.
- Basic knowledge of SQL would help.
- No past knowledge of BI or MSBI needed.
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?
- 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.
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.
This lab is a upgrade lab in which we will understand MSBI installation in detail so that you complete the course without issues.
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.
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 ?
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 ?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?