Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Joining Global Views and Key Flexfield Views in BI Tools

Global views generated in a NOETIX SYS schema can have columns generated for multiple instances of a key flexfield. If these instance-specific key flexfields support multiple structures, then a join-to (Z$) column is generated in the view for each instance-specific key flexfield column that enables you to join them to the key flexfield view. Because BI tools such as Oracle Discoverer, Oracle BI, Cognos BI, and SAP BusinessObjects do not support multiple joins between views, a key flexfield view is created for each instance-specific key flexfield column available in the global views when global views are generated into the meta-layer of these BI tools. In these BI tools, the Z$ column available in a global view corresponding to each instance-specific key flexfield column can be used to join the respective key flexfield view.

Note: When you generate global views into Oracle Discoverer, the joins between the global views and the key flexfield views are established automatically. Therefore, in Oracle Discoverer Desktop, the Z$ columns generated for each instance-specific key flexfield column in a global view will not be displayed in the view. However, these columns can be viewed using Oracle Discoverer Administrator.

For example, the POG0 PO Distributions global view contains multiple instances of the Accounting Flexfield. These instance-specific key flexfields correspond to accrual account, budget account, charge account, and variance account. For these instance-specific key flexfields, the following columns are generated in the view:

  • AcctAcrl (Accrual account)

  • AcctBdgt (Budget account)

  • AcctChrg (Charge account)

  • AcctVar (Variance account)

For each of these instance-specific key flexfield columns, a Z$ column is generated in the view that can be used to join to the XXK GL Acct key flexfield view. Therefore, the following Z$ columns are generated in the view:

  • Z$AcctAcrl$XXK GL Acct

  • Z$AcctBdgt$XXK GL Acct

  • Z$AcctChrg$XXK GL Acct

  • Z$AcctVar$XXK GL Acct

When the POG0 PO Distributions global view is generated into the meta-layer of the BI tools mentioned earlier, a key flexfield view is created for each of the instance-specific key flexfield columns. These key flexfield views will have the instance-specific key flexfield column labels in the suffix. For each of the instance specific key flexfield columns mentioned earlier, the following key flexfield views are created:

  • XXK GL Acct AcctAcrl

  • XXK GL Acct AcctBdgt

  • XXK GL Acct AcctChrg

  • XXK GL Acct AcctVar

The following diagram shows how the Z$ columns are provided for the instance-specific key flexfield columns in the NOETIX SYS schema and how the key flexfield views are created for these columns in the presentation model for the POG0 PO Distributions view:
Published:

Joining Global Views and Key Flexfield Views in BI Tools

Global views generated in a NOETIX SYS schema can have columns generated for multiple instances of a key flexfield. If these instance-specific key flexfields support multiple structures, then a join-to (Z$) column is generated in the view for each instance-specific key flexfield column that enables you to join them to the key flexfield view. Because BI tools such as Oracle Discoverer, Oracle BI, Cognos BI, and SAP BusinessObjects do not support multiple joins between views, a key flexfield view is created for each instance-specific key flexfield column available in the global views when global views are generated into the meta-layer of these BI tools. In these BI tools, the Z$ column available in a global view corresponding to each instance-specific key flexfield column can be used to join the respective key flexfield view.

Note: When you generate global views into Oracle Discoverer, the joins between the global views and the key flexfield views are established automatically. Therefore, in Oracle Discoverer Desktop, the Z$ columns generated for each instance-specific key flexfield column in a global view will not be displayed in the view. However, these columns can be viewed using Oracle Discoverer Administrator.

For example, the POG0 PO Distributions global view contains multiple instances of the Accounting Flexfield. These instance-specific key flexfields correspond to accrual account, budget account, charge account, and variance account. For these instance-specific key flexfields, the following columns are generated in the view:

  • AcctAcrl (Accrual account)

  • AcctBdgt (Budget account)

  • AcctChrg (Charge account)

  • AcctVar (Variance account)

For each of these instance-specific key flexfield columns, a Z$ column is generated in the view that can be used to join to the XXK GL Acct key flexfield view. Therefore, the following Z$ columns are generated in the view:

  • Z$AcctAcrl$XXK GL Acct

  • Z$AcctBdgt$XXK GL Acct

  • Z$AcctChrg$XXK GL Acct

  • Z$AcctVar$XXK GL Acct

When the POG0 PO Distributions global view is generated into the meta-layer of the BI tools mentioned earlier, a key flexfield view is created for each of the instance-specific key flexfield columns. These key flexfield views will have the instance-specific key flexfield column labels in the suffix. For each of the instance specific key flexfield columns mentioned earlier, the following key flexfield views are created:

  • XXK GL Acct AcctAcrl

  • XXK GL Acct AcctBdgt

  • XXK GL Acct AcctChrg

  • XXK GL Acct AcctVar

The following diagram shows how the Z$ columns are provided for the instance-specific key flexfield columns in the NOETIX SYS schema and how the key flexfield views are created for these columns in the presentation model for the POG0 PO Distributions view:
For an optimal Community experience, Please view on Desktop