Data Model in Service duplicating and replacing rows

[edhans]

I have a ticket open with MS on this but am interested to see if anyone is noticing an issue where their reports in the Service from published PBIX desktop files are not right. Here is what I've found:

• The record count is always correct. For example, we have 877 records in May 2018. That is correct.
• Some records are duplicated, but something in the data model is telling it to maintain the same record count, so other records are removed. This is 100% in the data model (DAX) portion. Not the query.
• I've seen reports refresh ok (most of the time) and then randomly exhibit this behavior.
• Started late last week. (towards the end of the June 11, 2018 week)
• I have verified there is no corruption in the PBIX file itself as I redownloaded it from the service and refreshed it on my desktop. The numbers work.
• If I download the PBIX from the service when it shows wrong numbers, and don't refresh it I can pinpoint which rows are duplicated and which are removed by using the new filter views in the PBI Desktop app and copying the filtered tables to Excel for further analysis. THat is how I know the record count is always correct.

As I said, I have a ticket open and have started the initial conversations, but wanted to see if anyone here was experiencing the same issue recently.

 On the June 2018 PBID and June 2018 Gateway. These are SQL Server tables. I've not seen this issue on other data types, but most of our data is SQL Server.

[Ehren]

@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

[edhans]

@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?

[Ehren]

Ah, yes. You'll need the new gateway as well (once it comes out).

Ehren

[edhans]

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.

[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

[edhans]

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

Status:

Service degradation

User 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 UTC

[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

[edhans]

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

[Ehren]

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

[edhans]

---

@Ehren wrote:

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

---

West US (California) is what it says.

[edhans]

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

[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

[edhans]

---

@Ehren wrote:

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

---

I think there may be an issue with the November gateway or service @Ehren

I have kept my report running that does the same sales table 3 ways:

1. Table.Combine()
2. Table.Buffer before doing the Table.Combine()
3. UNION() measure in DAX

Last night, #2 reported different results. Thousands of dollars. The report that is running has not been updated with the desktop in months, but I did just install the Nov gateway yesterday morning.

[Ehren]

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

Ehren

[edhans]

---

@Ehren wrote:

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

---

Thank you! Trying to restore confidence in the reporting tool hasn't been easy lately. Looking forward to the link.

[Ehren]

Sent. Please let me know if it came through.

Ehren

[edhans]

---

@Ehren wrote:

Sent. Please let me know if it came through.

Ehren

---

Yes. Downloaded and installed. Testing now. Appreciate it.

[edhans]

I kinda figured this issue was part of the "abnormality" 

[Aron_Moore]

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

[Ehren]

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

[Aron_Moore]

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