Descriptive Fields in Fact Table

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?

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.

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

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.

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.

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