Collapsing multiple related records from Oracle DB to smaller records

[Dr. Zathras]

This is a tricky one, at least for me, and not very easy to describe. I’m using FMP 9 server, linked to an Oracle database via ODBC and accessing the data via FMP 9 client. I haven’t had any problems interacting with most of the Oracle-derived tables once they’re in FMP, but one of the tables is giving me real difficulty.

The table in question has three fields. One of the fields, called “column name” has a series of values which repeats every 4 records. A second field (column value) has the corresponding value for the “column name” in the same record. The remaining field repeats and only changes every 4 records. This is, of course, quite crazy and I need to figure out a way to separate the column name/values in each set of 4 records into a single record with 5 fields.

That’s probably clear as mud so here’s a simplified example:

Record 1

Name: Smith

Column Name: Hair

Column Value: Red

Record 2

Name: Smith

Column Name: Eyes

Column Value: Brown

Record 3

Name: Smith

Column Name: Height

Column Value: 6’

Record 4

Name: Smith

Column Name: Weight

Column Value: 180

Record 5

Name: Jones

Column Name: Hair

Column Value: Black

Record 6

Name: Jones

Column Name: Eyes

Column Value: Blue

Record 7

Name: Jones

Column Name: Height

Column Value: 6’ 2”

Record 8

Name: Jones

Column Name: Weight

Column Value: 190

etc.

I want to collapse this simplified example to two records in a new table:

New Table Record 1:

Name: Smith

Hair: Red

Eyes: Brown

Height: 6”

Weight: 180

New Table Record 2:

Name: Jones

Hair: Black

Eyes: Blue

Height: 6’ 2”

Weight: 190

I hope this explanation makes some sense, although I’m not at all sure how to approach the problem. Has anybody else had to deal with this sort of situation when interacting with an Oracle database and know of a solution?

Colin Hunter

[Colin Keefe]

This is a classic attribute/element schema:

http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

It's meant, among other things, to abstract the data model a bit, when the possible number of attributes (fields) could be infinite, and also lets you audit modifications on a per-attribute basis.

Is it really true that the only key available to you is Name? Or are you simplifying the model for purposes of explanation?

If you had a key that you could roll up the attributes with, this would be considerably simpler. I've had to monkey with this kind of Oracle data before, but the tables in question had proper foreign keys to work with.

[Dr. Zathras]

Thanks for the Wikipedia link for the definition of the Entity-Attribute-Value model and you're right - it's exactly the model I'm dealing with.

The field definitions I gave were simplified examples as you suspected. The actual database I need to convert has a total of six fields:

Award number: 6 digits - 3 digits (e.g. 123456-123)

Sequence number: 1 digit

Column name;

Column value:

Date

User number

There are 19 different column names so the database cycles every 19 records. The unique key is the combination of both the award number and sequence number. Also, since this is a live ODBC link to the Oracle database I have to make a solution which will refresh once a day to keep the pivoted data up to date with the source database.

Does this help?

[Colin Keefe]

One method would be to import all the data to a holding table (which I'll call IMPORT), with a 1-1 relationship to your TARGET table based on Award Number.

Set up an IMPORT table with the column names from Oracle.

Set up a TARGET table with the Field Names that match the Column Names you need from the IMPORT table.

Define the relationship between the two TOs, using Award Number, and define the relationship to "allow creation of new records" on the TARGET side.

Import the data into IMPORT.

The write a script to walk through the IMPORT records, setting any attributes if necessary. If no valid match exists, one will be created via the relationship definition, with a proper Award Number.

Loop

If [iMPORT::Column name="Name"]

Set Field [TARGET::Name; IMPORT::Column value]

Else if[iMPORT::Column name="Hair"]

Set Field [TARGET::Hair; IMPORT::Column value]

etc.

You could probably get fancy with this, but this brute force approach will at least get you started.

[comment]

It would be helpful to know how many records are there, and most importantly, what do you intend to with them. Off the top of my head, you could either:

• Import them into another table while validating the combination of award number and sequence number as unique (an auto-entered field). This would give you one 'master' record for each group, which could then be related to the group of records in the original table, or:

• Do a series of 19 imports, importing an attribute at a time, while matching on the unique key.

[Dr. Zathras]

Many thanks for the solution. This will certainly work for us with the existing data. Would I be correct in thinking that it will automatically cope if/when the number of field definitions in "Column name" changes?

[Dr. Zathras]

It's a new database right now with only 76 records (4 x 19 sets), but there will be more than a thousand sets of 19 added each year going forward.

[comment]

And...?

[Dr. Zathras]

We don't need anything fancy after the records have been pivoted into the new table where each Column Name "field" in the Oracle-derived table has its own field in the new table. The data in the new table should be searchable and viewable - we don't even need to write to it as the ODBC link back to the Oracle DB is read-only.

[comment]

That's not telling much - after all, I suppose it is searchable and viewable now. For example, one could imagine a form view with buttons to skip 19 records forward or backward, with data shown in a portal. Or you could just omit 18 records in each set of 19.

For a true translation of each group to a single record of 19 fields, I would go with the second option of the two I outlined above. But I am not sure it's necessary, and whichever method you choose for this is bound to get slower as records are added - unless you have a way to isolate new records in your external source.

[Dr. Zathras]

I figure if I use FMP 9's server-side script function and have it run the import at midnight I should be fine, even if I have many thousands of records a few years down the road.

Between you and Colin K I've now got a few ideas on how to tackle the problem which I didn't have before (I even know the source database is called Entity-Attribute-Value) and I think I can figure it out from here.

Many thanks to the both of you.

[comment]

Just for fun, here's another option. I don't think it can get any simpler than this.

CollapseEAV.fp7.zip

[Dr. Zathras]

Well, that's certainly a simple and elegant solution, but it appears to work only if the name/color/size are the same within each entity grouping.

I've attached my EAV output if you want to take a look at it so you can see the exact form of the data I'm dealing with (the first two groups have 19 records each and the last two have 20). I think that the loop approach that Colin K outlined will work for me, but I'd welcome any alternative suggestions if there's a better/simper solution.

EAV.fp7.zip

[comment]

I don't think you have mentioned this complication before. I assume this is just a case of some attributes missing, because otherwise there would be no way to translate this into a column/row structure.

If so, I would go with a series of imports - here's the beginning of a script (it imports the records and fills in the first two columns).

EAV.fp7.zip

[Dr. Zathras]

Wow - thank you very much. Your solution appears to require a consistent number of field names in the Column_Name field, but I can ask the Oracle database maintainer to fix this so they are all 20. Even if he doesn't do that I can probably trap the error a missing definition would cause and just make it skip to the next layout/find/import script step.

I wasn't expecting you to actually code the solution for me! I can easily finish the script to fill in the remaining columns. Again, thank you.

[comment]

Your solution appears to require a consistent number of field names in the Column_Name field

I'm not sure I understand what you mean by that. I believe all that is required is that column names are drawn from a pre-defined list. If this is not adhered to, how can you define a table to receive the results? But if some attributes of an object are missing, they will simply not be found (you do need to trap for the case of ALL objects missing the same attribute, I think).

[Dr. Zathras]

The problem, at least with the first few records is the pre-defined list for the column names contained 19 values for the first two and 20 values for the second two. I've asked the Oracle database maintainer to change this so all 4 records have a consistent number of pre-defined values for the column names. It will certainly make life a little difficult otherwise.

[comment]

I don't think that should pose any problems when importing. It is for this reason only that the simpler solution I suggested earlier wouldn't work here.

To take the example of Name, Color and Size columns: suppose there are three "records" (i.e. entities) and the first one does not have a Color attribute. As long as it has ANY attribute, there will be a record created for it during the first import. Then, when the Import Color stage arrives, only two records will be found and imported. Or, more precisely - only two records out of the three already existing in the target table will be updated with the Color attribute.