[rmk]

I would like to address this question to data warehouse designers, is there a reason why some people call certain systems Data Warehouse and I dont find any Star/Snowflake schema as the data model, but thee could 2nd normal form models with summary tables to suite the reporting needs. I have been in the implementing data warehouse system for past 5 years, but I find this a widespread design in most organizations.

Any help or links to whitepapers and documents would be helpful.

I

[n_i]

Originally posted by rmk
why some people call certain systems Data Warehouse and I dont find any Star/Snowflake schema as the data model, but thee could 2nd normal form models with summary tables to suite the reporting needs.

Commonly recognized definition of a data warehous (by Bill Inmon) says that it is "... a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process."

As you can see, there's no reference to any specific schema or data model. I think whatever serves the purpose of a data warehouse, is a data warehouse.

Nick

[rmk]

Originally posted by n_i
Commonly recognized definition of a data warehous (by Bill Inmon) says that it is "... a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process."

As you can see, there's no reference to any specific schema or data model. I think whatever serves the purpose of a data warehouse, is a data warehouse.

Nick

Thanks Nick,

Take away from your message is that "whatever serves the purpose". But are there any downsides or limitations in using these kind of summary tables. Two things that come to my mind is extensibility of the system and size limitation due to data explosion.

Is there any thumb rule like when to go for Star Model (denormalized) or 2nd normal form with summary tables specific to reporting needs.

[n_i]

Originally posted by rmk

Is there any thumb rule like when to go for Star Model (denormalized) or 2nd normal form with summary tables specific to reporting needs.

I don't have much experience with DW so worthiness of my opinions may be questionable. However, I think that you would normally begin with a star schema as it gives you most flexibility and convenience. As the DW size grows its performance may suffer; this is where summary tables may kick in to improve response times for specific queries or even datamarts.

On the other hand, designing a good schema requires a lot of time and thinking; sometimes you may be tempted to take a shortcut by using summary tables that map directly to the user requirements.

Hope this makes sense.

Nick

[rmk]

Thanks for your time and recommendations.

[blindman]

I think the star schema is just the simplest form of data warehouse, and as such is probably most suitable for datamarts where the application is more limited and speed is more important than functionality. My successes with data warehouse development have been from designing a table structure that models reality, starting with just a few tables and then adding and pruning over time. Should the database get too large to be functional, you should by then have a good understanding of how it is being used and thus be able to make wise choices about data pre-aggregation, archiving, and spinning off smaller datamarts for specific analysis.
...and in all the companies I've worked with I've yet to see a successful data warehouse developed any other way. Using the standard application development model for a data warehouse will kill the data warehouse.

blindman