| Oracle® Fusion Applications Developer's Guide 11g Release 1 (11.1.1.5) Part Number E15524-01 |
|
|
View PDF |
| Oracle® Fusion Applications Developer's Guide 11g Release 1 (11.1.1.5) Part Number E15524-01 |
|
|
View PDF |
This chapter provides guidelines and best practices for designing and securing Oracle Application Development Framework (Oracle ADF) view objects and other supporting business component objects for use by Oracle Business Intelligence Applications.
This chapter includes the following sections:
Section 59.1, "Introduction to View Objects for Oracle Business Intelligence Applications"
Section 59.3, "Understanding Oracle Business Intelligence Design Patterns"
Section 59.5, "Designing and Securing Dimension View Objects"
Section 59.9, "Supporting Flexfields for Oracle Business Intelligence"
The view objects that are designed and created for Oracle Business Intelligence Applications (Oracle BI Applications) are shared between Oracle Transactional Business Intelligence and Oracle BI Applications.
The Oracle BI Applications warehouse is populated from Fusion application databases using an ETL (extract, transform, and load) process. The ETL tool uses the ETL tool to source data from the source system (Oracle Fusion Applications) to the target Oracle BI Applications tables. The extract from the source system is done using the Oracle Application Development Framework (Oracle ADF) view objects.
Figure 59-1 illustrates the Oracle Business Intelligence architecture.
Oracle BI Enterprise Edition (Oracle BI EE) needs to efficiently access data from two or more master/detail-linked view objects in order to aggregate, present, or report on that combined data set. An essential requirement is to efficiently retrieve the multiple-levels of related information as a single, flattened query result, in order to perform subsequent aggregation or transformation on it. Oracle ADF Composite View Object API allows the caller to create a new view object at runtime that composes the hierarchical results from two or more existing view-linked view objects into a single, flattened query retrieving the same effective set of data.
From a performance perspective, such queries would need to be performed on low-level data in Oracle BI EE, since the Oracle ADF layer does not directly support aggregation. This would generally slow query performance down. Also, going through additional servers (that is, JavaHost and Oracle ADF) in the network would also be slower than directly querying the database. Therefore, the SQL Bypass feature has been introduced to directly query the database and push aggregations and other transformations down to the database server, where possible, thereby reducing the amount of data streamed and worked on by Oracle BI EE.
The SQL Bypass functionality in Oracle BI EE utilizes the Composite View Object API feature to construct and return a flattened SQL Bypass query that incorporates all of the required columns, filters, and joins required by the Oracle Business Intelligence query. Oracle BI EE then executes this query directly against the database.
When designing view objects for Oracle Business Intelligence Applications, you should use the following guidelines with regards to entity objects, associations, view objects, view links, and view criteria.
An entity object represents a row in a database table and simplifies modifying its data by handling all data manipulation language (DML) operations for you. It can encapsulate business logic for the row to ensure that your business rules are consistently enforced. Entity objects are required for all Oracle Business Intelligence view objects to support SQL pruning of declarative view objects and to leverage many Fusion specific features. For more information see "Creating a Business Domain Layer Using Entity Objects" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
All attributes from the physical table (with the exception of special, highly sensitive attributes) should be exposed on the entity objects.
An association reflects relationships between entity objects and can be by either reference or composition. All view objects composed of multiple entity objects are flattened using entity object associations.
For more information about associations, see "Creating Entity Objects and Associations" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. For more information about flattening, see Section 59.3.1, "Understanding Flattened View Objects."
A view object represents a SQL query. You use the full power of the familiar SQL language to join, filter, sort, and aggregate data into exactly the shape required by the end-user task. For more information, see "Defining SQL Queries Using View Objects" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
This section includes some technical requirements, how to use declarative SQL mode, and guidelines regarding view object attributes and outer joins.
The following are the technical requirements driven by use of the Composite View Object API, SQL Bypass, and SQL Pruning.
Composite View Object API
Use view links to establish relationships between view objects.
View links must not contain custom SQL functions such as TRUNC and BETWEEN.
Use the BI_JOINTYPE custom view link property to define outer joins on view links.
There is no support for Java or Groovy calculated attributes.
Programmatic view objects, transient view objects, and transient attributes are not supported.
SQL Bypass
Full SQL can be obtained at runtime using vo.getQuery().
There is no support for transient attributes.
View objects must not contain bind parameters.
There is no support for Java logic or Java calculated attributes.
Do not apply data security view criteria programmatically.
If you are using Multi-Organization Access Control (MOAC) you must not enable MOAC for the view objects for Oracle Business Intelligence Applications. You should use the underlying Fusion Data Security instead.
For more information, see "About Specifying a SQL Bypass Database" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
SQL Pruning
You should create your view objects in Declarative SQL Mode.
For more information about Declarative SQL Mode, see "Working with View Objects in Declarative SQL Mode" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
You should set primary entity usage to identify the fact and dimension grain because primary entity usage cannot be pruned.
You must set the Selected in Query property for non-primary key and unsecured attributes to false.
You should limit view criteria on non-primary entity derived attributes because attributes used in applied view criteria cannot be pruned.
You should limit order by clauses on non-primary entity derived attributes because attributes used in applied order by clauses cannot be pruned.
As a general rule, you should include all attributes from the underlying primary and reference entity objects in your view objects.
Flex attributes are an exception from this rule. These attributes are not required because they are exposed using the Flex Extender utility.
You should only include name and description attributes from the reference entity objects that are included to only resolve ID and Code columns.
You should include Standard Who Columns from all participating entity objects on your view objects for Oracle Business Intelligence Applications. This is to support Oracle BI Applications's Change Data Capture requirements.
Exceptions include entity objects that are only included to resolve ID and Codes into meaningful descriptions. For example, entity objects included to only resolve Business Transactional Intelligence-only attributes into a view object using entity object associations.
Table 59-1 shows the Standard Who Columns.
Table 59-1 Standard Who Columns
| Standard Who Columns | Description |
|---|---|
|
|
The user who created the row. |
|
|
The date and time the row was created. |
|
|
The user who last updated the row. |
|
|
The date and time the row was last updated. |
|
|
The session login associated to the user who last updated the row. |
You should set the Selected in Query property to be false on all non-primary key view object attributes.
You should resolve duplicate attribute names on view objects, which are made up of multiple entities, by using an attribute prefix.
Use an alias property as both the table alias and column alias in the SQL as well as the view object attribute prefix. For example:
The POLinesVO includes both the HeaderEO and the LinesEO.
The LinesEO is specified as the primary entity on POLinesVO. The HeaderEO is specified as a reference entity.
This view object includes HeaderId attributes from both HeaderEO and LinesEO.
To avoid duplication of attributes across Header and Lines entities, an entity object alias is specified. For example, Header and Lines for HeaderEO and LinesEO respectively.
The POLinesVO is then created using Header as the prefix for all Header attributes, and Lines as the prefix for all Lines attributes. For example, HeaderHeaderId and LinesHeaderId; HeaderBusinessUnitId and LinesBusinessUnitId.
Use the following guidelines to resolve view object foreign keys:
If the foreign key is a dimension, Oracle Business Intelligence requests a dimension view object and a view link to the dimension view object.
If the foreign key is a warehouse domain, Oracle BI Applications requests a view object for ETL. No view link is requested. Oracle BI EE lookup functionality is used to resolve foreign keys.
If the foreign key is neither a dimension nor a warehouse domain, you should should resolve the foreign key using entity object associations. For MLS-enabled entities, ID and Code attributes should be resolved using _VL views.
An outer join is generally required when creating a view object based on multiple entity objects, so as to handle situations when not all of the reference entities' values are present. The specific outer join type (left, right, or full) used should be determined based on the expected data relationships between the primary and reference entities. Note, however, that in some cases, security considerations will require an inner join, instead. (For an example, see Section 59.4.1, "Designing Fact View Objects.") If a join is required to resolve an ID or Code attribute, use a _VL view instead.
View links are required to flatten view objects using the Composite View Object API.
To define outer joins on view links, you must add the BI_JOINTYPE custom property on the view link definition. Valid values for this custom property include:
LEFTOUTER
RIGHTOUTER
FULLOUTER
INNER (default)
For more information, see "Working with Multiple Tables in a Master-Detail Hierarchy" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
A view criteria identifies filter information for the rows of a view object collection.
Required filters for view objects for Oracle BI Applications should be created using named view criteria. This includes:
Security filters
For more information about security filters, see Section 59.3.4, "Understanding Business Intelligence Filters."
Functional filters for Transactional Business Intelligence or Oracle BI Applications.
Only filters required by both Transactional Business Intelligence and Oracle BI Applications should be created for view objects that are shared by both products.
Filters to distinguish different logical entities based on the same entity object. (For single entity object view objects).
For more information, see "Working with Named View Criteria" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
This section discusses Oracle Business Intelligence design patterns including flattened view objects, fact-dimension relationships, self referencing entities, filters, and translations.
The grain of a fact table represents the most atomic level by which the facts may be defined. The fact or dimension grain required for Oracle Business Intelligence modeling should determine the flattening required for view objects. You should only create flattened view objects for fact or dimension levels required for either Transactional Business Intelligence or Oracle BI Applications. For example, if neither Transactional Business Intelligence nor Oracle BI Applications requires (purchase order) PO Shipments, then do not create a flattened POShipmentsVO.
When flattening entity objects in a view object, include only entity objects that do not change the grain of the fact or dimension. For example:
If attributes from a backing requisition line are needed on the POLinesVO, then the Requisition Line entity object should only be included in the flattened POLinesVO if the join does not change the grain of the POLinesVO to Requisition Line.
A 1:n relationship requires two view objects only if you want to aggregate attributes from the child and store the result at the grain of the parent.
Flattened view objects should be modeled in the Oracle Business Intelligence layer as a single logical table with multiple logical table sources.
You should follow these rules when designing and creating fact and dimension view objects:
Create separate view objects for fact entities and dimension entities.
Do not flatten relationships between facts and dimensions into a single view object.
Create a view link between the FactVO and the DimensionVO.
Specify the FactVO as the source of the view link.
Specify the DimensionVO as the target of the view link.
In the case of a fact view object where the self-joins represent two different but functionally important objects, you should create separate view object instances that represent the two objects. You should then define a view link between them.
If the self-join does not need to be represented as separate objects, you should resolve the Foreign Key ID column to a more meaningful column. For example:
The InvoiceheaderVO contains the following attributes:
InvoiceId
InvoiceNum
TaxRelatedInvoiceId
CreditedInvoiceId
If you decide that these should be modeled as three separate facts, then you create two additional view instances, TaxRelatedInvoicesVO and CreditdInvoicesVO, with view links to the InvoiceHeaderVO.
If you decide that they do not need to be modeled as separate objects, then you should create the two additional joins inside the InvoiceHeaderVO to bring in TaxRelatedInvoiceNum and CreditedInvoiceNum.
Row and Column flattening is required for view objects with self-joins that are modeled as dimensions in Oracle Business Intelligence Applications. You should determine the level of flattening required on a case-by-case basis.
Only filters that are common to both Transactional Business Intelligence and Oracle BI Applications should be defined on shared view objects. If Transactional Business Intelligence requires additional filtering for an Transactional Business Intelligence specific application then it should be defined on the Oracle BI EE layer. If Oracle BI Applications needs to filter data from a shared view object for extraction, these filters need to be defined in the ETL layer.
Also note that view criteria cannot be pruned from the SQL at runtime.
All Fusion translatable entities with a corresponding _TL table require entity objects based on both _B and _TL entity objects. You should create a flattened view object to join _B and _TL entity objects.
Oracle BI Applications performs ETL (extract, transform, and load) processes from the flattened view object with no additional filters. However, Transactional Business Intelligence requires an additional session language filter in Oracle Business Intelligence layer.
Note:
The entity object associations required for ID and Code resolutions to Multi-Language Support-enabled entities should use a
_VL view.All date effective entities for a logical fact or dimension should be flattened and adhere to the following:
Date effective entity objects and view objects should be marked as such according to Oracle ADF.
Flattening requirement excludes scenarios where other design considerations require not flattening the entity objects in the view object. For example, 1:n relationships.
Both entity objects are date effective.
The PersonsVO should be flattened to include both PersonEO and PersonDetailEO and should also be marked as Date Effective.
In other words, there should be a single current person details record for each person record.
For more information, see "How to Store Data Pertaining to a Specific Point in Time" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
Oracle BI Applications identifies any date effective entity objects from which historical information is needed; single view object flattening does not meet their requirements. To compensate, you need to:
Create a separate view object for these entity objects. The entity object is removed from the flattened view object.
Create view links to join these view objects.
You must only include one historical entity object for any given view object in Oracle BI Applications.
You should still mark view objects as date effective so that Transactional Business Intelligence can share and date effective predicate can be applied in the Oracle Business Intelligence layer.
Separate view objects should be created for fact entities and dimension entities. Relationships between facts and dimensions should not be flattened into a single view object. Instead, you should create a separate FactVO and DimensionVO and then create a view link between them. Specify the FactVO as the source of the view link, and the DimensionVO as the target of the view link.
A flattened view object should be created for each logical fact grain in Transactional Business Intelligence and Oracle BI Applications. For example:
A purchase order contains four fact levels: Header, Lines, Shipments, Distributions. Flattened view objects should be created to represent each of the four fact grains, as shown in Table 59-2.
Table 59-2 Flattened View Objects Based on Fact Grains Example
| Flattened View Objects | Fact Grains |
|---|---|
|
POHeaderVO |
Header |
|
POLinesVO |
Header + Lines |
|
POShipmentsVO |
Header + Lines + Shipments |
|
PODistributionsVO |
header + Lines + Shipments + Distributions |
Entity objects can be included in flattened view objects as required, as long as the view object grain does not change.
Note:
View links are not required between these view objects.Join Type for Multi-Level Facts
Join types on entity associations between multi-level facts should be inner joins. This is because there are some security impacts if entity associations are modeled as outer joins. For example:
To support the query "Show me all PO headers with no associated distributed rows". If an outer join is used, you would need to implement security on both the header and the distribution entities in the DistributionVO. This would prevent pruning of the header entity from the DistributionVO; it is also a change from current guidelines to only secure the primary entity.
The following are general guidelines for securing fact view objects. The sub-sections describe different design patterns that may arise for Oracle Business Intelligence use cases. Also included are solutions for each design pattern.
Fusion Data Security view criteria should be applied to the fact view object.
For more information about Fusion Data Security view criteria, see Section 47.3.2, "How to Secure Rows Queried By Entity-Based View Objects."
The data security view criteria should contain:
Privilege – Relevant object privilege.
Object – Object being secured.
For Multi-Organization Access Control (MOAC) style grants, the object being secured is Business Unit, based on the way MOAC grants are authored. For other grants, it can be the transactional object.
Alias – Alias for object.
Alias is mandatory for view objects for Oracle Business Intelligence Applications privileges.
For example, For Payment Invoices fact view object using Business Unit security (MOAC style), the privilege is:
"FNDDS__AP_MANAGE_PAYABLES_INVOICE_DATA__FUN_ALL_BUSINESS_UNITS_V__BU"
The fact view object requires an entity object for securing the table. (BU in this example). The join between the fact and the securing table should be properly resolved. The alias used in the view criteria should be that of the entity object corresponding to the Object in privilege (BU in this example).
If a non-MOAC grant is made for a transaction object, such as, for example, the Payment fact of the Fusion Incentive Compensation Management (ICM) Application, the object and alias refer to the ICM Payment entity. For example:
"FNDDS__VIEW_INCENTIVE_COMPENSATION_PAYSHEET_DATA__IC_INCENTIVE_COMPENSATION_PAYSHEET__ICPAY"
In Oracle Fusion Applications, transaction data can be secured by more than one entity, based on the role used to access the transaction data. For example, consider the case of the Fusion Incentive Compensation Management (ICM) Application, in which:
Role Incentive Compensation Paysheet Management Duty can see Incentive Compensation paysheets for the participants for whom they are responsible.
Role Incentive Compensation Process Management Duty can see Incentive Compensation paysheets for the business units for which they are authorized.
In the above case, because the view object for the transaction object implements single data security privilege, the privilege should be able to provide access based on business unit as well as participants. Building this privilege provides a logical filter similar to:
"for the participants for who they are responsible" OR "for business units for which they are authorized"
You can achieve this by creating a new privilege and having two policies created using the same privilege. One policy should be created using instance set to provide "for business units for which they are authorized", and the second policy should be created using instance set to provide "for the participants for who they are responsible". The policies should be granted to existing roles.
To secure transaction by more than one entity:
The following steps are based on the Fusion ICM Paysheet use case.
Create a new data privilege titled View Incentive Compensation Paysheet Data.
Author the following data security policies, using existing duties and the new data privilege defined in Step 1:
<Incentive Compensation Paysheet Management Duty> can <view> <Incentive Compensation Paysheets> <for the participants for who they are responsible>
<Incentive Compensation Process Management Duty> can <view> <Incentive Compensation Paysheets> <for business units for which they are authorized>
Define the following grants:
For the data security policy described in Step 2a:
For this data security policy, you should attach the View Incentive Compensation Paysheet Data data privilege to the same FND_MENU that contains the grant for the Manage data privilege. This grants VIEW privileges to the same roles that have Manage privilege, reducing the number of grants to be managed.
For the data security policy described in Step 2b:
For this data security policy, you would create a non-MOAC grant against the Incentive Compensation Paysheet object against the business unit (BU) data role. This grant is parameterized instance set based, with the instance set returning Paysheet data by BU, using BU on the data role as the parameter value. This grant carries only the VIEW data privilege.
Note:
This is a data role grant and the role and grant is generated during the implementation phase using the data role template.Use privilege View Incentive Compensation Paysheet Data in data security view criteria in Incentive Compensation Paysheet. The view criteria should be like Example 59-1 assuming IC_INCENTIVE_COMPENSATION_PAYSHEET is the object registered in FND_OBJECT and ICPAY is the alias used for the entity object.
Caution:
With regards to the above proposal, if a user happens to have both Incentive Compensation Paysheet Management Duty and Incentive Compensation Process Management Duty roles granted; the Business Intelligence report will show theUNION of data, such as data for authorized business units *** AND *** data for responsible participants.
Whether such reporting behavior is acceptable should be decided on a case by case basis.
For Oracle BI Applications, the UNION effect for the above example, based on Oracle Fusion Incentive Compensation Management reporting (Access by Participants and access by business units), must be achieved in Oracle BI EE based on the OR join for individual dimensions. This can potentially be achieved by using two separate groups (one for business unit and another for participants) and having a user access to both groups (since predicates are ORed across Oracle BI EE groups).
There are other use cases that fall into same design pattern of a transaction being secured by multiple entities and Oracle Business Intelligence implementation needing UNION access. For example, in Oracle Fusion Projects, the transaction table Expenditure Item is secured by Business Unit as well as by Project. For Oracle Business Intelligence reporting, the query on Expenditure Item should return rows for authorized business units for a user as well as authorized project for user.
In general, while the Oracle Business Intelligence use cases for transaction being secured by multiple entities will be similar; application teams can make their own decisions about how they implement an Oracle Business Intelligence solution. For example, in the case of the Oracle Fusion Incentive Compensation Management and Oracle Fusion Projects applications, you can implement different solutions for achieving the same end results by having different styles of grants and roles. Therefore, application teams should choose their own implementation based on their existing roles and privileges, and the approach they want to take for Oracle Business Intelligence solution.
When transactions are analyzed in context of dimensions, sometimes the dimensions have their own security, which is not applicable for usage with the transaction.
For example, Grade data is secured using Fusion data security. When analyzing Assignment data, relevant information from the Grade dimension is required; however, the data security for the Grade dimension is not applicable when being used for analyzing assignments. Instead, the Grade dimension behaves as an unsecured source of data when used with assignment fact.
A solution for this use case is to create two view objects for the dimensions for which security is not required when analyzing fact. The two view objects should form two logical table sources (LTS) for the dimensions:
The first dimension view object implements data security. This is used in dimension browsing and can include all columns required for dimension browsing. To ensure BI EE uses the secured version for dimension browsing, make sure it is higher up in the list of Logical Table Sources (LTS) than the unsecured one.
The second dimension view object should be unsecured. To ensure that the unsecured view object is used to combine with the fact, physical joins should be defined between the physical fact table and the physical table for the unsecured version of the dimension view object.
Caution:
Dimensions, for which unsecured view objects are created, may contain sensitive attributes. If this is the case, then you must make sure that the unsecured view object does not contain these sensitive attributes.Dual (secured and unsecured) view objects are only required for entities that fall in this design pattern. Entities not requiring both secured and unsecured access do not require dual view objects.
Analysis of a fact may need reference information from another fact. In Transactional Business Intelligence, this is handled by creating a degenerate dimension for a fact whose attribute information is used in other facts. The degenerate dimension is just a logical layer entity in the RPD and it uses the same view object as the underlying fact. As a result, the data security for degenerate fact is the same as that of underlying fact table.
This may create a problem when the degenerate dimension is used in another fact that has different security than the degenerate dimension (or more accurately, the fact underlying the degenerate dimension). For example:
There is a degenerate dimension, Dimension A on top of Fact A.
Dimension A is used in Fact B as a reference.
Fact B is secured using a different dimension (or different privilege) than Fact A, which was used to source for Dimension A.
In such cases, the security of both Fact B and Fact A should be applied; where as the desired result was just to apply security of Fact B.
Multi-Org Access Control (MOAC) ADF infrastructure enables Fusion transaction applications to implement business unit based data security. Because the Oracle Business Intelligence technical stack works on the view definitions, the ADF Business Components MOAC infrastructure does not work for view objects for Oracle Business Intelligence Applications. These view objects should instead use underlying Fusion Data Security to support business unit based security.
This section discusses how to design and secure dimensions.
A flattened view object should be created for each logical dimension grain in Transactional Business Intelligence and Oracle BI Applications. For example, for the Geography dimension, the following view objects are required to represent each dimension grain:
Zip Grain — Zip Code
City Grain — City + Zip Code
State Grain — State + City + Zip Code
These should be modeled as a single Geography logical dimension table with multiple logical table sources, one for each of the dimension grains.
Create a view link between the Transactional Business Intelligence fact view objects that have Business Unit dimensionality to the common business unit dimension view object based on Business Unit ID.
If the dimension needs to be secured, then the FND view criteria should be applied on the dimension view object.
The following use case, is an example of how you should secure dimensions that are composed of multiple entities.
The Dimension Inventory Organization is composed of the following three entities:
InventoryOrgParameters
HrOrganizationUnits
HrLocations
Human Resources (HR) entities may have their own security. However, for the InventoryOrgParameters entity, only the security defined by inventory product Manage Inventory Org Parameters should be used. In other words, data security on HR entities should be ignored when consumed in InvOrg.
This use case is similar to Section 59.4.2.2, "Securing Transactions Different from Securing Dimensions," where unsecured view objects are used for dimensions.
The Dimension Business Unit is used to secure transaction data. When used in conjuction with transaction data, a secured version of the Business Unit, which can return business units allowed for a user for a function, is required. For example, a secured version of Business Unit is required to populate init block security variables for Oracle BI Applications.
However, if a user needs to browse only the business unit data, the user is allowed to see all dimensions. Therefore, it is deemed an unsecured dimension when dimension browsing in Oracle BI Applications. To use an unsecured view object for dimension browsing, make sure it is higher up in the list of LTSs than the unsecured one.
Separate view objects should be created for primary dimension entity and multi-valued dimension attribute entities. For example:
Using the Person model, the following view objects should be created:
PersonVO — Person Only
PersonAddressesVO — Addresses Only
PersonPhonesVO — Phones Only.
The following view links establish relationships between view objects:
PersonToAddressesVL — PersonVO -> PersonAddressesVO
PersonToPhonesVL — PersonVO -> PersonPhonesVO
Note:
The above example uses the Person model with a person having address and phone. Keep in mind that Transactional Business Intelligence models only the primary address and phone number while Oracle BI Applications can model more than one address and phone number per person.Junk dimensions should not be directly sourced from view objects. Oracle BI Applications should build them from Fact Stage tables. Transactional Business Intelligence should build them from the degenerate attributes in Fact tables.
Mini dimensions should not be sourced from view objects. Oracle BI Applications should build them from Dimension tables.
There are a number of situations in which a secured dimension view object must be deployed with an accompanying unsecured dimension view object. In this case, the term unsecured does not simply mean that security is disabled, but also that a subset of the column set of the secured dimension view object may also be excluded from the unsecured version.
Generally, the strategy for developing and deploying a pair of corresponding dimension view objects, where one is secured and the other unsecured, consists of the following:
A base dimension view object satisfying the basic, functional requirements for data retrieval is initially developed.
The base dimension view object is used to create a secured dimension view object by using the methods and strategies described earlier in this section.
An unsecured dimension view object is developed by manually creating an exact copy of the original base dimension view object.
The unsecured dimension view object is named <VO Name>ListPVO, where <VO Name> is the name of the base dimension view object.
The unsecured dimension view object is modified so as to exclude sensitive columns from its column sets.
The unsecured dimension view object is deployed in the same application module as its associated secured dimension view object.
Consuming applications must build View Links to both the secured and unsecured dimension view object definitions. Once the secured and unsecured dimension view objects have been deployed, you can begin developing models based upon them in Oracle Business Intelligence.
This section discusses the gregorian calendar as well as the special handling that is required for fiscal calendar, projects calendar, Timestamp columns, and role-playing data dimensions.
Date dimension view objects for the gregorian calendar are delivered through the ATG libraries.
You should create a view link between the gregorian calendar day level view object and all the facts that join with the date dimension. Create the view link with the Fact view object as the source and the day level view object as the target.
For all other calendars needed for the fact in a particular functional area, a view link should be created to the time dimension at the day level of the fact. For example, if the fact is at day level in Financials and the reporting calendar is fiscal (in addition to gregorian), view links should be created to the day level of the fiscal calendar.
If the fact is at the day level, you should create view links to the day level of the fiscal calendar only.
For all facts at the day level, the view link between the Fact view object to the Day Level flattened view object should include the ADJUSTMENT_PERIOD_FLAG = N condition to avoid double counting if the same day belongs to a normal period as well as an adjusting period.
Projects facts that need to be analyzed by the fiscal calendar requires a view link between the fact and the day level of the fiscal calendar on the date. Also required is a view link between the fact and the General Ledger on the Ledger ID column using the Fun_all_business_units_V table that is present in the fact side.
Projects facts that need to be analyzed by the projects calendar requires a view link between the fact and the day level of the projects calendar on the date. Also required is a view link between the fact and the pjf_bu_impl_all_v table on the Business Unit Id.
If the date column of a fact view object involves timestamp then teams will need to create a new SQL derived attribute to populate the date without the timestamp. A view link will also need to be created using the new date column of the fact view object and day level time dimension view object.
If the fact view object date column does not have the timestamp then it can be used for creating the view link.
If a lookup type is used as a dimension in Transactional Business Intelligence, you must deliver the dimension view object as follows:
Create a <Product short name>LookupsVO.
This new view object should be based on the product specific lookup view on FND_LOOKUPS.
If a view to FND_LOOKUPS is not available or not required for online transaction processing, the view object should be based on FND_LOOKUPS directly with an additional filter on all included Application IDs for the product.
View criteria should be created for each Lookup Type.
Create a view instance using the above view criteria for each dimension.
Foreign keys to low cardinality lookups, such as FND_LOOKUPS, should not be resolved in fact or dimension view objects. These should be resolved in the logical layer through the lookup function.
Business Transactional Intelligence-only low cardinality lookups should be resolved using entity object associations based on a _VL view.
Application trees managed by Fusion tree management infrastructure may be exposed to Oracle Business Intelligence systems, such as Oracle BI EE, for analysis. This is done by providing a view object that contains a column-flattened version of tree data joined with tree data sources. Such a view object is called a column-flattened view object for Business Intelligence (BICVO).
Designing and securing tree data for Oracle Business Intelligence involves the following activities:
Designing a column-flattened view object
Customizing the FND table structure and indexes
Using declarative SQL mode to design view objects
Securing Oracle ADF view objects for trees
Column-flattening is generally available for level-based trees. For those trees that may be exposed to Oracle Business Intelligence systems, such as Oracle BI EE, column-flattening for value-based trees also is available.
Figure 59-2 illustrates a generic example of a value-based tree.
Each node has a unique identity, in this case denoted by dot-separated numbers that correspond to the node's relative ordering in the overall parent-child structure. Such value hierarchies may be arbitrarily recursive (in terms of recurring node types), and are usually ragged, or unbalanced. There is only a general concept of "level" in these hierarchies, which refers to the path distance (or depth) from the root node to some specified node.
Two nodes the same distance from the root are thought of as being at the same level. However, unlike true level-based trees, there is no requirement for nodes at the same level to possess a common set of properties. In fact, a node in a value-based tree may have any arbitrary collection of properties. When these trees are used to represent dimensional hierarchies, facts, metrics, or transactions, values may be joined to any node. There is no constraint that facts or transactions only be joined to lowest-level nodes, as is usually the case with level-based trees.
The example value-based tree shown in Figure 59-2, also has multiple top-level or root-level nodes. Since it has five levels (or equivalently, a maximum depth of four), a column-flattened representation of this tree requires a minimum of five columns. This is illustrated in Table 59-3.
Note:
In practice, you would never have single node trees. However, root nodes 2.0 and 3.0 are in Figure 59-2 to simply illustrate multiple top-nodes.Table 59-3 Column-Flattened Representation of the Value-Based Tree Example
| C0 | C1 | C2 | C3 | C4 | Distance |
|---|---|---|---|---|---|
|
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
0 |
|
1.1 |
1.1 |
1.1 |
1.1 |
1.0 |
1 |
|
1.2 |
1.2 |
1.2 |
1.2 |
1.0 |
1 |
|
1.2.1 |
1.2.1 |
1.2.1 |
1.2 |
1.0 |
2 |
|
1.2.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
3 |
|
1.2.1.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
4 |
|
1.2.2 |
1.2.2 |
1.2.2 |
1.2 |
1.0 |
2 |
|
1.2.2.1 |
1.2.2.1 |
1.2.2 |
1.2 |
1.0 |
3 |
|
1.2.2.2 |
1.2.2.2 |
1.2.2 |
1.2 |
1.0 |
3 |
|
2.0 |
2.0 |
2.0 |
2.0 |
2.0 |
0 |
|
3.0 |
3.0 |
3.0 |
3.0 |
3.0 |
0 |
The following conventions apply to the logical column-flattened representation shown in Table 59-3.
The first column (C0) contains a complete enumeration of each node in the tree. In this example, each node is represented by the value of its unique identity.
Having the unique identity of each node of the hierarchy represented exactly once in the C0 column means that it is always possible to directly address each node, such as for purposes of joining with a transaction or measure, or for performing a calculation on that node.
The last column (C4 in the example) always represents the root node of some rooted ancestral path of the tree.
The intermediate ancestral path nodes between a given node in the C0 column and its ancestral root node in the C4 column, is represented by columns C1 through C3. Each column stores a reference to some node of the ancestral path, descending from C3 toward C0, filling each column (from right to left) with a reference to the next child node of the path. When a reference to the C0-th column node occurs, this reference is then repeated, if necessary, so as to pad the remaining columns until the C0 column is reached.
Having the complete ancestral path, with unused columns padded toward the C0 node value, facilitates more efficient drill down operations.
There is no implied ordering of the rows in the column-flattened representation. The complete hierarchy is represented by the table content, and a normalized representation can always be inferred or reconstructed from the flattened data set.
As far as Fusion tree management is concerned, the column-flattened representation always consists of a number of columns greater than, or equal to, the depth of the tree. If this were not the case, you would need a strategy for pruning or condensing the tree (for example, removal of intermediate nodes from the ancestral paths). On the other hand, having the number of columns exceed the depth of the tree is never problematic, because of the repeated padding of C0 node values.
ATG services allows you to specify some fixed maximum depth of up to 32 levels when defining a tree. For example, if you specify a 20-level tree, your column-flattened representation will contain 20 columns, C0 through C19, with padding of values toward the leaf, as shown in Table 59-4.
Table 59-4 Column-Flattened Value-Based Tree Fixed at 20 Levels
| C0 | ... | C15 | C16 | C17 | C18 | C19 | Distance |
|---|---|---|---|---|---|---|---|
|
1.0 |
... |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
0 |
|
1.1 |
... |
1.1 |
1.1 |
1.1 |
1.1 |
1.0 |
1 |
|
1.2 |
... |
1.2 |
1.2 |
1.2 |
1.2 |
1.0 |
1 |
|
1.2.1 |
... |
1.2.1 |
1.2.1 |
1.2.1 |
1.2 |
1.0 |
2 |
|
1.2.1.1 |
... |
1.2.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
3 |
|
1.2.1.1.1 |
... |
1.2.1.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
4 |
|
1.2.2 |
... |
1.2.2 |
1.2.2 |
1.2.2 |
1.2 |
1.0 |
2 |
|
1.2.2.1 |
... |
1.2.2.1 |
1.2.2.1 |
1.2.2 |
1.2 |
1.0 |
3 |
|
1.2.2.2 |
... |
1.2.2.2 |
1.2.2.2 |
1.2.2 |
1.2 |
1.0 |
3 |
|
2.0 |
... |
2.0 |
2.0 |
2.0 |
2.0 |
2.0 |
0 |
|
3.0 |
... |
3.0 |
3.0 |
3.0 |
3.0 |
3.0 |
0 |
Think of the tree in Figure 59-2 as a true level-based tree, with fixed levels, single top-nodes, and all leaf nodes residing at the same lowest level of the tree (such as level zero, represented by column C0). In this case, you would actually have three separate trees, and the tree rooted at node 1.0 would have the logical column-flattened representation shown in Table 59-5, assuming the same "pad toward leaf values" scheme as with the value-based tree.
Table 59-5 Column-Flattened Level-Based Tree Rooted at Node 1.0
| C0 | C1 | C2 | C3 | C4 | Distance |
|---|---|---|---|---|---|
|
1.1 |
1.1 |
1.1 |
1.1 |
1.0 |
1 |
|
1.2.1.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
4 |
|
1.2.2.1 |
1.2.2.1 |
1.2.2 |
1.2 |
1.0 |
3 |
|
1.2.2.2 |
1.2.2.2 |
1.2.2 |
1.2 |
1.0 |
3 |
The notion of distance from the root is still relevant, even though all of the leaf nodes are assumed to reside at the same level (level zero, or C0).
Attributes from the column-flattened version of the tree data use standard ADF Business Components attribute naming conventions. Attributes from the tree data sources also use the same naming convention, but are prefixed with DepN, where N is the zero-based height of the node within the tree; for example, Dep7EmployeeName or Dep13ProjectName. The Dep0 prefix is used for leaf nodes.
The following procedure is a summary of the overall process of defining and generating declarative BICVOs for trees. For more detailed information about the strategy for creating these BICVOs, see Section 59.8.4, "Guidelines for ATG-Registration and BICVO Generation" and Section 59.8.6, "Securing ADF Business Components View Objects for Trees."
To generate BICVO automatically using Tree Management:
Ensure that the namespace path /oracle/apps/fnd/applcore/trees/analytics is configured in Oracle Metadata Service (MDS). Example 59-2 shows a sample MDS configuration.
Example 59-2 MDS Configuration
<adf-mds-config xmlns="http://xmlns.oracle.com/adf/mds/config"
version="11.1.1.000">
<mds-config version="11.1.1.000" xmlns="http://xmlns.oracle.com/mds/config">
<persistence-config>
<metadata-namespaces>
<namespace path="/sessiondef" metadata-store-usage="mdsRepos"/>
<namespace path="/persdef" metadata-store-usage="mdsRepos"/>
<namespace path="/oracle/apps/fnd/applcore/trees/analytics"
metadata-store-usage="mdsRepos"/>
</metadata-namespaces>
<metadata-store-usages>
<metadata-store-usage id="mdsRepos">
<metadata-store name="fs1" class-name="oracle.mds.persistence.
stores.file.FileMetadataStore">
<property name="metadata-path" value="/tmp"/>
</metadata-store>
</metadata-store-usage>
</metadata-store-usages>
</persistence-config>
</mds-config>
</adf-mds-config>
Ensure that each view object attribute of the tree data source view objects is marked as relevant to Oracle Business Intelligence (or not) via the BI Relevant property that is exposed in the Property Inspector for the view object attribute.
Note:
By default, only primary key attributes are "BI Relevant". For performance reasons, it is recommended that only those attributes that are really relevant to Oracle Business Intelligence be marked as such to avoid generating very large BICVOs.Ensure that column flattening is enabled by specifying the column-flattened table and, optionally, the entity object for the table while setting up the tree structure. For more information, see Section 19.3.5, "How to Create a Tree Structure."
The tree management infrastructure then generates the BICVO for the tree structure into MDS.
Secure the generated BICVO using the data security infrastructure. For more information, see Section 59.8.6, "Securing ADF Business Components View Objects for Trees."
The generated BICVO includes a special view criteria named FNDDS__BICVO. In order to secure access to data through the BICVO, this view criteria must be enabled for instances of the BICVO in any application module. At runtime, data security rules affecting access to the tree data source view objects are automatically carried over to the BICVO.
Note:
In Oracle Fusion Applications V1, only filter-based data security rules are supported. In addition, only the "is descendant of" operator is supported.When using Oracle Fusion tree management to create and manage your trees, you should create and register its own, custom versions of the FND_TREE_NODE and FND_TREE_NODE_CF tables. This prevents applications from competing for use of the FND tables. Your custom tables must comply to the following rules:
They must have custom, (preferably application-specific) names; for example, PJF_PROJ_ELEMENTS_CF is currently being used by the Projects team to implement a column-flattened table for the Task Hierarchy.
The column names and column data types of each custom table must be exactly the same as those of the corresponding FND table.
Custom versions of FND_TREE_NODE_CF can define an index on each of the level-based foreign key references to support efficient drill-downs. However, it is understood that certain application query patterns do not necessitate this degree of indexing. Indexing is also not necessary if the column-flattened table is guaranteed to be relatively small.
Custom versions of the FND_TREE_NODE_CF should not include the ENTERPRISE_ID column as part of the primary key index defined on the custom table. This is because this column is not currently used by Oracle Fusion tree management.
All view objects for Oracle Business Intelligence Applications should be constructed in declarative SQL mode. This ensures that correct SQL pruning can be applied to any composite view object incorporating the Oracle Business Intelligence view object. This requirement also applies to the BICVO generated by Oracle Fusion tree management. However, of all the possible configurations of ADF Business Components objects defining a tree data source, only two configurations in particular actually lend themselves to the generation of declarative-mode BICVOs by Oracle Fusion tree management.
These configurations have been formalized as two distinct design patterns:
Design Pattern #1: Single data source view object, single data source entity object
Design Pattern #2: Multiple data source view objects, unique data source view object per depth of tree, single data source entity object per data source view object
Although either pattern can be used in the realization of either tree type, the first pattern is generally better suited to value-based trees, while the second pattern is more natural for level-based trees. However, the patterns are aimed primarily at supporting the automated generation of declarative-mode BICVOs, rather than supporting either particular type of tree.
This pattern ensures that Oracle Fusion tree management is capable of generating a declarative-mode BICVO from an Oracle Applications Technology (ATG)-registered data source. Figure 59-3 illustrates the ADF Business Components object configuration defining declarative BICVO pattern #1.
Figure 59-3 Declarative BICVO Based on Single Data Source View Object, Single Data Source Entity Object
In this pattern, there is a single data source entity object and a single data source view object based on that entity object. The data source view object is a declarative-mode view object built by developers and registered with Oracle Fusion tree management. The data source entity object in turn is based on a _VL database view that joins the data source base table (_B) with a table of translated values (_TL).
A second entity object is defined for the column-flattened table. Currently, the column-flattened table entity object must be created manually and made known to the generated BICVO via a manual workaround. Additionally, a collection of entity object associations, each joining the column-flattened entity object with the data source entity object for a unique level or depth of the tree, must also be created manually. If the application design requires that the base data source table expose multiple entity objects for any reason, then a _VL database view must be defined to join the multiple entity objects (possibly along with any translated attribute values), and that _VL database view must support the single data source entity object.
Once the data source view object is registered with Oracle Fusion tree management as part of the tree structure definition process, and the required manually-created objects are all in place, a declarative BICVO may then be generated by Oracle Fusion tree management.
This declarative-mode BICVO pattern is well-suited for value-based trees, since value-based trees are most often represented at the data source level by a single table with a recursive self-join. However, there is nothing about the pattern that strictly requires its use in value-based hierarchies, nor prohibits its use from other types of hierarchies (such as level-based or hybrid). The primary objective of this pattern is to facilitate the automatic generation of a declarative-mode BICVO from an ATG-registered tree.
This pattern ensures that Oracle Fusion tree management is capable of generating a declarative-mode BICVO from an ATG-registered tree. Figure 59-4 illustrates the ADF Business Components object configuration defining declarative BICVO Pattern #2.
Figure 59-4 Declarative BICVO Based on Multiple Data Source View Objects, Unique Data Source View Object per Level, Single Data Source Entity Object per Data Source View Object
In this pattern, there are multiple data source view objects, with a unique data source view object representing each level or depth of the tree. Each data source view object is based on a single, unique data source entity object. Each data source view object is a declarative-mode view object built by developers and registered with Oracle Fusion tree management. All of the data source view objects must be declarative-mode view objects; otherwise, a declarative-mode BICVO can not be generated. As with the previous pattern, each data source entity object in turn is based on a _VL database view that joins some data source base table (_B) with a table of translated values (_TL). While multiple _VL database views are represented in the diagram, there is no hard-and-fast requirement that each data source entity object actually be built on top of a unique _VL database view. The diagram simply admits the possibility of multiple such views, presumably one per level or depth of the tree.
The same as with design pattern #1, an entity object is also defined for the column-flattened table, and must also be created manually, and is made known to the generated BICVO via a manual workaround. This column-flattened table entity object is also joined to the data source entity objects via a collection of entity object associations. However, each entity object association relates the column-flattened table entity object to a unique data source entity object representing a particular level or depth of the tree.
If the application design requires that the base data source table expose multiple entity objects per tree level or depth, then a _VL database view must be defined to join the multiple entity objects (possibly along with any translated attribute values) at that tree level or depth, and that _VL database view must support the single data source entity object for that tree level or depth.
Once the data source view objects have been registered with Oracle Fusion tree management as part of the tree structure definition process, and the required manually-created objects have all been put in place, a declarative BICVO may be generated by Oracle Fusion tree management.
This declarative-mode BICVO pattern is well-suited for level-based trees, since level-based trees are often built on top of multiple data sources, with a unique data source per level. However, there is nothing about the pattern that strictly requires its use in level-based hierarchies, nor prohibits its use from other types of hierarchies (such as value-based or hybrid). The primary objective of this pattern is to facilitate the automatic generation of a declarative-mode BICVO from an ATG-registered tree.
In order to ensure correct SQL pruning, you must set the property values of the generated declarative-mode BICVO as follows:
Designate the column-flattened table entity object as the primary entity of the BICVO.
Designate the data-source entity objects as secondary or reference entities of the BICVO.
Do not mark primary key attributes of the data source entity objects as primary-key attributes in the resulting column-set. (These are exposed by the generated BICVO).
Set the selectedInQuery property of any non-primary key attribute of the generated BICVO to false.
The Oracle Fusion Applications team that owns the tree is responsible for creating a custom tree node (parent-child relationship) table that is structurally equivalent to FND_TREE_NODE. Once the tree node table has been created, it is registered with ATG via the Oracle Fusion tree management tree creation UI.
The Oracle Fusion Applications team is also responsible for creating a custom column-flattened table that is structurally equivalent to FND_TREE_NODE_CF. This custom table is depicted in Figure 59-3. Once created, it is also registered with Oracle Fusion tree management as the column-flattened table associated with the tree in the Oracle Fusion tree management creation UI.
The Oracle Fusion Applications team must then create both data source view objects and associated data source entity objects, according to either of the structural patterns illustrated in Figure 59-3 and Figure 59-4. As with the tree node and column-flattened tables, the data source view object is also registered with Oracle Fusion tree management, via the Oracle Fusion tree creation UI. During the registration process, the developer may specify a custom property on any of the data source columns, indicating to Oracle Fusion tree management that these columns are relevant to Oracle Business Intelligence and need to be exposed at each level within the BICVO. This collection of Oracle Business Intelligence attributes is represented by the set of view attributes attached to the data source view object. As a result, the generated BICVO will join these columns in from the data source entity object at each level of the tree, immediately following the level-specific data source foreign key references; that is, the sequence of DEP*_PK* columns are followed by a set of columns representing each of the BI-relevant attributes.
In addition to view attributes representing the Oracle Business Intelligence-relevant columns of the data source, the data source view object may also be configured with one or more view criteria filters. In particular, a view criteria must be defined to enforce data security if there's a requirement for data security at the source level. Any other relevant filters required by reporting may also be specified and attached to the data source view object. Each of these view criteria must specify a logical AND condition as its connective to other defined view criteria.
Next, using the Oracle Fusion trees creation UI, the developer automatically generates the BICVO; that is, the column-flattened BICVO based on the column-flattened table. In Figure 59-3 and Figure 59-4, dashed lines represent joins on the underlying entities that are automatically added by Oracle Fusion tree management to the BICVO definition at runtime. These joins are inferred by ATG internal generation logic via inspection of the data source view object and its attendant view attributes and view criteria, as well as inspection of the registered column-flattened table.
The BICVO, as generated by Oracle Fusion tree management, also includes a placeholder view criteria that is otherwise empty and specifies a logical OR condition as its connective to any other view criteria that might be defined as part of the BICVO. This placeholder view criteria is defined for data security purposes, and at the current time, simply directs ATG logic to invoke the data security view criteria defined on the data source view object.
Note:
There may also be a requirement to supply Oracle Data Integrator (ODI) with translations via a view object that is separate from the base data source table or_VL database view. In this case, you must develop a view object and entity object pair that directly goes against the translations table (_TL).You must take this entire collection of ADF Business Components objects, both hand-crafted and generated alike, and package them for deployment as part of an appropriate application module. Note that any ATG-generated artifacts, such as the BICVO, is generated to reside within the Oracle Fusion Middleware Extensions for Applications package namespace, which is:
oracle.apps.fnd.bi.applcore.trees.bi.model.view
Most of the Oracle Business Intelligence view objects and other artifacts are packaged under the Oracle Business Intelligence analytics namespace, which is:
oracle.apps.<LBATop>.<LBACore>.publicview.analytics
However, the Oracle Fusion Middleware Extensions for Applications package namespace is acceptable for ATG-generated objects seeing as they are artifacts of the ATG-Oracle Fusion Middleware Extensions for Applications services infrastructure. As long as the interfaces of these objects are publicly visible, this should not present any problems to clients of these objects.
It is possible for an inconsistency to arise between the three realizations of a particular application hierarchy across the application, and the Transactional Business Intelligence and Oracle BI Applications technologies.
Hierarchies on the Oracle BI EE server are necessarily limited to a maximum of 15 levels. However, Oracle BI Applications uses data warehouse tables to represent these hierarchies, and although the tables are not inherently bounded in size, restrictions on the number of levels of a given hierarchy being imported into the data warehouse are enforced by the ETL process. The majority of Oracle BI Applications hierarchies are fixed at eight levels plus a top-level for a total of nine fixed levels. A very small number of Oracle BI Applications hierarchies have greater than eight levels, plus a top-level and a base-level, with the largest of these hierarchies consisting of 21 fixed levels.
Trees, especially value-based trees, are generally unbounded in size. However, trees that have been implemented using Oracle Fusion tree management services are limited to 32 levels by the ATG infrastructure.
Problems can potentially arise when an application tree exceeds 15 levels. When this occurs, the corresponding Oracle BI EE representation of the tree, such as a Oracle Business Intelligence hierarchy stored within the repository (RPD), must be compressed to 15 levels. This is accomplished by retaining the leaf-level of the source tree (base-level), as well as the root-level (top-level), and pruning the tree starting with the base-1 level and working up the tree until enough levels have been removed.
Table 59-6 illustrates the general mapping of levels on the Oracle Business Intelligence hierarchy to levels or depths of application trees. The logical representation of the application tree is expressed in terms of the columns of the column-flattened Oracle Business Intelligence view object for that tree, which has a maximum of 32 levels. In this case the 15-level (maximum) Oracle Business Intelligence RPD representation of the hierarchy is mapped to the 32-level (maximum) application BICVO representation of the tree by pruning the levels of the source tree designated by columns C1 through c17 of the BICVO.
Table 59-6 Mapping Oracle Business Intelligence Hierarchy Levels to Application Tree Levels
| BI (RPD) | Application (BICVO) |
|---|---|
|
Top |
C31 |
|
Level Top +1 |
C30 |
|
..... |
..... |
|
Level Base - 1 |
C18 |
|
Base |
C0 |
When mapping application trees to Oracle Business Intelligence hierarchies, there are two types of problems that may arise:
The application tree exceeds 15 levels and the Transactional Business Intelligence realization of the hierarchy (provided by the Oracle BI EE server) has been pruned to 15 levels. However, the Oracle BI Applications realization of the hierarchy (provided by the ETL process) is allowed to exceed 15 levels. In this case, the Transactional Business Intelligence and Oracle BI Applications realizations of the hierarchy have different resolutions at their lowest levels.
The application tree exceeds 15 levels and the Transactional Business Intelligence and Oracle BI Applications realizations of the hierarchy are both pruned to 15 levels. In this case, Transactional Business Intelligence and Oracle BI Applications are the same in terms of resolution, but the Oracle Business Intelligence side and the application side are not the same. For example, the application tree has greater resolution than its Oracle Business Intelligence counterpart.
The following are two possible consequences that may result from the problems outlined:
Loss of information (loss of resolution) resulting from the pruning away of several lower levels of the hierarchy, as well as potential differences in information (resolution) between Transactional Business Intelligence and Oracle BI Applications.
An effect on fact-based security at the pruned levels. For example, you have established certain privileges on facts joined to nodes at tree levels that are ultimately pruned away. The security privileges of facts that had been joined to the pruned nodes may have been more restrictive than those at ancestral levels.
There are basically two choices for either completely resolving, or at least mitigating, the potential problems.
Note:
Neither of the following resolutions require any actual implementation work. However, they do require a combination of policy and documentation.Complete Resolution:
Any application tree that has a realization on the Oracle Business Intelligence side (Transactional Business Intelligence or Oracle BI Applications) must be restricted to no more than 15 levels.
Mitigation:
Ensure that, if any application tree exceeds 15 levels, and that tree has realizations on both Transactional Business Intelligence and Oracle BI Applications, that both technologies maintain pruned realizations of this tree and have the same number of levels (such as 15 or less).
For this resolution, it will be necessary that these situations be investigated and documented on a case-by-case basis. You must decide how you want to adjust the security privileges of metrics that had previously been joined to the pruned levels, and then revise your Oracle Business Intelligence models accordingly.
Data security privileges are effectively applied to the column-flattened representation of the tree (as described in Table 59-3) in the form of a filter based on an OR condition on the columns. For example, a reporting client has viewing privileges on nodes 1.1 and 1.2.2. This means that any row that contains either node in any of its columns (at any level in the tree) is viewable to the client, but the other rows are not. The viewable rows are shown in Table 59-7 in bold.
Table 59-7 Column-Flattened Result Set with Data Security
| C0 | C1 | C2 | C3 | C4 | Distance |
|---|---|---|---|---|---|
|
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
0 |
|
1.1 |
1.1 |
1.1 |
1.1 |
1.0 |
1 |
|
1.2 |
1.2 |
1.2 |
1.2 |
1.0 |
1 |
|
1.2.1 |
1.2.1 |
1.2.1 |
1.2 |
1.0 |
2 |
|
1.2.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
3 |
|
1.2.1.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
4 |
|
1.2.2 |
1.2.2 |
1.2.2 |
1.2 |
1.0 |
2 |
|
1.2.2.1 |
1.2.2.1 |
1.2.2 |
1.2 |
1.0 |
3 |
|
1.2.2.2 |
1.2.2.2 |
1.2.2 |
1.2 |
1.0 |
3 |
|
2.0 |
2.0 |
2.0 |
2.0 |
2.0 |
0 |
|
3.0 |
3.0 |
3.0 |
3.0 |
3.0 |
0 |
If the use of the DescendantOf hierarchical referencing operator is also available, enabling the display of rows that contain either 1.1, 1.2.2, or any descendant of either of these two nodes, then the viewable rows include the rows that are displayed in bold in Table 59-8.
Table 59-8 Column-Flattened Result Set with Data Security and DescendantOf Operator
| C0 | C1 | C2 | C3 | C4 | Distance |
|---|---|---|---|---|---|
|
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
0 |
|
1.1 |
1.1 |
1.1 |
1.1 |
1.0 |
1 |
|
1.2 |
1.2 |
1.2 |
1.2 |
1.0 |
1 |
|
1.2.1 |
1.2.1 |
1.2.1 |
1.2 |
1.0 |
2 |
|
1.2.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
3 |
|
1.2.1.1.1 |
1.2.1.1 |
1.2.1 |
1.2 |
1.0 |
4 |
|
1.2.2 |
1.2.2 |
1.2.2 |
1.2 |
1.0 |
2 |
|
1.2.2.1 |
1.2.2.1 |
1.2.2 |
1.2 |
1.0 |
3 |
|
1.2.2.2 |
1.2.2.2 |
1.2.2 |
1.2 |
1.0 |
3 |
|
2.0 |
2.0 |
2.0 |
2.0 |
2.0 |
0 |
|
3.0 |
3.0 |
3.0 |
3.0 |
3.0 |
0 |
Note:
The generalizedOR filter can be restricted. For example, to apply only to the C0 column. This ensures that only nodes and optionally their descendants, for which a client has sufficient privileges, are viewable from the column-flattened result set.The base table view object and column-flattened view objects (BICVO) are separate view objects. However, the data security definition must be consistently applied to both the base table view object and BICVO. For example, BICVOs must not have different data security behavior than the base entity on which security has been defined by Oracle Fusion Applications. This is achieved by Fusion tree management using the following process:
When Oracle Fusion tree management generates the BICVO, it automatically adds an Oracle Fusion data security view criteria to the BICVO (FNDDS__BICVO). You must not change this view criteria's name but must ensure that it is enabled for the application module for the Transactional Business Intelligence.
The view criteria predicate for BICVO is generated from the base table view object at runtime by Oracle Fusion tree management. This ensures that BICVO data security is in sync with the base object.
The following restrictions are placed on the base object view criteria so that the base view criteria is mapped to the BICVO view criteria, (which may have different column names), at runtime:
The base object view criteria must only use "Filter", which stores predicates using metadata. It cannot use SQL.
The base object view criteria must only use the DescendantOf hierarchy operator. It must not use any other hierarchy operators.
There may be situations in which a tree must support both secured and unsecured access. In this case, the BICVO that exposes the tree structure is deployed as both secured and unsecured versions.
The generated BICVO already has a security mechanism associated with it that is based on its data source view object. An unsecured version of the BICVO can be created by manually making a copy of the generated BICVO and editing it to exclude sensitive columns. Then, secured access to this edited BICVO is turned-off by de-activating the dummy FNDDS__BICVO view criteria associated with the BICVO. This causes the data source security view criteria to not be enforced. Again, both the secured and unsecured versions of the BICVO for the tree are to be deployed together in the same application module.
The following steps must be followed to allow the Flexfields ADF Modeler to generate a flattened view object containing only those attributes marked as BI Enabled:
Set the BIEnabledFlag for your key flexfield
Set the BIEnabledFlag for your descriptive flexfield
Create Flexfield business components
Define custom properties on the Oracle Business Intelligence application module
In order to enable your key flexfield for Oracle Business Intelligence, you must set the BIEnabledFlag to Y at both the flexfield level and the segment instance level. Example 59-3 shows how to do this.
Example 59-3 BI Enabled Key Flexfield
<KeyFlexfield>
<ApplicationId>0</ApplicationId>
<KeyFlexfieldCode>KFF1</KeyFlexfieldCode>
...
<TreeStructureCode>FND_FLEX_TEST_TREE_STRUCTURE1</TreeStructureCode>
<BIEnabledFlag>Y</BIEnabledFlag>
...
<StructureInstance>
...
<SegmentInstance>
<SegmentCode>L10_ZEROFILL</SegmentCode>
...
<DefaultValue isNull="true"></DefaultValue>
<BIEnabledFlag>Y</BIEnabledFlag>
...
</SegmentInstance>
...
<StructureInstance>
...
</KeyFlexfield>
Both of these BIEnabledFlag options default to N if they are not explicitly set.
If you want to equalize segments from different structure instances so that only a single attribute appears in the generated BICVO, you must assign a unique segment label to those segments. Non-unique segment labels are not equalized.
In order to enable your descriptive flexfield for Oracle Business Intelligence, you must set the BIEnabledFlag to Y at both the descriptive flexfield level and the segment level. This attribute is applicable to all three segment types (global, context, and context-sensitive), as shown in Example 59-4.
Example 59-4 BI Enabled Descriptive Flexfield
<DescriptiveFlexfield>
<ApplicationId>0</ApplicationId>
<DescriptiveFlexfieldCode>FLEX_DFF1</DescriptiveFlexfieldCode>
...
<Delimiter>.</Delimiter>
<BIEnabledFlag>Y</BIEnabledFlag>
...
<ContextSegment>
<ContextCode>Context Data Element</ContextCode>
<SegmentCode>Context Segment</SegmentCode>
...
<ReadOnlyFlag>N</ReadOnlyFlag>
<BIEnabledFlag>Y</BIEnabledFlag>
...
</ContextSegment>
...
<GlobalSegment>
<ContextCode>Global Data Elements</ContextCode>
<SegmentCode>GlobalSegment2</SegmentCode>
...
<ReadOnlyFlag>N</ReadOnlyFlag>
<BIEnabledFlag>Y</BIEnabledFlag>
...
</GlobalSegment>
...
<Context>
<ContextCode>VS_FRM_CHR_ON_CHR</ContextCode>
...
<Segment>
<SegmentCode>L10</SegmentCode>
...
<ReadOnlyFlag>N</ReadOnlyFlag>
<BIEnabledFlag>Y</BIEnabledFlag>
...
</Segment>
...
</Context>
...
<Context>
<ContextCode>VS_FRM_CHR_ON_CHR2</ContextCode>
...
<Segment>
<SegmentCode>L10</SegmentCode>
...
<ReadOnlyFlag>N</ReadOnlyFlag>
<BIEnabledFlag>Y</BIEnabledFlag>
<BIEqualizationTag>L10_TAG</BIEqualizationTag>
...
</Segment>
...
</Context>
...
<DescriptiveFlexfield>
Both of these BIEnabledFlag options default to N if they are not explicitly set.
Flexfields business components for Oracle Business Intelligence are created automatically in conjunction with the core flexfield business components. The same wizards used for creating core flexfield business components are used.
To create flexfield business components:
Create flexfield business components. A Flexfields Oracle Business Intelligence model is automatically created under a directory called bi in the package root directory.
oracle.apps.<lbaTop>.<lbaCore>.publicFlex.<flexName>.analytics.view
Flexfield view objects for Oracle Business Intelligence must be created using the same public entity objects that are being used to support the Oracle Business Intelligence model. These new flexfield view objects must reside in the public entity projects in the Oracle Fusion Applications workspace (jws).
Due to the length constraints in the Oracle Business Intelligence layer, view object names should be limited to 50 characters.
Caution:
If your flexfield uses hierarchical value sets, you must make sure that the flattened tree view objects are already in your project; otherwise, the wizard will report the missing view objects as errors.Create a master view object.
The same master view object for the core model can be used.
Create a flexfield view link from the master view object to the Flexfield Oracle Business Intelligence base view object. To distinguish the Flexfields Oracle Business Intelligence base view object from the core base view object, look for the prefix BI, as shown in Figure 59-5.
Create an application module.
Note:
Normally, the application module is created just for Oracle Business Intelligence. However, if you already have a product Oracle Business Intelligence application module, you may use it.Add an instance of the master view object. (Unlike the core model, a child flexfield view instance is not required.)
Add an instance of the flexfield Oracle Business Intelligence application module to the application module as a nested instance.
Note:
The flexfield Oracle Business Intelligence application module can be identified by the bi sub-package under the root package, as mention in Step 1.Using the Oracle BI EE development guidelines, define the custom properties required to link the master view instance to the default view instance inside the nested flexfield Oracle Business Intelligence application module instance. This default view instance is normally titled DefaultFlexViewUsage. Keep the following in mind when defining your custom properties:
BI Joins between view objects in different application modules are created during import from Oracle ADF if custom properties are defined on the application module.
The Property Name format must be BI_VIEW_LINK_<any name>.
The Property Value format must be <Source View Object Instance Name,<View Link Definition Name>,<Destination View Object Instance Name>.
Use the fully-qualified view object instance names for the SrcVO and DestVO, and the fully-qualified package name for the ViewLinkDefn, as shown in
To properly resolve meanings for set-enabled attributes, the setID attribute must be exposed to the Oracle Business Intelligence layer. The setID attribute should be exposed using the appropriate method for the following reference types:
Set-enabled lookups
Set-enabled reference tables
The setID is required to retrieve appropriate meaning if the lookup is set-enabled. The Set Assignments Query is required to retrieve the setID.
To expose the setID attribute:
Set-enabled lookups (shared and Transactional Business Intelligence) are registered as warehouse domains and the SetAssignment entity object is already provided by ATG.
Build an entity object association between the Fact entity object and the SetAssignment entity object for each set-enabled lookup on the fact.
Expose setID as an attribute on the FactVO for each set-enabled lookup type on the FactVO.
The Lookup function is used to retrieve the translated meaning from the warehouse using setID parameter.
The setID is stored on set-enabled reference tables. A Unique ID is used as the primary key of the reference table; ID and language form the unique key of the translated reference table. The determinant value is not stored on the reference table; the foreign key used to reference the table is stored on transaction tables.
To expose the setID attribute:
Because the foreign key to the reference table already exists on the transaction, meanings for set-enabled attributes should be resolved depending on usage.
Transactional Business Intelligence only:
Resolve meaning on the base view object using entity object association, bringing in the setID attribute.
Warehouse domain:
A separate view object is required. Build a view link from the base view object to the reference view object. The setID attribute exists on the reference table view object.
Oracle Fusion Middleware Extensions for Applications provides special MLS Currency view objects for Oracle Business Intelligence.
To support multi-currency, create view links from the primary entity currency code fields on transaction view objects to the new currency view object.
|
 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
