Yesterday the Power BI team released a new version of Power BI, which have included the most wanted feature ever.
The ability to share your reports outside your organisation, and easily do that. The feature was the most upvoted on the Power BI forum, and it show very clearly that Microsoft and the Power BI team is listening to the end users.
That’s not good. I suspected this was because of the format of the file, so I added a row terminator.
BULK insert MyTable
with ( ROWTERMINATOR = ‘\r’)
That didn’t help. I suspected this was because of the terminators for some reason. I also tried the newline (\n) terminator, and both, but nothing worked.
Since I was worried about formatting, I decided to look at the file. My first choice here is XVI32, and when I opened the file, I could see that only a line feed (0x0A) was used.
Little annoyances like this make me more appreciative of Integration Services (and its mess of little annoyances…).
Sometimes people will start a query, wait five seconds, and then declare that this must be a runaway query. Sometimes a query’s runtime can vary based on other things going on in the system, so four seconds on one run and six seconds on another is not necessarily “running forever.” Be sure that you’ve given a query adequate time to start returning results before giving up on it, and remember that Management Studio might seem “stuck” before it starts to render any grid results, especially if the resultset is large. If you feel you’ve waited a reasonable amount of time, and you’ve tried both Results to Grid and Results to Text, then…
This is a big question and can take years of experience to get correct. Aaron’s post is introductory-level on purpose and does a great job of answering the initial “what are some things I can try?” question after you determine that yes, there is a problem.
I quickly honed into the fact that the bad query was doing a lazy spool with 6 BILLION rows versus the 229 million in the good query. However, my friend who was looking at Management Studio was asking where I was getting those numbers. This is what the XML from the original plan showed:
<RelOp AvgRowSize=”51″ EstimateCPU=”0.00889634″ EstimateIO=”0.01″ EstimateRebinds=”0″ EstimateRewinds=”139581″ EstimatedExecutionMode=”Row” EstimateRows=”48868″
LogicalOp=”Lazy Spool” NodeId=”55″ Parallel=”true” PhysicalOp=”Table Spool” EstimatedTotalSubtreeCost=”1242.86″>
This is a helpful feature in scenarios like this, where operator weight is skewed because it only shows a single run but in reality happens more than once.
Yeah, that’s a DAX-powered, Power BI dashboard, right here in our website – a website that runs on WordPress, which is Linux for crying out loud. Don’t know what Linux is? No worries, just translate it as “there’s zero Microsoft software behind PowerPivotPro.com, and yet – BAM! Power BI, right here!”
And the dashboard in question is a near-real-time view of the traffic on this very site! Check back in an hour and you will be able to “see” yourself on the map (especially easy if you use one of the “rarer” browsers.)
Check out the technical walkthrough if you’re interested in doing something similar yourself.