MSBI interview Questions

Let's look at the rundown of most frequently asked MSBI Interview Questions -

How is SSIS related to SQL Server?

SQL Server Integration Services is a component of SQL Server in MSBI. This tool can be used to perform numerous data migration and ETL operations.

This platform is used for integration and workflow applications. Known for its quick and flexible OLTP and OLAP extensions for data extraction, transformation, and loading (ETL), SSIS can also be used to automate the maintenance of SQL Server databases and multidimensional datasets.

Mention the errors in SSIS and explain how error handling works.

There are three types of major errors in the SSIS landscape, namely:

Data connection error: This type of error occurs when the Connection Manager cannot be initialized with the connection string.
Data transformation error: This error occurs while the data is being transformed over a data pipeline.
Expression evaluation error: This type of error occurs if the run-time evaluated expressions are invalid.

When the column data is transformed, extracted from sources, or loaded into a destination, errors occur.

Procedure errors are handled in the control flow by the precedence control and by redirecting the execution flow, and data errors are handled in the data flow task by redirecting the data flow using the error output of a component.

How can we do logging in SSIS?

SSIS includes logging features that not only write log entries when run-time events occur but also write custom messages. Integration Services supports a diverse set of log providers and empowers users with the ability to create custom log providers. Integration Services log providers can write log entries into text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. The logging configuration provides an in-built feature that can log the details of various events like OnError, OnWarning, etc.

What do you mean by matrix in SSRS? What are sub-reports and how do we create them?

A matrix is a data region that is related to a report set. Matrices permit us to create crosstab reports with the report variables showing on rows and columns.

A sub-report is like any other reports which can be termed in the main report and can be generated through the main report. Parameters can be conceded from the main report to the sub-report, and on basis of that a report can be created