In its most basic form, deploying a data model means simply applying the model directly to a database via ODBC or a DDL script. But that neglects much of that value in your model beyond the physical structure.
Here are the steps in our model deployment process. We’ve scripted these in the build tool, but they could also be scripted directly in an extended model definition (XEM).
- Run a complete model check and stop if any model errors are encountered.
- Set the model version number, save the model, and commit it to source control.
- Generate the CREATE DATABASE statement into an independent file.
- Generate DDL for the structure of the database without foreign keys and without DROP statements. This is used only for a clean build or for model comparison with a database management tool such as Redgate.
- Generate DDL for foreign keys (this allows us to drop FKs during utility/static data loads and then reapply them easily).
- Output images of the model diagrams (for publication to the wiki).
- Export model metadata in CSV format (for loading into the MDR and eventually publication to the wiki). If you’re collecting/storing business rules in PD, this step is critical to the ETL process.
- Generate the ETL specifications report.
- Generate the changelog report based on a model compare against the previous model deployment.
- Generate incremental DDL (ALTER statements) for incremental upgrades of the database (actually, this happens independently based on the build system’s requests).
- Run any additional reports or list reports designated as part of the deployment.
