Continue in 2 seconds

I am working with a client that is currently enforcing a standard that does not allow direct query access to data mart dimensions and fact tables.

By
  • Sid Adelman, Chuck Kelley, Clay Rehm, Les Barbusinski, Scott Howard
Published
  • February 04 2004, 1:00am EST

Q:

I am working with a client that is currently enforcing a standard that does not allow direct query access to data mart dimensions and fact tables. They have created database views for this purpose. For instance, we have developed 10 reports in Brio that are run against a data mart (DB2 UDB tables). So, far this hasn’t caused any major issues (performance or otherwise). I would like to know if this is standard practice within other organizations. I would like to get other opinions and experiences on the matter because this is the first time I have seen such a standard. Through my experiences I have always learned to stay away from database views because they can sometimes cause performance issues and can also become difficult to manage if many views are created.

A:

Sid Adelman’s Answer: A number of organizations have established using views as their standard but this doesn’t mean it’s the ideal standard for your client. Before you do anything else, see if there is any documentation on why the use of views became their standard. They may have very strict security requirements and use views to control security.

Clay Rehm’s Answer: I am not sure there is a standard – what works at your company may not work somewhere else and vice versa. I have worked on projects that did exactly what you are going through and it works fine.

I am sure some will disagree with me, but views are especially nice for prototyping. Instead of having to define and build tables and build ETL processes, you can build views to provide alternative looks at data. Once the performance of the view becomes a problem, then tables and ETL processes can be built. If you don’t encounter performance problems, you have provided a solution to your users and in a very, very short time.

Les Barbusinski’s Answer: I’ve been in shops with the same silly standard (i.e., no direct access to database tables except through views). However, they are the exception, not the rule. The only reasons for using views rather than tables in a relational database are:

  • Hiding complexity (i.e., optimized queries can be codified in a view)
  • Limiting access to sensitive columns and/or restricting access to certain rows

As you said, views should be used sparingly because of the additional performance overhead they represent and the additional workload they create for the DBA.

Chuck Kelley’s Answer: I don’t think that this matters too much. I don’t recall seeing a problem with Views in DB2 UDB, although there may have been some early on in the product. The reason most people use views is to separate logical retrieval from physical retrieval. In this case, if the table changes, it won’t break all the queries since the view would probably still work fine. Another reason is so that they can hide some complexity of the data from the user community. Like I said, I don’t think I would worry too much.

Scott Howard’s Answer: My experience with current versions of DB2 UDB, versions 7 and 8, provides no concern for using views rather than real table access. Fortunately, you have chosen a database whose optimizer is not confused by the transparency provided by views, even views with very different structures than their base tables. Its advanced query rewrite capabilities shine here allowing the most complex queries, even combinations of views and base tables, to be optimized as efficiently as possible. Now there always can be the exception query that runs poorly, but this now is a rare case.

I do understand your concern. I also work with other current database managers where using views can be an issue. I often have to provide plan hints to overcome operational optimization deficiencies. It appears that you too have experience this, leading to your concern.

Maintenance is a real issue. I have recently relied more and more on the management GUIs provided by the vendors for the large systems I implement and support. This confession does not come easily as I love the control offered by scripts and the command-line maintenance approaches. However, DB2 UDB’s Control Center does a nice job simplifying view maintenance.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access