Advanced end users can be a blessing or a curse for every IT system. They always explore the possibilities and the data. They are never satisfied with the reports provided by the application(s) they use. Many DBAs give them excessive permissions on database objects in order to enable them to use client tools like MS Excel with MS Query to create ad-hoc reports. However, with MS Query they are able to perform any T-SQL command for which they have appropriate permission set. In worst case, they can make the data inconsistent (ok, in worst case they could possible even drop a database).
In addition, just giving them permissions is typically not enough. Usually they can write simple queries only, and a normalized structure of a relational database is too complicated for them. Therefore the DBA’s work is not finished yet – probably (s)he has to create some views as well.
Report models solve the problems mentioned. A report model is a semantic layer of metadata stored in the SSRS database on a SQL Server, describing the data from a business point of view, adding many additional properties that enable the quick and easy authoring of reports. Of course, a report model is not something an end-user would prepare. Nevertheless, this is a one-off job for a DBA.
After you create a report model, your advanced end users can use the Report Builder tool to build ad-hoc reports. They do not have to know anything neither about Transact-SQL language nor about the schema of the database. Compared to ad-hoc reports with MS Excel, there is another advantage in using report models. If a report designed from a model by end user is acceptable and useful for other users as well, the author of the report can deploy it on Report Server and make it available to anybody that would need it. Deployment from the Report Builder is simply a matter of clicking on the Save button, selecting the deployment folder and naming the report.
With Report Models and Report Builder, you can definitely turn your advanced users to a blessing.
