0

A cognos query turns out be a heavy CPU consumer which I tuned using Volatile tables. I'd like to know from someone who is familiar with implementing Volatile tables in Teradata via Cognos whether this is easily do-able . I did something like that in the past using JI's or materialized table but in the given situation VT's are the only option Original Q is something like this : 

sel 
Case statements,
Sum ( Fct.C1)
Sum ( FCt.C2)
Case when ( Dim.C3  in < value list of  3000+) then ______

each of these column is part o the cubes and these are written by "framework manager" component as ICD files that contain the SQL

What I want to do is manually modify these ICD's to stick in the VT creation code and but still keep the Column list SAME 

Create VT1 as ( sel ...) 
Create Vt2 as ( sel ...) 

Select  <same column list > 
from VT1 Join Conditions VT2 Join others

How can this be implemented and what challenges need to be addressed.

user1874594
  • 2,277
  • 1
  • 25
  • 49
  • I don't think you can really use volatile tables in Cognos, unless you use hand-written SQL, which I wouldn't advise. You can more or less accomplish the same thing in Cognos though. Instead of using IQDs, use a report as the source for your cube. In the report, just use two separate queries, one for each select, and then join those two queries together – Andrew Oct 21 '15 at 14:52
  • It has to be in the same session for VT to be used. Can I have a report that feeds a cube and inside that single report are 3 queries . 2 CT for VT 's and 1 Q that uses the 2 VT's – user1874594 Oct 21 '15 at 15:47
  • I'm saying use two queries in Cognos to replicate the volatile tables (get the exact same result set), and then join those two queries together. – Andrew Oct 21 '15 at 16:56
  • No I need the VT to choose my PI and the redistribution is the benefit from the VT.So it has to be VT's – user1874594 Oct 21 '15 at 17:02
  • Oh, got it. You may be able to do it using native sql in your FM query subjects, I've never tried that though. You could also look into using a stored procedure. – Andrew Oct 21 '15 at 18:28
  • SP wont work because that's prolly a new session. `native sql in your FM query subjects` Can you pl shine some light there . – user1874594 Oct 21 '15 at 18:34

0 Answers0