Examples

This page contains the information you need to configure the demonstration models / environment for the ‘virtual EDW’. To be able to do this the software and repository must be installed and available. The easiest way to understand what’s happening is to download the ERwin community (free) version which, while it is limited to 20 objects, does the job to support a sample model. All downloadable files are hosted via Google Drive, so the links redirect there.

Making sure all areas and datasets are available

The demonstration / testing set contains a sample model with a variety of exception scenarios. The way to set this up in the easiest way is to create separate databases for;

  • Source; this acts as proxy ‘source system’ / OLTP system and will contain some sample data
    • Create the ‘EDW_000_Source_Database’ database (or something of your own choosing) for this purpose.
    • The DDL (table structures) can be forward-engineered from ERwin by opening the model made available here. You can create some test records here, or use the test data generator from within the Virtual EDW tool to generate some.
    • Alternatively, the script containing both the DDL and some sample DML can be downloaded directly here as well. This is the only database that needs some data as everything will be loaded into the other tables from here.
    • If you want, you can also try out the usermanaged staging concept with some sample data provided here.
  • Staging Area; this is the Staging Area of the Enterprise Data Warehouse
    • Create the ‘EDW_100_Staging_Area’ database.
    • The DDL can be forward-engineered from this model.
  • Persistent Staging Area; this is the historical archive / history area
    • Create the ‘EDW_150_History_Area’ database.
    • The DDL can be forward-engineered from this model.
  • Integration Layer; this database will contain the Raw and Business Data Vault models
    • Create the ‘EDW_200_Integration_Layer’ database.
    • The DDL can be forward-engineered from this model.
  • Presentation Layer; this is the database that prepares the information for further consumption, in our case it houses the Raw Mart (as-is Dimensional Model).
    • Create the ‘EDW_300_Presentation_Layer’ database.
    • The DDL can be forward-engineered from this model.

After going through these steps you should now have a series of databases corresponding to the overall EDW architecture, with some sample data in the ‘source’.

Quickly uploading some sample metadata

To get started with the sample data, first make sure you have all the tables from the above models created in the corresponding database. You can create some initial sample ETL mappings and metadata by running an insert script (DML). This script populates the base mapping tables, which is then visible in the application (e.g. in the manage automation metadata screen):

metadata_management

The metadata can be ‘activated’ from here as well (press the ‘activate’ button). This pushes the base metadata into the designated metadata repository using the definitions provided.

activate_metadata

For longer-term users, this replaces the Excel sheet I used previously.

Virtualising the EDW (loading / processing the data)

When you start the application the default content is now available (assuming the database connections are configured correctly).

The easiest way to test is to start with ‘Generate Staging’, and check the results!

Run the examples