Is it possible to update an SSRS Model using code to account for DB changes?

Posted on 2011-03-22
Medium Priority
Last Modified: 2012-05-11
We have a DB where users can create custom fields against a record. These are stored as rows in the database.

I want to be able to add these custom fields to the reporting model using code on a nightly basis and re-publish the SSRS model.

I know the model is essentially XML, and I have code which returns the record rows with the user defined fields as columns.

Any advice or pointers would be very useful.

This is using MSSQL 2008 R2, with SSRS.
Question by:Hobbit-71
LVL 15

Accepted Solution

Aaron Shilo earned 2000 total points
ID: 35745307
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
LVL 101

Expert Comment

ID: 36235383
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question