Best Practice: no more queries in frontend tools

You may have experienced it: your report was working just fine, untill you published a database change and oops! a report doesn't work anymore. Especially when you have about 70+ reports running their own queries, this can be a pain.

Why would a report contain a query? Well, because it is easy, quick to develop, and why not?

The reason is, you don't get to manage all those queries at all. They are the hidden queries, long forgotten, never tuned and unfindable in your database.

Please use views or a stored procedure to feed your datasets in reporting services. If the query lives in the database and you use a daily build to check if the code still works, then you will be notified if the query is broken, before you publish it to production. Same goes for all front end tools, including Power BI. At least use a view. The other tools going to the database should not know the datamodel at all.

The idea is that a view can remain consistent while the underlying datamodel can change as needed.

BI consultant and trainer at Motion10

Leave a Reply

Next ArticleTrim the fat (put your data warehouse on a diet)