Wednesday, December 27, 2006

Reporting Services and Business Intelligence


Reporting

Information is supposed to be the second most important resource of any business (the most important resource being "people"). Having relevant information at the right time makes all the difference while making important business decisions. When the size of the business organization is small, maintaining business data and extracting the required information is a fairly simple task. But consider an organization the size of a huge corporate, with businesses spread across the world, working in different domains, maintaining several huge databases containing millions of records.
So, there you go; you have all the data you need, but there is this small catch: the data is spread across multiple databases, flat files, excel sheets, CSV files. The databases don't have a uniform architecture, as they were designed to serve a very specific purpose: store some very relevant business data.
What I mean by this, is that it takes a whole lot of re-organization of the raw data so you can put it to use, and derive any sensible conclusions regarding your business statistics. Most business databases store data directly related to the business operations, and any report generation has to extract the data from these databases and convert them into a suitable format before the reports can be generated.
The reports are the end product of the entire report generation process, which gives the business enterprise all the facts and figures that enable them to take the appropriate business decisions.
One might ask, "why is that such a big deal? Isnt that why the databases are there? Just run an SQL query and you get all the data that you want"
Point well taken, and for a simple enterprise with small busines operations, this might work fine. But we are talking about a different situation here. What works for a small organization, in this case does not scale well when the case is extended to a huge business enterprise like the one described above. It may not be fasible to run a distributed query across multiple databases, data files, excel sheets, and CSV files. The kind of processing power and time required to complete such an operation would be simply not feasible. Moreover, any small change in any of the data sources will bring the entire process crashing down.
So, the point is that all the data needs to be consolidated into a central repository, into a suitable format, from where it can be queried, in order to improve the processing and time efficiency. Aha, we are talking about a data warehouse.
Another important consideration is that delivering reports is more than just processing data through queries and making it available. It is providing information when you need it, on any platform, in any format. A common solution is generating reports in HTML format and viewing it on an internet browser. This is fine if you have a machine that can connect to a server. But you may need reports when you are on a flight, when you are with the customer, when you are in your car. This includes platforms like your pager, your PDA, you fax machine, your laptop.
What if you need those reports in a suitable format like PDF, CSV, TIFF or XML for further analysis. You may even need the reports in some custom extension/format. It is not enough. Even now, a single format and a single delivery channel may not be enough. How would you know which ones you will need at a particular point of time?

Enter Reporting Services

To take care of all these issues, we have Reporting Services. Reporting Service provides you a layer of abstraction and hides all these details from you. With a reporting server, all the reports may be rendered in any of the above specified formats and may be delivered on any channel.
Reporting services provide a clear separation between the report design and the rendering of the reports in a desired format and the way in which it will be consumed.
Report Designing and Generation
Thus the entire process of report generation and delivery consists of two separate processes: Report designing, and report generation & delivery. Both these processes are independent of each other. Hence, a report designer need not worry about the format report will be rendered in, or the channel/platform where it will be delivered.
Similarly, the report generation process does not need to know how the reports are designed. All it needs to do is pass the relevant parameters that are required as inputs for the report generation process. This report generation process renders the report in the desired format and finally delivers it to the end user.

SQL Server Reporting Services

SQL Server Reporting Services, also known as SSRS comes as a part of the SQL Server 2005 suite free of cost. The SSRS includes all the development and management modules needed to create/define reports, publish them on to a reporting server, and render them in HTML, PDF, EXCEL and CSV formats. Whats more, you can even develop your own custom format and register it with the SSRS.



As you can see from the diagram, the SSRS installs a dedicated web service on your machine. This web service has an interface which can be accessed from http://reportServerName/Reports
The SSRS provides a full-featured set of APIs that you can use to integrate Reporting Services with custom business applications. This includes the full featured web service that provides access to the complete functionality of the reporting server. The SSRS can use any data source like an SQL Server database, or an OLE DB data source as the primary database from which the reports will be generated.
:To understand all this better, lets just consider the entire reporting life cycle:
Report Authoring: Developers can define reports to be published on to the Reporting Server using a report design tool. One popular report design tool is proided by the Business Intelligence Development Studio (BIDS) which is installed along with the full suite of SQL Server 2005. These report design tools use the Report Definition Language (RDL) which is an XML based industry standard. The term report in this case might sound a little ambiguous, as this is not really a business report; but simply an XML file which specifies the connection string to the datasource to be used, the query to be executed, and other metadata. There is a corresponding Report Data Source (RDS) file which has the connection string to the real data source. The RDS and RDL files are then deployed on the reporting server. To deploy these files, you can use the BIDS tool, or do it programmatically using the web methods provided by the report server web service.
Report Generation and Delivery: Once the RDL and RDS files are deployed, the reports can be generated on request or as a response to a particular event. Reports can be viewed using the web interface provided for the report server i.e. http://reportServerName/Reports . Reports can also be generated and rendered in the desired format using the web methods provided by the report server web service.
Report Management: Administrators can use the Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history. Reports can be executed on demand or on a specified schedule.
In order to get reporting services up and running on your machine, do a default installation of SQL Services 2005 and let the installation configure the reporting service for you. Once the installation is completed, point your browser to: http://localhost/Reports, and you will be greeted by something like this:



Click on the project1 folder and you will see the reports deployed. Clicking on a report runs it and the report generated can been seen on the page. You can then export it in any of the supported formats.
An introduction to SSRS programming can be found at:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx

Reporting Services and Business Intelligence


Reporting

Information is supposed to be the second most important resource of any business (the most important resource being "people"). Having relevant information at the right time makes all the difference while making important business decisions. When the size of the business organization is small, maintaining business data and extracting the required information is a fairly simple task. But consider an organization the size of a huge corporate, with businesses spread across the world, working in different domains, maintaining several huge databases containing millions of records.
So, there you go; you have all the data you need, but there is this small catch: the data is spread across multiple databases, flat files, excel sheets, CSV files. The databases don't have a uniform architecture, as they were designed to serve a very specific purpose: store some very relevant business data.
What I mean by this, is that it takes a whole lot of re-organization of the raw data so you can put it to use, and derive any sensible conclusions regarding your business statistics. Most business databases store data directly related to the business operations, and any report generation has to extract the data from these databases and convert them into a suitable format before the reports can be generated.
The reports are the end product of the entire report generation process, which gives the business enterprise all the facts and figures that enable them to take the appropriate business decisions.
One might ask, "why is that such a big deal? Isnt that why the databases are there? Just run an SQL query and you get all the data that you want"
Point well taken, and for a simple enterprise with small busines operations, this might work fine. But we are talking about a different situation here. What works for a small organization, in this case does not scale well when the case is extended to a huge business enterprise like the one described above. It may not be fasible to run a distributed query across multiple databases, data files, excel sheets, and CSV files. The kind of processing power and time required to complete such an operation would be simply not feasible. Moreover, any small change in any of the data sources will bring the entire process crashing down.
So, the point is that all the data needs to be consolidated into a central repository, into a suitable format, from where it can be queried, in order to improve the processing and time efficiency. Aha, we are talking about a data warehouse.
Another important consideration is that delivering reports is more than just processing data through queries and making it available. It is providing information when you need it, on any platform, in any format. A common solution is generating reports in HTML format and viewing it on an internet browser. This is fine if you have a machine that can connect to a server. But you may need reports when you are on a flight, when you are with the customer, when you are in your car. This includes platforms like your pager, your PDA, you fax machine, your laptop.
What if you need those reports in a suitable format like PDF, CSV, TIFF or XML for further analysis. You may even need the reports in some custom extension/format. It is not enough. Even now, a single format and a single delivery channel may not be enough. How would you know which ones you will need at a particular point of time?

Enter Reporting Services

To take care of all these issues, we have Reporting Services. Reporting Service provides you a layer of abstraction and hides all these details from you. With a reporting server, all the reports may be rendered in any of the above specified formats and may be delivered on any channel.
Reporting services provide a clear separation between the report design and the rendering of the reports in a desired format and the way in which it will be consumed.
Report Designing and Generation
Thus the entire process of report generation and delivery consists of two separate processes: Report designing, and report generation & delivery. Both these processes are independent of each other. Hence, a report designer need not worry about the format report will be rendered in, or the channel/platform where it will be delivered.
Similarly, the report generation process does not need to know how the reports are designed. All it needs to do is pass the relevant parameters that are required as inputs for the report generation process. This report generation process renders the report in the desired format and finally delivers it to the end user.

SQL Server Reporting Services

SQL Server Reporting Services, also known as SSRS comes as a part of the SQL Server 2005 suite free of cost. The SSRS includes all the development and management modules needed to create/define reports, publish them on to a reporting server, and render them in HTML, PDF, EXCEL and CSV formats. Whats more, you can even develop your own custom format and register it with the SSRS.



As you can see from the diagram, the SSRS installs a dedicated web service on your machine. This web service has an interface which can be accessed from http://reportServerName/Reports
The SSRS provides a full-featured set of APIs that you can use to integrate Reporting Services with custom business applications. This includes the full featured web service that provides access to the complete functionality of the reporting server. The SSRS can use any data source like an SQL Server database, or an OLE DB data source as the primary database from which the reports will be generated.
:To understand all this better, lets just consider the entire reporting life cycle:
Report Authoring: Developers can define reports to be published on to the Reporting Server using a report design tool. One popular report design tool is proided by the Business Intelligence Development Studio (BIDS) which is installed along with the full suite of SQL Server 2005. These report design tools use the Report Definition Language (RDL) which is an XML based industry standard. The term report in this case might sound a little ambiguous, as this is not really a business report; but simply an XML file which specifies the connection string to the datasource to be used, the query to be executed, and other metadata. There is a corresponding Report Data Source (RDS) file which has the connection string to the real data source. The RDS and RDL files are then deployed on the reporting server. To deploy these files, you can use the BIDS tool, or do it programmatically using the web methods provided by the report server web service.
Report Generation and Delivery: Once the RDL and RDS files are deployed, the reports can be generated on request or as a response to a particular event. Reports can be viewed using the web interface provided for the report server i.e. http://reportServerName/Reports . Reports can also be generated and rendered in the desired format using the web methods provided by the report server web service.
Report Management: Administrators can use the Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history. Reports can be executed on demand or on a specified schedule.
In order to get reporting services up and running on your machine, do a default installation of SQL Services 2005 and let the installation configure the reporting service for you. Once the installation is completed, point your browser to: http://localhost/Reports, and you will be greeted by something like this:




Click on the project1 folder and you will see the reports deployed. Clicking on a report runs it and the report generated can been seen on the page. You can then export it in any of the supported formats.
An introduction to SSRS programming can be found at:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx