MSBI INTERVIEW QUESTIONS AND ANSWERS

1. Differentiate between Union all and Merge Join?
Answer:

  • Merge transformation can take only two inputs whereas Union all can take more than two inputs.
  • Data has to be sorted before Merge Transformation whereas Union all doesn’t have any foundation like that.

2. What is the control flow?
Answer: A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements: Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.

3. How to organize the Report?
Answer: Report can be organized in three ways :

  • Using visual studio: In visual studio, we can directly deploy the report through solution explorer by providing the report server URL in project properties at Target Server URL. This will organize the entire project or single report as per our selection.
  • Using the report server: We can directly go to the report server and deploy the report by looking at the report from the disk location on the server.
  • Creating the utility: SQL server provides the utility which can be used to create a modify utility for our report deployment in bulk.

4. What is the data flow?
Answer: Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

5. Differentiate between SSRS 2005 and SSRS 2008?
Answer: The major differences between SSRS 2005 and SSRS 2008 are as follows:
SSRS 2005 report server requires IIS, whereas SSRS 2008 comes with a built-in web server and hence does not require IIS.

6. Which programming language can be used to code embedded functions in SSRS?
Answer: Visual Basic .NET Code is used to code embedded functions in SSRS.

7. What languages are used in SSAS?
Answer:

The languages used are:

  • Structured Query Language (SQL)
  • Multidimensional Expressions (MDX) – an industry-standard query language orientated towards analysis
  • Data Mining Extensions (DMX) – an industry-standard query language oriented toward data mining
  • Analysis Services Scripting Language (ASSL) – used to manage Analysis Services database objects.

8. Name the tools used in MSBI?
Answer:
Microsoft BI contains the following tools:

  • SQL Server Analysis Services (SSAS)
  • SQL Server Integration Services (SSIS)
  • SQL Server Reporting Services (SSRS)
  • Performance Point Services (PPS)

9. What are the three stages of Enterprise Reporting Life Cycle?
Answer:

  • Management
  • Authoring
  • Access and Delivery

10. What do you mean by sub reports and how we can create them?
Answer: A subreport 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 sub report and basis of that report can be created. 

11. In which SQL Server type report builder introduced?
Answer: Report builder presented in SQL Server 2005. While creating or arranging report model project on report server we can get an error or it might not get formed. For this, we need to check whether the service pack 22 is installed or not.

12. Define the report model project?
Answer: Report model project is used for creating Adhoc reporting. We can create the Adhoc reports over report builder. Report model project can be created on bids or report server. This model can have a simple view.
Progress execution by practicing Linux software invasion and striping over four extents.

13. What is the name of the reporting services config file and what it is used for?
Answer: Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config exists in ISS.

14. What do you understand by the chart in the report?
Answer: Chart reports are for graphical representation. We can get pie charts columns harts and various other options. 3d charts are also presented in reporting services.

15. Explain report builder?
Answer:  Report builder is used to creating small reports and it is a defined interface. We can’t change the report interface in report builder it pre-designed. We can just drag columns in the report. It creates reports on database objects available with report model project.

16. Define RS.exe utility?
Answer:Rs.exe utility is used for organizing the report on the report server. It comes with the report server and can be modified accordingly.

17. S is it conceivable to communicate with MSMQ?
Answer: Yes, it is possible for that we have the Message Queue task. It contracts us send messages to MSMQ and receive a message from MSMQ.

18. What is processing?
Answer: Processing is a critical and resource-intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services offers high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

19. What is the advantage of using MSMQ?
Answer: MSMQ helps in communicating two applications with each other asynchronously. Specialty is two application may be built using dissimilar technology and it works even offline messaging. The sender will stock messages inside the queue and the reader reads it wherever required.

20. What is the environment variable in SSIS?
Answer:  An environment variable configuration sets a package property equal to the value in an environment variable.

Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.

21. What are the drawbacks of reporting in SSRS?
Answer: For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports.

Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the reporting process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.

22. Define Execution Tree?
Answer: Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees.

23. If you want to create a calculated member that intersects all measures, where do you put it and why?
Answer: You would put it in a dimension other than Measures because a member in a dimension cannot intersect its own relatives in that dimension.

24. Name the business analysis enhancements available for SSAS?
Answer: The table below shows the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also lists the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

  • Enhancement Type Applied to No data source
  • Time Intelligence Cube Cube No
  • Account Intelligence Dimension Dimension or cube No
  • Dimension Intelligence Dimension Dimension or cube Yes
  • Custom Aggregation Dimension Dimension (unary operator) or cube No
  • Semiadditive Behavior Cube Cube Yes
  • Custom Member Formula Dimension Dimension or cube No
  • Custom Sorting and Uniqueness Settings Dimension Dimension or cube Yes
  • Dimension Writeback Dimension Dimension or cube Yes

25. Explain the architecture of SSIS?
Answer: SSIS architecture involves four key parts :

  • a) Integration Services service: It monitors running Integration Services packages and accomplishes the storage of packages.
  • b) Integration Services object model: It contains a managed API for reading Integration Services tools, command-line utilities, and custom applications.
  • c) Integration Services runtime and run-time executables: It keeps the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions.
  • d) Data flow engine: It provides the in-memory buffers that move data from source to destination.
  • Download MSBI Interview Questions asked by top MNCs in 2018 GET PDF

26. What Is The Use Of Data Source View?
Answer: Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source.

27. What is the three different part of RDL file elaborate on them?
Answer: In visual studio, RDL files has three parts.

Data: It covers the dataset on which we write the query. Data set is associated with a data source.
Design: In design, we can design a report. We can create tables and matrix reports. We Drag columns values from source.
Preview: It is used to check the preview after the report run. (Company)

28. Differences Between Calculated Members And Calculated Measures?
Answer: Member is nothing but column on dimension table. The measure is nothing but a column on the fact table.

The term calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the measures dimension where a simple MDX expression such as addition or subtraction of two or more base measures results in a new measure. Such calculated members on the measure dimension are referred to as calculated measures.

Calculated Member is related to the Dimension whereas calculated Measure is related to the measure in the measure group.

29. Elaborate Web service task in SSIS?
Answer: First we configure HTTP Connection manager which will point to WSDL of a web service.
Web service task customs this HTTP Connection manager and let us invoke methods in it.
It returns values of method value we can store it in some variables and can use as input for some other tasks.

30. What are the different ways of creating Aggregations?
Answer: We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO – Usage-Based Optimizations. Always, prefer UBO method in realtime performance troubleshooting.

31. What is the web service used for reporting services?
Answer: Reporting Service Web Service used in SSRS. By retrieving this web service we can access all report server section and also get the report organized on the report server.

32. What do you understand by report server project?
Answer: Report Server Project comprises of RDL file and it needs to be deployed on report server to view the report files to application and user. It is a solution where we can design our reports. We can add it by going into BIDS clicking on a new item and then selecting reports server project. Once the solution is formed we can start forming reports.

33. How to send an SSRS report from SSIS?
Answer: Often there is a requirement to be able to send an SSRS report in Excel, PDF or another format to different users from an SSIS package once it has finished performing a data load. In order to do this, first, you need to create a subscription to the report. You can create an SSRS report subscription from Report Manager. At the report subscription, you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

34. What are the Transformations available in SSIS?
Answer: AGGREGATE – It applies aggregate functions to Record Sets to produce new output records from aggregated values.

  • AUDIT – Adds Package and Task level Metadata – such as Machine Name, Execution Instance, Package Name, Package ID, etc..
  • CHARACTER MAP – Performs SQL Server column level string operations such as changing data from lower case to upper case.
  • CONDITIONALSPLIT– Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
  • COPYCOLUMN – Add a copy of the column to the output we can later transform the copy keeping the original for auditing.
  • DATA CONVERSION – Converts columns data types from one to another type. It stands for Explicit Column Conversion.
  • DATAMININGQUERY– Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
  • DERIVEDCOLUMN – Create a new (computed) column from given expressions.
  • EXPORTCOLUMN– Used to export an Image specific column from the database to a flat-file.
  • FUZZYGROUPING– Used for data cleansing by finding rows that are likely duplicates.
  • FUZZYLOOKUP – Used for Pattern Matching and Ranking based on fuzzy logic.
  • IMPORTCOLUMN – Reads image specific column from a database onto a flat-file.
  • LOOKUP – Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.
  • MERGE – Merges two sorted data sets into a single data set into a single data flow.
  • MERGE JOIN – Merges two data sets into a single dataset using a join junction.
  • MULTICAST – Sends a copy of supplied Data Source onto multiple Destinations.
  • ROWCOUNT – Stores the resulting row count from the data flow/transformation into a variable.
  • ROWSAMPLING – Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
  • UNIONALL – Merge multiple data sets into a single dataset.
  • PIVOT– Used for Normalization of data sources to reduce anomalies by converting rows into columns
  • UNPIVOT– Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses.

35. Define cache in SSRS?
Answer: Report server can lay up a copy of the processed report in memory and return the copy when a user opens the report. This server memory is called as cache and the process is known as caching.

36. Explain Reporting Life Cycle?
Answer: The Reporting Lifecycle includes – Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition. – Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time.

– Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by the output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.

37. How to finetune Reports?
Answer: To tune-up, the Reporting Services, follow the below mentioned ways: – Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. – Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.

38. What is SSIS? How it is related with SQL Server?
Answer: SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.

This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.

39. What is the data flow?
Answer: Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Data Flow task is the executable within the SSIS package that creates, orders and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Three important categories in the data flow are:

  • Data Sources
  • Transformations
  • Data Destinations

40. What is a workflow in SSIS?
Answer: Workflow is a set of instructions to tell the Program Executor how to execute tasks and containers within SSIS Packages.

41. What is SSRS?
Answer: SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and a variety of printed reports. It is administered through an interface that is web-based. Reporting services utilizes a web service interface for supporting and developing customized reporting applicatons. It can compete with Crystal Reports and other business intelligence tools.

42. What Is MOLAP?
Answer: MOLAP – Multidimensional OLAP.

This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances performances. In the latter, joins are computed during the request.

Targeted at groups of users because it’s a shared environment. Data is stored in an exclusive server-based format. It performs more complex analysis of data.

43. Differences Between Script Task & Script Component And Its Advantages?
Answer :
A script task is a control flow level item whereas script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.

Normally we can implement custom code by using these components. By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well.

44. What is the difference between WHERE and HAVING clauses in SQL Server?
Answer: HAVING clause can be used only with a GROUP BY clause, whereas a WHERE clause can be used with constructs such as SELECT, UPDATE, DELETE, etc.

HAVING clause is applied as a filter to the data/output resulting from the GROUP BY clause, whereas a WHERE clause is applied to every row in the SELECT, UPDATE, DELETE, etc constructs.

In queries where both HAVING and WHERE clauses are used, WHERE clause is applied first (to every row in the SELECT statement to filter the records before they are fed to GROUP BY clause for aggregation) and then the HAVING clause is applied (to filter the aggregated result from GROUP BY clause).

45. Explain SSRS Architecture?
Answer: Reporting services architecture comprises of integrated components. It is a multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components: – Report Manager, Reporting Designer, Browser Types Supported by Reporting Services, Report server, Report server command-line utilities, Report Server Database, Reporting Services Extensibility, Data sources that are supported by Reporting Services. 

46. MSBI Characteristics?
Answer:

  • Criteria Result
  • Analytics Dashboards guided navigation and drill down
  • Type of Solution End-to-end Business solution with extended ETL
  • User-friendly Ease of installation, use, maintenance
  • Get a detailed understanding of the advantages of MSBI over other business intelligence tools.

47. Can we debug SSIS package? If Yes, How?
Answer: You can debug a Package by Setting Breakpoints on a Task or a Container

To set breakpoints in a package, a task, or a container follow below-

  • In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
  • Double-click the package in which you want to set breakpoints.

In SSIS Designer, do the following:

  • To set breakpoints in the package object, click the Control Flow tab, place the cursor anywhere on the background of the design surface, right-click, and then click Edit Breakpoints.
  • To set breakpoints in a package control flow, click the Control Flow tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • To set breakpoints in an event handler, click the Event Handler tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.
  • In the Set Breakpoints dialog box, select the breakpoints to enable.
  • Optionally, modify the hit count type and the hit count number for each breakpoint.
  • To save the package, click Save Selected Items on the File menu.

48. Name the differences between view and materialized view?
Answer: 

  • A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
  • A View can be used to simpl
  • if SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
  • In addition to operating on base tables, one View can be based on another. A view can also JOIN a view with a table (GROUP BY or UNION).

Materialized Views:

  • Materialized views are schema objects that can be used to summarize, pre-compute, replicate, and distribute data. E.g. to construct a data warehouse.
  • A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
  • The existence of a materialized view is transparent to SQL, but when used for query rewrites, it will improve the performance of SQL execution.

49. What important terms can be used in the reporting services?
Answer: 

  • Report definition: A report definition contains information about the query and layout for the report.
  • Report snapshot: A report snapshot is actually a report definition that contains a dataset instead of query instructions.
  • Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).
  • Parameterized report: A published report that accepts input values through parameters.
  • Shared data source: A predefined, standalone item that contains data source connection information.
  • Shared schedule: It is a predefined, standalone item that covers schedule information.
  • Report-specific data source: Data source information which is defined within a report definition.
  • Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.
  • Linked report: It is a report that derives its definition through a link to another report.

50.How many types of protection level in SSIS package?
Answer: It is a package level property. It is used to specify how sensitive information is saved inside the package. It also specifies whether to encrypt the package or the sensitive portions of the package.

Each SSIS component designates that an attribute is sensitive by including Sensitive=”1″ in the package XML. When the package is saved, any property that is tagged with Sensitive=”1″ gets handled per the ProtectionLevel property setting in the SSIS package.

The ProtectionLevel property can be selected from the following list of available options (click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties):

  • DontSaveSensitive
  • EncryptSensitiveWithUserKey
  • EncryptSensitiveWithPassword
  • EncryptAllWithPassword
  • EncryptAllWithUserKey
  • ServerStorage

Note: Browse latest  MSBI interview questions and MSBI tutorial. Here you can check  MSBI Training details and  MSBI Videos for self learning. Contact +91 988 502 2027 for more information.

Related Posts

Leave a Comment