MSBI Interview FAQs

1.   What is MSBI?

Ans: MSBI stands for Microsoft Business Intelligence. It is a suite of tools and technologies for business intelligence, data integration, data warehousing, and reporting provided by Microsoft.

2.   Explain the components of MSBI.

Ans: MSBI consists of three main components:

SQL Server Integration Services (SSIS): For data integration and ETL (Extract, Transform, Load) processes.

SQL Server Analysis Services (SSAS): For creating and managing multidimensional and tabular data models.

SQL Server Reporting Services (SSRS): For creating, managing, and delivering reports.

3.   What is ETL and how is it used in MSBI?

Ans: ETL stands for Extract, Transform, Load. In MSBI, SSIS is used for ETL processes. ETL involves extracting data from various sources, transforming it into a suitable format, and loading it into a data warehouse or another target system for analysis and reporting.

4.   What is a data warehouse, and how does it differ from a database?

Ans: A data warehouse is a centralized repository that stores structured data from various sources for reporting and analysis. It differs from a database in that it is optimized for read-heavy analytical queries and typically contains historical data for reporting purposes.

5.   Explain the difference between SSAS Multidimensional and SSAS Tabular models.

Ans: SSAS Multidimensional uses multidimensional cubes and is suitable for complex analytical scenarios. SSAS Tabular uses tabular models and is more straightforward, often preferred for simpler data models.

6.   How do you deploy SSIS packages?

Ans: SSIS packages can be deployed using SQL Server Data Tools (SSDT) or the SQL Server Management Studio (SSMS). You can deploy them to SQL Server or SQL Server Integration Services Catalogs.

7.   What is a data source and data destination in SSIS?

Ans: A data source in SSIS is the location from which data is extracted. A data destination is the location where data is loaded or stored.

8.   What are the different authentication modes in SSRS?

Ans: SSRS supports two authentication modes: Windows Authentication and Forms Authentication.

9.   What is the role of a Data Mart in a data architecture?

Ans: A Data Mart is a subset of a data warehouse that is designed to serve the needs of a specific group or department within an organization. It contains a focused set of data for a particular business area, making it easier for users to access and analyze data relevant to their needs.

10.  How can you optimize the performance of SSAS models?

Ans: Performance optimization in SSAS can be achieved through techniques like partitioning, aggregation design, and query optimization. Properly designing hierarchies and using appropriate indexing also plays a crucial role.

11.  What is the importance of indexing in a data warehouse?

Ans: Indexing in a data warehouse helps improve query performance by allowing the database engine to quickly locate the required data. Proper indexing strategies are crucial for optimizing data retrieval in data warehousing scenarios.

12.     Explain the concept of slowly changing dimensions (SCD) in data warehousing.

Ans: Slowly changing dimensions are used to manage historical changes in data over time. There are three types of SCDs: Type 1 (overwrite existing data), Type 2 (add new records with a new surrogate key), and Type 3 (maintain both old and new values).

13. 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.  

14. 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.

15. 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.

16. Explain the concept of writeback and its preconditions.

Ans: 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.

18. 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.

19. 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.

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.

21 . What is Impersonation?

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.

22. 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.

23. 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.

24. 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. 

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

26. 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.