Skip to main content
Language Selector

  1. Other Languages

    (machine translation)

Planview Customer Success Center

Data Warehouse Export

  1. Last updated
  2. Save as PDF

Overview

Note: This feature incurs additional fees.

With your business growing, the need to analyze data from multiple sources becomes more critical. It helps you answer questions, such as what is your ROI by customer, or project? Are your customer support initiatives improving your customer lifetime value (CLV)? Often, these are questions that you cannot answer by analyzing data from a single source.

Jump to

Benefits

  • Share work management data with other BI external sources (e.g. Salesforce.com)
  • Keep historical data-snapshots and perform trend analysis
  • Perform complex queries and in-depth analysis
  • Use the reporting tool of your choice

Supported Export Types

AdaptiveWork supports export of data to:

  • Amazon Redshift
  • Amazon S3 (as a flat file, e.g. CSV, JSON)

    Flat files must first be saved to Amazon S3, then downloaded locally if needed.

  • Box (as a flat file, e.g. CSV, JSON)

    A Box account is required before you can configure the export functionality.

  • Azure Blob Storage
  • SFTP Server (as a flat file, e.g. CSV, JSON)
  • Google Cloud Storage (as a flat file, e.g. CSV, JSON)

Supported data

In AdaptiveWork, you can schedule a data export on a regular interval of all entities of your choice that are exposed by the API.

Incremental Updates

For each entity type, AdaptiveWork stores a timestamp of the last export. The next time the export runs, it will get from AdaptiveWork all the entities that were created/modified since the previous run, and will export the data.

Secured Data

AdaptiveWork data is protected during the export process by Amazon security policies.

Before You Begin

In order to configure Data Export and create/update new tables, you will need the following permissions for these three applications:

mceclip0.png

 

Setting up Data Export

To configure data export, do the following:

  1. Log into AdaptiveWork as an administrator.
  2. Go to Settings > Extensions and scroll down to the Connected Web Apps section.
  3. Navigate to DWH Export, click Open. The DWH Export window opens.

Step 1

  1. Select and enable the Item Types that you want to export. For example, Cases.
  2. Enable the Links for data you want to export.

Step 2

  1. From the Database Type drop-down list, select where you want to export the data to:
    • S3 - Flat files only
    • Box - Flat files only
    • Azure Blob Storage - Flat files only
    • Amazon Redshift
  2. Enter a Database name
  3. Enter a target schema name, such as AdaptiveWork.
  4. Enter the host, port number, and credentials for your target database account.
  5. Click Test Database Connection.

    Note: AdaptiveWork recommends performing this test to ensure successful export to your selected database. If the connection fails, make sure you’ve entered your database information correctly and try again.

Step 3

S3 and Amazon Redshift

  1. Enter a bucket name where the data is to be stored.
  2. Enter a prefix if you want to store files extracted from AdaptiveWork in a subfolder within the Bucket. Enter the main folder and the sub-folder in this format: main/sub
    mceclip0.png
  3. Select the File Type.
  4. Select the region where the files will be uploaded to.
  5. Enter the AWS Access Key.
  6. Enter the AWS Secret Access Key.
  7. Click Test Cloud Storage Connection.
    Note: AdaptiveWork recommends performing this test to ensure successful export to your selected cloud storage system. If the connection fails, make sure you’ve entered your account information correctly and try again.

Box

  1. Click Associate with Box Account and log into Box.
  2. Optional - Use a prefix to store AdaptiveWork extracted files in a subfolder under the selected folder.
  3. Select the File Type.

Azure Blob Storage

  1. Use a Connection String or Shared Access Signature (SAS) to connect to Azure.
    • Connection String: Enter the Connection String you generated in Azure.
    • SAS: Specify the Shared Access Signature.
  2. In the Container, specify the target container name (case sensitive). If the container is not found, it will be automatically created.
    mceclip1.png
  3. Click Test Cloud Storage Connection.
    Note: AdaptiveWork recommends performing this test to ensure successful export to your selected cloud storage system. If the connection fails, make sure you’ve entered your account information correctly and try again.
  4. Select the File Type.

Step 4

  1. Select Result Handling options.
  2. Select the frequency of the export.
  3. Choose whether you wish to be notified whenever a data warehouse export is completed.
  4. Select or enter the email where you’d like to receive the notifications.
  5. At the bottom of the page, click Save to save your settings.
    Note: You can click Test the Service to check if your configuration is correct. Otherwise, you can wait for the scheduled service to run.

Extraction of Deleted Time-phased Data

What's New

Extract deleted time-phased data and resource links using Data Warehouse Export.

Benefits

  • Track changes to the data (including the deletion of time-phased data and resource link records) during incremental data extractions
  • Use the data for audits and analysis in external tools

How it Works

The Deleted data records are now extracted for the following time-phased and resource link entities:

LaborResourceTimePhase FixedPriceResourceTimePhase

  • RLTimePhase
    • RLTimePhaseMonthly
  • ResourceLinkFinancial
    • FixedPriceResourceLink
    • LaborResourceLink
  • ResourceTimePhase
    • LaborResourceTimePhase
    • FixedPriceResourceTimePhase

Extracting Currency Types Supported in Data Warehouse Export

The currency type can now be exported as part of the Data Warehouse export file.

In the Data Warehouse settings, select Enable multi-currency mode to include a Currency Type (“_currency”) column for each local currency in use.

Step_4.png

Note: The setting is only available for accounts using multi-currencies (system setting 9.8 Enable multi-currency).

Export results with the multi-currency mode disabled:

b.png

Export results with the multi-currency mode enabled:

c.png

Data Warehouse Export Structure and more

When data is exported using the Data Warehouse, each entity has its own folder (unless export to root  folder was selected) that includes the data that was updated since the last export.

Each folder contains X files with new and updated items, and Y files with deleted items, which include the record identifier, date of deletion, and the user who deleted the item.

Most of the entities are stored in the ‘Recycle Bin’, meaning that when the user deletes it, the data is not deleted from the database, but logically marked as deleted. This means that the data can be restored by a user. Restored data appears in the next export as an update.

Preventing Duplicate Data Export

           

NOTE

This feature is in Beta  / Controlled Availability. To request early access, contact your Customer Success Manager. Planview, at its own discretion, reserve the right to accept or reject a customer request to enable a feature that is in Beta / Controlled Availability (i.e. “Beta software”) state. If a customer’s request is approved,  an addendum to the Planview agreement will be required prior to the enablement of the feature in the customer environment, to address legal terms related to the use of Beta software.

           

To prevent duplicate data being exported, reducing file sizes and exports time the Data Warehouse configuration UI has been upgraded to include additional detection checks and clear indicators that help guide users to avoid duplicate entities selection. 

  1. When accessing the DWH settings page the system will automatically detect and highlight any collisions. If detected, the child entity type would be set by default to eliminate duplicates during the export. 

A screenshot of a computer Description automatically generated with medium confidence 

  1. Detailed warning log, located below the entities list, highlights any issues which requires attention with quick navigational access to the effected Item Types. 

A screenshot of a computer Description automatically generated with low confidence 

  1. The system automatically disables any Item Type based on selected items. The reason is displayed when hovering over the toggle.  

A picture containing text, font, screenshot, line Description automatically generated 

  1. Entities with sub hierarchy will show an icon indicator which on hover will display detailed information and the list of the child elements (including their children's children).  

A screenshot of a chat Description automatically generated with low confidence 

Item Types hierarchy

The complete Item Types hierarchy diagram is available in the attached PDF file Item Types hierarchy.pdf. It should be read as the following: 

  • From the bottom to the top of the tree, only one element could be selected 

  • There is no limitation on the neighbor elements selection (per one parent) 

  • Root element selection disables any child selection 

For example, we have selected Work Item, Generic Task, Task, Recurring Task, and Project item types. It would automatically detect 5 collisions (see the rules above). By going from the bottom to the top rule, both Task and Recurring Task have Generic Task and Work Item collision. At the same time, the Project cannot be selected together with Work Item.  

Following the default system behavior, the report will use only Tasks, Recurring Tasks, and Projects to prevent collisions in this case. 

A picture containing text, screenshot, font, diagram Description automatically generated 

To control the export, we may select Milestone, Task, Recurring Task, Project, and Program as a workaround.