Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

Problem with user in the security filter on Intelligence Cube

  • 1.  Problem with user in the security filter on Intelligence Cube

    Posted Wed November 04, 2020 07:50 AM

    Hi Everyone,

     

    I have a problem with the security filter on my Intellegence Cubbe, in MSTR Developer 10.4.

    We have a security filter on every project, the one I used is based on several parameters. For exemple, when I create a report and I look at the SQL code, I have :

    and a11.CD_SOURCE IN ( SELECT DISTINCT CD_SOURCE FROM V_MSTR_USER_ACCESS WHERE LOGIN = 'b_dal' AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )

     

    \t and a13.CD_STORE_BRAND IN ( SELECT DISTINCT CD_STORE_BRAND FROM V_MSTR_USER_ACCESS WHERE LOGIN = 'b_dal' AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )

     

    \t and ( a12.CD_REGION IN ( SELECT DISTINCT CD_REGION FROM V_MSTR_USER_ACCESS WHERE LOGIN = 'b_dal' AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE ) OR '*' IN ( SELECT DISTINCT CD_REGION FROM V_MSTR_USER_ACCESS WHERE LOGIN = 'b_dal' AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )))

    Where you can notice that the user appears between quotes.

     

    I created an Intelligence Cube. In this cube, I add the following attributes in the grid :

    [Code de la source]@[CD_SOURCE]

    [Marque]@[CD_STORE_BRAND]

    [Zone commerciale]@[CD_REGION]

    This way, I suppose the cube can be properely linked to the Security Filter.

    But when I create on Report based on the cube, I have no result with my user. And when I look at the SQL code generated by the cube's report, I have :

    from\tCube de IndicateursMagasinData

     

    with Table Join Tree: \t[F_MAIN_INDEX]<([Code de la source]@[CD_SOURCE] IN ( SELECT DISTINCT CD_SOURCE FROM V_MSTR_USER_ACCESS WHERE LOGIN = b_dal AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )

     

    and [Marque]@[CD_STORE_BRAND] IN ( SELECT DISTINCT CD_STORE_BRAND FROM V_MSTR_USER_ACCESS WHERE LOGIN = b_dal AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )

     

    and ( [Zone commerciale]@[CD_REGION] IN ( SELECT DISTINCT CD_REGION FROM V_MSTR_USER_ACCESS WHERE LOGIN = b_dal AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE ) OR '*' IN ( SELECT DISTINCT CD_REGION FROM V_MSTR_USER_ACCESS WHERE LOGIN = b_dal AND CD_PROJET = 'LH' AND DT_DEB_ACCES <= CURRENT_DATE AND DT_FIN_ACCES > CURRENT_DATE )))>

     

    I don't understand why I have no result, and I wonder if the reason could be that my user "b_dal" appear with no quotes in the generated SQL code.

    Can you help me ?

     

    Thank you very much !



    ------------------------------
    Tommy Gerald
    ------------------------------

    #PlanningAnalyticswithWatson