Loading Smartlists in Data Management using Multi-column functionality

•      Quite a long time we been getting some issues from customers that they were unable to load HR data because the accounts are in the columns.

•       This whitepaper will help you regarding how to resolve those issues.

•      Now, Data Management have the capability of loading non-numeric value and loading accounts in the columns.

Source File

            In this source file there is a combination of Percentage, Date, Text and a Smartlist.

 

List of Smartlists

There are some pre-defined or default Smartlists in PBCS, they are visible when you click on the Smartlist option under create and manage.

Apart from the default Smartlists, you can see the Smartlists you created.

 

Assigning the Smartlists to the Dimension

Refer the steps mentioned in the “Defining Properties for a Smartlist” and assign the Datatypes to the dimension

In this, all the Data types are assigned to the Account dimension as Members.

 

Import Format

Define an Import format in the Data management by setting the File type as Multi Column – All Data Type.

 

Defining Mapping Expression for DATA column

Click on the Add expression editor and choose the Expression type as Driver and dimension as Account as we have assigned the data types to the Account dimension.

Then in the Columns choose the Data type columns as 1, 4 

Create a new location by assigning an Import Format.

 

Setup a Global mapping by defining the Prior Period Key and the Period Key and define the Target year.

You can also setup a Source Mapping or Application Mapping and set it to Explicit and define the same in the Data load rule.

 

Define category mapping accordingly.

Data Load Rule

Create a new Data Load Rule for the Multi-period load,

Since we have defined the Expression in the Import Format i.e “Driver=Account”, you will find a new Column called “Column Headers”.

 

 

Column Headers

In this column headers you can define the column number for each and every data type and if required you can execute them as a standalone.

 

Target Options

Make sure that while loading Smartlists the Load method in the Target application is set to “All data types with security”

Data Load mapping

Define the Mappings for all the dimensions in the Source Value and the Target Value.

  

Executing a Data Load Rule

To execute a data load rule, go to Data Load Rule, click on Execute and enter the details as shown below

Select the start and end periods according to the data that is required to be loaded and click Run. Data will be imported, validated and exported for all the periods between the start and end period range.

After Executing the Process – LOAD SUCCESS

You can see the process is being executed and being succeeded in the process details tab.

   

Data in the Data Load Workbench

Once when the data is loaded successfully, you can validate the date in the Target Data column.

 

 

Output

Setting a Form in PBCS

To check whether the data is load to the target successfully we can validate in the Target application.

Go to the Navigator and click on the Forms option.

Create a new simple form using the Add icon. Define a name for that form, click on the Layout column, and set the Point of View accordingly.

 

Once when the POV is set, click on save and click the Preview so that you can see the data displayed over the form.

Loaded Data – Preview

 

Output – Smart Form