2 Flares Twitter 0 Facebook 1 Google+ 1 LinkedIn 0 Email -- Pin It Share 0 2 Flares ×

Map CDC Operation Transformation:

Map CDC Operation TransformationSummary:

Map CDC operation transform enables Source based CDC (Changed Data Capture) or Delta implementation.

  • Microsoft SQL (2000 onwards) and Oracle (9i onwards) supports Source based CDC.
    • Using values for the Sequencing column and Row operation column performs three functions:
      • Sorts input data based on values in Sequencing column box and (optional) the Additional Grouping Columns box.
      • Maps output data based on values in Row Operation Column box. Source table rows are mapped to INSERT, UPDATE, or DELETE operations before passing them on to the target.
      • Resolves missing, separated, or multiple before- and after-images for UPDATE rows.
      • Implementing source based CDC depends completely on source behavior.

Prerequisite:

  • A CDC datastore should be created for CDC tables providing required credentials at BODS side.
  • A CDC subscription name should be provided in Source editor properties.

NOTE:

  • Map CDC operation transform reads data only from a CDC table source.
  • CDC table on top of SQL table should be created in MS SQL Server database and should be imported to CDC datastore in BODS.

Setting up Microsoft SQL Server for CDC:

The following steps summarize enabling CDC on MS SQL Server (Enterprise version) database.

  • Enable CDC on database (that you are working on) before creating a CDC table as in the below figure.
Map CDC Operation Transformation in SAP BODS
Figure 1: Procedure to enable CDC on a database
  • Create a table on top of which you want to enable CDC as in the below figure.
Map CDC Operation Transformation in BODS
Figure 2: Query to Create base table
  • Enable CDC on the top of the table as in the below figure.
Map CDC Operation Transform in BODS
Figure3: Procedure to create CDC table over top of base table

The following steps summarize the procedure to configure SQL Replication Server for your Microsoft SQL Server database.

  • On the Replication node of the Microsoft SQL Enterprise Manager, select the Configure Publishing and the Distribution option. Follow the wizard to create the Distributor and Distribution database.

The following steps summarize the procedure to configure SQL Replication Server for your Microsoft SQL Server database. The wizard generates the following components that you need to specify on the Datastore Editor when you define a Microsoft SQL Server CDC datastore:

  • MSSQL distribution server name
  • MSSQL distribution database name
  • MSSQL distribution user name
  • MSSQL distribution password 
  • To create new publications that specify the tables that you want to publish. The software requires following steps
  1. Right-click Replication menu (or Local Publications menu), then select New Publication. The New Publication Wizard opens.
  2. In the New Publication Wizard, click Next.
  3. Select the database that you want to publish and click Next.
  4. Under Publication type, select Transactional publication, and then click Next to continue.
  5. Select tables and other objects to publish as articles. Set columns to filter tables. Then click to open Article Properties.
  6. Set the following to False:

–       Copy clustered index

–       Copy INSERT, UPDATE and DELETE

–       Create schemas at subscriber.

  1. Set the ‘Action if name is in use’ to keep existing object unchanged.
  2. Set ‘Update delivery format’ and ‘Delete delivery format’ to XCALL <stored procedure> if you want before images for UPDATE and DELETE commands. Click OK to save the article properties and click Next.
  3. Add filters to exclude unwanted rows from published tables (optional). Click Next.
  4. Select Create a snapshot immediately and keep the snapshot to initialize subscriptions and select Schedule the snapshot agent to run at following times(optional). Click Next.
  5. Configure Agent Security and specify the account connection setting. Click Security Settings to set the Snapshot agent.
  6. Configure the Agent Security account with system administration privileges and click OK.
  7. Enter the login password for the Log Reader Agent by clicking Security Settings. Note that it has to be a login granting system administration privileges.
  8. In the Log Reader Agent Security window, enter and confirm password information.
  9. Click to select Create the publication then click Finish to create a new publication.
  10. To complete the wizard, enter a Publication name and click Finish to create your publication. 

Setting up Business Objects Data Services for CDC:

To use SAP Business Objects Data Services to read and load changed data from SQL Server databases, do the following procedures on the Designer:

  • Create a CDC datastore for SQL Server

The CDC datastore option is available for SQL Server.

  1. Open the Datastore Editor.
  2. Enter a name for the datastore.
  3. In the Datastore type box, select Database.
  4. In the Database type box, select Microsoft SQL Server.
  5. Check the Enable CDC box to enable the CDC feature.
  6. Select a Database version. Change-data tables are only available from SQL Server 2000 Enterprise.
  7. Enter a Database name (use the name of the Replication server).
  8. Enter a database User name and Password.
  9. In the CDC section, enter the following names that you created for this datastore when you configured the Distributor and Publisher in the MS SQL Replication Server:
  • MSSQL distribution server name
  • MSSQL distribution database name
  • MSSQL publication name
  • MSSQL distribution user name
  • MSSQL distribution password
  1. If you want to create more than one configuration for this datastore, click Apply, then click Edit and follow step 9 again for any additional configurations.
  2. Click OK.

You can now use the new datastore connection to import metadata tables into the current repository.

  • Import metadata for SQL Server tables
  • Configure a CDC source 
Map CDC Operation Transform in SAP BODS
Figure 4: CDC Datastore configuration in Designer for MS SQL server

Steps to create CDC datastore are clearly mentioned in the previous section.

Map CDC Operation Transform in BODS 1
Figure 5: Map CDC Operation Transformation object hierarchy
Map CDC Operation Transform in BODS 2
Figure 6: Map CDC Operation transform Job ETL flow
  • Map CDC Operation transform reads data only from CDC table as in figure 6.
  • One CDC table is accepted per one job.
  • No other table is allowed in the job which consists of CDC table.
Map CDC Operation Transform in BODS 3
Figure 7: Map CDC Operation Transformation Defining rules
  • Schema out of Map CDC operation transform will have same structure as that of CDC table in MS SQL server as in figure 7.

CDC Table:

  • CDC Table is a different table generated using a procedure that comes with CDC package. It consists two types of fields.
    • Business fields – These are fields of SQL table on which CDC is enabled.
    • CDC or Technical fields – These fields are generated by SQL Server.
  • CDC table cannot be created through Data Manipulation Language like SQL table.
Map CDC Operation Transform in SAP BODS 1
Figure 8: CDC table structure
  • After importing the table two fields are generated by Data Services software.
    • DI_SEQUENCE_NUMBER – Acts like a Surrogate ID or Serial number column.
    • DI_OPERATION_TYPE – It generates operation type values.

Valid values for this column are:        I for INSERT

                                                         D for DELETE

                                                         B for before-image of an UPDATE

                                                         U for after-image of an UPDATE

  • If a record is Updated in Base SQL table, CDC table is updated with two records
    • Before image of an update.
    • After image of an update. 
  • CDC table Source editor properties:

    CDC options tab:

  • CDC subscription name – SQL Server CDC uses the subscription name to mark the last row read so that the next job starts reading the CDC table from that position.
  • Enable check point – Once a check-point is placed, the next time the CDC job runs, it reads only the rows inserted into the CDC table since the last check-point.
  • Get before image for each update row – If it is checked, database allows two images to be associated with an UPDATE row: a before-image and an after-image.

Attachment:

.atl File:

  • Import the below .atl file in the Data Services Designer to find the Job for Map CDC Operation transformation.

Click Here to Download ATL File.

That’s it.

Enjoy 🙂

2 Flares Twitter 0 Facebook 1 Google+ 1 LinkedIn 0 Email -- Pin It Share 0 2 Flares ×