Query folding doesn’t get a lot of press, but this unassuming functionality can dramatically improve Power BI performance. Visualizations or reports contain multiple queries that need to be processed and can tax your computational power. If your queries are complex and you’re running dashboards off a laptop, for example, things can bog down when query steps get handled locally instead of back at the data source.
Also called query delegation or predicate push-down, query folding is a way to maximize limited resources by generating a single statement to retrieve and transform source data. The goal of folding is to offload as much of the query heavy lifting and processing back to the source database. It helps you to best optimize what gets sent back to the data source and can also dramatically reduce what is imported into Power Query and processed locally. By understanding the steps of your query, you can make changes that will improve Power BI performance. The best part of query folding is it is built-in and ready for you to use!
See a demo on query folding. We go behind the scenes on how Power Query looks at your data. Along with an overview on query evaluation and folding, you’ll learn how to:
- Know what sources can achieve folding
- Determine when a query can be folded
- Understand the icons and indicators of what steps in your queries can be folded
- Leverage Query Plan to see what’s happening with your queries behind the scenes
Presenter
Pat Powers
Trainer and Consultant
Senturus, Inc.
Pat is one of our most popular instructors, regularly receiving high marks from students for their subject matter knowledge, clarity of communication and ability to infuse fun into classwork. Pat has over 20 years of experience in data science, business intelligence and data analytics and is fluent across multiple BI platforms. They are a Tableau Certified Associate and well versed in Power BI. An expert in Cognos, their product experience goes back to version 6. Pat has extensive experience in Actuate, Hyperion and Business Objects and certifications in Java, Python, C++, Microsoft SQL.
Read moreMachine transcript
Welcome to another Senturus webinar. Welcome to the final webinar of 2022. Oh! my goodness. And just some unrelated news, it will only be interesting to a few of you, this is our last GoToWebinar webinar. Starting in 2023, all of our webinars will be on Zoom. So Yeah, this is it. This is the last webinar of the year, the last GoToWebinar. Oh, my goodness gracious, but Penny, I know you’ll keep calm until all of them.
0:42
I see you out there, Penny, I see a lot of you that just come, because you want to hear me talk. I know how it is.
0:50
All right, everybody, today, we’re talking about query folding. We’re going to get our laundry on and a different kind of folding.
1:37
Click on the GoToWebinar arrow to see the whole control panel.
1:47
And here, you can submit questions. I’m paying attention to that question pane, the whole gosh, hack darn time we’re talking.
1:58
So please, send us questions. If for some reason we don’t get to your question, we will cover it, hopefully, at the end of the Q and A section, because maybe you’ll ask me a question, I don’t know, It actually happens.
2:15
If for some reason we don’t have time to get to it, or we can’t find the answer soon enough, we will have a written response document that is posted on Senturus.com.
2:25
And, of course, the next question that you’re all going to post, How do I get a copy of the presentation? Well, guess what?
2:32
We put that on our website. And if you pay attention to the chat window, look at that. I just gave you a link to it.
2:57
The other one will come in handy later, though you’ll be seeing that other URL later. So, click on the other URL and bookmark it just so you have it for later too.
3:08
The deck is available on Senturus.com, you want to go to the resources tab and then the Knowledge Center, or click on the link I just posted.
3:17
But the website, there’s all sorts of fun stuff.
3:21
A website, if you can’t tell, it is near the end of the year, I am just in a mood. I am just getting to that. We’re almost all to that last week of the year. Our agenda today, I will introduce myself.
3:48
We’re going to talk about query folding.
3:51
We’re going to talk about what can and cannot be folded.
3:56
How to tell if something has been folded using the indicators and the query plan and looking at all sorts of fun stuff. And of course, I’m going to demo all of this And then you’re going to stick around and you’re going to listen to me do all the marketing stuff where I go over the Senturus overview and additional resources.
4:15
Because after that, we’ll do the Q&A.
4:19
I’ve got to make, you listen to the other stuff, it’s part of the job, whether you like it or not.
4:41
So, who is this wonderful, weird person talking to you? Why it’s me, data scientists, consultant trainer.
4:49
My title is going to change in 2023, So you have to come to the next webinar to see my new title, as of 2023, because it’s almost here.
5:00
This will be my 26th year of data science, data warehousing, data analytics, whatever term digital you wish to call it.
5:10
My goodness, I’ve been doing it longer than some of you have been on this planet.
5:15
OK, Penny. I don’t want to be a VP, because then they’ll make me have to carry a budget, and then I’ll have to worry about money, and I don’t want to worry about money.
5:24
That’s no fun.
5:25
I just want to teach you all. I use Power BI, Tableau, Cognos, Hyperion. Actually, whatever two tool you can think of, in these last 20 something years, I’ve used them, I’m certified in Java, C++, SQL Server, etcetera, etcetera, That’s me.
6:23
We’re going to start with why.
6:25
Why are you here today? Why do you care about query folding? Why are you listening to me, ramble on.
6:35
It comes down to wanting to have better performance, wanting to have optimal queries, wanting to make happy users, know, those people that drive us nuts all the time, but that we have to keep around and make happy. So whether it’s when they load up reports, or when they open dashboards or whether they work with apps.
7:02
We want things to get on the screen faster.
7:06
We want things to show up better.
7:09
And what we’re going to talk about is how query folding gets you to that goal.
7:18
It is an important part of your data modeling.
7:23
Hey, it is an important part of things.
7:27
And especially when we look at our different types of table modes, whether they’re important versus direct versus hybrid, folding will improve the performance of these things.
7:41
Direct is one of those things.
7:44
Where, if you’re using direct queries, because you’ve got huge, huge data sources, anything you can do to fold, anything you can do to improve this is going to help now, before anybody asks.
8:00
Well, how much of an improvement might I get off of this?
8:04
irrelevant question?
8:06
Because it’s going to depend on so many different factors, it’s going to depend upon the size of the table, the complexity of the transformations.
8:17
There’s so many different things.
8:19
When we say there’s an improvement in performance, it’s relative to all those other things.
8:26
How big, how many, How much.
8:29
Any performance improvement is always welcome.
8:38
So if it’s five milliseconds faster, that’s still an improvement.
8:43
If it’s 30sec faster, that’s a direct correlation. And result that your users can see, and your users will be happier, which means you get your end of your bonus, which means you can go to Hawaii next year, and your spouse will stop getting on you about finally taking them away.
9:00
Hey, right, Right, Andrew.
9:04
You’re going to Hawaii next year.
9:09
Query folding is simply put is Power BI pushing as much back to the data source as possible versus processing this locally.
9:31
It’s the ability for Power Query to generate a single, simple query statement and push that statement back in native functions.
9:49
All right, pushing it back in native functions, that allows the source to do the processing.
9:59
Here’s the beauty.
10:01
All of this is built in.
10:03
There’s literally nothing you have to turn on. You don’t have to go check anything. You don’t have to start digging through menus. This is built-in functionality.
10:14
And it’s taking this built-in functionality.
10:17
It’s looking at the M that is generated by Power Query.
10:23
And it’s attempting to fold it.
10:26
And the folding occurs when we use native functions and things along those lines.
10:36
And this becomes important when you’re using Power Query to do your data transformations.
10:42
When you are using Power Query, two, remove columns, filter down rows, merge columns, our goal is to make sure that we’re using transformations.
11:00
That can get translated into native, sequel of our data source.
11:08
We’re talking about, how do I build my transformations more efficiently so that the query that gets created can be converted to a native script and handled by the data source versus being processed locally within Power Query?
11:34
There’s your summarization of what query folding is.
11:40
Just some quick definitions, just so we know what we’re talking about here.
11:46
Again, it’s the process of taking all these queries, taking all of these M scripts, pushing them back to the data source.
11:58
There are certain things that can and cannot happen in Power Query.
12:02
Any of you who’ve used Power Query, you’re aware that there are certain transformations that cannot be done, because you can’t affect a live table.
12:17
So there are certain things that you have to have import tables for, and import tables or tables that are loaded into memory.
12:26
And there are different transformations, different DAX functions, different M functions, that can be done against import tables versus direct query tables. Direct query tables are tables that are loaded from the source. Now, think about that for a second.
12:43
If there’s a transformation that I’m trying to do, that is not a native SQL function, it’s got to read that query, perform that function locally, return the results.
13:00
That’s overhead.
13:02
Folding helps get rid of that overhead.
13:06
Then we start thinking and talking about things like incremental refreshes, OK, incremental refreshes are efficient in terms of utilization, duration and things like that.
13:22
But when you do an incremental refresh, you’ll get a warning if it determines that something can’t be folded.
13:32
So you’re going to see all this, so don’t, don’t stress out, you’re going to see all this today.
13:36
But if you’re doing incremental refreshes and the incremental refresh doesn’t go back to the source data, then in some ways, the goal of an incremental refresh is defeated.
13:52
Because the mashup engine would then have to get all of the source roes figure out what filters and transformations then determine the incremental changes, etcetera, OK.
14:07
All right. There’s like 60 of you here. Like 45% of you are paying attention.
14:18
Hopefully, if there’s anything so far, I’ve said, that’s confusing. You. Let’s get those questions asked.
14:26
Let’s make sure we’re all doing swell.
14:31
I will not lose anybody. This is not necessarily an easy topic to understand.
14:38
It is a pretty technical topic.
14:40
So thank you to those of you who responded Penny and Danielle and Rosario. Thank you.
14:48
Now, when we fold, folding may occur for an entire set of steps.
14:58
So, Dennis’, excellent question, Dennis asked, if query folding can be applied to files, import it from SharePoint.
15:07
It’s a matter of the M script, Dennis, it’s a matter of what the M script is sending back.
15:15
If the M script is using certain functions, it will fold them.
15:21
And that’s what you’re going to see when we start looking at, how do you tell if something is folded or not.
15:27
So, there are things that are built into this whole world, there are certain functions that will absolutely, always fold.
15:36
There are other things that will never fold OK, Dennis.
15:43
So you’ll see, you’ll be able to tell, did this fold or not?
15:49
Is this a native function or not?
15:53
And what we might see is we might see that at query is partially folded or fully folded.
16:02
When it’s partially folded, then the engine has to compensate by doing the transformations itself.
16:12
So you’re striving to fold as much as possible. Now, what am I talking about? What, what is this query that I’m talking about?
16:24
Here’s what we’re talking about, the M queries that you would see inside of Power Query.
16:31
So, when we use Git data, and we load data in, we’re automatically using access data functions, all right?
16:42
And these are functions that you would see in the M that connect to our source in the native language. So, Dennis, to your question, there are data functions for SharePoint. There are data functions for Excel. There are data functions for SQL.
16:59
So those data functions, those get data, functions are in the native language, and they fault.
17:10
And basically, what you see here is a select statement.
17:17
SQL.database, that’s our function schema, that’s our Get Data function.
17:25
So, these are all native functions that are going to fold.
17:30
It’s the transformations that we do that we’ve got to watch out for it.
17:37
How do you know what data functions you’re going to use?
17:45
There is an entire list of them now, I already put that into the chat window. That was the first one that I pushed out there.
17:52
So, let me show you this too
17:56
Here’s the full list.
17:59
Access Data Functions and this was updated as of October 10th.
18:04
All right.
18:05
So you can see Adobe Analytics, Azure, Cubes, Exchange Folder Contacts, Oh, Data Feeds, Salesforce, and, hey, Dennis.
18:20
There you go, SharePoint.contents, SharePoint.files, SharePoint.tables.
18:30
So these functions, we’ll absolutely fold.
18:38
SQL Database, Sybase, Teradata, etcetera.
18:44
These are data functions. These are functions that are folding.
18:48
These are functions that are handled at the source.
18:53
All right, it’s anything after that that we’re concerned about. It’s anything after that that we want to pay attention to.
19:04
Because what we’re going to see after these data functions is 1 of 3 outputs.
19:12
We’re either going to see full folding. And that’s where every transformation we’ve done has been able to be translated back into a native query command. And everything is happening back at the data source.
19:27
This will use the minimal amount of the Power Query engine.
19:31
And this is our goal.
19:36
More realistically, we’re going to get partial query folding.
19:40
This is where a few of our transformations can get pushed back. Some, however, have to take place in the Power Query engine.
19:51
These are the ones we want to watch out for.
19:53
These are the ones we want to look at. And these are the ones where we want to ask ourselves.
19:59
Is there an alternate native command that I could use instead? And that’s what I’m going to show you in the demo today, is, hey, here’s how I normally would do this. Oh, look, this doesn’t unfold, What could I use instead?
20:20
The more that we can do of that, the better off we’re going to be.
20:35
Then, we have an no query folding situation.
20:39
If we’ve got a situation where nothing is folded?
20:49
If we’ve got a situation where nothing is folded, here’s where we want to start thinking. Maybe I should push this all back. Maybe I should be doing some of this amazing ETL.
21:01
Maybe I should be doing some of this somewhere else.
21:08
Maybe this is something that I need to do in a different tool.
21:13
Maybe I want to do this upstream if my, if my transformations are so complex that I’m getting no query falling ever, they cannot translate this at all to the native.
21:31
Now, this is usually because the transformations aren’t supported, or the connector isn’t supported.
21:39
So, Nelsen, to your question, snowflakes shouldn’t.
21:45
So, you’re talking snowflake, the technology, not necessarily snowflake, as in a database. Snowflake, I’m going to assume.
21:54
This could be a potentially, yeah, that’s what I figured.
21:56
So, this could be a potential situation where the connector doesn’t support fold it, OK, and off the top of my head, I don’t have an answer. I don’t know if a Snowflake connector will support folding or not.
22:14
But, that is exactly what we’re talking about here. Yeah.
22:20
You would find it on that page that I that I posted a second ago as terms of what data functions are supported.
22:30
OK, I believe that there is a list.
22:40
If I can find you, a list on the query folding stuff from MS where there is a list of connectors.
22:48
Yeah, I’m going to, So I’ll put that I’ll put a link to that in before we, before we finish up today, OK?
23:02
But that is exactly what I’m talking about when you get a no query folding, most of the cases it’s because the connector doesn’t support it.
23:11
All right, Now, there are some transformations that I’ll always fold.
23:16
When you’re in Power Query, these will always fall. We’ve got Paul Lynde in the Center square. I mean, we’ve got expansion to achieve joins, You know, the hard part about getting old?
23:28
Very few people get that joke anymore.
23:31
No, they don’t remember the Center Square.
23:35
I could say Whoopi Goldberg and the Center Square that at least covers a few more of you people.
23:43
Paul, in the Center Square, but thank you, Andrew. Somebody’s laughing at my bad jokes. This is the last one of the year gang.
23:53
This is where I can pull out my entire year’s worth of material.
23:57
No, it’s Thursday, I never quite get the hang of Thursdays.
24:03
Everybody knows that these will always fold.
24:08
These are things that are always safe to do in Power Query.
24:14
Getting rid of columns, renaming columns, non fuzzy merging, you should not have issues any relational data source.
24:24
They can have a single select statement, where we’re using things like, where’s group by? Join.
24:32
Any of these, So, filter uses a where clause grouping in summarizing uses a group by expanding columns uses and join, Non fuzzy uses a joy, a union all when we’re putting together foldable queries on the same source. Ah, what was that I just said?
24:51
Oh, hmm, ding!
24:56
If I am using the same source, and I’m doing things like a union all, if I’m doing things like simple logic in a custom column from the same source, like order date, I can use an order date function.
25:20
There’s a Power Query date.year.
25:23
For example, pivoted and pivot, as long as I’m using the same source for everything, these things will fold.
25:36
So what kind of data sources are we talking about?
25:41
Relational.
25:44
Oh, data feeds like SharePoint, Exchange, Active Directory.
25:50
Diesel fault.
25:51
These functions, these sources.
25:56
What doesn’t fold?
26:00
Flat files, CSVs, things like that blobs web sources if you’re using blobs to source your Power BI reports Yes.
26:20
I’d be a little worried if you’re using blobs or globs.
26:27
Flat files web sources. Hey, I can see how you might be using those.
26:32
Just need to be careful with them.
26:34
We need to see how much is getting folded, how much transformation we’re really doing. What’s the performance impact, etcetera, etcetera. See, here’s the thing. Again, I can’t give you, oh, it’s 42%, or it’s this, or that.
26:46
These are those types where you really want someone like us to help you, to work through it, to walk through, to do a true assessment, to help you figure out, Hey, how do we get better performance? Hey, what can we do at the source? You’ve got blobs.
27:02
OK.
27:06
Vivian just asked if there’s any code that takes an SQL files from SharePoint, but the data is from the same Oracle database.
27:14
I would wager that that should fold just fine Vivian, because here, we’re still using standard relational database with SharePoint, get data functions, functions that shouldn’t cause a problem with the folding engine.
27:31
However, here’s things that will prevent you from folding.
27:37
If I’m trying to do merged queries from different sources if I’m trying to do unions’ from different sources.
27:47
Or if I’m trying to do complex logic, like date, dot to text, date dot to text does not fold, because there’s no equivalent function in the source.
28:03
At the end of the day, what this is going to ask you to do this, this is going to ask you to really be familiar with the language of your source database.
28:15
For example, there are different functions in SQL versus Oracle, one of which I’m going to show you in the demo.
28:23
It doesn’t exist in SQL, but it does exist in Oracle.
28:27
So, knowing that helps you figure out when you go to do a transformation. So, for all these questions that are coming in, I want to be very clear.
28:36
Normally, the JIT data functions have no problems, It’s the transformations that we do on the data, and it’s the transformations that were questioning and saying: Should I be doing this upstream? Should I be putting index columns upstream in the source?
28:55
Should I have a better ETL process that’s loading this into a proper data warehouse, or data lake, and pulling it from there after it’s been cleansed in a more efficient tool?
29:08
Or is there a Native SQL function that I can use that will make things better?
29:15
All right, how do we tell?
29:22
OK, here’s another old people joke, Know when to fold them?
29:27
Because using Power Query Editor, we’re going to know what we’re going to see is when we look at the steps, when we look at the steps, if we see this.
29:45
Yeah, If we see this View native query, then that means this is being folded.
29:54
If this is grayed out, then the query cannot that step or a step before.
30:04
It cannot be folded, OK?
30:10
This does not show up for all connector type style, going back to nelsons question.
30:16
If you don’t see this, then it could be 1 of 2 things.
30:19
It could be that the connector doesn’t support this, or the query can be folded If I’m all the way up here at the top and I’m not seeing view Native query at my first step, then that’s probably an issue with the connector.
30:34
If I’m down here at renamed columns and I’m not seeing it, then one of my ladder steps is not being folded, and I would just keep working backwards until I see this option not grayed out.
30:51
Benjamin, to your question, would there ever be a situation where you wouldn’t want to fold?
31:00
I can’t think of one off the top of my head, because again this is built-in functionality.
31:04
This isn’t something you really have control over unless you intentionally chose functions that couldn’t be folded.
31:14
But uh I can’t really think of a situation where you wouldn’t want it to be faster where you wouldn’t want to use his native language as possible. The real question Benjamin, is how much of an impact is my transformation Causey?
31:30
Is this causing me a 32nd delay when somebody loads of a dashboard? Where is this causing me a 10 millisecond delay when somebody loads up a dashboard?
31:39
That’s what we’re talking about. That’s what we’re looking at.
31:44
Is this transformation simple enough that it can be done in Power BI, that I don’t have to spend hours trying to figure out if it could be translated to a native query?
31:56
That’s what we want to look at.
31:58
Now, there are query diagnostics.
32:03
And you can use the query diagnostics to see non SQL connectors.
32:09
All right, You aren’t going to see the same level of detail.
32:19
But you will at least be able to see if it’s folded.
32:23
Vivian.
32:25
Vivian says: I don’t have any control over the DW tables. Are there alternatives to adding an Index? That’s where you’re going to have to look up the language for, the data source you’re using.
32:36
And you’re going to have to look, is there a native function, And there might not be, if there’s no native function, we’re going to have to see what the impact is happening here in the Power Query Engine.
32:51
All right. We know when to fold them.
32:54
That’s right.
32:56
Why?
32:58
All right.
32:58
So again, if the option is grayed out, not all steps can be folded.
33:05
However, some can be folded and we’re going to use that option to see where the sequence stops.
33:15
Or, and we’ve got indicators.
33:20
Yeah, There are step diagnostic indicators.
33:28
These indicators will tell us if something is folding, not folding, might fold, opaque, unknown, the indicators are non sequential, the indicators are going to help us see what’s happening.
33:47
When we look at a step, there’ll be an indicator if it doesn’t unfold. If everything folds, we’re just going to see this first one at the top.
33:59
If something suddenly says, Nope, not full.
34:02
Then, we’re going to see this might be 10 steps down, which means that everything up to that step has folded.
34:12
The might fall, the opaque and the unknown are much less common.
34:18
These indicators are only likely to appear if you’re doing ODBC or data connections.
34:24
What it means is, hey, this might fold. I won’t be sure until runtime.
34:33
Opaque, it’s inconclusive for some reason.
34:37
Maybe there’s a true constant table. Maybe the connector isn’t supported.
34:42
Nelson, this is one you might see an Opaque or an unknown.
34:50
Here I might see an unknown if I’m coming against something that is a record, a List, a primitive.
34:58
These are the ones that are going to tell you, if you’ve got something that’s a non supported connector, the two primary ones that the majority of you are going to see folding, not fold it.
35:11
When we see these, we want to look at the query plan.
35:17
The query plan is going to show us, what steps are folding.
35:23
The query plan is going to show us what’s going on. And we’re going to look at those indicators. We’re going to look at the query plan that’s being generated. And we’re going to determine if there are steps that we can implement to change this.
35:38
What we’re going to see is this, and we’re going to see the actual sequel we’re going to see what is the SQL being generated.
35:48
Here is an example of where everything is folded.
35:54
Every single one of these is folded, and we’re go to view details. We would see the SQL that’s getting sent and we would see the native language, and we would see that this is the optimized translation of RM query.
36:13
So we’re going to see this.
36:19
And with that, let’s see it.
36:23
Let’s do it. Let’s do this altogether here.
36:27
OK, so what I’m going to do, Because I’m coming here to my power apps.
36:33
And I’m going to create.
36:37
A new Dataflow.
36:41
Create a brand new data flow.
36:46
I already have one that I was working on for this for this webinar. So no, I’m not going to use this one. I’m going to use a new Dataflow.
36:55
They’re taught by webinar Dataflow.
37:03
Look at all of these things I can connect to.
37:06
Somebody gave me a new and an aha, Pretend like this is exciting, no, you don’t want my feedback.
37:15
Somebody pretended like this is exciting now.
37:18
Nelson, Snowflake is listed, OK.
37:23
Snowflake is listed as a data source.
37:26
It’s not say it’s all necessarily going to fold.
37:32
But it’s out there, which gives me high hopes that the get data functions, we’ll fold.
37:39
It gives me high ups.
37:41
I’ve got, I’ve going to have high hopes.
37:44
Thank you, Rosario and Andrew, by the way, you each get a cookie.
37:50
Let’s go to SQL Server database.
38:06
You’re a friend of mine, Adventure Works.
38:10
We all love our adventure works.
38:15
I’m going to log in, I use my super secret password here.
38:48
And there’s all my tables.
38:51
My God, things actually worked, So I’m going to pick a few tables just to make things good. I’m just going to grab a couple of things here.
39:01
Header or details, salesperson’s, region.
39:08
Sounds good to me. Let’s go to transform data.
39:17
Here’s what I did.
39:19
Look, there’s that little indicator. Oh, I forgot to start that up.
39:29
Look, there’s my little indicator right there.
39:35
Here’s what I’m going to do.
39:37
I’m actually going to create a new blank query.
39:42
I’m going to write them myself, Copy, paste, Suck.
39:51
I’m sane, Go to this database, go to this schema.
39:57
Give me this table.
40:00
Only give me these columns, and filter the rows to where the total do let me zoom in on this, so you can see it better.
40:11
Where the total due is greater than one thousand.
40:15
And I want the bottom five.
40:20
So I’m doing a keep bottom rows.
40:24
And I’m saying, give me the bottom five rows.
40:29
As you can see, I should not have gone into art.
40:33
So I’m writing my own query here, basically, and don’t be an idiot, Actually change this to the right connection.
40:59
Thursday, at the end of the year, people give me a break. I?
41:07
So I just created this new query.
41:10
These are my bottom five rows. These are my bottom five order dates.
41:15
These are only where the total due is over a thousand dollars.
41:21
And look at that, this step right here.
41:28
Right there, not folding.
41:34
This means that the kept bottom rows step is done in the query engine and not at my source.
41:45
Everything else up to there is cool.
41:48
That first indicator is telling me, Hey, you’re good.
41:52
You’re good, everythings folding.
41:55
But when I get to this step, it’s not folding.
41:59
Now, before I go on any further, some of you may be saying, hey, I’ve never seen those indicators before. Or, hey, I’ve never seen that pop out before.
42:10
Option’s global options, turn these on.
42:17
These two right here.
42:18
Enable query folding indicators, Show script, instep call out.
42:25
That probably just answered some future questions right there. Hey, I’ve ever seen those, going to turn them on. That is the only thing you have to turn on when it comes to query folding.
42:36
The functionality is built it. See, that requires you to turn those two things on.
42:42
So that gives me this indicator.
42:44
And it gives me the script fly out.
42:47
So when I hover over each of these, I see what it’s actually doing.
42:54
I see exactly what it’s doing.
42:58
So that bottom row step, not folding. And if I right click it.
43:07
Oh, no, it is grayed out.
43:10
Oh darn, what do I do?
43:12
But if I go up one step, Oh, look, View Data, source query, not grayed out.
43:19
So I know that everything up to there, everything up to there is folded.
43:30
So, Rosario, you have to enable those indicators, OK?
43:38
You have to enable those indicators.
43:42
Options, global options.
43:46
Enable query folding indicators.
43:52
No, So the code, this is what I was expecting. The questions these indicators are in Power Query online.
44:04
The indicators show up in Power Query online, not in Power BI Desktop.
44:11
The functionality is still there.
44:13
The indicators and the view data source query is still there, the indicator. That’s why I went where I went, gang.
44:24
Yeah.
44:25
That’s why I went where I went.
44:27
So if I look at the query plan for this step.
44:33
So I right clicked, and I looked at the query plan.
44:37
Here’s where I see that last one is causing my problem.
44:41
Notice, there’s no view details on here.
44:44
I can see the details for my first two steps.
44:52
Oh, look, select From where.
44:58
That’s everything up to that kept the bottom rows.
45:05
The kept bottom rows is doing a full scan. And that full scan is happening in the Power Query mashup engine.
45:18
If this was something that was going to impact our performance, if this is something that was going to impact our performance, maybe instead we want to do something different.
45:35
So I’m going to come back in here, and I’m going to delete this step.
45:42
OK, I do let I select, I selected, I deleted my kept rows step. Now, my whole query is folded.
45:54
But I still only want to see the last five, OK?
45:59
So here’s what I’m going to do.
46:02
I’m going to take my Sales Order ID, and I’m going to sort it descended.
46:13
Oh, look at that.
46:16
I’ve got a sorted step.
46:18
Still fall then, Sort is a native command. No problem.
46:25
Now, what I can do?
46:28
I can take this You can take this right here, Keep top rows, and I’m going to say keep the top five rows.
46:47
Those are the same exact results I had when I did a capped bottom row.
46:53
Those are the exact same steps, our results.
47:00
But look at my query now.
47:04
OK, I’m now folding.
47:08
And when I look at this, and I look at that first off, oh, look, It’s not grayed out.
47:20
I’ve got an order buy, and I’ve got, on top, top is a Native SQL function.
47:30
Top goes back to the native source.
47:34
This is handled by my source, not via my query engine.
47:42
How nifty is that gang pretend like that is nifty? You all know how hard it is to make these things exciting, gosh, darn it, heck.
47:52
Thank you, Penny. Thank you, Vivian!
47:58
Here’s what I was talking about earlier.
48:01
If any of you are on Oracle, did you know that Oracle has a bottom function?
48:09
So if this was the Oracle instead of sequel, my bottom would have actually folded?
48:20
Ah, yeah, Oracle has a bottom function, SQL does not, SQL has a top function.
48:29
So when I change it to kept top Rows.
48:36
And now I say, show me the native query.
48:41
Show me the query plan.
48:48
Everything’s folded.
48:51
And it’s all being done in a single select statement.
48:57
I don’t have six different steps anymore.
49:01
I have to get data. Can it make a connection?
49:06
So here’s my make a connection step.
49:09
Here is my single select statement.
49:18
That’s what we’re trying to get to gain.
49:21
Native query, single select, pushing it all back to the source.
49:29
And all it meant all I had to do.
49:32
All I had to do in this case was replace the cap to bottom with a sort cap top, ta da.
49:48
How do you know which ones know your source?
49:51
Know what functions. It’s time to pull out your dust off your old SQL for smarties book.
50:00
Ed, For those of you who’ve never read SQL for smarties, Joe Saulteaux is the author of that.
50:08
That’s one of those great books that’s been around for decades, that’s the kind of thing where we’re talking about, you need to know, oh, my sources, Oracle, Oracle has a bottom function that’ll fold.
50:25
Remember, sometimes, there may not be a native equivalent.
50:31
That’s when we have to look at and go, what’s the performance impact?
50:37
Is this such a complex calculation, that it’s going to cost me 30sec when somebody loads this report up?
50:47
When somebody goes to access this dataset, is that 30sec going to give me an unhappy end user?
50:55
Should I do this calculation back in the source, in my ETL?
51:04
If I’m being perfectly honest and realistic in this dataset, given the size of this dataset, would that kept bottom rows impacted any of my end users? No, probably not.
51:20
It’s a millisecond difference.
51:24
But that’s this dataset.
51:26
And that’s this function.
51:32
So Patty, is that a native function?
51:37
If so, the answer should be yes. Penny asked if the or Oracle list ag function. As long as it’s a native function, it should fold.
51:47
Because again, let me ask. Let me ask the question this way. Patty, let me ask you a question.
51:53
Could I, in Toad write a select statement that uses the list ague function and get back the results?
52:04
Then it should fold.
52:10
That’s what we’re shooting for.
52:12
Could I put this in a select statement?
52:15
Yes.
52:16
It should fall with no problem.
52:20
If you can put in a select, we’re good.
52:26
All right, to know, they always are worried that I’m going to finish too early or too late. But look at this. It’s 252.
52:34
I know what I’m dealing with kids.
52:37
To summarize, it’s built-in functionality.
52:41
It helps us examine and understand and optimize our queries.
52:48
It’s going to give us overall performance improvements, and it should be used whenever possible.
52:53
Take a look at the query plan. Take a look at the indicators, take a look at these things.
52:59
Thank you, Patty. We’ll see you later.
53:02
You know, maybe in 2023, I’ll actually turn on my webcam.
53:06
We’ll see.
53:09
Leverage the indicators, leverage what’s going on, use it, and, as always, talk to us, talk to us.
53:26
We can help you reduce the total cost of ownership.
53:29
We can help you with the report sharing strategies. We can help make sure you’ve got the right tools.
53:36
We can help you get all this into an Azure data lake or anything else, heck. Heck, we can help with your fin ops in your reservations. That’s right. We can do it all. Gosh, darn it. And, where can you find all this stuff? You can provide hundreds of free resources on our website.
53:53
We have been committed to sharing our expertise for, ever, over a decade on the website.
54:00
OK, go to Senturus.com/resources, you’re going to find all sorts of product demos, upcoming events. Kitty, I am not ignoring your question. Let me get through this stuff and I will answer it.
54:14
Our upcoming events, for those of you who are still Cognos people: What’s new in Cognos? 11.2.4? I’ll be in January.
54:27
Then, January 26th.
54:31
I’m back at it. I’m going to be doing a dashboard comparison. I’m not doing the 19th. I’m doing the 26th. On the 26 by Comparing Dashboards.
54:43
We’ll have an info on our website next week for you to register.
54:52
If your mind is otherwise worried about the holidays coming up, hey, don’t worry about it, we’ll get you an e-mail.
54:59
I will e-mail you.
55:02
Andrew. Thank you very much.
55:04
It is a difficult topic to explain.
55:07
So hearing that you called it the best explanation really makes me happy. Thank you.
55:13
Hey, a little background on us. For those of you who are new to Senturus, we concentrate on BI modernizations and migrations across the entire stack.
55:21
We can do power BI, Cognos Tableaux, Python, Azure proprietary software for bimodal BI.
55:29
Migrations, we shine in hybrid environments.
55:33
If you are moving from one to another, if you’re trying to make decisions about what to do, this is what we’ve been doing.
55:40
We’ve been doing it for 21 years, 1300 plus clients, 3000 projects. Our team is big enough to meet all of your needs, yet, small enough to give you a personal attention. As you’ve seen today, I know each and every one of these folks I’ve been talking to, I’ve worked with them for years. Hey, that’s what we give you a Senturus, as this personalized attention.
56:04
We call you out in the webinars, because, gosh, Gali, it’s fun.
56:08
If you would like to be part of this Madcap team, we’re looking for a managing consultant and a Microsoft BI consultant.
56:16
Go to send Senturus.com why some Senturus careers, or Send you.
56:22
Send us your resume, jobs. It’s in Senturus.com.
56:26
Also, our fin ops consultant, we’ve got a cloud cost management practice.
56:35
Come be part of.our new FInOps team.
56:43
Also, take a look in the chat window again. Our managing director, Scott Felten, put in a nice little calendar link.
56:52
We can talk to you about query folding. We can talk to you about optimization. We can talk to you about performance. Look at that chat Window Kids.
57:01
OK, it’s right there. There’s a link.
57:03
Scott’s calendar is always open.. Scott has no life.
57:08
We’ve told Scott to get a life, doesn’t listen.
57:10
Q and A time.
57:12
I think there’s only one question I’m missing out on and that is yours Kitty.
57:17
Question on blob, we use Synapse to inject data as parquet files into a data lake. my gut instinct in my natural reaction KT is that is not going to default.
57:35
I don’t have 100% answer on that, but based on what I know of what can and can’t fold, I’m going to say that’s not going to fold.
57:44
So that may be something that you want to, we can help you with, that something that, but the question is, if it’s in a data lake, see OK now.
57:54
If it’s in a data lake and I can send a select statement to the data lake and I can use a standard native select statement yeah It’s going to depend on that connector.
58:11
It really is that is honest to God kitty. That’s the kind of thing that I would say hey let us walk through it with you.
58:17
Let us help you figure that out and see what the select statements generated.
58:24
OK, Kitty.
58:26
Because I’m really try to figure out if you could send it a select statement, it should fold.
58:35
Hey, if there’s anything else you want to know, send it to info@Senturus.com, we’ll set up a free consultation.
58:43
There we go. Merry Christmas, Kwanzaa CA, wherever you celebrate, whatever you want, happy holidays, Mary, whatever. Ladies and gentlemen, we are done.
58:56
Get the heck out of here.
58:59
Have a wonderful, remaining 2022. I will see you again in January 2023. Thank you all very, very much for giving me your time today.
59:11
I hope you all had fun. I hope you all enjoyed yourself.
59:16
As the last slide says, thank you.
59:22
Thank you all. Good. I’m glad that demo made sense to everybody.
