Descriptive Fields in Fact Table

View previous topic View next topic Go down

Descriptive Fields in Fact Table

Post  rf001 on Thu Mar 10, 2011 11:01 am

Hello,

If a business process records some descriptive information Data warehousing says to put such information in a dimension.
If there are no aggregations required and descriptive information is included in a fact table, what will be the negative effects?
avatar
rf001

Posts : 23
Join date : 2010-12-16

View user profile

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  ngalemmo on Thu Mar 10, 2011 11:08 am

Simple answer... performance will suffer.

I've seen time and time again where people would put textual attributes in fact tables to 'save a join' only to experience dismal query performance because the fact table is so large.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  rf001 on Thu Mar 10, 2011 11:46 am

What exactly happens? I mean how can we define performance here?
The response to queries is slower? or it is something else?
avatar
rf001

Posts : 23
Join date : 2010-12-16

View user profile

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  BoxesAndLines on Thu Mar 10, 2011 11:50 am

You got it. Who wants to wait around waiting on queries to return? The issue with most major relational databases is the whole row of data is returned regardless of how many columns are in the Select clause. The exception to this is Oracle Exadata and columnar databases.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  ngalemmo on Thu Mar 10, 2011 12:12 pm

And Netezza as well.

The perceived join 'problem' goes back to the old days with highly normalized databases and servers with small amounts of memory (when 1 MB was a HUGE amount).

Today, databases cache a lot of data. A database that understands star schema (most do these days) simply cache the dimension data before joining to the facts. There is little 'cost' in terms of time and queries run very fast because it does not need to pull a lot of data if the fact table is thin.

The most expensive and time consuming thing any database does is accessing disk. A wide fact table causes the database to access far more disk than it otherwise would need to.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  AlanB on Thu Mar 10, 2011 1:06 pm

ngalemmo wrote:Simple answer... performance will suffer.

I've seen time and time again where people would put textual attributes in fact tables to 'save a join' only to experience dismal query performance because the fact table is so large.

Won't a columnar database eliminate the query performance issue?

AlanB

Posts : 4
Join date : 2011-02-16
Location : Vancouver, BC

View user profile

Back to top Go down

Re: Descriptive Fields in Fact Table

Post  ngalemmo on Thu Mar 10, 2011 1:54 pm

AlanB wrote:
Won't a columnar database eliminate the query performance issue?

Not if you don't have one.

A columnar arrangement would not have that particular performance issue. But, assuming the database is row oriented, one could hardly justify changing platforms simply because they do not want to create a dimension table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Descriptive Fields in Fact Table

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