Sunday 9 March 2014

Scenario 11: How to maintain Audit Table for mapping/session/workflow.


Scenario 11: How to maintain Audit Table for mapping/session/workflow without accessing Repository tables?

Let's Chat: It's easy to maintain but it's take significant time to implement so, have a cup of tea. Splash water on face :-) Ready? Let's Start.

Theory/Analysis

For maintaining Audit table manually without accessing Repository tables we have to use predefined variables. Few you can find at Designer level and few at Workflow Manager level. We will use Pre Session Variable assignment option, available in components tab of session. (To use this option session must be Non-Reusable session)
We will create one mapping to process these variable values.

Practically:

Step 1: Create Audit Table

Audit Table Structure:
ID NUMBER,
FOLDER_NAME VARCHAR2(20),
MAPPING_NAME VARCHAR2(20),
WORKFLOW_NAME VARCHAR2(20),
SESSION_NAME VARCHAR2(20),
ERROR_CODE NUMBER,
ERROR_MSG VARCHAR2(500),
SRC_FAILED_ROWS NUMBER,
SRC_SUCCESS_ROWS NUMBER,
START_TIME DATE,
END_TIME DATE,
STATUS NUMBER,
TGT_FAILED_ROWS NUMBER,
TGT_SUCCESS_ROWS NUMBER

Step 2:  Create a source file.
It's just a dummy file. Ex: Create text file with only one value in that. I am taking code column in file with value 1234.

Step 3: Create Nine Mapping Variable
(Mapping Menu-->Parameters/Variable)

Name                       Type              Data Type         Aggregation
M_ERROR_CODE   Variable           Integer            count
M_ERROR_MSG     Variable           String (500)     max
M_SRC_FAILED_ROWS Variable   Integer            count
M_SRC_SUCCESS_ROWS  Variable Integer         count
M_START_TIME       Variable        Date/Time
M_END_TIME           Variable        Date/Time
M_STATUS                Variable         Integer              count
M_TGT_FAILED_ROWS Variable   Integer              count
M_TGT_SUCCESS_ROWS Variable  Integer           count
M_MAPPING_NAME Variable  String(100)           count
M_SESSION_NAME Variable  String(100)           count

Step 4: Drag and drop your source and target (Audit_Table).

Step 5:
Expression
Create total Thirteen Ports

FOLDER_NAME (Output Port) = $PMFolderName
MAPPING_NAME (Output Port) = $$M_MAPPING_NAME
WORKFLOW_NAME (Output Port)= $PMWorkflowName
SESSION_NAME (Output Port)= $$M_SESSION_NAME
O_M_ERROR_CODE (Output Port)= $$M_ERROR_CODE
O_M_ERROR_MSG (Output Port)= $$M_ERROR_MSG
O_M_SRC_FAILED_ROWS (Output Port)= $$M_SRC_FAILED_ROWS
O_M_SRC_SUCCESS_ROWS (Output Port)= $$M_SRC_SUCCESS_ROWS
O_M_START_TIME (Output Port)= $$M_START_TIME
O_M_END_TIME (Output Port)= $$M_END_TIME
O_M_STATUS (Output Port)= $$M_STATUS
O_M_TGT_FAILED_ROWS (Output Port)= $$M_TGT_FAILED_ROWS
O_M_TGT_SUCCESS_ROWS (Output Port)= $$M_TGT_SUCCESS_ROWS

Note: In Status column you will get value 103 for successful run and 105 for failed run.

Step 6:
Sequence Generator
Create a Sequence Generator Transformation.
We will use this value for ID column.
(No, Need to Change any properties, Use it with default properties)

Step 7: Connect to Target.
Connect NEXTVAL from sequence generator to ID and other values from expression to appropriate columns in target.

Step 8: Create session in same workflow for which you want to maintain Audit Information.

Step 9: Your actual session for which you are maintaining audit create as Non-Reusable Session.  (This Step required to get the Mapping name and Session name of actual mapping and session and assign those to workflow variable to be used by the session through which you are maintaining audit table. )
Session-->Right Click-->Edit-->Components Tab-->Post SessionVariable Assignment
$$W_MAPPING_NAME=$PMMappingName
$$W_SESSION_NAME=$PMSessionName

Step 10: Create Nine Workflow Variable (Workflow Menu-->Edit-->Variable Tab)
W_ERROR_CODE Integer
W_ERROR_MSG   nstring
W_SRC_FAILED_ROWS Integer
W_SRC_SUCCESS_ROWS Integer
W_START_TIME Date/Time
W_END_TIME Date/Time
W_STATUS Integer
W_TGT_FAILED_ROWS Integer
W_TGT_SUCCESS_ROWS Integer
W_MAPPING_NAME nstring
W_SESSION_NAME nstring

Note: Check Persistent check box for all variables.

Step 11:
Assignment Task
Create Assignment Task-->Assign Value for all Workflow Variable
$$W_ERROR_CODE=$S_FIRST_LAST_REC.ErrorCode
$$W_ERROR_MSG=$S_FIRST_LAST_REC.ErrorMsg
$$W_SRC_FAILED_ROWS=$S_FIRST_LAST_REC.SrcFailedRows
$$W_SRC_SUCCESS_ROWS=$S_FIRST_LAST_REC.SrcSuccessRows
$$W_START_TIME=$S_FIRST_LAST_REC.StartTime
$$W_END_TIME=$S_FIRST_LAST_REC.EndTime
$$W_STATUS=$S_FIRST_LAST_REC.Status
$$W_TGT_FAILED_ROWS=$S_FIRST_LAST_REC.TgtFailedRows
$$W_TGT_SUCCESS_ROWS=$S_FIRST_LAST_REC.TgtSuccessRows

Step 12:
Assign Workflow Variable to Mapping Variable
Session-->Right Click-->Edit-->Components Tab-->Pre SessionVariable Assignment

$$M_ERROR_CODE=$$W_ERROR_CODE
$$M_ERROR_MSG=$$W_ERROR_MSG
$$M_SRC_FAILED_ROWS=$$W_SRC_FAILED_ROWS
$$M_SRC_SUCCESS_ROWS=$$W_SRC_SUCCESS_ROWS
$$M_START_TIME=$$W_START_TIME
$$M_END_TIME=$$W_END_TIME
$$M_STATUS=$$W_STATUS 
$$M_TGT_FAILED_ROWS=$$W_TGT_FAILED_ROWS
$$M_TGT_SUCCESS_ROWS=$$W_TGT_SUCCESS_ROWS
$$M_MAPPING_NAME=$$W_MAPPING_NAME
$$M_SESSION_NAME=$$W_SESSION_NAME

We are done now. Your workflow will looks like below Image.

BINGO!!!

Common Error:
VAR_27001 Error: There is a recursive reference from mapping variable:[$$Mapping_Variable_Name] back to itself. Error detected when resolving initial value of mapping variable:[$$Mapping_Variable_Name]
Reason: By mistake may be your mapping workflow variables are conflicting with each other or default (System Variable Name)
Solution: Give different name for Mapping/Workflow variable.




Feel free to post your doubt/your marks for my effort in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

31 comments:

  1. Excellent work.... keep going

    ReplyDelete
  2. Thanks, Gopinath.. After 10 days of hardwork and after crossing 2000+ click in 7 days. You are first person to say some thing...

    Thank you for your words... It's encourage me to move ahead.

    ReplyDelete
  3. in interviews the interviewer ask me as tell me some complex mappings across your experience can i explain this scenario in that category
    and please tell me some more examples for complex mappings..
    Thanks in advance...

    Jayachandra.V
    vjchandra.mca@gmail.com

    ReplyDelete
  4. Yes, Jaychandra definitely you can take this as a complex mapping. Is in't it complex? :-)
    All the scenarios tagged as Real Time could be taken as complex mapping.

    Let's understand complex mapping: A mapping which took a lot of time of your development work. A requirement which grilled you most to implement. Each and every case belongs to you only :-) not for Interviewer.. A requirement which may be easy for you but difficult for me. So, feel free take any requirement which you felt is complex and it's good to go... :-)

    ReplyDelete
  5. Hi Buddy this is "Awesome" work

    ReplyDelete
    Replies
    1. Thanks, Muralidhar for you encouraging words :-)

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. Thank you Narendra for your valuable comment. It's encourage me to move ahead to share more and more.

      Delete
  8. Excellent rockstar!!God bless u always...Remain same forever:)

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. The audit session is getting succeeded and no data is being loaded. Please tell me where could be the problem.

      Delete
  9. Thank you for the post Harsh. I am trying to do this example. My workflow has the session which loads the data=>assignment=>session[audit]. However the audit session fails with this error."Failed to prepare the task" Can you please advise?

    ReplyDelete
  10. make sure to disable this option at session level and under general tab. and run the entire workflow and try to run now.

    ReplyDelete
  11. I haven't tried yet but its look good one. I will try today. thanks for sharing examples.

    ReplyDelete
  12. Thanks for sharing your knowledge and I appreciate the effort you are putting into the blog.

    Below are some of my doubts

    1- I don't understand what you are trying to convey in step 9. I think you mean the session we are auditing cannot not be re-usable as it wouldn't allow post session assignment. That takes us to doubt 3c.

    2- Why do we need to make these WF vars persistent? I don't see them used in next run.

    3- How do we handle auditing if my WF contains
    a)- multiple sessions and each session need to be audited. ( thinking of adding multiple audit
    sessions and assignment tasks after each session, but audit session cant be reusable
    obviously, and how will we handle WF vars in such case? )

    b)- my session (only 1 this time) contains multiple sources and targets, multiple target
    load orders. And I want individual source target statistics. I think the values of vars like
    $S_FIRST_LAST_REC.TgtSuccessRows would be total of all targets. are there vars for
    individual source/target statistics.

    c)- how to audit reusable session

    Eagerly awaiting your response.

    P.S - thanks for the tip about mapping vars in last example. Puts a lot of things into perspective. :-)

    ReplyDelete
  13. Awesome Harsh. Really useful. Thanks again for your effort to prepare this

    ReplyDelete
  14. Really very useful matter thank u soo much

    ReplyDelete
  15. can you please give me some more detail on step 9 I am trying to use this on original session that is reusable session.
    i am not able to see Post SessionVariable Assignment enable.

    please give me some suggestion....

    or any alternate way to generate session and mapping name in my audit table/file.?
    thanks in advance

    ReplyDelete
    Replies
    1. Hi, Rajeev

      My mistake, It should be Non-Reusable session typing mistake I have correct it now.

      You have create session as Non-Reusable to enable pre/post variable assignment in component tab.

      If you want to maintain for Reusable session the use Assignment Task hard code your mapping and session name and assign it to workflow variable.
      There is nothing wrong in this approach also, as Mapping and Session name won't be changed ever.

      I simply use Non-Reusable to cover post variable assignment option as well while explaining and implementing.

      Thanks.

      Delete
  16. Hi Harsh,
    This the best post ever came across. Great work!

    I tried the the mapping but have came across an error. Please help me solving the error.

    MAPPING DBG_21057 column[o_session_name], expression=[]
    MAPPING DBG_21057 column[o_WORKFLOWNAME], expression=[$PMWorkflowName]
    MAPPING DBG_21057 column[o_SESSIONSTARTTIME], expression=[02/01/1900 00:00:00.000000000]

    MAPPING TE_7002 Transformation Parse Fatal Error; transformation stopped...
    TE_7002 [02/01/1900 00:00:00.000000000]
    .
    MAPPING TE_7017 Internal error. Failed to initialize transformation [exp]. Contact Informatica Global Customer Support.

    mentioned above are few columns.

    Please guide.

    Regards
    Savita

    ReplyDelete
  17. Hi Harsh, This is what I was looking for and u have done a tremendous job.
    I have done the success scenario and it is working fine and loading all the statistics but if I want to make the main session fail and capture the error code and error_msg , in this case the session to capture audit info is failing with error :failed to prepare the task.
    Please help me with this issue.

    ReplyDelete
  18. Thanks for the article..its really good

    ReplyDelete
  19. What are the different status codes that I can get? 103,105 and ?

    ReplyDelete
  20. Very useful post..
    Could you also please clarify on the below scenario which has been already asked by one of the viewers:

    How do we handle auditing if my WF contains
    a)- multiple sessions and each session need to be audited. ( thinking of adding multiple audit
    sessions and assignment tasks after each session, but audit session cant be reusable
    obviously, and how will we handle WF vars in such case? )

    Is it possible to log audit by making audit session as reusable.

    Thanks in Advance.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Thanks for the article Harsh. It's really helpful. Besides these I also want to fetch the source table and target table. How can I fetch these?

    Thanks,
    Eshan

    ReplyDelete
  23. thank u very much ,i'm able to do this correctly after reading ur blog ,thank u once again....

    arun.

    ReplyDelete
  24. Hi Harsh , I want to capture the rejected record in the audit table with rejection reason in the same mapping. So my record should go to Target which meets the technical validation ( like not null etc ) but which ever fails to meet technical validation should move to audit table in same napping. I am using a router. But can you please guide me how can I implement both your above approach and rejection in the same mapping

    ReplyDelete
  25. I have done this kind of thing for one session as below.



    WF----> Session 1------> Assignment Task-------->Sessio1_Audit
    Session 2------> Assignment Task-------->Sessio2_Audit
    Session 3------> Assignment Task-------->Sessio3_Audit
    Session 4------> Assignment Task-------->Sessio4_Audit
    Session 5------> Assignment Task-------->Sessio5_Audit


    Session1 to 5 - Actual mapping which contains the ETL

    when I run the workflow all the business session run in parallel and the data is inserted to audit table , but for some of the session the count mismatch when i actually go and run the query for count

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete