Development > Business intelligence > Setup > Fact tables 

This function is used to describe the facts tables that are used as supports in the recovery of the version driven by the software. A facts table is a data table loaded by one or more tables coming from one or more live folders and is present in a datamart. It includes :

  • the fields (that will be used in the management reports)
  • from the links (to the dimension tables used as analysis criteria)
  • from the aggregates (in order to store the accumulated amounts for the criteria combinations, for optimization purposes)
  • from the indexes (for optimization purposes).

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

The loading of the facts tables is made by means of a function integrated within the software (the use of an external software of the type ETL is therefore of no use). It parameterization is defined by a dedicated function.

    Prerequisite

    SEEREFERTTO Refer to documentation Implementation

    Screen management

    Header

    Presentation

    Used to define, in addition to the code and the title of the table, an abbreviation that will be used to access the facts table by the update processes.

    Close

     

    Fields

    The following fields are present on this tab :

    This code identifies the created records in a unique manner.

    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.

    • Title (field ZINTIT)

    It used to define a name associated with each record.

    • field W

     

    • Evaluated title (field INTEVAL)

     

    Close

     

    Tab Description

    Presentation

    The general information associated with the facts table is found here, notably :

    • the authorization conditions by site, if a field of this type exists. In the description of each report based on the corresponding data, there is the possibility to define if the restrictions by site must be applied to the users by specifying the function for which the authorizations by site must be considered.
    • A date used for the purging of data in the facts table.
    • An update type 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)

    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

    Found here is the detailed definition of the fields that may be :

    • dimensions (that is to say the analysis criteria in principle linked to an annex table).
    • measures (values can be displayed in detail or aggregated according to the values of the dimensions).
    • information (additional informative fields).

    Close

     

    Fields

    The following fields are present on this tab :

    Table

    • 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)

    This title, which will be viewed by the user during the creation of the reports, must be unique for each field in the facts table or for the given dimension. In addition it is recommended to not have duplications between different tables. In fact, if there are duplications aliases will be automatically created (title_2, title_3) to prevent coherence problems, but this makes the data structures less readable for the user.

    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

     

    Functions accessed by right click on the grid

    Field Selection

    Fields

    The following fields are present 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.

    Table

    • Field (field CODZONE)

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

    • Title (field INTITCOURT)

    Title associated to the previous code.

    • Y/N (field SELECT)

    If the response is Yeson 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, from the current line, fields coming from a table previously entered in the header of the window that opens (if the facts table contains the name of one of the tables in the software package, this name is proposed by default).

    The default values are applied to the grid of lines that also contains :

    • The principal characteristics for the field are displayed in the report (title, type, length, title, local menu, miscellaneous table...).
    • Once the field is characterised by a type linked to an object (or if it is of the type Dateor Miscellaneous table, it is defined as being a Dimension.
    • If it is of the type DCB, it is defined as a Measure.
    • If not it is defined as being an Information.

    It is not possible to insert fields that carry the same title as field to be inserted that already exists.

     

    Fermer

     

    Tab Links

    Presentation

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

    A link is characterized by the dimension table code and by a link expression. When several lines exist to a single dimensions table, it is necessary to give a distinct alias number.

    It should be noted that the automatic links need not be described here. The automatic links include those generated for the following fields :

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

    Close

     

    Fields

    The following fields are present on this tab :

    Table

    • 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)

     

    • Title (field ZINTLNK)
    • Add. info. (field ZINTCOMP)

    Close

     

    Tab Index

    Presentation

    This tab is used to define the indexes that will be created in the facts table, with the goal of optimizing the access time.

    Close

     

    Fields

    The following fields are present on this tab :

    Table

    • 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

    Defined in this tab are the aggregates that are to be managed. A aggregate is a table storing the intermediate totals based on the different values that can take a field or combination of fields. The interest of these aggregates resides in an access time optimization when data recovery only involves the measures accumulated on one of the aggregates : the access is therefore direct, the data does not require recalculating.

    One or more aggregates can be defined in this screen :

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

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

    It should be noted 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 carrying an access code are also mandatory.

    The Index grid is used to define the indexes to give a more rapid access to the data for the aggregate.

    Close

     

    Fields

    The following fields are present on this tab :

    Table 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.

    Table 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).

    Table 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

    Used to create the process that will load the created aggregates.

    This process has the name WMF followed by the facts table code ; the labels OUVRE, FERME, INSERT, ERASE, ALIM_i (i=number of the aggregate) are found here, which are called by the processes that update the data.

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

    Block number 1

    • field OBJET

     

    • field CLES

     

    Block number 2

    • From folder (field DOSORG)

    This field is used to define the folder from which the record is going be copied. The possible syntaxes are described in the dedicated appendix.

    • All folders (field TOUDOS)

    This option is used to copy the record to all the folders defined in the dictionary (ADOSSIER table from the current solution).

    • To folder (field DOSDES)

    This field used to define the folder in which the record is going 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 links management. It is used to define the links between the current and other records (for example the links between functions and setups). 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.

    An attempt has been made to create a facts table without a field.

    The field must be numeric.

    An attempt has been made to create a measurement on a non numeric field.

    Code already defined in line LIG

    It is not possible to define a link to the same dimension more than once.

    Incorrect number of fields
            DIMENSION(NBCHAMPS)

    The link expression defined in the links grid contains a different number of components to that defined by the principal key for the dimension (where the code is given in the message, the number of components expected being given in brackets).

    CHAMP :Incorrect field type (XXX)
             (the field must be of the type : xxxxxxxx)

    A field of a type that does not correspond to a component type defined in the key has been entered in a link expression.

    CHAMP : the field must be of the dimension type
    CHAMP : Incorrect field type

    When an aggregate on a date, local menu or dimension type field is entered in the aggregates grid, the system verifies that the field in question has been declared as a dimension (and not as an information or technical field) and is of the correct type. If this is not the case, one of the two messages displayed above is displayed.

    EXPRESSION : Incorrect expression type

    When an aggregate of the type Others is entered in the aggregates grid, the link expression is only entered if several links to a single dimension exists. This error is displayed if the link entered does not correspond to one of the link expressions in the Links tab.

    CHAMP : Incorrect type of site field (XXX)

    The site field cannot have an activity code.

    These errors involve the site field indicated in the first tab. It is mandatory that this field must be of the type FCY (here, it is of the type XXX), and it is never optional, as can be the case if an activity code has been assigned to it.

    CHAMP : Non existant date field
    CHAMP : Incorrect type of date field (XXX)

    These errors involve the date field indicated in the first tab. It is mandatory that this field must be of the type D (here, it is of the type XXX), and it is never optional, as can be the case if an activity code has been assigned to it.

    CHAMP : Field not referenced in the links

    A field defined as being a dimension cannot be present in any link expression.

    AGREG : the aggregate has no dimension

    An aggregate has been defined without associating it with any dimension.

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

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

    CHAMP Incompatibility between aggregates and access code

    When a field is used to filter the access to the facts table, to manage the aggregates has no sign (because its aggregates risk accumulating information that will be partially filter when a zoom is made on the detail).

    DIMENSION : Dimension not defined in the table

    An existing dimension is referenced in the aggregates tab but not declared in the Links tab of the facts table.

    Tables used

    SEEREFERTTO Refer to documentation Implementation