As I sit here in Boston, I can’t help but find myself pondering views. At its core, what is a view, and specifically in regards to business intelligence, why is it such good practice to create views on our data warehouse tables? So as any sane person would, I immediately pulled out my computer and starting looking up best practices and asking the all important question: “Why?”
Immediately, I stumbled across a Microsoft whitepaper: http://msdn.microsoft.com/en-us/library/aa902663%28v=SQL.80%29.aspx
And in reading it, 1 particular section stuck out to me more than anything else:
“Using views is good system design for a number of reasons:
- Using views improves flexibility by providing a buffer between the physical database and downstream systems, like user access and cube population.
- It increases the likelihood that you can make significant physical changes to the system while minimizing disruption to other applications. “
In particular, one thing I’ve really begun to preach is the first bullet point in particular – “Providing a buffer between the physical database and downstream systems.”
It used to be that you would build your data warehouse, build your SSAS cube on top of it, and build your SSRS reports off of either a) your SSAS cubes, b) your data warehouse, or c) both. However, with the recent advent of Power Pivot, Power View, and the BISM tabular model, times are changing. Before if you ignored this practice, it wasn’t that bad, because more likely than not, it was just a field in a DSV in Analysis Services you had to change. But now, with all of the additions to the family, that is A LOT more stuff you have to update if you ignore it. Nowadays, ignoring and physically tying to the table creates dependencies on an almost infinite amount of systems, which could have you transforming from a BI developer into a DBA to watch all of those tables and make sure they perform. Views are a tool in the BI developers arsenal to give systems, and business users, insight into what’s going on while still keeping the structure in relative obscurity.
The other item I really like about is the “significant physical changes” piece. I’ve seen time and time again where business try to create something themselves, it doesn’t work, so they call someone, but their are already reports and cubes in production that reference the data warehouse. So it’s not an option to just “blow it away” and start over again. Views give us that functionality to support existing processes while we rebuild the inside.