footnote storage int relational tables

[Olivier Chapiteau]

There's a lack in database design for storing footnotes elements into the relational part.

The PROJECT_CUBE_NOTES table can store notes but only for the OLAP part : it's not a appropriate place to store during the data import AS THE CUBE COULD NOT EXISTS YET during data loading.

Can we add into GHO database schema the column ACTIVITY_MESUREMENTS.MESUREMENT_NOTE_ID related to NOTES.ID ?

and then creating notes in NOTES table and connecting into ACTIVITY_MESUREMENTS entries.

[Philippe Boucher (boucherp)]

If foot notes cant be linked to relational data, then that's a problem - it breaks the model where we want to be able to load and process data in stages without nescessarily having to build the final cube - can T4Bi confirm that foot notes are not attached to relational data and if so, what's involved in fixing this?

[Jiri Dvorak (jiri-dvorak)]

There are several types of notes supported by Open Health - notes identified via a single dimension (such as Country Notes, identified by Country Code, which is a field in the table COUNTRIES), and notes identified via multiple dimensions (such as location, time, indicator). In each case, the dimension tables ("reference tables") exist before data is loaded into the Staging Area (and obviously before a cube is built); many tables and a lot of code in the Staging Area references the Dimension tables.

While it is true that the translation of the MDX-style code value into a NOTE record is optimized primarily for the cubes, the relationship is perfectly valid in any data or code that can "see" the Dimension tables.

The only ultra-dynamic and (IMHO) somewhat artificial situation where there would be a dependency on the cube would be if any dimension values (e.g. Time dimension values) would be created only when the cube is being built, e.g. via an extrapolation algorithm - I sincerely hope that GHO will not be implementing that feature anytime soon.

Obviously, the developer using the Notes-to-Dimension links in Staging Area (or any non-cube) situation has to understand the data model and use the proper joins to link the data.

[Olivier Chapiteau (olivier-chapiteau)]

Jiri, i think your last comment is related on different issue ? : 490404 (https://bugs.launchpad.net/gho/+bug/490404), this other bug talk effectivly on storing footnotes but related on dimensions.

The problem we point here is a miss of place in the relational schemas for storing footnotes on data entries, so : activity_mesurements table entries.

What we need is to add the column ACTIVITY_MESUREMENTS.MESUREMENT_NOTE_ID related to NOTES.ID.
Do you agree Jiri ?

[Jiri Dvorak (jiri-dvorak)]

Adding ACTIVITY_MESUREMENTS.MESUREMENT_NOTE_ID will work, as long as you'll allow only one note per value (Many-to-One relationship).

If the system allows multiple notes per value, then you need either a cross-reference table (Many-to-Many relationship), or a mechanism that will:
(1) check for unique usage (=more than 1 ACTIVITY_MEASUREMENTS records referring to the same NOTE.ID)
(2) concatenate all notes into a "composite string"

Another option, as implied in my earlier note, is to link notes to a combination of dimension values - instead of linking it to ACTIVITY_MEASUREMENTS, you would link it to "Country=Albania AND Year=2006 AND Indicator=XYZ". Having said that, this "dimensional" approach will become increasingly demanding for cubes with many dimensions (e.g. 5 or 6-dimensional cubes).

[Olivier Chapiteau (olivier-chapiteau)]

thanks Jiri, for your answer,

As the goal of the gho load process is to load only into the relational schemas and not into the olap part, because the conversion into olap is a handled by another process, we will add the relation ACTIVITY_MESUREMENTS > NOTES.