A Fine Slice Of SQL Server
Gilbert Quevauvilliers troubleshoots an error:
What I did learn when working through the blog post is that I ran into some errors when trying to re-connect or trying to connect to the Azure Storage in my Premium App Workspace and it failed.
The errors that I got were, “We couldn’t connect to Azure, but it’s likely temporary. Try again later or see details.”
Read on for the cause and the solution.
Comments closedChris Webb explains that hiding a column isn’t the same thing as preventing access to a column:
In the last few months the following issue has been escalated up to the Power BI CAT team several times: customers have deployed reports into production and then found that users are able to see data they should not be allowed to see by using the “Show data point as a table” feature. The question is: is this a security hole? It isn’t, and in this blog post I’ll explain why and how you should think about security as something that happens on the dataset and not in the report.
My official response is “Hmm…” I don’t disagree with Chris, but I do understand how people might not know this and get blindsided because they think they’ve prevented someone from seeing a sensitive column. I think part of my reaction is that this functionality isn’t blaringly obvious to report developers, and so there’s a little bit of “How could you know this could happen?”
Comments closedSoheil Bakhshi has (sometimes) three header rows for the price of one:
Easy!
Hmm… Not really. Especially when we have multiple other Excel files; some have two, some have three rows of comments, and some have even more. So not only is the task time-consuming, but it also is pretty boring and prone to human errors. What if there is a custom function that I can invoke to get the job done?
Click through for one method, followed by the proper method.
Comments closedGilbert Quevauvilliers wants you to back that thing up:
Continuing with my series of using Power BI Premium Per User (PPU), today I am going to show you how to back up your PPU database.
As far as I am aware all the options below will work for Power BI Premium as well.
To me this is critical when my dataset size grows. Especially when it takes multiple days to process all the data into the required partitions.
Not only is having a backup best practice, if something must go wrong with a deployment (let’s say I wipe out the partitions by mistake) it will be quick and easy to restore from a backup.
Read the whole thing.
Comments closedPrathy Kamasani wants to track slicer counts:
In one of the projects, I was working on, I received feedback saying it is hard to understand how many items they have selected in a slicer, and it is not the first time I came across this. It is a valid point, especially when you have quite a few items in a slicer, you use a search bar to look for items, you select a couple, but you were not sure how many were selected.
Read on for a rather clever solution to the problem.
Comments closedChris Webb troubleshoots a problem:
If you’re working in Power BI Desktop you may sometimes find that your visuals error with the message “Couldn’t load the data for this visual. The operation was cancelled because of locking conflicts”:
Read on to see what causes this problem and what you can do to avoid it.
Comments closedMarc Lelijveld wants to refresh a single table:
Back in February, I wrote a blog on how you can trigger a single table to refresh in your Power BI data model. This blog described how you can achieve this goal using a PowerShell script and the ASCmd cmdlets for Analysis Services, which also works for Power BI Premium. In the wrap-up of that blog, I promised to follow-up with a blog on how to achieve the same goal with Azure Data Factory. It took a little bit longer than expected to finalize this post, but here it is!
In this blog, co-authored by my colleague Paulien van Eijk, we will describe how you can automate your single table refresh in the Power BI Service, including all dependencies with downstream dataflows using Azure Data Factory and Azure Automation. All this is based on real life scenarios and a solution build in collaboration between Dave Ruijter, Paulien and me.
Read on for Marc and Paulien’s solution.
Comments closedEd Hansberry works around a Power BI limitation:
It is easy to add an animated GIF to your Power BI Reports. However, if you just add it as an image, it won’t animate. You’ll just get a static image.
Animated GIFs can be useful in reports to explain new features to users, or on hidden developer pages showing how certain things were done.
Click through for instructions on how to include an animated GIF on your Power BI report. Just make sure to pronounce it the right way.
Comments closedMarco Russo and Alberto Ferrari don’t have time to wait:
How many times have you heard an executive request a panel with the company’s sales data in real time? How frequently has this single request – which is more often a preference than an important business requirement – affected the overall architecture of your analytical solution?
In the Power BI world, requirements for real time often drive the creation of a pure DirectQuery model, with no aggregations to avoid data latency. This choice is incredibly expensive: the computational cost of each individual query is borne by the data source, which is often a relational database like SQL Server. On top of its cost, with this approach you will face scalability, performance, and modeling issues. Indeed, the relational database on top of which DirectQuery runs is mostly designed for transactional processing instead of being optimized for the workload of analytical processes. Optimizing the model is both difficult and expensive. Finally, using DirectQuery creates specific modeling constraints and the need for modeling workarounds to obtain good performance.
Creating an entire model using DirectQuery for the sole purpose of achieving a few real-time dashboards is definitely excessive. The primary scenario where relying on DirectQuery makes sense is when it is not feasible to import data quickly enough to satisfy the latency requirements for the majority of the reports. When the entire model can be in import mode, and a small number of dashboards require DirectQuery, there are better options available.
Definitely worth the read.
Comments closedEric Svensen has a quick one for us today:
When you build a date table in Power Query you might use the functions under Date to add year, month and day
And this will give you three steps in your Query
But we can do this a bit faster, and you will save a few clicks with your mouse
Click through to see how.
Comments closed