Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎09-27-2016
Accepted Solution

Security model in Sales Management for Dynamics AX

The Sales Management for Dynamics AX Solution template includes row-level security with rules derived from the source application while using a SSAS solution on-premise (Option 2).
In a previous post it is also mentioned that in the future this security model will become available in the "Import data into Power BI option"
Row-level security within Power BI has become available not so long ago, so I am wondering if the "Import data into Power BI option" yet supports row-level security derived from the source application.
Does anyone has experience with this or knows the answer? Your help is much appreciated.

 

Best regards,

Jeroen van Steenbergen


Accepted Solutions
Moderator
Posts: 39
Registered: ‎01-14-2016

Re: Security model in Sales Management for Dynamics AX

Hi Jeroen,

 

You're referring to the Dynamics CRM template, correct?

 

Almost everything is in place for you to implement this - the UserAscendants table is in the pbix file and contains a row for every individual and manager/skip/.../CEO (This is documented in our docs at https://github.com/Microsoft/Business-platform-solution-templates/tree/master/Template/Microsoft-CRM...).

 

The username function behaves differently in the service than AS on prem (returns an alias instead of domain\user) so the dax expressions will change a bit from what they are in SSAS. For example, the DAX filter for opportunity for SSAS is:

 

=or(
contains(filter('User Ascendants','User Ascendants'[Ascendant Domain user]=username()), [User Id], 'Opportunity'[Owner Id]),
contains(filter('User Ascendants','User Ascendants'[Ascendant Domain user]=username()), [User Id], lookupvalue('Account'[Owner Id], [Account Id], 'Opportunity'[Account Id]))
)

 

Inside PowerBI this will have to change to emails instead of accoutnt. So it will look like (not tested!):

 

=or(
contains(filter('User Ascendants','User Ascendants'[Ascendant Email]=username()), [User Id], 'Opportunity'[Owner Id]),
contains(filter('User Ascendants','User Ascendants'[Ascendant Email]=username()), [User Id], lookupvalue('Account'[Owner Id], [Account Id], 'Opportunity'[Account Id]))
)

 

If you implement this yourself, let me know and I should be able to assist.

 

Regards,

Richard

View solution in original post


All Replies
Moderator
Posts: 39
Registered: ‎01-14-2016

Re: Security model in Sales Management for Dynamics AX

Hi Jeroen,

 

You're referring to the Dynamics CRM template, correct?

 

Almost everything is in place for you to implement this - the UserAscendants table is in the pbix file and contains a row for every individual and manager/skip/.../CEO (This is documented in our docs at https://github.com/Microsoft/Business-platform-solution-templates/tree/master/Template/Microsoft-CRM...).

 

The username function behaves differently in the service than AS on prem (returns an alias instead of domain\user) so the dax expressions will change a bit from what they are in SSAS. For example, the DAX filter for opportunity for SSAS is:

 

=or(
contains(filter('User Ascendants','User Ascendants'[Ascendant Domain user]=username()), [User Id], 'Opportunity'[Owner Id]),
contains(filter('User Ascendants','User Ascendants'[Ascendant Domain user]=username()), [User Id], lookupvalue('Account'[Owner Id], [Account Id], 'Opportunity'[Account Id]))
)

 

Inside PowerBI this will have to change to emails instead of accoutnt. So it will look like (not tested!):

 

=or(
contains(filter('User Ascendants','User Ascendants'[Ascendant Email]=username()), [User Id], 'Opportunity'[Owner Id]),
contains(filter('User Ascendants','User Ascendants'[Ascendant Email]=username()), [User Id], lookupvalue('Account'[Owner Id], [Account Id], 'Opportunity'[Account Id]))
)

 

If you implement this yourself, let me know and I should be able to assist.

 

Regards,

Richard

Frequent Visitor
Posts: 3
Registered: ‎09-27-2016

Re: Security model in Sales Management for Dynamics AX

Thank you for the quick response, this answers my question.