DW design

[paulagco]

We are designing a new DW and hence team is looking around for best practise, DW design standards that would help us in the end and long run. Currently we have a debate going around between our design team and DBA about what varchar column length should be.
Opinion 1: Source column; varchar(55), In DW all varchar columns should be set to default 250 as suggested by few renowned authors. Kimball.
However our DBA's opinion is the opposite for the same above scenario. He reckons it should be set to varchar 55. i.e as whats in source.
Please suggest whats the best long term approach that we should adopt.

Regards,
Paul Jacob

 

[johnherman]

One difficulty in making all VARCHAR fields a very large number (say 250) is that some 3rd party query tools (Cognos, for instance) will read the DDL and decide that the column needs 250 bytes whenever displayed in a report, cube, or query. This can be over-ridded, but requires additional effort when building the catalog or framework (Cognos terms for their database structure guide).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[paulagco]

Thank Johnherman. Pls don’t take me wrong.

3rd party tools like Cognos impromptu may potentially give problems as mentioned by you but to be honest overhead to overcome this is changing mere display format. I have been working on Cognos for last 5 years and barring my current project everywhere else we have taken the approach of using Varchar(250) as suggested by renowned authors like Kimball & Imnon. We did not face any major problem by following standard DW recommended practise. On the contrary we had some benefits when there were source column width changes. None of our columns needed restructuring in anyway. All reporting tool continued to run as normal. There were other benefits too.

Are you still for it or against this kind of design methodology.

Anyone else who are for or against it. We are keen to get a general consensus on this from all you qualified and experience techies.

Regards,
Keith Jones

 

[johnherman]

In principle, I like the concept of a storage and display length. And since DW's often combine data from multiple systems, I have no problem with DW column sizes being different from one or all source systems.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Hans63]

Actually I have implemented my last datawarehouse in way where all varchar fields where defined with a few possible lengths (100, 250, 4000). This was done in anticipation of changes on the source system, where a field could be widened (or shrunk) without having to revisit the ETL software or the DWH datamodel.
We applied the same methodology in regard to the numbers. All numeric values where mapped to a generic NUMBER datatype. So all changes in scale or precision were not of any concern.

Over all we standardized to just a few datatype/length combinations.