Solution Architecture Paralism Redeisgn to Share same Solution

Question

Hi,

I have SQL14 SMP with 8 processors, i have an 2 ETL with 2 main cube.
ETL A (cube1) is Source for ETL B (cube2)

Extracting 5 companies sequentially, ETL A runs 30min and then ETL B begins seuentially for the 5 companies

Company 1 is te biggest takes 80% of the time.

I have another company joining and the 6th company wants to run independantly every hour

I was thinking of using same solutions but have company 6 have its own DB but the server doesnt handle if the 2 solutions overlapped

Im not sure on how i should design where it does not affect each other, probably best to have had a great solution to have all 6 runs within 15min that runs hourly but i cant get the right sp i thought of duplicating the solution but it will still be a bottleneck accessing the ERP

Please help, how would you go about it?

regards

Answer 1

Hi, Thank you for assisting.

Using SSIS all in 1 db using the same tables but the 6th company wants the same solution but to run independantly of others timing

the currently solution ETLA runs every 2 hours with ETLB runs every 3 hours, but the process of A (30min) + B (1 hour) takes forever to see results and the smaller companies can possibly do every hour, doing A+5 within an hour but it uses the sames tables and solutions

was thinking of duplicating the solutions using different database replicating the tables however my SMP server does not do paralism well
and both solutions would fetch from the same ERP tables possibly the same time

the big company lready not hapy with the 2 hour refresh and i shud be improving and not worsening

please help