MSBI interview Questions and Answers
auto_adminComments off.
1) Define SSIS and how is it related to SQL Server?
SSIS or SQL Server Integration Service is SQL Server component that is used to perform a number of operations that may be data migration or ETL types of operations. In the MSBI process, SSIS is basically a component that is used by the SQL Server.
SSIS platform is used for workflow integration and applications. In this platform, OLAP and OLTP are quite faster that can be used for data transformation, extraction, and loading. This SSIS tool can also be used to automate the maintenance process of multidimensional datasets and SQL Server databases.
2) Define Control Flow in MSBI tools?
Control flow consists of more than one container and tasks that are executed along with the package execution. We can control the order of execution for containers and tasks of package control flow and for this, we prefer precedence constraints through which we can connect tasks and containers of a package. Even a subset of any container and task can also be regrouped repeatedly as a unit. SSIS or SQL Server Integration Service basically provides three types of containers that can provide package structures, tasks to provide functionality and precedence constraints to connect executable containers and tasks.
3) Explain data flow in SSIS.
Data flow involves sources of data along with its destination to load and extract data, a transformation that can extend and modify data along with paths that can link sources, transformation, and destinations. Data flow engine is an executable process of SSIS that can create, order, and run data flow. For each data flow task, a separate data flow engine is opened in the package. Hence, we can say that in the case of data flow data source, transformation, and data destinations are three important categories.
4) Explain error handling in SSIS.
In SSIS, data flow execution error may occur while fetching data from the source or in case of transformation or loading data to the destination. Here, the reason for the errors is unexpected data values while performing these operations. Typically following types of errors can occur in SSIS:
Data Connection Error: When the connection manager cannot be initialized, this type of error occurs. These errors can occur at data source and destination both as it is basically associated with the connection string and both of them use connection string for establishing a connection.
Data Transformation Error: When data is transformed over the data pipeline, this type of error can occur. Secondly, when data flows from source to destination then this error occurs.
Expression Evaluation Error: If any of the runtime expression execution fails then this error occurs.
5) Enlist and explain SSIS transformation and their purpose.
SSIS provides the following transformations as discussed below:
AUDIT: It is used to add task and package level metadata like package name, machine name, execution instance or package ID.
Aggregate: Through this aggregate function is applied to the record set and new output from these aggregated values is produced
Conditional Split: Separate input values are distributed among separate output pipelines as per the Boolean value of the expression that is configured for each output
Data conversion: It is used to convert column data types from one form to another
Character Map: It can perform column level string operations like to change data from lower case to upper case
Data Mining Query: It is used to perform data mining queries against managing controls and analysis services
Fuzzy Lookup: Is used to match the pattern on the basis of Fuzzy Logic
Merge: It can merge two sorted datasets into a single data asset or data flow
Multicast: Is used to send a copy of the supplied data source to multiple destinations.
6) How can an SSIS package be deployed?
There is a Deployment Manifest File that is provided by SSIS Build. This manifest file is run to perform deployment either onto the file system or to SQL Server. SQL Server deployment is quite faster and secure than file system deployment. So, packages can be imported from SQL Server, File System or SSMS.
7) How can you log into SSIS execution?
When a run-time event occurs then log entries are being written through SSIS logging features. This is not enabled by default. A diverse set of log providers are supported by SSIS and the user can also create a custom log provider. Here log entries are written to text files, SQL Server, SQL Server Profiler or XML Files. Logs are usually configured at the package level as they are associated with packages. Task and containers can be enabled for logging.
8) What is variable and the scope of variables?
The value that can be used by SSIS package containers, tasks, and event handlers at runtime are stored in the variables of the SSIS package. Script task and script components are used by these variables. Moreover, precedence constraints can be used to sequence tasks and containers into a workflow.
Usually, two types of variables are used by these integration services, one is user-defined variables and other is system variables. User-defined variables are basically defined by the package developers, while system variables are defined by the integration services. The user can define and use as many as user-defined variables, while there is only a certain number of system variables that can be used and they cannot be created.
9) Name any five perform counters for SSIS and what value they provide?
SQL Server: SSIS Service
BLOB bytes read
Buffer Memory
Flat buffer memory
Flat Buffer in use
10) What is the query parameter in SSRS?
Query parameters are included in SQL script data sources. They begin with the symbol @.
11) What is subreport and how are they created?
Subreport is just like other reports that can be termed in main reports and even generated through that as well. Main report parameters can be inherited to subreports and a report can be created in this way.
12) Define the UDM or Unified Dimensional Model and its significance in SAAS.
UDM or unified dimensional model provides a bridge between data sources and user and is built with the help of one or more data sources. End user issues queries through various client tools by using UDM, these client tools may be Microsoft Excel. Following benefits are offered by UDM:
The user model is enriched greatly.
Even for huge data volumes, it can provide high-performance queries and can support interactive analysis too.
Business rules can be captured in the model and they usually support the richer analysis.
13) Explain the need for SAAS component.
With the following facts, you will quickly understand the need for SAAS components
SSAS in an interactive and easy to use component,
Troubleshooting and faster analysis can be performed,
A data warehouse can be easily managed and created,
Efficient security principles can be applied,
Analysis and forecast operations can be performed by analysis services.
14) Explain two-tier SSAS architecture?
Both client and server components are used to provide data mining functionality for BI applications and to supply OLAP
The client can use analytics services by using XMLA protocol to receive a response, issue commands, and can be exposed as a web service
The server component is used as Microsoft Windows service and each analysis service implement as a separate instance of windows service.
15) What are the SSAS components?
Following components are SSAS components:
Dimensional database components can be used in OLAP
Drilling is the process to explore data details
Slicing means to place data in rows and columns
Pivoting means to switch data categories between rows and columns
Posted in: news