MSBI QUESTIONS AND ANSWER

SQL SERVER INTEGRATION SERVICES (SSIS)

1. What is BI?

Business Intelligence is a process of converting data into information.

2. What are Data and Information?

Data can be a different format like XML, CSV, SQL server. Meaningful collection of data is called as information.

3. What is SSIS, SSAS, and SSRS?

SQL Server Integration Services:

• SSIS performs ETL activity.
• It collects information and data. Data can be a different format like XML, CSV, SQL server.

SQL Server Analysis Services:

• SSAS performs Non-ETL activity.
• Analysis means calculation data like sum, count, etc.

SQL Server Reporting Services:

• SSRS helps us to fetch data from the cube that is from SSAS also fetch the data from the SQL server.

4. What is ETL?

Extract, Transform and Load (ETL) is the process of extracting the data from various sources, transforming this data to meet your requirement and then loading into a target data warehouse.

5. How to see errors?

First click on the progress tab then go to the first red sign in the progress tab and check the errors.

6. Explain the Full life cycle of BI.

• Data can be a different format like XML, CSV, SQL server.
• When data is identified it is transformed into a common data type using the ETL process.
• After that data is loaded into Data Warehouse.
• When data is analyzed from the data warehouse it is stored in the Cube.
• In the cube, we generate a report and give it to the end-user in the format of Information, Reporting, etc.

7. Difference between SQL Server and SQL Server Data Tool

SQL Server is a standard language for RDBMS. It is used for the database.

SQL Server Data Tool is a tool used for building models Analysis Services data models, Integrated Services packages and Reporting Services reports.

8. What is a data warehouse?

• Data Warehouse handles a large amount of data.
• It is denormalized and maintains historical data
• It makes the search and analysis faster by using the OLAP system.

9. Difference between varchar and Nvarchar?

• Varchar and NVarchar is a Variable length datatype.
• NVarchar stores Unicode characters. Unicode represents letter English, Greek, etc.
• Varchar stores ASCII code. ASCII represents the lower case, uppercase, digit, and symbols.

10. What is a dtsx file?

• A dtsx stands for data transformation services.
• In SQL Server Moving data from source to destination that concept called dtsx.

11. Explain data flow vs control flow?

Control Flow:

• Control flow invokes data flow. Control flow performs Non-ETL activity.
• A control flow consists of one or more tasks and containers that execute when the package runs.

Data Flow:

• Data flow perform ETL activity.
• Data flow transfer data from source to destination.

12. Explain the structure of the project?

• Solution → Project → Package.
• The SSIS project structure in a hierarchical format. The package belongs to the project and the project belongs to a solution.
• In solution have multiple projects, The project has multiple packages.

13. What are the Dimension and Fact tables?

Dimension table:

• A Dimension table also part of the star or snowflake schema.
• It contains dimensions of a fact.
• In dimension table, data in a string format.

Fact table:

• The fact table also called a measure table. It present at central of the star or snowflake schema surrounded by the dimension table.
• The fact table mostly has foreign keys to the dimension table.
• In Fact Table, data in a number format.

14. What is the need for SCD?

• SCD stands for slowly changing dimensions. SCD helps to synchronize data between the source to a destination only for dimension.
• SCD used to insert and update records in the dimensional table.

15. What is a business key in SCD?

• Business key compares incoming source data with existing destination dimension table data
• It is value not to change in source and destination.

16. Explain Type 0, Type 1 and Type 2?

Type 0: Fixed Attribute

• If the value changes then show error through the red arrow.

Type 1: Changing Attribute:

• This method overwrites the old data with the new data.
• It is used to correct data errors in the dimension.
• It does not store historical data.

Type 2: Historical Attribute:

• Audit and history are maintained of last changed value.
• It can store unlimited history.

17. What is inferred member in SCD?

• When data for the inferred member is loaded, it can update the existing record rather than create a new record.

18. Differentiate between DT_STR vs DT_WSTR?

• DT_STR contains Non-Unicode values. When data is in ANSI code then the SSIS package takes a datatype as DT_STR.
• DT_WSTR contains Unicode values. It can write multiple languages.

19. Difference between Snowflake and Star schema?

Star schema and Snowflake both are the denormalized technique.

Snowflake Schema-

• In the snowflake schema, there is a relationship between dimensions.
• A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions.

Star Schema-

• In a star schema, no relation between dimensions table.
• The center of the star can have one fact tables and a number of associated dimension tables.

20. Difference between For and For-each containers?

For loop container loops till a fixed count.

For-each loop containers load and traverse multiple files, folders, and records.

21. How to debug the SSIS package?

By adding a breakpoint we can debug the package.

• Right-Click on component
• Click on edit breakpoint
• Add breakpoint.

22. How to create variables?

• Right-Click on whitespace area
• Select the variable menu
• Give a name to the variable.

23. Differentiate between Merge, Merge join and Lookup?

Merge and Merge Join need to sort.

Merge: Merge is used to combine two records and produce one record.

Merge Join: It merges with a specific condition.

i. Left Join: It displays all the records from the left side table and only matching records from the right side table.

ii. Inner Join: It combines only matching records from tables.

iii. Full Outer Join: It combines all the records from both side table either they are matched or not.

Lookup: Lookup is used for comparing the data between two tables.

Lookup does not need to be sorted.

24. How do your transactions in SSIS?

The transaction helps us to logical group activities. So activities which are part of that logical group either all pass or all will fail.

To enable transaction we use sequence containers. Sequence containers group the component. Choose from these transaction options.

i. Required: Create his own new transaction.

ii. Supported: Joins the parent sequence container transaction.

iii. Not Supported: Does not participate in any transaction.

25. How to implement Checkpoints in SSIS?

Checkpoint Starts from the point where the things failed. If you want to Checkpoint first disable the transaction and choose the options.

i. Checkpoint FileName:- Specifies the filename of the checkpoint file.

ii. Checkpoint Usage:-

Never option will do not use to the checkpoint file.

IfExists option use to checkpoint file if exists. It is used to enabling checkpoints on a file.

Always option always uses the checkpoint file. If a file does not exist, the package fails.

iii. Save Checkpoint:- To enable checkpoints on the package.

Set the Fail Package On Failure and Fail Parent On Failure property to true. If the failure occurs then they inform a checkpoint.

26. What is the Multithreading in SSIS?

• Multithreading used to improve the performance of the SSIS package.
• Records are runs in a parallel manner so tasks executed in a minimum manner.

27. What is a Bulk insert?

• A bulk insert is a process to insert multiple rows of data into the database table.

28. What does a mean blue arrow and red arrow?

• Blue arrow indicates the actual data come out.
• The red arrow indicates it has an error inside.

29. How to convert one data type to another in SSIS?

• Data Conversion is a component of SSIS to convert data-type from one type to another type.
• For ETL operation we require data in the same format. By using the data conversion component we convert data in a proper format.

30. Sequence Container in SSIS?

The sequence container groups the multiple components in one logical transaction.

31. Component of SSIS Full Blocking, Semi Blocking, and Non-Blocking?

Full Blocking:

• In the Full blocking component, while performing activities full blocking blocks the current activity until all operations performed and then it sends the result to the next activity.
• Example: Sort.

Semi Blocking:

• In the Semi blocking component, while performing activities it blocks some of the parts of current activity and sends some part to the next activity.
• Example: Merge, Merge join, Unpivot.

Non-Blocking:

• In the Non-Blocking component, while performing activities it not block any part of the current activity.
• Example: Audit, Data Conversion, etc.

32. Buffer sizing in SSIS?

The default value is 10MB and its upper and lower boundaries are constrained by MaxBufferSize(100MB) and MinBufferSize(64KB).

33. What is Data Viewer?

Data viewer used for debugging purposes. It shows the data travels between two components.

34. Which component is used to update and delete in SSIS?

OLEDB Command is used to update and delete in SSIS.

35. Can destination component update and delete records?

NO, The destination component only inserts the record.

36. Explain the importance of redirect row, failed component and Ignore failure?

Redirect Rows: It returns the successful rows to the specified destination and failed rows to failed output.

Fail Component: If there is an error then the transformation, source, destinations, etc will fail.

Ignore Failure: It will ignore the row’s failures.

37. How did you handle errors in SSIS?

• SSIS allows a developer to create an error output through which error rows are directed from the red arrow.
• This red arrow can be connected as an input to another transformation component or destination component.

38. How to Run SSIS package through SQL Server Agent?

SQL Server Agent is a software that is provided by the SQL server.
Which helps to run any SQL job as a background process.

Steps to Run SQL Agent :

• SQL Agent →Start Create a new job
• Click on a new step for adding XMLA query
• Then add schedule means when the run cube exactly for eg. 5 min etc,
• Add new Role all SSAS security in this -It gives the permission
  Finally, Run the job.

39. How to load multiple files from a folder using SSIS?

Using the ForEach loop container, we load multiple files and folder using SSIS.

• Select ForEach loop container from SSIS ToolBox
• Create one variable
• Set Expression

40. What is a Multicast Transformation in SSIS?

• Multicast Transformation in SSIS sends input data to multiple destination paths without applying any conditions or transformations.
• Multicast Transformation, create multiple copies of outputs.

41. How to write a C# script in SSIS?

We can write a C# script in SSIS by using the Script Task component.

• Right-click on Script Task Component
• Click on Edit
• Goto the script
• It opens the new window
• In the Edit Script, we can write the C# code.

42. What is the importance of expressions in SSIS?

Expressions set the variable value to the connection string.

43. What is Condition split in SSIS?

• Conditional split is a component of SSIS. It is like if condition or case statement.
• Conditional split checks the condition and as per condition data can be pass to destination.

SQL SERVER ANALYTICAL SERVICES(SSAS)

1. What is the need of SSAS?

SSAS is about analysis. Analysis means calculations like sum, count or some complicated formula which does forecasting, analysis calculation.

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

Two ways of creating an SSAS project:

1. Analysis Services Multidimensional and Data Mining Project

2. Analysis Services Tabular Project

3. What is the use of Data Source and Data Source View?

Data Source: In Data source we define where is the RDBMS data.

Data Source View: Data source view helps to choose which table should participate in the SSAS project.

4. What is the use of the cube structure tab and browser tab?

Cube Structure:

Cube structure tab has all information related to the cube where it helps us to show Facts or Measures.

Browser Tab:

Browser tab: It helps us to show how the cube is created with measures and dimensions.

5. What is Cube?

Cube is precalculation which is to allow queries to return data quickly.

6. What is MDX?

• Multidimensional Expressions is a query language for online analytical processing using a database management system.
• The term axis used to avoid confusion with the cube dimensions.

7. What is the concept of Pivot and Unpivot?

Pivot: The pivot operator converts the rows data of the table into the column data.

Unpivot: Unpivot operator does the opposite that is it transforms the column-based data into rows.

8. Difference between OLTP and OLAP?

• OLTP and OLAP both are online processing systems.

OLTP:

• OLTP is an Online Transaction Processing system.
• OLTP system is to record the current Update and Deletion while transaction.
• A database is a normalized form(3NF).
• T-SQL language used for Query.

OLAP:

• OLAP is an Online Analytical Processing system.
• OLAP database stores historical data that has been inputted by OLTP.
• OLAP is used for faster searches.
• MDX is used for querying on OLAP cube.

9. What are the four things needed to define a KPI?

KPI define measure, progress, business goal, and activity.

KPI is a collection of calculations, which are associated with a measure group in a cube, that are used to evaluate business success.

10. In MDX cubes how can we get monthly and yearly fact values?

Using the Time Dimension Table, we get monthly and yearly fact values.

• Goto dimension usage tab in SSAS
• Right-Click to create a new dimension
• Complete the Wizard Process, Time Table is created.
• Check Time table is created in the SQL server or not.
• Goto cube and add a new Dimension table.
• In the browser tab, we can get monthly, yearly fact values.

11. what are the different relationship types of dimension usage in MDX?

Dimension Usage: Dimension Usage Tab in cube define the relationship between the cube dimension and measure.

Types of dimension usage:

• Regular Dimension Relationship
• Reference Dimension Relationship
• Fact Dimension Relationship
• Many to Many Relationship

12. Why do we see a truncation warning message?

A truncation warning message occurs when inserting data from the database column.

The datatype length of the source column is more than the length of the destination column.

13. How to process SSAS Many to Many dimensions in the Multi dimension?

• In a Many-to-Many relationship, A dimension is indirectly connected to a Measure Group through an intermediate fact table that joins with the dimension table.
• First, add the dimensions and measure
  Go to the dimension usage tab →select relationship (Many to Many Relationship) →After that process the cube.

14. Explain the new SSAS Hierarchies in MDX?

Date →Month →Quarter →Year

• Hierarchies are a useful tool in SSAS. It reduces complexity.
• Hierarchy is linked in a natural relationship like one-to-one or many-to-one. Like a Calendar hierarchy, Date level related to the month level, month level to the Quarter and so on.
• User-Defined Hierarchy: A user-defined hierarchy defines a relationship between hierarchy levels.
• Parent-Child Hierarchy: A parent-child hierarchy is formed by a single parent attribute that has a self-referencing relationship.
• Attribute Hierarchy: An attribute relationship basically moves from the lowest level of the hierarchy to the highest level.

15. Difference between Multidimensional and Tabular

Tabular:

• Tabular model read data from cached mode and Direct Query mode.
• In cached mode data loaded into memory. Direct Query mode data directly from the database.
• One-To-Many Relationship supported.

Multidimensional:

• In the Multidimensional model MDX query used and it works on the Fact table and Dimension table.
• In the Multidimensional model, Data access using ROLAP, MOLAP.
• One-To-Many and Many-To-Many Relationship supported.

16. What are ROLAP, MOLAP, and HOLAP?

ROLAP- Relational Online Analytical Processing.

• Data is stored and fetched from the data warehouse.
• Query performance is slow.

MOLAP- Multidimensional Online Analytical Processing.

• Data inside the cube. So Latency is high
• Query performance is fast.
• MOLAP is a precalculated.

HOLAP- Hybrid Online Analytical Processing.

• HOLAP is a storage type between MOLAP and ROLAP.
• Data is stored in ROLAP.

SQL SERVER REPORTING SERVICES (SSRS)

1. Can SSRS connect to different types of data sources?

Yes, SSRS connects with any type of data source like SQL Server, Cube, and XML.

2. What is the difference between the Report Server project wizard and the Report server project?

• Two templates in SSRS:- Report Server project wizard and Report server project.
• Report Server project wizard creates a new report server project using the report wizard.
• The report server project creates an empty report template.

3. Differentiate between Matrix and Tabular report?

• Tabular report display data in tabular format which is a row and column. Matrix report display summarized data.
• In matrix number of columns can change dynamically depending on input data.

4. Explain the Importance of the three sections in a report?

In a report it can have three sections:

i. Page (Header)- It display Page number, Name of company

ii. Group-It display Country or State wise report

iii. Details- It shows actual data of the report.

5. What is the importance of the RDL file?

RDL stands for Report Definition Language. The RDL file is nothing but an XML file and this XML file runs under the IIS web server.

6. What is the internal format of RDL?

The internal format of RDL file is XML

7. Explain the importance of the report data toolbar?

Report viewer provided readymade toolbar that reports data toolbar. Using toolbar we can print, import and export in various formats.

8. What does dataset and data source do in report data?

• Data source tell us where is the actual server is located.
• Data set stores MDX file. It tells us what kind of query fire.

9. How to create parameters in SSRS

STEP 1: Right-click on Dataset go to Query designer.

STEP 2: Next fill the field like dimension, Hierarchy, and Operator as per data.

STEP 3: Check the last parameter then click on OK.

10. How to do sorting and interactive sorting in SSRS

Sorting- If sort the column then right-click on column go to tablix properties click on sorting then add button as well select the column in order. Click on OK.

Interactive sorting- Right-click on the header column and go to textbox properties. After that go to interactive sorting and enable the interactive sorting on the textbox. Put the field name and OK.

11. Differentiate between expression and textbox properties

• The expression has the readymade formula. An expression is a value which evaluates the data which is display on a textbox.
• Text box properties use to UI behavior. By using textbox property we can change Font, Background color, Border, Fill, etc.

Start with MSBI video as mentioned below to learn topic practically in-depth: