MSBI Interview Questions, MSBI Interview Questions and Answers

MSBI Interview Questions and Answers, MSBI Interview Questions and Answers Freshers, MSBI Interview Questions and Answers, MSBI Interview Questions

Before getting on to the MSBI interview questions, the student must know that the MSBI is a continuously varying field which needs the students as well as professionals to upgrade their skills with the new features and knowledge, to get fit for the jobs associated with MSBI. This post related to MSBI Interview Questions and Answers, MSBI Interview Questions and Answers Freshers, MSBI Interview Questions and Answers, MSBI Interview Questions will help you let out find all the solutions that are frequently asked in you upcoming MSBI interview.

Over thousands of vacancies available for the MSBI developers, experts must be acquaintance with all the component of MSBI technologies. This is necessary for the students in order to have in-depth knowledge of the subject so that they can have best employment opportunities in the future. Knowing every little detail about MSBI is the best approach to solve the problems linked with problem.

APTRON has spent hours and hours in researching about the MSBI Interview Questions and Answers, MSBI Interview Questions and Answers Freshers, MSBI Interview Questions and Answers, MSBI Interview Questions that you might encounter in your upcoming interview.  All these questions will alone help you to crack the interview and make you the best among all your competitors.

First of all, let us tell you about how the MSBI technology is evolving in today’s world and how demanding it is in the upcoming years. In fact, according to one study, most of the companies and businesses have moved to the MSBI. Now, you cannot predict how huge the future is going to be for the people experienced in the related technologies.

Hence, if you are looking for boosting up your profile and securing your future, MSBIMSBI will help you in reaching the zenith of your career. Apart from this, you would also have a lot of opportunities as a fresher.

These questions alone are omnipotent. Read and re-read the questions and their solutions to get accustomed to what you will be asked in the interview. These MSBI interview questions and answers will also help you on your way to mastering the skills and will take you to the giant world where worldwide and local businesses, huge or medium, are picking up the best and quality MSBI professionals.

This ultimate list of best MSBI interview questions will ride you through the quick knowledge of the subject and topics like OLAP Modling, SSAS, MDI. This MSBI interview questions and answers can be your next gateway to your next job as a MSBI expert.

These are very Basic MSBI Interview Questions and Answers for freshers and experienced both.

Q1: MSBI Characteristics
A1:

Q2: Define Query parameter in SSRS?
A2: Query parameters is revealed in the query of the datasources that are to be included into the SQL script’s where clause of the SQL that can accept parameters. Query parameters begin with the symbol @.

Q3: What are the Reporting Service Constituents in SSRS?
A3:
A. Report Manager : A Web-based administration tool for managing the Report Server.
B. Report Server : Provides services for implementation and delivery of reports.
C. Report Designer : A place where we can create report.

Q4: What do you understand by matrix in SSRS?
A4: A matrix is a data region related to a report set. Matrix permits us to create crosstab reports with the report variables showing on rows and columns. It allows us to drag.

Q5: What do you mean by sub reports and how we can create them?
A5: A sub report is like any other reports which can be termed in main report and can be generate through main report. Parameters can be conceded from main report to sub report and basis of that report can be created.

Q6: Define report model project?
A6: Report model project is used for creating Adhoc reporting. We can create the adhoc reports over report builder. Report model project can be created on bids or report server. This model can have simple view.

Q7: What do you understand by report server project?
A7: Report Server Project comprises of RDL file and it need to be deployed on report server to view the report files to application and user. It is a solution where we can design our reports. We can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is formed we can start forming reports.

Q8: Explain report builder?
A8: Report builder is used to create small reports and it is a define interface. We can’t change the report interface in report builder it pre designed. We can just drag columns in the report. It creates reports on database objects available with report model project.

Q9: What is SSIS? How it is related with SQL Server.
A9: SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.

This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.

Q10: How does Error-Handling work in SSIS
A10: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

Type of typical Errors in SSIS:

-Data Connection Errors, which occur incase the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.

-Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.

-Expression Evaluation errors, which occur if expressions that are evaluated at run time perform invalid

Q11: What is environment variable in SSIS?
A11: An environment variable configuration sets a package property equal to the value in an environment variable. Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.

Q12: What is SSIS? How it is related with SQL Server.
A12: SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.

This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.

Q13: What is a workflow in SSIS 2014 ?
A13: Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.

Q14: What is the control flow?
A14: A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements: Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.

Q15: What is the data flow?
A15: Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.

Q16: How to log SSIS Executions?
A16: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

Q17: How do you deploy SSIS packages?
A17: SSIS Project BUILD provides a Deployment Manifest File. We need to run the manifest file and decide whether to deploy this onto File System or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQL Server.

Q18: Define RS.exe utility?
A18: Rs.exe utility is used for organizing the report on report server. It comes with the report server and can be modify accordingly.

Q19: What is the name of reporting services config file and what it is used for?
A19: Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config exist in ISS.

Q20: What are the three different part of RDL file elaborate them?
A20:
In visual studio RDL files has three parts.

1. Data :It covers the dataset on which we write the query. Data set is associated with data source.

2. Design :In design we can design report. We can create tables and matrix reports. We Drag columns values from source.

3. Preview :It ia used to check the preview after the report run.

Q21: Which language rdl files made of?
A21: RDL files are printed in XML.

Q22: What do you understand by chart in report?
A22: Chart reports are for graphical representation. We can get pie charts columns harts and various other options. 3d charts are also presented in reporting services.

Q23:Define Data Set in report?
A23: Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are receiving this data i.e. database server and database name joining string.

Q24: Differentiate between OLAP and ETL tools.
A24:

• OLAP is an online analytical processing tool.
• ETL stands for Extract, Transform and Load. This is a product to extract the data from multiple/single sources and transform the data and load it into a table, flat file or simply a target.

Q25: Name the tools used in MSBI.
A25: Microsoft BI contains the following tools:
• SQL Server Analysis Services (SSAS)
• SQL Server Integration Services (SSIS)
• SQL Server Reporting Services (SSRS)
• Performance Point Services (PPS)

Q26: What is a workflow in SSIS?
A26: Workflow is a set of instructions to tell the Program Executor how to execute tasks and containers within SSIS Packages.

Q27: What is the difference between WHERE and HAVING clauses in SQL Server?
A27:

• HAVING clause can be used only with a GROUP BY clause, whereas a WHERE clause can be used with constructs such as SELECT, UPDATE, DELETE etc.
• HAVING clause is applied as a filter to the data/output resulting from the GROUP BY clause, where as a WHERE clause is applied to every row in the SELECT, UPDATE, DELETE etc constructs.
• In queries where both HAVING and WHERE clauses are used, WHERE clause is applied first (to every row in the SELECT statement to filter the records before they are fed to GROUP BY clause for aggregation) and then the HAVING clause is applied (to filter the aggregated result from GROUP BY clause).

Q29: Name the differences between view and materialized view.
A29: Views:

• A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
• All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
• A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
• In addition to operating on base tables, one View can be based on another. A view can also JOIN a view with a table (GROUP BY or UNION).

Materialized Views:

• Materialized views are schema objects that can be used to summarize, pre-compute, replicate, and distribute data. E.g. to construct a data warehouse.
• A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
• The existence of a materialized view is transparent to SQL, but when used for query rewrites, it will improve the performance of SQL execution.

Q30: What is WriteBack? What are the pre-conditions?
A30: The Enable/Disable WriteBack dialog box enables or disables WriteBack for a measure group in a cube. Enabling WriteBack on a measure group defines a WriteBack partition and creates a WriteBack table for that measure group. Disabling WriteBack on a measure group removes the WriteBack partition but does not delete the WriteBack table, to avoid unanticipated data loss.

Q31: What are Attunity Driver and why do we user in SSIS?
A31: Attunity provides 2 high speed connectors. One for Oracle and one for Teradata. They have been selected by Microsoft to be included with SQL Server 2008 Integration Services (SSIS) SQL 2008 Enterprise Edition. These drivers are highly optimized and very easy to use.

Optimized, best-in-class performance
The connectors deliver unparalleled throughput for extracting and loading data to and from Oracle and Teradata. Architected with Microsoft, the connectors use direct integration into internal SSIS buffering APIs, cutting through .NET and other layers, and also use the high speed load/extract utilities offered by Oracle and Teradata.

Ease-of-use
The connectors are fully integrated into the Business Intelligence Development Studio (BIDS), part of Microsoft Visual Studio 2008, offering a user experience similar to that of the SSIS generic OLEDB Connector, with intuitive capabilities including configuration, metadata browsing, and error routing.

Q32: Difference between Cached Report and Snapshot Report?
A32: Cached Report
Here the system will save the last executed report. It is saved in the temp DB. It is not persisted. It has a lifetime e.g. 1 hour or so. We can have 1 only one “instance” per report (if you have parameters, you will have one per combination of parameter)

Snapshot Report
It is a persisted copy of the report. It is stored for good on the report database. You can have as many as you want. You can configure for example to save a snapshot of a report per day, so if you want to see how was your data 1 month ago, you just access the snapshot of that day.

When to use which one
Most of my reports, I cache them for 2 hours, so the first user who runs it will experience a small delay and the next will get the report on demand (with the data from when the report was ran, of course)

For large reports, execute them at night and configure them to be run from a snapshot (option “Render this report from a report execution snapshot”).

Q33: How to create Temporary Table using SSIS?
A33: You can use Execute SQL task to create temp table and set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

Q34: Differences Between Script Task & Script Component And Its Advantages?
A34: Script task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.
Normally we can implement custom code by using these components. By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well.

Q35: What Is A Master Package?
A35: Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called as master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.

Q36: What Mdx Functions Do You Most Commonly Use?
A36: This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

Q37: What Is Processing?
A37: Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators.

We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

Q38: What Are The Roles Of An Analysis Services Information Worker?
A38: The role of an Analysis Services information worker is the traditional “domain expert” role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.

Q39: What Are The Different Ways Of Creating Aggregations?
A39: We can create aggregations for faster MDX statements using Aggregation Wizard or through UBO – Usage Based Optimizations. Always, prefer UBO method in real time performance troubleshooting.

MSBI Conclusion Interview FAQs

We know the list of MSBI Interview Questions and Answers, MSBI Interview Questions and Answers Freshers, MSBI Interview Questions and Answers, MSBI Interview Questions is overwhelming but the advantages of reading all the questions will maximize your potential and help you crack the interview. The surprising fact is that this MSBI interview questions and answers post covers all the basic of the MSBI technology and you have to check out the FAQs of different components of MSBI too.

However, you will be asked with the questions in the interview related to the above mentioned questions. Preparing and understanding all the concept of MSBI technology will help you strengthen the other little information around the topic.

After preparing these interview questions, we recommend you to go for a mock interview before facing the real one. You can take the help of your friend or a MSBI expert to find the loop holes in your skills and knowledge. Moreover, this will also allow you in practicing and improving the communication skill which plays a vital role in getting placed and grabbing high salaries.

Remember, in the interview, the company or the business or you can say the examiner often checks your basic knowledge of the subject. If your basics is covered and strengthened, you can have the job of your dream. The industry experts understand that if the foundation of the student is already made up, it is easy for the company to educate the employ towards advance skills. If there are no basics, there is no meaning of having learnt the subject.

Therefore, it’s never too late to edge all the basics of any technology. If you think that you’ve not acquired the enough skills, you can join our upcoming batch of MSBI Training in Noida. We are one of the best institute for MSBI in noida which provide advance learning in the field of MSBI Course. We’ve highly qualified professionals working with us and promise top quality education to the students.

We hope that you enjoyed reading MSBI Interview Questions and Answers, MSBI Interview Questions and Answers Freshers, MSBI Interview Questions and Answers, MSBI Interview Questions and all the FAQs associated with the interview. Do not forget to revise all the MSBI interview questions and answers before going for the MSBI interview. In addition to this, if you’ve any doubt or query associated with MSBI, you can contact us anytime. We will be happy to help you out at our earliest convenience. At last, we wish you all the best for your upcoming interview on MSBI Technology.