issues using prompt macro in framework manager using database processing

[damzI]

Hello All,

I am using Hadoop database with a HIVE connector; and have had to push the processing to database only due to the volume of data being processed. I now need to use a prompt macro in my queries so that I don't have to change the hard coded values every time. However, when I use a prompt macro it throws up an error - and I  have to change to local processing for it to work.
So the question is - is there any other sql function that I can use instead of a prompt macro to force the database to be prompted at the database level in Hadoop.

Thanks in advance, D

[tjohnson3050]

The values in prompt macros can be pushed to the database as long as you aren't using functions that require local processing.  Can you post your prompt macro for us to see?  If you are using any Cognos specific functions within the macro, try converting it to a syntax your Hive connector understands natively.  Most likely, your hive connector will recognize Postgres specific language...

[damzI]

Thank you for your quick response..

So what I notice is that if I use the following macro - it works with the database processing
EM1key = #prompt ('eom', 'int')#

However, I need to use the same macro in the another column to filter on dates in my fact table

AND FACT1.EM2key  between
(CASE WHEN (mod (201512, 100) = 12)
THEN (201512 - 111)
ELSE (201512 - 199) END)
AND
(CASE WHEN (mod (201512, 100) = 1 )
THEN (201512 + 112 )
ELSE (201512 + 200 ) END)

I am using the above case statement - which works against the database - BUT if I combine it with the prompt macro (shown above) in Framework Manager it throws up an error...

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'.
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0481 An expression or operator (case) is not supported by the database. This operation requires local processing of the data.

That is the first issue - the second being that I really need to dynamically change the values (201512) in red to the same macro.

Every thing works if the processing is limited local.

- D

[CognosPaul]

It sounds like the database doesn't like the case statement there. It looks like there should be a few ways of simplifying that. Where is that value coming from? Is it a prompt? a casted current_timestmap? Can you post the full expression?

[damzI]

The 2 columns from transaction that I am filtering on are EM2key (a date key as int in YYYYMM format) to get the most recent month (this is not necessarily the current system month) - and using that I am trying to get a date range 2 years forward and backward from the EM2key  column with the exact same data type and format.


So here is the expression that I want to use...

Select
   FACT1.EM2key,
   FACT1.EM1key,
   FACT1.OFFCCDE
   FACT1.DSRCT
   FACT1.SLRY
   FACT1.EMPCNT
from
   FACT1
WHERE    FACT1.EM1key = #prompt ('eom', 'int')#
AND FACT1.EM2key  between
(CASE WHEN (mod (#prompt ('eom', 'int')#, 100) = 12)
THEN (#prompt ('eom', 'int')# - 111)
ELSE (#prompt ('eom', 'int')# - 199) END)
AND
(CASE WHEN (mod (#prompt ('eom', 'int')#, 100) = 1 )
THEN (#prompt ('eom', 'int')# + 112 )
ELSE (#prompt ('eom', 'int')# + 200 ) END)

I have isolated the filters to find where the issue lies. If I use only the first prompt FACT1.EM1key = #prompt ('eom', 'int')# it works like a charm.
But when I add the second one it returns that error.
I have also tried to use the 2 together with the prompt in the first and hard coded in the second (as below) - but it still errors out...its kind of getting tough to isolate the issue...

FACT1.EM2key  between
(CASE WHEN (mod (201512, 100) = 12)
THEN (201512 - 111)
ELSE (201512 - 199) END)
AND
(CASE WHEN (mod (201512, 100) = 1 )
THEN (201512 + 112 )
ELSE (201512 + 200 ) END)

[CognosPaul]

Is this a DQM model?

[damzI]

its a relational model - not dqm

[CognosPaul]

Relational can absolutely be DQM - DMR and OLAP is no longer a requirement. The reason I ask is DQM has a few macro functions that are not otherwise available in CQM.

Last question, can you modify the parameter being passed to EM2key to pass yyyy-mm instead of yyyymm?

Either way, I think I have a solution for you.

First, change the database query so it's only: "select * from FACT1". By modifying the query in any way, including inline filters or macros, you're implicitly disabling model portability and SQL minimization. Cognos will run a metadata request to the database for every request. Filters like this should be added to the logical or business layer. This ensures SQL will be minimized, while still passing the filter.


Create a new query subject that has three columns.
Key, Start, End

Key should be: 1
Start should be: CASE WHEN (mod (#prompt ('eom', 'int')#, 100) = 12)
THEN (#prompt ('eom', 'int')# - 111)
ELSE (#prompt ('eom', 'int')# - 199) END)
End: CASE WHEN (mod (#prompt ('eom', 'int')#, 100) = 1 )
THEN (#prompt ('eom', 'int')# + 112 )
ELSE (#prompt ('eom', 'int')# + 200 ) END


Next, create two parameter maps. Both based on that new query subject.

Then in the filter, call that parameter with

FACT1.EM2key between (#$startParameterMap{1}# and #$endParameterMap{1}#)

This way the start and end values are being calculated locally, but the query being sent is able to be processed by the database.

If DQM is enabled, and you can modify the eom to pass as yyyy-mm then we can skip the parameter map step.

EDIT: fixed a couple of stupid typos

[damzI]

Hi CognosPaul,

Thank you for the detailed solution response and effort. I tried the ParameterMap solution but am still getting that local processing error...

BMT-MD-0003 UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0481 An expression or operator (case) is not supported by the database. This operation requires local processing of the data.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'.
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0481 An expression or operator (case) is not supported by the database. This operation requires local processing of the data.

Also thanks for reminding that I can use the DQM mode in relational.
However it looks that the DQM mode is not compatible with Transformer. And so even when I am able to import my fully optimized package in Transformer - I am unable to fetch/read the data in the data sources !
- D

[CognosPaul]

Doh. I haven't touched transformer in years.

Did you create a new query for the start/end values? It should be a simple query subject that doesn't reference any database tables at all. If you try to run that, do you still get the local processing error? I'll try to recreate this once I get access to my personal laptop tonight.

[damzI]

Yes I did create the query with start/end values - that was simple easy and clever ! AND used it in the parameter maps and used it in the sql as you had suggested...but after calling the parameter for the date range in the filter it slipped back to the same error - about local processing...

[damzI]

It WORKED !

CognosPaul - I wanted to thank you for the parameter map solution - it worked.

What I had to modify for it to work was to use only the column name in the where clause - I was using the fully qualified with the table name the last time - Thanks again for all who helped resolve this.

- D

[the6campbells]

Some feedback on the thread.

(1) When you free hand type SQL, by default it will be parsed as the Cognos canonical SQL syntax unless it is explicitly denoted as native/passthru (see manuals)
(2) Using Generic ODBC will function but with a high number of cases where the engine has to perform query decomposition and local processing with much simpler SQL being sent to the source
(3) The data source objects in your FM model must have the processing option set to local (not database only)

#macro# functions are evaluated early in query planning, and effectively become literals in the SQL statement. suspect your initial problem is a combination of (1) and (2).

dynamic query (DQM) is fully integrated to the types/SQL that Hive 1.x, 0.14 and 0.13 can process that follows SQL standard semantics. hence, you will see a higher rate of SQL being send down by default than with DQM.

the #macro# facility while using DQM has a simple-case macro expression which is not available with CQM. You can simulate a conditional macro using parameter maps. parameter maps can be query backed and thus the name-value pairs can even by dynamic calculated using queries.