Thursday 13 September 2007

SQL Server Reporting Services 2005 XML extensions

Introduction



Microsoft SQL Server Reporting Services (SSRS) has support for a plethora of data sources. Out of the box, its data modules, called data extensions, allow you to build reports from SQL Server and Oracle database, as well as any other database that comes with an OLE DB or ODBC provider.
In SSRS2005 a new provider is available: XML Data provider. This enables you to create reports from ADO.NET datasets or XML documents from URL-addressable resources, e.g. Web services.
This entry shows a step-by-step example (basic report , no parameters) how to use this new SSRS 2005 feature.

XML Data Extension


Principle

The common way for a SSRS server-side report to get its data is through the specification of a relational database and the SQL statement to retrieve the data. The report (interpreted by the report engine) is the consumer and the database is the provider of the data.



It’s the SQL database data extension that will retrieve the data set specified in the report definition from the configured database during design time and run-time when the report has been deployed.

With the XML data extension is now possible to specify an xml document as data source or a web-service method that returns an ADO.NET (typed) dataset.




Instead of specifying a database and using SQL statements, you must point to a web service and specify the web methods and the Xpath for the returning result.

Benefits
  • Out of the box. No need to make a custom Data Extension for processing (serialized) ADO.Net datasets. Also you do not need to deploy extra software to the server and the report designer workstations.
  • You can put data retrieval and/or transformation in code behind the web service method instead of using pure SQL or stored procedures.

Concerns

  • Pure data push functionality still not available like in for example crystal Reports where you can give data to the report (and report engine). SSRS2005 has this possibility for client-side reports used together with the Report viewer control. Need for custom data processing extension for server-side reports.
  • ADO.Net serialization over SOAP is resource-intensive

Example

Webservice
Define a webservice with a webmethod that returns a (typed) dataset.







Configure the web service to use the IIS as host and not the default Visual Studio ASP.NET hosting server when you use VS2005 to build a web service. The web tab appears when you use the ASP.NET web service project template (available in VS2005 Service Pack 1 or you can download it as separate add-on)



Test the web service





Report data source
Specify a data source that point to the previous defined webservice. Choose XML as data extension. The connection string the URL to the webservice



Report data set
Specify the report data set. Instead of SQL syntax you must talk the “web-service” language. You can deduce this information from the web service test page and result page.
Basically you specify

  • Name of the webmethod
  • Xpath to get result from the web service response document , among others the name of the dataset and the name of the dataTable

    With a tool like Webservice Studio you can actually see what the soap messages look like when you execute a web method request.




Execute the data set


Report layout


Preview the report.


Report deployment


Run the report



Sources and recommended reading

Monday 10 September 2007

Software Architecture Review

Introduction

Reviews are an opportunity for others to eyeball your documents/design/code/software architecture and for you to inspect others’ work. They facilitate knowledge interchange. But their primary goal is to increase software quality. They help you to spot faults before they become real disasters.

I tried to bring together elements a reviewer can use in his/her software architecture review.
If you have any other elements or disagree with something, let me know.


Software Architecture

If you want some formal definitions what a software architecture is , I recommend reading the information on http://www.sei.cmu.edu/architecture/published_definitions.html#Modern


General tone in these definitions is that you need to make high-level decisions about the system you' re going to build;

  • What style are you going to use ? What is the structure?
  • How is going to function? How do structural components of the architecture work together?
  • How does it meet the needs of all the stakeholders?

For an existing system you can detect these key factors, that will give you an idea of the software architecture. An approach in evaluating software architecture is reasoning about the quality attributes a software architecture exhibits.

In the bullets below I tried to sume up the different quality attributes together with some typical things to look for when you're conducting a review. This list is not intended to be exhaustive. Maybe you have some ideas that you would like to share ....?



Performance

The time required to respond to stimuli (events) or the number of events processed in some interval of time.



Typical Design/Architectural principles to look for

  • Connection pooling - reducing the execution time overhead associated with establishing database connections by establishing a shared pool of connections
  • Load balancing – spreading the load evenly between a set of resources
  • Distributed processing
  • Caching – using a local copy of data to reduce access time
  • Lazy instantiation
  • Transaction Concurrency
  • Process isolation between OLTP and OLAP
  • Replication of data


Typical unit of measurement you could use

  • Transactions per unit time
  • Amount of time it takes to complete a transaction

Reliability

The ability of the system to keep operating over time in the context of application and system errors and in situations of unexpected or incorrect usage.(to perform in a predictable manner)

Typical Design/Architectural principles to look for

  • Using preventive measures (administration) e.g. recycling of server processes in IIS.6
  • ASP.NET /COM+ 1.5
  • Containment - COM+ server process isolation
  • Database transaction logs (rollback)

Typical unit of measurement you could use

  • Mean time to failure.


Availability

The proportion of time the system is up and running.

Typical Design/Architectural principles to look for

  • Fail-over - make the system more available as if one server instance is down, another instance can take over the work.
  • Transaction Manager - it improves availability and reliability by helping to ensure that the system is always in a consistent state and by providing a system-wide strategy for handling certain classes of failures.
  • Stateless design - When a particular stateless server fails, its work can be re-directed to a different server instance without implications for state management.

Typical unit of measurement you can use

  • Length of time between failures
  • How quickly the system is able to resume operation in the event of failure.


Security


Measure of the system’s ability to resist unauthorized attempts at usage and denial of service

Typical Design/Architectural principles to look for

  • Authorisation - How is the control access to information in the system organized once a user is identified and authenticated? (Role based ACL)
  • Authentication - identification and representation of end-user in your system and verification if he is telling the truth?
  • Auditing - verification and monitoring of the security policy
  • Integrity - Protection against improper or undetected modification of information in transmission or storage? (encryption)
  • Confidentiality - protection against improper disclosure of information during transmission and storage (encryption)
  • Denial of Service - continuity of services? (intrusion detection)
  • Data isolation (general public apps versus internal LOB apps)



Modifiability

Ability to make changes to a system quickly and cost effectively. .

Typical Design/Architectural principles

  • Client-server (separation of concerns) – This mechanism involves providing a collection of services from a central process and allowing other processes to use these services through a fixed protocol.
  • Independence of interface from implementation – This mechanism allows architects to substitute different implementations for the same functionality.
  • Separation – This strategy separates data and function that address different concerns. Since the concerns are separate, we can modify one concern independently of another . Isolating common function is another example of a separation strategy.
  • Encoding function into data meta-data and language interpreters – By encoding some function into data and providing a mechanism for interpreting that data, we can simplify modifications that affect the parameters of that data.
  • Run-time discovery no hard-coded connection strings, queue names, etc.

Typical unit of measurement

  • using specific changes as benchmarks and recording how expensive those changes are to make

Portability

Ability of the system to run under different computing environments. Sometimes considered as special kind of modifiability.


Typical Design/Architectural principles

  • Virtual machine

Functionality

Ability of the system to do the work for which it was intended.

Typical unit of measurement

  • Number change request

Extensibility

New feature implementation/replacement of components with improved versions and the removal of unwanted or unnecessary features or components

Typical unit of measurement

  • Easy, incremental addition of functionality (time, budget, etc)
  • Coupling/cohesion


Conceptual integrity

Underlying theme or vision that unifies the design of the system at all levels.

Interoperability

Interaction with other sub-subsystem, or a well defined access to externally-visible functionality and data structures or interaction with other run-time environments.

Typical Design/Architectural principles

  • Simple data-types
  • XML
  • Web services

Usability

How easy is it to use the program?

Typical Design/Architectural principles

  • GUI standards between product line

Typical unit of measurement

  • Number of errors made by a user familiar with prior releases or other members of the product line.

Maintainability

Problem fixing, repairing a software system after errors occur

Typical unit of measurement

  • Easy localisation
  • Ripple effects of change



Efficiency

Deals with the use of the resources available for execution of software, and how this impacts response times, throughput and storage consumption.

Typical Design/Architectural principles

  • Acquire late, release early
  • Reducing round-trips
  • Lowering traffic throughput, (only send what is necessary, only retrieve what is necessary)


Testability

How easy is it to test code unit, sub-systems, etc.

Typical design/architectural principles

  • Interface based programming
  • Inversion of control/Dependency injection
  • Classes with well defined responsibilities


Reusability


On SW arch level, the ability to reuse the SW architecture for another application.
On code level , framework aspects


Ease of deployment

How quickly can you deploy the system?

Typical measures

  • Installer programs (wizards)

Typical unit of measurement

  • Can be measured by the time and resources required to install the product and /or distribute a new unit of functionality


Ease of administration
Refers to the infrastructure, tools, and staff of administrators and technicians needed to maintain the health of the application. E.g. Change physical location of service with minimal impact on the rest of the system.

Typical unit of measurement

  • Decreased Support Cost: can be measured by comparing number of help desk calls for a standard period of time.


Scalability

Number of users changes while maintaining other qualities. Support continuous growth to meet user demand and business complexity. It must be possible to extend the minimum hardware configuration needed for the application with additional hardware to support increased workloads.

Typical Design/Architectural principles

  • Stateless design
  • Load-balancing
  • Concurrency (optimistic)


Debug-ability /Monitoring

Preparing application for easy and efficient debugging . Registration of abnormal behaviour. Real-time monitoring.

Typical Design/Architectural principles

  • tracing support
  • logging in exception handling mechanism


Development Productivity

Cost and time saving mechanism to aid development of applications based on the software architecture. The developers should be able to learn the architecture concept and how to implement it easily. Extending the development team with new developers should not cost much effort in instruction etc. A standardized way of working using templates and coding standards could help raise both the learning the curve and quality

Typical Design/Architectural principles

  • Framework
  • templates /code generation
  • best practices Coding checklist & standards