denormalised versus dimensional
4 posters
Page 1 of 1
denormalised versus dimensional
A couple of colleagues of mine and myself are involved in the creation of a new data warehouse/reporting solution.
Our company has never done this so we are learning as we go.
I am a fan of the Kimball method and am following the books(more or less). I really like the dimensional model but one of my colleagues wants to keep all data denormalised, his argument is that tempdb(We are a SQL Server outfit) will need to be massive to be able to handle the joins,even though it will probably be star schema and we will only have 10-15 dimensions, we will need to do on the dimensions and facts.
I do point out that a fully denormalised table will be absolutely massive and more difficult to modify and maintain over time.
I was wondering if anyone here had any useful information regarding pro/con articles/website the compares and contrasts denormalised/dimensional models
I have searched on the net but have only found dimensional model articles/websites
Our company has never done this so we are learning as we go.
I am a fan of the Kimball method and am following the books(more or less). I really like the dimensional model but one of my colleagues wants to keep all data denormalised, his argument is that tempdb(We are a SQL Server outfit) will need to be massive to be able to handle the joins,even though it will probably be star schema and we will only have 10-15 dimensions, we will need to do on the dimensions and facts.
I do point out that a fully denormalised table will be absolutely massive and more difficult to modify and maintain over time.
I was wondering if anyone here had any useful information regarding pro/con articles/website the compares and contrasts denormalised/dimensional models
I have searched on the net but have only found dimensional model articles/websites
podgeypoos- Posts : 6
Join date : 2012-11-07
Re: denormalised versus dimensional
Stars are a type of denormalization. The attributes are denormalized where as the facts are normalised.
A big attraction of Star Schemas is their performance. I can't see anything else competing with it. Plus, if you denormalize the fact tables, then you are putting a lot of strain on I/O. Stars reduce the I/O strain and put it on the processors, which is where it should be. And because the joins are simple, they are realtively easy to tune with indexes to improve preformance.
Stars have much of the benefit of a denormalized fact table without the bulk. Increased bulk means slower performance.
A big attraction of Star Schemas is their performance. I can't see anything else competing with it. Plus, if you denormalize the fact tables, then you are putting a lot of strain on I/O. Stars reduce the I/O strain and put it on the processors, which is where it should be. And because the joins are simple, they are realtively easy to tune with indexes to improve preformance.
Stars have much of the benefit of a denormalized fact table without the bulk. Increased bulk means slower performance.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: denormalised versus dimensional
Big flat tables are essentially unusable. They are slow to query and consume huge amounts of space. You would have many of these as the levels of detail vary. If you try to mix detail in a big flat table you wind up with an even bigger mess.
Thanks for the help
one of my colleagues would like that some(not all) of the dimensional attributes should be placed in the fact table.
e.g. they believe that we should put the name of our product in the fact table so that we do not have to join on a product dimension.
again I am no expert but I would not be a fan of doing this as this is then not a fact table just a really messy table that is neither fully denormalised or dimensional.
Is there any support to this type of data model?
e.g. they believe that we should put the name of our product in the fact table so that we do not have to join on a product dimension.
again I am no expert but I would not be a fan of doing this as this is then not a fact table just a really messy table that is neither fully denormalised or dimensional.
Is there any support to this type of data model?
podgeypoos- Posts : 6
Join date : 2012-11-07
Re: denormalised versus dimensional
Denormalizing dimensional data to the fact table is just plain wrong. There is no reason to do it. It will reduce the performance of the database. It will increase the amount of disk used by the data. It's harder to maintain.
Uses more disk. Slower performance. Harder to maintain. Sounds like a brilliant plan to me.
Uses more disk. Slower performance. Harder to maintain. Sounds like a brilliant plan to me.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: denormalised versus dimensional
Hi,
you can tell your colleagues just to avoid a join we should not store the name of the product in the fact table, if join is going to slow the performance then we should have been storing our data in a giant file with hundred's of columns.
thanks
you can tell your colleagues just to avoid a join we should not store the name of the product in the fact table, if join is going to slow the performance then we should have been storing our data in a giant file with hundred's of columns.
thanks
hkandpal- Posts : 113
Join date : 2010-08-16
Similar topics
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Bridge tables versus massive junk dimensions
» tenants and children versus facts
» snapshot facts: daily versus effective from & to
» Factless fact table versus Dimension (downstream implications)
» Bridge tables versus massive junk dimensions
» tenants and children versus facts
» snapshot facts: daily versus effective from & to
» Factless fact table versus Dimension (downstream implications)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|