Last updated on Nov 21, 2023
There is a bright future for BI professionals all over the world. The job markets are flooded with job opening related to BI roles. It is very essential to have MSBI skills for these job roles. Every interview for BI will have questions based on MSBI because MSBI is the king of business intelligence tools. In addition to this, companies are now hiring MSBI professionals.
Here is a blog that covers the most important questions that are asked during the MSBI interview. The MSBI interview questions given here will help you to crack the entry-level job openings as well as the positions requiring experience.
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
Get ahead in your career by learning MSBI Course through hkrtrainings MSBI Online Training
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.
Ans: In SSIS architecture, there are four main components they are:
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.
Ans: Unified Dimensional Model (UDM) goes about as an extension among clients and information sources.
It is important because of it-
Ans: SSAS has a 2-level engineering architecture. The following are the key highlights of SSAS design:
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-
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
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.
The writeback exchange box empowers or incapacitates writebacks for a measure bunch in a block. Empowering writeback on a measure bunch characterizes a writeback parcel and makes a writeback table for that measure gathering. Handicapping writeback on a measure bunch expels the writeback segment however doesn't erase the writeback table to maintain a strategic distance from unexpected information loss.
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.
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.
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.
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 ]
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.
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
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:
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.
[ Related Article: dax functions ]
Ans: The process of impersonation permits SSAS to accept the personality/security setting of the customer application which is utilized by SSAS to play out the server-side information tasks like information get to, PROCESSING, and soon.
Ans: SSRS uses many data sources to import and export data. They are:
Ans: In MSBI, there are three essential tools they are:
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.
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
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 :
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
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.
Want to know more about MSBI,visit here MSBI Tutorial.
Ans: In Reporting cycle consist of three stages they are:
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.
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.
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.
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.
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.
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.
Ans: In SSAS, we use four languages they are:
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.
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.
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.
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.
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:
Error handling options are:
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.
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
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.
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.
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.
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.
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.
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.
Ans. The following are the different types of Connection Managers-
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.
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.
|Batch starts on 2nd Mar 2024
|Batch starts on 6th Mar 2024
|Batch starts on 10th Mar 2024