Example - QIS with nested parent LU references¶
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
- Source View = BUDGET_TEMPLATE_ROW We have selected a view in the application owner schema.
- Quick Information Source ID = QFACT_BUDGET_TEMPLATE_ROW 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|
|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)^|
We also know the following:
- The LU/entity CompanyFinance is pre-defined, via basic data, to refer to the standard dimension DIM_COMPANY.
- The LU/entity BudgetProcess is pre-defined, via basic data, to refer to the standard dimension DIM_BUDGET_PROCESS.
- The LU/entity BudgetTemplate is in this example assume not to refer to any pre-defined standard dimension.
Drafting the Fact¶
When drafting a fact from an existing view definition, the following actions are performed:
- Identify the items
- Measure items
- Dimension items
- Light items
- Draft/connect possible dimensions
- If a dimension already exists, i.e. references information between LU and dimension exists, then connect the existing dimension.
- Else draft a Quick Dimension and connect it.
- Draft the fact-dimension joins
- Validate dimensions
- Validate join information for each dimension
- Log the initial warnings/errors
- Try to correct initial warnings/errors automatically.
- Revalidate and log warnings/errors.
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:
- Column names which ends with a $ character.
Based on the column comments the columns are grouped as follows:
Measure Items 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:
- Dimension Items Items that are used when connecting dimensions to the fact/Info Source. 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.
- Light Items All the columns which do not fall into either Measure items or Dimension Items are selected as light items.
Draft/Connect Possible Dimensions¶
The Dimension Items identified during the previous step will be used to connected the fact/IS to the actual dimension during this step.
- Get reference information for each column and find referenced LU
- Look up this reference LU in a special entity that keeps connections between LUs and dimensions.
- If a LU is found then the associated dimension will be used.
- If not, an attempt will be made to draft a new Quick Dimension.
- The connection between the Quick Information Source Draft and the found dimensions is now done.
Draft the Fact-Dimension Joins¶
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.
- Create the dimension ID column 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 Tabular Models powered by SSAS. E.g. the column BUDGET_TEMPLATE_ID has the following reference::
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:
Column CODE\_B has the following reference:
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_IDDECODE(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.
Creating the natural key join information. 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.
Corresponding columns in
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)
- Get the parent keys in the fact (QFACT_BUDGET_TEMPLATE_ROW) for each dimension, e.g. for QDIM_BUDGET_TEMPLATE via the column BUDGET_TEMPLATE_ID
...^REF=BudgetTemplate(COMPANY, BUDGET_PROCESS_ID)^ -> COMPANY, BUDGET_PROCESS_ID as parent columns
2. Get the parent keys from the dimension, QDIM\_BUDGET\_TEMPLATE, in this case represented by the dimension items:
parent1 item: DIM_COMPANY.CODE parent2 item: DIM_BUDGET_PROCESS.CODE
3. Number of parents must match. 4. Create the joins between the parent columns in the fact and the OWN columns referred by each parent dimension item.
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
Drafting a Quick Dimension¶
When drafting a Quick Dimension from an existing view, the following actions are performed:
- Identify the items
- Identify the parents
- Identify the Code Item
- Generate the Unique Identity column (Dimension ID column)
- If parent dimensions already exists, specify them as a parents.
- Else Draft a Quick parent Dimension and specify it as a parent.
Identify the Items¶
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:
- Column names which ends with a $ character.
Identify the Parents¶
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.
Identify the Code Item¶
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
CODEitem if there are more than one column in the LU defined as key (
Generate the Unique Identity Column (Dimension ID column)¶
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:
LU and Dimension References¶
Relations between LUs (Logical Units/entities) 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 Set as Default is also defined. 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. This is done by creating a new quick dimension based on another source view related to LU/entity AccountingYear.
Warnings, Errors and Remedies¶
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.
|This column is marked as a Parent Key. But Reference Information is missing in the view comments.||Though view comment has the flag
|Some of the specified parent keys for column
||Correctly specify the column names. Note that the column names should be separated by a comma.|
||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.|