SQL Server Performance

DW problem

Discussion in 'Analysis Services/Data Warehousing' started by shama, Jan 7, 2006.

  1. shama New Member

    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
  2. solar New Member

    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

    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.

Share This Page