Forum Discussion
Data Model in Service duplicating and replacing rows
Hi Ed. As I said over email, we've identified the root cause of this issue. We're currently targeting the fix for our July release.
In the meantime, you can work around the issue by ensuring that your folded queries are always sorted. This can be done by always including (and preserving in downstream queries) all primary keys for a given table. Or, if this isn't possible (as in your case when doing a Group By operation), you can explicitly sort by a set of unique keys before combining. (Also note that it may be necessary to buffer after sorting and before combining to ensure the sort is applied by the folding layer. You'll have to experiment to see whether or not this is necessary.)
Ehren
The SQL Queries are all pulling from SQL 2008 and simply connect to the tables. So:
let
Source = SQL_DATA,
Custom1 = Source{[Schema="dbo",Item="TableName"]}[Data]
in
Custom1My source is simply = Sql.Database("SQLSERVER", "DATABASE") - I do that so can change the server name in one place to do development work off of a different server.
Then there are a number of transformations, merges, etc.
I will tried the Buffer statement after the combine. After a few tests, it seems to have resolved it. The UNION and BUFFER tables consistenly report the same info, but the unbuffered one randomly gets out of whack. I ran 10 tests. F means the unbuffered table failed while the BUFFER and UNION tables worked, P means all 3 matched.
- P
- F
- F
- F
- F
- P
- P
- F
- P
- F
So you can see it is pretty random as to when it fails. These refreshes were done over a 15min period. Takes less than a minute usually for this to refresh via the Gateway.
This also just started the week of June 11, 2018 at the earliest. No changes to the reports here. Was published back in March and has been running fine. Only changes are updated gateway, and whatever MS does on the backend to its service.
Using table.buffer here could work as query folding was stopped a few stages before the Table.Combine, but I understand that kills query folding. Not looking forward to having to use table.buffer after appends if it kills query folding thereafter.
If that is good enough for helping you, let me know. If not, I will do the more detailed buffering on specific table and other tests.
I am avail to live debug. I ran some yesterday with tech support and they gathered Fiddler data. Is that what you mean or is there a more detailed process. The ticket is Ticket #:10593214 and is being tracked under emails with REG:118062218446002 in the subject.
Thanks, edhans. That's useful info.
Here's one more thing you could try: when running locally in PBIDesktop, you can (sort of) simulate what happens in the service by ensuring the Data Privacy firewall is enabled and marking all your data sources as Private. If you get rid of the Table.Buffer and get your queries back into their prior, refreshing-in-the-service-produces-bad-results state, what happens when you enable the firewall locally, set all your data sources to private, and refresh? I'm curious if this will cause the issue to happen locally in PBIDesktop.
Ehren
Ed, thanks again for your time today. I'll keep you posted as we continue to track down the root cause of this issue.
Ehren
Hi Ed. As I said over email, we've identified the root cause of this issue. We're currently targeting the fix for our July release.
In the meantime, you can work around the issue by ensuring that your folded queries are always sorted. This can be done by always including (and preserving in downstream queries) all primary keys for a given table. Or, if this isn't possible (as in your case when doing a Group By operation), you can explicitly sort by a set of unique keys before combining. (Also note that it may be necessary to buffer after sorting and before combining to ensure the sort is applied by the folding layer. You'll have to experiment to see whether or not this is necessary.)
Ehren
Ah, yes. You'll need the new gateway as well (once it comes out).
Ehren
Yes, this month is a little abnormal. :) I just checked and was told the July gateway should be out soon (in the next day or so).
Ehren
Hi edhans. Yes, it looks like you're hitting the issue described in the admin portal (which was an unintended side effect of the fix for the original issue you were hitting). Do you happen to know which region your PBI service is in? If so, I can let you know when the fix is deployed to your region.
Ehren
Here is what I did:
- Changed Global Options|Privacy to "Combine data according to each file's privacy levels." (it is just SQL Server here, one database, so one source)
- Changed Current File privacy to "Combine data according to your priacy level settings for each source"
- Edit Queries: Data Source Settings
- Cleared Permissions
- Edited the permissions for the Server/Database to Private
- Hit Refresh
- Authenticated again (domain, so just hit connect for the Windows Current Credentials)
- Refreshed about 10 times. Never failed the way it does in the service.
REdid the above but #1 at the top, changed global options to "Always Combine data according to privacy level for each source" (again, 1 source, so only one privacy level I suspect)
Repeated steps 2-6 as above.
About 10 refreshes. Worked every time. Never failed.Changed everything back to "ignore privacy levels." :-)
Ok. Just to clarify: when you did the buffering, were you buffering the result of the Table.Combine, or buffering each query before combining them?
Ehren
One buffer, immediately after the Table.Combine. Not the whole thing, but this is the M code:
let Source = Table.Combine({qryOESLSHST, SalesCurMonth}), Buffer = Table.Buffer(Source), #"Trimmed Cus_No and Item_No" = Table.TransformColumns(Buffer,{{"cus_no", Text.Trim}, {"item_no", Text.Trim}}),Hoping the team got in touch with you Ehren. Spent nearly an hour convincing them it wasn't the workspace, my browser, and renaming and republishing the report also wasn't the fix.
Honestly, if you need me to dump these tables into a different database so you can restore it into SQL 2008 let me know.I haven't seen anything. Can you please point them to this thread and ask them to add me to the email thread with you? I've met with two other customers who are experiencing this and would like to live debug some things with you.
Ehren
I'll do it again, but I sent the link to them already yesterday and we even read through some of it today.
She and her manager said there were a lot of "ehren" in the Skype directory and stopped there. :shrug emoticon:
Edit: Done. Sent 3:45pm pacific. Hopefully they get in touch with you.
We're experiencing similar behaviour with our SAP HANA report.
Sometimes the values displayed in our financial reports double but a manual refresh of the data seems to fix it. It's random and a pain to monitor and/or duplicate...
Hi Aron_Moore. SAP HANA uses a different backend pipeline than SQL, so yours is likely a different issue (although it's possible the root cause is similar). Can you create a new Power BI Community thread for it?
Thanks,
Ehren
I had a thread but hadn't received any input yet. Thought I'd tag along with this one for some ideas/help.
https://community.powerbi.com/t5/Service/Data-duplication/m-p/451485#M47670
edhans, This issue should be now be fixed. Please download the Power BI Desktop July release and let us know if things are now working as expected.
Ehren
Ehren wrote:
edhans, This issue should be now be fixed. Please download the Power BI Desktop July release and let us know if things are now working as expected.
Ehren
Thanks! Already have. So republish and I'm done? No new Gateway needed as well?
Thanks. I'll wait until that happens before I make changes to my process. Hopefully soon. Usually the gateway is out by last week of month. Of course, normally desktop is out first or second week of month, and that was later than normal this time.
I kinda figured this issue was part of the "abnormality" :smileyvery-happy:
Ehren I am not sure if this is the same issue or not.
You may recall in my tests I had a PBIX file loaded into my personal workspace that tested three scenarios:
- The original report that came from a Table.Combine() that was first experiencing the bug.
- A new table created in DAX by bringing in the two tables separately and then using UNION()
- A new table that was the original Table.Combine() from #1 above that then had a Table.Buffer() after it.
When the new gateway was released last week, I installed it, then for grins republished this report to my workspace and let it go, expecting to never hear from it. It was set to alert me if any of the 3 values above wasn't the same.
Today it alerted me. The Table.Buffer() version is way way way off. Instead of a total of 10,570 rows that #1 and #2 have above, it only has 1,402 rows. Instead of the last 12 months, it only has August 2017, Sept 2017, and August 2018. It dropped Oct 2017-July 2018. That data was pulled from the same source table as Aug-Sep 2017.
The Aug 2018 data is in a separate table in our server and that is what gets appended.
This report has run several times a day for the last week with no issues, and today, it blew up. Let me know if you want the PBIX file or something. I downloaded it from the service when I got the alert. It has already refreshed automatically again and all 3 are in sync.
Or maybe the rollout isn't finalized? I just saw PB145321 in the Office 365 Admin portal.Status:Service degradationUser impact:Users may see missing or incomplete data when refreshing reports, viewing reports or dashboards.Latest message:Title: Incomplete or missing data User Impact: Users may see missing or incomplete data when refreshing reports, viewing reports or dashboards. More info: Customers utilizing specific connectors when refreshing datasets without using native SQL queries, pulling SQL data that does not include a SQL primary key column in the final result, and doing Append or Merge operations may experience this issue. The following is a list of connectors impacted: SQL Server, Oracle, PostgreSQL, MySQL, Teradata, Sybase, Informix, DB2 or Access. Current status: We've initiated the deployment of the fix to the affected environment. We anticipate the deployment process completing by the end of this week. Scope of impact: Impact is specific to a subset of users who are served through the affected infrastructure. Start time: Friday, July 13, 2018, at 10:08 PM UTC Preliminary root cause: A recent update to the Power BI services resulted in missing or incomplete data when refreshing reports, viewing reports or dashboards in some scenarios. Next update by: Friday, August 3, 2018, at 7:00 PM UTCI think Western, and I only know that as that is what shows up when I have a sync error, and not 100% sure on that. I am in California if that matters. I don't see where in the PBI admin portal to check that out.
You should be able to determine the region by clicking the help question mark in the upper righthand corner of the Power BI website and selecting About Power BI. Look for the line that says "Your data is stored in...".
Ehren
Ehren - I am sure your team is working on this, but the Admin portal is either wrong, or there is still a bug you aren't aware of. My test queries using Table.Combine() and Table.Combine() followed by Table.Buffer() still fail on occasion, easily over 25% of the time.
The admin portal says this:
Title: Incomplete or missing data User Impact: Users may see missing or incomplete data when refreshing reports, viewing reports or dashboards. More info: Customers utilizing specific connectors when refreshing datasets without using native SQL queries, pulling SQL data that does not include a SQL primary key column in the final result, and doing Append or Merge operations may experience this issue. The following is a list of connectors impacted: SQL Server, Oracle, PostgreSQL, MySQL, Teradata, Sybase, Informix, DB2 or Access. Current status: The fix deployment has completed restoring functionality for most users. For all remaining premium users still experiencing impact, we anticipate the fix to reach your environment within 24 hours.
I am not premium. We are just standard pro-licenses. I just did a manual refresh at 6am Pacific Sunday morning, and it failed. As indicated earlier, our data is stored in the West US environment. California.
According to the text of the alert, our envornment should not be experiencing this, but we are.
