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

Posted on 2011-03-22
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 500 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

689 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