Dimension Tables as lookup tables

Recently one of the developers at my organization approached me and asked that I take a table that exists in one of our source systems and put it in the DW, so that he could populate a drop down and validate data for the web app he was developing.

ie, he wants to pick and validate a Clinic ID.

Since the table in question doesn't exist in the data warehouse and the data he is requesting will not likely be needed in he warehouse for some time, I advised him that the DW was an inappropriate place to contain this data.

However, it got me thinking whether using a dimension table as a lookup table to another application is appropriate? I'd be interested in hearing some other thoughts, and if this is not a good idea, I'd like to nip this in the bud, because I believe that there is development and design taking place without the knowledge of the DWAs, and we will soon be facing a lot of requests to put unrelated tables into the DW, and also use the DW as the source for a lot of non-DW applications.

Looking forward to your responses.

I can't say that I would support this logic. If he's building a transactional web application it should feed from a transactional system (putting the lookup in that system). The DW is for decision making and reporting purposes, not as an actual web application. The cardinal rule comes into play of "just because you can do it, doesn't mean you should". You run the risk of running into issues with your DW if you start bending it for use by transactional systems because you can't necessarily flex it to your DW needs if some other system is using the info.

What's wrong with the production OLTP database? These database environments are designed to support different functions. For instance, when I create a table in Oracle in my data warehouse, I always specify NOLOGGING. If I specified NOLOGGING in an OLTP table, I should be escorted out the door. You are absolutely right not to want developers circumventing the normal processes. I've never even heard of this happening before. And I'm, errr, well seasoned.

The issue is that it's a medical HCIS, with a proprietary, non-relational dbms. Nobody gets access to the data, except for the application itself. Instead, the data is trickle fed into a SQL Server database acting as a repository. The repository, in turn, acts as the source system for the DW.

The process which controls the trickle feed also places a load on the souce system, and so the concern from the other developers is that any unecessary access could compromise the performance of the repository, and thus the HCIS.

Once the DBAs and the DWAs converged on the developers, explained to them what a DW was for, and assured them that well designed, OLTP queries would not adversely affect the repository, they agreed that the repository would be the best place for their lookups.

Thanks for the input.

Hi ,
Why don't you design this table as Dimension for this use and later on you will need to re-design this table if any one will need it .