How to prevent direct RDBMS querying at powerplay report level.

[cogprog]

Hi friends,
Can anybody let me know the scenarios when powerplay report bypasses the powerplay cube to fetch data from underlying database (RDBMS)?
If exists... How can one prevent such situations? I am looking for a reporting solution where report never queries RDBMS at runtime and all QUERYING and reporting needs are fulfilled by cube even if person drills to the smallest grain of data.

Thanx
cogprog

 

[wolfe47374]

Powerplay itself will never hit the database. All powerplay reports will only use the cubes. The only instance of hitting the db would be drilling through (not down) to a impromptu report or cognos query.

 

[cogprog]

What happens when we drill through to the Impromptu report for the new dimension (The dimension which is missing in the cube but is there in the underlying impromptu report).
1.Does report (I presume impromptu report is a saved report)itself acts as a data source or it re-runs the .iqd file related to it to fetch the relevent data from the database.
Does an impromptu report act as a final datasource in this scenario.
If yes, then we are not hitting DB. If no, why. Please help me in understanding this.

cogprog

 

[griffindm]

Generally speaking, you limit the dimensions of the cube to consistently occuring aspects of activity that add value to the analysis. Other granular details of the transactions (such as transaction description, voucher number, etc) do not lend themselves to adding value (or to meaningful rollup) to OLAP analysis and are omitted from the cube.

This means that any drill-thru from the cube into Impromptu or Cognos Query will hit the database. With proper filtering from the cube this should not a an issue with either performace in the drill-thru or impact on other users of the database.

It's not practical or meaningful to include every attribute of a transaction into the cube. If the end users want the benefits of drill-thru to detail, then querying the database becomes the price.

HTH,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[cogprog]

Dave,
I have reporting requirements which are something like this.

RepairShopName Estimate Count
Jan Feb
AAA 3 4
ABB 12 11
ABC 27 77
ADX 8 11

Now Clicking on any number(under Jan for AAA repair shop I.E. 3) should give me details like following.

Claim Number Estimate Date AppraiserName Estimate Count
AP10045VB 12/01/2000 Roger Moore 1
AB10345SI 11/02/2001 Brian Driscoll 1
BS10119ST 11/02/2001 Peter Swayze 1

Now my first matrix may be my powerpoint report and second details report can be an impromptu report. So when I hit 3 my query which now will be directed to impromptu report will be having a filter of Month=Jan and RepairShopName=AAA.
I think that is the only reason why you said that their should not be a performance issue. Does this query by any chance effects the powerpoint cube?
The reason behind this is that I am currently working with BO and the microcube BO generates while drilling brings all the information regarding the dimension which is being drilled across on the desktop which definitely is undesirable.

cogprog

 

[griffindm]

I'm not sure exactly how BO handles their OLAP and drill-thru analysis. I've seen demos, but haven't got my hands on the product yet (perhaps soon though <g>).

Yes, this is what I meant about minimal impact on the database, assuming that the drill-thru filters operate on indexed columns.

If this is for a small operation with disconnected users, you could consider using a hotfile (refreshed regularly) as the source for the data for the drill thru assuming that the dataset size is small (say under 50k rows). This would work for users that want to use the cube and drill into detail, but cannot for other reasons connect to the database.

HTH,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com