A Fine Slice Of SQL Server
Chris Webb lays out the limits:
A few days ago I was tagged on a thread on Twitter: my old pal Mim was upset to find that he couldn’t use a SQL query with a Common Table Expression as the source of a table in DirectQuery mode. He worked out why without my help but I thought that it was worth me writing up an explanation for other people who might be struggling with the same problem.
That’s a fairly frustrating limitation, even if you have control of the database you’re querying.
Comments closedJoe Billingham shows off two similar-sounding functions:
I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.
The customer needed a Power BI report showing both old and new logging data sets as a single source. If both databases were SQL Server databases, I could have written a view with a cross-database join, imported that into Power BI and thought no more about it. However, the two sources being Azure SQL Databases, with no easy way to join the tables, caused an issue.
This is where the Merge and Append functions in Power Query come in.
Read on for the solution.
Comments closedGilbert Quevauvilliers puts a feature’s face on a milk carton:
I am sure everyone can agree that the new formatting pane is an awesome change.
But at the same time, I have found it a challenge to find settings with the new format pane.
In this blog post I will show you to find the conditional formatting which appears to have gone missing in the new format pane?
Click through to find out.
Comments closedMarco Russo and Alberto Ferrari are making a list and checking it twice:
The IN operator in DAX is useful in multiple scenarios to check whether an expression belongs to a list of values. It is oftentimes used along with the anonymous table constructors. IN is syntax sugar for the CONTAINSROW function. Just like CONTAINSROW, IN can be used with multiple columns at once although that syntax is not so common.
Click through to see how you can use IN in your work.
Kristyna Hughes puts Power BI to the test:
For example, one instance may have a very large data model that takes a lot of memory and CPU time to refresh, 20 users at peak viewing times, hourly refreshes, and the queries are all very simple and allow for query folding. Another business may have six smaller data models, 950 users at peak viewing times, daily refreshes, and the queries populating the data model are all very very complex. All of these elements impact the usage at any given time, making predicting overall CPU needs nearly impossible. Thankfully, stress testing your capacity gives us an option that is not purely reactionary.
This blog will walk through how to stress test your capacity, the elements of capacity planning, and how to understand the results of the stress test.
Read on to see how, using a step-by-step guide.
Comments closedIn Kusto (aka Azure Data Explorer aka ADX) you can have columns in a table that contain JSON structures.
In KQL it is very easy to extract elements from these columns and use them as regular columns.
It requires more resources but overall, it is standard.
An example can be found in the table TransformedMetrics in the SampleMetrics databases in the help cluster.
Click through for that process.
Comments closedGilbert Quevauvilliers perform a sleight of hand:
What I figured out was to then have an extra 4 options to filter on.
The reason that this came about is because I was using at integer field which does not allow for searching as shown below.
Click through to see how Gilbert was able to get the Advanced filtering drop-down option to include more than two OR clauses.
Comments closedMarc Lelijveld backs that thing up:
Basically for every data model, but in particular for large and enterprise-grade data models, it might be wishful to perform backups. Power BI Premium offers a way to perform backup and restore operations for Power BI datasets. This feature, coming from Azure Analysis Services, helps you to take a backup of your dataset logic as well as the data itself and the ability to restore in case your dataset gets corrupted or backend processes have failed. Furthermore, this backup and restore process is also a way to migrate your datasets from Analysis Services to Power BI Premium.
In this blog, I describe what is needed for Power BI backup operations, how you can perform them and how to script / automate them.
Read on for the process.
Comments closedChris Webb uses dynamic M parameters:
My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.
Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.
Leaving aside thoughts on table-valued functions in general, dynamic M parameters looks like a really nice feature and as Chris notes, it also works for things like stored procedures.
Comments closedGogula Aryalingam takes us through Power BI goals:
The feature is currently in preview, introduced some 8 months ago, and has quite a lot of promise. For me, it is particularly exciting since I am working with a large customer, who is a perfect candidate to implement goals for. So, what is Goals in Power BI?
Let us take a quick scenario first: Organizations, regularly (if not frequently) monitor indicators of their business performance to ensure their goals and aspirations are met. Sometimes these aspirations are difficult to keep track of due to various complexities. Consider a goal called Reduce employee turnover and increase satisfaction (something that I picked up from here). To effectively understand and track its progress, the organization would probably have a few key performance indicators (KPIs) that make it easy to look at reducing employee turnover and increasing satisfaction objectively. One such KPI could be a low human capital Turnover Rate while another could be a high Employee Satisfaction Indicator. Collectively these KPIs will help determine the achievement of the goal within a stipulated period (such as a calendar year). Similarly an organization will have many goals that are aligned to organizational KPIs or metrics. Sometimes, certain KPIs/metrics may cascade down the organization’s departments, where each department’s performance determine the overall organizational performance.
Read on to see how Goals work and one use case involving KPIs.
Comments closed