Top MSBI Interview Questions And Answers
Watch this video on MSBI Tutorial for beginners
Top Answers to MSBI Interview Questions
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:
|SQL Server Integration Services or SSIS is basically leveraged for integrating data from multiple databases together. This tool can deal with huge amount 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.|
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.
SSIS tools support the simplest solutions, in which users perform tasks such as copying data from one location to another, to the enterprise-level solutions, in which users develop a large number of complex packages in a team environment.
- Business Intelligence Development Studio (BIDS)
- Design and create new packages and data source objects and views that the packages use
- Modify existing packages
- Debug package functionality
- Create the deployment bundle that you use to deploy 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 aspects of package validation that makes a connection to data sources and other external components.
- Control Flow Designer and Event Handlers Designer
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.
Workflow is basically a set of instructions which is specified to 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 to 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
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.
There are three types of major errors in the SSIS landscape, precisely:
- Data Connection Errors: This type of error occurs when connection manager cannot be initialized with the connection string.
- Data Transformation Errors: Data Transformation Errors occur while data is being transformed over a Data Pipeline.
- Expression Evaluation errors: This error type occurs if the run-time evaluated expressions are invalid.
When column data is transformed, extracted from sources, or loaded into a destination, errors occur.
Procedure Errors can be handled in 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.
The configuration of environmental variables sets package property equal to the value in an environment variable. These are used to configure properties that are dependent on computers that execute packages.
They are, namely:
- Full Cache Mode
- Partial Cache Mode
- No Cache Mode
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 run-time executables: It keeps the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions.
Data flow engine: It provides in-memory buffers that move data from source to destination.
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 to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logging configuration provides an inbuilt feature which can log the details of various events like onError, onWarning, etc.
The SSIS Project, ‘BUILD’ offers a Deployment Manifest file. Users are required to run the manifest file and decide whether to deploy it onto File System or onto SQL Server Microsoft Database. SQL Server Deployment is very fast and also is relatively secure than File System Deployment. Alternatively, deployment can be done from packages in SSMS from File System or SQL Server.
There are three ways to do so:
- Through Manifest:
- Create 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 manifest file to display it on the product
- Using DTExec.exe utility
- Import the package directly in Microsoft Database from SQL Server Management Studio (SSMS) by logging in Integration Services.
Query parameters are revealed in the query of data sources that are to be included in the SQL script’s where clause for accepting parameters. Query parameters begin with the symbol ‘@’.
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. Also, 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.
One of the server-based software systems, 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. Reporting services utilize a web service interface for supporting and developing customized reporting applications.
Reporting services architecture comprises integrated components. This multi-tiered architecture is scalable and modular. A single installation can be used across multiple computers.
Various components are Report Manager, Reporting Designer, Browser Types supported by reporting services, Report Server, Report Server Command line utilities, Report Server Database, Reporting Services Extensibility, and Data Sources that are supported by Reporting Services.
The Reporting lifecycle includes several aspects.
Report designing: It is done in Visual Studio Report Designer. It generates a class which embodies the report definition.
Report processing: This brings the report definition from the report data source. It performs on all grouping, sorting, and filtering calculations.
Report rendering: It starts by passing the Report Instance to a specific rendering extension which need to be in HTML or PDF formats. The expressions of items are evaluated in the header and footer sections for every page. As a final step, the report is rendered to the specific output document.
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, Report Server targets the Report Server Database as the delivery destination and uses a specialized rendering extension called the null rendering extension. Different from other delivery extensions, the Null Delivery Provider does not have delivery settings that can be configured through a subscription.
A matrix is a data region which 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.
Report model project is used for ad-hoc reporting. We can create ad-hoc reports over Report Builder. Having a simple view, the report model project can be created on BIDS or Report Server.
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 forming reports.
RS.exe utility is used for organizing reports on Report Server. It comes with the Report Server and can be modified accordingly.
In Visual Studio, RDL files have three parts:
- Data that covers the dataset on which queries are written
- Design which creates tables and matrix reports
- Preview which is used to check the preview after a report is run
Several data sources can be used in SSRS for ingesting data.
- SQL Server SAP NetWeaver BI
- Report Server Model
- SSAS OLEDB
|Tabular Report||Matrix Report|
In order to do this, users need to create a subscription to the report. This can be done from 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, relevant job name can be passed and, henceforth, SSRS report subscription can be executed.
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 which is used in OLAP or Data mining.
Key features are:
- Ease of use with a lot of wizards and designers
- Flexible data model creation and management
- Scalable architecture to handle OLAP
- Provides integration of administration tools, data sources, security, caching, reporting, etc.
- Provides extensive support for custom applications
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 support better analysis
SSAS has a 2-tier 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).
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
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.
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.