A DirectQuery Connection Is Required - Taking Data Ownership For A Developer Who Left Company

[danielpaduck]

Hi All, 

Just wondering what the best approach to handle taking ownship for a Tenant from a developer who left before I started. So essentially, I was brought on about a month ago to help 'enhance' several Power BI Reports.  At first, I thought this was not too bad but after getting onto the tenant, I am dealing with one massive Data Set that about fifteen different reports are built from.  I was asked to update one of the reports, so I downloaded the report and wanted to import an Excel file to use with it but received the following:

Since this is a production environment, and such a large data set, I did not want to go any further with this for fear it will break existing items.  I actually did a test on my own tenant in which I set up a similar scenario and clicked 'Add to Data Model' but it seemed to have broken the first connection.  

Anyway, what do you think my best options are?

1.  Add the new Excel File to the existing data set in production

2.  Create a second Workspace and have a copy of the data set which would also have the new Excel file as part of the data set? 

Thanks for all your help! 

[danielpaduck]

Hi - when I tried to upload the data set to the new workspace I was getting a memory exception.  Thus, does Power BI only have so much capacity for memory for data?   Also, lets assume it does upload how do I point an existing report that is live connected to the original data to point to the sandbox dataset? Is that easy to do without breaking anything?  Thanks! 

[Anonymous · Accepted solution]

Hi @danielpaduck ,

Thank you for question. Based on the two options you presented, I will analyze the pros and cons of both for you:

1. Add the Excel File to the Existing Dataset

    You need to be CAUTIOUS. Please note that adding data to a DirectQuery dataset can be complex and may require changes to the underlying data source or the creation of a composite model.

Here are the steps:

1. Connect to the existing dataset using DirectQuery in the Power BI Desktop.
2. Use the "Import" connectivity mode for the Excel file.
3. Combine the data as needed using Power Query Editor.

4. Publish the changes to the service and ensure that the dataset refreshes successfully.

   For more information on this you can check out the following links:

   https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

2. Create a Second Workspace

   If you want to avoid any risk to the production environment, creating a second workspace is a SAFER option.

   Here's how to do it:

     1.Create a new workspace in Power BI Service and copy the Semantic model and the report to the new workspace.

2. Add the Excel file to the dataset in the new workspace.
3. Make the necessary changes and test thoroughly.
4. Once tested, you can consider replacing the production dataset with the updated one from the new workspace.

^{Best Regards,}

^{Yifan Wang}

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

[danielpaduck]

Hi - I appreciate your feedback.  I moved forward with the second option too.  I feel that is the safest considering the circumstances.  Thanks!