While consulting at the same customer in NYC, we were looking at performance issues related to implementing row level security.

As with many customers who implement row level security with PeopleSoft products, there is a relatively complex join between a set of tables that map users to the data values they have access to, and finally to the data table (i.e. Ledger table in this circumstance). Because the data table has a lot of values, and because the joins to it are complex, performance is often a significant issue.

Thinking back to PeopleTools 9

One of the features planned for PeopleTools 9 was definitional row level security. We planned to provide a centralized means of defining the rules that drove how sets of data values mapped to sets of users (including support for trees). Because we were trying to minimize the administrative effort required by organizations who were implementing this type of security, the logic required to evaluate a rule could be relatively time consuming if it were executed every time a user tried to access data.

Therefore, we decided that a good trade-off was to create a security staging table that was optimized for joining to the data table. This staging table would identify all the individual values a user or user group had access to, allowing an equality join to be done to the data table. The security staging table would be populated periodically by evaulating the rules that drove the mapping of the users to the data values.

Applying the design concept here This customer had already created a set of tables and UI for administering those rules. However, in order to identify which rows of data a user should see in the ledger table, a relatively complex set of joins and unions needed to occur. As we looked at the potential performance implications of this, we recognized that evaluating the rule at the same time data is selected from the table would cause serious issues.

Because this customer was running on Oracle, we were able to use the database to create the staged table using a materialized view. Depending on the complexity of the SQL needed to get the appropriate data, the view can either be updated incrementally, or not. In this circumstance, the SQL required two unions (the first select got the data for the first chartfield, the secon select got data for an alternative chartfield, and the third got data for blank values). This meant that the refresh of the materialized view needed to occur on a pre-defined schedule or be triggered. In this circumstance, the customer created a trigger on each of the tables used in the view (OPRCLASS, the Custom mapping table, and each of the chartfields referenced).

One important note was that the materialized view did NOT include the join to the data table (i.e. LEDGER table). The Ledger reporting view was used instead. This is important, because of the following factors:

• The LEDGER table changes frequently, which means that the refresh would have to be run each time data in the ledger table is modified.
• Materializing the join between the ledger table and the security table would physically store the combination of each user and each row of data that user has access to (in other words, much of the ledger table would be replicated for each user… although disk is cheap, it’s not that cheap.)

The Grey Sparling approach to Data Security

Although this is the method used in PeopleSoft to support row level security out of the box, it does have significant drawbacks. This is why we developed our own reporting security product.

Keep in mind that the PeopleSoft approach to row level security is to hide the security rule from the application and to filter on OPRID, OPRCLASS, or ROWSECCLASS when it finds it in the record definition. This means the following:

1. The criteria used to filter the data is hidden from the report. This is a big no-no from an auditing and compliance perspective, because you cannot easily figure out whether the data was missing in a report because security was applied, or whether the data simply didn’t exist.
2. The filtering of the data is convoluted. In other words, instead of filtering the data directly against the data table, multiple joins are required to do the filtering.
3. It is not trivial to apply the security rules to multiple data tables in the application. In each circumstance, either the record definition must be modified (for PS/Query), or a view joining the data table to the security rules must be created (for PS/nVision or PS/Query).

Our solution is to provide a place where the security rule can be defined in a manner optimal for the administrator, and then apply the rule directly to the report in the reporting tool. This means that the performance from row level security is better, it is easy to see how the data in the report had been filtered, and the security is applied consistently across different ledgers or tables without requiring app designer access or DBA access to create views. The secret sauce to this is how we apply the rule to the report without allowing the person running the it to be able to modify it. If you want to find out how we do that, feel free to contact me to get an NDA, I’ll add you to the Grey Sparling SPAM engine (just kidding), and we’ll talk…

Of course, I’m sure many of you are thinking “Well, if this is so great, why didn’t this customer buy the product instead of using this technique?” The answer comes down to timing. If our product existed before they did the analysis, design, development, and testing, they may have used it. Because we were looking primarily at the performance implications of using something that was about ready to be moved to production, it made more sense to look at performance as a stand-alone item (instead of revisiting the decisions already made). It’s entirely possible that there may be a future posting about how this customer purchased and implemented the Grey Sparling Report Compliance, Security and Distribution Extender.

Labels: nVision, Security

This week, one of our blog readers asked us for advice on filtering his/her nVision reports with respect to dates.

All of you nVision experts are already thinking “timespan”, and you’d be right. However, based on the needs of this customer, the answer wasn’t quite as simple as that.

So, why do I need to span my time?

First, some background for those who aren’t quite sure about timespans. Timespans were created for General Ledger, to allow reports and other processing to more intelligently react to changes in time. Because time is managed in General Ledger by accounting period, Timespans is all about how to pick the accounting periods to use in a report or process.

Taking from the concept introduced by effective dating in other areas of PeopleSoft (where you can look at the state of a system as of any point in time and determine what was current), the people who designed timespans realized that it would be valuable to use as of dates for picking data in reports (to allow you to see what a report looked like at a point in time… for example, run the profit and loss as of 3 months ago).

Therefore, timespans are evaluated based on as of date (just as effective dates are evaluated based on an as of date). When supplying an as of date, you can then determine what period or periods can be used. It also allows you to define which part of the date is evaluated when picking the starting period or ending period in a range. This allows the creation of timespans, such as year to date, current period, current period last year.

Because timespans were built for general ledger for the sole purpose of evaluating accounting periods, timespans are tied to the calendar (which is what defines what accounting periods are), and Ledgers in General Ledger have a calendar as a property of them.

Sounds straightforward, so what are the issues?

Very good question. The issues that customers hit are related to the fact that the creators of timespans didn’t envision other uses for them.

• They are bound to General Ledger. Unless you are reporting against a Ledger, there is no way of finding a calendar to use, which means that the tool does not know how to evaluate a timespan.
• They are bound to Accounting Periods. There are many circumstances where you want to filter on dates that aren’t accounting periods.
• Month and year are the only two levels of granularity in which you can create timespans in a detail ledger. This means that quarterly timespans are problematic.

Quarter To Date Processing

So, tell me why again that quarter to date processing is problematic?

The reason is that timespans are built by determining how to pick a starting period/year and an ending period/year. Here are two examples that work well:

YTD has a starting period of 1 (or 0 if balance sheet accounts) / and year of the as-of-date, and an ending period of period of the as-of-date / and year of the as-of-date.

Current period has a starting period of the current period of the as-of-date / current year of the as-of-date and an ending period of the current period of the as-of-date / current year of the as-of-date.

Now, current quarter is much more complex to calculate, because the beginning period is 1 if the as of date is <= march 31, or 4 if the as-of-date is between April 1 and June 30, etc.

So, how do you deal with it? There are two ways I know of:

1. The summary ledger method.
2. The nVision method.

Summary Ledger method

So, let’s start with the summary ledger method. At first, this method may seem overkill. I mean, isn’t summary ledgers there for performance? Why would there be functionality there to help me?

Well, the answer to this question gets to how year to date balances are calculated in GL (which is how you always report balance sheet accounts). Instead of storing balances for each period in balance sheet accounts, PeopleSoft stores the incremental period changes. This allows the posting process to use the same logic for any type of account, but means that the reporting tool must sum all the periods to get a balance. As you can imagine, this can be pretty expensive to get a december balance. Now, imagine that you’re a bank with 365 periods (i.e. daily balances). Now you’re really talking expensive SQL.

Therefore, one of the features in summary ledgers is to roll groups of periods into summary calendars for storing. This is where the magic comes in. Simply create a summary calendar with 3 detail periods rolling into 1 summary period, and current period in the summary calender = current quarter. Also, Quarter to Date becomes the same timespan as year to date on the detail calendar. Finally, if you nPlode within the summary calendar, you can show a quarterly breakout through the current quarter.

WHOOOHOOO!!!

So, you say you don’t want to build summary ledgers?

Well, we can help you too. This is where nVision comes in. You begin by creating a timespan for each quarter (Q1, Q2, Q3, Q4), each with the specific periods in them (Q1 = period 1 – period 3 of current year, etc.). When you build the nVision report, you include columns for each of the quarters. Then, if you want to hide the columns you don’t want, you use an instance hook macro to determine which quarter you’re currently in (most likely useing the %ASD% variable), and then hiding them.

For those who missed the instancehook posting, here it is: https://appsian.wpengine.com/embedding-macros-into-nvision-reports

Filtering against non-ledger data sources

Adding timespan support to other data sources was to be a big feature in release 9 (ah, but the other Larry said it was not to be so). On the drawing board was a feature called “data objects”, which would have allowed us to map calendars to either date fields or to period fields. Timespans would have come along for the ride, and we would have been able to leverage them in searches, pages, batch programs, as well as reports. Being able to pick timespan names to filter dates for searches would have dramatically improved usability for call center alone (show me all cases for this product issue for the current day, month, quarter, year). God, it would have been beautiful!

Unfortunately, the best way to filter dates for non-ledger reporting (i.e. query-based nVision reporting), is to build it into the query. This is one of the reasons why we felt that this was an important feature in our nVision PeopleSoft Solutions Extender product.

Because you can apply other criteria and labeling outside of the data source in nVision (and have them applied across the other axis), there are a few options for deploying this way.

Option 1

The first, and simplest to deploy is to create a query for each data source for each timeframe you want to report against. In most circumstances, you’ll want to have a table that is used to track the current date (and this table will be joined to your data table for the date filtering you need). If you are using Financials, your business unit table has this field already in it. If you need period processing similar to that done with calendars in GL, you may want to add a from and to date to this table and use PeopleCode to set the values to bound your periods.

So, if you want to have a current period, current quarter, and year to date column on a report, you will probably want to create a query that gives you each and put each query in a different column. You can then apply your row nPlosion and criteria as well as your worksheet criteria to generate the other pieces of the report.

Option 2

Option 2 extends the part of option 1 that has a table for administering the current date. Because nVision allows you to apply criteria outside of the query, it is possible to use a view to pro vide a generated field with values that can be filtered on. This, however, is dependent on your database having the ability to create a view with a union in it (sorry, DB2 customers).

Basically, what you would do is create a view with a select for each timespan you want to expose (PER, QTD, YTD). In essence, instead of having multiple queries in option 1, you would have a view with the same number of selects with a column that identifies which select it is.

In nVision, you would specify criteria for that pseudo-field as row, column, or worksheet criteria. Because you are only ever getting data from one of the selects in the union, performance should not be as bad as you might think.

One final note, is that you will also have to create a valid value table for this new field, so that nVision will allow you to pick the values you need.

Labels: nVision

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