Cognos Analytics

Expand all | Collapse all

Data security - Data Module

  • 1.  Data security - Data Module

    Posted 15 days ago
    Hi Everyone,

    We are implementing data security in Data Module. We are doing that at Data Source level by adding users roles and respective data filter for each role. 

    But Cognos for some reason that we aren´t able to explain is duplicating the same Data Security entry several times. 

    I would like to know if someone has experienced that behavior and what could be the solution.

    Our version is 11.1.2 - zLinux (Main Frame).

    Tks in advance.

    Jorge Nelson




    ------------------------------
    Jorge Moura
    Megadata
    Brazil
    ------------------------------


  • 2.  RE: Data security - Data Module

    Posted 15 days ago

    Hi Jorge,
    I also experienced this behavior in version 11.1.2.  I did not find a solution. Now we have upgraded to 11.2.0. When I open the datamodule I still see duplicates.
    Be also aware that if multiple filters are applied, the filters used will have an AND condition.



    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 3.  RE: Data security - Data Module

    Posted 15 days ago

    I would like to confirm my understanding about what actions you performed prior to your observation of the duplicated security filters.

    There was a bug in 11.1.x, which was fixed in 11.1.4, in which the security filters would be duplicated if you reloaded the metadata of the schema.   There is a manual step of removing the duplicated security filters which needs to be performed.  Interestingly, if you cleared the schema's metadata and then reloaded the security filters would not be duplicated.  

    I have not been able to find any other defect where the behaviour you are observing happens.   

    Keep in mind that security filters are built into the metadata and reloading the metadata will cause the security filters to be removed.  There is a message to that effect when you try to do that.



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 4.  RE: Data security - Data Module

    Posted 14 days ago
    Hi Ian,

    Today we took a more detail look to this issue and we found out that security entries are duplicated every time we reload metadata and your post confirmed our observations.

    As we use a single macro based on user roles to apply security to our secured tables (around 50 are secured) it will not be a very hard work to delete duplications manually every time we need to reload.

    But tomorrow we will test the workaround you suggested: clear and reload.

    Tks a lot for your post.



    ------------------------------
    Jorge Moura
    ------------------------------



  • 5.  RE: Data security - Data Module

    Posted 14 days ago
    Hi Ian, Jorge,
    I never knew that reloading the metadata was the cause of duplication of filters. Glad I know this now and that this bug is fixed. I only have to manually delete the duplicates one more time.

    @Jorge Just curious how you use a single macro based on user roles to apply security to your secured tables.
    In Framework Manager you can use parametermaps, but in Data modules they don't exist. So you can not use the function:
    CSVIdentityName ( %parameter_map_name )

    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 6.  RE: Data security - Data Module

    Posted 14 days ago
    ​Hi Thomas, good morning.

    Let me share with you how we are using macros to apply general security in Data Module.

    1) We use to create user roles with some business key inside the role name. In one of our projects for instance we use Brazilian city code like below:
    prefeitura-3305802_teresopolis is the user role name for users from the city Teresópolis near by Rio.

    prefeitura means prefecture and identifies that this is a city role
    teresopolis is the name of the city and it is here only for visual identification purposes
    3305802 is the code itselt that we use in the where sql clause

    2) We use a general user role for all users of this application that is used by Cognos to apply the security macro for city users only.

    3) We put an expression as security filter that combines fact city key (SK_MUNICIPIO) and the user roles, extracting the city code from the user role. Like below:
    SK_MUNICIPIO in (#join(';', substitute("prefeitura-", '',substitute('''', '', substitute('''', '', array('9999999999', grep('''prefeitura-', array(split( '_' , array(split(',', CSVIdentityNameList(',') + '$'))))))))))#)

    4) And the magic is done.

    5) But Cognos was duplicating this macro several times impacting overall performance. Now we know the reason and that problem will be solved when we upgrade for new releases.

    Hope this technique help you in your future projects.







    ------------------------------
    Jorge Moura
    ------------------------------



  • 7.  RE: Data security - Data Module

    Posted 14 days ago
    Hi Jorge,
    Thanx for sharing. I find your solution very creative!
    I have also tried this kind of solution, but unfortunately this approach was not suitable in my situation.
    We have one of more codes per group/role to filter on and multiple levels. I use multiple parametermaps (1 per level) to create a link between the groupname and the code(s) to filter on. In framework manager I create 1 filter (to use an OR condition) and use per level the parametermap for that level to create the filter condition. Then I use macros to split the parametermap value from single to multiple values.
    In this way I can filter on multiple levels, use multiple codes to filter on per group an I don't have to change anything in the model when a group is changed or added. Only updating the parametermap is enough. And the source of the parameters maps are tables/views so that is very easy to maintain.

    I would like to see that IBM will add this kind of functionality to Data models very, very soon, because this is the main reason for us NOT to use Data modules for end users/developers. It is to cumbersome to implement right now.

    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 8.  RE: Data security - Data Module

    Posted 13 days ago
    You can deal with the duplicates now, by manually deleting them.

    The fix for the defect is to not create the duplicates.


    Parameter maps is one of the items identified as a gap between FM and data modules.


    https://community.ibm.com/community/user/businessanalytics/blogs/mohammed-omar-khan/2020/07/30/retail-calendar-and-fm-vs-data-module-gaps-whats-n

    https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=dmfm-framework-manager-features-not-supported-by-data-modules

    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 9.  RE: Data security - Data Module

    Posted 13 days ago
    Hi Ian,

    We are dealing with duplication now manually removing that. The main concern for us before this post was to not know what was causing the duplication and if it is fixed in later versions. Thanks a lot for the information.

    Parameter map gap is not an issue for us. We use to avoid it even in FM. We prefer to use macros that extract information from user roles structured names to implement security filters in fact and dimension tables. Then we do not need to change model if secured data changes. We are using this same approach in Data Module as well because it also supports that same macros as FM.

    But metatdata reload was duplicating the macro N times causing an overall performance impact. Problem is solved now.




    ------------------------------
    Jorge Moura
    ------------------------------



  • 10.  RE: Data security - Data Module

    Posted 13 days ago
    Hi Thomas,

    Thanks for sharing your solution as well.
    In some cases the approach without parameter map is not possible.

    We had recent a project with many security levels (Sales force dimension) and we were able to use the approach without parameter map combining user roles that identify the level (Regional manager, Team Manager, etc) with others roles that implement the filters itself.

    But I do not know if it would be suitable for your case.

    Someone from IBM told me that parameter map in Data Module is planned for next releases.

    ------------------------------
    Jorge Moura
    ------------------------------



  • 11.  RE: Data security - Data Module

    Posted 14 days ago
    Hi Thomas

    Today we found out that this problem occurs when the metadata is reloaded. We could see that filters are replicated in the SQL where clause as well.

    There is another reply in this discussion from Ian Henderson telling us that this is a bug of 11.1.2 fixed in 11.1.4.

    I think in your case if you delete duplications manually this problem will no longer occurr.

    Tks for your reply,





    ------------------------------
    Jorge Moura
    ------------------------------



  • 12.  RE: Data security - Data Module

    Posted 12 days ago

    Hi Thomas & Jorge,
    Data Modules do not allow you to define parameter maps at this time, but the need of that is quite a bit reduced with the introduction of the macro function queryValue
    Assume that you used FM and created a parameter map  myMap based on query items,  key=[ns1].[qs1].[qi_k] and value=[ns1].[qs1].[qi_val]
    The use would be something like  # sq ($myMap { 'key1' } ) #     say that would give 'val1'.
    Using queryValue you could use  #   sq ( queryValue ('[ns1].[qs1].[qi_val]',  '[ns1].[qs1].[qi_k] = ' + sq('key1')) ) #
    This works in Data Modules, here is an example with based on the gosales sample model:
    #
    sq(
    queryValue(' PRODUCT_LINE.PRODUCT_LINE_NL',
    ' PRODUCT_LINE.PRODUCT_LINE_EN = ' + sq('Camping Equipment') )
    )
    #
    That gives as output: Kampeerbenodigdheden

    // Henk​​​



    ------------------------------
    Kind regards,
    HENK CAZEMIER
    ------------------------------



  • 13.  RE: Data security - Data Module

    Posted 12 days ago
    Hi Henk,
    I also tried this function, but I need a list of all groups a users belongs to and use that as a filter for the 'myMap'. That is something the function queryValue cannot do. I really need the CSVIdentityName ( %parameter_map_name ) function.

    ------------------------------
    Thomas van der Meer
    ------------------------------