1 table or many tables

[cbushey1]

Hi all,

I wanted to get opinions and best practices around combining tables. Specifically, I have 8 tables currently in my data model that all share the same key field. Each of these tables represents facts about the key field so I was thinking about combining them all into a single table. Doing this would mean instead of say 20M rows in each of the 8 tables, I would have 20M rows in a single table, but that table would have around 150 fields. The key field is only listed once per table in each of the 8 tables so I am not worried about inflating rows. 

Is there a point at which it makes more sense to keep these as separate tables?

Let me know what you think. 

[cbushey1]

No there is definitely more than 8 tables, however I am looking to consolidate these 8 into 1.
Unfortunately I can't share a picture.

[dplr-rn]

Ok. if there are multiple hops (dimensions for each of these tables) involved i would definitely recommend consolidating these 8 into 1 fact table.

[cbushey1]

I am concerned that doing a join on these tables will certainly cause bottlenecks in the reload. In the past, when I have done joins on large tables (10M rows) it would take around an hour. Certainly this was before trying to order the tables but I am still concerned that it might take more time than I want to sacrifice just to get all 8 tables into 1. If a table has 5 fields then I dont mind doing the Map/ApplyMap route.

Any suggestions for combing the large tables?

[dplr-rn]

You are right about the bottle neck piece. The question is to find the best fit for performance for end user and reload. Unfortunately will be a bit of trial and error for you. do yo have end user performance issue currently. if not i would probably leave it alone for now.

i have had apps with 100s of millions of rows and to optimze end user performance we created a singular fact table. reload take 3-4 hrs.
on the other hand i have seen at clients apps will 100s of millions of rows a lot of synthetic keys , but still without performance issue because the server is strong

[dplr-rn]

Missed to add if the app is still in development; i Would run benchmarks using scalability tool to asses response times