The Centralized FETCH

PR TRACKER has a separate Menu and process for using FETCH to pull Oracle information into PR TRACKER. It does it once and the result is shared by all of the tracking groups. Also as a part of that process, PR TRACKER adds the Cisco Hierarchy information into each record. This way, when each group does it's UPDATE process, the Oracle data is already available in that shared Central Database.

To accomplish this, the FETCH must be done multiple times. First, it is done separately for Open PO records and for AP records. Second, it is done separately for each of the upper management levels needed for all of the tracking groups. The PR TRACKER folder on the shared drive contains the Excel file PODataFetch.XLS -- you can use this for the FETCH or any other Excel file that has the Essbase Fetch set up.

So right now PR TRACKER needs Oracle data for the following Cisco Management Nodes:
        M32864 Bostrom, Susan L.
        M50385 Goodwin, Keith E.
        M05367 Rogan, David A.
        M02948 LePage, William J.

In Excel--Essbase the FETCH must be done for each of those Nodes, for ACTUAL OPEN PO's and for ACTUAL AP, for the Current Year and Quarter. As each FETCH is done, the resulting new Sheet must be renamed, using the following structure: Management Node initials (2) - OPO or AP - yyyyQq.  Examples, for 4th Quarter 2009, are:
        SB-OPO-2009Q4
        SB-AP-2009Q4
        KG-OPO-2009Q4
        KG-AP-2009Q4
        DR-OPO-2009Q4
        DR-AP-2009Q4
        WL-OPO-2009Q4
        WL-AP-2009Q4

Linking to the FETCH Results

At his point the Excel--Essbase FETCH is completed.  PR TRACKER is normally already linked to these sheets, but whenever you either change the name or location of the Excel file, or whenever you change the Year/Quarter involved, then you need to re-link PR TRACKER to these Sheets.  One way to check the linkages, if you need to, is to push the F11 function key -- which will bring up the ACCESS tables list, locate any of these eight tables, and highlight one and click on the OPEN button.  If it opens (and shows the right data), then the linkage is OK.

You do the re-linking using the Linked Table Manager form in ACCESS.  For ACCESS 2003 you go to TOOLS--DATABASE UTILITIES--LINKED TABLE MANAGER.  For ACCESS 2007 you go to the EXTERNAL DATA ribbon and on the right side you will find the LINKED TABLE MANAGER.

The Linked Table Manager shows all of the tables being used that reside in other files, and are thus linked into the PR TRACKER program.  Here, we are just dealing with the eight Excel Sheets described above, which ACCESS treats just like any other linked tables.  To re-link simply check to the left of each of these eight tables, and also check at the bottom to always ask for the location, then click OK.  You will then browse to the Excel file containing these eight sheets (normally PODataFetch.xls) and double-click on it. ACCESS should tell you that all have been re-linked successfully. If necessary, you can delete any linked table (which only deletes the linkage) then use the Linked Table Manager to re-link to them one at a time.

 

Build the Central Database Table

Once you are properly linked to all eight of the FETCH tables (the Excel Sheets you have built), you run the processes that merge this data together into the two central tables that all of the tracking groups use: tblOpenPOs and tblAPPOs.

You get to this menu form by going into SETUP and double-clicking on the title SYSTEM SETUP.

Then click on the yellow OPEN PO's button to merge together all of the Open PO records into tblOpenPOs.

Click on the green AP button to merge together all of the AP records into tblAPPOs.

Click on the blue Hierarchy button to add the Cisco Hierarchy information into both of those tables.

Finally, you can use the grey REVIEW button to view the resulting tables, if desired.