Wealth DistributionDistributed architecture, Web access, and user roles give a combination of freedom and controlBy Paul Dean
The Oracle Analytical Applications - Oracle Financial Analyzer (OFA) and Oracle Sales Analyzer (OSA) - are prebuilt business intelligence applications that use Oracle Express Server (OES) as their multidimensional OLAP data store and can be accessed by Windows or Web clients. Version 11i of the Analytical Applications supplanted version 6.3 in August. Both applications benefit from the general performance improvements made to OES 6.3.0.1, released November 1999, and now take advantage of the Aggregation Management facility introduced in that version. (See "Express Picks Up Speed" in the April 10, 2000 issue for the OES review Steve Elkins and I wrote.) The 11i release builds on the improvements made in 6.3, released in December 1999, but with a much stronger emphasis on Web functionality. This is the first release where the end-user functionality on the Web client has more or less caught up with the functionality of the Windows client - and in some areas, exceeded it. Each of the applications deserves its own review. This time, I'll focus on OFA. OFA is a financial reporting, analysis, and planning application. As such, it has all the administrative controls necessary for these types of capabilities. In addition, because of its distributed (as opposed to server-centric) architecture, it gives users the autonomy to create and manipulate their own scenarios of data - such as working with a different level of detail from what the administrator provides or headquarters requires in reports. ArchitectureOFA uses different workstation types to tailor features and capabilities by type of user, and manages the flow and control of structures and data throughout the application as shown in Figure 1 (see page 54). Here's how the architectural components fit together: Administrator. There is always one, and only one, 'Super' Administrator that has complete control of the application. Depending on the degree of flexibility and autonomy required, you can also create one or more Sub-Administrators. Administrators create and distribute common structures (such as financial account values), load and calculate data, create users, and control access. Associated with each Administrator workstation is a shared database and Task Processor. The shared database acts as the central data repository. Task Processor. The Task Processor controls the processing, submission, and distribution of data and structures around the system. The Task Processor can be run by its associated Administrator workstation as a background task, or from a dedicated workstation. Because Express allows for single-write, multi-read access to an individual database, the Task Processor sequentially controls the submission and processing of information to the shared database. Each Administrator can have one or more of each of the following subordinate workstations, depending on the user community's needs: Budget. Users have full read-write access to their own slice of data and can work connected or disconnected. What-if scenarios can be performed on this slice without affecting other users accessing the shared database. Analyst. Users access the shared database directly and can create their own documents (reports, graphs, and worksheets) and save them in their own personal database. Although the primary purpose of an Analyst workstation is to analyze existing data rather than to contribute new data, an analyst may have authorization to make ad hoc changes to data directly in the shared database. You can access Analyst workstations as well as create and view personal documents from either Windows or a Web client. External Web. As with Analyst workstations, users access the shared database directly, but don't have a personal database and therefore can't create and save their own documents. The typical user is a 'casual' one who needs only to view and manipulate existing documents or enter (and calculate) data. Excel. You can use the Express Spreadsheet Add-In to query and report directly against the shared database. In addition, the Excel Data Collection Toolkit can help you create a data entry mechanism for infrequent OFA users. The toolkit uses the Spreadsheet Add-In, an API that makes calls to standard OFA functionality, and Visual Basic for Applications (VBA). You use the toolkit to create a template Excel Workbook that you can then distribute to the user community. Some Express and VBA programming is required; the product provides samples. Sub-Administrator. Users control a subset of the application. Sub-Administrators operate in a similar way to the Super Administrator and can have subordinate Budget, Analyst, Web, Excel, and Administrator workstations. This organization lets you create a tiered, distributed architecture.
You can also install OFA in a cluster configuration with workstations on different servers (each requiring OES), to balance the processing workload or provide local processing. Implementing OFAWhen building an OFA application, the developer must create and populate its various components. These components are Express database objects such as dimensions, multidimensional arrays (known as Financial Data Items), hierarchies, and models. The majority of application development tasks can be performed from a Windows client using menu options in the Administrator workstation. Implementing allocation routines or currency translations usually requires some Express Stored Procedure Language (SPL) development; however, the Express multicube architecture lends itself well to implementing these features. A published API - for advanced users who have a working knowledge of Express SPL - is available for OFA and can be used to maintain OFA structures and supporting metadata. The main components of an OFA application follow: Dimensions (keys) provide the index to the multidimensional data. Hierarchies allow aggregation of data to higher levels of summarization and facilitate drill-down to detail. Dimensions can have multiple hierarchies. Financial Data Items (FDIs) store the different scenarios - such as Actual, Plan, and Forecast. FDIs can also be calculated dynamically; as in, Variance to Plan = Actual - Plan. OFA takes advantage of Express' multicube architecture allowing FDI dimensions to reflect their real-world business dimensionality. For example, a Salary Grade table will have different dimensions than Actual, and Planning can take place at a higher level of detail or over fewer dimensions than Actual can. FIGURE 1 Oracle Financial Analyzer architecture. 
Models are sets of equations used to perform interrelated calculations, usually between members of the Line Item (or Natural Account) dimension. Unlike spreadsheet applications, Express stores model logic separately from data, which allows the same model to be executed against different FDIs (such as Actual or Plan), or different models (pessimistic vs. optimistic assumptions) to be executed against the same FDI. Models are nonprocedural, which means that Express will determine the correct order of execution regardless of the order of the equations - useful when creating or making changes to existing models. Models can also solve simultaneous equations. This capability can be useful, for example, to a company forecasting its balance sheet. (See the sidebar "Simultaneous Equations" at IntelligentEnterprise.com ) Data entry support depends on how users are accessing OFA. Windows client, Web client, and Excel (via Spreadsheet Add-In) all support data entry. You can link models and hierarchies to Windows client worksheets, Web client data-entry forms, and Excel workbooks; letting users enter data and calculate the results before saving or submitting changes. Web data-entry forms let users enter data into multiple FDIs, support asymmetric edges (for example, display nine months of Actual and three months of Plan), and allow protection of referential data (based on write access controls). Windows client worksheets, on the other hand, are symmetric in nature and restrict data entry to one FDI. However, Windows worksheets provide out-of-the-box tools that allow users to grow, increase, and spread data, based on dimension values and hierarchies - something not available in Web data entry forms. New in 11i Windows worksheets are row-filtering attributes, providing the ability to filter the rows displayed based on a user-selected attribute, which is useful when entering very sparse data. Solves are models, hierarchies, or combinations of the two that define how an FDI should be calculated. Copy Data Profiles copy data from one FDI to another; for example, to update Forecast with the latest month of Actual or copy the current year of Actual as the starting point for next year's Plan. Group Solves sequentially run multiple Solve definitions. Group Solves can also contain Copy Data Profiles. This allows for, say, Actuals to be processed for the latest month, this latest month then copied to Forecast, and then the revised Forecast calculated, in an automated way. Oracle General Ledger IntegrationFIURE 2 Oracle General Ledger to Oracle Financial Analyzer mapping process. 
Not surprisingly, OFA is becoming increasingly integrated with the Oracle General Ledger (OGL) and is the analysis and planning tool of choice for the majority of companies with the Oracle Financials product suite. The main task in loading OFA with information from the OGL consists of mapping the chart of accounts and relational structures in the OGL to multidimensional structures in OFA as outlined in Figure 2. A series of forms in the OGL define this mapping. Information from other non-OGL sources can also be loaded into OFA; in this case I recommend that you set up the link with the OGL first.
|
 |
Most Popular This Week
IE Weekly Newsletter
Subscribe to the newsletter
| |
| ||||||||||||||||||||||||||||||||
