A Fine Slice Of SQL Server
Gilbert Quevauvilliers walks us through access control on Power BI pages:
With the update to the latest version in Power BI Desktop there is now the capability to control which users will see which pages in a report.
This is achieved using a combination of Row Level Security (RLS) and conditional formatting. This makes it a secure way of controlling access for specific users. I will demonstrate how this works below.
This is a clever solution to the problem of access control.
Comments closedChris Webb wants to make your joins in Power Query faster:
The first question I decided to investigate was this:
Does the number of columns in a table affect the performance of a merge?
First of all, I created two identical queries called First and Second that connected to the CSV file, used the first row from the file as the headers, and set the data types to all seven columns to Whole Number.
Click through for the answer to this question. Chris promises a series out of this and I would expect there to be enough content for that.
Comments closedBrett Powell gives a quick overview of a new whitepaper:
The Power BI Professional’s Guide to Azure Synapse Analytics, a white paper I wrote describing the Synapse Analytics platform and its benefits and use cases for Power BI professionals, was published a couple weeks ago. This post discusses a few themes from this paper and also shares a couple notes that weren’t included.
There are some interesting notes in here, so check those out and also get ahold of the whitepaper to understand how Power BI relates to the artist formerly known as Azure SQL Data Warehouse.
Comments closedI’m sure you know that PowerBI tabular models are 100% the same as SSAS tabular models, hence you can use SSMS to connect to a PowerBI Tabular model as if it was SSAS (because it is), to do whatever you want, for example scripting out the objects that you’ve created in the PowerBi Desktop GUI or extract the model to deploy it in SSAS.
The only issue to do so is that PowerBI Desktop each time you open a report creates the local SSAS Tabular instance on a random port, so it’s not like you can save a connection string and that’s it
But Emanuele has a quick Powershell script to find the port for you.
Comments closedJon Fletcher shows us how to sort a Power BI table by more than one column:
A common request that is raised by clients is how to sort a table in Power BI by multiple columns, in the same way you can in Excel.
For a while, there was no way (at least no easy way) to do this until the Power BI March 2020 update.I learnt this tip from the following YouTube video:
https://www.youtube.com/watch?v=ik0K1H9j2Uc
Full credit to Dhruvin Shah, check his video out.
I would call this feature moderately discoverable—once you see how to do it, you can say “Oh, that makes sense.” But it’s not something I would necessarily have thought to do without this prompting.
Comments closedAlice Drummond has two articles on improving the map experience in Power BI. First up is creating drill-down maps:
But why would we want to drill down on a map?
See the big picture: Using drill down, we can visualise the dataset at the ‘big picture’ scale, and then explore finer details for areas of interest (just think of the classic spatial hierarchy of Country à State à City à Suburb)
Save on real estate: We can also display multiple datasets at different levels in the same map – which goes a long way to conserving your precious report page real estate by reducing the number of visuals in your report!
Performance: Following on from above, less visuals almost always equates to better report performance! And this is especially true when using MapBox, which is at the slower end of the Power BI performance scale at the best of times – so using drill down to reduce the number of maps combined with filtering your datasets goes a long way to speeding up your reports!
The second post is all about styling choropleth maps:
If you’ve played around with MapBox in Power BI – you’ll know that it has loads of great features to create really rich and beautiful maps, including some great ‘out of the box’ map styles (i.e. base maps). However, you might not be aware that it also gives you the ability to design custom styles with your own spatial layers. I discovered this feature on a recent project where my client wanted to include Victoria’s Catchment Management Authority (or CMA) boundaries on their base-map to provide greater context to help interpret their data. Up until this point, the only option I knew of was to purchase an expensive ArcGIS Online licence to create custom map styles. So, you can imagine just how excited I was when I discovered that I could also do this in MapBox – for FREE!!!
Click through for the posts and videos.
Comments closedMarc Lelijveld shows how you can create custom tooltips per user in Power BI:
From an end user perspective, it is very user friendly to see that the report is adjusted to your personal preferences. Similar is the case for page navigation. If I am the country manager for Germany, I would prefer the report to be adjusted to my area.
With row level security you can filter the report down to only the specified area you want. But in this case, we want clarity over all countries. So, we do not want to filter the dataset itself, but only customize the end user interaction. For this setup, we will still use row level security, but in a different setup.
Click through to see how to do this.
Comments closedMelissa Coates has an updated whitepaper for us:
I’m really excited to announce that a new version of the Microsoft whitepaper “Planning a Power BI Enterprise Deployment” is now available.
This is version 3 of the whitepaper that I co-authored with Chris Webb. The previous version was from July 2018, so this update includes quite a lot of changes throughout.
Huge high-five to Meagan Longoria who was our tech reviewer again. She never fails to make my writing better.
That’s three very sharp people, so you can bet it’s going to be good.
Comments closedMarco Russo goes over the ramifications of a recent change to Power BI Desktop:
Starting from the May 2020 version of Power BI Desktop, regardless of the Windows locale settings DAX always uses standard separators by default. This change does not affect most Power BI users around the world; but if you use the comma as a decimal separator and 10,000 is just ten and not ten thousand, then you are affected by this change.
First of all, you can restore the previous behavior as I describe in this post, but the recommendation and the default are now to use the standard DAX separators. I want to describe why this (I think good) choice was made and how I contributed to making this happen.
Read the whole thing.
Comments closedThe customer is using Power BI to report on data from Service Now via APIs. So the customer was able to quickly connect Power BI to Service Now data and begin reporting on relevant datasets very quickly. The challenge, however, is that querying multiple years of data via the API was less than desirable for a variety of reasons.
The customer was interested in exploring the incremental refresh capabilities of Power BI, but were worried about using Power BI’s native incremental refresh capability since query folding (if you’re new to query folding, read this here) is not supported by Power BI’s web connector. So the customer team reached out to me with the challenge and I worked up an example that I think will help them meet their requirement.
Click through for the solution.
Comments closed