Creating Dimensions and Attributes for Extended Data Analysis

System administrators can define dimensions and attributes for data, based on table joins. This allows your users to analyze data using multiple dimensions. Some dimensions (Customer, Item, and Vendor) and attributes for those dimensions have been predefined, but the system administrator can create additional dimensions and attributes, or modify the predefined versions.

Use the Dimension Table Joins form to link the appropriate primary and secondary tables to the base table. You can then use the Dimension Attributes form to add columns from the base, primary and secondary tables to the list of attributes for a dimension. (An attribute can be any valid SQL statement used in a SELECT clause within the scope of the table joins.)

Use the Dimension Functions form to specify constants, SQL functions and math operands that can be used to add calculated fields to the list of attributes. The Dimension Attributes Query and Dimension Table Joins Query forms are also available.

Note: Access to the forms described here should be limited to database administrators, because they allow you to define table relationships that could allow general users access to information that should be restricted.

Process

Typically the process includes these steps:

Users tell you how they want to analyze data. For example, the CFO wants to analyze the Chart of Accounts and posted ledger transactions and drill down to information about the customers and items listed on those transactions.
Determine which tables contain the data that the user wants. One table should be the "base" table, which joins to other primary and secondary tables. If the needed data is not currently in a table, determine how it can be calculated or derived from existing data.
Determine the "object" that the data will belong to. An object can have multiple related dimensions; for example, the Chart and Ledger objects include dimensions for Customer, Item, and Vendor, as each of these relate to accounts.
Note: Currently, you can modify the predefined Chart and Ledger objects and their dimensions and attributes, but you cannot create new objects.
Use the Dimension Table Joins form to define the base table for the object and link the appropriate primary and secondary tables to the base table.
A parent form can have only one base table. Primary tables are defined as having a one-to-one relation to the base table. Secondary tables represent a subcollection; more than one result is returned in respect to the base or primary tables.

In the example above, the user wants to add dimensions to the Chart of Accounts. The base table used for Chart of Accounts is chart. At least one of the joined tables must link to the Primary Key column on the base table, in this case, acct. Use the predefined data for the Chart object as an example: the ledger table is joined to the chart table through ledger.acct=chart.acct. Other secondary tables can be joined to the base table or to another table linked to the base table. In the sample data, the matltran table is linked to the ledger table through matltran.trans_num=ledger.matl_trans_num, and then the item table is linked to the matltran table through item.item=matltran.item. The information in any of these linked tables is then available to be defined as an attribute for a dimension.
Use the Dimension Functions form to create a list of user-defined database functions that can be included in dimension attributes. Some standard SQL functions and custom functions are predefined for you as examples.
Use the Dimension Attributes form to add columns from the base, primary and secondary tables to the list of attributes for this object. You can also include constants, SQL functions, calculations, and math operands when you build an attribute.
Attributes provide the actual values that end users want to see in reports.
The dimension attributes must be accessible to users through a form tied to the base table.