Name is Anant Dubey and the intent to create this blog is to discuss the problems and issues that developer face in the dynamics AX development and to share the new things that come up with the new version of AX.

Tuesday, 24 July 2018

POWER BI - Changing datasource from Import to Direct Query

Hi all

I recently wanted to change the datasource from Import to Live Query using the "Power BI service" and had a good lookaround without turning up much other than "you can not"...

However I managed to get it going, effectively maintaining my designs/visuals and changing datasource so that I can get away with publishing one report whenever my data changes.

* TARGET:  A .pbix file with designs/visuals using import, no measures.
* SOURCE: A clean .pbix file using Live Query to a report/dataset published to (no designs/visuals)
   -This .pbix file must have the same table as the TARGET file (ie: using the same db)

1. Rename .pbix files to .zip
2. Do this [edited steps]:
        => DELETE "Datamodel" from TARGET
        => COPY "Datamashup" from SOURCE to TARGET, overwrite.
        [ =>Not necessary to copy "Connections" based on latest test, but it does not seem to hurt]
        (I used Total Commander for this as it was simplest and it supports archive files)
3. Rename TARGET file back to .pbix
4. Open TARGET file in Power BI
5. The design/visuals and the Old table structure should be visible under "Fields"
6. A yellow warning reading "There are pending changes in your queries that haven't been applied  [Apply changes]"
    may or may not be displayed
7. Click [Apply changes] if it appears
8. All designs/visuals should get an (X) and the message "Something's wrong..."
9. Now go to "Get Data" and select your datasource (i wanted to use data from Power BI services, and a previously published
    reports dataset. Select your datasource and Load it.
10. The designs/visuals should get the proper data filled inn (connection in model checks out) assuming that the previously
      published report/dataset holds the same table (same name etc). If Not all visuals are correct, the proper measures may not be present in the datasource, re-create them there and open the TARGET pbix again to check.


If you in your TARGET file which is relying on datasource import has created measures and such, these should probably be re-created in the published report/dataset prior to following the steps above (untested).

Note: If you are unable to open the .pbix file in PowerBI after renaming it back to .zip, there might be an issue with your zip program. For my part it failed when using windows inbuildt functionality but worked when using Total Commander to copy files between the .zip files...

Hope this is of value and help.