OFSAA Data Models are present in Erwin. Erwin also helps in the creation of data model documentation.
Topics:
· Impact of erwin use on the Data Model Documentation
Since OFSAA version 5, the data model has been defined and delivered as an Erwin file. erwin is a data modeling tool or environment on which, OFSAA currently depends. It is used to define all data model artifacts, both Logical and Physical data models, which are used by the OFSAA applications, as well as the Oracle Financial Services Data Foundation.
In the prior versions of OFSAA, the data model was documented in PDF form, and shipped separately from the actual application artifacts. Starting from OFSAA 5, this has changed. The primary store of all data model related documentation will be the erwin data model file. The reasons for this are as follows:
· erwin permits complete definition of model-related documentation within the model itself. In other words, the model is now ‘self-documenting’.
· In many cases, clients typically perform several customizations to the model. This causes any fixed model documentation to progressively become outdated or inaccurate, as additional fields or tables are added to the data model for client-specific needs.
· All OFSAA applications share a common physical data model, which is also shipped as part of the OFSDF. As clients may license independent OFSAA applications or OFSDF at different points in time, erwin permits these models to be merged in a controlled manner to account for site-specific changes as well as release-specific changes from Oracle.
Model documentation can then be generated for the combined, updated model, and therefore stays up-to-date with both the latest internal (client-specific) and external (product- driven) changes.
This guide explains the process of generating two important documentation artifacts from within erwin.
The data dictionary is a detailed listing of all tables/columns constituting a specific OFSAA application. Today, the erwin file containing the OFSAA Data Models provides the following information relevant to the creation of a data dictionary:
· Subject Areas in each part of the data model
A subject area is a specific grouping/organization of the data model content. It is a high-level categorization scheme that shows which application module or business analysis areas are served within the model. The tables and columns defined during the design process are grouped into these categories for easier navigation as well as comprehension of the model.
· Table/Entity definitions
§ Detailed definitions of all tables
§ Detailed, column level dictionary information including column specification, datatypes, as well as several User Defined Properties (UDPs) that provide additional metadata added during the design process to categorize a specific data model object.
· Data Model diagrams
§ Associated with each data model (either OFSAA or OFSDF) is a collection of data model diagrams (Entity Relationship diagrams). These are usually specific to a subject area, and provide a visual representation of the key entities, their relationships and keys.
As indicated earlier, the OFSAA Data Model is a common data model supporting multiple OFSAA applications. The organization of the data model is split into 3 distinct areas.
Figure 1: OFSAA Data Model Organization
· Staging Area: This schema is the common data sourcing layer across all OFSAA applications, and also the OFSDF.
· Processing Area: This refers to application-specific schemas that are exclusive to each OFSAA application, and provide storage/structures for intermediate outputs and application-specific configuration and setup data.
· Results Area: This refers to the collection of star schemas with conformed dimensions that support BI/reporting from aggregated outputs from the processing area.
The use of the common sourcing layer means that a single table in the staging area can serve multiple and different analytical applications/processing engines within the OFSAA architecture.
Therefore, it is necessary to know how specific applications map to specific staging tables/columns, and what subset of the complete unified staging data model is actually used by each application.
The download specification is a supplemental document to the data dictionary that provides this listing. For each staging table/column, it provides key information such as definition, datatype, and so on. Additionally, it maps each column in a specific staging table to the OFSAA applications/components that use this column, and also indicates whether this column is mandatory or optional.
This provides implementers with a scoping documentation for data sourcing that specifies the data requirements applicable to a particular solution, for which the sourcing scripts need to be developed.
This is done by means of specialized metadata within erwin called User Defined Properties (UDPs), which are additional tags that a data model designer can attach to any data model object in erwin. For additional details on UDPs please refer to the erwin Data Modeler documentation.
To generate specific documentat described earlier, use erwin Model Reporting Capabilities, which allows Data Modelers to produce customized reports of various Data Model artifacts.
erwin allows the generation of model-documentation in various levels of detail, and various formats like HTML, Text, PDF, and so on.
The generation process involves installing erwin reporting templates for the Data Dictionary and Download Specification, and executing these templates, with customization of the outputs as required.
NOTE:
This guide is not an exhaustive User guide for erwin’s reporting capabilities, but only details the specific steps to apply them to generate OFSAA/OFSDF documentation. Please refer erwin documentation or Contextual Help within erwin for more details.
This section provides the procedure to generate a report from the erwin Application by designing a new template. For illustration, generation of the PMG Report is mentioned.
To generate the PMG Report using the erwin Application, follow these steps:
1. Open the erwin Application.
2. Select the
Tools Menu and select Report
Designer.
The erwin Report Designer Window is displayed.
Figure 2: Select Report Designer in the erwin Application
3. In the erwin Report Designer Window, select the File Menu, and select New Report.
The Report Editor Window is displayed.
Figure 3: Select New Report in the erwin Report Designer Window
4. In the Report Editor Window, do the following:
a. In the Name Field, enter a name for the Report.
b. In the Report Type option, select Logical/Physical.
c. In the Report Design Tab, in the Select Report Subject section, expand the Model Tree and select Entity/Table. The corresponding fields are listed in the Select Report Fields Section.
d. For the PMG Report Type, in the Select Report Fields Section, select the following fields:
— In the Properties Sub-tree, which consists of the table related fields, select the Comments, Name, and Physical Name Fields.
— In the Attribute/Column Sub-tree, expand Properties and select the Domain Parent, Comments, Name, Null Option, Physical Data Type, and Physical Name Fields.
— In the Attribute/Column Sub-tree, expand Key Type and select the Is FK and Is PK Fields.
Figure 4: Report Editor Window
e. After selecting all the required fields, click OK.
5. The Report
is created and listed in the Report Explorer Section. To generate the
selected fields in a CSV file format, select the created Report and then
select 
icon.
Figure 5: Report Generation Process
6. The processing begins and in an Excel file, the Report is generated in the CSV format.
Figure 6: Sample PMG Report
7. Save the generated file in the default Excel file extension format (.xlsx).
8. Save the generated new Report as a template. To save the generated new Report as a template, follow these steps:
a. After selecting
all the required fields and before exporting the Report to the CSV file
format, in the erwin Report Designer Window,
select the Report and select the 
icon.
Figure 7: Select Save Solution
b. The Save As Dialog opens. In the File name Field, enter a name for the Report file. The file extension in the Save as type needs to be erwin Report Solution Files (*erps). To save the Report as a Solution (or template), click Save.
Figure 8: Save the template
To generate any type of Report, follow the same procedure. Select the required fields in the Select Report Fields section based on the Report type.
This section provides the procedure to generate a report from the erwin Application by using an existing report. For illustration, generation of the OIDF_DL-Spec is mentioned.
To generate the DL Specifications Report using the erwin Application, follow these steps:
1. Open the erwin Application.
2. Select the
Tools Menu and select Report
Designer.
The erwin Report Designer Window is displayed.
Figure 9: Select Report Designer in the erwin Application
3. To open a designed Report, in the erwin Report Designer Window, select the File Menu, and select Open solution.
Figure 10: Select Open Solution in the erwin Report Designer Window
4. In the Open Dialog, navigate to the location containing Reports. Select the required Report, which is in the .erps file format and click Open.
Figure 11: Select an existing Report
5. The selected Report is listed in the Report Explorer Section. You can select any new fields or remove any existing fields in the Report Editor Window. To make the changes, right-click on the Report and select Properties.
The Report Editor Window is displayed.
Figure 12: Select the Report Properties
6. In the Report Design Tab, in the Select Report Subject Section, expand the Model Tree and select Entity/Table. The corresponding fields of the Report are listed in the Select Report Fields Section. You can select any new fields or remove any existing fields. After making required modifications, click OK.
Figure 13: Select or remove the fields
7. To generate
the selected fields in a CSV file format, select the Report and then select
icon.
Figure 14: Export Report
8. The processing begins and in an Excel file, the Report is generated in the CSV format.
Figure 15: Sample DL Specifications Report
9. You can save the generated file in the default Excel file extension format (.xlsx).