incremental data refresh using union error

[Ashi]

I'M getting data refresh issue in power bi service.

error is 

"There was an error when processing the data in the dataset."

since the data is huge,i have  created multiple small fact tables and the final fact table is UNION of all tables.it works fine in power bi desktop,but not in service.

data base used is SQL 2016.

Any suggesions where i went wrong???

[v-jiascu-msft]

Hi @Ashi,

Due to there isn't a clear error message, we need to do some troubleshootings.

1. Is the SQL 2016 on premise or on cloud (Azure SQL)?

2. If the SQL 2016 is on premise, did you install an On-premise Gateway?

3. What's the status of the settings of the dataset? (two parts are important)

Best Regards!

Dale

[Ashi]

SQL is on premise,i have installed a gateway which works fine.

i have divided the dataset on the basis of date.

i have three tables

1-->facttable(jan-mar)(excluded in report refresh)

2-->facttable(apr-jun)(excluded in report refresh)

3-->facttable(july onwards)(included in report refresh)

4-->finalfacttable=union(facttable(jan-mar),facttable(apr-jun),facttable(july onwards))

if i only use "facttable(july onwards)" in my model,it works fine.The data is refreshed daily without any errors.

[v-jiascu-msft]

Hi @Ashi,

Usually there is a "See Details" of the error message. Could you please try to find it out? 

1. Maybe you could upgrade Power BI Desktop first, then republish the report again.

2. Try to download the "PBIX" from Service, then check if the error happens.

I tried, but didn't reproduce this issue.

 

Best Regards!

Dale

[Ashi]

This is what error looks like:

Processing error: The current operation was cancelled because another operation in the transaction failed.
Cluster URI: WABI-INDIA-WEST-redirect.analysis.windows.net

I will update the My Power BI Desktop and republish it.

will let you know the results

[v-jiascu-msft]

Hi @Ashi,

I recognized that "exclude in report refresh" only works in the Desktop after consulting an expert. Now it's clear for us to solve this issue. Involve the two tables in the refreshing in the Desktop. Test it and find out the cause. Then fix it. 

Best Regards!

Dale

[Ashi]

now i'm getting new error while  refreshing from the desktop

"failed to save modifications to the server. error returned:"memory error:memory allocation failure".try simplifying or reduce the number of queries"

server is already 64 bit and installed RAM is 48 GB.

[v-jiascu-msft]

Hi @Ashi,

Firstly, "the server" means the computer on which the Desktop is running. What is the RAM size? 

Secondly, what is the size of the whole dataset? It seems you have a big dataset. 

Best Regards!

Dale

[Ashi]

hi @v-jiascu-msft,

RAM Size is 48 GB.

Data size is huge and 

Fact table is about 30 GB.

[v-jiascu-msft]

Hi @Ashi,

Sorry for so late. It seems the memory is enough. Please try to uncheck this option. 

The dataset limitation of refresh of On-Premise gateway is 10G. Please Reference: powerbi-gateway-onprem-tshoot.

>>>The exact limitation is 10 GB of uncompressed data per table. 

Best Regards!

Dale

[Ashi]

Thanks for the reply,

 i will try that.

one more doubt i have.

my fact table is about 30 GB,and before doing union i was getting this error "The received uncompressed data on the gateway client has exceeded limit.

 "

was doing union a good solution??

[v-jiascu-msft]

Hi @Ashi,

Doing union means "Append queries" in the Query Editor, right? I have to check it out and reply later.

The dataset limitation of refresh of On-Premise gateway is 10G. Please Reference: powerbi-gateway-onprem-tshoot.

>>>The exact limitation is 10 GB of uncompressed data per table. 

Best Regards!

Dale

[Ashi]

Hi @v-jiascu-msft,

I have used Dax "UNION" function. 

and not tried merge option.

i have three tables

1-->facttable(jan-mar)

2-->facttable(apr-jun)

3-->facttable(july onwards)

4-->finalfacttable=union(facttable(jan-mar),facttable(apr-jun),facttable(july onwards)

[v-jiascu-msft]

Hi @Ashi,

According to my test, it's bad to use a "UNION" function. Any updates in the source tables will be applied on the UNIONED table. So "UNION" doubles the whole dataset. 

Best Regards!

Dale

[Ashi · Accepted solution]

Hi @v-jiascu-msft,

Thanks for the reply.

I also came to know that power bi has the limit of 10 GB uncompressed data for on-premise data refresh.