Creating Dimensions and Attributes for Extended Data Analysis

System administrators can define dimensions and attributes for SyteLine 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 new Dimension Table Joins form to link the appropriate primary and secondary tables to the base table. You can then use the Dimension Attributes 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 new 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 Overview

Typically the process includes these steps:

1. 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.
2. Determine which tables contain the data that the user wants. One table should be the "base" table, with 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.
3. Determine the "object" that the data will belong to.  An object can have multiple related dimensions; for example, the Chart object includes dimensions for Customer, Item, and Vendor, as each of these relate to accounts.

   Note: Currently, you can modify the predefined Chart object and its dimensions and attributes, but you cannot create new objects.

4. 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. See "Defining a Table Join" below for the steps to take.

   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.

5. 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.
6. 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. See "Defining an Attribute" below for the steps to take.

   Attributes provide the actual values that end users want to see in reports.

7. The dimension attributes must be accessible to users through a form tied to the base table. To continue our example and see how the CFO would define and use the Chart dimensions and attributes, see Specifying Dimensions and Attributes for G/L Accounts.

Defining a Table Join

On the Dimension Table Joins form, use these steps to define the table joins:

1. Specify the name of the object that these table joins describes. At the top of the form, specify the base table name, alias and primary key. (Currently, this information is display-only; only the Chart object and chart base table are allowed.) Be aware that there are more than 200 records in some of these drop-down lists, so use a partial name with a wild card to filter the list results.
2. Specify the name and alias of a table to be joined.
3. Specify the type of table join that the specified table has in relation to the base table:
   • Base Table: The principal table to which other tables will be joined.
   • Primary Table: An additional table that has a one-to-one relationship with the base table.
   • Secondary Table: An additional table that has a one-to-many relationship with the base table.
4. Specify how secondary tables are joined to other tables. Specify Inner Join only if the columns referenced in the join are not nullable and the join is on a foreign key. Otherwise, use Outer Join.
5. Select the column in the current table to use as the join, and the other table and column to join it to. The other table must already be listed in this form.
6. Click Add to display the join information in the text box and in the Join Text area of the grid.

Defining an Attribute

On the Dimension Attributes form, use these steps to define attributes:

1. Specify the object name to which the attribute applies. (Currently, only the Chart object is valid.)
2. Ensure that the information you want to add is not already included in the predefined attributes.
3. Specify a name and description for the new attribute.
4. Specify the attribute type:
   • Function: A field, calculation, or a SQL function that manipulates another attribute value or constant.
   • Attribute: The actual values that users want to see in reports.
5. Specify the value. You can either type directly in the Value text box, or use the fields and button in the right pane to build the Value expression:
   • In the Type field, select one of these options and then select the appropriate row from the grid:
     • Attribute Text: The grid lists the available attributes. When selected, the attribute name is displayed. In the Value text box, this appears as A(attribute). Using literals can be used to develop strings based on a concatenation of string values. This results in consistent descriptions that can be modified across dimensions.
     • Attribute Value: The grid lists the available attributes. When selected, the attribute value displays. In the Value text box, this appears as V(attribute).
     • Parameter: The grid displays a list of the available tables and columns that were defined for this object on the Dimension Table Joins form. Select a table and column from the list. Use the Table field to filter the grid for a specific table.
     • Operation: The grid displays a list of available operations.
     • SQL Function: The grid displays a list of the functions defined on the Dimension Functions form.
   • Click Add to add your selection to the Value text box. You can build a Value expression by combining parameters, operations, functions, and literals.
   • Literals must be typed in the text box and are designated as L(value). Literals can be concatenated with V(Attribute). For example, you could create the attribute Account Number with a value of A(Account) + L(-) + V(Account). This attribute is displayed as, for example, Account-11200.
6. For numeric or date fields, you can specify an appropriate mask in the Masking field.
7. Attributes can be hidden if they are used in calculations and not required to be displayed. This is useful when you create complex formulas.

   For example, you could define the literal from the example above and use it in a more complex literal: V(Account Number) + L( ) + A(Ledger Unit Code 1) + L(-) + V(Ledger Unit Code 1). This displays as Account-11200 Ledger Unit Code 1-123. In this example, there is no need to display the original literal, so it can be hidden.

8. Save your changes. In the case of a Chart object attribute, the attribute is then available for selection on the Chart of Account Dimensions form.

Related Topics

Example: Dimensions and Attributes