Home > SSRS Expert > Create Report Model in BIDS

Create Report Model in BIDS

This is from MSDN regarding report model:

In order to build a model, you need to 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 and data source view can be referenced in a report model (.smdl) file. Report Model Designer can generate report models from SQL Server and Oracle databases.

Here are some basic steps:

Instead of the normal Report Server Project, choose Report Model Project from BIDS.

image

Use the Data Source Wizard to create a data source (.ds file).

image

Use the Data Source View Wizard to create a data source view. Data source view is a logical model based on the underlying physical data model. This .dsv file describes the tables, their contents, and the relationships between them in terms of XML.

A data source view is a single, unified view of the metadata from the specified tables and views that the data source defines in the project. Storing the metadata in the data source view enables you to work with the metadata during development without an open connection to any underlying data source. Data source view allows to define the logical relationships, attributes, entities outside of the underlying data source.

in most case, use the defaults in Name Matching is good enough.

image

Select tables that are relevant for one project, or one sub-project as Included objects.

image

In the report model designer, all the relationships are defined correctly based on the default name matching rule, except 2 entities, which use the short-hand version of the primary key from amPortfolio entity.

image

To manually establish the relationship, find the source (foreign key) entity and the field, and right click to open the Create Relationship dialog. Note that the foreign key entity is the source, and the primary key entity is the destination, otherwise, you will get a warning to “Reverse” the relationship.

image

image

Once the Data Source View is created, the next step will be to create the Report Model.

Using the Report Model Wizard.

image

Accept the default to control how metadata will be generated from the data source.

image

Statistics, such as the number of unique instances of values within each attribute, and the cardinality information contained within the database,  are collected before the report model is generated.

image

It takes a few minutes to finish the report model.

image

Categories: SSRS Expert
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: