Development >  Business intelligence >  Setup >  Fact tables  

Display all Hide all

This function is used to describe the facts table which are to be used as base for the reports of the driven version of the software. A facts table is a data table loaded by one or several tables coming from one or several live folders and present in a datamart. It integrates:

  • fields (which can be used in the management reports)
  • links (to dimension tables used as analysis criteria)
  • aggregates (in order to store the totaled amounts on criteria combinations for optimization purposes)
  • indexes (also for optimization purposes).

The facts tables are not created in the folder itself but in a data structure called datawarehouse, which may be common to several folders.

The facts tables are loaded via a function integrated to the software (it is thus not necessary to use an external software of type ETL). Its parameters are defined by a dedicated function.

ABITUNNEL entry point

The ABITUNNEL entry point is used to modify the value of the OKK variable.

If [L]OKK = 0 then no tunnel can be defined on the dimension.

In any other case, it is possible to define a tunnel on the dimension.

Prerequisites

SEEREFERTTO Refer to documentation Implementation

Screen management

Header

Presentation

Is used to define the code and the title of the table but also an abbreviation which will be used to access the facts table via the update processings.

Close

 

Fields

The following fields are present on this tab :

This code identifies the current record in a unique way.

It corresponds to the name of the table that will be created in the datamart database.
  • Abbreviation (field ABRABF)

The table abbreviation must be composed of 1 to 4 characters (letters or numbers but must start with a letter). It is the unique identifier for all the table abbreviations of the data dictionary. The Adonix reserved words are prohibited.
  • Description (field ZINTIT)

 
  • field W

 
  • Evaluated title (field INTEVAL)

 

Close

 

Tab Description

Presentation

General information on the facts tables can be found here, notably on:

  • the authorization conditions by site, if a field of this type is present. In the description of each report based on the corresponding data, it is possible to define if the restrictions by site must be applied to the users by specifying what is the function whose authorizations by site are under consideration.
  • A date that ca be used for the purge of fact table data.
  • A type of update used by the loading functions.

Close

 

Fields

The following fields are present on this tab :

Characteristics

An activity code is used to:

  • make optional an element in the dictionary if the value associated with the activity code is null.
  • identify the specific/custom elements if they are marked with a code starting with X, Y or Z.
  • size a maximum number of lines when the activity code marks elements from a grid.

In this way, if the activity code is disabled, the marked element will not be useable, and the associated code (if any) will neither be generated nor can be activated.
  • Module (field MODULE)

 

Authorizations

  • Authorization site (field AUZFCY)

If this box is checked, a filtering by site can be made on the data in the facts table. This assumes that a site field exists in this table.

The access rights for the site in the reports will be determined by means of a function code managing the data filtered by site : the sites authorized for the function in question for a given user will be assigned to the data in the report.
  • Site field (field FLDFCY)

Mandatory if an authorization by site is defined, this field is used to define the site field code to be used to filter the data. It must correspond to a field defined in the tab Fields in the facts table. This field must in addition be of the type FCY.

Purge info

  • Date field (field FLDDAT)

This date type field is used, where it exists, to base any purging of the facts table from this date.
  • No. of storage days (field NBEPU)

Number of information detention days. The superior data will be purged as that calculated date.

Generation

  • Update type (field TYPMAJ)

This field defines if the loading logic for the facts table must be :

  • of the type Incremental (on each triggering, the modifications to the source table are copied, which have been logged with the help of a database trigger)
  • or of the type Delete and replace (all the lines in the table are deleted prior to completely recreating them).
  • Object "number of" (field NBRABF)

If this box is checked, an object that is used to count the number of lines in the facts table is created in the universe.

Close

 

Tab Fields

Presentation

Here is the detailed definition of the fields which can be:

  • dimensions (that is analysis criteria which are as a general rule linked to an annex table).
  • measures (values that can be displayed in detail or aggregated according to the dimension values).
  • information (additional information fields).

Close

 

Fields

The following fields are present on this tab :

Grid

  • Line no. (field NUMFLD)

 
  • Field code (field CODFLD)

Defines the code for the field in the facts table or the table describing dimensions. This code is that under which the field is known in the database (it is automatically suffixed with the characters "_0"). It is not visible when in the management reports (it is the title that is displayed) but can be viewed if looking at the SQL syntax that extract the data from the datamart.
  • Normal title (field ZINTFLD)

 

The field type is defined here, which characterize the entry format (numeric, any characters, date...) and the coherence controls (reference to a table for example).

The principal generic types that exist :

A : Alphanumeric
C : Short integer
L : Long integer
DCB : Decimal
D : Date
M : Local menu
MM : Local menu with filter
ACB : clob
ABB : blob

But many other predefined types exist. They often make reference to an annex table (currency, country...). A dedicated dictionary is used to described them and they can be selected using the key that is used to obtain the list.

In the case of the facts tables, it is necessary to enter the additional information for certain types (dates, miscellaneous tables, local menus).
  • Lg (field LNGFLD)

Used to define the length of a field when this field uses a generic data type where the length is not fixed. This is notably the case for the types A and DCB.
  • Date (field TYPDAT)

When a field from the facts or dimensions table is of the type date and is defined as dimension, it is automatically associated with a dimensions table named AX3DAT, which is automatically managed.

It is then possible to specify here what are the time characteristics linked to the date that will be accessed in the analyses. A list of characters to be defined is given, not forgetting there are the following correspondences :

  • Y=year
  • S=half year
  • Q=quarter
  • M=month
  • F=fortnight
  • T=decade
  • W=week
  • D=day

Thus, for example, YMW signifies that a direct access is available for the year, the month and the week defined by the date.
  • Menu (field MENLOC)

Defines the local menu number associated with the field defined on the line.

When a field is of the type local menu (from 1 to 255) corresponding to the rank of a title in a table named local menu, stored in the messages table APLSTD.

On entry or on display, the following are displayed according to the choices made in the user interface :

  • either a title can be chosen in a scrolling list commonly called a combo box
  • or a list of buttons.

The interest of this type of entry is that the list of choices is displayed in the user connection language.

Each local menu number characterises the list of available titles. For example, the local menu 1 corresponds to the choice No / Yes in this order. In this particular case, the user interface can also be a check box.

If the field is associated with a miscellaneous table, the number that identifies it is defined here.
  • Object type (field TYPFLD)

Used to characterize the data in the facts table. The following values are possible:

  • Measure assumes that the field is numeric. This type of field can be aggregated on the dimension combinations in the analysis reports, notably in order to obtain the totals, the minima, the maxima on the aggregates.
  • Dimension signifies that the field is considered as a analysis criterion. In principle, it is linked to the dimensions table that is used to store the values and to associated them with other characteristics In certain cases (the fields of the type date, local menu, miscellaneous table) the dimension table is implicitly manage by the software package.
  • Information corresponds to a field that will be presented in the reports, but it is not considered as being descriminant from the point of view of the analysis and the data aggregation.
  • Technique corresponds to the fields that are used in the data structure (for example to define the links), but which do not need to be visible when constructing the reports.

It should be noted that a field of the type ACS (access code) is automatically defined as technical (it will not appear in the description of the fields in the facts table, but will be used to filter the data when used for queries in the facts table).
  • Sub-class (field ZINTSSC)

 
  • Tunnel toward object (field TUNNEL)

 
  • Distinct account (field TYPOPE)

Used to specify if a count of the number of lines available is required by criterion value. A measure of the account type is then created, to optimize the corresponding access.

An activity code is used to:

  • make optional an element in the dictionary if the value associated with the activity code is null.
  • identify the specific/custom elements if they are marked with a code starting with X, Y or Z.
  • size a maximum number of lines when the activity code marks elements from a grid.

In this way, if the activity code is disabled, the marked element will not be useable, and the associated code (if any) will neither be generated nor can be activated.

Close

 

Action icon

Field Selection

Fields

The following fields are included in this window :

Block number 1

The table code must be composed of 1 to 10 characters (letters or numbers but must start with a letter). It is the unique identifier for all the table abbreviations of the data dictionary. The Adonix reserved words are prohibited.

Grid

  • Field (field CODZONE)

Corresponds to the name field in the database table (this name is automatically completed with "_0").

  • Description (field INTITCOURT)

Title associated to the previous code.

  • Y/N (field SELECT)

If the response is Yes on the line, the selected field is transferred with its characteristics (type, length...) to the screen from which the selection function has been called.

It should be noted that, by right click on the line (without being in entry on the field), it is possible to globally set the lines to Yes or No from the current line.

Close

This function is used to add to the field grid fields coming from a table entered previously in the header of the window being opened from the current line. If the facts table has the name of one the tables of the software, this name is proposed by default.

Default values are applied to the grid containing the lines that are thus recovered:

  • The main characteristics of the field are recovered as is (title, type, length, local menu, miscellaneous table, etc.)
  • Once the field is characterized by a type linked to an object (or if it is of the type Date or Miscellaneous table, it is defined as a Dimension.
  • If it is of type DCB, it is defined as a Measurement.
  • Otherwise, it is defined as an Information.

If fields having the same title as a field to insert already exist, they are not inserted.

 

Close

 

Tab Links

Presentation

This tab is used to define the various links to the dimension tables managed by the software.

A link is characterized by the code of the dimension table and by a link expression. When there are several links to a same dimension table, it is necessary to give a distinct alias number.

Note that the automatic links do not need to be described here. Automatic links are the ones generated for the following fields:

  • The fields of type date (D), which have an implicit join with an alias of the AX3DAT table.
  • The fields of type Local menu (M), which have an implicit join with an alias of the AX3MNLxxx table.
  • The fields of type Miscellaneous table (ADI), which have an implicit join with an alias of the AX3ADIyyy table.
  • The fields of type Folder code (ADS), which have an implicit join with an alias of AX3DOS.
  • The fields of type Access code (ACS), which have an implicit join with an alias of AX3ACC.

Close

 

Fields

The following fields are present on this tab :

Grid

  • Line number (field NUMLNK)

 

Define the table for thedimensions linked to the facts table.
  • Link expression (field EXPLNK)

Contains the links expression that is used to move up through the dimensions table by means of its key. A links expression can be composed of fields coming from the facts table and/or constants, separated by a semi-colon when there are several.
  • Join option (field OPTJNT)

 
  • Description (field ZINTLNK)

 
  • Additional info (field ZINTCOMP)

 

Close

 

Tab Index

Presentation

This type is used to define the indexes which are created in the facts table in order to optimize the access times.

Close

 

Fields

The following fields are present on this tab :

Grid

  • Line number (field NUMIND)

 
  • Description (field EXPIND)

This is where the list of fields that make up the index are entered. The fields are separated by a '+' if there are several fields (for example, in the sites table FACILITY there is a key whose definition is LEGCPY+FCY).

A descending sort field is preceded with the sign "-" (warning, it is used in the left list).

The first field that makes up the index is without a sign and is therefore ascending.

An activity code is used to:

  • make optional an element in the dictionary if the value associated with the activity code is null.
  • identify the specific/custom elements if they are marked with a code starting with X, Y or Z.
  • size a maximum number of lines when the activity code marks elements from a grid.

In this way, if the activity code is disabled, the marked element will not be useable, and the associated code (if any) will neither be generated nor can be activated.

Storage characteristics

  • field STK

A text describing the technical configuration of the table in the database is entered in this section. This text is recorded in a file "name.cfg" in the FIL directory for the application. This file is used by the "valfil" instruction. View the dedicated technical annex for more information.

Close

 

Tab Aggregates

Presentation

In this tab the aggregates to be managed are to be defined. An aggregate is a table storing the intermediate totals based on the various values that a field or combination of fields can take. The advantage of these aggregates is that they help optimize the access times when a report concerns only the measures totaled on one of the aggregates: the access is direct since the data are not recalculated.

In this tab, one or several aggregates can be defined:

  • a line of the upper grid represents an aggregate
  • when the cursor is positioned on a grid line, the bottom grids (Dimensions and Index) are loaded with the characteristics of each aggregate.

The table Dimensions displays the list of dimensions whose crossing defines an aggregate. These dimensions can have a predefined type (date, local menu, miscellaneous table), or have type Other; this type is used to enter one of the dimensions listed in the corresponding tab.

Note that for each aggregate defined in the facts table:

  • the site dimension is mandatory if an authorization by site is managed in the facts table.
  • the dimensions having an access code are also mandatory.

The table Index is used to define the indexes in order to make faster the access to the aggregate data.

Close

 

Fields

The following fields are present on this tab :

Grid Aggregates

  • field NUMAGG

 
  • Name (field CODAGG)

This code identifies the aggregate parameterized in the facts table.

This activity code is used to make the aggregates defined in a facts table optional.

An activity code is used to:

  • make optional an element in the dictionary if the value associated with the activity code is null.
  • identify the specific/custom elements if they are marked with a code starting with X, Y or Z.
  • size a maximum number of lines when the activity code marks elements from a grid.

In this way, if the activity code is disabled, the marked element will not be useable, and the associated code (if any) will neither be generated nor can be activated.

Grid Dimensions

  • Dim view type (field TYPDIM)

This field defines the dimension type that is part of the aggregation. This can be either a dimension defined in the links tab (in this case, the type Other is used), or a dimension implicitly created from the particular data type (the type Date, on which time aggregates can be defined and the types local menu and miscellaneous tables).

Used to give the code of the dimension that is part of the aggregate. When the type of dimension is not Other, the first dimension of the selected type is proposed by default (it is imposed if no others exist).
  • Fields (field CHAMPS)

Value displayed that gives the list of fields concerned by the dimension that is part of the aggregate.
  • Aggregation level (field NIVEAU)

Define the aggregation level for a dimension when it is of the type date. This can be either the day, the month, the quarter or the year (but only the levels defined in the grid containing the fields that can be used).

Grid Index

  • Index (field INDEX)

This is where the list of fields that make up the index are entered. The fields are separated by a '+' if there are several fields (for example, in the sites table FACILITY there is a key whose definition is LEGCPY+FCY).

A descending sort field is preceded with the sign "-" (warning, it is used in the left list).

The first field that makes up the index is without a sign and is therefore ascending.

Close

 

Specific Buttons

Is used to create the processing which loads the created aggregates.

The name of the processing is WMF followed by the facts table code. The labels OUVRE, FERME, INSERT, ERASE, ALIM_i (i=aggregate number) can be found, which are called by the processings allowing the data update.

The following fields are included on the window opened through this button :

Block number 1

  • field OBJET

 
  • field CLES

 

Block number 2

  • From folder (field DOSORG)

Use this field to define the folder from which the record will be copied. The possible syntaxes are described in the Dedicated appendix.
  • All folders (field TOUDOS)

Use this option to copy the record to all the folders defined in the dictionary (ADOSSIER table of the current solution).
  • To folder (field DOSDES)

Use this field to define the folder to which the record will be copied. The possible syntaxes are described in the Dedicated appendix.

Close

This button is used to copy the record definition from or to another folder.

Menu Bar

Documentation / Paragraphs

This function is used to access the documentation management on the first paragraph of the documentation (if there is one) associated to the current record.

Documentation / Links

This function is used to access the link management. It is used to define the links between the current record and other records (for instance the links between functions and parameters). These links are specific to the documentation and are used to load the generation of documentation structures.

Documentation / Generation

This menu is used to launch a documentation generation. The generation can also be launched from the [Generation] button at the bottom of the window.

Three types of generation can be launched one by one or simultaneously:

  • the generation of the documentation structure from the dictionary (tables ADOCUMENT, ADOCBLB, ADOCCLB).
  • the generation of the documentation from the previous tables.
  • the generation of the field documentation.

The range suggested by default takes into account the current record but it can be modified upon launch.

Error messages

In addition to the generic error messages, the following messages can appear during the entry :

There must be at least one field

The attempt was made to create a facts table without fields.

The field must be numeric.

The attempt was made to create a measure on a non numeric field.

Code already defined on line LIG

It is forbidden to define twice a link to a same dimension.

Incorrect number of fields
        DIMENSION(NBCHAMPS)

The expression of links defined in the link grid has a component number different from that defined by the main dimension key (whose code is given in the message, the number of expected components being specified in brackets).

FIELD: Field of incorrect type (XXX)
         (the field must be of type: xxxxxxxx)

The field entered in the link expression has a type which does not match the component type defined in the key.

FIELD : the field must be of type dimension
FIELD : Field of incorrect type

When entering in the aggregate grid a field aggregate of type date, local menu or dimension, the system verifies that the field in question is declared as dimension (and not as information or technical field) and that his type is correct. If this is not the case, one of the two previous messages is displayed.

EXPRESSION : Expression of incorrect type

When entering in the aggregate grid an aggregate of type Other, the link expression is only entered if there are several links to the same dimension. This error is displayed if the entered link does not match one of the link expressions of the tab Links.

FIELD: Site field of type incorrect (XXX)

The site field cannot have an activity code

These errors concern the site field specified in the first tab. This field must be of type FCY (here it is of type XXX), and cannot be optional, which could be the case if it was allocated an activity code.

FIELD : Date field does not exist
FIELD : Date field of type incorrect (XXX)

These errors concern the date field specified in the first tab. This field must be of type D (here it is of type XXX), and cannot be optional, which could be the case if it was allocated an activity code.

FIELD : Field not referenced in the links

A field defined as dimension is present is no link expression.

AGREG : the aggregate has no dimension

An aggregate is defined with no dimension associated to it.

SITE : the site field is not present in the aggregate AGREG

The field that defines the site (SITE in the example) is not present in the corresponding aggregate.

FIELD Incompatibility between aggregates and access codes

When a field is used to filter the access to the facts table, it does not make any sense to manage the aggregates because they could total information which are then partially filtered in the case of a zoom on the detail.

DIMENSION : Dimension not defined on the table

In the aggregate tab, an existing dimension is referenced which is not declared in the tab Links of the facts table.

Tables used

SEEREFERTTO Refer to documentation Implementation