Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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???
Solved! Go to 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.
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
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.
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
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
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
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.
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
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
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??
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
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)
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |