Integrate PowerBI with AX7

[fat0527]

I am a developer of AX, and would like to explore the integration between AX7 and PowerBI.

I saw there was a great update about Query Parameter and File Templates in April: Deep Dive into Query Parameters and Power BI Templates

However, I have some questions since I start learning the integration between AX7 and PowerBI.

1. In AX, most of data are company aware. AX creates a field called DataAreaId in all the tables if they are company specific. When AX user browse the data within AX, user can only see the data within the company context. I tried to connect AX via Odata or View. I found if I use Odata, PowerBI extract all the data from user's default company, which is not ideal, as one user can belong to more than one company, and they would like to swap the company to browse different data. If I extract the data from view, it extract all the data directly from the under layer tables. Seems the Query Parameters update allow user to select different company. However, is there a way to limit the user can and only can see/select the company they belong to? If that is impossible now, is there any plan for that?
2. When we implement ERP to different customers, some reports could become our standard reports and be shared among customers. The data structure could be the same but just the connection string would be varied. With the file templates function, seems we can extract the report definition without customer data now. However, is there any easy way to configure the connection string? I currently go to indivduial tables/views by using the advance query editor and change it one by one over there.

Thanks in advanced.

Frankie

[Greg_Deckler]

For your first question, I believe the answer will be row level security, RLS. @ankitpatira has a good post on it here.

For you second question, you should be able to use parameters in connection strings, otherwise, see the technique below:

let
    DevSource = Csv.Document(File.Contents("C:\temp\powerbi\test_dev.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    ProdSource = Csv.Document(File.Contents("C:\temp\powerbi\test_prod.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    
    #"Promoted Headers Dev" = Table.PromoteHeaders(DevSource),
    #"DevResult" = Table.TransformColumnTypes(#"Promoted Headers Dev",{{"Column1", type text}, {"Column2", Int64.Type}}),

    #"Promoted Headers Prod" = Table.PromoteHeaders(ProdSource),
    #"ProdResult" = Table.TransformColumnTypes(#"Promoted Headers Prod",{{"Column1", type text}, {"Column2", Int64.Type}}),

    result = 
        if Environment = "Dev"
        then #"DevResult"
        else #"ProdResult"
in
    #"result"

[fat0527]

Hi Smoupre,

Thanks for your super prompt reply.

For question 1, i think RLS should be the solution of it. Just seems there are several limitations for the RLS settings. Are my below understanding correct?

1. The RLS can only be configured on the service and based on the dataset created by desktop. Republishing the dataset will lose the setting (Refreshing the data will not lose it). Is that a plan to let the user can configure the RLS setting on Desktop and publish with the dataset together? That will save some time on the setup as sometime the setup could be very complicated in a big organization.

2. RLS is only available to the users have registered on the PowerBI.com. That means every user within AX has to sign up in PowerBI. Before that, the setup cannot be finished.

3. In AX, two ways to share the PowerBI content. One way is to show the dash board. With that option, user can add their own dash board into their AX work space. When user click the dash board within AX, it redirects the user to PowerBI.com. By that way, RLS works fine. Another way is to show the embedded PowerBI report within AX by using the embedded code. The user can directly interactively work with the report within AX. However, by that way, no matter which user you are using and whether the user is already signed up in PowerBI, they can run the report just like what they do for an embedded report on the web. I suppose in that case, RLS cannot work. Howevere, that is actually the preferred way we use in AX. As the second uses a embedded URL, is that possible the URL can accept parameter and apply to the query filter feature released in April?

For the second question, do you have a link i can learn a bit more? I should mention I am an AX developer not PowerBI developer:).

Cheers,

Frankie