Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSolved! Go to Solution.
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
I publish PBIX from desktop to service and use a gateway to SQL Server to retreive data to refresh daily. I too came across that the data was not refreshed properly and was random and since then i did not got this issue. But I also faced the same issue earlier.
Thanks. A good confirmation I am not nuts. If you have any data, please open a ticket. It may help MS track this issue down.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingha ha yup but later i didn't face this issue. Sure I will check and if have will open a ticket. But thanks for this information because I was intially thinking it was my issue in model. But frankly i didn't change anything in the model and the issue fixed by itself.
Yup. I've seen it fix itself, then come back, then fix, etc. Seems a bit random.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi all. Do you ever see duplicated rows after refreshing locally in PBIDesktop, or only when refreshing via the Gateway?
Ehren
Only via the gateway. I have the June 2018 version 14.16.6724.2 installed, which is what https://www.microsoft.com/en-us/download/details.aspx?id=53127&751be11f-ede8-5a0c-058c-2ee190a24fa6=... is pushing. (it shows a different version there, but it sends the version I listed.)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk. Does the duplication only happen for rows coming from a SQL Server data source? Or does it happen for other data sources as well?
Ehren
Most of our data is from SQL Server, so I've only seen it happen there.
Edit: Actually, 100% of our data through the gateway is SQL Server. In the few cases where we use CSV files or something else, that isn't via the gateway. I'm not mashing up any gateway+non-gateway data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI ran a test and this is what I am seeing:
Something isn't right with how the Table.Combine statement is being loaded either by the Gateway or being handled by the service. The desktop app is fine.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Ed. I've tried reproducing this locally (using SQL data refreshed via a Gateway) and am unfortunately not seeing any duplication. Would it be possible for you to try some other things for me?
I'm trying to pinpoint if the issue is happening on the M query side of things or somewhere in the AS pipeline.
Thanks for your help.
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
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks, @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:
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." 🙂
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk. 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}}),
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHoping 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |