Dimension Tables as lookup tables

View previous topic View next topic Go down

Dimension Tables as lookup tables

Post  gcoello on Tue Mar 24, 2009 11:41 am

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.

gcoello

Posts : 3
Join date : 2009-03-24

View user profile

Back to top Go down

Re: Dimension Tables as lookup tables

Post  Michael_K on Tue Mar 24, 2009 12:28 pm

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.

Michael_K

Posts : 7
Join date : 2009-03-14

View user profile

Back to top Go down

Re: Dimension Tables as lookup tables

Post  BoxesAndLines on Tue Mar 24, 2009 10:04 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension Tables as lookup tables

Post  gcoello on Wed Mar 25, 2009 6:09 pm

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.

gcoello

Posts : 3
Join date : 2009-03-24

View user profile

Back to top Go down

Re: Dimension Tables as lookup tables

Post  mmoayed on Fri Mar 27, 2009 10:52 am

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 .

mmoayed

Posts : 12
Join date : 2009-02-04
Age : 41
Location : Yemen

View user profile

Back to top Go down

Re: Dimension Tables as lookup tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum