Thursday, October 14, 2010

Macro Prompts in FM Model for Adhoc Packages

Working on creating an Adhoc Package for use in Query Studio. The package needs to include base metrics and compound metrics and users need to be prompted for Date range and other Dimensions on pulling any of the metrics.

For the prompts, I included filters in the model query subject and built of the metrics. But what I noticed was that, only the base metrics prompt the users and if you drag a compound metric you are not prompted the first time in Query Studio until you hit the re-run button.

Work-around is to use macro filters rather than ?parameter? filters.
.

Tuesday, October 5, 2010

FM and Teradata Stored Procedures

I have been working on using Stored Procedures to update tables through Event Studio based on a condition. The back end DB is Teradata.

With Teradata if you have a separate schema to create Stored Procedures, the schema will not show up in FM when you use the run meta data wizard unless it has at least 1 table or view. To overcome this issue, a dummy table was created in the schema after which the schema showed up in FM. I then imported the SP. The SP is a data modification SP. Teradata SPs cannot be run through FM or through Event Studio because Cognos fires a "CALL SPName" statement rather than an "EXEC SPName" statement.

To work around the above issue create macros and use them through FM or Event Studio. This is only for Teradata. Oracle Stored Procedures do not have any issues.