Star Schema vs All in one table

Hi,

I have a scenario where we have dimensional information and the facts in one table rather than a traditional break up into facts and dimensions. What could be the problem i could run into with having all information in one table. Thanks.

Poor performance, difficulty updating attributes, difficult to integrate with other facts...

I agree with the last two, but, was wondering how it would pull down performance. If i have everything in one table then i wouldn't have to make any joins and things should be faster. Prompts though would be slower, but, report would be faster. I might be wrong and missed something here. Thanks!!

What DB are you running on? It probably doesn't matter, because it will still run slower. I've done a few "fix-it" jobs where people have tried your approach and the performance was terrible for both ETL and querying.
Why? Well if your table is very wide - which is probably the case because you have all of your dimensional attributes in it - then it takes more disk i/o for each read because you will get less rows per page. So if for example, you want to sum your sales figures and group by location, then you will have to read many more pages using your method than you would using a proper star-schema.

It's not just the joins, it's the number of reads that slow down your query performance. Having everything in one table increases your reads. Don't do it.

The previous posts sum up the issues well, if you are using a relational database. On the other hand, if you are using a column-oriented database like Sybase IQ, or C-Store, many of these issues go away in One Big Table scenario. I would still use separate tables in my ETL area in order to track slowly changing attributes, do lookups, etc., but it may be OK to put it all in one table in the user database. I don't think even the Sybase folks recommend the One big Table approach, but I'm just making sure we consider all the options here. ;-)

See Ralph's design tip Columnar Databases: Game Changers for DW/BI Deployment? for more info.

--Warren

Interestingly, the next version of SQL Server, SQL Server 11, will have column store indexes. Using the same table without the column store index, an aggregation of over 1.4 Billions records returned in 501 seconds. Using a Column Store Index it returned in 1 second. Amazing stuff.

Basically, one big table approach is against the principle of both relational and dimensional modelling. Interestingly many OLTP systems have created this kind of quick and dirty monster transaction table in order to cater for reporting purposes. I think one of the up-front challenges in designing a proper BI system is to remodel the legacy system based on the best practice modelling techniques, and to us, it’s Kimball’s method.

Dimensional modelling is about denormalising dimensions and normalising facts, and it’s everything to do with performance. When the data is massive in DW, the performance has more to do with physical fact table size, as John pointed out, and less to do with joins with dimensions as dimensions are comparatively tiny anyway, unless they are heavily snowflaked.

Insightful. Thanks.

Dimensional modelling is about denormalising dimensions and normalising facts, and it’s everything to do with performance

What do you mean with "normalising facts" ?

By facts I referred to fact tables. So the fact tables should be normalized by replacing all the repeating groups(attributes) with dimension keys.

That's ok, thanks for your response.

Your comments are still valid in the case of exadata HCC technology?
When I have a look at the explain plan, it looks more costly but I can not see any problem in query execution time.