Tag: PBI RS

PBI: When you can’t change from a live connection

First off, I want to say I never intended to start a lot of my posts about Power BI. There are plenty of experts out there and I am merely an accidental PBI Admin and advocate for our Power BI platform. So why should I be writing about a topic I’m constantly learning new things about? Well, here’s the thing: when you are in the middle of learning things and you don’t find a quick and easy answer, that may be a good time to say “hey, maybe I should blog about it”.

And I think it was Kimberly Tripp’s keynote at PASS Data Community Summit 2022 that reminded me that it’s 100% ok to write about things other people have written about. In fact, several people there mentioned this same thing. Share what you have learned – it’s quite possible you bring a different perspective that will help someone along the way. And if all else fails, you may forget the solution and years down the road google it only to find your own post. (#LifeGoals)

Rihanna with ponytail - text: "Yup thats me"

Now that we have that out of the way, let’s talk about WHEN YOU CAN’T CHANGE FROM A LIVE CONNECTION in Power BI.

Recently, I’ve been advocating that we consolidate our reporting systems. We have a ton and with an extremely small team, it’s a huge headache to manage them all. Admin-ing reports are only supposed to be a small portion of my week-to-week tasks. (Hint: it’s not.) Plus, some of our reporting systems are just not that good. I won’t list all the different systems, but we are lucky enough to have Power BI as part of our stack and as such, I’m wanting to move as much as we can to our underutilized Power BI service. This includes our Power BI Report Server reports.

Since we needed to make some changes to some reports we had on Power BI RS, and wanted to reap some of the benefits of Power BI service with them, we decided these reports would be a good test group to move over. The changes were made in the newer version of PBI Desktop (instead of the older version of desktop we have to use with PBI RS) and we were ready to load them up to our Power BI service. This is where it got a little sticky.

Sticky buns with sticky spoon next to it.

sticky buns… mmmmmmm

FOCUS.

When I uploaded a new report, I remembered it was going to create a “dataset” with the report. Even if the lineage showed the dataset went to a live connection to a database. (In the case of our test case reports, they were connected to a SSAS database). Note, these datasets don’t seem to actually take any space when connected to a live connection, hence my quotes.

Shows a newly created dataset when you upload a new report.

A dataset for every report? Given the number of reports we needed to move over, all with the same live connection, this didn’t make sense to me. Even if the dataset was just a passthrough. (Did I mention how I really hate unnecessary objects in my view? It eats away at me in a way I really can’t describe.)

Diagram showing current state of a dataset for each report, even when connected to 1 data source.

So I thought – “why not just create 1 live connection dataset and have all the reports in the workspace connect to that?” (We also use shared dataset workspaces, and if you are using that method, this still applies. In this case I wanted to use deployment pipelines, and as of the writing of this post, that wasn’t doable with multiple workspaces per environment .) I built my new dataset, uploaded it, and prepared to connect my report to the new one.

SCREECH. Nope. After all that, when I opened my shiny new report updated RS report in PBI Desktop, I didn’t even get the option to change the connection.

Report showing live connection

Darn it. I couldn’t switch it to the new dataset. My only option was the original live connection. I couldn’t even attempt to add another data source.

I grudgingly loaded the report back up to PBI Service and now I had to look at 2 datasets while I noodled. Blerg. (I’ll mentioned again how much I hate a cluttered view.) Technically I could delete my test case dataset, but I wasn’t ready to give up yet. An idea occurred to me: let me download the newly uploaded file from the PBI Service, because logically it had created a new dataset to use when I uploaded it and the lineage showed it in the path.

I opened the report in the service, choose File–>Download this file, and then selected the “A copy of your report with a live connection to data online (.pbix)” option. (Actually I tried both, but the other way was a fail.)

Then I opened it in PBI Desktop… Meh. It looked the same.

Wait a minute! What is that at the bottom??? “Connected live to the Power BI dataset”!

I check the data source settings again under Transform data – BINGO! Now I had the option to switch the dataset from my Power BI service. Which I happily did.

After this was done, I saved and reloaded the report to PBI Service and checked the data lineage of the dataset – It was connected to the new dataset! YAY!!!!!! Since all the reports in this workspace used the same SSAS database, I could connect them all to the same singular dataset. Bonus that when it came time to setup the deployment pipeline, I only needed to change the data source rules for one dataset in each proceeding environment.

Some may say this is overly obsessive. Maybe. But when you think along the lines of maintenance or verifying data lineage, I now only needed to check 1 dataset instead of going through a whole list. That can be a timesaver when troubleshooting problems.

AND it’s prettier. AND there may be other reasons you want to change that connection and this should help along the way. AND there was another big reason I was going to list, but now I’m thinking of sticky buns again so we will just leave it right there. It’s time for lunch.

SSRS/PBI RS Error: Cannot read the next data row for the dataset

I’m an accidental SSRS / Power BI Report Server / PBI.com Administrator, along with my other do-all-things-data duties. It’s been a hot minute (cough years) since I’ve messed around in SSRS and I’m relatively new to PBI – both from the reporting and the administering side. Years ago I did some work with Tableau, but my most recent work in that realm was more R and Python based. Suffice to say – I run into issues a lot. Particularly with PBI Report Server (PBI RS).

Pssst – hint – PBI Report Server is basically the same as SSRS, so if you find some obscure problem and everything you google results in regular PBI answers, try googling the same problem with SSRS. Different database name, same almost everything else. YOU’RE WELCOME. You have no idea how much time I’ve just saved you.

That said, this specific error I came across was in SSRS, but really you could get this error from any of the reporting products.

You’ may receive an error that vaguely resembles this:

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset <objectname> (rsErrorReadingNextDataRow).
For more information about this error navigate to the report server on the local server machine, or enable remote errors

While there are probably MANY reasons you may receive this error, I’m going to talk about one here: XML. That’s right, your old frienemy XML.

In our case, we were getting XML documents from AX/various sources, and storing them in another database in an ntext column. <insert obligatory “I didn’t build it” disclaimer>. So when another process was pulling it … say for a report... it was barfing all over the place. Running a snippet of sql that the report was using, resulted in a more helpful message:

Msg 9402, Level 16, State 1, Line 15
XML parsing: line 1, character 38, unable to switch the encoding

What’s in line 1 of all these xml documents? The XML Prolog.

You wanna know something else? It’s optional. And that pesky little UTF-8 encoding is causing all of your woes. My suggestion: find what is adding the prolog to your XML docs and get rid of it. (Unless it’s a person, then just tell them to cut it out.)

There are alternatives you can do, but I wouldn’t recommend them. I’ll list some here in case you want to experiment (I didn’t.) You could clean the data and remove the prolog (aka declaration or header information), but that’s a messy solution that will probably have to be changed over time. (More than you would think at first glance.) If you are on SQL Server 2019 or above, you could also consider using the UTF-8 encoding support they introduced – even at the column level – but that could have unintended consequences, and again, unplanned maintenance down the road. Personally, I have enough to do – thank you very much. (If you want to investigate this route here’s a handy dandy little article Introducing UTF-8 support for SQL Server. )

If you aren’t dealing with an ntext column, then you may have some additional alternatives here: XML Parsing – Unable to switch encoding using utf-8 when casting from NVARCHAR. But again, you run into the issue of changing column types/lengths and from personal experience, that can cause other big headaches. Don’t say you weren’t warned.

That’s it. I didn’t really plan on making my first kinda techie article to be about SSRS/PBI/XML – but it happened to me today and now it is yours. Use it as you want or stuff it in a turkey.

Speaking of turkey: my daughter drew a great pic last night of a turkey that didn’t look exactly like a turkey because she “wanted to draw a turkey that people wouldn’t want to eat”. Unfortunately it got messed up and I can’t share it with you. As a poor substitute, I give you a turkey from Summer of 2021 that was contemplating his attack on ME.

Turkey about to attack me.

Hope you all have a wonderful Thanksgiving holiday!

–Data Work Mom