• Article

Creating a Report Model Project

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.

Data Sources

A data source file contains the information that report model needs to connect to the database. This file contains authentication information, a data source object name, and a connection string. In Model Designer, a .ds file can only be built from a SQL Client Data Provider. For more information, see Defining Data Sources (Analysis Services).

Note

You can create new data source views within report model projects or add existing data source views to the project. When adding existing data source views, verify they are based only on one data source.

Data Source Views

A data source view document is a description of the database to which the data source file is pointing. This .dsv file describes the tables, their contents, and the relationships between them in terms of XML. A data source view can be based on one or more data sources. However, only data source views based on a single data source can be used for report models. For more information about manipulating data source views, see Adding or Removing Tables or Views in a Data Source View (Analysis Services) and Lesson 1: Defining a Data Source View within an Analysis Services Project.

Note

You can create new data source views within a report model project or add existing data source views to the project. When adding existing data source views, verify they are based only on one data source.

Report Model Files

A report model file is a metadata description of the database that is being referenced by the data source view. When the report model file is generated, entities, roles, fields, and folders are automatically created. Typically, these entities, fields, and folders relate back to the columns and their data in the database. The model items are automatically generated and usually reference business names that Report Builder users are familiar with.

The contents of the entities and folders are automatically detected too. Field variations are created for you, if those options are selected when running the wizard. After running the Report Model Design wizard, the model can be published to the report catalog, assigned the appropriate role permissions, and then used in Report Builder and Report Designer. To make it even easier for users to create reports using this model, you can further refine the contents of the model.

Report Model Project in BI window.

Refining a Report Model

Once you have created your report model, you will probably want to refine it before publishing it. For example, you can reorganize the model items, rename items, and add additional entities, folders, and perspectives to the model. The items within the model can by further refined, too, by reorganizing their contents or by adding folders, source fields, expressions, and roles.

After you build and deploy your model, you might have to adjust the model's contents based on the feedback you receive from your users. You can open the report model file and adjust it as you need to.

Refreshing a Report Model

If the underlying schema has changed or if the database has changed, you can refresh the model or refresh an item within the model by running Autogenerate. When Autogenerate runs, it never overwrites the entire model; it simply detects the added items and incorporates them into the model. Autogenerate does not detect deleted or modified database items; therefore, you need to manually delete the model item(s). Otherwise, you will see an error when using the actual field in Report Builder or Report Designer.

Warning

Do not publish a new model with the same name because you will invalidate existing reports that are generated against this model. If you do create a new model with the same name and try to publish the model, you will see an error message. Always work on the same model to ensure that the IDs remain the same.

Primary Keys

If you have primary keys set in your physical database, this information is collected when you run Autogenerate. If you do not have primary keys set, you need to set a logical primary key using the Data Source View Designer. It is very important to set a logical primary key correctly; otherwise, incorrect data will be returned when you run reports in Report Builder or Report Designer. Your logical primary key must uniquely identify instances for the entity that will be created.

The primary key in data source views, used by report models, must be composed only of columns that do not contain null values. This might occur when primary keys are not defined in database tables, but are added to the data source view. If any column contains null values, the queries generated by the data source views might return unexpected results.

For more information about setting a logical primary key, see Defining Logical Primary Keys in a Data Source View (Analysis Services).

Change History

Updated Content

Clarified that only data source views based on a single data source can be used with report models.