Create single cube from multiple identical OLTP databases

Question

Create single cube from multiple identical OLTP databases

don.schaeffer

Ten Centuries

Points: 1195
More actions
April 1, 2014 at 12:44 pm

#146868

I need to create a single cube from about 50 separate, but identical relational databases, each representing a different client, each with a different value of ClientId. The client databases have dim tables like customer but CustomerId = 1 may exist in every client database representing a different customer for each client.
The fact tables in the client databases have up to 20 million rows.
How do I create a single cube from these? I've tried creating multiple data source views but do not know how to get more than one represented by the cube. Can I somehow use partitions to add or remove a client? Data is typically added at different times for different clients, so it would be preferable to process on a client basis.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Wouldn't it be easier to consolidate everything in a centralized data warehouse?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

don.schaeffer Ten Centuries Points: 1195 More actions · Answer 2

I don't know if that's the easiest approach. That would entail creating a 600 gig relational database and another step to populate it from the 50 odd client databases.

I would prefer cutting that step out if possible.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

You could use partitions, but since you have only 1 data source this would mean setting up linked servers, which I would try to avoid (performance wise).

You would also have to resolve the issue that you have conflicting customer IDs.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

don.schaeffer Ten Centuries Points: 1195 More actions · Answer 4

I'm pretty new to OLAP. What I've decided to try is creating a relational db which just has views for each table. Each view unions selects from the multiple client databases, plus adds a DatabaseId column. I'll use a composite key (ex DatabaseId, CustomerId) in the relationships to the dim tables.

Another option would be to build the same db with tables instead of views.