Access Azure TABLE Storage directly with Power BI Premium site

[pulatonano]

Let me state up front that I am at best Power BI novice. 

I have a very large quantity of data being concentrated from 14 locations across the USA in an Azure Table storage for Machine Learning purposes.  The data is made available to each location for internal use at the individual facility using a Power BI report that pulls data from Azure Table Storage.  Power Bi Report is setup to do incremental refreshes on a daily basis for 60 days. 

Incremental refresh settings

Common issues are that the connection is "Forcibly Closed".  On very large facility see this issue more than others and it recently failed and now the incremental refresh can no longer get caught up without the connection timming out.  Meaning-- it does not seem to be recoverable--unless I pull I can publish Power Bi Report in a nearly compete state -- it times out before I can get the 60 days downloaded.

I have tried building a Data Flow that pulls all the data into one Data Flow and build reports agains this--however it too often fails the refresh and when it dose succeed the refresh interval is 6 -10 hours.  I could live with the long refresh if it were consistent and did not fail over 50% of the time.

It seems that since the data is already in Azure, my Premium Power Bi sites are in Azure:
     1.  This refresh should not take so long?  

     2.  Do I reall need to keep replicating the same data in multiple locations within the Azure environment?

          Note: Flat Table storage is used for cost savings --we are currently pulling in over 1 TB per year for just this one project.

[v-yingjl]

When the refresh failed, it there any error information about it or has no information but always under refresh, you can try to find it in refresh history.

Currently about dataflow refresh, both refresh failed and refresh for a long time , many similar issues have been submited as far as I know(ICM: 292208833, 288091663), if there is any progress about it, would update here as soon as possible

In addition, here is the similar thread that you can refer: Dataflows not refreshing today 

Best Regards,
Community Support Team _ Yingjie Li

[pulatonano]

@v-yingjl :  Thank you for your response! 

YOU ASKED:  When the refresh failed, is there any error information about it or has no information but always under refresh, you can try to find it in refresh history:

Most common:

Data source error: DataSource.Error: <pii>Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.</pii>. DataSourceKind =
<pii>AzureTables</pii>. DataSourcePath = <pii>https://npusprdmdatalakeuseasta.table.core.windows.net/</pii>. ;Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. The exception was raised by the IDbCommand interface.
Cluster URI: WABI-NORTH-EUROPE-M-PRIMARY-redirect.analysis.windows.net
Activity ID: 8bc7244c-5b1e-4f2c-b337-01fbbe0c525a
Request ID: 0f4b8278-8ee9-466a-bdd0-328cfe71a30c
Time: 2022-03-02 15:37:50Z

I ALSO SEE:

The refresh operation failed because it took too long to complete. Consider reducing the size of your dataset or breaking it up into smaller datasets.
Cluster URI: WABI-NORTH-EUROPE-M-PRIMARY-redirect.analysis.windows.net
Activity ID: 211cb1a8-8d48-4f60-a0d4-b87c3b2ccefe
Request ID: d1cd3f88-ce44-42f6-832e-dc752b8523aa
Time: 2022-03-03 04:12:03Z

I am currently Experimenting with the Tabular Editor to reduce my Incremental refresh window until I can get the Report to refresh.  (Tabular Editor was referenced in this doc: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-external-tools )

Setting Rolling Window period to 3 Days [Red box below] I was able to get a refresh to work (after over a month of failing).  I am now trying to "walk" this one number back up to 14 days--2 days at a time.   If I start seeing refresh failures again I will lower it back down to find balance point -- some data is better than no data to my end users. 

Tabular Editor Screen shot

Again: Data source is in Azure and Power BI Premium Site is hosted in Azure--I'm sure it's more complicated than this but it would be nice if I could link the The Azure source in POWER BI so I don't have to keep on moving data. (Suggested Feature).