This blog is part of a series of best practice blogs for Oracle Analytics. This is a best practice for RPD administrators. For more information, see the Oracle Analytics Best Practices Series: Optimal Performance and Usage.
In the Oracle Analytics repository (RPD) you can set an Implicit Fact in the Subject Area. This blog describes a use case.
Different fact tables within the same business model often result in a different set of elements for the same query filters, e.g., the list of products for Revenue or Quota Amount for the month of January:
The values returned from a query of Select Month, Product from subject area A where month = ‘Jan’ will depend upon which fact table is used when executing the query.
Most queries contain a mixture of facts and dimensions, so the sources used are predictable and the results therefore match expectations. But when a query only contains dimensions, then OAC will choose a fact table using the best information it has, which may yield results that don’t match your expectations.
For this scenario, there is an option in the RPD to assign an implicit fact for a subject area. The implicit fact is then automatically included for any query that only includes dimensions from that subject area. This ensures a predictable fact source is always used, and that the query results will match your expectations.
The session log shows the implicit fact added to the logical query.
SELECT
0 s_0,
“C – Sample Costs”.”Products”.”P1 Product” s_1,
“C – Sample Costs”.”Time”.”T02 Per Name Month” s_2,
DESCRIPTOR_IDOF(“C – Sample Costs”.”Products”.”P1 Product”) s_3
FROM “C – Sample Costs”
WHERE
(“Time”.”T02 Per Name Month” = ‘2010 / 01’)
ORDER BY 3 ASC NULLS LAST, 2 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 500001 ROWS ONLY
——————– Logical Request (before navigation): [[
RqList [1,2,3]
0 as c1 GB,
D1 Products (Level Based Hier).P1 Product as c2 GB,
D0 Time.T02 Per Name Month as c3 GB,
D1 Products (Level Based Hier).P0 Product Number as c4 GB,
11- Fixed Costs:[DAggr(F0 Sales Base Measures.11- Fixed Costs by [ D1 Products (Level Based Hier).P0 Product Number, D1 Products (Level Based Hier).P1 Product, D0 Time.T02 Per Name Month] )] as c5 GB
DetailFilter: D0 Time.T02 Per Name Month = ‘2010 / 01’
OrderBy: c3 asc NULLS LAST, c2 asc NULLS LAST, c4 asc NULLS LAST
For more information, see the Oracle Analytics Server Guide to Managing Metadata Repositories: Set an Implicit Fact Column in the Subject Area. If you have questions, you can post them on the Oracle Analytics forum on Cloud Customer Connect.