This is a question that came up on the ITToolbox forum peopletools-l. I decided that it would be a good idea to flush out my answer in additional detail (and post it here). Interestingly, as I did my research, I discovered that how commitment control uses trees was more complex than I thought. I also discovered that one of my statements in my response was inaccurate.

Here is a link to the post.

Can you do it?

The answer to whether you can use them in GL is still yes, and the method by which you use them is the same as how you would use them elsewhere. For those who don't want to read all the way through my blog post that discusses trees, here is some background on winter and summer trees.

Winter and Summer Trees

The naming of summer and winter trees comes from whether they have leaves on them or not. So, just as with a deciduous tree, a summer tree has leaves and a winter tree doesn't.

The first question that may come to mind is "why have leaves in one type of tree, but not another?" The answer to that question is whether the hierarchy is logically separate from the items being organizaed by the tree. For example, in a position hierarchy, each position belongs at a specific place in the hierarchy, so there are no need for leaves. By contrast, in most department hierarchies, departments are rolled up into offices, regions, divisions, etc., which means that departments are the leaf, and the rollups are not actually departments (and are the nodes).

When these trees are used by products, such as nVision and Query, those tools understand how to gather the underlying information for subtotalling and filtering based on the tree type (the join for a winter tree uses the nodes for joining directly to the data table, whereas the join for a summer tree uses the nodes to identify the leaves to join to the data table).

Why would you want to use Winter Trees in GL?

This is a good question, because at first blush, most reporting is done against aggregations of chartfields in GL. However, there is one very good scenario where you may want to eliminate the leaves and have each node in the tree be a chartfield value in your ledger table: budgeting.

When you do budgeting, quite often it is just too cumbersome to budget everything at the lowest level of granularity in which you capture transaction data (i.e. your journals). If you make the decision that you will create summary accounts that represent different rollups in your hierarchy, then a winter tree will work for you.

The delivered PeopleSoft demo database has an example of a winter tree on accounts (and interestingly enough, it's called "CONTROL_BD_ACCOUNTS".

One thing to keep in mind, however, when having aggregate chartfields values that you use in your trees, is that they are real chartfield values that could have values posted to them unless you do something to specifically prevent that. The Financials developers have done exactly that for the budgeting scenario. When you set up a GL account, you can specify it as being a Budgetary Only account, which will allow you to post data into budget ledgers, but not actual ledgers for that account.

Spring Trees

I think it's also important to mention Spring Trees, because they are similar to winter trees in several ways (and this is something I got wrong in my answer on ITTOOLBOX). Strictly speaking, though, Spring Trees are summer trees.

This is because Spring Trees do have both leaves and nodes. However, in a Spring Tree, the Leaf and the Node attributes are stored in the same table and use the same field.

This is a relatively curious, because a winter tree would accomplish exactly the same thing, especially since the aggregate accounts (i.e. the ones referenced on nodes) are not duplicated on the leaves. I think the main reason for this is that the tree nodes themselves drive the commitment control rules for determining whether you have budget or not, whereas the leaves are the level at which the data is posted. However, it looks like I need to do a bit more research on this.

Labels: Tree_Manager

Upon reflection, I decided it was important to give credit to many of the folks that were involved in tree manager other than the heads of the teams involed in its creation, development, enhancement.

Based on my experience with the product, here are several of the folks who deserve credit other than Dana Quitsulnd, John Malatesta, and Baer Tierkel.

• Jack Whitney - From my understanding, Jack was involved in much of the early development of Tree Manager, and from personal experience, he was able to help me through several customer issues when I was consulting on it. As far as I know, Jack's still at Oracle.
• Cregg Lund - Again from my understanding, Cregg was one of the folks who was responsible for making tree manager work for both HR and Financials. He also was one of the first people to recognize that the use of tree manager was moving beyond the limitations of the original code that was developed (and that tree manager needed a major re-architecture back in PeopleTools 3). As far as I know, Cregg is an independent consultant doing PeopleSoft work.
• Nathan Vause - Nathan was what one would call the utility innfielder for the reporting development team (he was involved an practically every early reporting inititiative at PeopleSoft, tree manager included. He is one of those geniuses who can develop practically anything (and was Dana Quitslund's right-hand man in the early development stages of nVision). I believe Nathan is at SalesForce.com (as one of the many PeopleTools developers who left to go there).
• Kathy Fong - Kathy was one of the first developers tasked with fixing many of the issues created by the addition of tree branches to the product. At the time that I joined the tools team, she was diligently working through the customer issues and addressing them.
• Cynthia Share - Although I hired Cindy to work on Crystal's integration with Crystal, I recognized that the issues with tree manager required additional resources to address. I ended up moving Cindy to work with Kathy on re-architecting much of tree manager, which was a great fit because the two of them worked extremely well together and Cindy's experience as a developer with Primavera was perfect for what we needed to do with Tree Manager (albeit much later than Cregg Lund's original set of requests). This also paved the way for me to hire Brian Sparling to complete the Crystal integration (in case you don't know who Brian is, check out the following link. Cindy is still at Oracle working in the Tools Team.
• Brian Thill - I think I mentioned Brian in my original posting. Originally, Brian was hired at PeopleSoft to do the Financial Services work in EPM, but we were lucky enough to get him to do the Tree Manager port to the web from the windows client (PSTED.EXE). He then moved to do the same thing for PS/Query. He was the visionary for Data Objects, which Rick Bergquist demonstrated in the 2004 PeopleSoft Users Conference. Eventually Brian took over the whole PeopleSoft Internet Architecture development. Brian is an independent consultant working in the Portland area on different business intellige nce initiatives.
• Mikhail Dumay - Mikhail came to PeopleSoft with the acquisition of Intrepdid Software (there's a history of ingenious web developers from russia in the PeopleTools team). Mikhail took Brian's development one step further with drag and drop support. He also did much of the prototyping of the release 9 version of nVision that never made it out the door. Mikhail is still working in the PeopleTools team at Oracle.

I'm sure there are several other folks that I'm missing in this post and I'll apologize for missing them here (although I'll do my best to give them credit when I'm made aware of it).

Labels: History, Tree_Manager

The inspiration for this posting came while Chris and I were performing a technology review (actually, when we were at dinner with the customer after a long day). Here we were at one of the oldest restaraunts in the USA (which was actually a pub... quite fitting, we thought, since anybody who really knows us also knows that we really like pubs). Anyway, over a couple of beers the customer wanted to know where trees came from, etc. and was fascinated as I spun my tale.

Believe it or not, I started this post almost 2 months ago. However, I felt it important to check my facts with Dana Quitslund (the mastermind behind nVision). It also gave me a great excuse to pick up the phone and catch up with one of my heros (Dana's probably reading this right now, thinking, "Geez, why did he have to go on and say that?"

A Tree is Born

Okay. So, here is where we answer the question: "Which came first, the Ledger or the Tree". Believe it or not, I've had quite a few arguments with folks on the answer to this, because of how well trees are embedded into several of the PeopleSoft application suites. So... Place your bets... The answer is coming up...

Trees were invented for the initial version of General Ledger primarily to support nVision. The vision behind initial vision for Tree Manager came from the following folks:

• Mike Kaiser. He was the person in charge of creating the initial version of Financials, coming from Walker Interactive, where he was instrumental in the architecture of its GL.
• Dana Quitslund. The first developer that Mike Hired for GL. In addition to writing much of the early COBOL in GL, he also created nVision.
• Dan Bulos (AKA "Dan the tree man"). Brother-in-law of long-time PeopleTools developer Linda T.
• John Malatesta. Head of PeopleTools, and architect behind the toolset used by Dave Dufflield's new company, Workday.

PeopleTools 2

So, now you know the players and the reasons behind its creation. I think it makes sense to go on to discuss some the process that went into its creation. You see, even though tree manager was created to support nVision and the first release of financials, it was developed by the PeopleTools group so that all products could take advantage of it (for those interested in the timeline, this was PeopleTools 2, which was the release that Financials 1 shipped on).

Anyway, when John and Dan picked it up, they had the vision that trees should be applicable to HR as well as GL. As Dana put it, John Malatesta wanted to make sure that the initial version of tree manager didn't make HR look bad from a functionality perspective. What did this mean? It meant that winter tree support was included in the first version of tree manager, so that department hierarchies worked properly in HR.

One other concern they had was about performance, which was why Dana Quitslund came up with the tree numbering scheme that allows a single SQL statement to retrieve all of the descendents of a tree node (something that is relatively unique to tree manager).

Therefore, the tools guys developed tree manager in PeopleTools 2, and Dana and Mike used it to create nVision as part of the Financials application (note that in PeopleTools 3, query support was added to nVision, and nVision because a PeopleTool in its own right).

One other interesting note is that the tree selector funcitonality referenced in this blog entry came out of two of the first customers using trees and nVision (actually, I think they were the first because Dana spent a lot of time ironing out the last details of the product). These customers were DB2 shops, which had a quirk in optimizer that affected this design.

After putting together the proof of concept at Andersen Consulting for adding encumberance accounting to GL to create a public sector version of financials, my first involvement in trees and nVision was at one of these initial customers in New York (yes, this is the closest I can get to being involved in the initial creation of it... of course, that also explains why I'm writing a blog entry on it from my bedroom and not sitting on a beach somewhere sipping margaritas).

PeopleTools 3

Tree manager essentially stayed the same in PeopleTools 3. Because the PeopleTools group was project focused, the developers who worked on tree manager in PeopleTools 2 were working on new initiatives in PeopleTools 3 (not the least of which was creating PS/Query and adding support for Crystal Reports).

PeopleTools 5

Between PeopleTools 3 and PeopleTools 5 the PeopleTools group went through a radical shift (for those wondering, there was no PeopleTools 4, even though there was an HRMS 4). Dave Duffield asked John Malatesta and Ken Morris to come up with a radically new toolset for the next generation of business applications (code-named Sonoma). This meant that they were no longer working on PeopleTools.

In the meantime, Baer Tierkel stepped up to become the VP of PeopleTools development and adopted a product-focused organization (in other words, Developers and Development Managers owned their products from release to release, ensuring that there is a consistent investment and strategy for those product). I personally liked the change, because prior to that point, I found it very difficult to get issues looked into and resolved because somebody had to be pulled off of other work to look into it.

So, here we are in PeopleTools 5. Baer Tierkel is running the tools team. Dana Quitslund has a team of folks dedicated to the reporting tools. For the reporting team, there were a couple of key features in PeopleTools 5:

• Adding branching to trees (which allows multiple people to own parts of the tree and also allows them to work on the different parts at the same time).
• Adding Lotus 1-2-3 support (keep in mind that PeopleTools 5 was in 1994-1995, when Excel still hadn't won the spreadsheet wars... Not supporting Lotus was a big issue for a lot of very large PeopleSoft customers).

From a market perspective, the most important PeopleTools 5 feature in the reporting area was 1-2-3 support (there was revenue recognition issues as well as customer satisfaction issues). This was big, important, stuff to a small software company, like PeopleSoft was at the time. Unfortunately, due to the timing of the release and the feature set available in 1-2-3 at the time, we ended up burning through a lot of development cycles without getting the support for that product stable enough to release. A side effect of that was that the branching functionality was more complex than initially anticipated, and it introduced some issues with trees.

PeopleTools 6

After a gruelling release in PeopleTools 5, Dana decided that he preferred being the "nVision guy" and let somebody else deal with all the other things that are part of managing a portfolio of products through the release cycle. His true love was working with customers and figuring out cool ways to solve business problems and really wanted to get back into that. This was how I had the opportunity of a lifetime (and was chosen to do this by one of my biggest heros).

Anyway, here it is. PeopleTools 6. The development team is still focused on cleaning up many of the issues still lingering from PeopleTools 5. For tree manager, that meant agressively focusing on re-working many of the internals of it. There were way too many circumstances where a customer could corrupt their trees (without a good means of fixing them). Therefore PeopleTools 6 was the clean-up release for it.

PeopleTools 7.

For PeopleSoft, PeopleTools 7 was the 3-tier release (no web client yet, but 3-tier access through an application server). We did a lot of work to ensure that the tree internals would run properly with the new infrastructure changes supporting the app server.

Probably the biggest change with trees was adding the tree performance options to them (initially for nVision performance, but later adopted more widely). This was done in PeopleTools 7 and then later backported to PeopleTools 5 and 6.

PeopleTools 7.5.

In PeopleTools 7.5, we re-worked the user interface to use MFC and right-click menus. As part of this, we also did a lot of internal work that ended up improving the stability of tree manager.

PeopleTools 8.

So. This brings us to late 1998. PeopleTools 8. We started scoping the release to make it a brand-new web-based tool, right? WRONG!

PeopleTools 8.0 was initially the EPM release with a lot of other infrastructure things rolled in. Here is what the my scope list looked like in late 1998:

After completing over 80% of what was listed above, we then realized that we needed to move to the web. This initially meant that there was a browser version of the windows client and not the other client tools, such as tree manager, query, and nVision.

PeopleTools 8.14 and 8.4

It took us a while, but we finally created a web version of tree manager. This was a very large and involved project, because we didn't want to make the same mistakes with moving tree manager to the web as we had with PS/Query. This meant that we had to spend a lot more time prototyping different user interfaces (especially with respect to how to take actions on tree nodes). Because AJAX hadn't been invented yet, we were relatively limited in how we could approach the task. We ended up with the icon representation of actions that one uses today.

In addition to re-working the user experience, we also needed to rework the back-end. You see, the original code was written with the user interface code tied pretty tightly to the backend code. This meant that there weren't good services to use when splitting things up between the UI rendering and the backend processing. Because there were a lot of things people wanted to do with trees outside of merely using the user interface, we made the design decision to build a full set of PeopleCode objects to work with trees and then build the user interface in PeopleTools/PeopleCode using those classes/services. Again, this required a lot of development effort.

The end-result is not just the PIA tree manager, but a whole set of new tools to use and work with trees:

• Tree Manager - UI for maintaining trees.
• Tree Viewer - Read-only UI for seeing what's in a tree.
• Tree Viewer Component - Widget that can be embedded into a page to use a tree to pick a value.
• Tree Mover (aka Tree Import and Tree Export). Tool initially created for EPM to allow migration of trees to the warehouse from a source system.
• Tree Audit/Repair Utility. Tool that checks the health of your trees and fixes some common problems.

Fusion

Charles Phillips and Steve Miranda have stated on numerous occasions that Trees will be brought forward from PeopleSoft to Fusion. Although some of the designs details are still being hammered out, it is our understanding that the tree definition itself will be very similar to what you see today (although they seem to be taking my wish list of changes that I never got to do and incorporating them). If you want to talk about this over a beer, I'd be happy to provide my list and we can see in a few years which ones were adopted.

Grey Sparling Tree Enhancements

Since it's on topic, I thought I'd mention where we are with the product we're working on. We're wrapping up development on it and are in the process of packaging it up (which also means creating trial version of it).

Our good friend, Bert Laws, suggested that we finally solve the problem of exporting trees to BI tools. Much of the code we've written for the tree product we've got can be leveraged for this, so we'll be working on that within the next few weeks. If there's a customer trapped on an old version of Cognos or Essbase with Cube Manager (or if you want to get trees into another BI tool and don't want to buy EPM), contact us at info@greysparling.com

Labels: History, Tree_Manager

Many PeopleSoft Customers literally have forests of trees that they need to maintain to support their various needs. Many times these trees are needed to support several different reporting structures over their data. Keeping these trees up to date over time can be a very expensive and time consuming process.

For example, insurance companies are required by law to maintain two different account structures: one for standard GAAP reporting and one for the statuatory reporting required by licensing entities. This means that at a minimum, two trees have to be updated each time a new account is added to the system.

Tree Maintenance Snapon

Over the past few months, we've had several companies ask us to help them reduce the time and effort needed to maintain their trees as well as reducing the inaccuracies that occur in their reports because of tree maintenance errors. We're just wrapping up development on a product meeting these needs, the Grey Sparling Tree Maintenance Snapon

Intriguing... So what does it do?

Great question! This product helps organizations better keep their trees up to date through the following:

• By identifying all the trees that should contain a given value and whether those trees actually contain them
• By helping users to pick where the values should be added to the tree and using some ingenious logic to try to apply the selection to multiple trees
• By exposing this functionality at the point where the valid values supporting a tree are maintained
• And, by allowing the same functionality to be leveraged after the fact, by enabling a user to add values to trees by generating a list of trees that should contain a value (and doesn't) after the fact

Here's a brochure that discusses it.

If you're interested in seeing how the product works, we've put together a short demo of it in action.

Okay... We've finally put up the product pages as well... Here's where you can learn more about it.

Labels: 2007, Products, Tree_Manager

Over the past 24 hours, I've had lots of folks wanting to learn more about the HCM reporting examples in yesterday's post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).

In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.

Click here to watch the HCM Reporting in action...

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager

One of the great things about presenting at conferences, is the great questions that come up afterward. I got an email today that prompted this posting (and quite frankly, I'm surprised I haven't written an entry on this previously).

The issue that this customer is having is that they are getting inconsistent results in their nVision reporting. Whenever I hear about this, the first thing that comes to mind is problems with the tree selector tables. For a dissertation on how tree selector tables fit into how trees work, feel free to read the following post.

Problems? What do you mean?
Good question. For those who weren't able to read through the posting above, let me summarize the default behavior when running an nVision report with trees.

When nVision runs a report, it identifies all the pieces of tree criteria and determines how to put it in a staging table (the ever-famous tree selector tables). In order to minimize the impact to the system, it keeps track of the PeopleTools version number of the criteria in the tables and compares it to the version in the tree. If it determines that the selectors are out of date compared to the tree, it refreshes the value at report run time. This behavior is called "static selectors", when looking at the tree performance options (which is the default).

This is where the train can come off the tracks. Because there isn't centralized management of the trees and tree selectors (each process does its own work), it is possible for processes to accidentally cause undesired behavior (such as refreshing a tree selector table that another report is still using). There have also been bugs related to updating version numbers of trees that have also caused issues with nVision reporting.

When I was managing the reporting team, I remember having my developers spend a lot of time working on addressing these issues (which we had successfully accomplished). However, one of the hard realities of software is that regressions can often occur.

So, tell me about the Workaround, already!
The workaround is actually an artifact of the tree preformance options we put into PeopleTools 6 (actually Tools 7, backporting it to release 6). This performance option is the "Dynamic Selector". The dynamic selector gives each report its own set of values in the tree selector staging table. This means that regardless of whether a report has already used the current version of a tree node, nVision will pull the data from the tree at the beginning of the process. This takes out any of the versioning issues or the potential issues with multiple reports changing dependent values in the tree selector tables.

Therefore, if you're having problems with inconsistent results, switch the performance options to "Dynamic Selectors" and see if that fixes them (this recommendation helped many PeopleSoft customer meet their closing needs while we frantically worked on fixing issues).

I don't get it... Why put in Dynamic Selectors as a performance option??
Ah... Now the true geek in you has come out. This option was created for performance tuning for two purposes:

1. To allow equality joins between tree tables and data tables.
2. To give a DBA more control over the state of a table when they run statistics.

Let's start by talking about the equality joins. For the most part, equality joins are critical for a good performing join (and range joins, such as ones where you use a between clause, generally cause poorer performance). In order to do an equality join, every specific detail value must be put in a selector table and matched to a node. This means that all department values that fall under a given node need to be staged (which is retrieved from the "user" table... i.e. not a PeopleTools table). Because the list of detail values is an application data table, it is not versioned. That means that the version number of the tree does not know whether there are new values in the tree detail table. Therefore, the only way to ensure that you have the right values is to re-load the selector table each time it needs to be used.

Now, let's look at its implications related to statistics. Statistics tells the database the make-up of a table, and it is the data used by the database to pick how to access tables and join them together. With static selectors, the tree selector tables are generally full of data. With dynamic selectors, the tree selectors are generally empty of data, because the processes clean up after themselves. Although it seems that static selectors will give better statistics, your DBA can tweak the system for certain reports by seeding the tree selector table with data and using dynamic selectors to ensure the seeded data drives the optimizer.

Labels: nVision, Tree_Manager

One of the cool features added in PeopleTools 8.4 was drag and drop in tree manager. Yesterday, as we were installing Report Explorer at another customer, they mentioned that recently drag and drop stopped working in their environment. Interestingly, they had been dealing with that issue for quite some time although the solution was relatively simple.

Drag and Drop in Tree Manager... What's that?
Drag and drop allows you to move tree nodes around within a tree without requiring you to cut and paste them. This makes the product much more usable, when you are resequencing nodes or moving subtrees from one location to another. For companies that perform reorganizations on a regular basis, this feature is a life saver.



Okay, so why did it break all of a sudden?
Good question. The drag and drop features in Tree Manager are enabled through some javascript embedded in the Tree Manager page. In almost all the circumstances where drag and drop stops working, it has to do with the security settings in Internet Explorer.

Mike Friedman put together a very good posting on the IEBlog that explains how Internet Explorer applies and manages security related to JavaScript and other embedded objects that could be used to perform malicious activities on a machine.

Great... How do I fix it?
Fortunately, the fix is relatively simple. You merely need to make your PeopleSoft URI a trusted site in Internet explorer. In the default IE configuration, JavaScript will execute on a trusted site.

Here's how you do it. First, you go into Internet Explorer and use the Tools menu and select Internet Options.



You will then be displayed the Internet Options dialog. Click on the Security tab to modify the security settings.



Finally, click on the Trusted Sites button and enter your PeopleSoft site into it. You can use wildcards in the URL, if you like.



Drag and Drop should start working if you put in a valid address.

Labels: Security, Tree_Manager, User

After reviewing my last posting, I realized that although I covered the specifics of style definitions and nVision, I missed a critical set of information. You see, there are limitations in Excel as well as nVision that can easily be worked around if you understand the following:

The posting and formatting process nVision follows
Because nVision uses excel for storing results and for formatting, a basic understanding of the steps that are followed, and excel behavior will give you some insight as to the hooks available for formatting outside of the nVision stylesheet functionality discussed in the previous posting.

So, in a nutshell, here are the steps followed in nVision in running a report once it has retrieved the data to be included:

Step 1: Insert rows or columns in the worksheet

It inserts the appropriate number of rows or columns for the results of the nPlosion. When it does this, it calls the excel function to do this. This is important to know, because the standard excel behavior for an insert occurs. You may be thinking "yea... so...?"

So... This means you can leverage some of the cool things that excel does on an insert with respect to data ranges and formatting:

• If you insert a row or column in the middle of a range (either a named range, or a range that is used in a formula, chart, etc), the references in the range are automatically updated. This means that you can bind a chart to nPloded data without having to write a macro!
• If you insert a row or column in the middle of two cells that have the same formatting, the new cell in the new row or column is given the formatting as those around it. This means that if you apply conditional formatting to a cell in the row or column with the nPlosion specified as well as the one above it, all nPloded rows or columns will also have the conditional formatting applied (even though styles in Excel don't support conditional formatting).

In other words, by paying attention to where nVision inserts rows or columns, you have additional control over the behavior of the results.

Step 2: Paste the data into the worksheet

This is done using some bulk APIs available in excel. Because it only pastes the values (and formulas for formula nPlosion), it does not override the formatting.

Step 3: Apply the styles

I'm not sure whether this is done prior to pasting the data, but it actually doesn't matter because the paste data step doesn't affect the formatting of it. The important point is that the default behavior of excel for inserting a row is applied prior to the step of applying the appropriate nVision styles .

One additional point is that excel styles are not all or nothing from a formatting perspective. In other words, you can define a style to only include the cell color and nothing else. This gives you a lot of flexibility, and also allows you to retain formatting in the original layout as needed. Because of this, you can think of the formatting as being applied in layers (where you can override part of what comes from a lower layter).

Putting this into practice

Although there are a couple of examples above as to how to put it into practice, I believe the following example does a pretty good job of putting it all together.

Imagine that you have a report that shows payroll costs, FTEs, and Percent over/under budget as three different colums. It nPlodes the total amount for a general manager into the department detail. Notice that the number formatting for each column should be different: currency for column 1, a number with no decimals for column 2, and percentage for column 3.

Because the nVision stylesheets do not support multiple amount styles for row nPlosion, the number formatting needs to come from the report layout, where the columns are created. Therefore, the report developer would need to do the following:

• Apply the number formatting for the different columns as needed in the nVision layout(one for dollars, one for percentage, etc.)
• Modify the amount styles (the ones that end in "A") to exclude number formatting. You may want to save off a new stylesheet workbook for use in this type of report in the future.
• When you run the reports, the number formatting won't be applied from the styles, while the other formatting, such as font size, border, and pattern will be applied.

Labels: nVision, Tree_Manager

So, you've created this really cool nVision report that gets all the data you want, but you can't for the life of you figure out how to get it to format the way you want. You've gone through all the documentation, and have reached the point where you're wondering where you can get a lamb to sacrifice to the gods of nVision. In order to save countless sheep out there, I've tried to describe how it works and what you should do.

In the beginning....

Originally, formatting in nVision was pretty straightforward. You would format the cells you wanted, and the formatting of the contents cells were used for any rows or columns inserted through nPlosion. Because the options for nPlosion were limited, there wasn't much need to deal with it.

Along came tree nPlosion...

Then, we added the ability to nPlode multiple levels in a tree. This was great, because there was less hard-coding needed in reports, but it added an issue of how to format the different levels different ways. We needed to create an infrastructure to support this.

This infrastructure was created to allow formatting to be applied to rows or columns that nVision inserts due to the nPlosion feature (which shows supporting detail dynamically). It pulls together multiple elements in an effort to simplify the effort for applying these formatting rules.

• It understands the inherent structure of an nVision report, where there are


• Headings that precede a set of children rows or columns and label them.


• Labels that are used to describe rows or columns.


• Numeric amounts.


• Base formatting for rows or columns inserted, where the cells are not populated by nVision.


• It understands that results are generated at multiple levels of granularity, and that the different levels need specialized formatting rules.


• It leverages the formatting functionality delivered in excel, and allows the rules to be applied in combination with each other using Excel styles:


• Number formatting


• Indentation and other alignment features


• Font formatting, including size, name, and emphasis


• Outline formatting, such as that used to designate a total line.


• Background color.

In addition, an understanding of the way that nVision inserts rows and applies formatting allows customers additional flexibility in applying formatting, such as conditional formatting (which is not supported in style definitions in excel). Conditional formatting allows the data in the report to control the format (such as highlighting a row of data that falls outside an acceptable threshold).

It’s important to note that nVision styles are not applied to rows or columns that existed in the layout prior to running the report. They are only applied to rows inserted due to nPlosion.

More on nVision Stylesheets

In order to support defining different formatting for rows, columns, different levels of aggregation, and different types of fields in a report, nVision has implicit knowledge of specific style names in excel that have different meanings. In other words, when it comes time to apply styles to an nVision report, it looks for excels styles with specific names and applies them. This means that there are different style names for these different attributes.

Therefore, Excel styles that nVision utilizes has the following naming convention:

First Character:

R (for row), or C (for Column)

Second and Third Characters:

The level number (00 = detail, 01 = first level, 02 = second level)

Fourth and Fifth Characters:

B (for Base), A (for Amount), L (for label), H (for heading)

This means that R01H is the formatting to be applied to the heading row for a row nPloded at the first level of the hierarchy.

In order to simplify the maintenance of these styles, spreadsheets are delivered with the styles populated and displayed. Customers can maintain the styles within these worksheets and then click a button to apply (or copy) the styles to a given nVision layout. After the style names have been copied to the layout, they can be modified directly in the layout without affecting other reports.

Labels: nVision, Tree_Manager

Here's a neat tip that I wish I figured out when I was responsible for tree manager. Had that occurred, it would have been part of the product, versus a blog tip (oh well... you can't have everything).

I had a little help in figuring out this approach from one of our customers as we discussed the need to document the values in the chart of accounts for business users (using trees).

Chart of account listings
In order to make it easier for business users to understand the structure of the chart of accounts, especially when looking at individual chartfields, many customers have need to generate highly formatted reports, incorporating the trees. One of the major complaints about tree manager is the limited printing functionality available in it (and I can remember many long meetings trying to figure out a quick and easy way to deliver printing for large trees without writing a completely new tool to generate it).

Here is a quick synopsis of the major limitations of printing in tree manager.

• It cannot be run easily by users unless they manually open it up in tree manager.
• The results are generated in HTML in the browser, which means that the results have limited use and cannot be saved easily for future use (not to mention that printing large trees is a significant issue from the browser)
• It does not show the set of detail values in the system or any attributes of those values. This is critical when the audience needs the listing to document both the tree and the values represented in the tree (versus just documenting the tree itself).

With nVision, tree nPlosion, and nVision styles, it's possible to do all this without programming. A side benefit is that the resulting report could be used to drill into other detail (for example, one could look at the project report which shows all projects and activities hierarchically and drill into the journals posted to a given category of projects... Pretty Cool, right?)

Creating a tree/value listing report can be accomplished using the following steps:

• Build a query against the detail value table that the tree was built on. This query is merely to get the "facts" into the nVision report (much of the content of the report will come from the fact that you will be nPloding using a tree in it). As such, all it needs is the key and an aggregate field. Using Project as an example, the query would have the following:
  
  • The key field by which the tree would be linked. In this example, it would be Project ID.
  
  
  • An aggregate field that caused a count of the number of rows (values) to be displayed. PS/nVision requires this for matrix reports.
  
  
• Built a matrix layout in nVision for the report. Because the nPlosion rules cause the tree/dimension to be linked into the report along with all fields available in the tree/dimension and detail values, much of the content of the report will be the nPlosion and labels of values in the tree and detail value table.
• nPloded the rows on the appropriate tree, using full tree nPlosion to detail values.


• Set the source of the sheet to be the query that was created.


• Labeled the first column to show the tree node ID and the project id


• Labeled the second column to show the tree node description and the project description.


• Labeled the third column to show the project manager.


• Labeled the fourth column to pick the count field from the query as the source
• Applied the stylesheet to the report in nVision.

This report can now be generated through nVision and distributed to the users using standard reporting tools.

Labels: nVision, Tree_Manager

There was another set of requirements that were important to the customer in my conference call yesterday. That was hierarchical reporting in nVision. The customer's initial question was, "Can you do it, and how?" Obviously, the answer to the first part was "yes" (otherwise, I wouldn't be writing this posting). However, there are actually 3 different answers to the second part, depending on what you mean by it.

So, let's frame the discussion by listing out the 3 different things one could be trying to do with hierarchical reporting.

1. Generate reports at different levels of detail. The example that comes up most often is generating a profit and loss for each level of an organization.


2. Generate a report with a hierarchy used within as the structure. The example that comes up most often here is using the account tree to generate the details and subtotals in a profit and loss statement.


3. Look at a report and drill using a hierarchy into a number in that report. An example of this is looking at the payroll number in a profit and loss statement and drill into the department tree to see how that is broken down (and repetitively drill to get additional levels of detail).

So, let's look at each one to see how you would accomplish it with nVision.

Generating reports at different levels of detal.
This is a standard feature of nVision, using the "scope" object. When running a report, you can tell nVision to generate instances using a scope. The scope allows for instances to be created based on values in the system or nodes on a tree. One little known feature of the scope is that you can pick multiple levels of the tree in the same scope definition, and it will process all of them. This works very well when a customer runs the nVision reports on a pre-defined schedule and wants to get a "package" of reports pre-generated at all levels of granularity.

If you want to do this in a more ad-hoc manner (in other words, allow the user to run a report for "his" part of the hierarchy), this is a little more cumbersome. One approach is to use row level security to eliminate the data, but this is done at the database level and the reporting tool actually doesn't know that data was eliminated (so there is no way to show in the report what was included or pulled out other than looking at the detailed data).

Our PeopleSoft Solution Extender for nVision provides an ad-hoc solution that does address these needs. We've created a new report request page for nVision that allows the user to specify what nodes to use to filter the report without requiring the scope to be involved. This will allow the user to pick which part of the hierarchy to use for the data in the report at the point they want to run the report.

Generate a report with the hierarchy used within as the structure
This is another standard feature of nVision that we added in PeopleTools 8 (called tree nPlosion). When designing the report, you can nPlode on a tree, and pick to nPlode through the tree. Because nPlosion will be inserting rows for the different levels of the tree, formatting is important, so we also added a robust style and formatting set of features that allows control over the font, color, indentation, etc. for each level of the hierarchy.

This works well for most situations. However, there are a couple of limitations in the implementation. The most striking of which is that every amount field in the results must use the same style (which is an issue in excel, where the number formatting is part of the style). This means that Dollars, Percentages, Headcount, and Square Feet all have the same number format applied (not desirable). A simple workaround is to use an instance hook to apply the appropriate formatting. However, we've also addressed this in the PeopleSoft Solutions Extender for nVision.

Looking at a report and drill using a hierarchy into a number in that report.
Again, this is a standard feature in nVision (and meeting this requirement was considered as part of the design for the tree nPlosion functionality added in release 8). The way to accomplish this is to build a drilldown layout that has the nPlosion options set to drill to the next level of a tree.

One common mistake that people make when developing drilldown layouts is to qualify them too much (that's why in your drilldown layout, you should pick the root node of the tree for filtering and then pick "next level" as your nPlosion option... If you're already drilling from a lower node of the tree, it will override the criteria in the drilldown layout).

To use the layout, you merely pick the number you want to drill from, and then pick the layout to go to the next level (if it's the first time, then you will probably be starting from the root of the tree). If you want to see the next level from your drilldown results, you drill from the number you want to see more detail from, and then re-pick the "to next level" drilldown layout. Here is an example layout for the department tree.

Another option is to create one layout that nPlodes through the whole tree. This will show in one step, all levels in the tree and the associated breakouts of supporting detail from the starting value. This is an important technique when you want to see organizational responsibility for a balance or expense item. For example, if you want to see the sales contribution from your profit and loss through your sales organization, you can develop a drilldown layout that nPlodes to all levels of the organization tree and use it to drill from the sales number on the profit and loss statement. It will then show you all levels of the organization tree, the contribution to that sales number (which is important from a Sarbanes Oxley perspective).

Labels: nVision, Tree_Manager

I've talked with many PeopleSoft customer who have needed to use PeopleSoft trees outside of nVision, Query, or Cube Manager. One of the services I had pushed for was to add this functionality into PeopleCode (especially app engine).

Doesn't Tree Manager already have an API?
Sort of. Although there is an API available in PeopleCode for trees (called the tree classes), these APIs are focused on describing and maintaining trees (essentially tree manager functions). However, as any good BI person will tell you, it's not the act of maintaining a dimension that's important... it's what you can do with the dimension when you join it to other stuff.

So, what sort of things would you want to do?
The things you would want to do is to filter data with a tree or subtotal data using a tree as the grouping mechanism. The former is available to both nVision and PS/Query as either Filtering criteria or "in tree" criteria. The latter is only available in nVision through nPlosion. However, there are lots of things outside of nVision or Query that you would want to do.

• Aggregate data using the tree. Those familiar with Summary ledgers in GL understand the concept. You can aggregate data using a tree for other purpose, such as improving performance for downstream processes. For example, if you want to display a count of reported problems by product family for all product families, you're doing this type of aggregation at the product family level of the tree.
• Filter data using the tree. This is generalizing the "in tree" criteria in PS/Query. A couple of good examples of this are the batch programs used in GL for selecting open receivables to restate, or for selecting what data to use in Allocations. Another example of where this would be valuable would be in search records, where you could find all cases for eastern region customers, that are reported against any product in the Finanicals product line.

Today, PeopleSoft applications and customers use the technique I'm describing below to accomplish this.

First, a little more on Trees

The first thing to understand is how trees work. A tree is not a standard structure (such as a strict self-referencing table, as described in Ralf Kimball's definitive book on data warehousing), or a standard denormalized structure. It has a unique structure that allows it to do the following things:

Things that are not supported well by denormalized tables.

1. It supports unbalanced (or ragged) hierarchies.
2. It supports dynamic leveling.
3. It supports joining at any level of the hierarchy.

Things that are not supported well by a self-referencing table.

1. It supports finding all children at any level in a single SQL statement regardless of the number of intermediate levels.

So, how does it do it?

The key to it is the data model, and the fields added to it (that are maintained by tree manager). Here are the tables in the model:

• PSTREEDEFN - This identifies the tree, and the attributes of it (such as where to get application attributes of the nodes, leafs, and levels).
• PSTREENODE - This is the heart of the tree. It is a self-referencing table that also has additional attributes to facilitate non-recursive access to children of a node.
• PSTREELEAF - This maps a node to the sets of leafs or detail values that can be used with it. This can be thought of as the "leaf" level in the BI world, but that the leafs can be attached to any level of the tree, even to nodes that have children nodes.
• User tables - These are tables that store additional attributes to the node, leaf, or level, such as description, size, manager, etc. This allows a tree to be defined against any data element in the application and also opens up a whole host of opportunities for using trees in a given customer enviornment.

It's also important to understand that the tree definition is the starting place for everything, and that all the tools owned tables (i.e. not the user tables) strictly follow the key structure of the tree definition. Because trees are effective dated and utilize setid indirection, this is an important thing, because effective dating and setid indirection is only evaluated for the PSTREEDEFN table and the user tables. This logic is not used when joining between PSTREEDEFN and PSTREENODE. This means that from the perspective of effective dating, any nodes that have not changed between to tree instances are duplicated in the PSTREENODE table.

Once you've identified the tree you want to use (setid, tree name, and effdt are the unique keys), you also know the high order keys to use in the PSTREENODE table to get the nodes for that tree. Now, let's look at the structure of the PSTREENODE table:

• Setid
• Tree Name
• Effdt
• TREE_NODE
• TREE_LEVEL_NUM
• TREE_NODE_NUM
• TREE_NODE_NUM_END
• PARENT_NODE_NUM

As you can see, this table is self-referencing (the PARENT_NODE_NUM tells you the NODE_NUM of a tree node's parent). However, tree manager also maintains the TREE_NODE_NUM_END field, which is what prevents the need to do a recursive set of selects to find all the descendants of a node. Tree nodes are strictly numbered. A tree nodes' descendents will always have a node number that is between the value of its TREE_NODE_NUM and TREE_NODE_NUM_END.

Therefore, if I want to select all the descendents of the node "Assets", here is the SQL I would issue:

Select B.TREE_NODE, B.TREE_LEVEL_NUM
from PSTREENODE A, PSTREENODE B
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END

You could also filter by level number to get the descendents at a given level.

Now, to add in the leafs. For winter trees, this step is not necessary (winter trees are trees where the nodes themselves represent data points of interest, such as positions, whereas with summer trees, the nodes are the hierarchy that categorizes something else, such as most account trees or customer trees).

Because leafs are essentially mapping tables, you either join them directly to another table (either the user table to get attributes, or a fact table to get rows). However, for performance purposes, most PeopleSoft processes will stage the data values before joining it to a fact table.

Getting back to the subject at hand, here's the table structure of the PSTREELEAF table:

• Setid
• Tree Name
• EFFDT
• TREE_NODE_NUM
• RANGE_FROM
• RANGE_TO
• DYNAMIC_DTL_FLG

As you can see, the high level keys are again the same. Also, you can see that the mapping to the tree node is by node ID, and the mapping to the target table is by either individual key values or by range.

So, let's say we want to find out all account numbers and account types for all accounts under the "Asset" node of the tree. Now, there are three things that need to be looked up in order to determine what to put in the SQL:

1. The table and field where the Accounts are stored. This is stored in a tree table I haven't discussed, which is the PSTREESTRUCT table (which has a reference in the tree defninition).
2. The setid to use for the accounts. As mentioned, setid indirection occurs when going after the user tables. Depending on where your getting your business unit or setid, you will have to do the lookup separately (which is a more complex topic, but is often hard-coded at a customer anyway).
3. The effective date to use for the accounts. Again, this occurs when going after the user table. This means that you have to perform the standard correlated subquery for effective date logic. I won't go into detail on this in this posting, but you can see the SQL by building a query against the table in PS/Query.

So, your SQL will look like the following when after the list of accounts under a tree node:

Select C.ACCOUNT, C.ACCOUNT_TYPE
from PSTREENODE A, PSTREELEAF B, GL_ACCOUNT_TBL
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END
and C.ACCOUNT between B.RANGE_FROM and B.RANGE_TO

As you can see, this SQL statement didn't have to look at any children nodes at all! Because the leafs are attached using node numbers, and the range on the tree node record identifies all the node numbers of all its children, you could join the leaf directly to the node.

Now, it's important to note that this SQL will not perform in DB2, because indexes are not used when joining two fields of different size (which is why nVision has 30 different tables for staging the join between the tree leaf and the data table).

Staging the join

As mentioned previously, when joining to a fact or data table, one generally stages the join into an intermediate table. nVision and all other PeopleSoft delivered processes use selector tables (in nVision it's PSTREESELECT##, where the ## represents the field size of the field joined to. In nVision, there's a whole subsystem dedicated to managing what goes into the PSTREESELECT tables. For the purposes of this discussion, you can merely create your own selector tables that identify what you are putting in there, and the set of values that it represents. Let's assume, for example, you have a table called "PS_ACCOUNT_TREE_FLAT" to represent the flattened account tree. The SQL to load the data related to the "Assets" node might look as follows:

Insert into PS_ACCOUNT_TREE_FLAT as
Select "Assets", B.RANGE_FROM, B.RANGE_TO
from PSTREENODE A, PSTREELEAF B
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END

You could then join to the ledger table as follows:

Select Sum(B.POSTED_TOTAL_AMT)
from PS_ACCOUNT_TREE_FLAT A, PS_LEDGER B
where A.FILTERED_NODE = "Assets"
and B.ACCOUNT between A.RANGE_FROM and A.RANGE_TO

Hopefully, this gives a starting point to determining how to approach this. I suggest playing around with a SQL editor and PS/Query to make sure you understand (I did the same thing with nVision when I was first learning). Keep in mind, however, that the SQL that gets displayed in either an nVision trace or the SQL tab in Query does not include the SQL for populating that tree select table. However, it does show the filtering conditions used for joining the select table to the data table. You can select directly against the tree select table to see what values were put in it, and then look at the PSTREE% tables to compare the two.

Follow-on Info

For those who use tree manager extensively, we recently added a product that makes it easier to maintain multiple trees together. Additional information can be found here. A demo of this product can be found here.

Labels: PeopleSoft, Tree_Manager

It's surprising to me how few people know how to effectively tune nVision. Even from within PeopleSoft, there didn't seem to be many people who could do it. One side benefit of knowing the "secret handshake" was that I got a free trip to Paris to help Carrefour.

Over the years, we added new features to the product to make it easier to tune nVision, and believe it or not, it's easier to tune nVision now than it ever has been in the past. Hopefully this weblog entry will help you understand how to approach it.

Why is it so challenging?
Well, actually, it's challenging to tune almost any product that does what nVision does. Because data is stored at a very granular level, tools like nVision need to aggregate the data to get results. In addition, the flexibility for using trees in nVision can make it more challenging to tune than a star schema in a data warehouse (but the trade-off with a warehouse is that you are more limited in what you can do with it, believe it or not).

In many ways, I believe that the biggest challenge is more cultural than anything else. Because nVision makes it extremely easy to create "analytical" reports and view data in different ways, many people don't expect that they have to manage it as closely as they have to manage a data warehouse (although there are may data warehousing techniques not used by nVision that would dramatically improve performance).

How to approach tuning
The performance of nVision is driven by two different aspects of how data is stored and how it accesses it. These are:

1. How easy it is to find the rows of data it needs to report on.
2. How many rows have to be aggregated to get a number on a report.

Let's talk a little bit about each of these.

Finding rows

The first topic is the first place you should look when analyzing performance.

nVision accesses data very much in the same manner that a ROLAP tool does. There is a centralized fact table (often the ledger), with many different "dimensions" (or chartfields) that are used to group and filter the results. There are two key differences between the ledger tables in PeopleSoft and a well-designed star schema. The first is that in a star shema, the dimension tables are always joined to the fact table to minimize the impact of indexing the fact table. In nVision, dimension tables are only joined when needed for filtering and grouping the data, which means that the focus needs to be on indexing the fact (or ledger) table instead of the dimension tables.

Another difference is that nVision does not use a star or snowflake schema in the dimensions. It uses a proprietary structure called a tree, which provides much more flexibility in how you model hierarchies than a star schema would support. It also allows many different hierarchies to be used interchangably with the fact table (which also is a big bonus). However, this also means that the rules for flattening the tree and generating SQL needs to be managed closely.

Therefore every organization needs to look at two things when managing the performance of nVision: indexing (especially on the fact table), and the tree performance options (which drive how the SQL is generated by nVision).

The most successful organizations are ones who take a methodical approach to both of these (this is how one organization is able to generate around 200,000 nVision instances each month on a 4-way NT box running nVision).

When looking at indexing, it is important to look at which fields are constrained and grouped on the most (and analyzing the cardinality or number of values of each). Quite often, putting the accounting period as the first field in the index is the best place to start, because there are at least 12 values, and they are always used to constrain data. Account is often the second field, because you almost always constrain against the account field (otherwise you would get meaningless financial statements).

When looking at tree performance options, the best approach is to go through all the permutations of options for each tree used (focus on one tree at a time), to determine which option will work best for a given report and data set. This is the approach that the high-volume customer used. They also prioritized their reports by the ones that had the worst performance and focused on them one at a time. They also had a defined performance goal for their reporting window, so that they could claim success when meeting that goal.

Aggregating Rows

When you're confident that you've done a good job of indexing and setting performance options, the next place to look is the number of detail rows you're reporting on. Because nVision is all about summing up details to get summary values (that's what a financial statement is), you will get into performance issues merely because each number on a consolidated financial statement may represent tens of thousands of detail rows in the database (and all of them need to be aggregated to get that number).

A good way to determine the affect of this is to do a a SQL trace of your nVision report and do the following. Any SQL statements that perform a sum of a numeric field (generally on the ledger table), edit the traced SQL to change it to count instead. Run the SQL with the count in it, and each number in the results is a count of the number of rows that needed to be selected to get a number in the report. Now, depending on your performance options you may not have the other tables populated so it's a good bet to make sure that you have the report open in the nVision designer when you run this trace.

If each number on a report requires more than 100 rows, it's a good bet that a summary ledger would be required to improve performance. The trade-off you make by using a summary ledger is that you are summarizing the data in batch versus online. Therefore, if you can have many reports use the same summary ledger, you are making the most of this trade-off.

For populating the summary ledger, you can either use the GL program to do this or use a little trick that I've had many customers use --> a materialized view. In both circumstances, you'll want to populate the summary ledger definition, so the reporting tools know how to access them. However, for the materialized view, you can let the database do the aggregation for you instead of running a program to do it (much better from a performance perspective). The key, however is that a materialized view will only support aggregating by dropping a chartfield in the summary table (wherease the batch program will allow you to summarize to nodes in a tree).

More on SQL tracing in nVision

It's impportant to note that in PeopleTools 8.44, we improved the tracing so that you can easily turn it on and get the SQL when running nVision on the process scheduler server (it's an option on the process scheduler configuration). Because the trace is captured and managed with the process, you don't have to go through a long log of SQL to determine which statements are associated with which reports. Additionally, it eliminates the need to try to trace it in the design environment (which has just enough differences in how it works to make it an ineffective way to capture it and manage performance).

Not sure how understandable this is. I'm more than happy to clarify any area in a future posting that is confusing.

Labels: nVision, Performance, Tree_Manager

This topic was brought up by Bob Stambaugh in a very thought provoking presentation to the Northern California PeopleSoft RUG called "The new 21st century business environment".

Bob talked discussed what decision networks are. They are the informal way you get things done in a company. In other words, who does a person go to when they need help. Bob has done several projects where he had employees fill out questionairres to determine who were the control points of information. Who did people go to when they needed to understand a certain policy? Who did they go to when they needed to get information about an aspect of their business? ect. He posed that this network does a much better job of describing who has power in an organization than an org chart. In fact, he postulated that one could determine who was dead wood in a reduction in force by determining who wasn't part of the network (and one could also determine who was supporting the top producers by looking at it as well).

I found this extremely interesting (being the former product manager of tree manager for 9 years). Obviously, this is a much different way ot looking at things than I was used to (and also much different than the way ERP vendors build their products).

However, I also realize that the informal network may determine how things get done, but they do not represent who is accountable for things. In a day where investors and the government want to make individuals responsible for the controls and performance of a company, there is still a very important role for the org chart (as outlined in the following Blog entry). In fact, accountability is a very important area that is also missing from ERP vendors (which is an opportunity that Doctor Richard Connelly and I have discussed at length for many hours).

So, in the end, both structures need to play an increasingly important role in business applications. They need to better reflect not just how to capture transactions and secure access to the application, but they need to help organizaitons do the following:

• Assign, delegate, and manage responsibility of roles and actions.
• Help people get things done by helping guide users to the best individual in the network to get things done (and imagine using the network as a means managing the careers of individuals by identifying opportunities for training and networking).

Definitely food for thought.

Labels: Tree_Manager

Strategic Structure Alignment... pretty important sounding term (that probably doesn't tell you that much). It's something that probably every customer struggles with, but doesn't realize what it is.

Strategic Structure Alignment is all about making sure that the important organizational constructs are aligned across different applications and domains. In 90% of the circumstances, this is looking at how the HR responsibility structure maps to roles that people play in other systems. Throughout my years of experience with PeopleSoft applications, I've had many discussions with PeopleSoft customers about how the department chartfield in financials can be aligned with the HR employee structure (in fact, one of the remaining baby bells has a whole system dedicated to this, and we've put in enhancements to tree manager to help them handle distributed nature of this structure alignment).

There are other areas, however, where this can be applied:

1. Row/Role level security in reporting
2. Sarbanes Oxley compliance delegation
3. Responsibility definition in front-line applications (such as call center and sales force automation

Basically, any place where the official reporting structure is needed to be used to drive use and functionality in an application. This is different from a knowledge network, which was discussed by Bob Stambaugh at the Northern California RUG on friday.

So, how do you align strategic structures? There are several approaches:

1. Create one MASTER structure that gets replicated to other systems. This is the approach that the baby bell took. All information was maintained in the HR org tree, and then the tree was replicated to the other database for use in reporting. They, then made sure that the chartfield used by the tree had the appropriate values for the tree to link to (which could be a challenge). Another limitation is that you must put entries in the HR org tree that don't have any HR function in order to use them in financials.
2. Another option is to replicate the departments in HR to financials, but not the hierarchy itself. This allows a financial organization structure to exist that does not have to exactly match the HR one (but ensures that all departments are represented financially).
3. A third option is to synchronize the maintenance of trees between the two applications using ERI. In other words, as the user is making changes to the org tree in HR, he can drill into the appropriate tree in Financials and determine how that should be reflected there.

Because of the new requirements of Sarbanes Oxley, it is my suggestion that customers do a combination of the three approaches above. This is because the HR responsibility hierarchy will be the key to ensuring Sarbanes Oxley compliance. A good example is managing expense accounts. If you can drill down the HR responsibility hierarchy to see which people are responsibile for how much of that expense, it provides accountability and visibility into the accuracy of the financials. To accomplish this, one merely needs to provide a means for linking the HR org structure to the department chartfield in financials and drilling in nVision into a drilldown layout that uses tree nPlosion to the HR org tree.

Labels: PeopleSoft, Tree_Manager