Report Models provide an abstraction layer for business users to query the database without have to understand SQL

Report Models are created in BIDS and contain two principal elements:

1. A Data Source file to provide connection information
2. A Data Source View to describe the structure of the data

Only one data source can be provided for each report model, based on either SQLClient or OracleClient. Data Sources are added via Solution Explorer with the 'Add New Data Source' command.

For the DSV, provide user-friendly names for object names and relationships. Only add tables and views that will be used in the Report Model. Multiple tables can be combined into a named query, analagous to a View in the database. Named queries can be used to add derived columns and to filter data rows returned.

Add a DSV via Solution Explorer with the 'Add New Data Source View' command. Once a DSV has been created, the DSV can be edited by double-clicking the item in Solution Explorer to open the DSV Designer. Relationships can be added and 'FriendlyName' values can be edited. Use the DSV Designer to change table and column names: this way changes are preserved after re-generating the model

A single table can be replaced with a named query by right-clicking the table and selecting the 'Replace Table, With Named Query' command.

Generating the Model

With the data source and DSV in place, the Report Model can be generated either manually or using the 'Report Model Wizard'. Start the Wizard from Solution Explorer with the 'Add New Report Model'. The Wizard prompts for:

1. DSV
2. Report Model Generation Rules
3. Report Model Statistics
4. Report Model Name

On completion, the Report Model is created as an SMDL file.

A Report Model contains three types of objects:

1. Entities - tables in the DSV
2. Attributes - columns in a table
3. Roles - relationships between entities

When reviewing a model, make sure you have all the objects needed for reporting and note which objects should be removed

Report Model Designer consists of two panes: a tree pane, displaying all entities and; a details pane, showing attributes and roles for the selected object.

Each entity should have an aggregate attribute to count the number of records associated with an entity.

Report Models can be refined in BIDS to:

• Change the appearance of objects, eg alignment, format, sort direction and name
• To make it easier to find and recognise objects that will be added to Ad Hoc reports
• Add folders to organise objects better
• Add user filters (perspectives), to hide extraneous objects
• Create hidden folders for objects you plan to delete later
• Add expressions to store commonly used business logic. To add an expression, right-click an entity and select 'New, Expression'
• Add drill-through, allowing a user to click a field to display detail records for the row. Set the EnableDrillthrough property and then set the DefaultDetailAttributes property

Before giving users access to a model, test it by building some Ad Hoc reports.

Publishing a Report Model

Use the 'Deploy' command from BIDS to deploy the model to the server. During deployment a copy of the data source file and the SDML file is placed on the server. On subsequent deployments, only the SDML file is copied to the server by default. If a report model with the same name exists on the server, it is overwritten during deployment. Right-click the model in Solution Explorer and select properties to view the deployment settings:

1. OverwriteDataSources - True or False
2. TargetDataSource Folder - Data Sources
3. TargetModel Folder - Models
4. TargetServer URL - http://localhost/ReportServer

Right-click the model in Solution Explorer to run the Deploy command. To access the model from the Report Server to the Models folder on the report server. Follow the 'Home, Data Sources' link to check the Data Sources.

Change Management

If no reports depend on the report model, then you can use the Report Model Wizard to create and overwrite the previous report model file. The data source can also be changed as long as the new data source contains the same database objects with the same names used in the report model's DSV.

If a report model has dependant reports, the report model should be refreshed rather than replaced to preserve GUIDs for entities, attributes and roles. The report model can be regenerated from the DSV, preserving existing objects and adding new objects found. The 'Autogenerate' command can be issued at the model, entity or attribute level. If a column associated with an attribute has been deleted from the DSV, an error message is generated, and the attribute should be deleted manually from the report model.