How to efficiently reuse a query result set in a report

[seballen]

Hello, I'm not even sure how to phrase my question. Here goes:

I have written a large report for  a medical practice. It takes a long time to run and I fear that it is re-running one of the queries in it many times.

In a nutshell, there is a query that determines the number of diabetic patients in the practice.

There are 8 additional queries (or multiple queries with joins) that calculate the percentage of patients who meet certain criteria.

Finally, each of the 8 queries is individually outer-joined to the original diabetes query to calculate the percentages.

All of the data is displayed on a single summary page using singleton fields.

It looks great, and works in that after about 10 minutes, the data is displayed and is correct. However, I worry that I am just hammering the database.

The rough outline of the report:
1.0 Diabetic patients
1.1 Percent of diabetic patients with an uncontrolled hgb a1c test.
1.2 Percent of diabetic patients with good blood pressure control.
1.3 Percent of diabetic patients with a BMI under x.
1.4 etc,
etc, etc

Is report studio re-running the initial 1.0 Diabetics query for every one of the additional queries? If so, is there a better way to accomplish this in one report?

Many thanks for any insight.
Sasha

[CognosPaul]

Cognos will only run queries that are attached to data containers. So, even though each query is joined locally to the same query, the engine sends the same number of queries as you have singletons.

The design sounds a bit odd to me though. Why outer joins?

How do you determine number of patients for each metric? I assume that it's not a simple matter of filtering a single attribute. If it was you could use a single list with the percentage function. Instead I suspect different filters are used, as one patient might be listed under multiple metrics. Why not try to create a single query with different data items for each metric? Something like:
data item 1 (All patients): [Measure]
data item 2 (hgba1c): case when [HGB A1C Level] >= 3 then [Measure] else 0 end
data item 3 (hgba1c%): [hgba1c] / [Measure]

Data item 3 may need to have solve order set to 2. If you do that for all of the metrics then the resulting query should have 1 row with the correct data. You could then use 1 singleton instead of 9. Then you could drag the table into the singleton, instead of the singletons into the table.

[seballen]

Thanks very much for the reply.

There are often multiple filters for each metric, and all or nearly all of the metrics are calculated from different tables.

Here is a specific example of how I handle a query for Diabetic patients whose last Blood Pressure is under 140/90

1. Create a query "Alldm" for All active DM patients that meet the criteria. This requires 4 tables based on database structure.

2. Create another query "LastBPValue" for patients with one or more BP readings in the time frame. Create a calculated field [lastBP] = maximum ([date] for [patientID] and set a filter [date] = [lastBP]

3. Create a final query "DMandBP" with outer join Alldm to LastBPValue to show all DM patients and their last BP value.

4. Create a calculated field in DMandBP that looks for a value in the target range --- basically (if (bp not null and bp< 140/90) then 1 else 0)

5. Create fields to count Total DM, Total "goodBP" and calculate % goodBP

------

I repeat this basic structure 9 times and it seems horribly inefficient.

I'm not sure if it's even possible to add criteria for the multiple measures into one query - It seem like it would be very complex.

Am I going about this all wrong?

Thanks,
Sasha

[Greg]

It sounds like the "Alldm" query is common to all 9 of your cases.  Is this true?  In your solution the query engine will send this query to the database 9 times, once with each of its outer-joined queries.  It is possible to combine all 9 cases into one final query with "Alldm" as the commonality among all of the cases.

E.g.  (with 3 cases)

Final Query
--- (Left Join)
   --- (Left Join)
   |   --- "Alldm" query
   |   --- "LastBPValue" query
   --- BMI Query

This pushes a single "Alldm" query along with a bunch of outer joins to the database and lets it optimize more effectively.

[CognosPaul]

Does the data need to be "live" data? If not, I recommend dumping everything into a separate datamart (cube, materialized views, etc.) and referencing that.

[navissar]

Hi,
In the event you can't do what was suggested by my living mentor Mr. PaulM, i.e. dumping the data elsewhere and referencing that (I would imagine that critical patient data might have to be as live as possible), there's a way to "Force" Cognos to only fire the query once, but it is cumbersome.
You need to create the query that returns all diabetic patients once (From now on, ALL_DIABETIC).
Then you create another query (ALL_DIABETIC_COPY) and use ALL_DIABETIC as reference query. You do that by dragging ALL_DIABETIC to the right of ALL_DIABETIC_COPY. It will display ALL_DIABETIC with an arrow pointing at ALL_DIABETIC_COPY.
Double click ALL_DIABETIC_COPY and drag all the fields in.
Then use ALL_DIABETIC_COPY as you would have used ALL_DIABETIC, with whatever joins you need.

The way Cognos executes reference queries is, it first of all executes the original query (ALL_DIABETIC), caches it, then every time you use ALL_DIABETIC_COPY it will bring the cached data from the one time ALL_DIABETIC ran.

I hope that was clear enough, if you choose this way (PaulM's is better, if possible) and need further explanation, I'm here! :)

[seballen]

Thanks very much for the replies -

Sadly, I don't have the option to dump the data elsewhere for reporting, so I will try the reference query idea. Aside from being cumbersome, are there any downsides? It sounds like a perfect solution to me...

Thanks,
Sasha

[navissar]

Not a lot of downsides. Makes adding a new field a bit of a bottom pain, but that's basically it. Give it a go and tell us how it went.