Data Model in Service duplicating and replacing rows

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

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

Hi edhans. This issue has now been fixed in the Power BI service, but the gateway update will be coming in the next day or two. I will email you a private link so you can download the new gateway before it's officially released.

Ehren

Hi edhans. That sounds concerning. I'll try reaching out via email.

Ehren

Also, if you're available to live debug this, please ask the folks handling your ticket to schedule a Skype meeting with me. Might be easier than going back-and-forth here on the forum.

Ehren

Another question: what version of SQL Server are you pulling from?

Ehren

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
    Custom1

My 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.

1. P
2. F
3. F
4. F
5. F
6. P
7. P
8. F
9. P
10. 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

Here is what I did:

1. 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)
2. Changed Current File privacy to "Combine data according to your priacy level settings for each source"
3. Edit Queries: Data Source Settings
   1. Cleared Permissions
   2. Edited the permissions for the Server/Database to Private
4. Hit Refresh
5. Authenticated again (domain, so just hit connect for the Windows Current Credentials)
6. 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:

1. The original report that came from a Table.Combine() that was first experiencing the bug.
2. A new table created in DAX by bringing in the two tables separately and then using UNION()
3. 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.