For something as fundamental as Setid indirection, it’s surprising how few people really understand how it works. Hopefully, this will shed some light on the subject.
Why, when, and how?
Setid indirection is the key to why PeopleSoft has been able to compete at the same level as Oracle and SAP. It allows complex organizations to use a single instance of the application, versus requiring multiple installs, each set up and configured differently. The Structure definition in Oracle Flexfields is intended to provide similar functionality (but less consistently).
Setid indirection was something introduced in PeopleTools 2, and to this day, amazes me that PeopleSoft was able to change something so fundamental in its products after the products were initially released. It’s also interesting that the approach for delivering it was to introduce it as a core component of the toolset, versus creating a standard around it.
So, we’ve talked about the when and why. Let’s talk about the how. The best way to explain the purpose of Setid indirection is to look at a complex company as an example. One I like to use is Virgin. This is because it’s pretty clear that the company has multiple disparate lines of business (and helps explain how it works.
So, let’s look at the high level structure of Virgin:
- It has a recording label.
- It has a series of retail stores.
- It has an airline.
We could also complicate matters by looking at the different countries it operates in, and some of the requirements there as well, but let’s get to that later.
So, there are 3 different lines of business. In this example, there are definitely certain things that need to be different across the different lines of business:
- There are probably different suppliers with different rules of operation.
- There are definitely different products that are delivered.
- There are probably different jobs, jobcodes, and unions.
However, in any organization, there are certain things that should be the same (to provide consistency where needed).
- There needs to be a common chart of accounts for reporting financial information.
- There usually needs to be a common fiscal calendar (again to allow consistent reporting of financial information).
Therefore, there are aspects of the business that need to be kept separate between lines of business, and aspects that need to be shared across lines of business.
Now, let’s add back in the complication of operating in multiple countries. Because each country has its own regulations and currency, some of those business rules also need to be segregated between countries, but also need to be shared across countries.
Enter: the Setid. The Setid is the means by which sets of values are given an identifier. The Setid is generally the topmost key of any table in a PeopleSoft application that does not hold transactions. Setids are managed at the table level (or, actually, at the group of table level). So, in the example of Virgin, you could see the following:
- One common setid for the set of account numbers used to represent the financials.
- 3 different setids for the different products sold by each line of business.
One way to understand setids is to understand the entities in the application that use it (i.e. the entities where it makes sense to both segment and share definitions across lines of business). These are:
- Entities that would be dimensions in an OLAP cube. These are things like customers, vendors, employees, products, and departments.
- Business rules that govern how processing is to occur in the application. These are things like payment terms, pay cycles, allocation rules, calendars, and timespans.
The other main type of table in a PeopleSoft application is the transaction or document. These entities are keyed by a corollary field, called Business Unit. You could think of the business unit as a set of books in financials, or a discrete instance of a set of transactions that stand on their own for other applications. Examples of business unit keyed tables are Journals, Ledgers (which are just an aggregation of journals), vouchers, receivables, orders, returns, and cases). In the OLAP world, tables keyed by business unit are generally facts in the system.
So, how to business units and setids relate to each other? Good question. Remember, that transaction tables are keyed by business unit. One could also think of a transaction as any business event that happens in an ERP application that needs to be captured or measured. Therefore, the role of setids is to control either the set of values used for things captured in a given transaction, or the rules used for processing that transaction.
This means that there is a relationship between a business unit, and the sets of values that are used for valid data, or for controlling processing (which are stored in tables). This is done using the tableset controls (which is accessed in the PeopleTools menu, under the administration sub-menu). The mapping of setids to business units occur with the following levels:
- There’s a level that groups similar tables together into a functional group (called record groups). This means that if there are 5 tables for storing or processing journals, you don’t have to map those tables individually, they are assigned a group and the mapping is at the group level.
- There’s a level called record group controls, that actually performs the mapping. When setting this up, you first provide a business unit value, and then assign setids for each record group that would be used for that business unit. The sharing and segregation occurs when you pick different setids for business units (or the same across business units). This means the following:
- If you use a single “share” setid across all business units for your account tables, then all business units will use that common set of values.
- If you use a setid for “recording”, one for “retail”, and one for “airline” four your product tables, then whichever business units you assign the “airline” setid will share a common set of products (you may be in a sharing situation if you set up a different set of books, or business unit, per country the airline operates in).
So, what happens when I use the application?
Another good question. Let’s build on the previous examples by looking at what happens when you enter a journal (i.e. a transaction).
When you go into the journal page, the first thing it does is ask you what business unit the journal is for. When you pick this value, you have identified what set of record group controls will be used for entering that journal. The first place you will se that is in prompting in the page. Pretty much any field, with the exception of the numeric fields, will use the setid indirection (i.e. the record group controls) to identify what values to use. One example may be the following:
- When you prompt on the journal source, the setid for sources may be mapped to “share”.
- When you prompt on the account, the setid for account tables may be mapped to “share”.
- When you prompt on the department, the setid for the department tables may be mapped to “retail”, and so on.
When you run any batch program, the first thing you need to supply in the run control is the business unit for the transactions to be processed. This will identify the business rules to be used for that process (such as balancing rules for posting journals).
Okay, what about reporting?
Another good question, especially for nVision. PeopleSoft reporting tools definitely need to understand about setid indirection, so that the appropriate values are returned when the report joins transaction data to the attributes of the trasaction (i.e. dimensional information).
However, reporting has two extra levels of complexity:
- It is possible, and quite often desirable, to show data across multiple business unites in a single report (a consolidated balance sheet is a good example).
- Trees are very important for aggregating data, and need to be shared in a manner similar to the detail values (for example, a tree rolling up department values only makes sense for the setid of those values).
So, from a reporting perspective, here is how setid indirection works:
- Query will automatically perform setid indirection when joining between a transaction table and a valid value (dimension) table, if that table is set as a prompt table in application designer.
- Query will perform setid indirection for cascading prompts, as long as the business unit prompt has a sequence prior to the prompts that depend on it.
- nVision will only do setid indirection for the business unit specified in the report request (even if the report displays multiple business units). This is very important to know, because it is the key to solving setid indirection issues in nVision).
Now, for trees. Trees are mapped separately from tables. This means that you have the ability to map the setid for each tree differently for each business unit. However, each business unit has a default setid, and if the default setid for the business unit is correct for the tree, you don’t have to explicitly map it.
More on nVision: in design mode of nVision, setids can become even more complicated. This is because an nVision report layout can be run for different business units (which are supplied at runtime on the report request). This means that in design mode, you don’t know what business unit to use for setid indirection (and that is why in the “layout options” dialog in nVision, you can pick the setid to use for prompting of values when designing the report. In a scenario with multiple setids, this is important to know, because you may have to change the setid when adding criteria for different fields.
Applying this to the real world
Probably very few of you will be designing the business unit and setid structure of your PeopleSoft application. However, many of you will need to put this to use when writing reports, creating trees, or even maintaining the list of vendors, customers, accounts, and departments.
The first thing to know is that it’s very easy to be tripped up by setid indirection. I’ve handled lots of calls from customers who don’t get any data in their reports and think it’s a problem with the reporting tool. In the end, it’s usually either a setid indirection issue (i.e. the business unit resolves to a setid without any data for a given field), or an effective dating issue (i.e. the as-of-date resolves to a date before the first effective date).
The second is figuring out how to work around limitations in setid indirection. Many organizations will use setid indirection for security purposes (i.e. ensure that only a certain set of departments can be entered for a given business unit). Unfortunately, if you want to build a tree across the departments, the tree can only refer to a single setid (and, yes, because the tree is built on the department, which has its own definition for tableset mapping, the tree is resolved independently of the table it is built on). The only way to build a tree across multiple setids, is to create a single setid with all the values in it (assuming that the department values mean the same thing across setids). I call this “creating a super-setid”. This can be done through PeopleCode (either in an app engine program if batch is acceptable, or in SavePostChg, if it needs to be done immediately). Probably the easiest way to accomplish this is to create a view that gives a common setid for all values. You can then build the tree on the super-setid. In nVision, to get to the super-setid, you can create a super-business unit that has all the appropriate tableset sharing options for the super-setids. Because the business unit filtering in nVision can be different than that of the report request, this is possible.
Labels: Financials, PeopleSoft
