Top MSBI Interview Questions and Answers
Here you will find the top 30 MSBI interview questions that are most widely asked in MSBI interviews at top organizations. Some of the important aspects you will learn include reporting services constituents, matrix in SSRS, Report Server Projects, Report Builder, SSIS architecture, and more. Learn MSBI from Omni Academy’s MSBI Certification Training and fast-track your career!
Top Answers to MSBI Interview Questions
1. What is MSBI? What are the tools that MSBI comprises?
As per definition, MSBI or Microsoft Business Intelligence is a powerful suite of tools that provide solutions for Business Intelligence and Data Mining queries. MSBI empowers users to gain access to accurate and up-to-date information for better decision-making in an organizational level. There are three tools that constitute the bulk of MSBI:
| SSIS | SSAS | SSRS |
| SQL Server Integration Services or SSIS is basically leveraged for integrating data from multiple databases together. This tool can deal with huge amounts of data and is therefore used for large transactions. | SQL Server Analysis Services or SSAS is mostly used to analyze the performance of SQL Server when it comes to load balancing, heavy data, and transactions, among other uses. This tool is mostly related to the administration of SQL Server. | SQL Server Reporting Services or SSRS pertains to report generation. Being platform-independent, this tool can be used across numerous applications and is therefore quite popular in market these days. |
2. 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.
3. What are the tools associated with SSIS?
SSIS tools support both the simplest solutions—in which users perform tasks such as copying data from one location to another—and the enterprise-level solutions, in which users develop a large number of complex packages in a team environment.
- Business Intelligence Development Studio (BIDS)
- To design and create new packages and data source objects and views that the packages use
- To modify the existing packages
- To debug package functionality
- To create the deployment bundle that is used for deploying packages
- SQL Server Import and Export Wizard
- This is the simplest way to create Integration Services packages. These packages can extract data from a variety of data sources, such as Excel spreadsheets, flat files, and relational databases, and load the data into similar data stores.
- SSIS Designer and SSIS Menu
- SSIS Designer shows tabs for each designer and also the tab for the package content. It is a graphical tool for developing packages.
- When you open an Integration Services project in BIDS, the SSIS menu is added to the menu bar. When you select the Work Offline option, Integration Services skips the aspect of package validation that makes a connection to data sources and other external components.
- Control Flow Designer and Event Handlers
- The package control flow can be constructed using Control Flow Designer.
- An Event Handler is a workflow that runs in response to an event that the run time raises.
4. What is a workflow? What do you mean by a control flow and a data flow?
A workflow is basically a set of instructions that is shared with the Program Executor as a guideline to execute tasks and containers.
A control flow mostly consists of one or more tasks and containers that execute when a package is run. To define conditions for running tasks in the package control flow, precedence constraints are used that connect the tasks and containers in a package.
There are three control flow elements that SSIS provides:
- Containers that provide structures in packages
- Tasks that provide functionality
- Precedence constraints that connect the executables in an ordered control flow
A data flow includes sources and destinations that extract and load data, transformations that modify and extend data, and paths that link sources, transformations, and destinations. The data flow task is executable within SSIS packages that create, order, and run the data flow.
5. 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.
6. Define environmental variables in SSIS.
The configuration of environmental variables sets package properties equal to the value in an environment variable. These are used to configure properties that are dependent on computers that execute packages.
7. What are different lookup cache modes available in SSIS?
Different lookup cache modes in SSIS are as follows:
- Full cache mode
- Partial cache mode
- No cache mode
8. Explain the architecture of SSIS.
SSIS architecture involves four key parts:
- Integration Services: It monitors running Integration Services packages and accomplishes the storage of packages.
- Integration Services object model: It contains a managed API for reading Integration Services tools, command-line utilities, and custom applications.
- Integration Services run time and runtime executables: It keeps the layout of packages, runs the packages, and provides support for logging, breakpoints, configuration, connections, and transactions.
- Dataflow Engine: It provides in-memory buffers that move data from source to destination.
9. 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.
Interested in learning MSBI? Enroll in Omni Academy’s MSBI Training!
10. How do you deploy SSIS packages?
The SSIS project, ‘Build’ offers a deployment manifest file. Users are required to run the manifest file and decide whether to deploy it onto a file system or onto Microsoft SQL Server database. The SQL Server deployment is fast and is also relatively secure than the file system deployment. Alternatively, this deployment can be done from packages in SSMS from the file system or SQL Server.
11. How can we organize SSIS packages on production?
There are three ways to do so:
- Through Manifest:
- Create a deployment utility by setting its properties as true. It will be formed in the bin folder of the solution.
- Once the package is built, copy all the files in the utility and use the manifest file to display it on the product.
- Using the DTExec.exe utility
- By importing the package directly into Microsoft database from SQL Server Management Studio (SSMS) by logging into Integration Services.
12. Define query parameters in SSRS.
Query parameters are revealed in the query of data sources that are to be included in the SQL WHERE clause for accepting parameters. Query parameters begin with the symbol ‘@’.
13. What are variables? Define the variable scope.
Variables are used to store values that SSIS packages and their containers, tasks, and event handlers leverage at the run time. Scripts can also use variables. Additionally, precedence constraints that sequence tasks and containers into a workflow use variables when constraint definitions include expressions.
Integration Services supports two types of variables: user-defined variables and system variables.
14. Define SSRS and also talk about the SSRS architecture.
One of the server-based software systems, SQL Server Reporting Services (SSRS) generates reports developed by Microsoft. It is used for preparing and delivering a variety of interactive printed reports and is administered through an interface that is web-based. It utilizes a web service interface for supporting and developing customized reporting applications.
The architecture of SSRS comprises integrated components. This multi-tiered architecture is scalable and modular. A single installation can be used across multiple computers.
Various components of SSRS architecture include Report Manager, Report Designer, the browser types supported by Reporting Services, Report Server, Report Server command-line utilities, Report Server Database, Report Services Extensibility, and the data sources that are supported by Reporting Services.
15. Explain the Reporting Life Cycle in SSRS.
SSRS reporting life cycle includes several aspects:
- Report designing: It is done in Visual Studio Report Designer. It generates a class that embodies the report definition.
- Report processing: This brings the report definition from the report data source. It performs all grouping, sorting, and filtering calculations.
- Report rendering: This stage starts by passing the Report Instance to a specific rendering extension that needs to be in HTML or PDF formats. The expressions of items are evaluated in the header and footer sections for every page. Finally, the report is rendered to a specific output document.
16. When shall we use a null data-driven subscription?
Users should first create a data-driven subscription that uses the Null Delivery Provider. When the Null Delivery Provider is specified as the method of delivery in the subscription, the Report Server targets the Report Server Database as the delivery destination and uses a specialized rendering extension called the null rendering extension. Different from the other delivery extensions, the Null Delivery Provider does not have delivery settings that can be configured through a subscription.
17. 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.
18. Define Report Model Project.
Report Model Project is used for ad-hoc reporting. We can create ad-hoc reports with Report Builder. Having a simple view, Report Model Project can be created on BIDS or Report Server.
19. What do you mean by Report Server Project?
Report Server Project comprises RDL files, and it needs to be deployed on Report Server to view the report files for applications and users. It is a solution where users can design reports. Once the solution is formed, users can start designing reports.
20. Define RS.exe Utility.
RS.exe utility is used for organizing reports on Report Server. It comes with the Report Server and can be modified accordingly.
21. What are the three different parts of RDL files?
In Visual Studio, RDL files have three parts:
- Data that covers the dataset on which queries are written
- Design that creates tables and matrix reports
- Preview that is used to check the preview after a report is run
22. What are the different data sources used in SSRS?
Several data sources are used in SSRS for ingesting data:
- OLEDB
- SQL Server SAP NetWeaver BI
- Oracle
- Report Server Model
- SSAS OLEDB
- ODBC
- Hyperion
- Teradata
- XML
23. Differentiate between Tabular and Matrix Reports.
| Tabular Report | Matrix Report |
| A tabular report is the most basic type of reports. | A matrix report is a cross-tabulation of four groups of data. |
| Each column corresponds to a column selected from the database. | One group of data is displayed across the page.Another group of data is displayed down the page.The third group of data is the cross-product.The fourth group is displayed as ‘filler’ of the cells. |
24. How to send SSRS Reports from SSIS?
In order to do this, users need to create a subscription to the report. This can be done with the Report Manager. The report format and the email address of the recipient can be mentioned at Report Subscription. When a schedule is created for the SSRS report, an SQL Server Agent Job will be created. From SSIS, using sp.start_job, the relevant job name can be passed and, henceforth, the SSRS report subscription can be executed.
25. What is SSAS? List out its key features.
The middle-tier server for analytical processing, OLAP, and data mining, SSAS manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. Analysis services provide a combined view of data that is used in OLAP or data mining.
Key features of SSAS are:
- Ease of use with a lot of wizards and designers
- Flexible data model creation and management
- Scalable architecture to handle OLAP
- Integration of administration tools, data sources, security, caching, reporting, etc.
- Extensive support for custom applications
26. What is the significance of UDM in SSAS?
Unified Dimensional Model (UDM) acts as a bridge between users and data sources.
Benefits of UDM in SSAS:
- Enriches the user model
- Provides high-performance queries supporting interactive analysis
- Allows business rules to be captured in the model to provide better analysis
27. What is the architecture of SSAS?
SSAS has a 2-tier architecture. Below are the key features of SSAS architecture:
- Both server and client components supply OLAP and data mining functionalities to BI applications.
- Server components are implemented as individual Microsoft Windows Services.
- Clients communicate with Analysis Services using the standard XMLA (XML for Analysis).
28. What languages are used in SSAS?
There are four languages that are used in SSAS. They are:
- Structured Query Language (SQL)
- Multidimensional Expressions (MDX) for analysis
- Data Mining Extensions (DMX) for data mining
- Analysis Services Scripting Language (ASSL) for managing Analysis Services database objects
29. How are cubes implemented in SSAS?
Cubes are multidimensional models that store data and aggregations from one or more sources. They are created using the Cube Wizard. Also, dimensions are built while creating cubes.
30. What is writeback? What are its preconditions?
The writeback dialog box enables or disables writebacks for a measure group in a cube.
Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group.
Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table to avoid unanticipated data loss.
Related Courses -Learn Online Now!
Power Bi Advanced Course (BootCamp)
Microsoft Office ( Complete Course )
Ms Office – Advanced Excel with Outlook
Oracle BI Training 11g R1 – Create Analyses and Dashboards
Oracle DBA 11g/12c Training
