Report Lifecycle – SQL Server Reporting Services 2008 R2

Report Lifecycle is based upon Creating, Managing, and Delivering Reports. All the phases having their own importance in the reporting lifecycle.

Creating Reports: You can create a report definition file using Report Designer or Report Builder 1.0/2.0. Report Designer is a full-featured report authoring tool that runs in Business Intelligence Development Studio. Report Builder features a Microsoft Office-like authoring environment and new features such as enhanced data layout, data visualization, richly formatted text, and on-demand rendering.

 Managing Reports and Other Items: One of the principal advantages of using Reporting Services is the ability to manage reports and related items such as folders, data source connections, and resources, from a central location. You can define security, set properties, and schedule operations. Report management includes the following tasks:

• Organizing the reporting environment into folders to store collections of reports.
• Enabling features such as My Reports, report history, and e-mail report delivery.
• Securing access to folders and reports by assigning users and groups to roles.
• Building shared schedules and shared data sources that you want to make available for general use.

Accessing and Delivering Reports: Once your report is ready, Reporting Services have two methods are available for accessing and delivering reports:

• On-demand access allows users to select the reports from a report viewing tool. You can use Report Manager, a Microsoft SharePoint Web part, an embedded ReportViewer control, or a browser.
• Subscription-based access automatically generates and delivers reports to a destination. You can deliver reports to an e-mail inbox or a file share.

An Overview – Business Intelligence with SQL Server 2008 / 2008 R2

Microsoft BI Box is a magical box for high-impact query, data integration, reporting and analysis server that provides services to the other components of the Business Intelligence Suite such as Answers, Dashboards, Data Mining, Reporting, and Analytic Applications.

 What is BI (Business intelligence)? – In single line “Transform Raw Data into Decisions Making Report”. In computer world Business Intelligence (BI) refers to computer-based techniques used in collecting, analyzing and presenting business data, such as sales revenue by products and/or departments or associated costs and incomes. Generally used for product management, sales, human resource and finance departments.

Currently we have several BI Tools; these tools/technologies provide historical, current, and predictive views of business operations. Common functions of Business Intelligence technologies are reporting, online analytical processing, analytics, data mining, business performance management, benchmarking, and predictive analytics.

 An Overview of Business Intelligence and Data Warehouse: Business intelligence (BI) helps enterprises/companies to gain past information from historical data and formulate strategic initiatives for the future growth. The historical data are stored as an electronic repository, which is called a data warehouse.

A data warehouse is a system of records (a business intelligence gathering system) that takes data from a company’s operational databases (Data from Heterogeneous Data Stores) and other data sources and transforms it into a structure conducive to business analysis and reporting. Finally, the data is made available to the end user for analysis, querying, and reporting.

A data warehouse system has organized & optimized storage of historical records gives the business an intelligence storage system to understand the business. Business analysis can be done in reactive mode or predictive mode. Reactive mode business analysis (also known as business analytics) is a function where business analysts and other business users investigate the records and identify patterns and trends, and make business decisions to improve their business processes. Predictive mode analysis (also known as predictive analytics or data mining) is done using mathematical models to predict future trends on the system of records. The general approach to storing business data in a dimensional model and providing quick answers by slicing and dicing the business data is known as On Line Analytical Processing (OLAP). OLAP systems are architected in different ways. The most common types are MOLAP (Multidimensional OLAP), ROLAP (Relational OLAP), and HOLAP (Hybrid OLAP).

 Extract, Transform, and Load Systems: Commonly expressed as ETL, extract, transform, and load refers to a set of services that facilitate the extraction, transformation, and loading of the various types of source data (for example, relational, semi-structured, and unstructured) into OLAP cubes or data mining structures. SQL Server 2008 includes a sophisticated set of tools to accomplish the ETL processes associated with the initial loading of data into cubes as well as to process subsequent incremental inserts of data into cubes, updates to data in cubes, and deletions of data from cubes.

Report Processing Systems: Most BI solutions use more than one type of reporting client because of the different needs of the various users who need to interact with the cube data. An important part of planning any BI solution is to carefully consider all possible reporting tools. A common production mistake is to under-represent the various user populations or to clump them together when a more thorough segmentation would reveal very different reporting needs for each population.

MS SQL BI – Core Component

Based on Microsoft SQL Server database platform, Microsoft has developed or acquired technology which has allowed it to offer its clients a full and comprehensive product suite for Business Intelligence software market.

SQL Server started out as a database product but has grown to include additional capabilities that put core BI concepts into action.

SQL Server Database Engine: The core program used to create standard relational databases, including data warehouses and data marts. If you see the above image, Database Engine is the base for all other BI component.

SQL Server Reporting Services (SSRS): Software for creating reports based on Microsoft (and nearly all other) data sources. A web based product, it can deliver interactive report functionality as well as display refreshed reports. The web interface also allows users to subscribe, view and manage their reports.

SQL Server Integration Services (SSIS): Software for connecting to a multitude of data sources, transforming the data into a single useful format, and loading it into a Microsoft SQL Server database — all using the ETL (Extract, Transform, and Load) process.

SQL Server Analysis Services (SSAS): A Microsoft version of Online Analytical Processing that stores massive amounts of data in a special database called a Cube for very quick real-time analysis.

SQL Server 2008 / 2008 R2 – CheatSheet

SQL Server 2008 / 200 R2 CheatSheet will provide you a new way to learning the database. It is a 20 page PDF document which will give you a bird eye view of SQL Server 2008 and 2008 R2. Please use the password: Harinam to open the file.

This document wills also having the best interview SQL Query.

Try it and leave your comments / Suggestions / Changes.

Please download the following PDF file.

SQLServer2008R2_CheatSheet_V1.0

Thanks,
Harinam

Common Table Expressions

• Common table expressions (CTEs) are defined in the SQL_99 standard, and let you create queries that work with recursive data. CTE-based recursive queries are generally more readable than those created using traditional Transact-SQL syntax.
• Definition: A CTE is a named temporary result set based on a regular SELECT query. You then use the result set in any SELECT, INSERT, UPDATE, or DELETE query defined within the same scope as the CTE.
• Advantages of CTEs: Using CTEs provides you with two main advantages:
• Queries with derived tables become simpler and therefore more readable: Traditional Transact-SQL constructs used to work with derived tables usually require a separate definition for the derived data (such as a temporary table or table valued function). Using a CTE to define t he derived table makes it easier to see the definition of the derived table with the code that uses it.
•  You can traverse recursive hierarchies: CTEs reduce the amount of code required for a query that traverses recursive hierarchies (such as when rows in the same table can be linked with a self-join).
• Example of a simple CTE: The following example shows the creation of a CTE named TopSales that displays the number of sales for each salesperson based on information provided by the SalesOrderHeader table.

WITH TopSales (SalesPersonID, NumSales) AS
(
SELECT SalesPersonID, Count(*)
FROM Sales.SalesOrderHeader GROUP BY SalesPersonId
)
SELECT * FROM TopSales WHERE SalesPersonID IS NOT NULL ORDER BY NumSales DESC
The query then restricts the TopSales CTE based on the SalesPersonID and sorts the rows based on the NumSales column.
Example of using a CTE and a join: The following example shows the same CTE named TopSales joined with the SalesPerson table to retrieve the salesperson’s year-to-date sales figure and number of sales.
SELECT ts.SalesPersonID, sp.SalesYTD, ts.NumSales FROM Sales.SalesPerson sp INNER JOIN TopSales ts
ON ts.SalesPersonID = sp.SalesPersonID ORDER BY NumSales DESC