ReportStudio filtering on wrong fields

[halldn]

Hi Everyone,

I have a question about Framework Manager relationships and query efficiency.

I have a database with a fact table that is partitioned into separate tables by year and combined using a view.  This table is linked in Framework Manager to a date dimension table via a week_key field so that users may query data using fiscal week and year fields (these values are different than calendar week and year for some dates) in the date dimension.  I have the query subject dimension data set up correctly so that there is no double-dipping and report results are as expected.

The problem I'm having is that when ReportStudio users filter fact data based on the week and year fields from the date dimension, ReportStudio creates a stitched query that selects all data regardless of date and then joins the data to the filtered date dimension in the final steps.  As a result reports take three times longer to run than necessary because the database is scanning three years but only returning one.  If I bypass the separate week and year fields and filter only on the date dimension's week_key then the query correctly scans only one year.

If I could somehow get Framework Manager to understand that when a user selects a single week and a single year that it translates to a unique week_key value, then it could filter the fact data on the indexed week_key field instead and the report would process in a fraction of the time.  I'm unaware of a method for doing this, though.

Any ideas?

[cognosfreelancer]

What version of FM are you working on.

I dicsovered a bug while working on ReportNet 1.1 MR2 which seems relevant to your problem.

In my case when the report contains multiple left joins the where clause that contains the report filter gets executed locally on the server after all the data is brought in from the database instead of being placed within the multiple stitched queries.

HTH
NKT