Flat Table vs star schema

View previous topic View next topic Go down

Flat Table vs star schema

Post  bi_at_nj on Sat Oct 31, 2009 12:38 am

We hear a lot of BI implementations using star schema. (with few dims snowflaked sometimes )

Why not choose a flat table with all data put together instead of a star schema?
(well , this question may seem strange, but please help me out here)

Few answers which I have answered to myself are:
* Data retrieval with star joins perform better in star schema for the sake of reporting.
* It all depends on the volume.

What other possible answers are out there for FlatTable Vs StarSchema?

- Thanks in advance
bi_at_nj

bi_at_nj

Posts : 14
Join date : 2009-10-31

View user profile

Back to top Go down

Re: Flat Table vs star schema

Post  ngalemmo on Mon Nov 02, 2009 12:04 pm

Why big flat tables don't work:

1. They can be huge.
2. It is very difficult to integrate with other big flat tables.
3. Life (and business) isn't that simple.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Flat Table vs star schema

Post  Prasanna on Tue Nov 03, 2009 7:01 am

1.Flat table can be a real nighmare to query against huge volumes of data.
2.Flat table is of no life, I mean to say you cannot quickly recognise what columns fall under single group(dimension). On the other side by looking at the Star Schema itself one can find the facts (Whats??) linked to dimensions (How???). It gives a business Outlook to frame every entity into perfect slot answering "What Do we want?", "How can we get??"
3.Star Schema Emphasizes deep dive to check for different combinations by facilitating quick analytics interms of query performance.
4. In simpler words, Star Schema is as comfortable as having a Kitchen,DiningRoom,Bedroom,DrawingRoom,Bathroom etc in house rather having a single room for everything.FETCH becomes easier in Star Schema.

Prasanna

Posts : 6
Join date : 2009-10-20

View user profile

Back to top Go down

Re: Flat Table vs star schema

Post  cridal on Tue Nov 03, 2009 4:45 pm

Having one big table combining facts and dimensions can be a solution if:

1) You have few dimensions and they are narrow (few attributes)
2) Your fact table is not too long (maybe single digit millions of rows max)
3) There is not need to run SCD Type 1 updates to your dimensions (because you would need to run historical updates for every SCD Type 1 attribute for every dim)

So in case you have a relatively small fact table and few, skinny dimensions that don't require updates, then maybe it's a go. Otherwise, you have one giant elephant of a table with many indexes, which accumulates even more information once in production. Every operation you want to do on it gets slower and slower. A star schema dimensional model alleviates a lot of the pain...

cridal

Posts : 9
Join date : 2009-03-27

View user profile

Back to top Go down

Re: Flat Table vs star schema

Post  Vincent Rainardi on Fri Nov 20, 2009 2:42 am

Two additional points:
1. Using 1 table approach it is a night mare to create the OLAP cube. With star schema it is a lot easier.
2. The ETL is not easier with 1 table. To transfer a normalized (3/BCNF) transaction system schema into a flat structure we need to map the columns and do lots of joins.
Thanks for throwing the idea, bi_at_nj.

Vincent Rainardi

Posts : 6
Join date : 2009-02-03
Location : London

View user profile http://www.datawarehouse.org.uk

Back to top Go down

Re: Flat Table vs star schema

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