All technical details will be covered by this section where the source view BUDGET_TEMPLATE_ROW is used as the source for a Quick Information Source Draft.
Note: It is assumed that no standard dimension exists for the LU BudgetTemplate
We have selected a view in the application owner schema.
This is the identity of the Quick Information Source we are about to create.
Reference information for some of the columns in the source view:
Column Name | Reference Information |
---|---|
COMPANY | FLAGS=PMI-L^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Company^REF=CompanyFinance^ |
BUDGET_PROCESS_ID | FLAGS=PMI--^DATATYPE=STRING(10)/UPPERCASE^PROMPT=Budget Process Id^REF=BudgetProcess(COMPANY)^ |
BUDGET_TEMPLATE_ID | FLAGS=PMI--^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Budget Template Id^REF=BudgetTemplate(COMPANY, BUDGET_PROCESS_ID)^ |
CURRENCY_AMOUNT | FLAGS=A-IU-^DATATYPE=NUMBER^PROMPT=Currency Amount^ |
ROW_COMMENT | FLAGS=A-IU-^DATATYPE=STRING(100)^PROMPT=Row Comment^ |
We also know the following:
When drafting a fact from an existing view definition, the following actions are performed:
The view columns and the comments associated with the columns are checked against the LU Dictionary cache. All the columns with Oracle data type DATE, VARCHAR2, NUMBER are collected. Columns with the following strings in the column name are however skipped:
Based on the column comments the columns are grouped as follows:
All the columns with Oracle data type NUMBER qualify as Measure Items with the exception of those having the following strings in the column names:
Items that are used when connecting dimensions to the fact/IS. All the columns with a reference in their comments or the columns specified as parent keys for the references are selected as Dimension Items.
These columns should not be visible in the Information Source and should only be accessible through the dimensions. These items are not shown in the Information Source by default.
If we e.g. look at the column BUDGET_TEMPLATE_ID, it has the following comments:
FLAGS=PMI--^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Budget Template Id^REF=BudgetTemplate(COMPANY, BUDGET_PROCESS_ID)^
This means that the column itself, BUDGET_TEMPLATE_ID, as well as parent columns COMPANY and BUDGET_PROCESS_ID, will be defined as dimension related items.
All the columns which do not fall into either Measure items or Dimension Items are selected as light items.
The Dimension Items identified during the previous step will be used to connected the fact/IS to the actual dimension during this step.
This is the most important step required to make sure that correct information is retrieved from the Quick Information Source when using connected dimensions. The goal is to make sure that on line access is handled correctly, meaning that natural joins between the Information Source and all connected dimensions are drafted.
According to the star schema model, one single column is used when connecting a dimension to a fact. This column represents a unique key and should be defined in the same way as the ID column in each dimension. The column does more or less not exist in any transaction view, which means that it has to be automatically created in the new access view representing the Quick Information Source.
The dimension Id columns will not be used when accessing the new Information Source from e.g. IFS Business Reporter. In this case we will use so called natural join. But the column becomes very handy as the connections column when building Data Warehouse or MS Cube models.
E.g. the column BUDGET_TEMPLATE_ID has the following reference::
...^REF=BudgetTemplate(COMPANY, BUDGET_PROCESS_ID)^
The definition will lead to creation of the Quick Dimension QDIM_BUDGET_TEMPLATE, having the keys COMPANY, BUDGET_PROCESS_ID and BUDGET_TEMPLATE_ID. To support this dimension we have to create a dimension ID column:
QDIM_BUDGET_TEMPLATE_ID COMPANY||'^'||BUDGET_PROCESS_ID||'^'||BUDGET_TEMPLATE_ID
Column CODE_B has the following reference:
...^REF=CodeB(COMPANY)^
For this column there is available basic data saying that LU CodeB is represented by dimension DIM_CODE_B meaning that the following dimension ID column will be created:
DIM_CODE_B_ID DECODE(CODE_B, NULL, NULL, COMPANY||'^'||CODE_B)
For CODE_B column we know that the column may have a NULL
value and if the value is NULL
the dimension ID column value
should be NULL, leading to a definition where we use the DECODE
function.
Using the dimension ID columns for join in the On Line access case when running IFS Business Reporter is in most cases a very bad idea due to the risk for very bad performance. This is mainly because the dimension ID column is a derived column. The solution is to also define so called natural joins between each fact and dimension.
Columns in QFACT_BUDGET_TEMPLATE_ROW |
Corresponding columns in QDIM_BUDGET_TEMPLATE |
Type of join |
---|---|---|
COMPANY | COMPANY | Natural |
BUDGET_PROCESS_ID | BUDGET_PROCESS_ID | Natural |
BUDGET_TEMPLATE_ID | BUDGET_TEMPLATE_ID | Natural |
QDIM_BUDGET_TEMPLATE_ID | ID | ID join (star schema) |
...^REF=BudgetTemplate(COMPANY, BUDGET_PROCESS_ID)^ -> COMPANY, BUDGET_PROCESS_ID as parent columns
parent1 item: DIM_COMPANY.CODE parent2 item: DIM_BUDGET_PROCESS.CODE
QFACT_BUDGET_TEMPLATE_ROW.COMPANY linked to DIM_COMPANY.CODE QFACT_BUDGET_TEMPLATE_ROW.BUDGET_PROCESS_ID linked to DIM_BUDGET_PROCESS.CODE QFACT_BUDGET_TEMPLATE_ROW.BUDGET_TEMPLATE_ID linked to DIM_BUDGET_TEMPLATE.CODE
When drafting a Quick Dimension from an existing view, the following actions are performed:
The view columns and the comments associated with the columns are checked against the LU Dictionary cache. All columns with Oracle data type DATE, VARCHAR2, NUMBER are collected. Then the columns with the following strings in the column name are skipped:
The view columns with the view comment flag P
qualify as
parents. For these columns there should be a reference (REF) defined. Based on
the reference value, a quick dimension will be created or a existing dimension
will be fetched.
The view columns with the view comment flag K
qualify as the
code Item.
Note: Only one code item , i.e. one key
column, should exist. This
means that there is a problem defining the CODE
item if there are more than one
column in the LU defined as key (K
).
The ID column will be generated by concatenating the parents and code item identified during the previous steps.
E.g. the dimension QDIM_BUDGET_TEMPLATE, created from LU BudgetTemplate
,
has two parent keys COMPANY and BUDGET_PROCESS_ID and the key column
BUDGET_TEMPLATE_ID. So the ID column will be generated according to:
COMPANY||'^'||BUDGET_PROCESS_ID||'^'||BUDGET_TEMPLATE_ID
Relations between LUs (Logical Units) and Dimensions is basic data provided during installation by each Applications component that owns dimensions. The information is used when creating a new Quick Information Source Draft, by scanning the selected source view for LU (entity) references to find out if existing dimensions can be connected to the new Information Source. The reference information is also used when drafting a Quick Dimension Draft.
During the processing of creating a Quick Information Source Draft, it might happen that new Quick Dimensions are created. The relation between newly created dimensions and the source LU will also be stored, to enable reuse of Quick Dimensions next time a Quick Information Source Draft is created.
Assume that there is a need to create a Quick dimension based on LU (entity) AccountingYear in component ACCRUL.
The view ACC_YEAR is selected as source view and the check box Set as Default is also selected. When the draft is created the reference information will be updated due to that Set as Default was selected.
LU (entity) AccountingYear now has a relation to the new quick dimension QDIM_ACC_YEAR. It is important that the [parent] keys of the LU (entity) corresponds to the key and the parents of the dimension. Once this definition is available, it will be used every time a quick draft is created and when the used source view references the LU (entity) AccountingYear, thus making it possible to automatically add the dimension QDIM_ACC_YEAR to a Quick Information Source.
If needed it is possible to override the reference information.
If e.g. we want to use another dimension as the default one for LU (entity) AccountingYear, then we can create a new quick dimension based on another source view.
Since the check box Set as Default is selected, the reference information will be updated.
During the processing of creating a Quick Information Source Draft or a Quick Dimension Draft, some warnings or errors might be generated. It is important to go through the warnings and errors to make sure that the Information Source or Dimension to be generated really works as expected.
Warning/Error | Remedy |
---|---|
This column is marked as a Parent Key. But Reference Information is missing in the view comments. | Though view comment has the flag
P , a reference is missing in the view comment. Reference is
required in order to automatically find a dimension or to create a quick
dimension. Manually enter a reference and/or a dimension id. |
Some of the specified parent keys for column
COLUMN_NAME do not exist in the Quick Information Source |
Correctly specify the column names. Note that the column names should be separated by a comma. |
The column COLUMN_NAME has been used as a
parent. A Dimension should be specified. |
When a column is specified in the parent keys, that column should be connected to a dimension in order to correctly define the join between the dimension and the fact. |
The parent of the connected dimension and connected dimension for the parent column does not match | The parent dimension item does not exist in the specified parent dimension. |
There are warnings for the connected quick dimension draft | |
There is a mismatch between number of parent columns and parents of the connected dimension. The number of parent keys in the quick information source and the parents of the connected dimension should match for the join information to be created successfully. | Modify parent info for the quick dimension draft or the parent info, reference, for the IS column. |
Dimension has more than one key column. The generated Information Source join information may need to be modified manually. The dimension to be connected should only have only one code item. | Correct the quick dimension by marking one of the key columns as an ID column and specify a parent item in the parents. |
Dimension <dimension_id> is connected more than once. A dimension can only be connected to a fact once. | If the reference is the same for two information source items, the
remedy would be to create another quick dimension for one of the columns and
connect it.
E.g. Columns COMPANY and PARENT_COMPANY both has reference to LU |
Total number of key columns should be equal to 1. It might not be possible to connect the dimension while creating the Information Source. The generated join information may not be valid if not modified manually. | A Dimension should only have only one code item. The remedy would be to mark one of the key columns as a ID column and specify a parent item in the parents. |
Parent information has not been defined for all the parent keys. | When a dimension is connected and the parent keys are specified, the parents of the dimension should exactly match the number of columns in the parent key. |