Denormalizer

This module denormalizes entities using database views or tables. Also known as star schema, snowflake schema, snowflaking, etc.

This is a tool used to assist business intelligence tools in consuming Drupal data without knowing Drupal internals (like how to join fields).

Inspired by http://drupal.org/project/etl with a different approach. Denormalizer does not use entity_load and also does not perform the "Load" part of ETL.

Warning: as this module creates databases and performs destructive operations on tables and views, take extra caution when using this module. There are sanity checks to prevent you from overwriting your own tables, but you've been warned!

To use, enable the module.

Add a hook to your custom module to define the data you want to denormalize. See hook_denormalizer_info():

/**
 * Implements hook_denormalizer_info().
 *
 * Provide a list of plain tables and entities that should be denormalized.
 */
function hook_denormalizer_info() {
  return array(
    // Plain Drupal table
    'location' => array(
      'base table' => 'location',
    ),
    // Typical entity
    'denormalized_table' => array(
      'entity_type' => 'node',
      'bundles' => array('page', 'story'),
      'changed_key' => 'changed',
    ),
    // Some external table
    'ScormActivity' => array(
      'base table' => 'ScormActivity',
      'external' => TRUE,
      'changed_key' => 'update_dt',
    ),
  );
}

Visit admin/config/development/denormalizer

Set prefixes and where you want your views or tables to be.

- View mode will create database views.
- Table mode will create tables and SELECT data into them. On the next run, they will incrementally update based on the primary key and the "changed" field.
- DB determines where these views or tables live. This is useful if you want to restrict access to a BI or ETL tool by putting denormalized data in a new database.

Use the "Create" tab to create/update views or tables.

Use the "Export" tab to see the SQL that will be used.

Related modules

• Views fast field - build Views without entity_load()
• Singer - export denormalizer info as a singer catalog file