Freezing hierarchies

To report on sales roll ups and incentive compensation, you may want to “freeze” the hierarchies over time. To do this, you can take snapshots of hierarchy data using transformation queries. The data is stored in a custom table so you can compare it with data from other time periods.

You can schedule a snapshot to be created for any time period; for example, weekly, monthly, or quarterly.

Creating snapshots of hierarchy data

Taking snapshots is a process that you can automate.

Complete the following tasks:

  • Create transformation query- Use a transformation query that will generate a custom table as the output.

  • Create a target subscription - Link the transformation query to a target subscription. When the subscription runs, the custom table will be created; no data will be exported.

This is a one-time setup. You can schedule the target subscription to run at any interval to take snapshots for the required time periods.

Transformation query

Create a transformation query that saves the query output as a custom table. For this example, we'll create monthly snapshots.

  • To create the query, in the Admin console, click System Interfaces > Transformation Queries and click Add Query.

For detailed steps for creating outbound transformation queries, see Transforming outbound data.

Details

In this section, define a meaningful name and description and choose the Outbound query type.

Example

Query Output

In the Query Output section, define the following settings:

  • Output type- Choose Custom Table.

  • Table Name and Description

  • Save to Folder - Choose the folder in SQL Query Editor that the custom table will be saved to.

    Tip: You can create Shared folders to organize the tables that will be created. For example, create a folder called Monthly Snapshots. For more information, see Custom tables.

  • Table Name Options - Choose Add timestamp to table name.

Example

Transformation Query

Paste the following query into the query box. The query uses the Ownership Hierarchy reporting table.

select * from flat_ownership_hierarchy

Save the query.

Target subscription

Link the transformation query to the target subscription. When the target subscription runs, the transformation query creates the custom table; no data will be exported.

  • To create the subscription, in the Admin console, click System Interfaces > Target Subscriptions and click Add Subscription.

Details

Define the following settings:

  • Name and Description.

  • Type - Choose Data.

  • System - Choose a source system. This is a requirement for all target subscription configurations; no data will be exported.

    Tip: Add a system (System Interfaces > System) that you can use for these subscriptions; for example, create a system called CreateCustomTables.

  • Status - The subscription is Enabled by default.

Example

Note that the Data Flow View and Code do not display until the subscription is saved.

General Export Options

Accept the default settings in this section. They do not apply to the subscription because no data will be exported.

File & Field Selection

Choose Export None for the Export Options. All objects will be set to Do Not Export.

Example

Transformation Queries

In this section, click Add Query to add the transformation query that you created for the monthly hierarchy snapshot.

Example

Job Schedule & Triggers

In this section, schedule the target subscription to run at the beginning of each month; for example, at 12:00 am on the first day of each month.

Example

No job triggers are required.

Save the subscription.

The snapshots are stored in the Shared Folder in the SQL Query Editor (Reports). You can create queries using the custom table to report on changes over different time periods.