Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JenM
Advocate II
Advocate II

Obsolete data sources in dataflow lineage

I have a dataflow with 8+ sources.
Within this dataflow, over time I've moved from a development server to a production server, and I've referenced both dev and prod databases.

 

I've also noticed that I can reference a server & database in a single steps (Source = Sql.Database( <server>, <db> )) instead of two steps, and so I've edited all my entity queries to use this more concise approach. It seems that this single-line reference produces a distinct 'data source' in the lineage view from a 2-line approach, which I understand.

 
My issue is that my lineage view shows every data source I've ever used. I expect it to show the current datasource only - In this case, it seems like it should just show one (#3):
 
  1. currentserver.database.windows.net
  2. DevDB-currentserver.database.windows.net
  3. ProdDB-currentserver.database.windows.net
  4. ProdDB-obsoleteserver.database.windows.net
What am I missing?
10 REPLIES 10
EG151
Frequent Visitor

I know this is an old post, but figured I would share something that worked for me...

One thing that I did to remove old SQL connections on a dataflow was when EDITing the existing table on the dataflow, I went into OPTIONS > PROJECT OPTIONS, changed the DATA GATEWAY to "(none)", clicked OK. 

I then received a "Credentials are required to connect to the SQL source" error message on the query. 

I went back into OPTIONS > PROJECT OPTIONS, changed the DATA GATEWAY back to the correct gateway that contains the connection.

Received a "We could not evaluate this query due to invalid or missing credentials" message.

Clicked on CONFIGURE CONNECTION...the connection to my correct gateway popped up, I clicked on CONNECT, and then clicked on SAVE and CLOSE. 

After the validation completed and I closed the power query editor screen and dataflow, the lineage view no longer contains the old connections and it only contains the current active connection.

I did this successfully on 20+ dataflows that recently had a SQL server change (same query, just new server name).

Hopefully this helps.

This worked for me too, with one small modification. It actually wouldn't allow me to change the gateway to "None." I tried several times, but every time I clicked "OK", it automatically reverted back to the same gateway that was selected before. What actually worked was changing it to a different gateway, and then changing it back again. Thank you for pointing me in the right direction!

Your suggestion about taking "None", then you get the creds issue, then you select the desired gateway, worked for me.   MUCH APPRECIATED!!!

molegris
Advocate III
Advocate III

@JenM , you're right, the problem is not resolved.

@v-kelly-msft , the workarounds you suggested don't address the issue at hand.

 

Here's my similar use case:

 

The Import from CDS dataflows has 2 entities from the same exact source (source A in the image below).  The lineage shows an old artefact of a previously used connection that is no longer active in this dataflows.  This is very confusing for the people responsible to maintain it!

 

molegris_0-1595352097748.png

When I go to Edit Entities, I can confirm via the Advanced Editor that both entitites are connected to source A.  

I also tried to go to the Settings view of this dataflow and it gets worst there!  There's a warning saying I can't refresh this dataflow because one of the credentials are invalid.  Well, the invalid one is NOT used anymore.  And to add to the confusion, there is also a 3rd source listed here which is neither used in that dataflow nor showing up in the lineage view.

 

I'm desperate to fix this and any help would be greatly appreciated!

 

Thank you.

v-kelly-msft
Community Support
Community Support

Hi @JenM ,

 

Actually there's no such functionality to work it out,but I have a workaround,that is, you can click on the bottom right corner which I marked in red circle,then you will see that all related to the current datasource will be highlighted,and others will be grayed out.

 

Untitled.png

 

Here is the related reference.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-data-lineage

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

Sorry, but your workaround doesn't address the bug. If I click on the arrows for the dataflow, all 7 upstream data sources get highlighted. Only one of them is valid, the others should not exist, and they provide bad intelligence.

 

My (horrible) workaround is to export the dataflow as json, delete the dataflow and then reimport the json to a new dataflow. When I do this, the lineage diagram correctly shows just a single datasource for the dataflow. Of course this breaks every single usage of the dataflow, so in a high-usage scenario this would be unacceptible.

Hi @JenM

 

Is your issue solved?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

No, the problem is not resolved. Then only way to remove obsolete data sources is to export and reimport the dataflow (as JSON). But that breaks any downstream usage...

Hi @JenM ,

 

If so ,I have a simple way for you:

Connect the dateflow in desktop,then go to data source setting and clear your data source then reenter it:

Annotation 2020-04-07 094342.pngAnnotation 2020-04-07 094858.png

 

Finally save as a new report or cover the previous report and  publish to service.

I guess this way will help solve your issue.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

The question was how to remove old Data Sources from a Power BI Dataflow, not a Report in Power BI Desktop. Please see the answer posted by @EG151 above, which actually resolved the issue: Re: Obsolete data sources in dataflow lineage - Microsoft Fabric Community

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors