MySQL Forums
Forum List  »  Data Warehouse

Dimensional model, user ids and number of tables
Posted by: Mads Jensen
Date: June 24, 2009 01:07PM

Hello

I'm currently working on the dimensional model design for a redesign of a service. I've been searching far and wide for an answer to the following question, without luck.

So my problem isn't the dimensional model itself, the problem exists due to the nature of the project. It will be a 'software as a service' type project where users can sign up and use the service for free or through payment.

This means that most data (all fact tables and most dimensions) will have a user id associated with each row.

So my concerns are about performance and tables sizes.

As i see it, i have 3 options on how to place the tables in my database (InnoDB):
Every option would be able to use partitioning as needed.

1.
----
One or a few very large fact tables and dimension for all users(>50+ millions of rows).
As of now it would contain data for 5000+ users with anywhere from 10.000 - 100.000 rows (facts tables and dimensions) per user.

Concerns:
Performance due to joins (must be joined not only be keys, but also by user_id = '111'.
Unmanageable database size

---

2.
---
'Clusters' of users sharing facts tables and dimensions. Maybe 3-500 users for each dimensional model.
This would give me more manageable tables sizes, but i would still have to save the user_id with most rows.
Would give me some hundred tables, some with redundant data.
---

3.
---
One dimensional model per user. Could be some 'static' dimensions that more could be used across multiple user dimensions.
I would then not have to save the user_id in the table and thereby increase performance using joins.
But i would also have thousands of tables in the database (with 5000 users probably at least 20.000 tables).
---

Any adwise on which way to go with this?

Options: ReplyQuote


Subject
Views
Written By
Posted
Dimensional model, user ids and number of tables
10265
June 24, 2009 01:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.