Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only
Back to discussions
Expand all | Collapse all

Data Modules and Data Server Connections

  • 1.  Data Modules and Data Server Connections

    Posted 01/08/20 03:10 PM
    Currently, our PROD connection has 2 sign-on's (cog & secure_cog).
    The sign-on "cog" has deny authority for a group of users and the sign-on "secure_cog" has grant permissions for that same group of users.
    When a report is run using a Package, the user is directed by permissions as to which sign-on the connection uses.
    For users in the "cog" group, all rows are returned but, certain data items are masked with the value 'SECURE' (we want the rows returned, just not the ability to see certain data elements).  However, when the same report is run by a user in the "secure_cog" group, all data items are returned and visible.

    So, a report over a Package using a Cognos connection data source, run by a user in the "cog" (masked data group) has their query on the database "redirected" to views (for certain tables) using Oracle synonyms - These views mask the returned data.  The same report run by a user in the "cog_secure" (all data group) has their query go directly over the tables in the Oracle database.

    However, when I use a Data Module with a Data Server connection (using the same defined PROD connections) for a report it defines the schema in the generated SQL - I presume that this is because I need to define schema(s) in the data server connection.

    Simply, this is the difference in generated SQL's (as seen in Toad):

       With Package and Cognos Connection/Sign-On's:  select * from table
       With Data Module & Data Server:                            select * from schema.table

    This "hardcoding" of the schema name, even though the report runs with the appropriate user group sign-on, overrides the Oracle database synonym functionality as the schema is explicitly defined.  Thus undermining our security.

    Note:
    I can use a Data Module containing a Package and get the expected results, it is only the inclusion of the Data Server as the source that seems to invalidate our security. 

    At the Data and AI Forum it was expressed that I should use Data Modules going forward instead of Framework Manager, but this issue will severely limit that transition.   

    Is there a way to take advantage of Data Server Connections in Data Modules without having to completely redesign our security?
    Any thoughts will be greatly appreciated.

    Thanks in advance,
    Adam.   


    ------------------------------
    Adam McIlravey
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Data Modules and Data Server Connections

    Posted 01/09/20 08:24 AM
    Edited by System Admin 01/20/23 04:48 PM
    I've run into this issue too. It's a very important feature missing from Data Modules that forces people to still use Framework Manager. There needs to be some way to dynamically switch between connections based on user group, but as far as I know, there is still no way to do that with Data Server connections.

    I opened an RFE about this some time ago, but it hasn't gotten much traction. 
    https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-164

    ------------------------------
    Rory Cornelius
    ------------------------------



  • 3.  RE: Data Modules and Data Server Connections

    Posted 01/09/20 08:39 AM
    Thank you for the response.

    I have used your link to go out and vote for your RFE - Let's hope that this gets into a release soon.
    If it is IBM's general direction to move to Data Modules instead of Framework Manager (or potentially replace FM with Data Modules) this functionality is a requirement.

    Thanks again,
    Adam.

    ------------------------------
    Adam McIlravey
    ------------------------------

    Original Message


  • 4.  RE: Data Modules and Data Server Connections

    Posted 01/10/20 09:40 AM
    A bit off topic, but how do you redirect to a different view based on the Cognos Group/Role?

    In Framework Manage, we use a Cognos Macro that switches the data source based on the users Groups/Roles. For users in the SecureGroup, the macro changes the data source to SecureDS which uses the signin SecureUser. The SecureUser signin logs into SQL with specific credentials that have special permissions - mostly for decrypting sensitive information. If the user is not in the SecureGroup then the default data source and SQL credentials are used. 

    t sounds like you do something very different. Can you explain a bit more about your setup in Framework Manager.

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------



  • 5.  RE: Data Modules and Data Server Connections

    Posted 01/10/20 01:37 PM
    We do not use Framework Manager to control any security - We control everything with multiple sign-on's on each data connection in Cognos Administration > Data Source Connections > Configurations.

    Typically, we have 2 sign-on's for each connection, each with specific grant/deny permissions.
    Therefore, a user running a report will be automatically directed to a specific sign-on and then there is security on our Oracle database which handles redirection using synonyms to a view (with masked columns) or the table (with all columns available) based on the sign-on selected.

    So, all our necessary security (in this particular scenario) is handled at the database level rather than in Cognos.
    That's why, as we have fully functional database security in place, I don't want to have to recreate it either in Framework Manager or in Data Modules.

    Hope this helps.
    Thanks again,
    Adam.

    ------------------------------
    Adam McIlravey
    ------------------------------

    Original Message


  • 6.  RE: Data Modules and Data Server Connections

    Posted 01/10/20 12:54 PM
    Take a look into this article and comments.

    https://community.ibm.com/community/user/businessanalytics/blogs/michael-mcgeein/2019/05/15/starting-a-new-modelling-project-fm-or-data-module?CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=recentcommunityblogsdashboard

    I have working with Congnos for 15 years. FM is a consolidated tool, certainly have some bugs. But is much more stable than data server and data modules.

    We have 7k consumers and 1k of report authors.  We have opened several PMRs about issues using data modules, data sets and data servers. Problems with union querys, conversions, performance, etc.

    If you take a look into this list to see how much PMR are opened about data sets

    https://www.ibm.com/support/home/search-results/N180263X74618Y31/Cognos_Analytics?sortby=-dcdate&filter=DC.Type_avl:CT748&prod=U025040U04546H36&ct=apr


    Regards

    ------------------------------
    JEAM COELHO
    ------------------------------



  • 7.  RE: Data Modules and Data Server Connections

    Posted 01/10/20 01:27 PM

    Thanks for the link - At least I have a better understanding of the necessary choices that need to be made between Framework Manager or Data Modules.

    It's a pity, I was hoping to begin the clean-up of some of our gargantuan FM models using Data Modules instead by building smaller and better "data marts" which we can then be re-utilized and compounded as needed.

    My issue is more of the functionality of data servers corrupting our existing security setup.
    I want a data server to connect to the appropriate data source/sign-on in the same way as Cognos Configurations Connections do using permissions.
    In my opinion this is fundamental and should be the default way of working .  If I can make these configuration connections available as Data Servers by simply selecting "Allow Web-based Modelling" - It should work in the same manner.

    If the goal of IBM is to move towards a truly self-service analytics environment, there shouldn't be roadblocks that will require the business community to understand database security and have to rebuild that into data modules (which is what I think I am going to have to look at).

    Thanks again,
    Adam. 



    ------------------------------
    Adam McIlravey
    ------------------------------

    Original Message


  • 8.  RE: Data Modules and Data Server Connections

    Posted 01/13/20 04:55 PM
    Support for multiple connections and dynamic schema references in data modules is currently on track for release in 11.1.7.  As with all features in development, this is subject to change.

    ------------------------------
    JASON TAVOULARIS
    ------------------------------



  • 9.  RE: Data Modules and Data Server Connections

    Posted 01/14/20 08:44 AM
    Jason...

    Thanks for the update - I'm excited to hear that this feature is on the roadmap!

    ------------------------------
    Adam McIlravey
    ------------------------------

    Original Message


Related Content