ColumnStore Index - SQL 2012 New features

Summary:

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

Download "Column Store Index" white paper

Note: above content is copied from the White paper discussed in this post

How Reporting Services Works?

Here I’ve explained briefly on how Reporting service requests are handled through the components of SSRS architecture.

As shown in the diagram: Firstly, request will be received by Reporting server’s OS through Http.sys Driver, the driver will route the communication to reporting service’s Web service.  Http Listener will receive the request which will be re-routed by Http.sys.

Http Listener transfers the request to Security sub layer (SSL) for authentication.  Requestor is identified using 4 different authentication mechanisms like Kerberos, NTLM, Negotiate and basic authentication.

Each mechanism supports different approach. Once user has been authorized by SSL, user communication will be redirected to either Report Manager or Report Server which is hosted in RS web services (refer the exhibit). These two components will read the report definition, report details like parameter, building report query, and some of the activities which are listed below, etc., will be done with the help of Core Processing unit.

Activities of Core Processing component are:-

• Scheduling
• Subscription Management
• Report Processing

Upon completion of processing all SSRS components, Report output will be rendered on report viewer.

Hope this is helpful!

5 Tips for a Smooth SSIS Upgrade to SQL Server 2012 - White Paper

Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property.

• TIP #1: Edit Package Configuration and Data Source after upgrading
• TIP #2: Convert to project deployment model using Project Conversion Wizard
• TIP #3: Update Execute Package Task to use project reference and use parameter to pass data from parent package to child package
• TIP #4: Parameterize PackageName property of Execute Package Task to dynamically configure which child package to run at execution time
• TIP #5: Convert package configuration to parameter when possible

Download Whitepaper: 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

Reference:- MSDN Documentation

The History of SQL Server

This history of SQL Server goes back to 1989 and this video covers each of the SQL Server releases up to the new SQL Server 2012

SSRS 2008 - Subscriptions Types

Usually the term, Subscription is used for a fixed set of services, such as one copy of each issue of a magazines, newspaper for a certain period of time

SSRS subscriptions allow developer to help the users/clients by configuring the subscription, which will deliver the reports into the hands of your users/clients based on predefined schedule, or at specific event, such as data changes, updates., etc.,

SQL Server Reporting services support two types of subscription as classified below

1. Standard Subscriptions

2. Data Driven Subscriptions

Standard Subscriptions

·  This subscription is created and managed by Individual users, it gives more freedom to its user

·  It consists of default pre-defined parameter values which will not varied during subscription process

·  In this subscription, a report is rendered in a specific format with default parameter, and delivered to a single, pre-set location

Data Driven Subscriptions

·  This subscription is used to call as Dynamic Subscription.

· It is more flexible and better for managing delivery of reports to a larger number users with varying needs.

· To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions.

· You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient.

By Default, Subscription delivery is limited to e-mail transmittal

SSRS 2008 Command Line Utilities

Microsoft has provided following three utilities for managing the reporting server activities.

1.    RS.exe

2.    RSConfig.exe

3.    RSKeyMgmt.exe

These tools are more useful for configuring SSRS scale-out deployment, sharing encryption keys, managing SSRS objects like reports, data Source, etc.,

RS.exe

This utility helps to execute the VB.NET Scripts for automating the reports deployment. This tool support only when “Reporting services” not running in SharePoint Integrated Mode

Here are the parameters for RS.exe, which is used for deploying reports:

Parameter	Description
/i	Input .rss file for execution
/s	URL to SSRS virtual directory
/u	Username
/p	Password
/l	Timeout in seconds; default is 60 seconds
/b	Batch command execution
/e	SOAP endpoint used; default is mgmt2005
/v	Global Variable mapping
/t	Trace log output goes to SSRS

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rs.exe

RSConfig.exe

                                    

RSConfig utility helps to modify/manage the connection information of report services database

Here are the parameters for RSConfig.exe. Note that you will need to use quotation marks if any of the parameters e.g. database name contain a space:

Parameter	Description
/m	Remote server name; default is localhost
/i	Instance name, if a named instance is used
/c	Connection
/s	Database server name
/d	Database name
/a	Authentication method; either Windows or SQL authentication
/u	Username
/p	Password
/t	Trace log output goes to SSRS
/e	Unattended report execution; also needs /u and /p

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rsconfig.exe

RsKeymgmt.exe

This utility helps to manage the encryption key as well as it shares the encryption key to share with other Reporting services installation or instance.

Here are the parameters for RSKeyMgmt.exe:

Parameter	Description
/a	Restored and overwritten
/d	Deletes the key and encrypted data
/e	Extract key for backup to a file
/f	Filepath parameter for /a or /e parameter
/i	Named instance
/p	Password
/t	Trace log output goes to SSRS
/j	Adds a remote SSRS instance to the local Report Server database. /m (report server) and /n (instance name) are used with this parameter; /i refers to the local named instance
/r	Removes an SSRS instance from the scale-out deployment; use the GUID Installation ID to specify the instance
/u	Account name of the remote SSRS instance (optional)
/v	Password for the local admin of the remote SSRS instance (optional)
/m	Remote server name for /j
/n	Remote instance name for /j

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rskeymgmt.exe

Reporting Tools Vs. Reporting Life Cycle

I have posted the stages of Reporting life cycle in my previous post. In this post, we will see, how SQL Server Reporting services is supporting full reporting life cycle?

Microsoft has provided collection of tools and services to implement/follow the Life cycle processes. 

Refer the following tables which is classified in form of “Reporting Tools” Vs. “Reporting Life Cycle”

Hope it Helps!

Reporting Life Cycle:-

Reporting life cycle is described as following three phases and listed sequential processes in each phase:-

1. Authoring:

a. Requirement  Gathering

b. Design and Clarification

c. Data & Requirement validation/verfication

2. Management

a. Publishing reports

b. Report configuration for end user access

c. Enhancement

3. Delivery

a. End user consumption in terms of viewing reports

b. On demand request (Pull Methods)

c. Subscription on a predefined schedule (Push Methods)

Types of Hierarchies in Data Warehouse:

Hierarchies are logical entities that an end user can use to analyze fact data.  These entities can be made of one or multiple levels.

Hierarchies are classified as three ways:-
1. Balanced
2. Unbalanced
3. Ragged


Have a look at the exhibit given below, hope it will help you to understand about the types of hierarchies in better way


Balanced Hierarchies:-

Each level in a hierarchy has the same number of members above it as any other member at the same level


Un Balanced Hierarchies:-

Each level in a hierarchy may not the same number of members as the member which are at the same level, refer the hierarchy structure shown above.


Ragged Hierarchies:-


It is not like balanced Hierarchy, the logical parent member of at least one member is not in the level immediately above the member. It will skip the immediate parent. 

For Example: In a company, all the divisions will be reporting to the VP thru hierarchical structure, but Finance department will directly reporting to VP without any middle level.

Architecture Changes Between SSRS 2005 Vs. 2008

There are major changes between SSRS architecture 2005 and 2008. Have a look at the following exhibit first then go thru changes listed below: -

SQL Server Architecture 2005:-

 

SQL Server Architecture 2008:-

New Changes in SSRS 2008:-
1. Initially, all the SSRS related applications, services and utilities were running as an individual segment. In 2008, these components are merged as single windows service segment. It makes our jobs simple, easy to manage, deploy, configure the SSRS Objects

2. Report server no longer dependent on IIS. Instead, http.sys is used to handling authentication.  Http.Sys is called as HTTP Server API. In general word, HTTP.sys is just a driver, which allow us to communicate the clients (browser) with Report Server without Microsoft Internet Information Server(IIS). Also SSRS 2008 has all IIS components which require functioning without having any dependency with IIS.
 

3.Moving away from IIS, two major actions to be taken care by SSRS components:
 

 a.    Disabling some of the functionalities IIS handles which there are no equivalent in SSRS 2008, such as anonymous, client certificate and ISAPI.
 

 b. Enabling some of the functionalities that IIS used to handle for SSRS before. Things such as ability to add virtual directory, port, SSL certificate, logging mechanism and so on…
 

4. Report Server uses shared components from SQL Server, such as SQL CLR for ASP.NET management, network interfaces, and memory management capabilities. This implies that any advances in administration and logging in SQL Server can be leveraged in SSRS in future releases.
 

5. CLR Host – All the SSRS components are running under the control of CLR. So CLR is act as host.
 

6. the Report Manager application and the Report Server web service have been combined into a single Windows service