Understanding Warehouse Business Units, TableSet Sharing, and SetID Mapping

In PeopleSoft source systems (such as Campus Solutions or CRM), the rows in a control table (such as supplier, customer, and account) are divided into groups based on a key called the SetID. Each group of rows with the same SetID constitutes a tableset..

Image: Tablesets on a hypothetical supplier table

This diagram illustrates the tablesets on a hypothetical supplier table with two SetIDs represented in red and blue

Every business unit associates with a particular SetID on every control table. For example, BU1 might use the red tableset on the supplier table. This assignment serves to limit the suppliers that appear in prompts for transactions that take place in the context of BU1. Several other business units could also use the red SetID on the supplier table, in which case these business units share the red tableset (hence the term tableset sharing). Since control tables in a PeopleSoft source system generally become dimensions in EPM, most EPM dimensions are SetID-based.

Record Groups

Control tables such as supplier are themselves grouped into record groups based on the commonality of business process. SetID assignments actually take place at the record group level. In other words, a business unit must have the same SetID on all control tables in a given record group. Each business unit in a PeopleSoft source system must have a SetID assignment on every record group. At the time a business unit is created, the system assigns a default SetID to all record groups for that business unit. The user can then manually modify the SetID assignments as desired. For example, the following table illustrates the SetID assignment for a few hypothetical business units on a few hypothetical record groups:

Image: SetID assignments for hypothetical record groups

This diagram provides an example SetID assignments for record groups.

Assuming that the supplier table shown earlier is included in record group RG1, then (given the SetID assignments indicated in the figure above) BU1, BU2, and BU3 would have access to the first three suppliers, while BU4, BU5, and BU6 would have access to the last three.

EPM is a SetID-based database. Consequently, all warehouse business units must have SetID assignments on all warehouse record groups. However, because each PeopleSoft source system has its own tableset sharing (or set control space), the mapping between source business units and warehouse business units has implications for how SetIDs must relate between the source system and the warehouse. As illustrated below, in order to correspond two set control spaces, it is necessary to provide two distinct mappings: the business unit mapping (which is under user control) and the warehouse lineage. Warehouse lineage defines the correspondence between all PeopleSoft source tables and EPM warehouse tables.

See Warehouse Lineage and Source Blueprints.

Image: Hypothetical source to warehouse set control space mapping

This diagram provides an example of source to warehouse set control space mapping.

The preceding figure illustrates two set control spaces and the mappings between them, with SetID values shown as colored squares. In this example, warehouse business units have been created and mapped to source business units in a one-to-one relationship. Based on the lineage, it is possible to determine which SetID values to assign to the corresponding warehouse record groups. Without the lineage information this determination would not be possible.

SetIDs from one or more sources can merge into a single warehouse SetID (in other words, "many-to-one" relationships are allowed). However, the system does not allow one-to-many relationships to exist between source SetIDs and warehouse SetIDs on the same warehouse record group. For example, in the illustration below, BU1 and BU2 shared SetID RED in the source. Assuming that RG1 is related by lineage to WG1, then the situation illustrated would force one SetID (RED) to map to two different values (SHARE and MODEL) on warehouse record group WG1. This situation is not allowed because it would create a multi-valued lookup that would cause errors in the ETL process. Consequently the system does not allow this configuration to pass its validation step. You must modify the warehouse SetID assignments to remove the splitting. In the case illustrated below, simply assigning the same warehouse SetID to WBU1 and WBU2 on record group WG1 resolves the problem.

Image: SetID splitting

This diagram illustrates SetID splitting.

FSCM and SetID Conflicts

Business unit merging is not the only way that SetID conflicts can arise. There are two instances in the FSCM product that has a lineage that can introduce conflicts, as shown below:

Image: SetID conflicts in the FSCM to warehouse mapping

This diagram illustrates SetID conflicts when mapping FSCM to the EPM warehouse.

The problem occurs when record groups from a source merge in the warehouses (for example, AP_10 and FS_02 merging into FS_02, as shown in the diagram above). When you use the Business Unit Creation Wizard, it assigns an initial guess for the SetIDs of the warehouse business units that it creates for you. A problem can occur in the case where two or more business units have different SetIDs on AP_10 and FS_02. For example, if you had two business units in FSCM (BU1 and BU2), and assigned them both SetID RED on AP_10, and BLUE on FS_02, it is possible that the wizard could select RED as the SetID assignment for FS_02 for WBU1, and BLUE for the SetID of WBU2. In this case the wizard would detect splitting because it would see both RED and BLUE from the source being split to RED and BLUE on the target. The solution to this problem is straightforward: you should manually create the warehouse business units, and assign the correct SetID. Then you can map the business units and do the validation in the Business Unit Mapping page. If you have an FSCM source with a complex SetID configuration and you detect conflicts related to AP_10, FS_02, FS_07, or FS_18, it may be due to this lineage problem.

Warehouse lineage defines the relationship between PeopleSoft transaction tables and EPM target warehouse tables, as determined by the ETL process. Certain ETL jobs move data from PeopleSoft source tables to EPM Operational Warehouse - Staging (OWS), Operational Warehouse - Enriched (OWE), and Multidimensional Warehouse (MDW) target tables, and warehouse lineage simply refers to the path the data takes between the two systems. Lineage information is used during the warehouse business unit creation process to determine the appropriate PeopleSoft source SetID for a given warehouse business unit, and map that SetID to the appropriate warehouse record group.

Source blueprints actually record the warehouse lineage, and ship with EPM as system data.

Warehouse lineage information is only required for SetID-based source systems. As such PeopleSoft delivers blueprints for PeopleSoft source systems only. Typically, third-party source systems do not use a SetID column, and thus have no impact on the blueprints. You need not create blueprints for non SetID-based third-party source systems.