Sunday, November 14, 2010

Report Model Overview

Enterprise reporting solutions are managed in much the same way as any typical IT project and usually focus on the functionality and report types that are predictable and add strategic value to the business.
Ad hoc report authoring is a complex task that does not fit a traditional business profile. Information workers know what information they need and how their report should look, but they usually don’t have the technical skills necessary to create a rich report design, to understand real-world database schemas, or to use formal query languages such as SQL and MDX. In contrast, IT staffers understand report design, the technical aspects of the data source schema, and formal query languages, but they are not available on demand to analyze, design, and deploy urgent or one-time reports. Therefore, businesses need a reporting solution that enables information workers to create reports on demand and that speeds up simple report generation by IT staff. The appropriate solution should cover two objectives:
• Make physical data source schemas easier to understand and utilize.
• Simplify the overall report-authoring experience for non-technical employees.

You can fulfill the first requirement by creating a metadata layer on top of the database schema and preparing it to present the most-frequently requested data in the most intuitive and accessible manner by using business taxonomy. The metadata layer will then translate report design, filtering, and parameters into the correct and optimized native queries against the underlying physical data source.
The second requirement is met by combining query design and layout design into simplified, high-level user actions. Report authoring using Report Builder starts with a predefined report template, which modifies the query and report layout together as the user adds or removes fields, groups, and subtotals. Templates drive the task of setting report layout, adding some uniformity to reports produced by different users.
SQL Server 2008 Reporting Services addresses these two requirements with two new technologies known as report models and Report Builder. The metadata layer is implemented in Semantic Model Definition Language (SMDL) models and the Semantic Query Engine. The technology to simplify report authoring is provided by the Report Builder application. In the following section you will learn more about these tools and technologies.

Ad Hoc Reporting Components
The following components comprise the ad hoc reporting functionality in Reporting Services.
• Report Builder client (end-user report creation tool).
• Report model (abstraction layer that provides business context to the end user).
• Model Designer (design tool used to create the report model).
• Semantic Query Engine (processor that generates data-source specific queries and accesses relational and multidimensional data sources when necessary).
• The report models and the user-created reports that are deployed on a report server and managed as standard report server objects.

Report Model
The report model is the metadata layer component that serves as the basis for building reports. Report designers, both end users and IT staff, will use the report model as the data source for the report instead of directly accessing a relational database or OLAP cube data source when designing reports. The report model contains three key elements necessary for the ad hoc reporting process to function smoothly: bindings to the underlying data-source schema, a logical representation of the schema in the form of business entities that are familiar to end users, and the default information to be used according to the role that the entity plays in the report (primary entity, lookup entity, summary, and so on). A report model definition is an XML file that follows the Semantic Model Definition Language (SMDL) specification. Therefore, report models are also referred to as semantic models, SMDL, or SMDL models.

Report Model Components
Report models primarily consist of the following three item types:
• Entities are the equivalent of objects or tables in a database. Entities are composed of attributes and roles.
• Attributes are the equivalent of object properties or columns in a database table.
• Roles are the equivalent of object references or relational foreign keys and join operations.
Additionally, report models incorporate some organization functionality, allowing administrators to create folders (containers) and perspectives (sub-models). Consequently, report models for larger data sources can be more focused for their intended audience.

Semantic Model Definition Language
SMDL is an XML specification that defines the items allowed in a report model file. The report model file extension is ".smdl". SMDL defines items similar to objects, attributes, and references that are stored in collections whose root ancestor is the Semantic Model object itself.

Model Designer
You can create report models manually or generate them automatically. SQL Server 2008 provides three tools for generating report models: Model Designer, Report Manager, and SQL Server Management Studio. Model Designer is an editing environment, embedded in SQL Server Business Intelligence Development Studio, that lets IT staff (development and/or operations) generate a draft report model from a relational data source and then refine it to match actual user reporting needs. Report Manager and SQL Server Management Studio can automatically generate a report model from a SQL Server or Oracle relational data source or an Analysis Services multidimensional data source. However, you cannot use these tools to customize a report model after it has been generated. You can use Model Designer to customize models generated from relational data sources, but not from Analysis Services data sources.

Semantic Query Engine
The report model maps the data used in report definition to the schema in the data source. This mapping serves two purposes: first, it gathers the data to display in the report, and second, it filters the data request according to default filters, security filters, and report parameters.
The Semantic Query Engine’s job is to build and execute the appropriate queries against the SQL Server relational database or Analysis Services cube in Transact-SQL or MDX, respectively.

Report Builder
Report Builder is a client tool that lets users create, edit, view, and save report definitions from report models. Report definitions are stored as XML files that follow the Report Definition Language (RDL) specification, which is the same specification that all Reporting Services reports use. Thus, the only difference between reports designed with Report Builder and those designed with other tools is that the former ones always use a report model as the data source, while the later ones may or may not use a report model. Report Builder is a Microsoft .NET Framework application deployed dynamically via Click Once technology to the client. It requires the .NET Framework version 2.0 to be deployed on the client workstation. When a user starts Report Builder, it is installed automatically from the report server to a client workstation. The application is cached locally on the client workstation, updating automatically when a new version is deployed to the report server.

Report Models from SQL Server Relational Databases
You can create and edit an SMDL model from scratch by creating entities and adding attributes and roles to them. Both entities and attributes bind to specific fields in the underlying schema. However, you can accelerate this task by using the model-generation options provided by Reporting Services. You can use either the Model Designer to manually create a report model, or you can use Reporting Services management tools to automatically generate and publish a model. The Report Model Wizard, invoked from the Model Designer, creates a draft model and provides a user interface for specifying rules for model generation, whereas the automated process uses predefined rules.

Create a New Report Model Project
Report Model Projects contain a Data Source definition file, a Data Source View definition file, and a Model definition file. We will gain exposure to each of these components in the respective section of the procedure that follows.
1. Click the Start button.
2. Select Microsoft SQL Server 2008 within the Program group of the menu.
3. Click SQL Server Business Intelligence Development Studio Close the Start Page tab.
5. Select File --> New on the Visual Studio main menu.
6. Select Project from the cascading menu
7. Select Business Intelligence Projects in the Project types pane of the dialog.
8. Select Report Model Project in the Templates pane

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2008 components are installed in your environment, as well as whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.
9. Change the project Name (currently displaying a default) to the following:
The Solution Name changes to match the project Name by default.
10. Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).
11. Click OK to accept our input and to create the new Report Model Project.
The New Project dialog closes, as Visual Studio creates the project. Both solution and project RS028 Intro Report Model appears in the Solution Explorer
Solution Explorer presents a tree view of the objects contained in the solution, the primary management unit within the Business Intelligence Development Studio, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type.
As we can see in the present instance, the Report Model Project template, upon which our RS028 Intro Report Model Project was based, contains the following folders:
• Data Sources
• Data Source Views
• Report Models

Define a Data Source
Defining a Data Source, as we have discovered in past articles, is typically the first step we take with most BI Projects – be they Analysis Services, Report Model, or other varieties. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual source of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2008, the AdventureWorks database.
Let's create a Data Source within our new RS028 Intro Report Model Project by taking the following steps:
1. Right-click Data Sources folder within the Solution Explorer.
2. Select Add New Data Source from the context menu that appears
The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page
3. Click Next.
The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. (It also allows us to base a Data Source upon a connection that has been previously created, as long as the existing Data Source definition exists within the same project, within another project housed in the same solution, or from within another Report Model Project.) Select how to define the connection page appears. 4. Ensuring that the radio button to the left of Create a data source based on an existing or new connection is selected, click New. The Connection Manager dialog appears.
5. Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.
6. Type \ select the appropriate server name / server name with instance into the Server name box.
We can substitute "localhost" or "localhost\" here, if it is appropriate to our environment.
7. Select / complete the appropriate authentication choice for your environment in the section labeled Log on to the server (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article).
8. Select AdventureWorks in the Select or enter a database name selector box, within the Connect to a database section of the dialog.
The Connection Manager dialog appears
9. Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.
We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears
10. Click OK to dismiss the message box and return to the Connection Manager dialog.
11. Click OK on the Connection Manager dialog to accept our settings and create the data connection.
We return to the Select how to define the connection page of the Data Source Wizard
12. Click Next.
The Completing the Wizard page appears.
13. Replace the name that appears in the Data source name box with the following:
RS028_Adventure Works
The Completing the Wizard page appears
14. Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.
The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer
Having created a Report Model Project, and defined a Data Source, we are ready, to define a Data Source View.

Define a Data Source View
Our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in both Analysis Services and Report Model Projects. It is important to realize, as we work with a "live" data connection that we have defined, that we could certainly continue our development efforts with the metadata without an open connection. The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.
To define a Data Source View, we will take the following steps:
1. Right-click Data Source Views folder within the Solution Explorer.
2. Select Add New Data Source View from the context menu that appears
The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard
3. Click Next.
We arrive at the Select a Data Source page, where we see the Data Source we created in the previous section, RS028_Adventure Works, in the Relational data sources list box on the left side of the page. Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. A Data Source View for a Report Model Project, unlike a Data Source View for an Analysis Services Project, can only reference a single Data Source.
4. Click Next.
We arrive at the Select Tables and Views page, where we see the various tables of the Adventure Works data source appear in the Available objects list box on the left of the page.
5. Select all tables in the Available objects list, by clicking the button marked ">>"
The tables in the Available objects list box move to the included objects list on the right half of the page
6. Click Next.
The Completing the Wizard page appears.
7. Replace the name that appears in the Data source name box with the following:
RS028_Adventure Works
The Completing the Wizard page appears
8. Click Finish to create the new Data Source View, and to dismiss the Data Source Wizard.
The Wizard closes, and the new Data Source View appears in the Data Source Views folder within the Solution Explorer. We have defined a Data Source View that contains the metadata for the entire Adventure Works sample database. Naturally, we might have only entrained select tables, based upon the business requirements of the intended audience of the model, but for purposes of our practice session, this will serve to illustrate the concepts. We will next define our Report Model based upon some of this metadata, which designated information consumers can then use within the Report Builder.

Define the Report Model
Report Models themselves represent an abstract layer, as we noted earlier, within which we can create business-based entities from which consumers can select report items. Let's create a Report Model next, and then discuss publishing it for consumer use.
1. Right-click the Report Models folder within the Solution Explorer.
2. Select Add New Report Model from the context menu that appears
The Report Model Wizard appears, opening with the Welcome to the Report Model Wizard page
3. Click Next.
We arrive at the Select Data Source View page, where we see RS028_Adventure Works, the Data Source View we created in the previous section, displayed within the Available data source views list box
Our newly created Data Source View is positioned as the default, and will serve us in the creation of our new Report Model.
4. Click Next.
We arrive at the Select report model generation rules page, where we see the rules upon which automatic metadata generation will be based. For purposes of the current session, we will allow selection of all the defaults
5. Click Next.
The metadata is combined into entities. The Collect Model Statistics page appears next.
6. Ensure that the checkbox to the immediate left of Update model statistics before generating is selected, to ensure that database statistics upon which the Report Model depends are updated from the Data Source View
7. Click Next.
We arrive at the Completing the wizard page, where we see that, by default, the Report Model has been given the same name as the underlying Data Source View, RS028_Adventure Works
8. Click Run.
The statistics update occurs, and then the rules that were selected on the Select report model generation rules page are applied within the Report Model creation process. After all passes are completed, the status messages come to an end, and the Finish button becomes enabled
9. Click Finish.
the new Report Model appears in the Report Models folder, within the Solution Explorer
Moreover, we see that the entities within the new Report Model appear on the new Report Model tab of the Designer
10. Click the Address entity on the Report Model tab, in the left Model tree.
We note that a list of fields, folders and roles (where appropriate), for an entity we select, is displayed in the middle pane of the tab. Moreover, properties for the selected entity appear in the Properties pane, as is the general case with objects throughout the Visual Studio environment.
We can define new folders, source fields, expressions, roles and filters to further add to the richness of our Report Model. We do this by simply right-clicking within the pane, selecting New, and then selecting the object type from the cascading menu

Publish the Report Model Project
We "deliver" our design efforts to the information consumers by publishing the Report Model Project to a report server. We will publish the components of our project by taking the following steps.
1. Ensure that the Report Server information is properly configured for the project, by right-clicking the new RS028 Intro Report Model Project in Solution Explorer.
2. Select Properties from the context menu that appears
The Properties page appears, with the report server URL specified within the TargetServerURL property setting
By default, the Business Intelligence Development Studio general setting is as shown above, http://localhost/reportserver. Once we have the report server aligned as we desire, deployment is a simple exercise.
3. Make changes as appropriate to the report server URL within the TargetServerURL property setting.
4. Click OK to accept changes, and to dismiss the Properties page.
5. Right-click the RS028 Intro Report Model project in Solution Explorer, once again.
6. Click Deploy from the context menu
The Model Project uploads successfully to the report server (we can view it via Report Manager to verify the upload). To make the new model available to our information consumers, we must provide access via role assignments. We will take up these steps for using Report Builder, as well as the details of various in-depth techniques and nuances in Report Model design, in separate articles within the MSSQL Server Reporting Services series.