SQL Server Performance Forum – Threads Archive
DW problem
there r 1000 clients and each client has 5 sites so our flat file or spreadsheet has the following structure.client_id| site_id| BMindex| Desc| Value
001 | 10 | BM001 |SUM |300
|20 | BM002 |— |200
likewise for all 1000 clients now here i ve 1000(client)*5(sites/client)*1400(each site sends flie containing 1400 rows)=7 million rows roughly. and we have 5 such files say,1,2,3,4 and 5.
problem here is like in order to load data in Data mart as file–>ODS–>Staging area–>datamart.
we ve to repeat the process for 5 times for each time. other way is to join first 3 files and last two files and follow the above process there by reducing it to 2 steps. but joins drastically reduces performance.
another problem is with files having 500 cols and 5000 rows.in this case we cant join 3 files.
If anyone of u ve the better idea with this problem please shrare. …shama Shama "koi umeed nazar nahi aati, koi soorat nazar nahi aati,aati hai haal-e-dil par hansi,neend raat bhar nahi aati
quote:Originally posted by shama
there r 1000 clients and each client has 5 sites so our flat file or spreadsheet has the following structure.
client_id| site_id| BMindex| Desc| Value
001 | 10 | BM001 |SUM |300
|20 | BM002 |— |200
likewise for all 1000 clients now here i ve 1000(client)*5(sites/client)*1400(each site sends flie containing 1400 rows)=7 million rows roughly. and we have 5 such files say,1,2,3,4 and 5.
problem here is like in order to load data in Data mart as file–>ODS–>Staging area–>datamart.
we ve to repeat the process for 5 times for each time. other way is to join first 3 files and last two files and follow the above process there by reducing it to 2 steps. but joins drastically reduces performance.
another problem is with files having 500 cols and 5000 rows.in this case we cant join 3 files.
If anyone of u ve the better idea with this problem please shrare. …shama Shama "koi umeed nazar nahi aati, koi soorat nazar nahi aati,aati hai haal-e-dil par hansi,neend raat bhar nahi aati
client_id| site_id| BMindex| Desc| Value
001 | 10 | BM001 |SUM |300
|20 | BM002 |— |200
likewise for all 1000 clients now here i ve 1000(client)*5(sites/client)*1400(each site sends flie containing 1400 rows)=7 million rows roughly. and we have 5 such files say,1,2,3,4 and 5.
problem here is like in order to load data in Data mart as file–>ODS–>Staging area–>datamart.
we ve to repeat the process for 5 times for each time. other way is to join first 3 files and last two files and follow the above process there by reducing it to 2 steps. but joins drastically reduces performance.
another problem is with files having 500 cols and 5000 rows.in this case we cant join 3 files.
If anyone of u ve the better idea with this problem please shrare. …shama Shama "koi umeed nazar nahi aati, koi soorat nazar nahi aati,aati hai haal-e-dil par hansi,neend raat bhar nahi aati
Shama,
If I am not missin anything then …
I would use small files instead of two big files. The big reason is easy to manage and if an error occurs, you don’t need to go through all files to figure it out. Thanks.
]]>