[Closed] Problem using a star schema in the Framework Manager with Query Studio

[Yves]

Hi everybody,

The problem encountered is the behaviour of Query Studio when preparing a list of prompts (reference values) to create a filter and select records from a detail table (facts) using then one ore more of these values to set up the inner join.

Expected behaviour is: I fill in the list of prompts from a reference table (containing all distinct values of the field considered as criteria in the facts table) and then I run the select on the big table. Doing so, the performance is excellent as I avoid running a "select distinct" on this field in the facts table (the big one). I mean, references tables are pre-built to avoid decreasing performance when filling in the list of values (prompts).

But... although I force the underlying meta-data to use the reference table when building the query in Framework Manager, this results anyway in a query like "select distinct" on the facts table with a join on the references table (visible with a SQL trace), therefore still reading the entire facts table and resulting in a very bad response time...

And I can't use option "limited data" to restrict (and speed up) selection from the facts table as it would indeed result in omissions in the "select distinct" for the prompts list!

I know this problem doesn't exist using Report Studio, but this is really a critical issue for us as 80% of our users will be using the Query Studio as a maximum, with regard to their knowledge and technical level (they are definitely not advanced users...)

Any idea on a solution?

Any help or advice will be much appreciated, thanks in advance.

Kindest regards,

Yves

[BIsrik]

I feel this is how RN will create the sql. when contacted one of the DBA person he said select distinct is used for performance improvement. I think select distinct in fact table is used on the values retrieve by the table. As u also said there is a relation b/w ur fact and reference table in sql.

Well anyhow let me find how to tell QS not to use select distinct as we do in RS.

Srik

[sir_jeroen]

What's the status of this topic?