settingsLogin | Registersettings
Show Menu

How do I model dynamically generated tables (HUBS)?

0 votes
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,
asked Oct 22, 2017 in Modelling by shawnmandel (120 points)

1 Answer

0 votes
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
answered Oct 26, 2017 by simco_admin (660 points)
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license. | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke