6.2.5 Microsoft Excel - Fact List

The Microsoft Excel - Fact List adaptor allows fact lists to be imported directly from an Excel workbook into DPMA.

Creating an Excel Import Template

When creating an Excel import template, the following options appear above the Tables group box:

• Tables (default): generates the Excel file without any changes.

  Fact List: enables the Variable row tables as tables option.

  Variable row tables as tables (this option is false by default):

  • If true, selected variable row tables are included as normal templates in the Excel import file.
  • If false, the table is marked as being sourced from a fact list rather than a normal template for refresh link purposes.

In all cases, a worksheet called Fact List will be created.

Automation Panel

To assign values, either drag and drop the Fact List node from the Automation Panel, or use Ctrl + T to apply the corresponding value from the Excel Fact List sheet directly to the target cell.

Value Editor

The adaptor can be edited by clicking on the Configure Adaptor button:

Excel Fact List Data Adaptor Form for Fixed Row and Column Tables:

New Variable Row Table Property

Currently, variable row tables use the "Nominate Connection" property. To support fact list imports, a new property has been added:

Use Fact List

If the connection is Excel, a Use Fact List checkbox appears below the Nominate Connection property.

Reference Tables

As with SQL adaptor scripts, dimensional information is included for reference.

• If the Fact List is used, the dimensional reference tables for selected tables are exported into the workbook.
• Worksheet names are prefixed with Ref.

Summary

• Use the new Excel – Fact List adaptor to import data directly from Excel.
• Control variable row behaviour using the new Use Fact List property.
• The Excel import template, Link Tables to Connection, and Update Import Template forms all include new options for Tables, Fact List, and Variable row tables as tables.
• Refresh Links will automatically process the Fact List worksheet and report errors if it is missing.
• Reference tables are included for dimensional clarity.

This enhancement provides flexibility: customers can use fact lists for fixed row/column tables and continue using traditional templates for variable row tables where preferred.