denormalised versus dimensional

View previous topic View next topic Go down

denormalised versus dimensional

Post  podgeypoos on Wed Nov 07, 2012 1:37 pm

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

podgeypoos

Posts : 6
Join date : 2012-11-07

View user profile

Back to top Go down

Re: denormalised versus dimensional

Post  Jeff Smith on Wed Nov 07, 2012 6:01 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: denormalised versus dimensional

Post  ngalemmo on Wed Nov 07, 2012 6:26 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Thanks for the help

Post  podgeypoos on Thu Nov 08, 2012 7:37 am

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?

podgeypoos

Posts : 6
Join date : 2012-11-07

View user profile

Back to top Go down

Re: denormalised versus dimensional

Post  Jeff Smith on Thu Nov 08, 2012 10:49 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: denormalised versus dimensional

Post  hkandpal on Thu Nov 08, 2012 4:22 pm

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



hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: denormalised versus dimensional

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