I've been spending a good portion of the year so far working with SQL Server 2005. I have to comment that when Microsoft hits the mark with a product release, they really nail it. With SQL 2005 and all of its component services, they've created a hugely powerful suite of servers. It's been well worth my time spent learning this new tool set.
The learning curve is steep, however, and there have been numerous times where I have been stumped with problems integrating the various tools, setting up servers, and using "SQL Server Business Development Studio" um.. Sorry Visual Studio.
One of my goals has been to create a Report Model that will simplify the ability for end users to create reports. If you're not familiar with how Report Models make Reporting Services easier to use, watch this webcast: End-User Ad Hoc Reporting with SSRS (Level 300)
Here was my problem. After creating a report model from my cube (using the auto-generate feature of SSMS or Reporting Services) the model really wasn't usable. Click-through entities weren't setup properly, naming was not right, and there were a lot of fields I didn't want the end-user report writers to see.. It was a start, but not deployment ready.
I was unable to use Visual Studio's support for report model projects to let me create a model from an Analysis Server Cube... This functionality seems to be absent from this release, or at least wouldn't work with my installation. I did manage, in a round about way to edit my generated report models in Visual Studio: Here's what I did:
Step 1: Add A Report Model Project to your Solution:
The key to getting this to work was to add a Report Model Project to my solution which already contained my Analysis Server Project. Copy the Data Source and Data Source View from your SSAS project to your Report Model Project. This is important! Without the same named Data Source View and Data Source, your won't be able to import this report model.
![]()
![]()
Step 2: Export and Add your Generated Report Model (.smdl) to your Project
You can use SSMS or the Reporting Services web interface to do this. I found it easier to do this in SSMS by Right-Clicking the Model and selecting Edit.
Import this model into your new Report Model Project, and you should be able to edit all of the entities:
Step 3: Import the Model back into SSRS, using SSMS.
DO NOT publish your model back to the server using Visual Studio. This will add some schema information to your model, and it will no longer function against your cube in Reporting services. The key here is to use SSMS to "Import File" and import your edited .smdl model back into SSRS.
Step 4. Re-Configure the Model's Data Source
The final step is to re-set the data source back to use the Analysis Server Cube. You'll notice that the newly imported data model has an invalid data source. Select the original cube data source:
Once you've done this, your new model should work against your Cube, and will provide your end-user report writers a customized Model of your data. Over time, as you customize your Cube, you will have to re-generate a model from the cube, and migrate the changes into your production report model file by hand. You'll find that this can be done using a diff tool quite easily, since the .smdl file is XML.