SQL Server Reporting Services – Report Models

A report model is a metadata description of a data source and its relationships. In order to create reports using Report Builder, you must first build a report model based on your SQL Server database, SQL Server 2005 or later Analysis Services cube, or Oracle databases running version 9.2.0.3 or later.

  1. For the user building the report, report models expose only specified views and items to choose from.
  2. A report model is a metadata layer on top of a physical database that identifies business entities, fields, and roles.
  3. To create model-based reports, at least one report model needs to be available.
  4. To build a report using the model that you just created, you must publish it to a report server. The data source and data source view are included with the model when it is published.

In order to build a model, you must first create a report model project. A report model project is a container for the model and consists of one or more data source (.ds) files, one or more data source view (.dsv) files, and one or more report model (.smdl) files. Only one Data Source (.ds) and Data Source View (.dsv) can be referenced in an .smdl file (Report Model).

Models are published to a report server or SharePoint library and users must have permissions to access the model in order to use it in Report Builder.

Create Report Model Project:

1. Open Business Intelligence Development Studio.

2. Create new project and select “Report Model Project” template.

3. Define a Data Source for this project.

After defining the data sources that you will use in your report model project, your next step is to define a data source view for the project. A data source view is a logical data model based on one or more data sources. However, only data source views based on a single data source can be used for report models.

Data source views facilitate the model design process by providing you with a useful representation of the data that you specified. Because metadata is stored in the data source view, you do not need to be connected to the underlying data source to work with the objects. You can rename tables, and fields, as well as add aggregate fields and derived tables, in a data source view without changing the underlying data source. For an efficient model, add only those tables to the data source view that you intend to use.

A report model is a metadata layer on top of a physical database that identifies business entities, fields, and roles. Once published, these models enable Report Builder users to develop reports without having to be familiar with database structures or understand and write queries. Models are comprised of entities, which are sets of related report items that are grouped together under a friendly name, predefined relationships between these business entities, and predefined calculations. Models are defined using an XML language called Semantic Model Definition Language (SMDL). The file extension for report model files is .smdl.

Report models have the following features:

  • Database fields and views can be given logical business names, so knowledge of the database structure is not required to produce reports.
  • Items can be grouped logically.
  • Relationships between items can be defined.
  • Model elements can be secured so that users can see only the data that they have permission to see.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s