Friday, February 21, 2020

Update: My Favorite (Data Platform) Podcasts

A tweet on the #SQLFamily hashtag triggered me last week to update an old blog post I wrote a few years back on My favorite (SQL server) podcasts.




After I switched jobs 2,5 years ago I have been doing a lot less commuting, which is good actually, but that left me also with less time to listen to podcasts. Therefore I chose to not listen to some episodes that are not that interesting to me.

Old podcasts

Here's the list I was already following since my last post:

  • .NET Rocks!
    • This is still a great podcast. I particularly like the Geek-outs.
  • Dear SQL DBA
    • The last episode is from the beginning of 2019, I always liked this very much because of the way Kendra explains things.
  • Developer On Fire
    • This was still a thing in 2019, but kinda dried up at the end of last year. The list of book recommendations is still there and huge, so do have a look there.
  • Hanselminutes
    • Still very much a thing. Good, short and fun episodes.
  • no dogma podcast
    • Still a regular flow of good episodes.
  • Office Hours Podcast: SQL Server Pain Relief
    • This one had its last episode on Jan 2 2019.
  • RunAs Radio
    • Still a constant, weekly flow of information and knowledge!
  • Simple Programmer Podcast
    • The frequency was turned down to weekly in 2019, but still regularly producing content.
  • SQL Down Under
    • The URL changed, and after 9 months of silence there was a new episode in December of last year.
  • SQL Server Radio
    • After a few months of silence there was a (re)new(ed) episode with a great announcement.
  • voiceofthedba’s podcast
    • Steve is actually on a sabbatical right now, but his short bursts of knowledge will certainly continue as he goes back to work next week.

New podcasts

These are the podcasts that are new:

Dutch podcasts

Next to the Data Platform related ones above, I also have a few Dutch podcasts that I like to listen to:

Closing

I still use the app Podcast Addict forAndroid, now the paid version without ads.
And one more pro tip before I finish: I use a podcast app that can set the speed of the podcast. I listen everything at 2 times the speed now :-) You can start at 1.5 and work your way up to what feels comfortable for you.

Are you also listening to podcasts, on your way to work, during work or somewhere else? Let me know in the comments which ones you like and if you have others not in my list!

 @NickyvV
at No comments:
Labels: ,

Monday, February 10, 2020

My Upcoming Speaking Engagements: Belgium and Iceland!

Before talking about my upcoming speaking engagements I would like to tell you a little story. And it goes like this.

How it all started

Back in 2018 I started my journey of getting more involved in the SQL and Power BI community. It actually all started with a Power BI User Group (the Dutch Data Dude Farewell Tour with Jeroen ter Heerdt (B | @jeroenterheerdt)) organized by PBIG (and me) at the Van Lanschot Kempen office in Den Bosch, on which I wrote about earlier last year. Jeroen Schalken (B | @JeroenSchalken) and me did a little introduction talk (around 15 minutes) about our implementation of Power BI at Van Lanschot Kempen. It was frightening and exciting at the same time. During the break of the evening Edgar Walther (B | @edgarwalther) of PBIG came to say it was a very interesting presentation and if we would like to extend that and come talk at the Power BI User Day in March 2019. It was right there that I (and Jeroen) made the decision to say YES. We still had to make a one hour presentation, but also had plenty of time left.

After that presentation I really got the feel of it and started looking for other opportunities to speak. Eventually this turned out in speaking at Power BI Days Belgium with a session and a workshop, followed by SQL Saturday Prague together with Jeroen, dataMinds Connect in Belgium, and even the Power Platform World Tour in Dublin!
It brought me so much more than I could have imagined.

The first thing is that I get the chance to share my knowledge with like-minded people. Next, it was not only amazing to travel to these venues with great speakers and people I look up to. There were also awesome social events during every trip. And that's where the real magic happens. You make contacts, chat a bit, have a beer (or drink of your choice) and make new friends. And at another event you meet even more people to connect with, but sometimes also catch up with the newly made friends from the previous conference. It's been an amazing experience so far.

Upcoming events

But now back to the title of this blog post: it was time again to update my Speaking page, because I already have a few very nice events in my calendar.

 This year will start with SQL Saturday Belgium (#956) on March 7, where I will deliver (an updated version of) the presentation that started it all.
Next up is SQL Saturday Iceland (#937) on March 28, where I will deliver my new session A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar. This session is based on the lightning session I gave at the Dublin Power BI UG during the Power Platform World Tour in Dublin.
Next to those two SQL Saturday events there's also a UG meeting planned, but not officially released yet. So more info on that one later.

Teaser

And last but not least: I will have some very exciting news to share in the coming weeks hopefully. I'm currently finalizing everything, so I'll let you know when everything is signed, sealed and delivered :-)

at No comments:
Labels: , , , ,

Friday, January 31, 2020

A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar - Part 2

In this blog series I will set apart all the steps needed to get this demo up and running on your own. I will use the text in this post, as well as screen shots and video's explaining it step by step. So if you're already (somewhat) familiar with Forms, Power Automate and/or Power BI streaming datasets, feel free to go directly to the video's and start building your own demo right away. Otherwise, keep reading and eventually you'll come across all the resources as well.

In Part 1, I already explained what the reason behind this demo is, and what I used it for in my presentations. I made a start with the demo and showed you how to make a Form and set up a streaming dataset in Power BI.
In Part 2 of this blog series I will show you how to glue it all together with Power Automate and create a report out of the data. There are some things you need to know in the streaming dataset, to properly show the output in your Power BI report. And lastly, I will show you a trick to get your data refreshed live while respondents are answering the questions, but without refreshing your browser!

Create an automation flow

In Power Automate you can create flows manually or with the help of a template. I used a template for my first flow, but if I search for this same template in the gallery it actually doesn't show up.


Power Automate template
I do have the URL because you can check that in your current flows. I resubmitted it as a template because I think it's quite useful:
Power Automate flow details
You can use that template here, or create the steps yourself manually. It's actually not that hard if you take a look at the template.



Create a Power BI report

When the previous steps have been completed, we have built ourselves a working solution, we only have to visualize the results from the dataset. OK, I know, I could also use the default reporting of Microsoft Forms, but that uses pie charts… And you know: every time you create a pie chart, God kills a kitten! :-)

Microsoft Forms default report
Before creating a report it's good to have some data to create the visuals from. To do that, in Forms, clicking on Preview lets you enter the form as a user. You can then create a report by connecting to the streaming dataset, live from Power BI Desktop.



As you saw in the video, I created a basic report with the questions I used in the form. You can create any kind of fancy report with this approach of course.
If you use for example a multiple choice question in your form, with multiple possible answers, I use the DAX formula CONTAINSSTRING or CONTAINSSTRINGEXACT to get the values out of the JSON-string. You write 1 measure per choice and then plot them in a chart. I'm not sure if there's an easier way of doing this, also maybe with a dynamic number of values. Let me know in the comments if you figure this out :-)

Share the Form

Last but not least, we also want to share our form with our audience, right? You can do this by creating a shorter URL with any of the URL shortener services like bit.ly, tinyurl.com or Google. You then share this link in the first few slides of your presentation and even have it visible while attendants are walking in prior to the start of your session. That way they have a minute to fill in the form and you don't have to wait on their answers during the presentation.

Create a Live Dashboard

So the trick for creating a live refreshing view of the answers coming in, is to create a dashboard of the visuals in the Power BI service. So pin all the visuals you need from the report to a dashboard, maybe also add the URL to the form and you're done. You could also refresh the browser every few seconds (either manually or with a browser extension in Chrome). But I do think the automatic refresh in a dashboard looks cleaner, otherwise you're also depending on the speed of the WiFi for loading your whole page every time you refresh.

Recap

As you have seen, setting up this Power Platform demo is actually not that difficult, you just have to know the steps and maybe go through them once to remember them. It does however add a lot of value to a presentation in my opinion. After the PowerPlatform World Tour Meetup in Dublin I had some good feedback from people that wanted to use this setup in their next presentation. From Mary Fealty (B | @Br0adtree) and Marc Lelijveld (B | @MarcLelijveld) I already heard they implemented this, so that's nice to hear!
Mary even added a great feature to the report.

Let me know in the comments or on Twitter if you use this or another method or if you have any questions!
at No comments:
Labels: , , ,
Location: Nederland

Tuesday, December 3, 2019

A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar - Part 1

This is the title of a live demo session that demonstrates the Microsoft Power Platform products working together to create an interactive data application. I recently had the honor of presenting this at the Dublin Power BI User Group (@DublinPUG) during my visit to the Power PlatformWorld Tour in Dublin. The slides of my regular session I gave there can also be found on my GitHub page.
I see you thinking about my title… I know Microsoft Flow is now renamed to PowerAutomate, but then my title just doesn't have the same flow of reading anymore :-)

 In this blog series I will set apart all the steps needed to get this demo up and running on your own. I will use the text in this post, as well as screen shots and video's explaining it step by step. So if you're already (somewhat) familiar with Forms, Power Automate and/or Power BI streaming datasets, feel free to go directly to the video's and start building your own demo right away. Otherwise, keep reading and eventually you'll come across all the resources as well.
As this is a blog series, in Part 1 I will explain what the reason behind this demo is, and what I used it for in my presentations. I will make a start with the demo and show you how to make a Form and set up a streaming dataset in Power BI.

Problem

I think it's good to have some form of interactivity in your presentation. Whether it's by (live) polling your audience, telling a story, asking questions during your presentation or any of the other 8 ways to make your presentation more interactive, doesn't really matter.
The way I started doing this during my first presentation was by firing some questions at the audience after I introduced myself. For example, I asked what kind of job they were in at the moment and with what technologies (related to the topic of the presentation) they have worked with. Although that worked pretty well, I was asking closed questions with a show of hands, so if someone didn't participate I couldn't really notice. I wasn't counting hands for that matter. I did get a feel of the audience from my perspective so I know on what topics to focus on a little more in my presentation.

Solution

As we work with the Power Platform suite of products, with my main focus on Power BI, I thought it was a good idea to use some of that Power! So what I did was the following:
Let's go through it step by step.

Create a form

Go over to Microsoft Forms, login and create a new or use an existing form.
I'm going to create a form that I can use in my presentation, so it is focused at the topic I'm presenting on. So I would like the audience to enter their Name, Continent (of origin), Occupation and Experience. Let's see how it works.
A little side-note: unfortunately I can't change the language in Forms to English because this is disabled in our O365-tenant, so some words on the screen are still in Dutch, sorry for that.


I showed a few possible options for creating questions in a form, but you can experiment with other options yourself. Some questions (like the multiple choice answers) are returned as JSON-values so you have to be aware you have to do some calculations in the Power BI report to nicely visualize the values.

Create a streaming dataset

The streaming dataset, of type push, has to be created before selecting the workspace in Power Automate, because otherwise the streaming dataset won't show up. In PowerBI.com you can create a dataset in any workspace, also in My Workspace. You do that by clicking on the +-sign in the home screen of your workspace.


Make sure you turn on the switch for Historic data analysis when creating the dataset. That way Power BI will store the data that's sent through this data stream, and you'll be able to do reporting and analysis on the collected data stream. This switch can also be used to clear the dataset.

Recap

In Part 1 of this blog series I started with Microsoft Forms and a Power BI streaming dataset.
In Part 2 I will show you how to glue it all together with Power Automate and create a report out of the data. There are some tricks you need to know in the streaming dataset to properly show the output in your Power BI report. And lastly I will show you a trick to get your data refreshed live while respondents are answering the questions, but without refreshing your browser!

at 2 comments:
Labels: , , ,
Location: Nederland

Wednesday, November 20, 2019

Quick tip: Sharing Power BI Dataflows


Problem

What rights does a user need to access a Power BI dataflow?

Just a quick post on Power BI dataflows after I saw this question on the Power BI Community forum about sharingdataflows - only via member/contributor permission?.
The user on the forum wants to know how to share dataflows with other users with the least privilege possible.
From my testing, it seems you need to provide people Contributor/Member/Admin to the workspace with the dataflow. If you choose the lowest permission Contributor - then they can't edit the dataflow BUT they can take over ownership and change the scheduling ! Not Good. I just want people to be able to connect to the dataflow.Have I missed anything ?
I thought this was just an easy lmgtfy-question, because there are already a lot of sources on dataflows:
Returning to finding the answer to this question, it turned out it wasn't that easy. There even is an open issue on the documentation on GitHub to add this information to the docs. So I ended up testing it myself.

Solution

To access a PowerBI dataflow you need either
  • access to the workspace the dataflow resides in as an Admin, Member or Contributor, or
  • Viewer role access to the workspace

Option 1, edit rights to the workspace, might not be desirable for your use case. But we have one other option: the new Viewer role that was introduced back in June of this year can also be used to share dataflows. I already wrote about the new Viewer role in PBI Desktop earlier.

When you are granted Viewer role access to a workspace where a dataflow is created, you can connect to and use that dataflow in Power BI Desktop:


Final thoughts

On a dataset you also have the ability to give the Build permission to give someone access to create content from that dataset (e.g. reports, dashboards, pinned tiles from Q&A, and Insights Discovery, but also content outside Power BI). This feature is still in preview however at the moment of writing. As the documentation says: Build permission is only relevant for datasets, for now? :-)

There is an idea that sounds like the Build permission on dataflows: Dataflow Permissions to give access to an individual dataflow.
Maybe the Build permission will also be added to dataflows in a later phase?
at 2 comments:
Labels: , , ,
Location: Nederland

Monday, November 11, 2019

Power Query and The Curious Case of Keep Top Rows

The Power Query Editor in Power BI has many transformations that can be set from the UI. Keep Top Rows is one of them. I'd like to take you on my journey through this curious case I ran into a couple of weeks back when preparing for my session on Query Folding in Power BI.

Transformations in Power Query

Problem

I was preparing my demo's for my session at dataMinds Connect on Query Folding in Power BI (slides). If you're not familiar with query folding: transformations in Power Query are transformed into the native (data source) language and executed at the source for better performance. For a more thorough overview have a look at the post How Query Folding Works from Matt Allington (B | @ExceleratorBI).

If we look at SQL Server, some transformations can be folded (like Sorting, a simple Group By and Keep Top Rows), others cannot (merging two columns from the UI, using the Buffer() M-function and Keep Top Rows). See what I did there? :) Keep Top Rows on its own is a foldable transformation, it is (obviously) transformed into the TOP operator in SQL.
However, when using Keep Top Rows in combination with some other transformations it (looks like it) is not folding anymore. Your best chance is to use the View Native Query option in Power Query SQL Server Profiler to check if the query is folding or not. More on that later :)
A bit of contemplation: I do think the real use case of the Keep Top Rows transformation might be mostly in development environments, where you can limit the number of rows to import for example. I still think the findings were rather odd and worth sharing. So, let's have a look at how it works.

Setup

I used import mode for the Fact.Movement table from the WideWorldImportersDW database. I then added the Keep Top (100) Rows transformation in the Power Query Editor.
Keep Top Rows


As you can see it folds to SQL and all is good for now.

View Native Query with TOP 100



























Adding transformations


Let's add a transformation on the Quantity column and apply an absolute value to it.
Add Absolute value of Quantity

So now the option to View Native Query is gone. Let's see if Power Query is speaking the truth (spoiler: No) 😀
I started SQL Server Profiler and captured the queries running against the database after I Close and Apply my changes. As you can see, now both the TOP operator and ABS function are folded.
SQL Server Profiler: TOP 100 and ABS

Now let's try something different and add a filter on the Date Key. Something strange has happened, because I already filtered the first 100 rows, so only 2 dates remained after that. But when I click on the Calculated Absolute Value query step I suddenly have a lot more dates that shouldn't be there in my opinion.
Filter on Date Key


Let's add a field from the Customer dimension this time and see what happens.
Expand Customer Dimension

The query for Fact.Movement is still the same, so the TOP operator and ABS function are still folding. And this is what is sent to the database for the Customer dimension:
SQL Server Profiler: Customer Dimension with WHERE clause

The Curious Case

Now watch closely to the where clause: so it actually already filters out the Customer Key based on the steps performed earlier in the Query Editor. But this Customer dimension is a separate query that returns 1 row, which will be joined to the other query inside Power Query.
Back to that WHERE clause: that Customer Key = 0 is valid for my (broken) Calculated Absolute Value query step, not for the Kept First 100 Rows as you see in the picture above. So I actually get the wrong a different result back into Power Query than I would expect.
I see you're thinking: "But you didn't add an explicit order, right?" So the top 100 is a little arbitrary. That's true, so let's add that order by.
Add Sorted Rows

This is a slightly better result in my opinion. Because now the Power Query steps are in sync with each other. The issue with View Native Query still remains the same by the way. It stops working at the Calculated Absolute Value query step, but the query send to the database is the following, so including the ABS function:

View Native Query after Sort

Recap

I showed several variations in the Power Query Editor of using Keep Top Rows and showed that adding subsequent steps brakes the View Native Query option in the editor, although query folding sometimes does take place. And adding an order to your query before applying a TOP operator is always a good idea, not only in SQL.

The steps I used might not all be best practices when creating a data model in Power BI. In case of the join to the customer dimension for example, it might be better to model it as a separate dimension, because STAR SCHEMA ALL THE THINGS. But I used those steps to highlight the problem.

I already mentioned this bug feature to Patrick and Adam (members of the Power BI CAT team) from Guy in a Cube (B | @GuyInACube) when I was at the Precon at Data Saturday Holland. Patrick LeBlanc (B | @patrickdba) also hadn't seen this before and hopeully will take it up with one of his team members. I thought I'd make a write up for him to easily reproduce it.
at No comments:
Labels: , , ,

Monday, October 21, 2019

Speaking at dataMinds Connect in Belgium

I already posted a short update on LinkedIn, but I now finally also had the time to update my blog.


Query Folding in Power BI at dataMinds Connect

I had the honor of doing a session at the Newcomer track at dataMinds Connect. This was a special track for people like me that have (almost) no conference speaking experience. The conference assigned buddies to these newcomers. For that, I'd like to thank Koen Verbeeck (B | @Ko_Ver) for helping me enormously with feedback to and try-outs of my presentation.
My submission to this conference was actually the first I did earlier this year, after that I also submitted to the other places like SQL Saturday Prague. But this was actually my first technical session with lot's of demo's.

Slides on Github

The slides were already shared with the conference organizers, but are also available via my Speaking page. I recently made some changes to the way I share them because I moved all my talks and slides to my Github. I still have some things and links to add there, but the basics are there.

I also did some calculations on the feedback I received and I was pretty happy with the average of 3.9!

Feedback scores


Half marathon Eindhoven

Like I mentioned in my session at dataMinds Connect I also participated in the half marathon in Eindhoven on Sunday October 12.
As it was relatively warm with 24 degrees Celcius, I had to shelve my plans of running a PR fairly quickly after 5K. A lot of people also were struck by the warmth and humidity that day


Power Platform World Tour in Dublin

The next stop is the Power Platform World Tour in Dublin where I'm presenting my Tips & Tricks of our Power BI implementation! I'm also doing one of the "Show & Tell" sessions at the Power Platform World Tour Meetup on the evening of October 30, organised by Ben Watt (B | @benrebooted).
I can also offer a $100 discount to all members who are part of their local Power Platform User Group community with the following code: 2019PPWT100DUB

NvV
at No comments:
Labels: , , , , ,
Subscribe to: Posts (Atom)

Featured Post

Power Apps or Translytical Task Flows?

I think I have gotten this question at least five or six times in the last few months, and with Translytical Task Flows reaching GA in the M...