Fact table, strings and row size

View previous topic View next topic Go down

Fact table, strings and row size

Post  adrianw on Wed Jan 26, 2011 10:08 am

I'm very new to dimensional modelling, but I'm looking at a design made by someone very experienced.

That design has in each fact table a set of metadata attributes, e.g. "data provider", along with denormalised versions of things are really dimensions but which no one is ever going to query and make the design too complex to have as dimensions, e.g. currency code, currency description.

So an example row would be:

"RETAIL", "Import from retail transactions", "123.45", "USD", "United State Dollars", 11/22/2010

The upshot of this is that each fact table row is over 1kB, but the actual numeric facts only amount to about 100B. To me that seems madness, meaning I have to spend a huge amount of money on disc bandwidth to feed the processors during table scans (this is SQL Server, so no column oriented compression).

Is this normal/usual/correct?

adrianw

Posts : 3
Join date : 2011-01-26

View user profile

Back to top Go down

Re: Fact table, strings and row size

Post  John Simon on Wed Jan 26, 2011 7:28 pm

It's terrible. It wouldn't matter if it was a columnar database, but in a row-based database like SQL Server it's extremely bad practice. Essentially if you run a query for a sum of amount, you have to go through more pages than you need to, adversely affecting performance.

Rip them out.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Fact table, strings and row size

Post  ngalemmo on Wed Jan 26, 2011 8:28 pm

Yep... ditch the text, use dimensions.

I have seen this before, and the common complaint is that queries run for a very long time. Most of the time this type of thing is done by ex 3NF modelers who believe the issue is reducing the number of joins. Actually, the real issue is how you join. A properly designed star schema on a database system that supports star joins will outperform big flat tables any day.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table, strings and row size

Post  adrianw on Thu Jan 27, 2011 2:17 pm

The thing that bothers me is that the person who designed has an impeccable industry reputation and decades of experience.

Maybe I misled with my example, there are also dimensions (and copies of the dimension values) involved so it is more like:

"RETAIL", "Import from retail transactions", customerid, "JOHN SMITH", customeraddressid, productid, 123.45, "USD", "United State Dollars", 11/22/2010


Last edited by adrianw on Thu Jan 27, 2011 3:26 pm; edited 1 time in total

adrianw

Posts : 3
Join date : 2011-01-26

View user profile

Back to top Go down

Re: Fact table, strings and row size

Post  BoxesAndLines on Thu Jan 27, 2011 3:16 pm

Yep, I've seen it before as well. Create a dimensional model, but denormalize all the dimension attributes to the fact table to "improve performance". It will actually degrade performance over time. Hopefully none of the denormalized columns are from type 2 dimensions or you'll really be in a world of hurt.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact table, strings and row size

Post  adrianw on Thu Jan 27, 2011 3:28 pm

In this instance the denomalising is not to improve performance but to improve readability of the fact table.

adrianw

Posts : 3
Join date : 2011-01-26

View user profile

Back to top Go down

Re: Fact table, strings and row size

Post  John Simon on Thu Jan 27, 2011 4:56 pm

If you want to improve readability then use a view. These additional attributes will adversely impact performance.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Fact table, strings and row size

Post  BoxesAndLines on Thu Jan 27, 2011 6:22 pm

adrianw wrote:In this instance the denomalising is not to improve performance but to improve readability of the fact table.

This doesn't make sense to me. Why wouldn't you just do a join to pull the columns in that you want to see?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact table, strings and row size

Post  ngalemmo on Thu Jan 27, 2011 7:21 pm

Bottom line, its a bad design. My comment is not intended to vilify the person that did it. Sometimes, particularly if you are a consultant, have to compromise designs to suit the client. Politics often drive decisions, not technical purity.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table, strings and row size

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