Macro for schema

[Cognos8]

Hi,

I have developed dimensional modeling. for security purpose i need to list the dimension based on user login. Each user is mapped with separate schema. So schema has to be selected by user first.

To achieve this, i am seeing an option in FM -> Datasources > PROPERTIES > Schema . Here i need to write macro. Based on user login it should fetch the schema and list the values in tree prompt. let me know the macro to be writtent to acheive this.

[Michael75]

I'm not sure that this can be done entirely within FM - others might have a solution to propose here - but I have a technique which will give you the desired result.

- In FM , leave the Schema name empty (yes, really!)

- In Cognos Admin, under your single Data Source (the one referenced in FM Data Sources), create as many Connections as you have Schemas

- Using Object Security, limit the Permissions to each Connection according to your organisation. This will avoid users being unnecessarily prompted each time they run a report.

- Within each Connection, under Connection - Commands - Open session commands, enter a script like this one:

Code Select Expand

<commandBlock>
<commands>
<sqlCommand> 
<sql>ALTER SESSION SET CURRENT_SCHEMA = DM_required_schema</sql>
</sqlCommand>
</commands>
</commandBlock>

This solution is tried and tested.

[Cognos8]

Mich,

Thanks for Smart answer. I have another stuff needs to be incorportated with this. User has connected database via this datasource connection, and validate one table for flag Y or N. Y means dataloaded then connect to one schema and if N means not loaded and connect to other schema. How can i achieve this within command blocks.

[Michael75]

I wouldn't know how to do this myself, but there are plenty of examples available in the documentation. Take a look at Using Command Blocks, under Data Source Connection Settings in the Administration & Security Guide. This might give you some ideas. But one problem I see is that you're going to need two schemas available to each user, depending on whether the data has been loaded or not, and this will cause the prompting I mentioned in my earlier reply, unless you are able to automatically enable and disable these. There was a discussion on this here:

http://www.cognoise.com/index.php/topic,27117.0.html

[Cognos8]

Hi,

Little set back from my customer after even giving detail explanation. They want to acheive this using Macro. So i have to go back to my earlier question.

"I have developed dimensional modeling. for security purpose i need to list the dimension based on user login. Each user is mapped with separate schema. So schema has to be selected by user first.

To achieve this, i am seeing an option in FM -> Datasources > PROPERTIES > Schema . Here i need to write macro. Based on user login it should fetch the schema and list the values in tree prompt. let me know the macro to be writtent to acheive this."