Hi all,
I too had a similar requirement and found the solution. PFB for the detailed steps.
Requirement:
As a user I need to import the user, group and group member data from the single excel sheet.
Solution:
Excel data: (Demo data)
Step 1: Create a Data Source and attach the excel file.
Step 2: Create a Robust Transformer from the related list of the data source (created at step 1)
Step 3: Create a Transformer definition (ETL definition) and Entities.
As per the requirement, we need to load the data into 3 different tables (User, Group & Group member table).
Hence we need to create 5 tables in total (including Import set and Staging table)
1. Import set entity – To store the data from the source.
2. Staging entity – To store the copy of the source data to do the required operations.
3. User entity – sys_user table (Target)
4. Group entity – sys_user_group table (Target)
5. Group Member entity – sys_user_grmember table (Target)
Entities:
1. Import set entity:
2. Staging entity:
3. User entity:
4. Group entity:
5. Group Member entity:
Note:
Group member entity fields are dependent on previous entities (User and Group).
In the User entity, “User Name” field is considered as a primary key (Coalesce = true) and in the group entity “Name” field is the primary key.
So while declaring the path for Group Member entity fields, we need to use the primary keys of the dependent entities.
Step 4: Create Entity Mappings
1. Import set to Staging table:
2. Staging to User table:
3. Staging to Group Table:
4. Staging to Group member table:
Finally, we need to load the data from the file and run the robust transform map.
1. Load all the data from the file
2. Run the Robust transform
Results:
Users table:
Groups table:
Group Members Table:
Regards,
Sivamuruganandam J
