0

What is the cons of using the star schema in database design for big data?

Is the large size of fact table is a problem? Or we can consider that the disk space is cheap and and large size of the fact table is not a problem at all?

Michael harris
  • 966
  • 2
  • 14
  • 25

2 Answers2

0

The problem will be the time consumed to join all the needed information together. If you have complex evaluations on the data, this can take considerable amount of time.

http://en.wikipedia.org/wiki/Star_schema

fancyPants
  • 50,732
  • 33
  • 89
  • 96
AlphaOne
  • 91
  • 6
  • Are you sure you know what a star schema is? – fancyPants Jun 01 '13 at 11:16
  • One large facts table with many many foreign keys to additional tables there further information is stored. Additional information might be country namas, product classes etc. The fact table only contains their respective IDs. – AlphaOne Jun 01 '13 at 11:21
  • @tombom http://en.wikipedia.org/wiki/Star_schema <-- that is the thing, you see the need to join? – AlphaOne Jun 01 '13 at 11:34
  • So? A star schema is denormalized. You seem to say, that it's slower than joining multiple tables in a normalized schema or a snowflake schema. That is not the case, actually there's no other schema design, where joining is faster. And you don't have to point me to wikipedia, I'm working in the business intelligence business for 4 years now. – fancyPants Jun 01 '13 at 11:35
  • "the need to join"...if you don't want to join anything, don't use a database at all. – fancyPants Jun 01 '13 at 11:36
  • @tombom Let me be more precise: yes, star schemes are more denormalized than a "fully normalized" scheme would be. I was a bit lax on that. I think the wiki article is the bast answer to the question. Additionally I still point out, that joining costs computing time, so that remains a disadvantage (not listed in the wiki articles disadvantage section). You *could* do full denormalization (with all disadvantages this brings), that is very depending on the field of application. I had no intention of offending you with that article. – AlphaOne Jun 01 '13 at 11:46
  • The question will be closed anyway, as it's not constructive, so there's actually no need to argue. Actually the arguing is one more reason to close the question. (Had to edit your answer so I can remove my downvote) – fancyPants Jun 01 '13 at 11:59
  • @AlphaOne Given that a foreign key from your fact table is going to be a (probably integer) surrogate, and assuming that you use a reasonable indexing scheme, joins in star schema models are about as fast as it gets. Dimensions are effectively normalized, drastically reducing the size of the fact table. – Corey Sep 19 '13 at 23:02
0

It is better to compare and the design will be based on your priorities like performance, memory, cpu etc. See the comparison that compares both Star and Snowflakes:

Read: Snowflake Schema vs. Star Schema

rchacko
  • 1,965
  • 23
  • 24