 |
 |
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|
||||||||||||
|
Structured HOLD Files – Smarter Is Better and Coming Soon
To summarize from the last issue of TFC, Structured HOLD Files will allow you to retain not only the data selected by a request but also its structure in the original files. Among the many benefits of this are the ability to rerun the same request on the subset of data selected – allowing your end users to change their requests without doing additional data downloads.
The data can be held in FOCUS, relational, alphanumeric, or XML files, depending on your application or reporting requirements. Aggregations and data roll-ups are supported and are created using normal FOCUS syntax.
Structured HOLD Files Will Also Support Efficient Structural Transformation. What Does This Mean and How Will I Use It?
Consider a college registrar preparing reports for the selection committee. From a database containing applicants' personal information, the schools attended, courses taken, and grades received at each school, the registrar is charged with delivering the data in several ways.
First, certain members of the committee will wish to see applicants listed by the schools attended, with another list of the courses taken by each applicant, ranked by highest grade received. This is what is called a denormalization transformation, wherein data from one segment is duplicated in multiple segments – in this case, the school information.
Another member of the committee may wish to see a list of students and their schools grouped by the student home states. In this case, the information in the applicant segment must be split apart or normalized, with the parent segment containing the student home states, and the child segment containing the student names and other details, including their schools.
Finally, someone may wish to see the data inverted – the third type of transformation, where the parent-child order of the original hierarchy is reversed. In this case, the listing of the applicant's courses including their school is ordered by highest grade. This is shown in the first diagram.
Regardless of the structure you report from, a single TABLE command can be used to convert any one input hierarchy into one output hierarchy, provided that transformation can be done in a single pass of the data and without accessing any input record more than once.
The real power of the Structured HOLD File and its transformation capabilities is the ability to perform all three types of transformation on the source data in one pass of the data.
How Does the Transformation Work?
All hierarchical transformations use the same technique. Qualified source data – selected by the TABLE request – is mapped into the target structure as it is read, without intermediate work files.
All other operations – roll-ups, BY sorts, duplicate eliminations – are executed on the transformed structure. Thus it appears as if the transformed structure was the original source and no transformation was required.
All four operations – extraction, transformation, sorting, and elimination of duplicates – are done in a single pass of the source data.
What Syntax Is Used to Perform a Transformation?
If no transformation is required – normalization, denormalization, or inversion – then the output structure can be inferred from the generating TABLE request and no new syntax is required.
In the event that transformation is required, which may not be evident in the generating request, the following syntax is used:
The Master File specified as MODEL contains the transformations you need and serves as a map for creating the structured HOLD file.
All transformations are defined by moving the fields referenced in the report request from the source to like-named fields in the target MODEL. Source and target fields must all have the same usage formats. If the names are not the same, the source field can be mapped to the target field using an AS clause in the request.
If the ABC and DEF fields are in different segments, as in a normalization, this simple technique is used to duplicate the field in the output.
How Is the Structure of the Structured HOLD File Described?
As indicated above, if MODEL is specified, that master file will be used as the master file for the HOLD file. Without a MODEL specification, FOCUS will create a master file based upon the fields referenced in the request and their hierarchical structure will be inherited from the structure of the source data.
Here, however, only source segments with fields referenced in the request will appear in the output, assuming that their fields were not all promoted to a higher level through roll-ups. Thus, it is not always the case that the root segment of an extracted structure will be the same as the root segment of the input, but rather depends upon the request. Also, the parent of a referenced segment may in fact be its grandparent in the input structure if the immediate parent segment was not referenced.
The following Structured HOLD File Master File field attributes are inherited from the Master Files of the source data:
These HOLD file Master File field attributes are not inherited from the source Master Files:
The relative order of fields in the source segment will be maintained in the extract Master File with the possible exception of BY fields. If the segment was sorted and all of the sort fields were either ascending or descending (not mixed), then the sort fields will be placed first in the segment, in their relative sort order, and the segment type will be Sn for ascending sorts or SHn for descending sorts.
How Are Unique Segments Handled in Structured HOLD files?
In normal retrievals, FOCUS deals with missing unique segments by creating artificial instances consisting entirely of default field values – blanks or zero. This will not be done with structured HOLD files as the object is to have the extracts resemble the sources to the greatest degree possible. An instance of a unique segment will occur in an extract only if the segment's source appeared in the input.
Are There Any Limitations on Commands Used to Create Structured HOLD Files?
Requests based upon TABLE, TABLEF, and GRAPH will all support Structured HOLD Files. The MATCH command will not support the creation of a Structured HOLD File.
When Will Structured HOLD Files Be Available?
We are currently planning to include the Structured HOLD Files feature in Release 8.1, which is tentatively scheduled for delivery around the middle of 2006.
|
