AX1091

Exposing table data for use in the Data Explorer

The Data Explorer is intended to be used for quick and easy data analysis. Therefore, instead of exposing all data in your system at once (which may be overwhelming for users to sift through and find what they want), the Data Explorer uses the concept of data sets to define a distinct set of data available for analysis. Administrators must define one or more data sets to enable use of the Data Explorer.

Data sets are based on table columns. When you create a data set, you pick columns from one or more tables. Users can then drag and drop those columns as desired within the Data Explorer. Any data outside of those columns is not available in the Data Explorer while the user is working with that data set.

In order to expose data for use in the Data Explorer, you must do the following:

  • Review the column classification for any column that you plan to include in a data set, and adjust as needed.
  • Define one or more data sets to make available for use in the Data Explorer.
  • (Optional) Define intuitive display names for columns in data sets so that users can more easily understand what is in each column.

These activities are performed using table management features in the Excel Client or the Windows Client.

Defining column classifications

Each column in a table is assigned a classification. This classification is used to determine whether the data in a column is considered to be values or dimensions:

  • Value: Value columns contain reportable data such as budget or actuals data. For example, most columns in a data table such as GL2018 are value columns, such as M1 through M12, as well as calculated columns such as CUR, TOT, and YTD.

  • Dimension: Dimension columns define the levels at which you want to group and display the value data. For example, the key columns in a data table such as GL2018 are dimension columns (such as Acct and Dept). Columns in lookup reference tables such as Dept and Acct are almost always dimension columns as well. In addition to the Acct and Dept key columns, this would include columns such as AcctCategory, Company, Region, VP, etc.

When using the Data Explorer, value columns define the data values to be reported on, and dimension columns define the row groupings (and optionally column groupings). For example, you can drag a dimension column to the rows or the columns of the Data Explorer, but you can't drag it to the values. Similarly you can drag a value column to the values of Data Explorer but not to the rows or columns. Filters can be defined using either value or dimension columns.

All columns are assigned a classification by default, but you can override this classification as needed. The default classifications are as follows:

  • Numeric columns are classified as Value by default.

  • Integer columns (all types) are classified as Dimension in reference tables. In data tables, non-key, non-validated Integer columns are classified as Value, otherwise they are classified as Dimension.

  • All other columns are classified as Dimension.

NOTE: All columns in document reference tables are assigned Dimension as the classification. This cannot be changed.

If the default classification is not appropriate for a particular column in a data or reference table, you can change it in the column properties. To edit column properties in a table, you must either be an administrator or have the Administer Tables permission (as well as access to the table).

To change the classification for a column:

  1. On the Axiom tab, in the Administration group, click Tables and then navigate in the Table Library to the desired table. Select Edit Table Structure for that table.

    TIP: If you are in the Axiom Explorer table view, you can right-click a table to edit the table structure.

  2. In the Edit Table dialog, click the Columns tab.

  3. In the left-hand pane of the Columns tab, select the column that you want to change. Then in the right-hand pane, scroll down the list of properties until you locate Column Classification.

  4. Select one of the following options for Column Classification:
    • Default: The column will use the default classification for this type of column. The default classification for the current column is listed in parenthesis.
    • Dimension: The column is considered to be a dimension column, regardless of its default classification.
    • Value: The column is considered to be a value column, regardless of its default classification.

  5. Click OK to save.

Column display names

By default, the columns in the data set display in the Data Explorer using their actual column names. If desired, you can define more intuitive display names that will be used instead of the column names. This may make it easier for non-technical users to identify the data that they want to see. The user can still see the Table.Column name in the tooltip when they hover over a display name.

To define a display name, use the Hierarchy Display Name property for the column. This display name is also used by hierarchies (such as in the Quick Filter and Filter Wizard). Make sure that the defined display name is appropriate for all places where it will be used.

To edit column properties in a table, you must either be an administrator or have the Administer Tables permission (as well as access to the table).

To define the display name for a column:

  1. On the Axiom tab, in the Administration group, click Tables and then navigate in the Table Library to the desired table. Select Edit Table Structure for that table.

    TIP: If you are in the Axiom Explorer table view, you can right-click a table to edit the table structure.

  2. In the Edit Table dialog, click the Columns tab.

  3. In the left-hand pane of the Columns tab, select the column that you want to change. Then in the right-hand pane, scroll down the list of properties until you locate Hierarchy Display Name.

  4. Type the desired display name into the Hierarchy Display Name field.

  5. Click OK to save.

Defining data sets

Data sets define collections of table columns that are available for drag and drop analysis within the Data Explorer. When users create new Data Explorers, they first select which data set they want to use. They can then report on any data within the value columns in that data set, and they can group and display the data using any dimension columns in that data set.

When defining a data set, you should consider the following:

  • What is a useful and distinct set of data that users may want to perform ad hoc analysis on? Data sets are most useful when they are focused on a particular area of interest to help users answer a specific set of questions. Including all of your data in a data set may make the Data Explorer difficult to use.
  • Once you have decided on the data, which specific columns should be included in the data set? Generally speaking, you want to limit the data set to only the necessary columns so that users can quickly and easily identify what they want to see and how they want to see it, without sifting through endless lists of columns.
  • What name should the data set have, so that the contents and purpose of the data set will be obvious to users? For example, instead of naming the data set "Budget Columns", you might want to name it something like "Analyze Budget Data".

To define data sets, you use the Data Set Manager. This tool is available from the Tables > Table Administration menu, and is only available to administrators or users with the Administer Tables permission. For more information, see Data sets for Data Explorer.