Customizing Fact and Dimension Tables

This chapter describes how to configure the OLAP tables with facts and dimensions that are the source for the Dashboard components in DirX Audit Manager Classic.

The default configuration of these tables is located in the following folder:

install_path/conf/defaults/fact-configuration

It is evaluated by all components that create or update facts and dimensions, including:

The fact population job in the DirX Audit Server. It is responsible for regularly filling the tables. See the section “Managing Fact and Dimension Tables” in the DirX Audit Administration Guide for a description of its schedule.
The command line tool db_fact_population. It is intended to calculate the facts and dimensions for previously collected or for re-imported audit messages. See the section “Populate Fact Tables” in the DirX Audit Command Line Interface Guide for a more detailed description.

Note that both components create the fact and dimension tables according to the configuration when necessary. But they don’t delete existing columns from tables when they are removed from the configuration.

About Fact and Dimension Tables

The OLAP cubes are essentially fact tables with columns for facts and columns for dimensions.

The table names for facts and dimensions should follow these rules:

Fact table names start with FCT_
Dimension table names start with DIM_

The next sections describe fact and dimension tables for audit messages and history entries.

Fact and Dimension Tables for Audit Messages

A typical fact table for audit messages has the following items:

Three fact columns. Most often these are FCT_TOTAL, FCT_SUCCEEDED and FCT_FAILED for the count of all, succeeded and failed events associated with the table. A calculated FCT_FAILED_RELATIVE can provide the relative number of failed events. For audit messages dealing with approvals, the facts FCT_APPROVED and FCT_REJECTED for the number of accepted and rejected approvals are used.
The mandatory dimension DIM_DATETIME. It contains the day as timestamp with time 12am.

A list of other dimension columns which are specific to the fact table. These columns frequently include the dimensions for the operation and the Where_From_Application. These dimension columns must follow the naming schema: table_name_ID.

The following diagram shows the columns of the fact table for audit messages related to accounts with the dimension tables for operation and application:

Figure 1. Fact and Dimension Tables Example

For more information, see the section “Configuring Fact Tables”.

A fact column contains numbers: the count of all (or all succeeded or failed) audit messages associated with that table and with the dimensions of that row. The dimension columns contain a foreign key reference into the corresponding dimension table.

All of the dimension tables have the same columns:

VAL – contains a value for that dimension. It is restricted to a maximum of 255 characters.
DISPLAY_NAME – by default, this column contains the same value as the VAL column. It is used when the dimension is displayed in DirX Audit Manager Classic and can be changed to a customer-specific value.
table_name_ID – contains the primary key for the dimension value. It is used as the foreign key reference from the dimension columns in the fact tables. Note that this is the same column name as the one in the fact tables that contains the foreign key to this dimension table.

Note that due to restrictions for Oracle Database in previous versions, the maximum number of characters for a dimension table name is 23. The reason is that DirX Audit must use a trigger for generating the primary key in the dimension tables. The trigger name has the structure GEN_table name_ID and Oracle Database in previous versions supports trigger names with a maximum length of 30 characters.

A sample row for the table FCT_ACCOUNTS might have the following values:

FCT_SUCCEEDED: 9
FCT_FAILED: 0
FCT_TOTAL: 9
DIM_DATETIME: 2011-02-21 12:00:00.000
DIM_OPERATION_ID: 6
DIM_APPLICATION_ID: 3

The fact table contains only audit events related to accounts. The facts of this row correspond to audit events produced on February 21, 2011, which are associated to the operation with primary key 6 and application with primary key 3. There are 9 audit events matching these conditions. All of them were successful, none failed. Looking for the primary keys in the dimension tables, we might find the application (here: target system) “Intranet Portal” in the table DIM_APPLICATION and “Add Object” in the table DIM_OPERATION.

Fact and Dimension Tables for History Entries

Tables for fact and dimensions associated with history entries are much the same as those for audit messages. The tables for facts and dimensions for history entries are saved in the DirX Audit History Database.

The table names start with FCT_HST_ for fact tables and DIM_HST_ for dimension tables.

Fact tables on history entries have the following mandatory columns:

The dimension columns DIM_DATETIME and DIM_MONTH. They contain the day as a timestamp with the time 12am. The column DIM_MONTH is empty for all days except the last day of the month or the current day.
The only fact column FCT_HST_TOTAL. It contains the number of entries existing at the end of the day and matching the other dimensions. For history entries dealing with certifications, there are additional facts FCT_HST_CERTIFIED and FCT_HST_UNCERTIFIED for the number of certified and uncertified entries. For history entries dealing with users, there are additional facts FCT_HST_WOUT_ROLE, FCT_HST_WOUT_PERMISSION, FCT_HST_WOUT_GROUP and FCT_HST_WOUT_PRIVILEGE for the number of users without a role, a permission, a group and any privilege. For history entries dealing with approvals, there is an additional fact FCT_HST_DURATION_MI for the length of approval duration in minutes. The record with a non-empty DIM_MONTH shows the number of entries at the end of the month.

The fact table might contain additional dimension columns such as DIM_HST_ENTRY_TYPES for distinguishing the entry types User, Role, and so on, or dimensions DIM_HST_OU for distinguishing users according their organizational unit. The columns for such a dimension table are exactly the same as those for audit events.
The dimension DIM_HST_ENTRY_TYPES doesn’t need an extra dimension table: the fact population generator just takes the dimension values from the existing table HST_ENTRY_TYPES.

Fact Table for Imported Memberships

The imported membership database table is called FCT_HST_IMPORTED_MEMBERSHIPS.

The dimension columns DIM_HST_DATETIME and DIM_HST_MONTH contain the day as a timestamp with the time 12am. The column DIM_HST_MONTH is empty for all days except the last day of the month or the current day.
DIM_HST_APPLICATION_ID is distinguishing applications where imported group memberships belong.
The only fact column FCT_HST_TOTAL contains the number of entries existing at the end of the day and matching the other dimensions.

Configuring Fact Tables

The configuration for the fact tables is defined in the file confFactTables.xml. This file is an XML document that conforms to the XML schema with the namespace https://factpopulation.persistence.audit.dirx.solutions/tables. The root element <confFactTables> contains a number of child elements <confFactTable>. Each <confFactTable> describes one fact table and has the following child elements:

<name> – the name of the fact table. The fact population component creates a table in the database with exactly that name. The customDrilldown="true" attribute and value indicates that customized result table of drill-through data will be shown. The enabled="false" attribute and value indicates that fact table will not be created and populated, by default it is set to "true".
<onRecordType> – the element is optional. For tables on history entries only, it must contain the value HISTORY_ENTRY.
<description> – a string describing the content of the fact table.
<common> – contains shared elements for dimension and tact tables. The <ObjectType> enumeration provides information, whether the fact or dimension is object in database and if it is table or view.
<facts> – the list of <fact> child elements for the facts. The element <fact> contains the name of a fact and must correspond to a fact definition in the file confFacts.xml.
<dimensions> – the list of <dimension> child elements for the dimensions. The element <dimension> contains the name of a dimension and must correspond to a dimension definition in the file confDimensions.xml.

<query> – the filter conditions to identify the audit events covered in this fact table. The component transforms this element into a SQL select statement and collects only facts and dimensions for audit events matching these conditions. Alternatively for history entries, there are the <statements> element instead of the <query> element.
<statements> – references to files containing SQL statements for creating the fact table, populating the fact table and drilling the data.

The sub-elements <query> and <onRecordType> are defined in the XML schema namespace https://factpopulation.persistence.audit.dirx.solutions/query.

The sub-element <statements> is defined in the XML schema namespace https://factpopulation.persistence.audit.dirx.solutions.net/statements.

The sub-element <common> is defined in the XML schema namespace https://factpopulation.persistence.audit.dirx.solutions.net/common.

The next sections describe fact tables on audit events and on history entries.

Fact Tables on Audit Events

Here is a sample snippet showing the structure of a fact table definition on audit events:

<t:confFactTable>
  <t:name>FCT_ACCOUNTS</t:name>
  <t:description>Account related operations</t:description>
  <t:facts>
    <t:fact>FCT_SUCCEEDED</t:fact>
      …
  </t:facts>
  <t:dimensions>
    <t:dimension>DIM_DATE_DAY</t:dimension>
    …
  </t:dimensions>
  <q:query>
    …
  </q:query>
  <s:statements>
    <s:create>
      <s:script path='data/fct/FCT_ACCOUNTS/create.sql'/>
    </s:create>
    <s:fill>
      <s:script path='data/fct/FCT_ACCOUNTS/fill_01_FCT_ACCOUNTS_merge.sql'/>
    </s:fill>
  </s:statements>
</t:confFactTable>

The <query> element conforms to the XML schema https://factpopulation.persistence.audit.dirx.solutions/query. The important child elements of <query> are:

<table> – the name of a table, which is related to audit events and must be one of the following tables:
- DAT_AUDITEVENTS – this table contains the audit events. The columns that may be used for querying here are: OP (Operation), TYPE, DETAIL.
- DAT_AUDITMESSAGES – this table contains the audit messages as transformed from the native audit trails. The columns most useful for fact identification are: IDENTIFICATION_SOURCE, IDENTIFICATION _OUTCOME, WHEREFROM_APPLICATION.
<column> – the column name within the table defined in <table>.
<operator> – the SQL comparison operator. Allowed values are: = (the default if not supplied), !=, like.
<value> – the comparison value. Use the wildcard % (zero or more characters) for searching with the like operator.

<query> elements can be combined by the logical operators <and>, <or> and <not>. They can even be nested as in the following sample:

<q:query>
  <q:and>
    <q:or>
      <q:query>
        <table>DAT_AUDITEVENTS</table>
        <column>OP</column>
        <operator>like</operator>
        <value>Accept%</value>
      </q:query>
      <q:query>
        <table>DAT_AUDITEVENTS</table>
        <column>OP</column>
        <operator>like</operator>
        <value>Reject%</value>
      </q:query>
    </q:or>
    <q:query>
      <table>DAT_AUDITEVENTS</table>
      <column>TYPE</column>
      <operator>like</operator>
      <value>User to%</value>
    </q:query>
  </q:and>
</q:query>

This query searches for audit events that correspond to an approval operation: either Accept or Reject. It is not interested in all approvals, only those for User to Privilege assignments. See the delivered default configurations for more examples.

Fact Tables on History Entries

The following sample shows a fact table on history entries:

<t:confFactTable>
  <t:name>FCT_HST_USERS</t:name>
  <q:onRecordType>HISTORY_ENTRY</q:onRecordType>
  <t:description>History Users with states and organizational units</t:description>
  <t:facts>
    <t:fact>FCT_HST_TOTAL</t:fact>
    <t:fact>FCT_HST_WOUT_ROLE</t:fact>
    <t:fact>FCT_HST_WOUT_PERMISSION</t:fact>
    <t:fact>FCT_HST_WOUT_GROUP</t:fact>
    <t:fact>FCT_HST_WOUT_PRIVILEGE</t:fact>
  </t:facts>
  <t:dimensions>
    <t:dimension>DIM_HST_DATETIME</t:dimension>
    <t:dimension>DIM_HST_MONTH</t:dimension>
    <t:dimension>DIM_HST_DXRSTATE</t:dimension>
    <t:dimension>DIM_HST_OU</t:dimension>
    <t:dimension>DIM_HST_O</t:dimension>
    <t:dimension>DIM_HST_L</t:dimension>
    <t:dimension>DIM_HST_DXRRSKLEVEL</t:dimension>
  </t:dimensions>
  <s:statements>
    <s:create>
      <s:script path=’history/fct/FCT_HST_USERS/create.sql’/>
    </s:create>
    <s:fill>
      <s:script path=’history/fct/FCT_HST_USERS/fill_01_FCT_HST_USERS_TMP_delete.sql’/>
       …
    </s:fill>
    <s:drilldown>
      <s:script path=’history/fct/FCT_HST_USERS/drilldown.sql’/>
    </s:drilldown>
  </s:statements>
</t:confFactTable>

The table FCT_HST_USERS is on users and distinguishes according the dimensions state, organizational unit, organization and locality.

The fact tables are created and populated with SQL scripts executed by schedule and stored in the install_path\conf\defaults\sql\common\factpopulation folder. It also contains drilldown statements to supporting drill-through from aggregated data to individual records.

Configuring Facts

The configuration for the facts is defined in the file confFacts.xml. This file is an XML document that conforms to the XML schema with the namespace https://factpopulation.persistence.audit.dirx.solutions/facts. The root element <confFacts> contains a number of child elements <confFact>. Each <confFact> describes one fact and has the following child elements:

<name> – the name of the fact. The fact population tool creates a column with that name in every fact table that references this fact. This name must match the <fact> names used in fact table configurations.
<description> – a string describing the fact.

The fact population tool transforms the following elements into a SQL select statement and counts only audit events matching these conditions. The elements describe a query similar to the one used in the fact table configurations.

Note that in most cases the only supported fact on history entries is FCT_HST_TOTAL. It counts the records in the table HST_ENTRIES separated by their primary key in HST_ENTRIES_ID.

Here is a sample definition for the fact for all successful operations:

<f:confFact>
  <f:name>FCT_SUCCEEDED</f:name>
  <f:description>Successful Operations</f:description>
</f:confFact>

There is also a virtual relative fact called FCT_FAILED_RELATIVE, which is a representation of the failed entries as a relative part of total entries. This fact is computed based on the FCT_FAILED and FCT_TOTAL facts.

<f:confFact
  virtual="true"
  relative="true" >
  <f:name>FCT_FAILED_RELATIVE</f:name>
  <f:description>Failed Operations</f:description>
  <f:display-sql>100.0*sum(FCT_FAILED)/sum(FCT_TOTAL)</f:display-sql>
  <f:additional-sql>sum(FCT_FAILED)</f:additional-sql>
</f:confFact>

Configuring Dimensions

The configuration for the dimensions is defined in the file confDimensions.xml. This file is an XML document that conforms to the XML schema with the namespace https://factpopulation.persistence.audit.dirx.solutions/dimensions. The root element <confDimensions> contains a number of child elements <confDimension> or <confDimensionVirtual>. Each <confDimension> describes one dimension based on the database view or table and is created with SQL DDL scripts and has the following child elements:

<name> – the name of the dimension. The fact population tool creates a table with this name. Exceptions are the DATE dimensions, which do not need a table. This name must match the <dimension> names used in fact table configurations. The enabled="false" attribute and value indicates that dimension will not be created and populated, by default it is set to "true". Note, that setting enabled="false" to dimension could also disable fact table(s), which are dependend on this dimension.
<onRecordType> – the element is optional. For tables on history entries only, it must contain the value HISTORY_ENTRY.
<description> – a string describing the dimension.
<table>: the name of a table, which is related to audit messages. You can use the tables TAG_EVENT_DIMENSIONS and TAG_MESSAGE_DIMENSIONS for dimension filtering in addition to the tables allowed for dimension tables (DAT_AUDITEVENTS, DAT_AUDITMESSAGES).
<column> – the column name within the table defined in <table>.
<common> – contains shared elements for dimension and tact tables. The <ObjectType> enumeration provides information, whether the fact or dimension is object in database and if it is table or view.
<statements> – references to files containing SQL statements for creating and populating the dimension table or database view.

<query> – the filter conditions to identify the dimension. The fact population tool transforms this element into a SQL select statement and uses the resulting list of distinct values for grouping the facts.

The following child elements describe a query similar to the one used in the fact and fact table configurations:

<table> – the name of a table. In addition to the tables allowed for facts (DAT_AUDITEVENTS, DAT_AUDITMESSAGES, TAG_EVENT_DIMENSIONS, TAG_MESSAGE_DIMENSIONS) you can use the following tables for dimension filtering:
- TAG_EVENT_DIMENSION_TYPES – this table contains the distinct tag names for audit events, not their values. The only column that may be used for querying is "NAME".
- TAG_ MESSAGE _DIMENSION_TYPES – this table contains the distinct tag names for audit messages, not their values. The only column that may be used for querying is "NAME".
  Note that for dimensions on history entries only, the tables HST* can be used. They are listed in the section “Fact Tables on History Entries”.
<column> – the column name within the table defined in <table>.
<operator> – the SQL comparison operator. Allowed values are: = (the default if not supplied), !=, like.
<value> – the comparison value. Use the wildcard % (zero or more characters) for searching with the like operator. If the value is not supplied, all distinct values of the table column are considered.

Note that in the same way as fact tables, dimensions are created and populated with SQL scripts executed by scheduled jobs and stored in the install_path\conf\defaults\sql\common\factpopulation folder.

Note that the file also contains a couple of virtual dimensions <confDimensionVirtual>. Please do not change them! DirX Audit Manager Classic evaluates them for grouping the charts by days, weeks, months and years.

Adding and Disabling a Fact Table

To create a new fact table, add an element <confFactTable> into the file confFactTables.xml. See the section “Configuring Fact Tables” for information about the format.

When the fact table population is executed next time, the table is created. The fact population job in the DirX Audit Server automatically updates the table according to the configured schedule.

To disable a fact table, set the attribute enabled="false" of an existing element <confFactTable>. See the section “Configuring Fact Tables” for information about the format.

When the fact table population is executed next time, the table will not be created and updated.

Adding a Fact

To create a new fact, add an element <confFact> into the file confFacts.xml. See the section “Configuring Facts” for information about the format.

Enter the new fact as element <fact> into an existing or a new fact table. See the previous sections for details.

Adapt your database schema accordingly.

When the fact table population is executed next time, the new column is populated too. The fact population job in the DirX Audit Server automatically updates the table according to the configured schedule.

Adding and Disabling a Dimension

To create a new dimension, add an element <confDimension> into the file confDimensions.xml. See the section “Configuring Dimensions” for a description of the format.

Enter the new dimension as the element <dimension> into a fact table. See the previous sections for details.

Adapt your database schema accordingly.

When the fact table population is executed next time, the new dimension table and the new dimension column are populated too. The fact population job in the DirX Audit Server automatically updates the table according to the configured schedule.

To disable a dimension, set the attribute enabled="false" of an existing element <confDimension>. See the section “Configuring Dimensions” for information about the format.

When the fact table population is executed next time, the dimension table will not be created and updated.

Note, that setting enabled="false" to dimension could also disable fact table(s), which are dependend on this dimension.

Configuring Custom Fact Tables

If you want to customize fact table configuration, it is possible to do that for all tenants or for a specific tenant. Copy the common core default configuration from the folder:

install_path/conf/defaults/fact-configuration

into the core custom configuration folder for all tenants in:

install_path/conf/fact-configuration

and/or into the tenant custom configuration folder in:

install_path/conf/tenants/tenantID/fact-configuration

where tenantID is a unique tenant identifier. Now you can customize configuration files for all and/or a specific tenant by following the instructions given in the previous sections of this chapter.

Remove all <confFactTable> elements from the customized confFactTables.xml file and keep only those you want to modify, disable, or add a new one as described in previous chapters.

Remove all <confFact> elements from the customized confFacts.xml file and keep only those you want to modify or add a new one as described in previous chapters.

Remove all <confDimension> and <confDimensionVirtual> elements from the customized confDimensions.xml file and keep only those you want to modify, disable, or add a new one as described in previous chapters.

Components that create or update fact tables, facts and dimensions will implicitly use the core default configuration. If configuration folder for all tenants is available, its configuration will be merged, same applies for tenant specific customization.

For example, table FCT_HST_USERS is defined on core default level in confFactTables.xml file with specific elements and values. If the table is defined/customized on core custom level, its elements and values will be overwritten. And if the table is also defined on tenant custom level, its elements and values will be overwritten again.

To easily identify, which fact tables, dimensions or facts are customized, it is recommended to only include in customized configuration files those elements, which you want to add, modify, or disable.

Configuring SQL scripts

If you want to customize SQL scripts for creating, filling, or drilling data, it is possible to do that for all tenants or for a specific tenant. Follow the instructions described in the General Customization chapter. Examples are available in chapter Customizing SQL scripts.