A Fine Slice Of SQL Server
Chris Webb has a performance tip for us:
You may have noticed that a new dataflows connector was announced in the August 2021 release of Power BI Desktop, and that it now supports query folding between a dataset and a dataflow – which you may be surprised to learn was not possible before. In this post I thought I’d take a look at how much of an improvement in performance this can make to dataset refresh performance.
Click through for the demonstration.
Comments closedImke Feldmann has an update to the Power BI Cleaner:
In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.
Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.
Read on to see how to set everything up, as well as the things you can do with it.
Comments closedMarco Russo announces a new set of tools:
Power BI Push Tools is a set of open-source tools to work with Power BI push datasets.
Read Implementing real-time updates in Power BI using push datasets instead of DirectQuery to learn the architecture of solutions based on push datasets and how to create and populate a push dataset starting from a regular Tabular model.
In addition to reading that linked post, click through to see the three tools which are currently available.
Comments closedMatt Allington wants to change a bunch of column names at once with Power BI:
This is not the first time I have shared this concept. In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some M code. The trick you need to learn to solve this problem is “how to create a list of lists”.
Click through for a video to see it in action.
Comments closedGilbert Quevauvilliers hits on an important factor:
One of the most important aspects when looking to migrate from AAS to PPU is what will costs be. This plays an important part in the decision.
In this blog post instead of doing an AAS and PPU comparison I am going to do this side by side, which I feel will make it easier to compare.
In my opinion it is only fair to compare the same sizing between AAS and PPU, and this can only be done by using the Memory allocation.
Click through for the analysis.
Comments closedTreb Gatte has an interesting use case for Power BI Goals:
OKRs are but one of three emergent usage scenarios for Power BI Goals. I see some companies using Goals for Metric Aggregation and some for Balanced Scorecards. This post focuses on OKRs as I see the scenario offering broader benefits to the entire organization. OKRs are critical to creating an Action-centric culture.
Read on to understand what an OKR is and how Power BI Goals can help with tracking them.
Comments closedChris Webb explains the cost side of the ledger when it comes to measure creation:
You probably know that it’s a best practice to build your Power BI datasets in a separate .pbix file from your reports – among other things it means that different people can develop the dataset and reports. You may also know that if you are building a report in Power BI Desktop with a Live connection to a published dataset or Azure Analysis Services you can define your own measures inside the report. While this is very convenient, if you create too many measures there’s a price to pay in terms of query performance.
Click through for a demonstration of this.
Comments closedMarc Lelijveld continues a series on taking Power BI to the enterprise level:
On to the next topic, which is super important for global, enterprise grade solutions if you ask me. Security and information protection! The fifth blog in the series of transforming local into global Power BI solutions.
Good to know up front is that information protection is a feature that not specifically related to Power BI, but also applies to other Microsoft products and services. Though, in this blog I will explain the functionality of information protection, also known as sensitivity labels and how this applies to Power BI. Let’s first look at what the functionality is, where you can apply it and why it is important.
Read on for more information concerning sensitivity labels and where they become useful.
Comments closedIn this blog post I am going to cover how to implement Row Level Security (RSL) when using AAS and how this can be done on PPU.
In my example below I am going to show creating to simple RLS roles which will limit data for the users who belong to 2 roles.
Despite the simplification, we can see how row-level security applies to both products and how the two differ.
Comments closedMarco Russo and Alberto Ferrari share their take on a classic debate:
A question that is often asked during the design of a Power BI data model is whether it is better to use an Integer or a Datetime column to link a fact table with the Date dimension. Historically, using Integers has always been a better choice in database design. However, Tabular is an in-memory columnar database, and its architecture is quite different from the relational databases we might be used to working with.
Indeed, in Tabular there are no technical differences between using a Datetime or an Integer to create a relationship. The database size, the query speed, and any other technical detail are absolutely identical. Therefore, the choice is not related to technical aspects, but rather on the convenience of the design. Depending on the specific requirements of your model, you might favor one data type against the other. In the most common scenarios, a Datetime proves to be better because it provides more possibilities to compute values on dates without having to rely on relationships. With that said, if your model uses Integers and you do not need to perform calculations on the dates represented in the table, then you can choose the most convenient data type – that is, the one already used in the original data source.
The remaining part of the article aims to prove the previous sentences, and to provide you with the technical details about how we tested the respective performance of the two options.
Click through for Marco and Alberto’s analysis, noting that “date” here does not include time of day, so it would have the same cardinality as the integer date key. This was a more important thing fifteen years ago, before columnstore technologies (like columnstore indexes and VertiPaq) were readily available and that 4-byte integer was considerably smaller than an 8-byte DATETIME.