Forum Discussion

pawlowski6132's avatar
pawlowski6132
Icon for Helper V rankHelper V
4 years ago
Solved

Migrating queries from one source/mode to another. Best practice?

I have a report with a query that runs native SQL to Oracle in import mode. I need to switch that query to sourcing from a Power BI dataset which, will only operate in DQ mode.

 

I have 8 relationships to other dimension tables and numerous measures built off this fact query/table.

 

I don't see around re-building this report unfortunately. I hope I'm wrong. 

 

Does anyone have any best bractices they've developed going through a similar exercise?

 

I actually have six of these queries/tables in this report I have to deal with.

Need Help
  • pawlowski6132's avatar
    pawlowski6132
    4 years ago

    Hmmm. I have to think through this a bit. There might be something in there I can use. Of course, and it's always the case, my situation is a bit more complicated I think. I'm actually going to be creating 6 discreet datasets. The reason is, while 2 of the queries need to refresh multiple times a day the others need to refresh weekly, monthly or not at all. 

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi pawlowski6132 ,

     

    If your model started out in Excel, but now you are forced to connect to data in a database, you do not have to stress out. Yes, unless you watch this video, you will have to rebuild everything, but if you do watch this video, I will show you how you can move your table(s) from one data source type to another without having to break anything.

    The steps to switch are the following:

    1. Bring the new table in (do not delete the old one – NEVER delete the old one)
    2. Disable load for the new table
    3. Make the new table look as close as possible to the old table (if you can’t, don’t stress, as long as the old column names can still be found – nothing will break)
    4. Go to Advanced Editor in Transform Data window and copy the code (M code for the techies) into your clipboard then hit escape
    5. Now go to Advanced Editor for the old table and replace its code with the new code you had just copied into your clipboard, then hit OK
    6. Do it for all tables you need to switch and then hit Close and Apply
    7. You are done, everything should still work measures will still work as long as the column names stayed the same and your relationships should not be broken

    Please watch the video for more details:

    https://www.youtube.com/watch?v=9AG1gIMRADo

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.