Track Portal Page where Discoverer Portlet are stored

For customers having discoverer portlets it's an easy task to confirm whether they are executing correctly or not discoverer portlets by querying the PTM5_CACHE table on the Infrastructure. But it could be a nightmare if they have hundreads of these portlets and they want to open it through the Portal site. Indeed, how to find which is the Page where they are stored ? Following is a query that query Portal Page containing Discoverer Portlets :

Select Portal.WWPOB_Page$.Name
from Portal.WWPOB_Page$
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$
where Portal.WWPOB_Portlet_Inst$.Provider_ID =
and Portal.WWPOB_Portlet_Inst$.Portlet_ID = ));

You need to substitute the PORTLET_ID and PROVIDER_ID value with your own value. Those can be obtained with procedure explained above:

1. Login to Portal as the portal or orcladmin user;
2. Navigate to Administer -> Portlets;
3. Select the 'Display Portlet Repository' link;
4. Locate the provider containing your discoverer portlet (this may be under the Portlet Staging Area)
5. When the page with list of portlets (Discoverer Gauges, Discoverer Worksheet, Discoverer List of Worksheets) is displayed, click on the "Edit" link you see on the top right and you will get properties for this portlet (Portlet_id included) ;

To get filter data, pointing to specific workbook, you can query the Infrastructure DB by the following :

Select Distinct CacheTable.Cch_Url, Portal.WWPOB_Page$.Real_Name
from Portal.WWPOB_Page$, Discoverer5.Ptm5_Cache CacheTable
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$ Where Portal.WWPOB_Portlet_Inst$.Ref_Path in
(select Discoverer5.Ptm5_Instance.Ins_Id
from Discoverer5.Ptm5_Instance
where Discoverer5.Ptm5_Instance.Ins_Surrgt_Pk in
(select Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk
from Discoverer5.Ptm5_Cache
where Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk = CacheTable.Cch_Ins_Surrgt_Fk
and Discoverer5.Ptm5_Cache.Cch_Surrgt_Pk = CacheTable.Cch_Surrgt_Pk
and Discoverer5.Ptm5_Cache.Cch_Id = CacheTable.Cch_Id
and Discoverer5.Ptm5_Cache.Cch_Url = CacheTable.Cch_Url
and TO_CHAR(CCH_NEXT_UPDATE,'DD-MON-YY') != '02-JUN-01'))))
order by Portal.WWPOB_Page$.Real_Name;

In this case I took out the failing portlets with Update Date = '02-JUN-01'.

Aggregation : What Happens with Fan-Trap ?

I have received recently a claim from a customer regarding the aggregation behaviour of his workbook. In particular he was afraid to see an item on the Group By section clause of the Discoverer Query. At first look I immediately recognized that the architecture selected for his workbook was resulting in a fan-trap schema and so could generate ambiguous data, but due to his insistence I reviewed inside his workbook the reason of this. So, let's first define what is a fan-trap and its rules.

What are fan traps, and how does Discoverer handle them?

A fan trap is a group of joined database tables that might return unexpected results. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently. Figure shows a simple fan trap schema





Although this construction is relationally correct, you are likely to return incorrect results if you use a straightforward SQL statement to aggregate data points. However, if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results, provided fan trap detection is enabled. Every query that Discoverer generates is interrogated for potential fan traps. If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.

In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:

• if the detail folders use different keys from the master for the join
• if there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
• if non-aggregated values are chosen from more than one of the detail folders if more than one detail folder has a separate join relationship to a different master folder
• if more than one detail folder has a separate join relationship to a different master folder

How the aggregation works in fan-traps ?

Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.

Now, back to the customer issue, I verified the following schema in the Workbook (in green the folders which items are used in the Workbook) :


Each one of this folder, joins the other folders (or Master Folders) on a different keys, generating a fan-trap schema. So, if he creates a crosstab workbook with the following layout :







The aggregation will be made on

• Department.Item (Because it is Page Item and rollup at page item level)


• Customers.Item (Because it is an axis item and rollup at row level)


• Product Details.Item (Because it is an axis item and rollup at row level)

but will add to the Group-By expression also the Sales SUM.Item, because by looking at customer schema, this item is from Sales SUM folder, which is detail of Stores folder which is the master of Products folder, which is iself master of Product Details, for which items are in the workbook. So, basically Sales SUM Item is interpreted in this case as Master of involved items, and so added to the Group By clause, as the aggregation is made for each master-detail relationship.

The only solution here is to create a custom folder just for this workbook, otherwise one should re-architecture the joins so that workbook will not be built on a fan-trap schema.

Welcome

Hello Everyone!

And thanks for looking inside this blog. Hope to adds something to the Big Discoverer community, sharing information that may be useful for you in order to use Discoverer and in order to better interact with Support Discoverer Team (in EMEA) if you need it.

Grazie e Ciao,
Gianluca