Press "Enter" to skip to content

Author: Kevin Feasel

Dynamic What-Ifs in Power BI

Published 2023-02-13 by Kevin Feasel

Chris Webb breaks the limits:

My recent post on passing any value to a Power BI dynamic M parameter from a report generated quite a lot of interest but only among people who use DirectQuery mode and who want more flexibility when filtering. In this post, however, I’ll show you an application for this technique that is useful for anyone, regardless of whether they are using DirectQuery mode or Import mode, and which is nothing to do with filtering.

You’re probably familiar with Power BI’s what-if parameter feature which allows end users to pass values into calculations. It’s great but it has one big limitation: all the values that can be used need to be hard-coded in a calculated table at design time. What if (haha) you could let your users enter the values they wanted to use in the report instead? Let’s see a simple example of how to do this.

There’s a little bit of trickery but this solves a problem that I’ve had with What-If scenarios for a while: by asking the question, you naturally invite additional scenario ideas and people want to try them out right then and there.

Comments closed

Data Cleanup in Query Store

Published 2023-02-13 by Kevin Feasel

Grant Fritchey does some housekeeping:

The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:

Even with that limit, there are still several options available to you for when to clean up old Query Store data, whatever the definition of “old” (i.e., time-based or at the user’s whim).

Comments closed

Building Custom Lineage in Purview

Published 2023-02-13 by Kevin Feasel

Alex Crampton writes some Python code:

The aim of this blog is to explain how to create custom Purview processes, enabling you to add lineage from processes that are not tracked out of the box.

As covered in this blog, Azure Purview can help with understanding the lineage of your data, offering visibility of how and where data is moving within your data estate.

Lineage can only be tracked out of the box when using tools such as Data Factory, Power BI, and Azure Data Share. Lineage is lost when using other tools like Azure Functions, Databricks notebooks, or SQL stored procedures.

Read on to see the code, as well as what you can do.

Comments closed

Handling Balance Sheets in Power BI

Published 2023-02-13 by Kevin Feasel

Imke Feldmann performs a balancing act:

This article presents a method to automatically plug or balance your balance sheet in Power BI. You can use it if your bookkeeping system doesn’t contain the Profit and Loss (PnL) bookings yet. Or for planning or financial modelling purposes when your calculated balance sheet figures don’t add up.

Click through for the example, which pertains to including a current year profit/loss on the reporting breakdown.

Comments closed

A Primer on Stan

Published 2023-02-10 by Kevin Feasel

Jack Kennedy explains the concepts of Stan and JAGS:

You may have used a probabilistic programming language (PPL) in the past, such as BUGS, to perform Bayesian inference. You’ve heard about Stan and want to learn a little more. Or maybe you’re about to step into the Bayesian paradigm and don’t know where to start. You want to know whether you should make the switch from JAGS to Stan, or you’ve used neither of JAGS or Stan and want to know which will suit you best. This post will focus solely on the differences between JAGS and Stan as I have experience with both of them, but there are many more PPLs out there. For example, I have never used Bean Machine, but of all the PPLs, it certainly takes the crown for best name.

Stan has been on my to-learn list for a while and I did successfully get one of my employees (a rassa-frassin’ frequentist) to use and enjoy the power of Bayesian analysis. One of these days, I’ll have to get back to it.

Comments closed

Automated Data Visualization in Python

Published 2023-02-10 by Kevin Feasel

Brendan Tierney saves some time:

Creating data visualizations in Python can be a challenge. For some it an be easy, but for most (and particularly new people to the language) they always have to search for the commands in the documentation or using some search engine. Over the past few years we have seem more and more libraries coming available to assist with many of the routine and tedious steps in most data science and machine learning projects. I’ve written previously about some data profiling libraries in Python. These are good up to a point, but additional work/code is needed to explore the data to suit your needs. One of these Python libraries, designed to make your initial work on a new data set easier is called AutoViz. It’s good to see there is continued development work on this library, which can be really help for creating initial sets of charts for all the variables in your data set, plus it has some additional features which help to make it very useful and cuts down on some of the additional code you might need to write.

This looks like it’s worth a try and could serve well as a first-glance approach to exploratory data analysis.

Comments closed

Disabling Public Network Access in Synapse

Published 2023-02-10 by Kevin Feasel

Ryan Adams builds a private endpoint:

If you disable public access to your Azure Synapse Workspace you will get the following error message when attempting to open Synapse Studio. 

“Failed to load one or more resources due to forbidden issue, error code 403.” 

Click through for more information about routing for Synapse resources and what you’d need to do in order to disable public network access entirely.

Comments closed

So You Have a Programming Question

Published 2023-02-10 by Kevin Feasel

Louis Davidson takes us through the process of asking a good question:

Over the past 25 years, I have answered a lot of programming questions in online forums, from co-workers, and from friends. It has been a while since I had been around forums, but I recently decided it was time to get back to what started me in the SQL community: answering questions. Not only is it complementary to my current job as Simple-Talk editor, it is really a great joy to be able to help other people with their problems. It is also educational to see the kinds of things other people are going through before you also go through them.

Sometimes you know the answer right away, but sometimes the question is a challenge (these are actually my favorite.) At times I have spent hours on a question, trying to figure out a solution that works, and then writing up the answer to post back. Along the way I am learning something new and (assuming I figured out a correct answer,) the question poster usually learns something too. I mean, I have been wrong a few times, at least.

It’s been a decade since I’ve haunted Q&A forums but yeah, answering questions is a good way to learn more. Still, it can be really frustrating for everybody when you get (or ask) a vague question: there’s a lot of opportunity for confusion, non-viable answers, or even people ignoring the question completely because they can’t make sense of it.

Comments closed

Power BI Incremental Refresh with Non-Standard Dates in Parquet Files

Published 2023-02-10 by Kevin Feasel

Shabnam Watson hits on a specific but interesting use case:

The most common scenario for setting up the out of the box incremental refresh in Power BI is to base it off of a datetime column; however, there are cases when you may want to set up incremental refresh based off of a column with a data type other than datetime. Examples are when you are working with a smart date ID (01012023 for Jan 1,2023) column or when you are working with a source system that has partitioned data using a column such as Year that has a numeric data type.

A use case for the latter scenario is when you are working with Parquet/Delta files via Azure Synapse Analytics Serverless SQL Pool. When working with larger datasets, it is typical to see the Parquet/Delta files partitioned by date ranges. Depending on how much data there is, the partitioning may be at the Year level instead of Day.

With that scenario in mind, read on to learn how you can minimize your Power BI processing time and costs when doing incremental refresh.

Comments closed