Posted by: repettas | March 5, 2008

TREENAME is Undefined, Inactive or Invalid

NV: “[TREENAME] is undefined, inactive, or invalid. (28,21)” (Master Resolution

This resolution contains several information regarding error message: “[TREENAME] is undefined, inactive, or invalid. (28,21)” in nVision.

=======================================================

 “[TREENAME] is undefined, inactive, or invalid. (28,21)”

IMPORTANT: Do not confuse the above error with “Treename is not found or is marked as invalid. (28,106)”.

 The error “[TREENAME] is undefined, inactive, or invalid. (28,21)” occurred after a user changed the DEFAULT SETID for a business unit in UTILITIES, USE, TABLESET CONTROL, TABLESET RECORD GROUP CONTROLS. 

The following information helps to explain how nVision determines what setid to use in selecting a tree. It also explains the relation to TABLESET CONTROLS and record group FS_21. 

There are two key points that you should keep in mind:

1. When the user is creating or revising a layout, we presume that the layout can be used by a number of different business units, all of which have enough in common that they can use the same report. Because we want the layout to have maximum flexibility (Can be used by business units for which the same tree references mean different things), we use a layout SETID as an indirect pointer to the trees that the prospective using business units have in common. This SETID in turn points to trees that are similar to those used by the various business units. The design-time SETID is entered on the Prompt dialog box, which is available at any time using the PROMPT button in the Layout Definition.

The layout SETID supports prompting and validation when creating or modifying the layout, and is ignored at run time.

2. The REQUESTING BUSINESS UNIT field in Report Request is the starting point in determining what SETID will be used. The system then looks at TABLESET TREE CONTROLS in UTILITIES / TABLESET CONTROL for the requesting BU. Under TABLESET TREE CONTROL, nVision looks at two pieces of information; The DEFAULT SETID (The same one seen on the panel TABLESET RECORD GROUP CONTROLS.) and the TREE CONTROLS. Under TREE CONTROLS, you can specify the actual name of a tree and the corresponding SETID that nVision is to use for that tree and BU. If there is an entry for the given tree under TREE CONTROLS, that takes precedence. If there is not an entry under TREE CONTROLS, then the DEFAULT SETID will be used in determining what tree to use.

For nVision reporting, the RECORD GROUP of FS_21 under TABLESET RECORD GROUP CONTROLS is not used.
(Therefore, the check-box FORCE USE OF DEFAULT SETID is not looked at for nVsion reporting.)

Additional Notes from Development: “I don’t know why the FS_21 group exists, except that all customer-defined records need to belong somewhere. In fact, much of our software, especially views we deliver, assume that the SETID for a given tree’s nodes (or levels) is the same on both the “system” table (PSTREENODE, PSTREELEVEL) and the “user” or “customizable” table (PS_TREE_NODE_TBL, PS_TREE_LEVEL_TBL, or other tables specified in the tree structure. As a result, I don’t expect our reporting tools to access the tree records through the FS_21 group much. However, they might be important to the panel processor and/or application logic.”

Error on renaming the Tree, Invalid, Undefined or Inactive Tree

It is a known problem and there is a document in the T/T which lists the various resolutions. NV: Tree undefined or invalid. This is a very common error message in nVision when you’ve modified a tree and are trying to run a layout that previously ran just fine. This also occurs if you make the changes to the report manually using tree nodes/criteria from a newly created tree. Another symptom is that when you are trying to add criteria using nVision/Layout Definition from a new tree you’ve created, the tree doesn’t show up in the list box. Here are some possible causes and solutions.

1) TableSet problems.

A) nVision doesn’t recognize the trees for the particular setid. Go to the Utilities window and select TableSets /TableSet Controls/TableSet Tree Controls. Add the Tree Names that you want to report on to this panel. If more than one tree is necessary, use add function (F7) to add more Tree Names to the panel.

B) Be sure you have implemented the fix R-CJC-51 which relates to FS_21 on the Utilities menu.

The cause of this problem was that the PS_TREE_NODE_TBL and PS_TREE_LEVEL_TBL records were put into record group FS_04 which is also the tableset record group for detail & summary ledgers. 

The problem occurs when a tree is created under a setid (eg WORLD) and the tablsetid indirection for record group FS_04 is different than the tree setid (eg NEWGN). When you’re adding nodes and levels to this tree the tree editor is doing setid indirection by looking at record group FS_04.

2) Effective dating problems. Clone the tree (File, Save As) and use the effective date of 01/01/1900. This will rule out any effective dating problem. Also be sure that all the nodes on the tree have that same date.

An example of how to set up dates:

The accounts (nodes on tree) effective dated 08/01/95
The tree effective dated 08/01/95
The nVision layout effective dated 08/15/95
The nVision report request effective dated 08/24/95

Don’t forget that the effective date of the tree to be used in the report is specified on the report request. If you use the ledger’s Main As Of Date, this is the date that is defined to be the as of date on the Ledger Definition panel.

This date can be over-ridden by the specifying a date for the tree in the Report Request, if you are getting the error msg when you run the report. The date used by nVision for accessing trees when building the report is different from the one used when the report is run.

3) Problems with tree selector numbers or version numbers. Try doing the following:

a) Delete the Swap files.

b) Rename a node from the tree, and save it. This will ensure that the tree selector table is in sync. Delete a detail value from the tree, re-add it and save the tree again. (This should update the version number in the PSLOCK table. VersionTDM & VersionTAM.) A Save As will also accomplish the same thing.

If you still can’t see the tree after doing steps a) and b):

c) Build a brand new report layout to run against the tree.

d) Save the tree with a new name. Re-save it with the old name. Delete the swap files. Try running the report. If problem persists, re-create the report. If problem persists, re-create the tree, then re-create the report.

4) After an upgrade the nVision reports are not recognizing the trees. It is not pulling in the tree information. Apparently the following DLLs were causing the problem (PSNAM.DLL, PSNLT.DLL, PSNXL.DLL and PDSPS.DLL ).

5) Under very odd circumstances, nVision brings in the wrong setid for the report, and hence the wrong trees as well. Resolution: go into the panel PEOPLETOOLS > UTIILITIES > USE > RECORD GROUP > U/D select FS_21 TREE NODE AND LEVEL and in upper right click the check box, FORCE USE OF DEFAULT SETID. You will be able to see whether the report is bringing the right Tree in the first place by looking at the trace.

6) A SYSAUDIT AND A DDDAUDIT will show the integrity of the tables and views. If still running into problems, run these two reports, (should take less than a 1/2 hour each), and make sure the output is “no rows found”. If there is output with rows found, those problems have to be taken care of before you can proceed.

 Node (XXXX) is undefined, inactive, or invalid (28,20)

While attempting to run a nVision report receive an error: Node (XXXX) is undefined, inactive, or invalid (28,20).

Check for duplicate trees with different effective dated rows.

TREENAME is undefined, inactive, or invalid. Resolve this issue by resaving the tree.

“Node (XXXX) is undefined, inactive, or invalid (28,20)”

If a node name has been selected within the criteria of the layout that does not exist within the tree pointed to, then the above error will occur. Frequently, customers will rename trees and not rename the node criteria within the layout (ie. Node in tree is named “Revenue” and criteria in layout is “Revenues”).

To correct this, edit the criteria in the nVision layout either via nVision Layout Definition, or directly in the criteria in Column A or Row 1. To see Col A, Row 1: nVision, Options, and check the “Show Column and Row Criteria” checkbox.

Error when running an nVision report for a particular BU. Run the same report for a scope that includes this same BU and several others, the report runs, but not all of the data is retrieved according to the report layout. Receive the following error message: “Tree ACCT_TREE undefined, inactive, or invalid. (28,21).” Detail explanation: “nVision could not access the specified tree, because the name is incorrect or the tree has been saved in an invalid state.”

Check the SETID of the tree to the default setid of the requesting business unit. nVision uses the “Default Setid” as the setid for all of the trees in the report (unless specific trees are set up in TableSet Tree Controls).

Another reason you can get this error: The Main As Of Date on the Report Request is less than the effective date of the tree. nVision will not recognize trees with an effective date in the future. For example, if the Main As Of Date on the Report Request is 1/1/1995 and the tree’s effective date is 1/1/97, this tree cannot be accessed in nVision. The effective date problem can also occur if the Report Request uses the date from the “Business Unit Table” and this table isn’t populated with an As Of Date. Check this by going into Establish Business Units and looking at the As Of Date. If this is blank the reports won’t work if you tell them to use the date from the business unit table.

Tree INCOME_WMLOP undefined, inactive, or invalid. (28,21)

Looks like a user changed the default setid for one of the business units. What is disturbing is that one of the machines was still able to process reports until the cache was purged.

Problem was with SetID GO->PeopleTools->Utilities->Use->Table Set Controls->Table Set Group Controls. The value Default SetID was set to WMLOP and should have been set to TRUCK which is the shared accounts SETID/Business Unit. Problem was identified by comparing FPRD to FCPY (which was an image of FPRD as of 09/28/00) and the table PS_SET_CNTRL_TBL.

In versions 7.0x and 7.5x these tables for GL business units effected are:

PS_BUS_UNIT_TBL_GL
PS_BUS_UNIT_TBL_FS
PS_SETID_TBL
PS_SET_CNTRL_TBL
PS_SET_CNTRL_GROUP
PS_SET_CNTRL_REC
PS_BU_JE_ID_CFS
PS_BU_JE_IC_CFS
PS_CONS_BU_LED_TBL
PS_BU_JE_BS_CFS

XXX tree Undefined, Inactive, or Invalid. (28,21) OR sometimes (28,20)

Created a layout that goes against his summary tree called F_SUM_PROJ_ESD. The layout itself runs fine and creates a valid instance. (Using a requesting BU of ESD and is checking Data From Requesting BU only.) While in the instance, perform a drilldown on the total line of his imploded values and the drilldown runs successfully. However, if you drill down on any of the detail lines within the inploded total, immediately get the error “F_PROJ_D_ABM_TUS Undefined, Inactive, or Invalid. (28,21)”. The drilldown is a custom drilldown. Detail tree is F_PROJ_D_ABM_ESD (setid of ESD) and his summary tree is F_SUM_PROJ_ESD (setid of ESD). The structure used on the summary tree (custom structure) points to the detail tree F_PROJ_D_ABM_ESD. (ESD and TUS are two of the 6 business units.). Tableset Tree Controls have been reviewed and are accurate with a default setid of ESD and none of the project trees listed. When he enters the Summary Ledger definition under setid of SHARE (no entry of ESD) the tree specified is F_PROJ_D_ABM_ESD. The ledger in the layout and ledger definition is MR_AESD.

To fix this renamed all his summary chartfields with different names and create 6 new tables. This has fixed the problem.

Another similar problems with summary ledger drilldown  getting the following error message: “Node XXX undefined or invalid in tree YYY. (28,20) ” The same solution fixed this error as well.

“TREENAME is undefined, inactive, or invalid. (28,21)”

PROBLEM: The above error occurred after a user changed the DEFAULT SETID for a business unit in UTILITIES, USE, TABLESET CONTROL, TABLESET RECORD GROUP CONTROLS. 

The following information helps to explain how nVision determines what setid to use in selecting a tree. It also explains the relation to TABLESET CONTROLS and record group FS_21. Some of the information is taken directly from an email from development.

There are two key points that you should keep in mind:

1. When the user is creating or revising a layout, we presume that the layout can be used by a number of different business units, all of which have enough in common that they can use the same report. Because we want the layout to have maximum flexibility (Can be used by business units for which the same tree references mean different things), we use a layout SETID as an indirect pointer to the trees that the prospective using business units have in common. This SETID in turn points to trees that are similar to those used by the various business units. The design-time SETID is entered on the Prompt dialog box, which is available at any time using the PROMPT button in the Layout Definition.

The layout SETID supports prompting and validation when creating or modifying the layout, and is ignored at run time.

2. The REQUESTING BUSINESS UNIT field in Report Request is the starting point in determining what SETID will be used. The system then looks at TABLESET TREE CONTROLS in UTILITIES / TABLESET CONTROL for the requesting BU. Under TABLESET TREE CONTROL, nVision looks at two pieces of information; The DEFAULT SETID (The same one seen on the panel TABLESET RECORD GROUP CONTROLS.) and the TREE CONTROLS. Under TREE CONTROLS, you can specify the actual name of a tree and the corresponding SETID that nVision is to use for that tree and BU. If there is an entry for the given tree under TREE CONTROLS, that takes precedence. If there is not an entry under TREE CONTROLS, then the DEFAULT SETID will be used in determining what tree to use.

For nVision reporting, the RECORD GROUP of FS_21 under TABLESET RECORD GROUP CONTROLS is not used. (Therefore, the check-box FORCE USE OF DEFAULT SETID is not looked at for nVsion reporting.)

Tree Invalid or Undefined

This is a very common error message in nVision when you’ve modified a tree and are trying to run a layout that previously ran just fine. This also occurs if you make the changes to the report manually using tree nodes/criteria from a newly created tree. Another symptom is that when you are trying to add criteria using nVision/Layout Definition from a new tree you’ve created, the tree doesn’t show up in the list box. Here are some possible causes and solutions.

1) TableSet problems.

A) nVision doesn’t recognize the trees for the particular setid. Go to the Utilities window and select TableSets /TableSet Controls/TableSet Tree Controls. Add the Tree Names that you want to report on to this panel. If more than one tree is necessary, use add function (F7) to add more Tree Names to the panel.

B) The cause of this problem was that the PS_TREE_NODE_TBL and PS_TREE_LEVEL_TBL records were put into record group FS_04 which is also the tableset record group for detail & summary ledgers. This is incorrect.

The problem occurs when a tree is created under a setid (eg WORLD) and the tablsetid indirection for record group FS_04 is different than the tree setid (eg NEWGN). When you’re adding nodes and levels to this tree the tree editor is doing setid indirection by looking at record group FS_04.

Our resolution was to add a new record group FS_21. The PS_TREE_NODE_TBL and PS_TREE_LEVEL_TBL were moved from FS_04 to FS_21. The peoplecode for populating the initial tableset sharing values was modified to hardcode the SETID value for rec group FS_21 to the SETCNTLVALUE for the tableset group. This field is also grayed on rowinit when entering the tableset group control panel to prevent a user from changing it to anything else.

2) Effective dating problems. Clone the tree (File, Save As) and use the effective date of 01/01/1900. This will rule out any effective dating problem. Also be sure that all the nodes on the tree have that same date.

An example of how to set up dates:

The accounts (nodes on tree) effective dated 08/01/95
The tree effective dated 08/01/95
The nVision layout effective dated 08/15/95
The nVision report request effective dated 08/24/95

Don’t forget that the effective date of the tree to be used in the report is specified on the report request. If you use the ledger’s Main As Of Date, this is the date that is defined to be the as of date on the Ledger Definition panel.

This date can be over-ridden by the specifying a date for the tree in the Report Request, if you are getting the error msg when you run the report. The date used by nVision for accessing trees when building the report is different from the one used when the report is run.

3) Problems with tree selector numbers or version numbers. Try doing the following:

a) Delete the Swap files.

b) Rename a node from the tree, and save it. This will ensure that the tree selector table is in sync.
OR Delete a detail value from the tree, re-add it and save the tree again. (This should update the version
number in the PSLOCK table. VersionTDM & VersionTAM.) A Save As will also accomplish the same thing.

If you still can’t see the tree after doing steps a) and b):

c) Build a brand new report layout to run against the tree.

d) Save the tree with a new name. Re-save it with the old name. Delete the swap files. Try running the report. If problem persists, re-create the report. If problem persists, re-create the tree, then re-create the report.

4) After an upgrade the nVision reports are not recognizing the trees. It is not pulling in the tree information. Apparently the following DLLs were causing the problem (PSNAM.DLL, PSNLT.DLL, PSNXL.DLL and PDSPS.DLL ).
After sending the customer the correct DLLs for his PS Tools version it corrected the problem.

5) One last thing, under very odd circumstances, nVision brings in the wrong setid for the report, and hence the wrong trees as well. Resolution: go into the panel PEOPLETOOLS > UTIILITIES > USE > RECORD GROUP > U/D select FS_21 TREE NODE AND LEVEL and in upper right click the check box, FORCE USE OF DEFAULT SETID. You will be able to see whether the report is bringing the right Tree in the first place by looking at the trace.

6) A SYSAUDIT AND A DDDAUDIT will show the integrity of the tables and views. If still running into problems, run these two reports, (should take less than a 1/2 hour each), and make sure the output is “no rows found”. If there is output with rows found, those problems have to be taken care of before you can proceed.

7) tree needs to be specified PS_LED_FLD_TBL .

Posted by: repettas | March 5, 2008

How To Run Tree Utility Tree Audit

How to run the Tree Utility Tree Audit to check for Tree Corruption

There is an Application Engine program that you can run that can check for tree
corruption.  The navigation to get to the Tree Utility Tree Audit is through the
browser:

8.1x:  PeopleTools>Tree Manager>Process>Tree Utilities. 
8.4x:  PeopleTools>Tree Manager>Tree Auditor

Users will then need to create a run control id.  The default will be set to
‘Tree Utilities’.  There is a checkbox for ‘all trees’.  If this box is checked,
then the tree audit will check all the trees for tree corruption.  This would be
good to use just for regular tree maintenance, but to check for a specific tree,
you can input the tree name, tree key, and effective date. 

After the process is run, go to the Process Monitor to verify that it went to
success.  From Process Monitor you can click on a link that will take you back
to the Tree utilities.  From the Tree Utilities page you can click on the
‘View Results’ link.  In there it will give you the results of the Tree Audits.

Posted by: repettas | March 5, 2008

Tree Utility Audits Check What?

What does the Tree Utility tree Audits through the web check for?

In PeopleTools 8.1x there is a new tree utility program that will audit the trees.
What does this check for?

This tree audit will check for trees with the following:
1)  Parent node does not exist
2)  Orphan tree leaves
3)  Tree Node Numbers are great than end numbers
4)  Tree node’s end number is greater than parent’s end number
5)  Tree nodes with overlapping ranges
6)  Node’s level number is less than parent’s level number

How to fix:

  1. Parent Node does not exist: If there are no other audit errors for this tree
    then running the ‘Correct Parent Node’ Utility program should correct the problem. Do NOT run the ‘Correct Parent Node’ Utility program if there are any other audit problems with the tree.  The other errors should be addressed first before trying to correct problems by running the utility program.
  2. Orphan Tree Leaves: There are records in the PSTREELEAF table where the leaf’s parent node number does not have a correspondnig record and node number in the PSTREENODE table.  Run the ‘Delete Orphan Tree Objects’ utility to delete these records
  3. Tree Node Numbers are Greater than End Number: Tree Nodes that have node numbers greater than the ending range for that node. The only solution is to use TreeManager to delete these nodes and then run the ‘Reset Tree Node Gaps’ utility program.  You will then need to re-insert the deleted nodes.  If for some reason you are not able to delete these nodes from within TreeManager it is recommended that you call the GSC for support.
  4. Tree Node’s End Number is Greater than Parent’s End Number: This audit reports on tree nodes whose end range value is greater than the end range value of its parent node. The only solution is to use TreeManager to delete these nodes and then run the ‘Reset Tree Node Gaps’ utility program.  You will then need to use Tree Manager to re-insert the deleted nodes.  If for some reason you are not able to delete these nodes from within TreeManager it is recommended that you call the GSC for support.
  5. Tree Nodes with Overlapping Ranges: Two nodes that have overlapping node number ranges. The only solution is to use TreeManager to delete these nodes.  If for some reason you are not able to delete these nodes from within Tree Managerit is recommended that you call the GSC for support.
  6. Node’s Level Number is Less than Parent’s Level Number: Nodes whose level number is less than the level number of their parent.
  7. The level numbers can be corrected by either: Switching the levels manually from within Tree Manager. Run the ‘Correct Level Numbers’ utility to reset all invalid level numbers on the tree.
Posted by: repettas | March 5, 2008

How to Get Custom Queries from Production Database

What are the steps to get current custom queries from Production database?

In order to get a copy of your queries, you should perform the following steps:

  1. Obtain a list of queries, trees, and tree structures that you need to preserve.
  2. At the beginning of the test Move to Production, you should make a new
    copy of your current production database (I’ll refer it as “Tree/Query Copy
    of Production”). Now you should have a Copy of Production database and a
    Tree/Query Copy of Production database.
  3. Perform the test Move to Production on your Copy of Production database.
    (NOT on your “Tree/Query Copy of Production”).
  4. The Tree/Query Copy of Production database needs to be at the same
    release level as the Copy of Production database on which you just completed
    the test Move to Production. To update your Tree/Query Copy of Production to
    the same release, you run release scripts against this database. PeopleSoft
    refers to this as “reling up” the database. Use the Custom Compare template
    to “rel up” your database. Select the Product Line PEOPLETOOLS when
    configuring your Change Assistant job. Perform all the steps in the template.
  5. Create a project in the Tree/Query Copy of Production that contains all
    of the queries and trees that you wish to preserve called PRESERVED.
    Manually compare the queries, trees, and tree structures that are in your
    PRESERVED project against your Copy of Production database.
  6. Copy the PRESERVED project to the target database (Copy of Production
    database on which you ran the test Move to Production). 
  7. Queries, trees, and tree structures that you wanted to preserve should
    be in the Copy of Production database now. You must test and re-test and
    make any necessary changes if the test results are not what you expected.
  8. Once you are satisfied with the test results, you must re-export the
    PeopleTools tables from the Tree/Query Copy of Production using MVPRDEXP.DMS
    in order to actually preserve the queries, trees, and tree structures. You
    will now use the output files created from running this script as input files
    during your final Move to Production. Running the MVPRDEXP.DMS script again
    ensures that you have the most current PeopleTools tables that include your
    preserved custom queries, trees, and tree structures.

Find details on how to run each of the steps above in the appendix Queries Trees
and Tree Structures of your upgrade documentation.

Posted by: repettas | March 5, 2008

nVision Tree Undefined, Inactive or Invalid

E-NV:  Error running nvision ‘Tree undefined, inactive or invalid’

SOLUTION 200993098:  E-NV:  Error running nvision ‘Tree undefined, inactive or
invalid’

SPECIFIC TO:
Enterprise, PeopleTools, nVision, All PeopleTools versions

ISSUE: Running nVision report gives an error that the tree is undefined, inactive,
or invalid.  Checks and the tree exists and is valid. 

SOLUTION:
This error is generally caused by the business unit being used for the nVision
report not having the default setid of the tree being used.  The first thing to
check is the table set tree controls:

PeopleTools -> Utilities -> Administration -> Table Set Controls
Look for the Business Unit that you are using in your nVision report.
Go to the tree tab and see if the default SETID is the setid of the tree that
you are using.  If it is not, then add the tree to the bottom area there.  (Even if it is already the default, try adding it and see if it makes a difference).

NOTE:  When adding a tree to the tableset tree controls, you need to
first use the lookup for the SETID and then the lookup for the tree name. 
It looks backwards.

Posted by: repettas | February 15, 2008

Query Performance

Improvement on Query performance implemented in 8.17 and 8.41

Performance issue getting a list of Queries and/or Records on the Web/Windows Client and running Queries on the Web for 8.x.

To improve the performance, Development has added two mechanisms. Therefore starting with PeopleTools 8.17, when bringing up a list of Queries, the system will first check:

- whether a temp Record has been created from the App Engine run job to store the data of the SQL statement
- SQL Object
- pull data using the SQL statement directly from the Database (PTools 8.16)

Here is the description of the 2 mechanisms:
**********************************************************
1. This specific Incident (T-SSHAIK-XQ8LT) discusses a new App Engine program that has been built to de-normalizes some of the Tables that are used to build the Query Access List. If the Customer chooses to use this new feature, it will greatly simplify the SQL that needs to be executed by Query to build Access List — regardless of what process is running the Query, i.e. would work the same for Crystal as it does for online Query. This new feature is called Query Access List Cache. Here is some more information regarding it:
**********************************************************

Excerpt From Query PeopleBook Enhancement for 8.17 -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Maintain Security – Query Access List Cache page
To enable the Access List Cache:
1. Select PeopleTools, Maintain Security, Setup, Query Access List Cache.
2. Select Enable List Cache.
3. Select the Run button to run the process.
If the process does not complete, the Cache will be disabled.

From Query PeopleBook in 8.4x -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Security – Query Access List Cache page
To enable the Access List Cache:
1) Select PeopleTools, Security, Query Security, Query Access List Cache.
2) Select Enable List Cache.
3) Select the Run button to run the process.

If the process does not complete, the cache will be disabled.

Note. Rerun the process when changes have been made to Query Access Groups or Query Access Group settings on Roles or Permission Lists.

If you need to be able to tell if the Cache is working:
1) Clear the Access List Cache.
2) Load Windows Query directly by running psqed.exe and use a stopwatch to time how long it takes from the moment you click the ok button to login until the time Windows Query appears.
3) Enable the Access Cache and run the App Engine program to populate the Cache.
4) Perform Step #2 again and compare the times.

Note that if the time in Step #2 is less than 5 seconds, you probably won’t see much improvement as it’s mostly overhead at that point. If you try this on a full Financial Database, you should see the time drop from about 60 seconds to less than 10 seconds.
**********************************************************

The only qualifier on this is that the DBA should only make changes to SQL that modify the access plan, i.e. would be acceptable to carefully reorder Tables in FROM clause or WHERE conditions, but they would need to be very careful to make sure that the actual SQL logic/result set is not affected.

Added code to read the SQL for Accessible Record List from a SQL Object. If the SQL Object is not found, then the hard-coded SQL in C++ is used. The rules are:
1) Look for the de-normalized table for Accessible Record List.
2) If #1 is successful, get the Records using that Table.
3) If #1 is not found or data is not found in #2, look for SQL Object PS_QRYACCRECLIST.
4) If #3 is found, use that SQL for fetching the Record List.
5) If #3 is not found, use the hard-coded SQL.

Added optimizable SQL Objects PSQRYACCLISTBIGSQL and PSQRYACCLISTFASTSQL. The former is for when the Cache is not used, the latter is the Cache access SQL.

Posted by: repettas | February 15, 2008

Add Oracle Hints to Query Search Views

Adding Oracle hints to Query Search Views (Security Views)

To add hints to the view SQL, do the following:

Open Application Designer
File/Open select Record then choose the SQL View in question (ex: EMPL_SRCH_GBL)
goto Object Properties and click on Type tab, you will see the SQL View

After the word SELECT you will insert the hint. ex: SELECT /*+ RULE */ A.EMPLID, B.RMPL_REC#

If in Query and want to add a HINT in the query see below:

What you will need to do is create an EXPRESSION. Right click on Expression then select New Expression.

In the box where you can write your expression you will type in the HINT then click on the Add Field button. Add the first field in your field list. You have to do this so that PS/Query doesn’t put a comma after the hint. The text in the Expression box should look something like this: /*+ ALL_ROWS */ A.EMPLID

Syntax = /*+ */

Make sure that the is the first field you want in your query output. When you are finished adding the Expression you should click on the SQL tab to make sure the SQL looks OK. It will look something like this:

Select /*+ ALL_ROWS */ A.EMPLID
From EMPLOYEES A

Posted by: repettas | February 15, 2008

What Are the Tree Table Names

E-TR: What are the names of the Tree tables?

Names of Tree Tables and relationship parent/child

PeopleSoft Tables – Tree Manager

Table Name Table Description
PSTREEDEFN Stores valid tree definitions. Updated by the Tree Manager. Non English stored in PSTREEDEFNLANG.

PSTREENODE Contains one entry per tree node / deptid. Defines relationships
between nodes. Updated by the Tree Manager.

PSTREEBRANCH Stores tree branches.

PSTREESTRCT Defines the different records and fields that control the tree attributes.
Updated by the Tree Manager.

PSTREELEVEL Validation table for tree levels. Updated by Tree Manager.

PS_ACCESS_GRP_TBL Stores defined Access Groups. Used for Query Security

PSTREESELCTL Control table for PSTREESELECT## tables. Also: PSTREESELNUM.

PSTREESELECT01-30 Stores tree leaf information (one table for every possible length of a chart field 1-30 characters). Work table for optimization purposes.

PSTREELEAF Contains ranges of field values that are associated with a node of a tree.

PSTREEPROMPT (Specific to 8.x and up only) Stores similar information as PSTREEDEFN and is needed for trees to be viewed through PIA

Parent/Child relationship: The range of nodes from PSTREENODE.TREE_NODE_NUM to TREE_NODE_NUM_END
represents all the nodes in the sub-tree headed by this node. So the Tree Manager uses these fields to determine parent-child relationships.

Tree_Node_Num The node number of this node.
Tree_Node_Num_End The node number of the last child of this node. The range of nodes from Tree_Node_Num through Tree_Node_Num_End
represents all the nodes in the sub-tree headed by this node.

Posted by: repettas | February 15, 2008

nVision Tree Selector Tables – What Are They

NV: Tree selector tables – what are they and why are they used?
 “NV: Tree selector tables – what are they and why are they used?”

 Analyzing the SQL statements that are generated when nVision reports are run. The SQL statements keep using tables called “PSTREESELECTxx” where xx is some number. What are these tables?

nVision and Query use tree “selector” tables when there is a need to interpret node data from a tree. These tables “flatten” a tree by populating the selector table with all the ranges of values for a given tree node. This table is then joined to the field being rolled-up in order to retrieve the rows that fall under that tree node.

There is a selector table for each chartfield in the GL. The number after the PSTREESELECT represents the length of the chartfield. For instance, PSTREESELECT06 is normally the selector table used for account values because the account chartfield is 6 digits in length. PeopleSoft delivers 30 selector tables.

Selector table data is built when the nVision report or query is run. Selector table data can be “static” or “dynamic” depending upon the performance options which are set for each tree. Static selectors remain in the selector tables until a tree is changed. If nVision or query detect a change in the version number of a tree the selector table data gets rebuilt. Dynamic selector data is build “on the fly.” The selector table data is built at runtime and then deleted from the table.

For more information on selector tables and nVision performance please read the PeopleTools PeopleBook. The information can be found under Reporting and Analysis Tools, nVision, Performance.

More Information: The data in the PSTREESELECTXX tables can be purged. You should do this if the data gets out of synch (especially after an upgrade). Data can be deleted from both PSTREESELECTXX and the control table PSTREESELCTL.

Posted by: repettas | February 14, 2008

Rebuilding Tree Selector Tables

NV: Rebuilding Tree Selectors for nVision (PSTREESELECT)

Tips and Techniques

Subject: Rebuilding Tree Selectors for nVision

Content: The following are 2 methods in rebuild tree selectors.

Method 1: nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it, or if the tree is copied to a new effective date.

Method 2: delete the selector control entries associated with the ACCOUNT trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):

delete from pstreeselctl
where tree_name in
(select tree_name from pstreedefn
where tree_strct_id=’ACCOUNT’);

One additional note: deleting the Selector Controls forces nVision to rebuild them from the other tree tables. It also leaves all the old selector rows orphaned in PSTREESELECT06 (if ACCOUNT is 6 char. long). But it doesn’t affect the node name lookup, which is done either on the system node table (PSTREENODE) or in a cache (swap file) of nodes nVision has already looked up. I have heard of cases where that cache was not rebuilt after a tree change, but that only happens when the nVision user stays logged on while the tree change takes place. Logging off and logging back on should solve this, and is less disruptive than deleting swaps.

————————————————-

More information regarding PSTREESELECT table.

When does PSTREESELECT get updated?

The PSTREESELECT# tables are used to speed tree-based data selection. The table gets rows inserted into it the first time a tree is used by an nvision report.

PSTREESELCTL is the table which controls whether or not to refresh data in the selector tables based on the Tree effective date. If you modify a tree and save it, the pstreeselect tables will get updated when you run an nVision report that uses that tree.

The indexes need to be tuned based on how they use it. PSG goes out and does this a lot. It is covered in Chapter 12 of the Using your GL system.

======================================================

How does PeopleTools repopulate the tree selector tables without a database 

The selector tables should be recreated each time the nVision reports are run. Basically what happens is that nVision looks to see if the selector table values exist for items in the trees. If they are not there, the process recreates the static selector table values. You should never have to copy or create selector table values, the system will do it for you. Unfortunately, if you have changed the trees between the time the selector tables were deleted and the time they are rebuilt, you may have ‘orphaned’ records which may impede performance. This can be identifed and deleted using SQR.

If you are running with Dynamic Selectors on in the Performance Options, the selector values for the trees being used only exist for the duration of the report run. They will not remain after the run completes.

« Newer Posts - Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.