Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

604 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