MSBI Interview Questions and Answers

BI or business intelligence techniques are used to transform data into information that can help in decision making. Through BI, raw data is transformed into meaningful and useful information that can be used for the business analysis process. If we talk about MSBI or Microsoft business tools then the tools that are provided by Microsoft can be used to transform information and they are known as MSBI tools. At the time, when SQL Server was re-released in 2012, it was renamed as SQL Server Data Tools.

1.What is MSBI? What are the tools that MSBI comprises?

Ans:

As per definition, MSBI or Microsoft Business Intelligence is a powerful suite of tools that provide solutions for Business Intelligence and Data Mining queries. MSBI empowers users to gain access to accurate and up-to-date information for better decision-making at an organizational level. There are three tools that constitute the bulk of MSBI:

SSIS	SSAS	SSRS
SQL Server Integration Services or SSIS is basically leveraged for integrating data from multiple databases together. This tool can deal with huge amounts of data and is therefore used for large transactions.	SQL Server Analysis Services or SSAS is mostly used to analyze the performance of SQL Server when it comes to load balancing, heavy data, and transactions, among other uses. This tool is mostly related to the administration of SQL Server.	SQL Server Reporting Services or SSRS pertains to report generation. Being platform-independent, this tool can be used across numerous applications and is therefore quite popular in the market these days.

2.How is SSIS related to SQL Server?

Ans:

SQL Server Integration Services is a component of SQL Server in MSBI. This tool can be used to perform numerous data migration and ETL operations.

This platform is used for integration and workflow applications. Known for its quick and flexible OLTP and OLAP extensions for data extraction, transformation, and loading (ETL), SSIS can also be used to automate the maintenance of SQL Server databases and multidimensional datasets.

3.What are the tools associated with SSIS?

Ans:

SSIS tools support both the simplest solutions—in which users perform tasks such as copying data from one location to another—and the enterprise-level solutions, in which users develop a large number of complex packages in a team environment.

• Business Intelligence Development Studio (BIDS)
• • To design and create new packages and data source objects and views that the packages use
  • To modify the existing packages
  • To debug package functionality
  • To create the deployment bundle that is used for deploying packages
• SQL Server Import and Export Wizard
• • This is the simplest way to create Integration Services packages. These packages can extract data from a variety of data sources, such as Excel spreadsheets, flat files, and relational databases, and load the data into similar data stores.
• SSIS Designer and SSIS Menu
• • SSIS Designer shows tabs for each designer and also the tab for the package content. It is a graphical tool for developing packages.
  • When you open an Integration Services project in BIDS, the SSIS menu is added to the menu bar. When you select the Work Offline option, Integration Services skips the aspect of package validation that makes a connection to data sources and other external components.
• Control Flow Designer and Event Handlers
• • The package control flow can be constructed using Control Flow Designer.
  • An Event Handler is a workflow that runs in response to an event that the run time raises.

4.What is a workflow? What do you mean by a control flow and a data flow?

Ans:

A workflow is basically a set of instructions that is shared with the Program Executor as a guideline to execute tasks and containers.

A control flow mostly consists of one or more tasks and containers that execute when a package is run. To define conditions for running tasks in the package control flow, precedence constraints are used that connect the tasks and containers in a package.

There are three control flow elements that SSIS provides:

• Containers that provide structures in packages
• Tasks that provide functionality
• Precedence constraints that connect the executables in an ordered control flow

A data flow includes sources and destinations that extract and load data, transformations that modify and extend data, and paths that link sources, transformations, and destinations. The data flow task is executable within SSIS packages that create, order, and run the data flow.

Go through the MSBI Course in London to get a clear understanding of MSBI!

5.Mention the errors in SSIS and explain how error handling works.

Ans:

There are three types of major errors in the SSIS landscape, namely:

• Data connection error: This type of error occurs when the Connection Manager cannot be initialized with the connection string.
• Data transformation error: This error occurs while the data is being transformed over a data pipeline.
• Expression evaluation error: This type of error occurs if the run-time evaluated expressions are invalid.

When the column data is transformed, extracted from sources, or loaded into a destination, errors occur.

Procedure errors are handled in the control flow by the precedence control and by redirecting the execution flow, and data errors are handled in the data flow task by redirecting the data flow using the error output of a component.

6.Define environmental variables in SSIS.

Ans:

The configuration of environmental variables sets package properties equal to the value in an environment variable. These are used to configure properties that are dependent on computers that execute packages.

7.What are different lookup cache modes available in SSIS?

Ans:

Different lookup cache modes in SSIS are as follows:

• Full cache mode
• Partial cache mode
• No cache mode

Want to learn more about MSBI? Enroll in this MSBI Training in New York to get ahead in your career!

8.Explain the architecture of SSIS.

Ans:

SSIS architecture involves four key parts:

• Integration Services: It monitors running Integration Services packages and accomplishes the storage of packages.
• Integration Services object model: It contains a managed API for reading Integration Services tools, command-line utilities, and custom applications.
• Integration Services run time and runtime executables: It keeps the layout of packages, runs the packages, and provides support for logging, breakpoints, configuration, connections, and transactions.
• Dataflow Engine: It provides in-memory buffers that move data from source to destination.

9.How can we do logging in SSIS?

Ans:

SSIS includes logging features that not only write log entries when run-time events occur but also write custom messages. Integration Services supports a diverse set of log providers and empowers users with the ability to create custom log providers. Integration Services log providers can write log entries into text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. The logging configuration provides an in-built feature that can log the details of various events like OnError, OnWarning, etc.

10.How do you deploy SSIS packages?

Ans:

The SSIS project, ‘Build’ offers a deployment manifest file. Users are required to run the manifest file and decide whether to deploy it onto a file system or onto Microsoft SQL Server database. The SQL Server deployment is fast and is also relatively secure than the file system deployment. Alternatively, this deployment can be done from packages in SSMS from the file system or SQL Server.

11.How can we organize SSIS packages on production?

Ans:

There are three ways to do so:

• Through Manifest:
• • Create a deployment utility by setting its properties as true. It will be formed in the bin folder of the solution.
  • Once the package is built, copy all the files in the utility and use the manifest file to display it on the product.
• Using the DTExec.exe utility
• By importing the package directly into Microsoft database from SQL Server Management Studio (SSMS) by logging into Integration Services.

12.Define query parameters in SSRS.

Ans:

Query parameters are revealed in the query of data sources that are to be included in the SQL WHERE clause for accepting parameters. Query parameters begin with the symbol ‘@’.

13.What are variables? Define the variable scope.

Ans:

Variables are used to store values that SSIS packages and their containers, tasks, and event handlers leverage at the run time. Scripts can also use variables. Additionally, precedence constraints that sequence tasks and containers into a workflow use variables when constraint definitions include expressions.

Integration Services supports two types of variables: user-defined variables and system variables.

14.Define SSRS and also talk about the SSRS architecture.

Ans:

One of the server-based software systems, SQL Server Reporting Services (SSRS) generates reports developed by Microsoft. It is used for preparing and delivering a variety of interactive printed reports and is administered through an interface that is web-based. It utilizes a web service interface for supporting and developing customized reporting applications.

The architecture of SSRS comprises integrated components. This multi-tiered architecture is scalable and modular. A single installation can be used across multiple computers.

Various components of SSRS architecture include Report Manager, Report Designer, the browser types supported by Reporting Services, Report Server, Report Server command-line utilities, Report Server Database, Report Services Extensibility, and the data sources that are supported by Reporting Services.

15.Explain the Reporting Life Cycle in SSRS.

Ans:

SSRS reporting life cycle includes several aspects:

• Report designing: It is done in Visual Studio Report Designer. It generates a class that embodies the report definition.
• Report processing: This brings the report definition from the report data source. It performs all grouping, sorting, and filtering calculations.
• Report rendering: This stage starts by passing the Report Instance to a specific rendering extension that needs to be in HTML or PDF formats. The expressions of items are evaluated in the header and footer sections for every page. Finally, the report is rendered to a specific output document.

16.When shall we use a null data-driven subscription?

Ans:

Users should first create a data-driven subscription that uses the Null Delivery Provider. When the Null Delivery Provider is specified as the method of delivery in the subscription, the Report Server targets the Report Server Database as the delivery destination and uses a specialized rendering extension called the null rendering extension. Different from the other delivery extensions, the Null Delivery Provider does not have delivery settings that can be configured through a subscription.

17.What do you mean by matrix in SSRS? What are sub-reports and how do we create them?

Ans:

A matrix is a data region that is related to a report set. Matrices permit us to create crosstab reports with the report variables showing on rows and columns.

A sub-report is like any other reports which can be termed in the main report and can be generated through the main report. Parameters can be conceded from the main report to the sub-report, and on the basis of that a report can be created.

18.Define Report Model Project.

Ans:

Report Model Project is used for ad-hoc reporting. We can create ad-hoc reports with Report Builder. Having a simple view, Report Model Project can be created on BIDS or Report Server.

19.What do you mean by Report Server Project?

Ans:

Report Server Project comprises RDL files, and it needs to be deployed on Report Server to view the report files for applications and users. It is a solution where users can design reports. Once the solution is formed, users can start designing reports.

20.Define RS.exe Utility.

Ans:

RS.exe utility is used for organizing reports on Report Server. It comes with the Report Server and can be modified accordingly.

21.What are the three different parts of RDL files?

Ans:

In Visual Studio, RDL files have three parts:

• Data that covers the dataset on which queries are written
• Design that creates tables and matrix reports
• Preview that is used to check the preview after a report is run

22.What are the different data sources used in SSRS?

Ans:

Several data sources are used in SSRS for ingesting data:

• OLEDB
• SQL Server SAP NetWeaver BI
• Oracle
• Report Server Model
• SSAS OLEDB
• ODBC
• Hyperion
• Teradata
• XML

23.Differentiate between Tabular and Matrix Reports.

Ans:

Tabular Report	Matrix Report
A tabular report is the most basic type of report.	A matrix report is a cross-tabulation of four groups of data.
Each column corresponds to a column selected from the database.	One group of data is displayed across the page.Another group of data is displayed down the page.The third group of data is the cross-product.The fourth group is displayed as ‘filler’ of the cells.

24.How to send SSRS Reports from SSIS?

Ans:

In order to do this, users need to create a subscription to the report. This can be done with the Report Manager. The report format and the email address of the recipient can be mentioned at Report Subscription. When a schedule is created for the SSRS report, an SQL Server Agent Job will be created. From SSIS, using sp.start_job, the relevant job name can be passed and, henceforth, the SSRS report subscription can be executed.

25.What is SSAS? List out its key features.

Ans:

The middle-tier server for analytical processing, OLAP, and data mining, SSAS manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. Analysis services provide a combined view of data that is used in OLAP or data mining.

Key features of SSAS are:

• Ease of use with a lot of wizards and designers
• Flexible data model creation and management
• Scalable architecture to handle OLAP
• Integration of administration tools, data sources, security, caching, reporting, etc.
• Extensive support for custom applications

26.What is the significance of UDM in SSAS?

Ans:

Unified Dimensional Model (UDM) acts as a bridge between users and data sources.

Benefits of UDM in SSAS:

• Enriches the user model
• Provides high-performance queries supporting interactive analysis
• Allows business rules to be captured in the model to provide better analysis

27.What is the architecture of SSAS?

Ans:

SSAS has a 2-tier architecture. Below are the key features of SSAS architecture:

• Both server and client components supply OLAP and data mining functionalities to BI applications.
• Server components are implemented as individual Microsoft Windows Services.
• Clients communicate with Analysis Services using the standard XMLA (XML for Analysis).

28.What languages are used in SSAS?

Ans:

There are four languages that are used in SSAS. They are:

• Structured Query Language (SQL)
• Multidimensional Expressions (MDX) for analysis
• Data Mining Extensions (DMX) for data mining
• Analysis Services Scripting Language (ASSL) for managing Analysis Services database objects

29.How are cubes implemented in SSAS?

Ans:

Cubes are multidimensional models that store data and aggregations from one or more sources. They are created using the Cube Wizard. Also, dimensions are built while creating cubes.

30.What are the differences between TRUNCATE & DELETE clauses in SQL Server?

Ans:

TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.

TRUNCATE removes all the records from a table without making a log entry for individual row deletions whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.

TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (optional) is used or not used respectively

TRUNCATE cannot be used on a table if it satisfies one of the following conditions:

The table is referenced by one of more FOREIGN KEY constraints

The table is marked/enabled for replicationTRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITy.

31.What are the Command-Line Utilities available in Reporting Services?

Ans:

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.

32.Name different types of data sources in SSRS?

Ans:

SSRS uses a different data source. Some of them are listed below.

• SQL Server SAP Net weaver BI.
• Report Server Model.
• SQL Server Analysis Service OLEDB.
• SAP Net weaver BI.
• XML

33.What are the differences between DTS and SSIS?

Ans:

Data Transformation Services SQL Server Integration Services

Limited Error Handling Complex and powerful Error Handling

Message Boxes in ActiveX Scripts Message Boxes in .NET Scripting

No Deployment Wizard Interactive Deployment Wizard

Limited Set of Transformation Good number of Transformations

NO BI functionality Complete BI Integration.

34.What are the benefits of using embedded code in a report?

Ans:

 The benefits are:

Reusability of Code: function created in embedded code to achieve a logic can be then used in manifold expressions

Centralized code: it helps in the better manageability of code.

35.How can we schedule an SSIS package?

Ans:

 Using SQL Server Agent we can schedule an SSIS package.

36.What Are Variables And What Is Variable Scope?

Ans:

Variables store values that an SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables. 

37.Can You Name Five Of The Perfmon Counters For Ssis And The Value They Provide?

Ans:

• SQLServer: SSIS Service
• SSIS Package Instances
• SQLServer: SSIS Pipeline
• BLOB bytes read
• BLOB bytes are written
• BLOB files in use
• Buffer memory
• Buffers in use
• Buffers spooled
• Flat buffer memory
• Flat buffers in use
• Private buffer memory
• Private buffers in use
• Rows read
• Rows wrote.
• S Utility: this utility is mainly used to automate report server deployment.

38.How to create Drill-Down reports?

Ans:

 By grouping data on essential fields

Then toggle reflectivity based on the grouped field.

39.What is MSBI (Microsoft Business Intelligence)?

Ans:

Microsoft Business Intelligence (BI) is a suite of products and tools that you can use to monitor, analyze and plan your business by using scorecards, dashboards, management reporting, and analytics.

40.What are the tools in MSBI?

Ans:

 It contains the following tools:

1. 1. SQL Server Analysis Services (SSAS)
2. 2. SQL Server Integration Services (SSIS)
3. 3. SQL Server Reporting Services (SSRS)
4. 4. PerformancePoint Services (PPS) which was added as a free service in Microsoft Office SharePoint 2010.

41.How can we create Drill-through reports?

Ans:

By Using Navigation property of a cell and scenery child report and its parameters in it we can create Drill-through reports.

Progress execution by practicing Linux software invasion and striping over four extents.

42While creating a new calculated member in a cube what is the use of a property called non-empty behavior?

Ans:

Nonempty behavior is an important property for ratio calculations. If the denominator is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then The expression is also treated as empty and no error is returned. 

43.Explain transfer SQL Server object task?

Ans:

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

44.Name a few Business Analysis Enhancements for SSAS?

Ans:

The following table lists the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

• Enhancement Type Applied to No data source
• Time Intelligence Cube Cube No
• Account Intelligence Dimension Dimension or cube No
• Dimension Intelligence Dimension Dimension or cube Yes
• Custom Aggregation Dimension Dimension (unary operator) or cube No
• Semi Additive Behavior Cube Cube Yes
• Custom Member Formula Dimension Dimension or cube No
• Custom Sorting and Uniqueness Settings Dimension Dimension or cube Yes
• Dimension Writeback Dimension Dimension or cube Yes.

45.Where do you put calculated members?

Ans:

The reflexive answer is “in the Measures dimension” but this is the obvious answer. So I always follow up with another question. “If you want to create a calculated member that intersects all measures, where do you put it?” A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.” If they can answer it, I immediately ask them why. The answer is “Because a member in a dimension cannot intersect its relatives in that dimension.”

46.What Are The Types Of Parameters In Ssrs?

Ans:

The types of parameters in SSRS are:

• Single value parameters
• Multivalue parameters
• Cascading parameters.

47.What Is Hyperion? Is It An Olap Tool?

Ans:

It’s Business Intelligence tools. Like Brio which was an independent product bought over my, Hyperion has converted this product name to Hyperion Intelligence.

48.What Is The Difference Between Olap And Etl Tools?

Ans:

OLAP: It’s an online analytical processing tool. There are various products available for data analysis.

ETL: Extract, Transform and Load. This is a product to extract the data from multiple/single sources, transform the data and load it into a table, flat file or simply a target.

49.How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

Ans:

Simply using bottom count will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

50.What Is Processing?

Ans:

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

51.How in MDX query can I get the top 3 sales years based on order quantity?

Ans:

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy” and “OrderByAttribute ”. Let us say we want to see order counts for each year. In AdventureWorks MDX query would be:

52.How do you extract the first tuple from the set?

Ans:

Example:

53.What are checkpoints and how do they work?

Ans:

SSIS 2005 included a feature called checkpoints, which allows you to restart the package if it fails for any reason. During package execution, the last completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.

You need to set three package properties:

CheckpointFileName. For this property, you need to provide a path and filename for the checkpoint file. If you plan to keep checkpoints implemented when you put a package into production, it’s a good idea to use a Universal Naming Convention (UNC) path.

CheckpointUsage. This property has three possible values: Never, Always, and exists. The default is Never, which prevents checkpoint creation. When you specify the Always option, the package uses the checkpoint file if it exists. If it doesn’t exist, the package fails. Therefore, the Always option isn’t recommended for a package in production because the package shouldn’t be failing regularly. (A package failure is the only way a checkpoint file gets created. Once the package completes successfully, the checkpoint file is removed.) The best option to use is IfExists. When you select this option, the package uses the checkpoint file if it exists. If it doesn’t exist, the program starts from the beginning of the package.

SaveCheckpoints. This property must be set to True. Otherwise, the previous settings won’t have any effect. By default, it’s set to False.

54.How does the checkpoint work in for loop?

Ans:

The Foreach Loop container is another atomic unit of work that can be restarted. However, the checkpoint file does not contain information about the work completed by the child containers, and the Foreach Loop container and its child containers run again when the package restarts.

55.What Is A Master Package?

Ans:

The Master package is nothing but it is also a normal SSIS package, it acts as a master to run all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package is called a 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.

56.How can we handle Early Arriving Facts?

Ans:

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

57.Explain the two-tier architecture of SSAS?

Ans:

A SSAS uses both server and client components to supply OLAP and data mining functionality to BI Applications.

The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services is implemented as a separate instance of the Windows service.

Clients communicate with Analysis Services using the standard XMLA (XML for Analysis) protocol for issuing commands and receiving responses.

58.How does error-handling work in SSIS?

Ans:

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.

The types of typical Errors in SSIS are:

Data Connection Errors, which occur 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, 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 runtime perform invalid

59.What are the Reporting Services components?

Ans:

Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report server is used to execute and distribute reports. a report manager is used to manage the report server.

60.SQL Server Reporting Services vs Crystal Reports?

Ans:

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 the only user-defined field labels.

61.What is Report Builder?

Ans:

Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

62.How does Report Builder support Analysis Services cubes?

Ans:

Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for an 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.

63.Differentiate between Cube-deploy And Process?

Ans:

Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual SQL server analysis server. This process is called DEPLOYMENT of a cube. (like creating one object(table) in the relational database).

So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table) So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to an SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube. 

64.Difference between a Logical Page and a Physical Page in SSRS?

Ans:

Logical page breaks are page breaks that you insert before or after report items or groups. Page breaks help to determine how the content is fitted to a report page for optimal viewing when rendering or exporting the report. The following rules apply when rendering logical page breaks: Logical page breaks are ignored for report items that are constantly hidden and for report items where the visibility is controlled by clicking another report item. Logical page breaks are applied on conditionally visible items if they are currently visible at the time the report is rendered. Space is preserved between the report item with the logical page break and its peer report items. Logical page breaks that are inserted before a report item push the report item down to the next page. The report item is rendered at the top of the next page. Logical page breaks defined on items in table or matrix cells are not kept. This does not apply to items in lists.

65.How can we achieve parallelism in SSIS?

Ans:

Parallelism is achieved using MaxConcurrentExecutable stuff of the package. Its default is -1 and is calculated as the number of computers + 2.

66.How do users use Report Builder with SQL Server data sources?

Ans:

While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio-based development shell.

67.How do I get Report Builder to generate a parameter that can be set by users viewing the report?

Ans:

In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

68.Define Query parameter in SSRS?

Ans:

Query parameters are revealed in the query of the data sources that are to be included in the SQL script where clauses of the SQL that can accept parameters. Query parameters begin with the symbol @.

69.What do you understand by the matrix in SSRS?

Ans:

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.

70.Do I need a report server to run reports in my application?

Ans:

In addition to publishing reports to a report server, you can build reports using the Report Designer that is directly integrated with Visual Studio language projects. You can embed reports directly in any Windows Forms or ASP.NET Web application without access to a report server. The data access in embedded reports is a natural extension of the Visual Studio data facilities. Not only can you use traditional databases as a source of data for your reports, but you can also use object collections as well.

71.What Is MOLAP?

Ans:

MOLAP – 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.

72.Differences Between Script Task & Script Component And Its Advantages?

Ans:

A script task is a control flow level item whereas a script component is a data flow level item, both of the functionalities are same. These two 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.

73.What is the difference between WHERE and HAVING clauses in SQL Server?

Ans:

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, whereas 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).

74.Explain SSRS Architecture?

Ans:

Reporting services architecture comprises integrated components. It is multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components: – Report Manager, Reporting Designer, Browser Types Supported by Reporting Services, Report server, Report server command-line utilities, Report Server Database, Reporting Services Extensibility, Data sources that are supported by Reporting Services. 

75.MSBI Characteristics?

Ans:

Criteria Result

Analytics Dashboards guided navigation and drill down

Type of Solution End-to-end Business solution with extended ETL

User-friendly Ease of installation, use, maintenance

Get a detailed understanding of the advantages of MSBI over other business intelligence tools.

76.Can we debug the SSIS package? If Yes, How?

Ans:

You can debug a Package by Setting Breakpoints on a Task or a Container

To set breakpoints in a package, a task, or a container follow below-

In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.

Double-click the package in which you want to set breakpoints.

In SSIS Designer, do the following:

To set breakpoints in the package object, click the Control Flow tab, place the cursor anywhere on the background of the design surface, right-click, and then click Edit Breakpoints.

To set breakpoints in a package control flow, click the Control Flow tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.

To set breakpoints in an event handler, click the Event Handler tab, right-click a task, a For Loop container, a Foreach Loop container, or a Sequence container, and then click Edit Breakpoints.

In the Set Breakpoints dialog box, select the breakpoints to enable.

Optionally, modify the hit count type and the hit count number for each breakpoint.

To save the package, click Save Selected Items on the File menu.

77.Name the differences between view and materialized view?

Ans:

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

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

78.What important terms can be used in the reporting services?

Ans:

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.

79.How many types of protection level in SSIS packages?

Ans:

It is a package level property. It is used to specify how sensitive information is saved inside the package. It also specifies whether to encrypt the package or the sensitive portions of the package.

Each SSIS component designates that an attribute is sensitive by including Sensitive=”1″ in the package XML. When the package is saved, any property that is tagged with Sensitive=”1″ gets handled per the ProtectionLevel property setting in the SSIS package.

The ProtectionLevel property can be selected from the following list of available options (click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties):

• DontSaveSensitive
• EncryptSensitiveWithUserKey
• EncryptSensitiveWithPassword
• EncryptAllWithPassword
• EncryptAllWithUserKey
• ServerStorage

80.Can you import Microsoft Excel data to SSRS?

Ans:

Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you’re asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You’ll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.

81.Can we deploy SSRS reports on our personal website?

Ans:

Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services.

82.Can we use data grids for our report in SSRS?

Ans:

We have an ASP.NET project that populates a datagrid. Using datagrid as my datasource for my report using SQL Server Reporting Services. Is this possible? The simple answer is no. However, nothing’s ever simple. A set of reporting controls was added in Visual Studio 2010 allowing you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.

83.What are the drawbacks of reporting in SSRS?

Ans:

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.

84.Will running SSRS on Windows XP limit the number of users?

Ans:

Yes, but not because of SSRS. The Internet Information Services (IIS) component of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS, this would prevent more than a few people from using SSRS at once. Also, the only edition of SSRS that will be installed on Windows XP is the Developer Edition. This edition can not be used for production use. You need Standard or Enterprise Edition for production use, which requires a Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).

85.Are there issues when exporting SSRS reports into Microsoft Excel?

Ans:

When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be? Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as OfficeWriter. From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.

86.How to send a SSRS report from SSIS?

Ans:

Often there is a requirement to be able to send a SSRS report in Excel, PDF or another format to different users from a SSIS package one it has finished performing a data load. In order to do this, first you need to create a subscription to the report. You can create a SSRS report subscription from Report Manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.