Msbi Real Time Interview Questions And Answers

Are you looking for Interview Questions on Msbi for Experienced or Fresher? You are at accurate place. There are loads of chances from various reputed corporations in the business. According to the analysis, Msbi has a business share of around 24.5%. So, you, however, have a chance to move forward in your career in Msbi Development. To achieve the desired job as Msbi Developer you need to go through the advanced topic in Msbi Interview Questions 2018 provided by SVR Technologies.

Top 50 Msbi Real Time Interview Questions And Answers

Companies across the globe are frequently choosing Business Intelligence devices to apprehend data and investigate it for cleverer decision making Msbi online training. That has begun up variable possibilities for Business Intelligence experts and the requirement for experienced BI practitioners is tremendous. There has never been a valid time to initiate work in the Business Intelligence space! Familiarizing yourself with Microsoft Business Intelligence, the administration of BI tools can support you pack best jobs in Business Intelligence. Here post list the topmost Microsoft Business Intelligence interview questions to encourage you to prepare for your job interview later mastering Microsoft BI.

Here is genuine 50 objective model sample MSBI Interview questions and their answer are presented merely under the questions itself. Certain sample questions are composed of masters from SVR Technologies who train for Msbi Training to provide you a concept of the type of questions which may be proposed in an interview. We have taken extensive care to provide accurate answers to all the questions.

1. What are the differences between TRUNCATE & DELETE clauses in SQL Server?
Answer: TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.

TRUNCATE removes all the records from a table without making a log entry for individual row deletions whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.

TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (optional) is used or not used respectively

TRUNCATE cannot be used on a table if it satisfies one of the following conditions:

  • The table is referenced by one of more FOREIGN KEY constraints
  • The table is marked/enabled for replicationTRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITy.

2. What are the Command-Line Utilities available in Reporting Services?
Answer:  Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file.

RsKeymgmt Utility: it is used to Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access.

3. Name different types of data sources in SSRS?
Answer:  SSRS uses a different data source. Some of them are listed below.

  • SQL Server SAP Net weaver BI.
  • Report Server Model.
  • SQL Server Analysis Service OLEDB.
  • SAP Net weaver BI.
  • XML

4. What are the differences between DTS and SSIS?
Answer: Data Transformation Services SQL Server Integration Services

  • Limited Error Handling Complex and powerful Error Handling
  • Message Boxes in ActiveX Scripts Message Boxes in .NET Scripting
  • No Deployment Wizard Interactive Deployment Wizard
  • Limited Set of Transformation Good number of Transformations
  • NO BI functionality Complete BI Integration.

5. What are the benefits of using embedded code in a report?
Answer: The benefits are:

Reusability of Code: function created in embedded code to achieve a logic can be then used in manifold expressions
Centralized code: it helps in the better manageability of code.

6. How we can schedule an SSIS package?
Answer: Using SQL Server Agent we can schedule an SSIS package.

7. What Are Variables And What Is Variable Scope?
Answer: Variables store values that an SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables. 

8. Can You Name Five Of The Perfmon Counters For Ssis And The Value They Provide?
Answer:

  • SQLServer: SSIS Service
  • SSIS Package Instances
  • SQLServer: SSIS Pipeline
  • BLOB bytes read
  • BLOB bytes are written
  • BLOB files in use
  • Buffer memory
  • Buffers in use
  • Buffers spooled
  • Flat buffer memory
  • Flat buffers in use
  • Private buffer memory
  • Private buffers in use
  • Rows read
  • Rows wrote.
  • S Utility: this utility is mainly used to automate report server deployment.

9. How to create Drill-Down reports?
Answer: By grouping data on essential fields

Then toggle reflectivity based on the grouped filed.

10. What is MSBI (Microsoft Business Intelligence?
Answer: Microsoft Business Intelligence (BI) is a suite of products and tools that you can use to monitor, analyze and plan your business by using scorecards, dashboards, management reporting, and analytics.

11. What are the tools in MSBI?
Answer: It contains the following tools:

  • SQL Server Analysis Services (SSAS)
  • SQL Server Integration Services (SSIS)
  • SQL Server Reporting Services (SSRS)
  • Performance Point Services (PPS) which was added as a free service in Microsoft Office SharePoint 2010.

12. How we can create Drill-through reports?
Answer: By Using Navigation property of a cell and scenery child report and its parameters in it we can create Drill-through reports.

Progress execution by practicing Linux software invasion and striping over four extents.

13. While creating a new calculated member in a cube what is the use of a property called non-empty behavior?
Answer: Nonempty behavior is an important property for ratio calculations. If the denominator is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then The expression is also treated as empty and no error is returned. 

14. Explain transfer SQL Server object task?
Answer:  It allows us to allocate different SQL server objects between different instances of SQL Server.Object incomes from the table, stored procedures, user-defined functions, etc.

15. Name a few Business Analysis Enhancements for SSAS?
Answer:  The following table lists the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also shows 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.

16. Where do you put calculated members?
Answer: The reflexive answer is “in the Measures dimension” but this is the obvious answer. So I always follow up with another question. “If you want to create a calculated member that intersects all measures, where do you put it?” A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.” If they can answer it, I immediately ask them why. The answer is “Because a member in a dimension cannot intersect its relatives in that dimension.”

17. What Are The Types Of Parameters In Ssrs?
Answer:

The types of parameters in SSRS are:

  • Single value parameters
  • Multivalue parameters
  • Cascading parameters.

18. What Is Hyperion? Is It An Olap Tool?
Answer: It’s Business Intelligence tools. Like Brio which was an independent product bought over my, Hyperion has converted this product name to Hyperion Intelligence.

Yes. You can analyze data schemas using these tools.

19. What Is The Difference Between Olap And Etl Tools?
Answer: OLAP: It’s an online analytical processing tool. There are various products available for data analysis.

ETL: Extract, Transform and Load. This is a product to extract the data from multiple/single source transform the data and load it into a table, flat file or simply a target.

20. How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?
Answer:  Simply using bottom count will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

21.What Is Processin?
Answer: Processing is a critical and resource-intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 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.

22. How in MDX query can I get top 3 sales years based on order quantity?
Answer: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy” and “OrderByAttribute”. Let us say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];
Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

23. How do you extract the first tuple from the set?

Answer: Use could usefunctionSet.Item(0)
Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]

24. What are check point and how they work?
Answer:  SSIS 2005 included a feature called checkpoints, which allows you to restart the package if it fails for any reason. During package execution, the last completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

You need to set three package properties:

CheckpointFileName. For this property, you need to provide a path and filename for the checkpoint file. If you plan to keep checkpoints implemented when you put a package into production, it’s a good idea to use a Universal Naming Convention (UNC) path.

CheckpointUsage. This property has three possible values: Never, Always, and exists. The default is Never, which prevents checkpoint creation. When you specify the Always option, the package uses the checkpoint file if it exists. If it doesn’t exist, the package fails. Therefore, the Always option isn’t recommended for a package in production because the package shouldn’t be failing regularly. (A package failure is the only way a checkpoint file gets created. Once the package completes successfully, the checkpoint file is removed.) The best option to use is IfExists. When you select this option, the package uses the checkpoint file if it exists. If it doesn’t exist, the program starts from the beginning of the package.

SaveCheckpoints. This property must be set to True. Otherwise, the previous settings won’t have any effect. By default, it’s set to False.

25. 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 2005 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.

26. How checkpoint works in for loop?
Answer: The Foreach Loop container is another atomic unit of work that can be restarted. However, the checkpoint file does not contain information about the work completed by the child containers, and the Foreach Loop container and its child containers run again when the package restarts.

27. What Is A Master Package?
Answer: the Master package is nothing but it is also a normal SSIS package, it acts as a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called a master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.

28. How we can handle Early Arriving Facts?
Answer: Early Arriving Facts sometimes get unavoidable because delay or error in Dimension ETL or may be due to logic of ETL. To handle Early arriving Facts, we can create a dummy Dimension with natural/business key and keep the rest of the attributes as null or default.

29. Explain the two-tier architecture of SSAS?
Answer: ASSAS uses both server and client components to supply OLAP and data mining functionality to BI Applications.
The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services is implemented as a separate instance of the Windows service.
Clients communicate with Analysis Services using the standard XMLA (XML for Analysis) protocol for issuing commands and receiving responses.

30. How does error-handling work in SSIS?
Answer: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

The types of typical Errors in SSIS are:

Data Connection Errors, which occur in case the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.
Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.
Expression Evaluation errors, which occur if expressions that are evaluated at runtime perform invalid

21.What Is Processin?
Answer: Processing is a critical and resource-intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 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.

22. How in MDX query can I get top 3 sales years based on order quantity?
Answer: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy” and “OrderByAttribute”. Let us say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];
Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

23. How do you extract the first tuple from the set?

Answer: Use could usefunctionSet.Item(0)
Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]

24. What are check point and how they work?
Answer:  SSIS 2005 included a feature called checkpoints, which allows you to restart the package if it fails for any reason. During package execution, the last completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

You need to set three package properties:

CheckpointFileName. For this property, you need to provide a path and filename for the checkpoint file. If you plan to keep checkpoints implemented when you put a package into production, it’s a good idea to use a Universal Naming Convention (UNC) path.

CheckpointUsage. This property has three possible values: Never, Always, and exists. The default is Never, which prevents checkpoint creation. When you specify the Always option, the package uses the checkpoint file if it exists. If it doesn’t exist, the package fails. Therefore, the Always option isn’t recommended for a package in production because the package shouldn’t be failing regularly. (A package failure is the only way a checkpoint file gets created. Once the package completes successfully, the checkpoint file is removed.) The best option to use is IfExists. When you select this option, the package uses the checkpoint file if it exists. If it doesn’t exist, the program starts from the beginning of the package.

SaveCheckpoints. This property must be set to True. Otherwise, the previous settings won’t have any effect. By default, it’s set to False.

25. 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 2005 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.

26. How checkpoint works in for loop?
Answer: The Foreach Loop container is another atomic unit of work that can be restarted. However, the checkpoint file does not contain information about the work completed by the child containers, and the Foreach Loop container and its child containers run again when the package restarts.

27. What Is A Master Package?
Answer: the Master package is nothing but it is also a normal SSIS package, it acts as a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called a master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.

28. How we can handle Early Arriving Facts?
Answer: Early Arriving Facts sometimes get unavoidable because delay or error in Dimension ETL or may be due to logic of ETL. To handle Early arriving Facts, we can create a dummy Dimension with natural/business key and keep the rest of the attributes as null or default.

29. Explain the two-tier architecture of SSAS?
Answer: ASSAS uses both server and client components to supply OLAP and data mining functionality to BI Applications.
The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services is implemented as a separate instance of the Windows service.
Clients communicate with Analysis Services using the standard XMLA (XML for Analysis) protocol for issuing commands and receiving responses.

30. How does error-handling work in SSIS?
Answer: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

The types of typical Errors in SSIS are:

Data Connection Errors, which occur in case the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.
Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.
Expression Evaluation errors, which occur if expressions that are evaluated at runtime perform invalid

31. What are the Reporting Services components?
Answer: Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report server is used to execute and distribute reports. a report manager is used to manage the report server.

32. SQL Server Reporting Services vs Crystal Reports?
Answer: Crystal reports are processed by IIS while SSRS have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSRS is available for Report history snapshots. Crystal reports have standards and user-defined field labels. SSRS allows the only user-defined field labels.

33. What is Report Builder?
Answer: Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

34. How does Report Builder support Analysis Services cubes?
Answer: Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

35. Difference B/w Cube-deploy And Process?
Answer: Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual SQL server analysis server. This process is called DEPLOYMENT of a cube. (like creating one object(table) in the relational database).

So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table) So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to an SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube. 

36. Difference between Logical Page a Physical Page in SSRS?
Answer: Logical page breaks are page breaks that you insert before or after report items or groups. Page breaks help to determine how the content is fitted to a report page for optimal viewing when rendering or exporting the report. The following rules apply when rendering logical page breaks: Logical page breaks are ignored for report items that are constantly hidden and for report items where the visibility is controlled by clicking another report item.

Logical page breaks are applied on conditionally visible items if they are currently visible at the time the report is rendered. Space is preserved between the report item with the logical page break and its peer report items. Logical page breaks that are inserted before a report item push the report item down to the next page. The report item is rendered at the top of the next page. Logical page breaks defined on items in table or matrix cells are not kept. This does not apply to items in lists.

37. How we can achieve parallelism in SSIS?
Answer: Parallelism is achieved using MaxConcurrentExecutable stuff of the package. Its default is -1 and is calculated as the number of computers + 2.

38. How do users use Report Builder with SQL Server data sources?
Answer: While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio-based development shell.

39. How do I get Report Builder to generate a parameter that can be set by users viewing the report?
Answer: In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

40. Define Query parameter in SSRS?
Answer: Query parameters are revealed in the query of the data sources that are to be included in the SQL script’s where clause of the SQL that can accept parameters. Query parameters begin with the symbol @.

41. What do you understand by the matrix in SSRS?
Answer: A matrix is a data region related to a report set. Matrix permits us to create crosstab reports with the report variables showing on rows and columns. It allows us to drag.

42. Do I need a report server to run reports in my application?
Answer: In addition to publishing reports to a report server, you can build reports using the Report Designer that is directly integrated with Visual Studio language projects. You can embed reports directly in any Windows Forms or ASP.NET Web application without access to a report server. The data access in embedded reports is a natural extension of the Visual Studio data facilities. Not only can you use traditional databases as a source of data for your reports, but you can also use object collections as well.

43. Can you import Microsoft Excel data to SSRS?
Answer: Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you’re asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You’ll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.

44. Can we deploy SSRS reports on our website?
Answer: Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint, offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services.

45. What is the need for SSAS component?
Answer: Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations.

  • SSAS is very easy to use and interactive.
  • Faster Analysis and Troubleshooting.
  • Ability to create and manage Data warehouses.
  • Apply efficient Security Principles.

46. How we can restart the package from previous failure point? Define Checkpoints and how they are implemented in SSIS?
Answer: When a package is arranged to use checkpoints, material about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs effectively, the checkpoint file is deleted, and then re-created the next time that the package is run. Learn more about MSBI in this MSBI Certifications Training to get ahead in your career!

47. Will running SSRS on Windows XP limit the number of users?
Answer: Yes, but not because of SSRS. The Internet Information Services (IIS) component of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS, this would prevent more than a few people from using SSRS at once. Also, the only edition of SSRS that will install on Windows XP is the Developer Edition. This edition can not be used for production use. You need Standard or Enterprise Edition for production use, which requires a Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).

48. Define SSIS. How it is related with SQL Server?
Answer: A component of SL Server, SQL Server Integration Services (SSIS) can be used to perform a variety of Data migration and ETL operations. SSIS is a platform for Integration and Workflow applications which are known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). It can also be used to automate maintenance of SQL Server databases and multidimensional data sets.

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

50. How we can do Logging in SSIS?
Answer: Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, warning, etc.

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.

Leave a Comment

FLAT 30% OFF

Coupon Code - GET30
SHOP NOW
* Terms & Conditions Apply
close-link