MSBI Interview Questions

Explain MSBI ?

Ans: MSBI means Microsoft Business Intelligence. It is one of the ETL tools which provides the best solution for BI and Data Mining Queries. Using BI, you can plan and analyze your business with dashboards, reporting analytics, and scorecards. It uses SQL Server and Visual studio to provide various tools for different processes in BI. BI provides information by converting and processing the data. Data and information seem to be similar, but both are different. 

There are three tools in MSBI  

• SSIS – SQL Server Integration Services
• SSRS – SQL Server Reporting Services
• SSAS – SQL Server Analytical Services

What is the procedure to log SSIS executions?

Ans: Well, on the off chance that your SSIS Package is sent utilizing Project Deployment Model and its SSIS Package is executed inside SQL Agent JOB at that point play out the accompanying strides to send out sign in Excel format.

Open the SSMS (SQL Server Management Studio).

Go on to the node that is given here

 (Select Your Server then select Integration Services Catalog then select SSISDB, the option of your folder then move on to your project and finally selectYourPackag).

The next step is to Right-click on Package then on Reports then on Standard Reports then on All executions as is given in the below image.

Now when the Report is visible to you the next step is to go and click on "All messages" option that is given there

In the Report go and do a right-click on Export then Excel-like below.

Explain SSIS Architecture?

What do you gather by the term Analysis Services?

Ans: Analysis Services is a logical information motor (Vertipaq) utilized in choice help and business examination. It gives undertaking grade semantic information models for business reports and customer applications, for example, Power BI, Excel, Reporting Services reports, and other information perception apparatuses. Analysis Services is accessible in various stages, for example, SSAS, Azure Analysis Services, and so on.

Explain the term Unified Dimensional Model and its importance in MSBI.

Ans: Unified Dimensional Model (UDM) goes about as an extension among clients and information sources.

It is important because of it-

• Enriches the client model
• Provides superior inquiries supporting the intuitive investigation
• Allows business rules to be caught in the model to give better investigation

Briefly explain the architecture of SSAS.

Ans: SSAS has a 2-level engineering architecture. The following are the key highlights of SSAS design:

• Both the server and customer parts supply OLAP and information mining functionalities to BI applications.
• Server parts are actualized as individual Microsoft Windows Services.
• Clients communicate with Analysis Services utilizing the standard XMLA (XML for Analysis). 

What is FASMI?

Ans: FASMI is the acronym for Fast analysis, shared, multidimensional, information. It is required to be complied with in order to see whether a database is called an OLAP Database or not. This is because if the database fulfills the FASMI rules then only it is considered to be OLAP.

The functions of the four wings of FASMI are-

• Fast Analysis–is characterized in the OLAP situation in five seconds or less.
• Share – Must help access to information by numerous clients in the variables of Sensitivity and Write Backs.
• Multidimensional – The information inside the OLAP Database must be multidimensional in structure.
• Information – The OLAP database Must help enormous volumes of information.

Explain MSBI partitioning.

Ans: Partitioning is where you partition your information from one coherent unit into isolated physical lumps. This can have a few favorable circumstances, for example, improved execution or simpler upkeep. You can for instance segment a table in a SQL Server database, however, you can likewise segment your measure bunches inside an Analysis Services (SSAS) Multidimensional 3D cubes.

Related Article: MSBI Incremental load in SSIS

What Are The Roles Of An Analysis Services Information Worker?

Ans: Security in Analysis Services is overseen by utilizing jobs and consents. Jobs are gatherings of clients. Clients, additionally called individuals, can be included or expelled from jobs. Consents for objects are determined by jobs, and all individuals in a job can utilize the articles for which the job has authorizations. All individuals in a job have equivalent authorizations to the items. Consents are specific to objects. Each item has a consents assortment with the authorizations allowed on that object and various arrangements of consents can be conceded on an article. Every authorization, from the consents assortment of the article, has a solitary job allocated to it.

. Explain the concept of writeback and its preconditions.

. What is processing?

Ans: Preparing is a basic and asset escalated activity in the information distribution center lifecycle and should be painstakingly enhanced and executed. Investigation Services offers a superior and adaptable preparing engineering with a thorough arrangement of controls for database administrators. We can process an OLAP database, singular shape, Dimension, or a particular Partition in a cube.

MSBI Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

. What MDX functions do you most commonly use?

Ans: The Most Commonly utilized functions are as per the following:

1. Cross join

The Crossjoin work restores all the mixes of the sets demonstrated by the contentions in the Crossjoin work.

2. ParallelPeriod

The ParallelPeriod work is part work, so it restores the current part, parent, predecessor, youngsters, and so on. It recovers a part dependent on the time measurement and a few conditions. 

3. Count and DistinctCount

The Count and DistinctCount are Numeric Functions. The check work figures the number of things present in a specific item like tuple, measurement, and so forth. The DistinctCount work accepts a Set_Expression as contention and returns a numerical worth which indicates the quantity of individual(distinct) things Present in that Set_Expression.

. How to send SSRS Reports from SSIS?

Ans: SSIS can send SSRS report in various arrangements like Excel, PDF, and so on. This is accomplished by making a reported membership utilizing the Report director. In the reported membership, the organization of the SSRS report can be referenced alongside the email address of the beneficiary. sp_start_job is utilized to execute the reported membership.

[ Related Article : incremental load ]

. Differentiate between a Matrix Report and a Tabular Report.

Ans: A Matrix is fundamentally the same as a table, however, it is designed to show information assembled by sections and columns, with total information at the convergences. A portion of the information you may hope to find straight can turn into a section header. This is like utilizing a rotate table in a spreadsheet.

The table and matrix data areas can show complex information connections by including settled tables, lattices, records, graphs, and checks. Tables and frameworks have an unthinkable design and their information originates from a solitary dataset, based on a solitary information source. The key contrast among tables and networks is that tables can incorporate just line gatherings, whereas frameworks have push gatherings and segment gatherings.

. What are the DAX functions? Explain the different DAX functions of MSBI.

Ans: This function reference of MSBI gives a point by point data including linguistic structure, parameters, Return esteems, and models for every one of the more than 200 capacities utilized in Data Analysis Expression (DAX) recipes. Some DAX elements of MSBI are

. What is the benefit of using SQL commands in an OLEDB source?

Ans: The OLE DB Command change runs a SQL articulation for each line in an information stream. For instance, you can run a SQL explanation that supplements, refreshes, or erases push in a database table.

You can design the OLE DB Command Transformation in the accompanying manners:

• Give the SQL proclamation that the change runs for each line.
• Determine the number of seconds before the SQL explanation times out.
• Determine the default code page.

Commonly, the SQL command incorporates parameters. The parameter values are put away in outer segments in the change info, and mapping an information section to an outside segment maps and info segment to a parameter.

Want to know more about MSBI,visit here MSBI Tutorial.Want to know more about MSBI,visit here MSBI Tutorial.Want to know more about MSBI,visit here MSBI Tutorial.

. What is Impersonation?

. List out various Data Sources used in SSRS

Ans: SSRS uses many data sources to import and export data. They are:

• Microsoft SQL Server
• Oracle
• SAP BW
• Hyperion Essbase
• Microsoft SharePoint List
• Teradata
• OLE DB
• ODBC
• XML

.Mention the tools used in MSBI

Ans: In MSBI, there are three essential tools they are:

• SSIS – SQL Server Integration Services
• SSRS – SQL Server Reporting Services
• SSAS – SQL Server Analytical Services

SSIS – SQL Server Integration Services: it helps integrate the data arriving from various sources in the data warehouse. Saves the data using ETL (extract, transport, load) process at a centralised location. SSIS usually carries out bulk transactions. 

SSRS – SQL Server Reporting Services: the primary function is Making Reports. It produces reports in tabular, charts, and many other forms. SSRS is an efficient and platform-independent tool.

SSAS – SQL Server Analytical Services: it helps in analysing and sharing metadata, speed, security, multidimensional analysis, etc. it is similar to an SQL administration server. SSAS analyses data before updating it into the database. SSAS carries out analytical processing and data mining functions.

. Explain control flow?

Ans: Control flow is a workflow of tasks to be executed in order. A Control Flow contains more than one task and containers executed during the package run. Control Flow can be created using the Control Flow tab in SSIS Designer, and SSIS provides three types of control flow elements.

Tasks -which provide functionalities

Containers - which provide structures in packages

Precedence Constraints - which connect containers, tasks within an ordered control flow, and executables.

Nesting of containers is supported in integration service architecture. Multiple levels of nested containers are included in the control flow. For example, a box contains a box inside, and that inside box also contains another box. And so on

. How does a checkpoint work in a for loop?

Ans: Checkpoint stores the failed task in the package While performing multiple tasks in a package. If a task is failed in the checkpoint and we begin the package, then the checkpoint helps us to begin the failed task from the last point in the checkpoint file. When the task is succeeded, the checkpoint will automatically erase the task.

There are four types of checkpoints in the database they are :

• Automatic
• Indirect
• Manual
• Direct.

Subscribe to our YouTube channel to get new updates..!

Subscribe

. Differentiate views and Materialised Views?

Ans:

View

• A view is technically a virtual logical copy of the table created by query.
• It does not store the resulting tuples of the query. It only stores the query expression
• Storage cost is not associated with views.
• SQL views have a standard architecture approach and design. A view has SQL standard definition.

Materialised view

• Materialised views are the stored results of a view expressed in a database system.
• The resulting tuples of the query and the query expression are stored in the database in the materialized view.
• Storage cost is not associated with materialised views.
• It does not have any SQL standard definition for a materialised view.

. How can we do logging in to SSIS?

Ans: SQL Server Integration Services contains log providers to execute logging in tasks, packages, and containers. By logging, we can write custom messages, capture runtime information, troubleshoot packages, assist in auditing, etc. SSIS contains different log providers with which users have complete freedom to create specific log providers.

By the following steps, we can configure logging in to SSIS

• For logging, we must enable the package and respective tasks.
• Choose a suitable log provider and add a log to the package. Logs are created at the package level only.
• Select the log schema information and events for every event you want to capture.

. Define query parameters in SSRS?

Ans: When a parameter passes the SSRS RDL file through a query string, it is called a query parameter. We can pass multiple value parameters or a single value parameter.

. Explain the Reporting Life Cycle in SSRS.

Ans:  In Reporting cycle consist of three stages they are:

• Authoring
• Managing
• delivery

These three require some tools to work, and SSRS provides the required tools in reporting applications. Now let us know about the detailed stages involved. The given below is the representation of the stages.

Authoring:

In this stage, the report author defines the sources of data and report layout. You can design reports using a report builder 1.0 or report designer at this stage. You can also report by Report Builder 2.0 the latest version and use it depending on the SQL server you are working on.

Managing:

In this stage, the author publishes the report to a centralised location where a report administrator scrutinises it for delivery and security purposes. SSRS database is the centralised location. When the report gets published, the administrator uses the report manager, custom-written scripts, share point, SQL, or third-party tools and manages published reports.

Delivery:

In this stage, the report is distributed to the intended users and is available in different output formats. With the SSRS retrieval mechanism, users can immediately update to the output format, and SSRS provides many delivery methods.

. Define Report Model Project?

Ans:  A report model project is a template that contains data sources and data sets, and it is used in ad-hoc reporting. Ad-hoc reports are created with the help of a report builder, and they can be made using Report Server or BIDS.

. What do you mean by Report Server Project?

Ans: It is a Business Intelligence Projects template installed by the visual studio, available in the New Project dialog box. RDL files are present and deployed on the report server for users to view the reported files.

. What languages are used in SSAS?

Ans:  In SSAS, we use four languages they are:

• Multidimensional Expressions (MDX)
• Data Mining Extensions (DMX)
• Structured Query Language (SQL)
• Analysis Services Scripting Language (ASSL) 

. How can you set up SSIS Packages on Production?

Ans: In SSIS, the project BUILD furnishes deployment manifest files. We must execute the manifest file and determine whether to deploy this onto SQL Server or the File System. We can alternatively import the package from SSMS from SQL Server or File System. SQL ServerDeployment is faster and more secure when compared to File System Deployment. 

. Define OLAP

Ans: OLAP means Online Analytical Processing. OLAP is a technology that systematises a business's colossal database and organises complex analysis. With OLAP, users can easily extract data and query and analyse it from another standpoint. OLAP BI queries financial reporting, budgeting, sales forecasting, and other planning purposes and aid in trend analysis.

. Explain a workflow in SSIS. What do you mean by a control flow and a data flow?

Ans: 

Workflow in SSIS: 

Workflow processes data in sequence order. Workflow is used in many businesses. A workflow is created if data is transferred between the system and the user. It is like a to-do list describing steps involved by visualizing complex procedures in a workflow.

Control flow :

Control flow is a workflow of tasks that are to be executed in order. A Control Flow contains more than one task and containers that are executed during the package run. Control flow is called the brain of SSIS. Control Flow can be created using the Control Flow tab in SSIS Designer.

Data flow: 

A data flow is a flow of data from a source to a destination. Unlike control flow, rules or loops are not present in the data flow. It gives inputs and outputs of anything in the system, also called DFD. data will flow between selected entities like sources, destinations, and transformations.

. Mention the errors in SSIS and explain how error handling works?

Ans:  Errors occur in SSIS when a data flow component runs a transformation to column data and extracts data or loading data to destinations. With unexpected data values, errors occur.

Errors are categorised into three types they are:

• Data conversion errors occur due to loss of significant or insignificant digits, truncation of strings, and unsupported conversions.
• Expression evaluation errors: errors occur due to invalid operations or incorrect or missing values.
• Lookup errors: errors occur when the lookup operation fails to identify a match in the lookup table

Error handling options are:

• Fail Component: when an error occurs, then source, transformation, destination, etc. will fail.
• Ignore failure: row failures are ignored.
• Redirect Rows: in this, the task will not fail but failed rows are sent to failed output and successful rows to required destinations. 

. Explain the concept of variables and also shed light on what variable scope is?

Ans:  A Variable is a named object which is used in the SSIS package. It stores values and 

Various SSIS components refer to those in the package execution. Users can configure a variable and can assign a value and update in the run time. The SSIS package has two types of variables: user-defined and system-defined.

The scope is the availability of a constant, procedure, or variable used by another procedure. The scope of a variable is determined when we declare it. There are three types of scope levels. 

• Procedure level
• Public module level
• Private module level

. Explain the Ragged Hierarchy?

Ans: It is a user-defined hierarchy and consists of many levels. Here Hierarchy means an arrangement of items. In SSAS, it is a multidimensional database. Hierarchies analyse factual data logically. Each Hierarchy consists of one or more than one levels. At the end of the Hierarchy, we find leaf members. Types of Hierarchies are:

 Balanced Hierarchies, Unbalanced Hierarchies, and Ragged Hierarchies

. What Are Aggregations in SSAS?

Ans: Precalculated summaries of data from cells are called Aggregations. They enhance query response time by preparing answers before questions are asked. They are stored in a multidimensional structure of cells, depending on the percentage of calculations and storage modes like ROLAP, MOLAP, and HOLAP.

. What are PerformancePoint services in MSBI?

Ans: Performance point service or PPS is a BI tool used to display information dashboards by using the SharePoint Platform. It was added as a service within the enterprise version of SharePoint 2010. It is a service application that analyses and monitors business data and builds dashboards, Key Performance Indicators (KPIs), and scorecards.

.What is OLAP? How is it different from OLTP?

Ans: OLAP means Online Analytical Processing. OLAP is a technology that systematises a business's colossal database and organises complex analysis. It provides data analysis for businesses to make better decisions. The main objective is data analysis but not data processing.

OLTP means Online Transaction Processing System, an online database modifying system and database query answering system. Both OLAP and OLTP are online systems.

. What is the way that SSRS Reports are deployed on our website?

Ans: You only need to use a site of reporting services to deploy your SSRS reports on a website. The only option you have to check them on other websites indicates an HTTP link. 

Even though the report has yet to be deployed to or received from reporting services, tools or technologies, like SharePoint, offer controls that enable you to see reports within various websites.

. How to achieve parallelism in SSIS?

Ans: By using MaxConcurrentExecutable components like the package, parallelism is attained. It is calculated as the volume/number of computers + 2 and has a default value of -1.

. How can I use cubes in SSAS in MSBI?

Ans: Multidimensional models called cubes are useful to hold data and aggregates from single or multiple sources. The Cube Wizard is used to build them. Also, while developing cubes, dimensions are built.

. List out the various types of Connection Managers available.

Ans. The following are the different types of Connection Managers-

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

. What is the use of the RS.exe utility?

Ans: The RS.exe utility comes with the Report Server in SSRS and is useful to automate the report server deployment and other admin tasks. Also, we can organize the reports on a report server using it. Furthermore, the script you supply in an input file is processed by the RS.exe utility.

Conclusion:

In this blog, we provide you with some of the most important questions that are probable to be asked in the interview. Revise basics and prepare these questions before going to the interview. Be confident and attend the interview well. For more topics and interview questions, please go through our website. Have a happy reading.