What's the goal of this session?
At what I would like to do is I want to together with you. I want to catch some God Josh and Gotchas are things that I learned during my my path of learning decks and if you get to know this gotchas it might speed up your.
Yes.
Um secondly, I also want to create awareness of the immense power of the engine that's behind decks becaus today. What I do. I give a demo on my on my own laptop and I'm working with lots of data? Which you will see.
And finally and at last, but not least. I want to just to have fun together, we have fun together. Just experimenting with decks because when I was preparing this session, it. I took it kind of like in the experiment and so I've been really like, Yeah Experiementing, doing things with decks and trying to
To the limit and of course, if you push things to the limit things get fun. Things get strange and crazy and that's all. What II like to share with you today.
So my agenda is quite simple I will start showing like in uh an overview of the lab environment that I used during this demo so you get an idea of how I set things up from a higher level and then during the session. I'm going to show you the particular.
And while we going through this all bits and pieces. I will point out all those different gotchas and quirks about dex. Finally, I will do a summarize of my session.
Um my session is also in the theme of this conference. You know it's Speakeasy. The time when the bootlegging and people were secretly drinking and I also during this demo, I will use a data set that reflects this.
Hum.
And I can show you here on the right hand side so.
What do we have we have consumption so at that time people were drinking a lot of cops?
Most of the cops were of course, coffee and tea, but some of them may be contain something else who knows.
Yeah, so we just have a few tables here.
The left environments.
As you can see here, the what I've done is. I actually have loaded 2 power. BI desktop files. So on the left hand side you see the random dot PBIX and on the right hand side you see the gotcha happened PBX.
What I do in the random dot PBIX? Is this is where I generate all the data so I generate a large data set an I'm doing that of course, also using decks, 'cause Index. You can also generate data as well in memory and also really fast, so I will then use the generates.
And and the random functions to get like a nice data set that has variations.
And then on the bottom you see the consumption table and the consumption table uses the people from New York, but then is going to add to blow. It even up so we get like a really large stable and is even so, large it has it will have 1,000,000,000 rows.
So yeah, we saw already I think that the keynotes yesterday. We talked about half a billion rows in the cloud. But this is 1,000,000,000 rows running on my laptop that I bought like 3 years ago and that's what I want yeah. I want to emphasize the power of this engine is immense.
And yeah that's 1 of the takeaways from this session.
And then I'm going to copy the data from the random to the gotcha. PBX I will tell you later. Why I'm doing that because of course I have a reason why I'm doing this, once the data is copied and I do the same for the New York people and maybe you guys.
Abstract but then II was explaining about the filter in the row and it's like I made like a paradox statement. I will then I write a query that shows that paradox and then I'm pulling the data also also then I have a copy and then I create also other calculated table so there.
Based on New York people and the consumption and 11 table. The monthly consumption. It will be a summary table.
Think about like the like an aggregation table, but not the aggregations feature from probably a premium. This is just writing a calculated table and and I also have another table Speakeasy. People active date, which well I will also explain to you later. Why why I've done this.
This is kind of the environment, let's continue.
There's any question or if you just raise your hands and just let me know. I just I like to make this an interactive session, so feel free if you have any any questions.
So I am now, I'm going to switch to the other PBIX.
And this is the the the random PBX.
So what I looked at what I did is I looked at Wikipedia and then I went back to the year 1920 and looked up? How many people were living in the city of New York.
So we can see here about 5 and a half million people are living in the city of New York.
And then I also found another Speakeasy fact and speaking like this. There were different types of speakeasies in most major cities that vary in size quality and the types of people that visit them and then the Speakeasy creates an environment where like the gangsters and the wealthy.
People were all part of this group of people so that that's also why I will reflect in my data set so how can I create a data set based on these kind of fix so then.
I will go into.
The table so.
First, we see here I'm I'm using generate series. The function and this is the amount of people from New York? What you just saw from the 1920.
Then I will generate a whole set of random numbers and those random numbers. I use later to add generate random data set.
Then, if we go down.
And for me, it is I would like to highlight some bits and pieces from this piece of code because it might be quite overwhelming to see from the start like a big piece of decks code, but later we will also see like a smaller pieces, so for now, I just want to highlight a few things.
Another thing I want to highlight is I'm using here at columns and you have selected columns.
And.
For those 2 functions, they both create a row context.
And they they behave both the same but there is one difference with add columns. You have a table and you just add the extra column with select tables son select columns select columns. Then you would just select of course, the particular columns from the table that's the only difference between those.
Then, if we go down, we can generate random numbers. But we can even also generate text as well because there's an unique. Char function index and then you can provide the number and then the number will return back a letter.
Feed function here will then repeat this. This letter and I got this piece of code from Phil see mark. He also likes to have fun with decks. I even created games index. So I would really recommend. He did like tic tac toe and hang man just by creating decks.
We want to have fun II definitely recommend looking at the at this stuff as well.
And if you go further down is that I just used the figures like the the distribution of the white the the white people and black people in Asian people and then.
Then I will and this will return the New York people data set.
OK, now the first thing I want to point out, we have an index. We have variables and variables is actually may be a bit of a misnomer because I've actually it are constants index and the reason why that is, is that once you assign a value to a variable in the.
She cannot change it anymore and if you cannot change anymore. You could probably better. Just see this as a constant value. Decks variables have 2 advantages advantages that it improves the readability of your code and I think that's very important because of?
This is not the the easiest language and then by making the codes as readable as possible.
Is very important for your maintainability of your your code so this is a very important one and the second. One it can also improve performance because if you if you assign the the if you do a calculation and you store it in the variable then of course, then you can reuse the same very multiple times. Instead of doing the same calculation multiple times, so that's
Vantage of the variables.
Now I'm going to show you an example of the consumption table. So we have now created. This New York. People table and now we want to create the consumption table.
This example, you don't need to understand everything, he did, just this example is showing you that that we're not using variables, so this doesn't use any variables and as you can see here you see the nesting of those those functions. You select columns cross joint select columns.
See you next the more difficult it gets to understand what's actually going on here and then it doesn't improve your readability and yeah, so that's why that's why I like variables, so now. I'm going to show you a version of variables and as you can see here this piece of code.
24 lines if I go to the version here.
With variables, we even after 37 lines. We have more lines of code. But I would still say that this piece of code is a better piece of code and the reason? Is is that you can see my thought process and building up this table.
You can also compare it in SQL with common table expressions. If you have common have a common table expression. You can do step-by-step. You can write your code and it makes it readable you can something similar here.
N.
The key like keep on here is that the cross join 'cause That's what I'm going to do here. You can see it's a cross join of New York people and dates so.
As I told you we have more than 5,000,000 new people. New York and then I'm picking 180 days and that makes more than a billion rows.
Um.
So, just to go step by step again. The piece of code that eventually you so we, we do select columns to select the data from the New York. People table then I'm picking a start date of the 1st of January 1920, then I'm picking 180 days.
And here I can highlight another piece of thing of decks is that index date is basically a number so if you have a date. You can add an integer and then it will just add those days so if I'm doing plus. One 80 it will add 180 days.
And this all those things that yeah, you need to when you're learning. The X is all the different things. And that's why I might make can make it hard, but once you get all those gotchas. It's is a very nice language to work with.
Um.
So then then we have, I'm generating the dates start and end date and then finally we, we have the we have a cartesian product?
And then we select the columns that we need, and one column that we will use later is the partition bucket. So, just put in on your mind, but I will use this one later in my story.
Alright so then once I've done this you can also see here and I'm not doing this on stage because of course. This take some time and I don't want you to wait but just to show you that.
The consumption table here.
Oh.
Can see the other 101,000,000,000 believe me?
But then when I was doing this, then II found something experience, something very annoying annoying thing is that when you use a random function and because we having so much data that we randomly generated so if even if I will now create a new measure in this model somehow it.
Calculation and if it triggers that recalculation have such big table. Then it will just going to recalculate all those billion rows again.
And then it spins and it weights and gets unresponsive and especially when you do a demo. Yeah, you don't want to have this so then you get that working on it, and then you're you're lost so then I was trying to think, OK? How can I? How can I solve this problem and then at the way how I can solve this problem? Why not use?
Target desktop as a data source.
So that's what I've done so. This is now also remember from the beginning. We had on the left hand side random dot PBS. This is the regular PBX. I'm going to copy the data to the other part PBX.
The first so now I'm going back here to the The Gotchas PBX.
And then we're going to the next the paradox statement.
And this, I think is a good example. Why Dexis Yeah, different filter creates a row context.
And when you're in a row context your eyes, it in the draw contact sold at that point. There's no filter context. So you have an function filter with when it's actually when it's going through the Rose. It doesn't ever feel the context. Then you also have a row function.
Which does not create a row context and if there's no row context? There's filter contact because there's always a context index, then so the ro function creates a filter context.
And this of course, is not intuitive and that makes it hard to learn a language like decks.
To give you an example of this.
Is and I will show you this in?
Um.
Index studio.
Who knows the deck studio probably have absolutely? No need to, I can just go straight through so I'm I'm connecting here to the the random model.
So what I've done here is to show you what I just explained with the filter and locals. I made an example of this so here you see that I have quite a variable and I call it the Great Gatsby Social Security numbers. So there's the Great Gatsby's Social Security number and what I'm going to do is that?
Throughout this guy of this number. This is the same number, you also see the power of those variables and declaring it above then I'm going to filter out. This number from the data set and the second bit is that I'm creating a new role, which is the Great Gaspee's on assigning at the same Social Security number.
And then I'm providing all the necessary columns and then finally I will do in a union.
And if I would run this piece of code.
Then we can, we can also see the proof that there is no there is no current row 4 column.
And the reason the piece of code that is breaking. It did this, this PC. I added here. A reference to the other column, but that to the other column in this role. But I cannot access. This There's no road context. So this proves that the role function does not have a row context.
Um.
Well, how can I fix this if I if I would comment out this line.
And then I can also now I only want to select the Great Gaspee Row. So here I have a union if I comment out the union.
So this is again the same as common table expressions. You can also in common table. Expression is SQL. You can also select only the the step that you need so here I'm selecting the Great Gaspee Row.
And if I now execute this, then you can see it returns the row and it's it's working.
So this piece of code is what I used to copy the data as you should copy the New York people from the random PBIX to the gotcha. PBX and then you can also see another thing they can use dex as a query language.
To show you where this is happening, so I'm going here through the?
The The Gotchas PBX.
And then if I go to edit queries.
And I go to the New York people.
Then you see here.
That I'm executing just the same query.
And then and then it copies the the data to the other side and the New York. People are just a few million rows. That's OK. So then even if I just run. This piece of code within evaluate and the query that we saw it will transfer the data to the other side.
But now we have another guy to transfer and that's the consumption table and the consumption table has 1,000,000,000 rows.
So when I was experimenting with this, I just thought OK? How do I select all the Rose from those from a table index? We have evaluate so I would just do and evaluate consumption.
So from here, I can I can pick and Ella Services as a data source?
And then it asked me for a server name.
Um because actually as we know the engine within power desktop is just analysis services. The same technology. So I can also use analysis as a source in order to connect to my local power. BI desktop so it asked me for a service server name in the server name is localhost.
But then I also need a port number and this is nice about deck studio deck studio.
Provide me also the port number that's my power be a desktop is running so in this case is 53501.
And then we also need to select the database and also for knowing which database there is I can also use deck studio because the studio has.
An option, the DMV's the dynamic management views.
And what I can do, I can click here and then just the top one. The catalogs if I just drag and Drop it here. It says select star from the catalog and if I run this piece of code. It gives me here, the the catalog name.
So I'm going to.
Face it in here, then I'm going to evaluate.
Consumption.
And now I'm pressing OK.
As you can see it's
Start to spin there's lots of data we now selecting one the table with 1,000,000,000 rows.
Exciting at this, I had lots of fun when I was doing this.
So as you can see here.
It is.
Having.
Lots of CPU and lots of memory.
And now you must try to imagine what is actually now happening because as you might know the tabular after the Ducks engine? Is cold storage data column by column.
What we're now going to ask give me all the rows?
And in order to give all those rows, it needs to do a lot of computation. That's also why they're not happening. The CPU is going like Sky High because it needs to do all those calculations. All the transformation to the Rose and yeah that's it's hard work.
And it's actually search hard work. I don't have enough time, but it will die. This will die. This will not succeed at some point what will happen is it will raise an out of memory exception. Because yeah, it's it cannot handle it anymore. The memory is going up and it will.
In vocal laughs.
So what I'm now going to do, I'm I'm going to cancel.
So this does not work.
But I want to get those 1,000,000,000 rows to the other side, so how how can I do that and then you need to think about what? What is the decks anywhere is good that is good? Is at specific things. If you say me. Give me everything from the table then? It's not optimized for them. If you just tell I want a particular piece, then it's good at that.
So well, I showed you earlier I created and partition bucket and what I did is I just put from 0229, 199, so that all the workers and then and then I'm just going to say. Give me one. Give me to give me free give me for.
And death makes the decks engine happy.
And the reason the way how I've done this is I created a function.
And what I will.
Do is I will duplicate this function.
And I don't want to pay too much attention to the M language is a beautiful language. But this this session is about that. But what I'm going to do is I need to change, now to the again to the same port number, So what I've done earlier 53101, then I'm picking again.
This catalog name.
Ann.
Yeah, so and and then I also copy this. I also copied this query and then if I would enfoque this function. Then it will say. Give me one. Give me 2. I'm running a bit out of time. So I will not run the moment but believe me when you do that, so you want to free.
Yeah, then it's you can see the memory stays stable and then the data is flowing but then what I also experience experience during my experiment is that if you let it run follow because it will take a few hours at least and I think after 2 or 3 hours. It was it was still breaking so.
So then I actually also created and a small program that is clearing the cache of the analysis is every every 10 seconds. I'm telling clear cache clear Cache Creek, so that combination so the combination of clearing the cache and saying. Give me one. Give me to give me free that managed me to transfer all the data.
And the good news is that once I transferred to the other side. I don't have the random function. I don't have the recalculation and I can start playing with the billions of rows.
I'm going to delete this one again.
Alright So what what we've just wanted to show you is the first time I was doing evaluate 1,000,000,000 rows that it was just dying.
Then.
Then I'm using the particular but the partition buckets, 12 free. Give me one. Give me 2, etc. Then it works.
So the conclusion is.
Querying large decks tables is just like how you eat an elephant one bite at a time.
All right, the next thing we have related and related table.
N how I always remember related groups, the one and related table grabs the many.
I'm going to show you this also on the New York people, which has the million roseanne which has a relationship to the billion rows.
So here New York people and then this is the billion rows so we have a one to many relationship.
Um.
The first piece of code. He is this one, So what I'm calculating is the average counts of cops in the weekend.
And you must imagine you have a million. Rosen is going to look at the billion rows and then calculate this value and surprising enough this is really fast.
I'm not going to execute in al because it would take me too much time.
Um what I want to highlight here as well is also when you when you're dealing with the decks engine. All that matters is the amount of distinct values. You have in your column and here I'm using a trunk. So I'm getting rid of part of the decimal.
Number because then that will also reduces the amount of distinct values and that's always how you need to think when you dealing with the decks and you always try to limit the amount of distinct values.
So this is the related an example of the related table.
So because I'm getting data from the consumption table then.
I also created one and this is in a more you need to be careful because of course. He is dealing with the beast of a billion and this is the consumption table.
And the consumption table, I created here also and column.
Is this an alcoholic drink? What people are consuming? I said we're not all drinking cups of coffee? There also some booze in those cups.
And as you see here I'm using the related function and.
Because this is a boolean expression this returns and also this true or false when I was writing. This piece of course, the first version. I was also saying if if this value is true. Then it is alcoholic. Otherwise, it is not alcoholic. But by putting in and if it already drastically.
The performance goes down because an if statement, then the formula and needs to work and then that has an impact on the on the performance so actually what I've done it was actually running. I think it was like 10 minutes and this one was running in 3 minutes, 1,000,000,000 rows so but then that I could see how you need to when you deal with the decks and you need to be.
Very specific what what's actually happening and this one would then say if the drink is alcoholic or not, and I said the conditions were it needs to be a person who's fish. It's a Speakeasy pub and also the membership needs to be valid if it's not a member, then of course will not drink any alcohol.
Alright let's.
Sorry.
Plus, there's an end date.
Let me go back.
Yeah, this is this is because there's a good I told earlier that with variables and what I just do here III store. The value of the end date, which is in the local died stored in the variable so I don't need to access it twice, but also for readability. Now I have here only 2 end dates. If I would have to the other piece. Of course, such a long piece of code and I have to do it twice.
It's bigger so I said it mainly also for readability. I did it here that's a.
Sorry.
The end date is the end of the membership so if the membership has ended.
All right.
So conclusion is those relationships are extremely fast, but I say I was running was working with billions of rows and it was just work, giving doing the calculation in a few minutes, so these relationships are very, very fast, so if you can use them use them.
Then the next with the calculate why you see what I also see a lot of times is that people want when we create a measure. They they use calculate but I use calculate when it's not needed so if you could measure like this just calculate in AM.
And I can understand that you do this because if you not new to dex you think I want to serve. I want to calculate the sum so let's put calculator around it. But in this case, it doesn't do anything and if it doesn't do anything just get rid of it because it just makes your code longer. So this is an example of where calculate doesn't do anything.
Um.
Then.
We also might know this piece of code that is something like then if we do a calculate so here I'm counting the cups and then I'm using a filter on the gangster.
Um.
But then this piece of quotes is actually this piece of code.
So and that's also when you work with deck deck. Sometimes have to call like syntax sugaring to call it So what happens is that yeah, then one piece because actually something else but it's a short version, the nice thing is the codes that clean the difficult thing is that then you don't really might not know what's actually going to what's happening so.
Yeah, practical example because you will use that a lot because again this version is the same as.
This version.
Alright another one that I want to highlight is keep filters.
Um what you will probably also might experience last time when you set a filter so I said the filter to gangster and then gangsters a group if I would do the the one where I just said the filter. You see the same value. All the time you might have seen this before if you use keep filters then actually it provides probably fail?
Sports only wealthy people there. Not that many wealthy people. This is the the the gangster people so if keep filters. You can actually then get the value that you that you need.
So that's another tip for you.
Uh.
I just showed you an example of calculate and with calculates it doesn't do anything where if you use calculate in a row context. It does something and I'm going to show you this quickly.
Hum.
So lots of the New York.
People.
And then
I'm going to table view.
And here you see the different versions.
And then when you when there's no when you use the some of the count of cops, then it will just give you the same thing all the time because it's in a row context. And there's no filter context. But if you use a calculate?
Inside the euro context is actually produces the yeah, the number so this is something to be aware of the year. Calculate does matter. And I don't have the time to go way? What exactly is road contacts and filter context. There are very good. Other resources like are better and Marco Ormet Ellington.
Reboot sessions about this.
Alright then.
Next thing I'm going to show you ace.
A joke.
Have a joke.
Well, we might all know this joke a SQL query goes into a bar walks up 2 tables and ask can I join you?
And then we have summarized columns summarized columns goes into a bar walks up to. I said, I will join you and that's also why I really like summarize tables because summarize tables. If you select columns from different tables. It just just joints for you, because the relationships already exist in the model and because.
Yeah, it will return those so I dusted joins for you and also extremely fast, so I'm a big fan of summarize columns.
Um.
To give you an example.
Is when you think about summarize columns you? Should actually think of if you're having a table of issue and just dragging and dropping from the from the right hand side. That process on the background also uses summarize summarize columns. It's the same way has the same principle.
Um.
So that's I I need to go a little bit quicker because I have some other stuff and I'm running out of time. So I said the code is available if you want to Chris at table adults and Elsa. I mean, email and I will send you the code so you can play with it because now.
I load all those data not interacted with it. Yes, so here you can see the count of cops on the 1,000,000,000 table.
And if I just do like some different selections.
Can see hear it spinning so it takes like now a few seconds?
It's a little bit slower than when they used to be, but now it's their own. It's all there.
Let me go to because this is this is on the 1,000,000,000 roll.
I see. Now it's it's finished so still in the billion rows.
But how can we make this quicker is by making that aggregation table and this version is based on the aggregation table and you can see if I selected here.
It is very quick.
You can see it's it's an this becaus an aggregation tables. Of course from the billion and aggregating it to a million and that's very quick.
Um.
And the way how I've done that is by creating a calculated table.
Is this one the monthly consumption?
And this is so.
This is the.
To summarize columns.
And then I want to show you the next gotcha here.
Is that I'm having here because 'cause here? I'm selecting column but you can also select if you want to select a value and summarize columns does not have a role context. And if you want to get a particular value. Then then it doesn't work, but then we have locally we have another.
And it's called selected value and selected value is the one I used in this selected month number.
This is selected value again selected value is actually a piece of Syntax Sugaring again.
You can look this up in the The Demo code when you want to play with it, but then it's another example of?
Um.
So selected file you can pick up the current value when there is no row context, which comes in very handy with summarized columns.
And.
Yeah, this was a little bit of Philosophy 'cause. I saw a quote letting profile process. If there is no wind row and then I said if there is no role then use selected value.
I would now it gets sticks.
So I already mentioned the fact about decks can be very sweet, but be aware of the syntax triggering.
What I said earlier.
And the final thing I want to show you is.
The way how I use generate because generate index is equivalent to across apply in in SQL.
And where does it come in handy because she saw we have I have the newer people they have a start and end date and what I want to do is based on those start and end date. I want to create a row. For every date that that person is active, but of course, the start and end date is for every person different so if you if you have depth type of problem then think about.
And when you
So what what the generates did is it's created this, this table.
Social Security number date and then I also edit and his start date and is ended and the reason why I'm doing that is that now. I unable to write very simple measures to get something that's very common because here we also usually want to know how many so now in this case how many Speakeasy people.
Our active how many you must think about the users together with the date so how many people are active on a certain moment? How many people are ends on a certain moment? How many people have started and by creating this table in the middle I can also create.
As well. An.
The mesh is so the definition here, you can see how simple this is this is just the distinct count of the Social Security number of the table that I created using using generate.
So just distinct counts and then you have the amount of active people.
Amount of people that have started.
Is just saying his start date? Is true and again at this thing counts and your?
And you're done so. These are not that complex measures and then I'm coming to the final point of final point finally very important conclusion might have heard it already before the conference data modeling if you do the correct data modeling it can really reduce the complex.
Dex measures 'cause that was the most the point that I want to get across to you so if you do the right data modeling does. Macy's become very simple and In addition, what's also very nice is that is also extremely fast as well because, as I also showed earlier in the demo is that if you have relationship so I now created table in middle and active.
The ship there are extremely fast as well so you have very good performance and you have simple meshes simple decks measures and that's what you want because we want to reduce the complexity.
So then I'm coming to the conclusion of my my session is that the deck. Squirreling makes I find it very elegant very expressive because you can write like very, very clear piece of code actually one piece because I have a little bit of time left 'cause I didn't even show you the generate.
So this is the month.
It mostly you know sort the active people.
This is everything so I'm I'm starting with the New York people.
I only want the people who have a start and an end date because not everyone was Speakeasy visitor. Some filtering out only the one that have a start date and then then I'm the next bit is that I am going to the calendar function, which has a start and an end date and what I.
What what I didn't do is that I'm looking at the start and end dates of that particular person.
And then using to generate by doing a select columns. It will create this. This intermediate table. We can see this piece of code is just a few lines of code. If we need to do it in SQL. It's I need a lot more lines so that's what I like about decks. It can be very if you get the right piece of code that can be like very elegant very to the point.
And also very fast as well so.
That's why I really like decks.
And again the decks engine can deliver and tremendous query performance. I was working on 1,000,000,000 rows and it was still it still performs good and finally when I also want to mention the decks engine can be very cost effective because what I've been doing today is just on power death.
This for free you can run it on your machine and I know at that point I know. Yeah, this is just a free 2.
And for example, what I did, I created like a summary table with the monthly consumption, which is like an aggregation table. We also have an aggregation feature in power. BI premium the way how it compares is that the one in power, BI premium. It has the cleverness that if you send a?
It knows which table to pick here. I need to pick the measure that is built on the aggregation table so that's the difference. Yeah, that's the only difference is a little bit, for usability point of view as a user you need to yeah, you need you need to know which measure to pick on the other hand, it's still the power is still there, so you can even.
Not premium.
You can also achieve a loss of course, Prima rules organize all the features which are away, which are very useful and now I see that I'm so I'm finishing off.
So yeah, thank you very much for attending my my session. Please fill in the feedback forms and sorry. I was a little bit rushed. I think I'd just too much to show it to you today. But as I said, you can send me an email and then you can get the code thanks very much.