Interactive reports can be built directly over a transactional table, but can have slow performance with large datasets. Summary tables filter the transactional tables for the required data, perform logic block calculations over that data, then categorize and summarize it in meaningful ways. Interactive reports built over the summary table have much better performance since the underlying table has already done most of the data manipulation. For example, an interactive report built over an AccountReceivable table would potentially have to load 100,000 records each time the report was opened. Users would then have to use the interactive report features to filter and refine the data into meaningful patterns. Instead, a summary table built over the same tables would have up-to-date summarized information from that table already ready for the interactive report to query.
Additionally, summary tables can perform complex calculations that interactive reports are unable to do since they can use logic blocks during processing.
Records from transaction tables are tracked in real-time, then categorized in the summary tables with a dimensional modeling approach. Dimensional modeling uses metrics, dimensions, and display fields to sort records into related groupings. Each unique dimension set has a single record within the summary table that is continuously updated by records from the transaction tables that have the same dimension values.
The table below reviews important terminology for dimensional modeling and summary tables, and provides examples of how each aspect would be configured for a Receivables summary table:
| Term | Definition | Example for a Receivables summary table |
|---|---|---|
| Data Sources | The transactional tables that feed into the summary table. | ReceivablesHeader, CashReceiptsHeader, AppliedCashReceiptsDetail, ReceivableAdjustments tablesThis would feed receivable records from multiple application tables into the summary table. |
| Dimensions | The data which provides contextual information about your metrics. This is generally not transactional information like credits or debits, but values like company name or dates that help sort the transactional data into meaningful categories. Unique combinations of dimension values create the categories which the transaction records are sorted into. Unique indexes are automatically created from the dimension fields specified. | EffectiveStartDate and OrganizationalUnitThis would create a summary table record for each org unit and transaction date combination. Then, transactional records from all of the data source tables would be sorted into the summary table record that matches their effective start date and org unit. |
| Indexes | Summary table indexes are automatically created based on the dimensions you specify. They help maintain data integrity, and improve performance. Learn more in Indexes. | Having dimensions for EffectiveStartDate and OrganizationalUnit creates indexes that filter the data first by the start date, then by the org unit. This helps the system quickly narrow down and identify records based on the unique value combinations of those fields, and improves performance when the summary table is queried. |
| Metrics | The data, most often numerical, which measures aspects of your business. This is generally transactional values, such as credits, debits, or voids. Once the transactional records are sorted into their dimension set, their metric values are aggregated and summarized in the summary table record. | AppliedAmount, UnappliedAmount, AdjustmentAmount, VoidAmount, and GrandTotalThis would aggregate the applied amount, unapplied amount, adjustment amount, void amount, and grand total values from every transactional record that is within the dimension set. The summary table record shows a single, aggregated value that represents the cumulative values of all of the records that match the dimensions. |
| Display Fields | Optional fields which contain non-categorical values related to your dimensions. Display fields should provide context to your summary table data. | TransactionCurrencyThis would ensure that the currency code for the transactional records is visible in your summary table. Currency code is not a useful way to categorize the data, or a value that needs to be aggregated, but it is useful information to have present in the record. |
Summary values for metrics are continuously updated by new transactions, so when records are entered into the transaction tables, the system reviews the EffectiveStartDate and OrganizationalUnit values then updates the appropriate summary table record with those values.
Summary table types
There are several types of summary tables which provide different functionality, such as:
Cube—The most basic summary table type. Use cube summary tables when you want logic block calculations applied to records as they are processed. Results of these calculations can then be used by business users in interactive reports. Learn more in Cube Summary Table Definitions application and Cube summary table configuration.Periodic—Has all of the functionality of a Cube summary table but also allows you to summarize business data based on fiscal year and periods. Periodic tables allow complex logic to be configured when values are rolled forward from one period to the next, so accurate cumulative balances, such as year-to-date, values can be summarized, and previous year data can be omitted from roll forward behavior if desired. Learn more in Periodic Summary Table Definitions application and Periodic summary table configuration.As Of—Has all of the functionality of a Cube summary table but also allows you to summarize business data based on an As Of date or date range. As Of tables allow logic block calculations to be applied to historical values balances from a certain point in time. For example, you may want to view the open balance for a customer on January 1, 2023, and compare it to their open balance at the end of the month.
Activations
Once you've built your summary table, you must activate it with the Activate form action in the summary table definition. Activated summary tables have a record in the Summary Table Activations application. Summary tables only begin receiving data from transactional tables once they have been activated. Activated tables cannot be changed. If you do need to make changes to the underlying summary table definition, you would need to activate the table again once you have completed your changes. The updated table would then have its own record in the Summary Table Activations application.
You can have multiple activated summary tables from the same definition, but only one of those activations can be queried by the system. Mark an activation as the Primary activation in the summary table activation record to make it available for use in interactive reports.
Table lookups in summary tables
If you want a table lookup's related fields analyzed in reports built over the summary table, there are several configuration options outlined in the table below:
| If the value of the related fields at the time of record creation... | Add the... | Considerations |
|---|---|---|
| Does not need to persist if the related field subsequently changes... | Table lookup field as a dimension or display field. | If a related field changes after the primary record has been written, the original value of the related field is lost. |
| Must persist if the related field subsequently changes, even if a summary table rebuild occurs... | Table lookup's related field to the transactional table as a new field, then use that field to populate a dimension or display field on the summary table. | If a related field changes after the primary record has been written, the related field value persists with the transaction. |
| Should persist if the related field subsequently changes, but can change if a summary table rebuild occurs.. | Table lookup's related field as a dimension or display field. | If a related field changes after the primary record has been written, the related field value remains the same unless a summary table rebuild occurs. In those instances, the current value of the related field during the rebuild is applied when the primary table reprocesses the records. This method is not recommended for related table lookup dimensions which may change. If a dimension for a transaction record no longer matches its prior dimension, the system treats that summary table record as a new record. |
Track processed summary table records
Summary tables can be configured to track the status of their processing records.
This configuration can be applied to the Cube Summary Table Definitions, Periodic Summary Table Definitions, and As Of Summary Table Definitions applications.
Summary table definition
On the General page of your summary table definitions application, you must:
- Select the Enable Record Log checkbox.
- Save the definition record to prompt the system to create the Record Log Table. This is the table you query if you want record log information.
Table definition
In the Table Definitions application, you must:
- Create a new table of type
Join. - In the Join Type field, select
Left Outer Join. - In the
Primary Join Table, select the name of the transaction table from your summary table definition which you want to track. - In the Secondary Tables subtable, select the name of the table populated in the Record Log Table field of the summary table definition.
- In the Join Fields subtable, you must:
- In the Primary Join Field, specify
nwId. - In the Secondary Join Field, specify
STFDataSourceRecordId.
- In the Primary Join Field, specify
View data
Once you definition is configured, you can build either an interactive report or application over the join table. This allows you to view the record log information in the report data table of the report or the list form of the application.
Summary Table Activations application
Use the Summary Table Activations application to view, deactivate, delete, and manage different versions of summary tables, also called activations.
Multiple versions of a summary tables can be built over the same data table. These activations can run and receive data simultaneously from the underlying transactional tables while you build, test, and prove the functionality of the new table. This allows you to build the new table without having to stop using the old one. Only the primary activation is referenced by interactive reports and logic blocks when they reference the data table. Select the Make Primary Activation row action to activate a table. This automatically deactivates a summary table if it was previously marked as the Primary activation.
Open a summary table record to view information about the activation, such as the:
- Status—Indicates the current status of the activation. Only
Loading,ActiveandOut of Syncactivations receive data from the transaction tables which feed the summary table. - Error Tolerance—Indicates the number of failed events which may occur on the table before it becomes corrupted and can no longer receive data.
- Primary—Indicates which activation table is referenced by the system when the table is queried by outside applications or reports. Only one activation can be selected as the primary.
Summary Table Events application
System administrators use the Summary Table Events application to view transactions going in and out of summary tables.
Summary table best practices
These best practices ensure that summary tables are configured consistently throught Nextworld.
Create a summary table at the most general level of dimensions and metrics you can. Then, leverage interactive reports to further aggregate, pivot, and filter the data.
Data Sources
There are several considerations for your summary table data sources, including:
- If your data source is a Detail table, any applied filters should be on values from the Detail table. Only changes made in the Detail tables are tracked by the system.
- If your data source is a Detail table, you only need to define the Header Detail table if Header fields are used.
Dimensions and Metrics
There are several considerations for dimensions and metrics, including:
- If you are creating a dimension related to a table lookup, use the table lookup data item. If you use the related fields, it can become out of sync if the table lookup changes.
- If a field is both on the Header and the Detail table, it is more performant to use the Detail table field.
- Not all metrics require mappings. You may be reading multiple tables and only have one table that holds the metric, such as Debit and Credit amounts. Only map what you need to map.
Display Fields
There are several considerations for display fields, including:
- Display fields should typically be fields that describe an aspect of a metric, but are not functional categorizations. For example, if your summary table is modeling inventory items, you could use Unit of Measure as a display field since it is connected to an item dimension.
- Functionally, display fields and logic block calculated metrics are the same. Use logic block calculated metrics for numeric calculations and display fields as descriptors. For example, Percent of Total would be an appropriate Metric, while Unit of Measure is an appropriate Display Field.
- Use the format of <FieldName><Work> when naming any transaction event work fields. For example,
UnitOfMeasureWork.
Logic blocks
- Generally, it is not recommended to use Interrupt with Errors within transactional event logic blocks. This can break the summary table, depending on the allowable error tolerance.
- You should define both an insert and removal event type for your transactional event logic blocks. Removal covers cases where a transaction changes and no longer fits the summary record criteria.
- Assign your logic blocks to an event data source. If you don’t have a specified data source and then the summary table is extended in the future, any additional data sources would also be impacted by the logic block.