DMR Union Issue

[kado]

DMR Gurus,

I opened this case (somewhat similar to another PMR I opened last month) and haven't had much feedback so I thought I would share here and see if anyone out there has any insight/feedback.

Thanks,
kado

Hello,                                                                 
I am looking for a DMR guru to help me through a couple issues         
(potentially bugs) that I am seeing. I've had PMR 34569,033,000 opened 
for a very long time and need to escalate it because it is impacting a 
production solution and must be fixed (or retired).                     
My newest issue has to do with a report I am building for one of our   
users. The Spec calls for prompts for 2 different date ranges (which I 
am presenting as Months within a multy select) and then the report     
takes the results from these 2 different date ranges, calculates some   
percents and then needs to compare these results.                       
So basically, I am using 2 different queries, 1 for each date range and
calculating the measures with 'Calculated Measure's. I am using a data 
item within each query to differentiate between 'Date Range 1' and     
'Date Range 2' and then using a UNION to join these 2 in one place in   
an attempt tp put them back together in one crosstab. By themselves     
results are accurate BUT when put together in a UNION I am seeing some 
very weird (and inaccurate) results. I've tried a number of different   
ways of calculating these measures on the fly and oddly enough they are
all different and wrong.                                               
When I pull the SQL out and run it against my Teradata source the       
results are as expected and returning exactly what I need. So it       
appears that Cognos is doing something after the fact but I don't know 
what.                                                                   
This concerns me greatly because this is the 2nd major issue I am       
having with our DMR model (which I feel is designed accurately based on
IBM documentation and many years designing FM models (but not DMR       
ones)). The other is the pre-mentioned PMR.                             
The spec is a bit complex but easily handled by a relational model and 
should be available with DMR.   

[blom0344]

I am not pretending to be a DMR guru, but whilst developing a new complex DMR model , I am constantly checking semi-finished model packages by creating test reports in Analysis Studio.
I basically start a database trace (SQL server in my case) and check for the SQL executed against the database while I create reports (piece - by - piece.
Now, running traces in SQL server is extremely easy compared to other databases. I have no idea if this is easy  with teradata.

However, it has given my quite an insight what Cognos is pushing to the database and what not.  (I hope next year to publish the entire recorded experience somewhere , perhaps on cognoise.com)

Meanwhile using unions in combination with DMR has not been - by us - tackled yet.
So i am actually interested in your model. Did you try to build the union in the model as an alternative?

[cognostechie]

Where exactly did you union? Your words 'data item' etc indicate you did it in Report Studio? You said when run seperately both the queries show correct data. If the resulting query shows wrong data, did you check the aggregate properties of the data items in the resulting query?

If you did union in the model, how did you do it? In the query subject that feeds the DMR?

[kado]

thanks for the feedback. that was actually going to be my next step. right now i am trying to test it from a 'semantic layer' of the model (that feeds the DMR), so just a relational model to isolate if it's a DMR issue or perhaps a problem with the intersections on the crosstab.