MSBI Interview Questions and Answers
Q1. Differentiate views and Materialized 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 on 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 as 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.
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:
Learn to analyze data sets, software programs and build a comprehensive bi solution by enrolling in our MSBI Training
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.
Subscribe to our youtube channel to get new updates..!
Ans. SSRS uses various data sources for importing and exporting the data; they are:
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.
Q6. Differentiate Truncate and DELETE Commands
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 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)
It 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 centralised 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(read 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:
The Crossjoin function returns all the combinations of the sets indicated by the arguments in the Crossjoin function.
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 which 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 is a data zone which 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.