When do I need to update the Report Model

There is a constant change (!) in our database, new columns are often added.

Is reporting services the tool to choose for reporting in this case?

Case1: Developers add a new column to a table used in a report. Will the old reports created with a report model based on the old table still work?

Case2: Developers add a new column, and end users want to be able to report on it. If we update the report model, will the old reports based on the old report model still work? Or do we have to create a new report model every time the end user wants to report on a newly created column?

Regards

Lars

Reporting services has required strategies for change management. So, adding new column to a table in the underlying data source does not affect the reports.

If you want to include a newly added table column into your report model you should update (not create from scratch) your report model. Updating the report model automatically insert your new column to the model and does not break your old reports. on the other hand, updating report model does not update/delete the existing item if you change them (like table/column name or column data type etc) in the underlying source. You should manually change them at the report model and at the affected reports.

So, in your case, you won't be having any problem with reporting services.

Here i'm adding a change management section of the Reporting Services/Report Model document and strongly suggest you to read it.

Change Management

Models and the reports based on them have many internal and external dependencies. Therefore, you need to consider the impact of changes introduced into the dependency chain. Report models based on relational data sources use GUID attributes to identify each entity, attribute, and role. As mentioned, the report model-generation process sets the GUIDs, which are re-created at each generation. For that reason and to preserve edits on the report model, generating a new report model each time a change occurs is not an option. You must work with the existing model and update it, either manually or by using the update options described below.

The Semantic Query Engine manages missing attributes when they are not critical to report processing. This functionality is in place to keep reports running when security attributes preclude users from seeing some attributes in the report that may be allowed to other users. Thus, if a user is not allowed to access a field such as the employee home telephone number, the Employee Listing report will run for that user but will not show the excluded information. This functionality works to your advantage when models are edited to delete a non-critical attribute. The report will still run after you have removed an attribute, although the report might show a blank field. However, query or report processing can be broken by other changes to the model.

Remember that you should not overwrite a model generated from a relational data source when any reports depend on it.

Schema Changes

If the underlying schema changes and report model entities or attributes are affected, you might have to update the report model accordingly. To do so in BIDS, use the Autogenerate command on the Reporting Model menu. You can also select Autogenerate from the model item's context menu. By using the context menu, you can select which item on the model you want to update without having to update the entire model.

The autogeneration process will show informational, warning, and alert messages. These messages will show all items in the model that are out-of-sync with the underlying DSV, even though those items are not specifically included in the item selected for autogeneration. This functionality helps detect potential errors than may lead to unpredictable errors when running reports based on the model.

Automatic update affects newly added items only. The autogeneration process will add any new entity, attribute, or role found in the DSV, but will not delete or change any entity, attribute, or role. Therefore, you need to manually manage updated or deleted items. The messages shown at the end of the generation process will highlight any entity, attribute, or role that needs to be updated in the resulting out-of-sync model. You will have to update the model manually or revert the DSV changes to maintain model-to-schema coherence.

Data Source Changes

You can develop and test your model in a development environment and then deploy the model in a production environment easily by changing the connection string in the data source file that the DSV uses. The two data source schemas must be identical.

Note that the DSV contains statistics based on the actual database data. As mentioned in the section "Statistics in Report Model Generation," the value of those statistics will drive some algorithm decisions during the model generation. Therefore, if the development database data is significantly different from the production database data, the model might not be optimized for the data that will eventually be used.

Hope this help.