How the data loaded into the HCM Staging Area is moved to the productive identity store?

[akhilseth]

Hi All,

I am the process of connecting HCM system to Productive Identity store.We have already defined the set up which is extracting data from HCM system and exported it to HCM staging area.

My Question here is how we can transfer the data which is currently in staging area to productive identity store.

Thanks

Akhil Seth

[Ckumar · Accepted solution]

Hello Akhil,

Loading data from HCM staging area to Productive Identity Store is a standard process so you dont have to do anything.

If you have done the configuration properly using the standard documents then it will be loaded in to Productive Identity store automatically, however please check you global constant SAP_MASTER_IDS_ID value. It should be same as your productive Identity Store ID.

Just run the below query to verify the data in the productive identity store. It will total count of user in your productive Identity store.

select Count(A.mskey) from IDPM_OPER.IDMV_VALLINK_BASIC A, IDPM_OPER.IDMV_VALLINK_BASIC B where A.mcAttrname='MX_ENTRYTYPE' and

A.mcvalue='MX_PERSON' and B.mcAttrname='MSKEYVALUE' and A.mcIdstore='YOUR_PRODUCTIVE_IDENTITY_STORE_ID' A.mskey=B.MSKEY

Don't forget to replace the YOUR_PRODUCTIVE_IDENTITY_STORE_ID by your productive Identity Store ID before running the query.

Regards,

C Kumar

[akhilseth · Accepted solution]

Hi C Kumar,

Thanks for your explanation.I just checked global constantSAP_MASTER_IDS_ID value. It was not same as my productive Identity Store ID and is showing value 1 which we had deleted long time back.

My assumption is it might be taking by default as 1.I just changed the value to 2 which is the correct id.

Question1: Do I need to re-import the data currently residing in VDS or it will automatically recognize the same?

Question2: If automatic , where we can check the frequency maintained in the system so we can validate the data availability

Thanks

Akhil Seth

[akhilseth · Accepted solution]

Further to the last comment, I also tried executing the query by replacing the 'YOUR_PRODUCTIVE_IDENTITY_STORE_ID' to '2' which is my productive ID.But error received stating:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'A'.

Query written:

select Count(A.mskey) from IDPM_OPER.IDMV_VALLINK_BASIC A, IDPM_OPER.IDMV_VALLINK_BASIC B where A.mcAttrname='MX_ENTRYTYPE' and

A.mcvalue='MX_PERSON' and B.mcAttrname='MSKEYVALUE' and A.mcIdstore='2'A.mskey=B.MSKEY

[Steffi_Warnecke · Accepted solution]

Hello there,

I'd say there is an "and" missing in the query. At the end after your identity store id and before the mskey comparison.

But that query is pretty ... interesting ... anyway. Very complicated for just getting this info. What's wrong with a simple

select count(mcmskey) from idmv_entry_simple where mcentrytype = 'MX_PERSON' and mcidstore='2'

Regards,

Steffi.

[akhilseth · Accepted solution]

Thanks Steffi for your response.I tried adding 'and' and got the below error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'IDPM_OPER.IDMV_VALLINK_BASIC'.

Akhil

[Steffi_Warnecke · Accepted solution]

If you are not running the query with the oper-user, you don't have the permission to reach those tables (and therefor can't find them). AND if your oper-user has another name than "IDPM_OPER" it won't work either.

I like to test queries with the runtime user (rt) and if I'm locked in with that one, I don't need any <username>. in front of the tables.

[Ckumar · Accepted solution]

Hello Akhil,

I am sorry as I just copied the SQL from my Test environment

The right query should be

select Count(A.mskey) from IDMV_VALLINK_BASIC A, IDMV_VALLINK_BASIC B where A.mcAttrname='MX_ENTRYTYPE' and

A.mcvalue='MX_PERSON' and B.mcAttrname='MSKEYVALUE' and A.mcIdstore='2'  and A.mskey=B.MSKEY

.

Run this query and check whether it is showing the right number of employee data in your master identity store.

If not then you need to manually test the "Write HCM Employee To SAP Master" job by running it for anyone HCM Employee by passing the MSKEYVALUE. If it is working fine then you can dump you staging area data and push all the employee data again.

I will suggest that before loading every employee, first check everything for 1-2 employee and then load all the employee to IDM.

Regards,

C Kumar

[Ckumar · Accepted solution]

Thanks Steffi for rectifying me.

Actually I was using my query to find the mskeyvalue of the person too and I just replaced it with Count and paste it here.

Regards,

C Kumar

[Steffi_Warnecke · Accepted solution]

Hello Kumar,

you'll find the mskeyvalue in the idmv_entry_simple, too. If you just need those information, I  recommend using that table, because the result will be there pretty fast and you don't have to use alias for the same table and so many where clauses. etc.

Regards,

Steffi.

[Ckumar · Accepted solution]

Yes Steffi, you are absolutely right we can use idmv_entry_simple view for MSKEYVALUE however I was trying for some more field too.

Regards,

C Kumar

[akhilseth · Accepted solution]

Thanks C kumar for your detailed reply.I ran the query and it is showing me the count but I am not sure if that is the exact count as I have imported data several times for testing purpose but not sure how much is the success.

My question at this time is that do we have any specific table where these data are getting stored and I can see the data.

In my SQL server database, I see so many tables but not sure which table has this data coming from HCM system.

Akhil

[akhilseth · Accepted solution]

Also, I tried replacing Count(A.mskey) with * to see the data but it is not showing me the data I tried importing from HCM.

Akhil

[akhilseth · Accepted solution]

Hi All,

I just wanted to let you know that I can see the data with query idmv_entry_simple that has been imported from HCM and is under MX_HCM_EMPLOYEE entry type.

I am sure, there is the command line modification needed for the query C Kumar suggested and I am gonna explore that more.But I like to thank both of you for being so supportive.

Thanks again!

Akhil Seth