Adding views and creating relationships in FWM

[MPritchard55]

hello! 

My colleague and I are struggling to make a custom view work efficiently in Cognos.  It returns data super fast when run in SQL Developer, so I believe it has to do with the relationship we defined in FWM.  Here's some info:

Business: Higher Education
Cognos 10.2.1
Datasource: ODS 8.3 
ERP: Banner
Framework Manager packages: Vendor (Ellucian) delivered

1.  We created a custom view (PL/SQL) directly in the ODS (Operational Data Store) which performs custom calculations (related to donations to the university) per PIDM (Banner's version of a SSN). 
2.  We brought that view into Constituent Package/Constituent Namespace in FWM and related it to the Constituent view by PIDM (PIDM is the common key in Banner tables). 
3.  Published and tested the view in QS.  VERY SLOW.  Even when filtering by a single PIDM.
4.  Ran same query in SQL Developer and it returns in seconds.  VERY fast. 
5.  Went back to FWM and noticed that the PIDM was automatically set to a 'Fact'.  Changed that to 'identifier.'
6.  Published and tested.  MUCH FASTER when filtering by a single PIDM.  However, when I run a query (no filter) using the Constituent view data, it returns data in about 1 second.  When I add a column from my custom view, it takes 40 seconds to return.

I believe the relationship we established is accurate (linked the PIDM in the Constituent view with the PIDM in the custom view and cardinality that identifies that there is 0 or 1 constituent record for each custom view record and 1 and only 1 custom view record per constituent record). 

Wondering if I'm overlooking something simple in the relationship or adding the view? 

Any assistance is appreciated.  Thanks. 

[blom0344]

You should check for full tablescans introduced by using an outer join. You can test this from the Cognos framework side by publishing a package with a straight join between the query subjects. If this amounts to response within a mere second, than you may have found the cause.


Another cause may be with storing data within the db cache. Using data from memory may be quite a bit faster (as often observed when re-running a query)

[MPritchard55]

I forgot to note in last post, but I tried changing the cardinality to 1:1 for both arguments and that didn't help. 

However, since my last post, I found that the custom view itself (even when not joining to another view) takes 40 seconds to return (unfiltered) data, so we will try materializing the view.   

Thanks for your help!

[blom0344]

For some reason the filter from the Cognos framework definition is not passed down to the select statement on the view. The reason could be that your filter does not reference a key stored in 1 of the underlying tables. Materializing the table and still having bad selectivity may not improve matters as the whole table could still be scanned. With proper selectivity selecting from a DB view should be able to work. I've build models against 100% view definitions..

[MPritchard55]

We materialized the view and it now returns in 4 seconds versus 40.  That seems acceptable.   :D

Thank you!!

[MMcBride]

Materializing Complex views is always a good idea.

It lets the Database do the heavy lifting
At the end of the day COgnos is just an SQL Generation Engine, you provide the rules and Cognos builds what it thinks is best based on the query from the report - using the limits set within the FM Model.

So you though you may bring in the View Logic into Cognos it may try to over write it, or as we saw in one of our projects it was replicating the logic.

We ran the RAW SQL for the view and got 3 seconds
We put the SQL into FM and ran it from a RS Report and it was taking 30 seconds for the exact same records...

But when we looked at the SQL Generated in the RS Report it was replicating our view logic 10 times for some ungodly reason... Materializing the View and then treating it like a table - wham back down to 3 second queries.