Star Schema vs All in one table

View previous topic View next topic Go down

Star Schema vs All in one table

Post  daredevil on Thu Nov 11, 2010 6:52 am

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.

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  ngalemmo on Thu Nov 11, 2010 12:13 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Star Schema vs All in one table

Post  daredevil on Sun Nov 14, 2010 11:52 am

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

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

It's not just the joins

Post  John Simon on Sun Nov 14, 2010 6:03 pm

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.

John Simon

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

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

Back to top Go down

Database assumption

Post  warrent on Sun Nov 14, 2010 10:39 pm

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
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  John Simon on Sun Nov 14, 2010 11:33 pm

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.

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: Star Schema vs All in one table

Post  hang on Mon Nov 15, 2010 1:48 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  daredevil on Mon Nov 15, 2010 6:46 am

Insightful. Thanks.

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  umutiscan on Wed Apr 25, 2012 4:50 am

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

What do you mean with "normalising facts" ?

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  hang on Wed Apr 25, 2012 8:14 am

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  umutiscan on Wed Apr 25, 2012 10:20 am

That's ok, thanks for your response.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Star Schema vs All in one table

Post  umutiscan on Thu Jun 11, 2015 9:49 am

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.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Star Schema vs All in one 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