More blog posts in Data Services and Data QualityWhere is place located?

    Data Services and Data Quality

    2 Posts authored by: Suraj Garg

    First step is to check if installed OS and your DB  are compatible with BODS.

    (Refer to BODS 3.2 Supported Platforms. PDF)

     

    If using old OS, then uninstall BODI and install BODS.

     

    If new OS has been installed, then directly install BODS.

     

    Steps to setup BODS on existing server

     

    1. Install BODS
    2. Upgrade/configure all the repositories after taking sufficient backups.
    3. Modify  the datastore connections (applicable only if new database server has been installed).
    4. Add job servers.
    5. Add repositories to the Management Console.
    6. Add Users
    7. Create ODBC connections (applicable only if new OS has been installed).
    8. Create folder structure (applicable only if new OS has been installed).
    9. Sybase Client installation and configuration (applicable only if new OS has been installed).
    10. Oracle Client installation and configuration (applicable only if new OS has been installed).

         

    Step 1: Installation of BODS

    Refer to Installation Guide Data Services 3.2.pdf

     

    Use Skip Configuration setting for following options:

    Directory Selection

    Local Repository Selection

    Configure Job Server

    Configure Access Sever

    Configure Metadata Integrator

     

    Use Tomcat server with default settings as Web Application Server.

     

     

     

    Step 2: Upgrade/configure Repository

    Go to Start--> All Programs -- >SAP BusinessObjects XI 3.2-->SAP BusinessObjects Data Services-->Data Services Repository Manager

     

    Follow below steps:

     

    i)                    Select Local Repository.

    ii)                  Database server name

    iii)                Give Repository name.

    iv)                Give credentials

    v)                  Get Version

    vi)                Upgrade Repository.

    NOTE:  Take repository DB backup first and for worst case take backup of JOBs also in atl file.

     

     

     

    Step 3: Modify the Datastores settings.

     

     

    Step 4: Add Job servers

    1. Go to Start--> All Programs -- >SAP BusinessObjects XI 3.2-->SAP BusinessObjects Data Services--> Data Services Server Management
    2. Specify Cache as existing path i.e. D:\Log\Cache
    3. Select “Edit Job server config -> Add”. Check default repository option for all.
    4. Restart after adding all the job servers.

         

    (Note: Keep the screen shot of details of existing job servers)

     

    Step 5: Add Repositories to Management Console

    1. Go to Start--> All Programs -- >SAP BusinessObjects XI 3.2-->SAP BusinessObjects Data Services--> Data Services Management Console

       

    1. Use default login credentials.

    User Id – admin

    Password – admin

     

    1. Go to Management-> Repositories -> Add.

     

    (Note: Keep a screen shot of existing repositories from console.)

     

     

    Step 6: Add Users

    1. Go to Start--> All Programs -- >SAP BusinessObjects XI 3.2-->SAP BusinessObjects Data Services--> Data Services Management Console

     

    1. Go to Management-> Users-> Add.

     

    (Note: Keep the screen shot of details of existing users)

     

    Step 7: Create ODBC connections

    This step is applicable only if new OS has been installed.

     

    Go to C:\Windows\SysWOW64\odbcad32.exe.

    (Note: Keep the screen shot of details of existing ODBC connections)

     

    Step 8: Create folder structure for input files

    This step is applicable only if new OS has been installed.

     

     

     

    Step 9: Sybase Client installation and configuration

    This step is applicable only if new OS has been installed.

    Install Sybase Client. After installation check if it configured. If not, then follow below steps.

    1. Go to Start-> All Programs -> Sybase ->Connectivity->Open Client Directory Service Editor.
    2. Server Objects -> Add.

     

     

     

    Step 10: Oracle Client installation and configuration

    This step is applicable only if new OS has been installed.

    Install Oracle Client.

     

    And configure it.

    Snapshot of few fields of source file

     

    AccountGroupcodeBalance amount
    123452743
    223451255
    323455150
    523457870

       

    Snapshot of lookup table

    XXX1XXX2amount selectionGroupcode selection
    8076807601Balance1;3;15-25;27;29-35
    8075807601Balance52-60;65;70;77-79;85
    9004900401Balance5

     

    Now you have to lookup the XXX1 and XXX2 value from the lookup table based on the condition : value of sourcefile.groupcode in (lookup table.groupcode selection).

     

    Method 1 : First way is splitting the semi-colons separated in the rows as described in this link http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=283705665 and then apply the logic as shown below :

     

    1. replace - into ..

     

    e.g. replace_substr( ff_final_selection."Groupcode selection",'-','..' )

     

    2. Append it with square brackets in case of range values and then append overall by ms()

     

    e.g.  'ms(' || ifthenelse( index(Query."Groupcode selection",'.',1) is not null,'\[' || Query."Groupcode selection" || '\]' ,Query."Groupcode selection" ) || ')'

     

    Our final lookup table looks like now :

     

     

    XXX1XXX2amount selectionGroupcode selection
    8076807601Balancems(1)
    8076807601Balancems(3)
    8076807601Balancems([15..25])
    8076807601Balancems(27)
    8076807601Balancems([29..35])
    8075807601Balancems([52..60])
    8075807601Balancems(65)
    8075807601Balancems(70)
    8075807601Balancems([77..79])
    8075807601Balancems(85)
    9004900401Balancems(5)

     

    Now you can easily lookup these values using the operator '~' in the lookup_ext function and get the desired output.

     

    For lookup_ext with ms(). you can follow the link http://wiki.sdn.sap.com/wiki/display/EIM/lookup_ext%28%29+with+pattern

     

    Output will be :

     

    AccountGroupcodexxx1xxx2Balance amount
    1234527807680760143
    2234512nullnull55
    3234551nullnull50
    5234578807580760170

     

     

    Method2 : Instead of splitting into the rows using a custom function with word_ext function

     

    Function definition is given below :

     

    # Initialization of Variables
    $L_CNTR = 1;
    $L_CNTR1 = 0;
    $L_CNTR2 = 1;
    $L_LEN_STR = length($input_field);
    $L_OUTPUT_STR = '';

    # Count the no. of semi-colons
    while ($L_CNTR <= $L_LEN_STR)
    begin
    $L_TEMP = substr($input_field,$L_CNTR,1);
    if (match_simple($L_TEMP,';') = 1)
          $L_CNTR1 = $L_CNTR1 + 1;
    $L_CNTR = $L_CNTR + 1;
    end
    # replace the column value with suitable syntax
    while ($L_CNTR2 <= $L_CNTR1+1)
    begin
    $L_TEMP = word_ext( $input_field,$L_CNTR2,';');
    if (index($L_TEMP,'.',1) is not null)
    $L_OUTPUT_STR = ($L_OUTPUT_STR || '\[' || $L_TEMP || '\]');
    else $L_OUTPUT_STR = ($L_OUTPUT_STR || $L_TEMP);
    if ($L_CNTR2 <> ($L_CNTR1+1))
    $L_OUTPUT_STR = ($L_OUTPUT_STR || ';');
    else $L_OUTPUT_STR = $L_OUTPUT_STR;
    $L_CNTR2 = $L_CNTR2 + 1;
    end

    $L_OUTPUT_STR = ('ms(\{' || $L_OUTPUT_STR || '\})' );
    # While loop ends

    Return $L_OUTPUT_STR;

     

    Apply the above fxn on the filed in lookup table and then follow the same steps described in Method1

    Filter Blog

    By author:
    By date:
    By tag: