Question

One of my clients deals with educational institutions and student test evaluations for the purposes of improving teaching methods for instructors.

The challenge is that when students respond to teacher evaluation questionnaires, the questions can be customized by the teachers.  The concept of Project (HUB) designates a particular semester and is a centralized HUB.  The concept of Response (HUB) is a challenge, since it is dynamically generated by the client ERP/ETL tool.  For example, when a response is generated by a student responding to a questionnaire, a dynamic Project ID (PID Key) is created for the particular dynamic response table.  For example, PID+Response would be a dynamically generated table.  It has other tables that give it context and that store history which can be considered as the SATELLITES.

Sorry for the long winded explanation, by I had to give the back story prior asking my question.  So, my questions are:

- How do I model for these dynamically created tables which create these Project autokeys all over the place whenever students respond to questionnaires?  
- They can have about 100 of these dynamically generated tables based on the customized questions as defined by the instructors.  Would this be modeled as multiple HUBs in the Data Vault?
- If so, doesn't this increase the complexity of the DV model by having these dynamically created HUBs in the model?

Any guidance would be very appreciated.

Thanks kindly in advance,
Shawn

Answer 1

Hi Shawn,

In my opinion you should try to generalize the output of the tool to 1 table format. This is off course out of scope for data vault but should make your life much easier.

Before you stage and try to load the data from different tables, try to load all of them into 1 generic table for example Responses, containing the PID key (original table name) and the other columns.

If the columns of the tables are also dynamic the challenge will become bigger, possibly you need to map columns and values to make it generic.

However, at some point they must be generalized, because other wise the data will not be useful I guess.

Probably not ideal answer you waited for, but I hope it helps a little.

Greets Jacob Siemaszko