MSBI Interview Questions

Rating: 5
Views: 5958
by Ravindra Savaram
Last modified: April 24th 2021

Are you looking to get trained on MSBI, we have the right course designed according to your needs. Our expert trainers help you gain the essential knowledge required for the latest industry needs. Join our "MSBI Certification Training program" from your nearest city.

MSBI Interview Questions and Answers - Table of Content

  1. What are the tools used in SSIS?
  2. List out Various Data Sources used in SSRS.
  3. Define OLAP
  4. What are the most commonly used MDX Functions?
  5. What is the default code page of SSIS?
  6. What is FASMI?
  7. Name different types of connection managers?
  8. Explain a Query parameter in SSRS?
  9. Explain about processing?
  10. What is the importance of the SSAS component?

 

MSBI Interview Questions and Answers

Q1): Differentiate views and Materialized Views?

Ans:

1.Views

Views are used to visualize the query result as a virtual table. Views are used almost everywhere where a table is used.

Any operation performed on the view will directly impact the data of the base table, so we have to deal with the triggers and limitations of the base table.

Views are used for query simplification and to separate the application from future changes.

2. Materialized Views

Materialized Views are considered schema objects which can be used for summarizing, copying, and distributing the data.

It stores the results of a query in an individual schema object to provide implicit access to table data.

The Presence of Materialized view is apparent to SQL, however when we use it for query rewriting it enhances the efficiency of SQL execution.

Learn to analyze data sets, software programs and build a comprehensive bi solution by enrolling in our MSBI Training

Q2):  What are the tools used in SSIS?

Ans: SSIS tools are employed to do simple tasks like copying the data from one place to another place. SSIS Tools are also used to provide enterprise solutions in which users build complex packages. Various tools in SSIS are:

[Related Article: SSIS Tutorial for Experienced]

1) SQL Server Export and Import Wizard

This tool is used to build integration service packages. These packages are used to import or export data from various data sources like files, Excel spreadsheets, relational databases, etc.

2) Business Intelligence Development Studio(BIDS)

 This tool is used for creating new packages and changing the present packages. It is also used for troubleshooting the packages and for building the deployment kit used for package deployment.

3) SSIS Menu and SSIS Designer

SSIS Tool is used for building packages. While you open an integration service package in BIDS, the ssis menu is included in the menu bar.

Q3): Explain SSIS Architecture?

Ans: The SSIS Architecture has four important Parts

Integration Services Object Model: It includes a controlled API, which is used for learning custom applications, command-line utilities, and integration service tools.

DataFlow Engine: It offers in-built buffers which change from one place to another.

Integration Services: It checks the work of integration service packages, and carries out the storage of packages.

Integration Services runtime executables and run time: It maintains the layouts of packages, executes the packages, and assists with configurations, breakpoints, connections, etc.

Q4): List out Various Data Sources used in SSRS.

Ans: SSRS uses various data sources for importing and exporting the data; they are:

  • Oracle
  • ODBC
  • Teradata
  • Hyperion
  • XML
  • OLEDB

Q5):  How you can set up SSIS Packages on Production.

Ans: We can set SSIS Packages in three ways.

With the help of Manifest. Generate a deployment utility with the help of its features. It is generated in the bin folder of the solution. When the package is developed, utility files are copied and displayed on the product through the manifest file.

Through DTExec.exe utility, we can set up SSIS packages on production.

We can arrange SSIS packages on production by importing packages from SQL Server Management Studio to Microsoft Database.

[Related Article: SQL Server Tutorial]

Q6): Differentiate Truncate and DELETE Commands

Ans:

Truncate Delete
It is a DDL Command. It is a DML Command.
It deletes the data from the table without doing log entries for every row deletion. It deletes a particular record or all records and does log entries for every row deletion.
We cannot use WHERE Clause with Truncate Command In DELETE Command, we can use WHERE Clause.

Q7): Define OLAP

Ans: The full-form of OLAP is On-Line Analytical Processing. It comes under the applications that enable the collection, reproduction, and manipulation of Multidimensional data, with an objective of data analysis.

Q8): Mention the tools used in MSBI

Ans: The tools used in MSBI are as follows:

1. SQL SERVER ANALYSIS SERVICES(SSAS)

SQL Server Analysis Services is used in MSBI for sharing metadata, multidimensional analysis, security, speed, etc. It creates data mining and online analytical processing functionalities.

2. SQL SERVER INTEGRATION SERVICES(SSIS)

It is used in MSBI for implementing various data integration activities and gathering data from different data sources and saves them in a centralized location.

3. SQL SERVER REPORTING SERVICES(SSRS)

It is used in MSBI for reporting purposes. It contains processing components and two important processing components are as follows:

Processor: This component adds new functionality.

Extension: This component is used for indicating the processing functionality.

4. Performance Point services

MSBI uses these services to check and analyze the business. It is also used to make decisions that attain the Organization’s strategies and goals. It provides tools for building scorecards, dashboards, and key performance indicators.

Q9): What are the most commonly used MDX Functions?

Ans: The MDX Functions are used in MDX Queries or MDX Expressions. MDX Functions are Categorised into four types, they are:

Functions(red dot function): These functions return the object being referenced.

Function: These functions are regularly used in Dimension or cell.

Function(): These functions need parenthesis, but they will not take any arguments.

Function(arguments): These functions take arguments and return the result after performing the required operation on the arguments.

The Most Commonly used functions are as follows:

1. Cross join

The Crossjoin function returns all the combinations of the sets indicated by the arguments in the Crossjoin function.

2. ParallelPeriod

The ParallelPeriod function is a member function, so it returns the present member, parent, ancestor, children, etc. It retrieves a member based on the time dimension and some conditions.

3. Count and DistinctCount

The Count and DistinctCount are Numeric Functions. The count function computes the number of items present in a particular object like tuple, dimension, etc. The DistinctCount function takes a Set_Expression as an argument and returns a numerical value that specifies the number of individual(distinct) items Present in that Set_Expression.

Q10): What is a Matrix in SSRS? How we can create Sub-Reports?

Ans: A Matrix in SSRS is a data zone that is associated with the report set. Matrices allow us to generate cross-reference reports through the report variables displayed on rows and columns. 

Sub-Reports can be created from the Main reports. The Parameters present in the Main report are passed to the sub-report, and according to that, a report is generated.

Q11): What is the default code page of SSIS?

Ans: The default code page of SSIS is 1252(ANSI Latin). The code page, also recognized as a character set, is a collection of 256 uppercase plus lowercase letters, symbols, and numbers. The printable characters are the first 128 values are identical for all character collection choices.

Q12): Explain about control flow?

Ans: A control flow consists of one or more tasks plus containers that execute during the package runs. To control order or define those conditions as running the subsequent task or container within the package control flow, we apply precedence constraints to correlate the tasks also within a package. A subset from tasks, including containers, can be grouped plus run frequently as a unit inside the package control flow. SQL Server Integration Services (SSIS) gives three various sorts of control flow elements: Containers that give structures within packages, Tasks that give functionality, Precedence Constraints that connect the containers, executables, also tasks within an ordered control flow.

Q13): Explain about variables and variable scopes?

Ans: Variables save values that an SSIS package also its containers, event handlers, and tasks can utilize at run time. This script within the Script task also the Script component can utilize variables. The precedence constraints which sequence tasks, including containers within a workflow, can utilize variables during their constraint definitions add expressions. Integration Services maintains two types of variables: user-defined variables plus system variables. User-defined variables defined through package developers. Also, system variables are defined through Integration Services. You can build the desired number of user-defined variables as a package needs, but you can’t build further system variables.

Q14): What are the benefits of utilizing embedded code within a report?

Ans: There are two essential benefits of utilizing embedded code within a report they are:

  • Centralized code: centralized code assists us in managing the code in a better way.
  • Reusability of code: function built within the embedded code to obtain a logic can be then utilized within manifold expressions.

Q15): What is FASMI?

Ans: The database which satisfies the FASMI rules is called an OLAP database.

Fast Analysis: fast Analysis is defined within an OLAP situation in less than five seconds.

Shared: Have to maintain access toward data by various users within the parts of Sensitivity plus Write Backs. 

Multidimensional: The data within the OLAP Database remains to be multidimensional within a structure.    

Information: The OLAP database supports vast quantities of data.

Q16): What are the different types of parameters in SSRS?

Ans: There are three different types of parameters in SSRS they are:

  • Single value parameters
  • Multivalue parameters
  • Cascading parameters  

Q17): How does checkpoint work in a for loop?

Ans: The Foreach Loop container means a different atomic unit from work that can be restarted. But, the checkpoint file doesn’t hold data regarding the work finished by the child containers. Also, the Foreach Loop container plus its child containers run repeatedly whenever the package restarts.

Q18): How to manage the facts that arrive early?

Ans: Early Arriving Facts sometimes become unavoidable because lag or error within Dimension ETL or else may be due to logic of ETL. To manage Early arriving Facts, we can build a model Dimension including natural or business key plus retain the rest of the attributes as null or else default.

MSBI Developer Interview Questions

Q19): How does Error-handling work in SSIS?

Ans: Whenever a data flow element utilizes a transformation toward column data, plucks data from sources, or else stores data within targets, errors can happen. Errors generally happen because of random data values.

Q20): List the types of errors in SSIS?

Ans: There are three types of errors in SSIS they are

  • Data transformation errors
  • Data connection errors
  • Expression evaluation errors

Q21): Name different types of connection managers?

Ans:

  • OLE DB Connection Manager
  • ODBC Connection Manager
  • Flat file connection Manager
  • SMTP connection Manager
  • Excel Connection Manager

Q22): Explain about the cube?

Ans: A basic unit of Analysis and storage within the analysis services is called the cube. A cube is a set of data that is aggregated to permit Queries and to return data fastly. Cubes are arranged into dimensions plus measures come from the fact table.

Q23): Explain a Query parameter in SSRS?

Ans: Query parameters are exposed within the query of the data sources which are to be involved in this SQL script where clause of the SQL which can receive parameters. Query parameters start with the @ symbol. 

Q24): What is the transfer SQL server object task?

Ans: It permits us to designate different SQL server objects among various cases of SQL Server. Object incomes from the table, saved procedures, user-defined functions and so on.

Q25): Explain about processing?

Ans: Processing is an essential and resource-intensive operation within the data warehouse lifecycle and requires to be significantly optimized plus executed. Analysis Services 2005 gives high performance plus scalable processing architecture, including a complete collection of controls as database administrators.

Q26): How to achieve parallelism in SSIS?

Ans: Parallelism is accomplished utilizing MaxConcurrentExecutable stuff like the package. Its default is -1, and it is calculated as the amount/number of computers + 2.

Q27): Explain about OLAP?

Ans: OLAP is abbreviated as On-Line Analytical Processing. It is a kind of application and technology that permits the storage, collection, manipulation, and also reproduction of multidimensional data, including the aim of that Analysis.

Q28): Is it possible to deploy SSRS reports on our website?

Ans: Your reports can just be deployed upon a reporting services site. Your sole choice for seeing them from different sites signifies an HTTP link. Any tools, like SharePoint, give controls permitting you to see reports within the context of the various websites, though the report is yet deployed to plus received from reporting services.

Q29):  What is the importance of the SSAS component?

Ans: Analysis Services is the unique component within SQL Server utilizing which we can perform Analysis plus Forecast operations.

SSAS is very simple to utilize and interactive.

More agile Analysis plus Troubleshooting.

Capacity to build and maintain Data warehouses.

Use effective Security Principles.

Q30): Do we need any report server to run reports in our applications?

Ans: In enhancement to publishing reports over a report server, you can create reports utilizing the Report Designer which is instantly integrated, including Visual Studio language projects. You can insert reports right in some Windows Forms or else ASP.NET Web application without access on a report server. The data access within inserted reports means a natural extension of that Visual Studio data facilities. Not only can you utilize regular databases as a root of data as your reports, but you can utilize object collections as well.

These courses are equipped with Live Instructor-Led Training, Industry Use cases, and hands-on live projects. Additionally, you get access to Free Mock Interviews, Job and Certification Assistance by Certified MSBI Trainers

MSBI Training Hyderabad, MSBI Training Bangalore