SQL Server Reporting Services Interview Preparation Guide

Download PDF

SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applicatons. It can be competed with Crystal Reports and other business intelligent tools.

Reporting services architecture is comprises of 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 is supported by Reporting Services.

The Reporting Lifecycle includes

- Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition.

- Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time.

- Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.

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

SQL Server Management Studio is used for taking backup of SQL Server Analysis Services database. SSAS backup option can not perform the SSAS database backup operation. To perform this task, create SQL Server Agent Job and schedule that job as per custom requirement. The following is the process:

► Right click the job folder of the SQL Agent node in SSMS.
► Select the option New job
► Specify the Job name for identification purpose.
► Go to Steps Page and click the New button.
► Select SQL Server Analysis Services command as the type of job after entering the name of the step and enter the server name.
► Enter the following command.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW</DatabaseID>
</Object>
<File>AdventureWorksDW.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<Password>password</Password>
</Backup>

► Click on OK
► Goto the Schedule page for creating schedule as per the requirements.

SSIS package does not support any function to generate auto incremental numbers. A script component can be used for the designated task. The steps are as follows:

1. Drag and drop the Script Document to the Data flow and select the Script Component Type as Transformation.
2. Double click the Script Component.
3. Select the column which is to pass through the script component, in the Input Columns tab.
4. Add a column with an integer data type, in the Inputs and Outputs tab.

Data from two sources is combined by Lookup Transformation. There are matching fields from these sources. Case insensitive transformations is performed by the lookups. For the purpose of accessing additional information in relation to the tables is one of the purposes of lookups. Lookups can be used in data warehousing.

Lookups are used for the following purposes

► Data Cleansing
► Error Tolerance
► Data search in data warehouse
► Optimizations using cache modes

The following is the process for Unzip a file in SSIS.

► Use Execute Process Task in the Control Flow Task.
► From BIDS, drag and drop an Execute Process Task to the control flow and configure.

In the Execute Process, perform the following configurations:
► Executable: The path of the application that is being used.
► Arguments: Need to supply the arguments to extract the zipped files. –o+ is the default parameter for overwriting files if they exist.
► Working Directory: The current directory for all process.

Truncate:
► Truncate command is used to remove all rows of the column.
► The removed records are not recorded in the transaction log.
► It is the fast way to remove all the records from the table.
► The records once removed can’t be rolled back.
► It can’t activate trigger.
► It resets the identity of the column.

Delete:
Delete command removes records one at a time and logs into the transaction log.
It can be used with or without where clause.
The records can be rolled back.
It activates trigger.
It doesn’t reset the identity of the column.

Cursors behavior can be controlled by dividing them into cursor types: forward-only, static, keyset-driven, and dynamic. SQL server supports keywords SCROLL and INSENSITIVE to define cursor behavior in terms of scrollability and sensitivity.

Identity Column
► Column with identity property contains unique system generated value in the table.
► Column with identity property is similar to AutoNumber field in MS Access.
► You can set start and increment value while setting column property to identity.
► Column with identity property contains unique values within the table.

Uniqueidentifier, GUID
► The column with uniqueidentifier property contains globally unique data.
► SQL server uses uniqueidentifier property for merge replication.
► Same like Identity Column, SQL server supports uniqueidentifier column that also contains system generated value.
► A column or local variable of uniqueidentifier data type can be initialized using NEWID function.

An integer data type can have a variety of IDENTITY values like int, bigint, tinyint etc.

To avoid the integer IDENTITY to run out of scope, BIGINT should be used as the data type or DECIMAL (38,0)

CREATE TABLE sample_t ( col1 BIGINT IDENTITY(-9223372036854775808, 1) )

Stored procedures provide performance benefits through local storage, precompiling the code, and caching.

Stored procedures offers security features that includes encryption and privilege limits that restrict users from modifying structure of stored procedure.

Stored procedures manage, control and validate data.
Large queries can be avoided.
Reduces network traffic since they need not be recompiled.
Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
Permissions can be granted for stored procedures. Hence, increases security.

RAID is a mechanism of storing the same data in different locations. Since the same data is stored, it is termed as redundant. The data is stored on multiple disks which improves performance. The drive’s storage is divided into units ranging from a sector (512 bytes) up to several megabytes. This is termed as disk stripping.

There are NINE types of RAID plus an additional non-redundant array (RAID-0). However, RAID levels 0, 1, and 5 are the most commonly found.

► RAID 0: This level does involve stripping but no redundancy of data. Offers the best performance at the cost of NO fault tolerance.
► RAID 1: This level is termed as data mirroring consisting of at least two drives that duplicate the storage of data. No stripping involved. Often used for multi user system for best performance and fault tolerance.
► RAID 2: It involves stripping with some disks storing error checking and correcting (ECC) information.
► RAID 5: Consists of 3 or more disks in a way that protects data against loss of any one disk.
► RAID 6: Has stripped disks with dual parity.
► RAID 10: uses both striping and mirroring.
► RAID 53: Merges the features of RAID level 0 and RAID level 3

RAID controller is used when one drive fails and the other is still running well. The controller will automatically rebuild the data from the other devices and restores the same to the crashed system. Hence the RAID controller technology, depending on the importance of the data, is used to restore the data automatically from the other systems.

RAID, Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. We have 0 through 5 RAID levels that offers different levels of performance and fault tolerance.