Solved

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

Posted on 2011-03-22
5
319 Views
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.
0
Comment
Question by:Hobbit-71
5 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
Comment Utility
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
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now