MSBI Interview Questions and Answers

MSBI stand for Microsoft Business Intelligence. This intense suite is made out of tools which help in giving best answers for Business Intelligence and Data Mining Queries. This tool utilizes Visual studio alongside SQL server. It basically means the process of understanding the complex data work, allocating it, analyzing it and setting up a proper report that will help in business decisions.

[dt_sc_button type=”type1″ link=”http://www.interviewgig.com/discussion-room/post-a-question/” size=”large” bgcolor=”#7ed640″ textcolor=”#ffffff” target=”_blank” timeline_button=”no”]Post a Question[/dt_sc_button]

BI stands for Business Intelligence, refers to set of techniques or tools  which helps in searching, finding and analyzing best data or relevant info out of huge data for good business decision making.

SQL Server Analysis Services (SSAS)

SQL Server Integration Services (SSIS)

SQL Server Reporting Services (SSRS)

Performance Point Services (PPS)

SSSAS stands for SQL Server Analytical Services. SSAS analyze service, SSAS analyze from the stored data. This analyzing process done with a online analytical process component and data mining capabilities. For analyze process it construct a multi dimensional structures called cubes.

SSIS stands for SQL server Integration Services. It  is the data-warehousing arm of the SQL Server 2008 R2 suite — equipped with superior Extract, Transform and Load (ETL) capabilities. It provides the vehicle for moving data from different data sources to another and changing the data, if necessary.

SSRS stands for SQL Server Reporting Tool. SSRS is a reporting service, now represent analyze data in a graphical way SSRS issued. SSRS generate reports; analyze data, Plans, Scorecards, Dash boards, Excel etc.SSRS 2005 had two separate services. SSRS 2008 is easier to configure, deploy and manage without losing any functionality. SSRS Shares internal components with SQL Server. It enables better memory management capabilities. It has Supports single instance, multi instance and Scale Out.

PPS stands for PerformancePoint Services. It is a SharePoint Server 2013 service application. It enables users to create business intelligence (BI) dashboards that provide insight into an organization’s performance. You can create custom reports, filters, tabular data sources, and scorecard transforms to extend the native functionality of PerformancePoint Services.

Data warehouse is commonly used for reporting and business analysis purposes. This system is actually the output of integrated data from multiple sources and stored into a centralized repository. The Data warehouse stores the current and historical data, so it is easy to generate trend reports, predictive analysis and comparison reports. It’s very helpful for the top management to take the quick decisions about the business.

A Data Mart means that it’s a small part of a Data Warehouse and indicates only a single part (for example sales or finance). It always holds more summarized information.

• Easy Data Exploration and Visualization
• Managed Self-service BI tool
• Full Use Of Native Excel Features
• Dashboards And Scorecards
• MSBI Support To Web Services
• Complete End to End Business Solutions
• MSBI Supports Data warehousing Applications

Rs.exe utility is used for organizing the report on report server. It comes with the report server and can be modify accordingly.

Aggregate: It applies aggregate functions to Record Sets to produce new output records from aggregated values.

Audit: Adds Package and Task level Metadata – such as Machine Name, Execution Instance, Package Name, Package ID, etc..

Charactermap: Performs SQL Server column level string operations such as changing data from lower case to upper case.

Conditionalsplit: Separates available input into separate output pipelines based on Boolean Expressions configured for each output.

Copy column: Add a copy of column to the output we can later transform the copy keeping the original for auditing.

Dataconversion: Converts columns data types from one to another type. It stands for Explicit Column Conversion.

Dataminingquery: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.

Derivedcolumn: Create a new (computed) column from given expressions.

Exportcolumn: Used to export a Image specific column from the database to a flat file.

Fuzzygrouping: Used for data cleansing by finding rows that are likely duplicates.

Fuzzylookup: Used for Pattern Matching and Ranking based on fuzzy logic.

Importcolumn: Reads image specific column from database onto a flat file.

Lookup: Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.

Merge: Merges two sorted data sets into a single data set into a single data flow.

Mergejoin: Merges two data sets into a single dataset using a join junction.

Multicast: Sends a copy of supplied Data Source onto multiple Destinations.

Rowcount: Stores the resulting row count from the data flow / transformation into a variable.

Rowsampling: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.

Unionall: Merge multiple data sets into a single dataset.

Pivot:Used for Normalization of data sources to reduce analomolies by converting rows into columns

Unpivot: Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses.

SSRS use different data source. Some of them are listed below.

• OLEDB
• SQL Server SAP Net weaver BI
• Oracle
• Report Server Model
• SQL Server Analysis Service OLEDB
• ODBC
• SAP Net weaver BI
• Hyperion
• Teradata
• XML

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.

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.

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.

• First we configure HTTP Connection manager which will point to WSDL of a web service.
• Web service task customs this HTTP Connection manager and let us invoke methods in it.
• It returns values of method value we can store it in some variables and can use as input for some other tasks.

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: This error occurs in case 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: This error occurs while data is being transformed over a Data Pipeline from Source to Destination.

Expression Evaluation errors: This error occurs if expressions that are evaluated at run time perform invalid

Report definition: A report definition contains information about the query and layout for the report.

Report snapshot: A report snapshot is actually a report definition that contains a dataset instead of query instructions.

Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

Parameterized report: A published report that accepts input values through parameters.

Shared data source: A predefined, standalone item that contains data source connection information.

Shared schedule: It is a predefined, standalone item that covers schedule information.

Report-specific data source: Data source information which is defined within a report definition.

Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

Linked report: It is a report that derives its definition through a link to another report.

Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is called as cache and the process is known as caching.

In visual studio RDL files has three parts.

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

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

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

It allows us to allocate different SQL server objects between different instances of SQL Server.Object incomes from table, stored procedures, user defined functions etc.

Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.

MOLAP stands for Multidimensional OLAP. This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances performances. In the latter, joins are computed during the request. Targeted at groups of users because it’s a shared environment. Data is stored in an exclusive server-based format. It performs more complex analysis of data

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.

To enhance the custom codes in report go to report tab on top then properties and there you will find the selections for custom code.

Merge transformation can take only two inputs whereas Union all can take more than two inputs. Data has to be sorted before Merge Transformation whereas Union all doesn’t have any boundation like that.

There are basically 4 Cache Modes available in SSIS Lookup Transformation

• Full Cache Mode.
• Partial Cache Mode.
• No Cache Mode.
• Full Cache Mode

SSIS package contain mainly have two types of errors:

Procedure Error: It can be handled in Control flow bythe precedence control and redirecting the execution flow.

Data Error: it is handled in DATA FLOW TASK by redirecting the data flow using Error Output of a component.

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.

Early Arriving Facts sometime get unavoidable because delay or error in Dimension ETL or may be due to logic of ETL. To handle Early arriving Facts, we can create dummy Dimension with natural/business key and keep rest of the attributes as null or default.

• Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations.
• SSAS is very easy to use and interactive.
• Faster Analysis and Troubleshooting.
• Ability to create and manage Data warehouses.
• Apply efficient Security Principles.

To tune-up the Reporting Services, follow the below mentioned ways: – Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. – Replication of data continuously. Using no lock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.

The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source.

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.

It helps in communicating two applications with each other asynchronously. Specialty is two application may be built using dissimilar technology and it works even offline messaging. Sender will stock messages inside queue and reader reads it wherever required

The languages used are:

Structured Query Language (SQL)

Multidimensional Expressions (MDX) :an industry standard query language orientated towards analysis

Data Mining Extensions (DMX) :an industry standard query language oriented toward data mining

Analysis Services Scripting Language (ASSL) : It is used to manage Analysis Services database objects.

Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 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.

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

Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file.

RsKeymgmt Utility: it is used to Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access

RS Utility: this utility is mainly used to automate report server deployment.

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

To enhance the custom codes in report go to report tab on top then properties and there you will find the selections for custom code.

Visual Studio / SSDT / BI Data Tools (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.

Crystal reports are processed by IIS while SSRS have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSRS is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSRS allows only user defined field labels.

Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios: Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example distribute a monthly report to all current customers. Distributing reports to a specific group of recipients based on predefined criteria. For example send a sales performance report to the top ten sales managers in an organization

A database is called an OLAP Database if the database satisfies the FASMI rules:

Fast Analysis: It is defined in the OLAP scenario in five seconds or less.

Shared: Must support access to data by many users in the factors of Sensitivity and Write Backs.

Multidimensional: The data inside the OLAP Database must be multidimensional in structure.

Information: The OLAP database must support large volumes of data.

Tablular report: It is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix report is a cross-tabulation of four groups of data:

1. One group of data is displayed across the page.
2. One group of data is displayed down the page.
3. One group of data is the cross-product.
4. One group of data is displayed as the “filler” of the cells.

For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports. Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the report process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.