End-User Ad Hoc Query and Reporting - SQL Server 2008

While BIDS is a great place for a developer or database administrator (DBA) to create reports, and the stand-alone Report Builder is a great place for the power user, most end users will not understand those environments. This is why the Report Builder client exists. From a technology standpoint, Report Builder uses Reporting Services on the back end to create and show the reports. What is actually produced by Report Builder is RDL that is executed on the server. For this reason, Report Builder works only when a user is online.

Report Builder uses a metadata model, called the Report Model, which sits between the end users and the data they access. So, the different pieces of the Report Builder architecture are the Report Builder client, the Report Model and its designer, and the server side of Reporting Services.

The Report Builder Client

The Report Builder client is a report designer that creates RDL on the fly, and this RDL gets passed to the server. Since most end users do not understand relational databases or sometimes even OLAP structures, the semantic data model takes the complexity of the database and puts it in terms that the end user understands, such as entities, relationships, and aggregates. This means that the full power of the relational database and your OLAP systems is used, while reducing the complexity of getting the data needed for the report. Plus, end users do not have to depend on information technology folks or developers to build reports for them; they can self-service their needs through the Report Builder client.

The Report Builder client is aWindows Form application built using .NET. You may be wondering how you could deploy this application to all your different end users, since it is Windows-based. Well, the Report Builder client actually leverages some deployment technology in .NET called ClickOnce. ClickOnce provides Windows-based interactivity and applications, with the deployment model of web applications. When end users click the Report Builder button in Report Manager, ClickOnce checks their machine to make sure that the system has the .NET 2.0 Framework (or higher) and also that the latest version of the Report Builder client that is published on the server. If the system does not have these components, they are downloaded and installed, making for a very easy deployment experience. You could also deploy the client via standard mechanisms, though, such as Systems Management Server (SMS), and when the end user clicks, if the versions match, nothing will be downloaded.

Realize that the Report Builder client is not Report Designer. It does not have all the power and flexibility that Report Designer has. There is a good reason for this, since that power and flexibility is a liability for an end-user tool that is supposed to be simple and easy to use. Therefore, you may be frustrated by the perceived lack of flexibility in the tool, but end users will find it easier to create reports because it is structured and works only in a certain way. However, you can open a report created with the Report Builder client in Report Designer. In that case, you will be working against the semantic data model and not the data source underneath. Any reports that you modify in Report Designer may not be able to go back to Report Builder due to the differences in functionality. It is usually a one-way street between the two.

The Report Model and Semantic Model Definition Language

A Report Model is built using the Semantic Model Definition Language (SMDL), which is an XML-basedgrammar that describes your data sources in human-readable terms. Think of it as a universal translatorfor your data sources from geek to end user. Building a Report Model uses the other pieces of SQL Server that both SQL Server Integration and AnalysisServices use, which are data sources and data source views.

Data sources are straightforward. The only caveat is that currently Report Models support only SQL Server and Oracle as data sources. The reason for this is that the queries in SMDL must be translated to data-specific queries for the underlying source. However, the query translator that is built into Reporting Services is pluggable, so that in future versions, Microsoft could add more data sources. Also, only one data source—in fact, if you are working with databases, only one database is supported per model. So, you cannot query relational and OLAP data sources together; you can query only OLAP or relational databases in a single model.

Data source views (DSVs) allow you to separate physical schema from virtual schema. Think of it this way: you may have underlying schema in your relational database that you cannot modify, but in order to make the schema better for end users, you need to add schema or modify schema. With DSVs, you can perform these actions in the metadata for the DSV rather than in the underlying data source. You can create calculated columns, virtual tables, and other schema changes in DSVs without modifying the original data source.

SMDL is made up of entities, folders, attributes, expressions, perspectives, and roles. Entities correspond to a table or query view in the underlying data source. For example, you may have a product entity that maps to the product table in your database. Folders are just containment vehicles for entities. If you want to group entities together, put them in a folder. Attributes correspond to columns or named calculations on an entity. For example, the price attribute on the product entity could just be the price column in the products table, or it could be a calculated aggregate that exists only in the DSV.

Expressions are what they sound like: calculated fields in your model. You can create expressions that create aggregates, do string manipulation, perform conversions, and perform other functions. You control the creation of expressions while the Report Model creates attributes for you automatically. Perspectives provide the ability to break a large model into smaller models. You can create perspectives that contain a subset of an existing model. End users can then see the perspectives they have permissions on from the client. You can think of perspectives like relational database views.

With views, you can hide your underlying tables and display information only to users who have permissions on the view.

Roles define relationships among your data. For example, an employee has an address or a customer has orders. You can define roles to have a one-to-one, one-to-many, or many-to-many relationship. Customers do not have one order but have many orders. The way roles are surfaced in the client is through the navigation.

The reason for having the Report Model, beyond the simplicity it presents to the end user while still providing a powerful model for the DBA and developer, is the ability to provide rich relationships and infinite drill-through. Say you enable infinite drill-through on your data model, and you own the Enterprise Edition of SQL Server (since infinite drill-through is only supported there). When a report is rendered, if there is a path through the model, users can click attributes that send queries, which in turn generate drill-through reports. End users do not need to create each individual report themselves, and DBAs do not need to manually link together reports, since they can just mark relationships in the model.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics