Implementing the Data Collection Toolkit

Before you begin

Before you can build a data collection template, make sure of the following:

• The Express Spreadsheet Add-In software is installed on your PC and in Excel.

• You have access to an Express Client Connection file to make a connection to the instance of Express Server used by the appropriate Financial Analyzer shared database.

  Note: The Oracle Express Database Administration Guide describes how to configure Express connections.

• The user id for your operating system is associated with a Financial Analyzer user name that has appropriate access to the shared database.

You must also ensure that the following settings on the Database tab are specified in the Options dialog box that is accessed from the Express menu in Excel.

Property	Setting
Default Attach Mode	Read only
Permanently write changes to databases attached read only box	Off

You must also ensure that the following settings on the Format tab are specified in the Options dialog box that is accessed from the Express menu in Excel.

Property	Setting
Show Page Dimension Controls box	On
Preserve Formatting After Drilling/Selection box	On
Resize Columns to Fit Data box	Off

Implementation process

To implement the toolkit, follow these general steps.

1. Create an Excel workbook template as described in the topic, "Step 1: Creating an Excel workbook template".

2. Define the query or queries using the Express Spreadsheet Add-In as described in the topic, "Step 2: Defining queries using the Express Spreadsheet Add-In".

3. Insert an Excel macro module in the workbook as described in the topic, "Step 3: Inserting an Excel macro module in the workbook".

4. Define routines in the macro module as described in the topic, "Step 4: Defining routines in the macro module".

5. Create a custom Financial Analyzer database as described in the topic, "Step 5: Creating a primary custom Financial Analyzer database".

6. Define Express programs in the custom database to handle recalculate and submit procedures as described in the topic, "Step 6: Defining Express programs".

7. Clear each query in the workbook as described in the topic, "Step 7: Clearing the queries".

8. Install the workbook template on each users' desktop as described in the topic, "Step 8: Installing the template on users' desktops".

Step 1: Creating an Excel workbook template

To implement the toolkit, you must create an Excel workbook template to use for data collection. By incorporating Excel VBA routines in the workbook, you determine what functionality the template makes available to users.

Using the new template, users can directly access their Financial Analyzer shared database. The users' profiles in Financial Analyzer determines the data to which they have access. Users cannot use the template if they disconnect from the server where the shared database is located.

One workbook can be created for many users. The template workbook can be distributed to users through electronic mail or via a network. Oracle Corporation recommends that each user have a personal copy of the workbook file. This is preferable to a situation in which multiple users access a shared copy.

Step 2: Defining queries using the Express Spreadsheet Add-In

Queries that are defined using the Express Spreadsheet Add-In are bound to the database in which they were created. Therefore, you must create the queries using the shared database that users will be accessing through the workbook template.

To define queries, follow these steps.

1. Open a new workbook template in Excel.

2. Connect to the appropriate Financial Analyzer shared database and attach it read-only.

3. Run the Express Wizard to define the necessary query or queries.

   For instructions on running the Express Wizard, refer to the Express Spreadsheet Add-In Help system.

4. Format the query or queries as desired using standard Excel formatting features.

   Tip: Although editing the query will preserve formatting in most cases, try to ensure that the query has all the necessary information before beginning formatting.

5. Save the spreadsheet.

Step 3: Inserting an Excel macro module in the workbook

Next, you must create an Excel Macro Module in the workbook. In this module you will define a number of macro routines to control functionality within the workbook.

To insert a macro module in Excel, follow these steps.

1. Select a sheet in the workbook. The module will be inserted to the left of the sheet you select.

2. From the Insert menu, choose Macro, and then choose Module.

Step 4: Defining routines in the macro module

In the macro module, define a number of macro routines to control functionality within the workbook.

The sample workbook template, SAMPLE.XLS, uses the following macro routines to control functionality within the workbook. These routines use Express Spreadsheet Add-In API methods that must be defined within the macro module of the workbook.

Note: For code listings of the sample macros described here, refer to "Data Collection Toolkit Technical Reference".

Auto_Open routine

Auto_Open is a predefined Excel procedure that runs whenever a workbook is opened. Auto_Open ensures that the configuration for the workbook is always the same on startup.

In the sample workbook template, the Auto_Open procedure calls the Startup routine.

Startup Routine

The Startup routine performs the following tasks:

• Makes a connection into Express Server.

  The Startup routine uses the Express Spreadsheet Add-In XPConnect method to establish a connection with Express Server. The login routine is based on the Express Client Connection file for a user, described in the topic "Before you begin".

  If a connection cannot be made, XPConnect returns FALSE and the workbook is closed.

• Sets the Excel environment.

  The Startup routine sets the following Excel options and environment variables:

  • Turns screen updating off to improve macro performance

  • Sets the Calculation option to Manual

  • Turns the Recalculate Before Save option off

  • Turns the Express menu off to limit the functionality available to users

• Runs the Financial Analyzer Startup routine. The Financial Analyzer Startup routine is located in the OFASERVE database. This database must be attached first.

  The Startup routine runs a Financial Analyzer API named OFA.EXT.INIT. The Express Spreadsheet Add-In XPExecute method attaches the OFASERVE database and executes OFA.EXT.INIT.

  OFA.EXT.INIT performs the following tasks:

  • Attaches all code databases

  • Populates temporary Financial Analyzer catalogs

  • Attaches the shared database

  • Applies the appropriate access rights to the shared database based on the operating system user id entered at login

• Checks for outstanding data submissions.

  Because the user is making changes to a shared database that is attached read-only, the changes are permanently written to the shared database only when the user's data submission is processed by the Task Processor.

  If the user exits a session where a submission has been made and then creates a new session against the same shared database before the earlier submission has been processed, then the user will be working with out-of-date data.

  This part of the routine stops the user from entering the workbook if any submissions made by that user are waiting in the Task Processor queue.

  The Financial Analyzer API, TK.CHK.UNPROC, returns TRUE if any tasks for the user are still waiting in the Task Processor queue.

  Note: The internal Financial Analyzer user name is stored in the SHR.USR.ROLE variable in Express during a session.

• Sets Express Spreadsheet Add-In options.

  Certain Express Spreadsheet Add-In options should be set in a particular way. Others can be varied by implementation. The Express Spreadsheet Add-In XPSetOption method can be used to set individual options.

• Registers the shared database.

  Once the shared database has been attached, the Express Spreadsheet Add-In XPDatabaseAttach method registers with the defined active query. After the registration, the query can be refreshed from the database by using the Express Spreadsheet Add-In XPRefresh method.

  Note: When running Express Server on NT, XPDatabaseAttach will only accept a drive path and not a UNC. All other references to the shared database in Financial Analyzer will also need to use a drive path.

  Tip: When you call XPRefresh, use coordinates that reference an active cell of the query.

• Disables access to the Selector through the query.

• Users can access the Selector by double-clicking on a query. If you do not want to allow users to change the status of the query then you can disable this option by setting the OnDoubleClick property to null. If you have queries defined on multiple sheets, you will need to do this for each sheet.

Calculation Routine

In the sample workbook template, the calculation routine writes back changes made to the current page to the shared database and then runs a solve definition in the database.

The Express Spreadsheet Add-In XPSetCustomWriteCommand method sets the Express program that will handle the writing of the changes to Express. Then the Express program RECALC.OFAXSA is called.

The RECALC.OFAXSA program performs the following tasks:

• Applies the changes to the database

• Filters out financial data items that should not be updated

• Runs a solve definition

The Express Spreadsheet Add-In XPWrite method executes the write for any changes made to the page. After the calculation routine has been run, the query refreshes to show the results of the solve definition.

Tip: When calling XPWrite, supply coordinates that reference an active cell in the query.

To execute the Calculation routine, the user chooses buttons created with Excel VBA that have the appropriate macros assigned.

Submit routine

In the sample workbook template, the submit routine takes the data that has been changed by the user in the current session and creates a submit data task.

Initially, the Submit routine checks to see if the user has changed any data for the current page but has not yet run the calculation routine.

If this is the case, the calculation routine is run before the data submission. The Express Spreadsheet Add-In XPGetChangeScript method identifies if a change has been made to the current page that has not been written back to Express.

In the sample the data submission is executed by the Express program SUBMIT.OFAXSA. This program sets the status of the financial data items to be submitted and creates the data submission task.

Tip: When calling XPGetChangeScript, supply coordinates that reference an active cell in the query.

To execute the Submit routine, the user chooses buttons created with Excel VBA that have the appropriate macros assigned.

Auto_Close

Auto_Close is a predefined Excel procedure that is run every time a user closes the workbook. In the sample workbook template, Auto_Close uses the Express Spreadsheet Add-In XPClose method to close any open connection to Express Server. XPClose also calls the Excel Close method that closes the workbook, automatically saving any changes that have been made.

Step 5: Creating a primary custom Financial Analyzer database

The Financial Analyzer custom database should be created in the CODE directory and its name declared in the OFACDCF.CFG file.

Any custom Express programs or objects defined during a Financial Analyzer implementation should be defined in the primary custom database. This database should be created in the code directory and its name declared in the OFACDCF.CFG file.

For example if you have created a primary custom database called CUSTOM, your OFACDCF.CFG file should contain the following information:

 [Primary Custom Database]
OFALCNAME=custom

Step 6: Defining Express programs

You define custom Express programs to handle the recalculate and submit procedures.

The sample file, SAMPLE.INF, contains two Express programs that handle the recalculate and submit procedures. The programs can be incorporated in a Financial Analyzer custom database using the Express INFILE command.

Recalculate Procedure

In the sample workbook template, the recalculate procedure RECALC.OFAXSA is defined as a boolean program in the primary custom database.

The RECALC.OFAXSA program performs the following tasks:

1. Applies the changes to the database

2. Filters financial data items that should not be updated

3. Runs a solve definition

The status of dimensions is tracked through the use of valuesets. Valuesets should be defined for all the dimensions of the financial data items being written and solved. The status of the valuesets is initially be set to null. In the sample, valuesets are defined for the Line, Org, Prod and Time dimensions.

The XPSetCustomWrite method passes to the program a string of qualified data references (QDRs) that reference the cells of the query that have been changed. RECALC.OFAXSA evaluates each QDR to determine if that financial data item should be written to. In the sample, the financial data items to be written to are BUDG and BUD.ASMP99. The status of the dimensions of each QDR for these financial data items is added to the appropriate valueset and the QDR is executed. This routine filters out those financial data items that are present in the query but that should not be altered.

In the sample, only the part of the financial data item that has changed will be solved. The status of the solve is set by the status of the valuesets. In the sample, the only exception to this is the LINE dimension. The solve runs a model on the line dimension and status must be set to all.

Also, only the data relating to the pages of the query that have changed will be submitted. In the sample, the Prod and Org dimensions are the page dimensions. A list of Prod and Org values that have been changed between submissions is kept in two temporary variables named OFAXSA.PRD.LIST and OFAXSA.ORG.LIST. These are defined as single cell text variables in the primary custom database.

The solve definition is run by the Financial Analyzer API named SO.RUN.DFN. SO.RUN.DFN is passed two arguments:

• The solve definition to be run expressed as a value of SO.ENTRY

• The list of valuesets that correspond to the dimensions of the financial data item being solved

Tip: You can find the value of SO.ENTRY you need to pass by looking at SO.DESC.

Submit Procedure

In the sample workbook template, the submit procedure SUBMIT.OFAXSA is defined as a Boolean program in the primary custom database.

SUBMIT.OFAXSA sets the status of the financial data items to be submitted and creates the data submission task. In the sample, the two temporary page variables named OFAXSA.ORG.LIST and OFAXSA.PRD.LIST contain the list of page dimension values that have been changed. If either temporary page variable is empty then the program exits without submitting.

The dimension status of the financial data items to be submitted is set through the dimension valuesets. In the sample, the status for Line is set to all because a model has been solved on that dimension. The status of Org and Prod are set by the temporary page variables. The status of Time is set according to the values shown in the query.

The submit is run by a Financial Analyzer API named SB.DATA.SHR. SB.DATA.SHR is passed the following two arguments:

• The list of valuesets that correspond to the dimensions of the financial data item being submitted

• The object name of the financial data item to be submitted

Tip: To keep changed data from being submitted more than once, the temporary page variables OFAXSA.ORG.LIST and OFAXSA.PRD.LIST are set to NA at the end of the procedure.

Step 7: Clearing the queries

Before the workbook is ready for distribution each query should be cleared. Clearing the query clears labels and data from the query and prepares it to be opened by the users. When the user opens the workbook the labels and data are placed into the query according to their dimension access definitions.

To clear a query, follow this procedure.

1. In the Excel workbook, position the cursor in any cell that contains data for the query you want to clear.

2. Select Clear Query from the Express menu.

   The Express Spreadsheet Add-In displays a confirmation box.

3. Choose Yes in the confirmation box to clear the query.

Step 8: Installing the template on users' desktops

Before they can use the data collection template, each template user needs the following:

• The Express Spreadsheet Add-In software installed on their PC and in Excel. The Oracle Express Spreadsheet Add-In User's Guide contains information on installing the Express Spreadsheet Add-In. It includes a list of supported versions of Excel and other software and hardware requirements.

• Access to an Express Client Connection file to make a connection into the instance of Express Server used by the appropriate Financial Analyzer shared database. The Oracle Express Database Administration Guide describes how to configure Express connections.

• A Financial Analyzer user name.

• The appropriate dimensions, financial data items, hierarchies, attributes, models and solve definitions distributed to them.

• Note: Access to the data defined in the template will depend on the structure distributed to each Financial Analyzer user name. If more than one Financial Analyzer user name is assigned to an operating system userid, then the first Financial Analyzer user name defined will be used. The exception to this is if the operating system user id is assigned to the Financial Analyzer user name of the Administrator. In that case, this profile will be used.

The workbook template can be distributed to users through electronic mail or via a network. Oracle Corporation recommends that each user have a personal copy of the workbook file. This is preferable to a situation in which multiple users access a shared copy.