Forum Discussion

fat0527's avatar
fat0527
New Member
10 years ago

Integrate PowerBI with AX7

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 Para...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
fat0527
New Member
10 years ago

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