bennidejagere

This user hasn't shared any biographical information

Homepage: http://bennidejagere.wordpress.com

T-SQL Tuesday #137 – Jupyter Notebooks for the win?

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

T-SQL Tuesday #137

T-SQL Tuesday is the brainchild of Adam Machanic (Blog | Twitter). December 2009 was the first T-SQL Tuesday invitation that went out by Adam. It is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog | Twitter) organises the event and maintains a website with all previous posts. Everyone is welcome to participate in this monthly blog post.

The Ask

This month’s T-SQL Tuesday is hosted by Steve Jones (Blog | Twitter). Steve says:

“For this month’s T-SQL Tuesday, I want you to write about how you have used, or would like to use, a Jupyter notebook. This seemed to be exciting for many people at first, but I haven’t seen a lot of uptake from users in general. So I’m curious if you are using them.”

The original post is here.

My contribution

Jupyter Notebooks and me

In my write-up for T-SQL Tuesday #135, I wrote the following:

“After seeing some folks like Julie Koesmarno (twitter), Aaron Nelson (twitter) and Rob Sewell (twitter) show off Notebooks in Azure Data Studio, I’m keen in rewriting some of my scripts and processes into a fancy Jupyter Notebook. I’m convinced this will really help me in some of the assessments I do, and easily share my work and results. Other than that, it’s fancy toys I want to try out for myself.”

By now, I actually did start on reforming some of my scripts into Jupyter Notebooks, with the emphasis on using them in an explanatory context. Maybe even use it an x step plan to execute certain tasks that can not be automated against the APIs. Maybe even turn them into guidebooks for clients to start working on some processes of their own.

I have to admit, I’m liking them a lot so far. My plan is to make this into a series of blog posts, and a community session to present. But, I’m having issues finding time and motivation to stride forward on this.

What kept me from actually getting started with this was the initial step and learning curve I had to take to rewrite my scripts (on top of my natural talent for procrastination). But then, we had Aaron Nelson (twitter) over for a user group session at dataMinds.be, on how he uses PowerShell (and Jupyter Notebooks) to aid in some of the processes. I voiced my intent on getting started, and Aaron graciously replied to me how easy it actually is to rewrite an existing script into a Jupyter Notebook running PowerShell. The only thing I ran into, is that I had some issues with the way I use comments, and how they’re transformed into Markdown. But, nothing I wasn’t able to solve easily with the help of my trusty friend Mr. Bing.

 

Extracting the Power BI Activity Log with a Jupyter Notebook

Right now, the only Jupyter Notebook I have ready destined for public eyes, is one to get started with extracting the Power BI Activity Log, and storing them into a .csv file on a location of your choice. I’m convinced every Power BI Admin should set up extraction of the Activity Log as soon as possible, to be able to backtrack to historical usage and analysis. This log only returns data for the past 30 days, so it’s not all that long.

Why did I opt for PowerShell and .csv files, when there’s other solutions out there like using Azure Data Factory as Just Blindbaek (blog post) did? I have a mix of clients scheduling tasks on-premises, or through Azure Automation with Azure Functions. The solution I came up with, had to be easily changeable in between the different environments without having to rewrite the entire thing.

This scenario is written in 2 flavours, being a regular and simple version. The simple version doesn’t have any of the frills added to it, and is purely destined for explaining the happy path that can occur with this. The version I use myself is the regular one, and this comes with some logging and simple error handling, to counter some of the scenarios I ran into when testing.

You can find the notebooks on my GitHub repo, and can easily get started with this. All you need is a user that has Power BI Service Admin privileges, and you’re good to go!

What notebooks do I have coming up?

The notebook I’m currently rewriting is to bulid up a Power BI Metadata inventory of your most commonly used artefacts, through the Read-only Admin APIs. In addition, I’m working on assembling a lineage view of your artefact through the scanning APIs that were released when the Azure Purview solution came into preview.

I have some more things brewing for on-premises Enterprise Data Gateways, and using the Graph API for licenses, group memberships, .. Finding the time for those is something I’m really struggling with right now.

Is there anything else brewing for Power BI and Jupyter Notebooks?

The Power Platform 2020 Release Wave 2 stated Embedding of Power BI Reports in Jupyter Notebooks was coming to GA in March 2021. I think it got moved back, but I’m hoping to see it come to life soon. Purely for Data Analyst purposes, there’s some cool things that can be done with this 😃.

“Scientists, journalists, researchers, developers, and other Jupyter notebook users will be able to embed Power BI reports, and then use Power BI features to improve their data storytelling. Once embedded, adding Power BI bookmarks will make it possible to show different views of the same report and order those views to facilitate data storytelling.”

Conclusion

This T-SQL Tuesday was exactly the kick I needed to start finalising some of the things that had been brewing for a longer time, and came at exactly the right time! I’m absolutely convinced Jupyter Notebooks have a place for explaining some of the more practical things in Power BI Governance, because of the capabilities of adding virtually any object in Markdown. The initial learning curve I feared, wasn’t that hard at all, and it actually made me realise these things should be used more often.

That said, I hope you enjoyed my ramblings.

Stay safe, take care!

, , , ,

2 Comments

PowerBIQuiz: APIs & PowerShell

Wednesday past (March 24th 2021), I had the wonderful pleasure of appearing as a co-host on the bi-weekly PowerBI Quiz by Just Blindbaek, a Danish fellow Data Platform MVP. We’re nearing the end of Season 3, and though I haven’t been able to chime in every time, or score the way I wanted score, I still have a blast every time I play this. There’s a large number of returning faces every time, and we keep each other on our toes, and have tons of fun whilst doing so.

When Just asked me a few weeks back if I wanted to host a topic, and I immediately jumped to this opportunity. I’ve been doing some work around APIs and PowerShell, which was a topic I dreaded before. Hence, this was an excellent topic to test the knowledge of the Power BI Quiz participants. All questions are aimed at a governance perspective, and how the APIs/PowerShell can be a useful tool for helping things stay afloat.

Whilst making these questions, I also realised it would be an excellent blog post to explain the questions and answers in a blog post afterwards, to have a better explanation of my reasoning. As well, I learned it’s harder than expected to create questions that fall in the not too hard, not too easy sweet spot for a quiz. This makes me respect the other quiz makers even more!

The recording of the Power BI Quiz can be found below, or you navigate through the Power BI Quiz website

The Questions and Answers

1. The Power BI Activity Log can return data for the last ..

  • A – 90 days
  • B – 30 days
  • C – 45 days
  • D – 60 days

Answer: B
Explanation: The Power BI Activity Log will only return day for the last 30 days, which is different than the Office 365 Audit Logs through the Security & Compliance Centre, which will return the last 90 days. You’ll have to “sacrifice” those extra 60 days of possible history, but in return you get a more stable API (in my opinion), and less required privileges to extract that data.

Meaning, you only need to have permissions on the Power BI side (Power BI Admin Role specifically), and no roles in the Security & Compliance Center of Office 365 (At least viewer role). I have had some organisations where this was a definite no-go, as this would mean they could extract the logs for all Office 365 components.

Microsoft Docs page outlining the differences

2. The Export to File API will work with workspaces using

  • A – An A1 Sku (or higher)
  • B – Power BI Pro
  • C – Power BI Free
  • D – A P1 Sku (or higher)

Answer: A, D
Explanation: The Export to File API will export your Power BI Reports or Paginated Reports to file format of your choice. Based on the capacity you have backing up these requests, you’ll get a higher concurrency rate for processing these reports. In the background, it’s essentially doing the print/save as options you can manually, but more optimised for bulk usage.

Some things to note .. Right now, you can technically get it to work with a PPU license, but throttling will hit you fast ‘n hard. If this behaviour will persist after General Availability on April 2nd, I can’t tell. An A1 Sku (or higher) is intended for external (outside of your own organisation) embedding scenarios, and can help you get started with this scenario really quickly.

Export to File API Explained
Export to File API Reference Page

3. Power BI dataflows can be created/copied through API calls

  • A – TRUE
  • B – FALSE

Answer: A

Explanation: Yes, it’s possible! But it’s not easy 😃. This process is based on the Import APIs, which lets you upload .pbix files for a report, or the .json files for dataflows. While this sounds easy enough, there’s a few hoops you have to jump through, which I never got working on my own. Luckily, a few community members have posted working options and scripts to do this, and now there’s even an External Tool for it.

Marc Lelijveld – Move dataflows across workspaces
Marcus Wegener (German post, but it translates well) – Export to dataflow

4. To determine if a .pbix file uses DirectQuery through APIs, I can

  • A – use Report Information
  • B – use Dataset Information
  • C – use Datasource Information
  • D – use DirectQuery RefreshSchedule

Answer: B, C, D

Explanation:

When using the Dataset Information (GetGroupsAsAdmin, with a $expand on datasets), it will return a field called ‘ContentProviderType’, which displays the connection mode for the .pbix file. For those files that run DirectQuery, you’ll see a value ‘PbixInDirectQueryMode’.

When using the Datasource Information (GetDataSourcesAsAdmin), there’s 2 fields called Name and ConnectionString. Based on the tests I did on my environments, they only returned values when DirectQuery is being used. Would I trust this completely to base myself on for all scenarios? Definitely not, but it’s something!

When using the DirectQuery RefreshSchedule, you can call this API for every dataset. The ones that actually return a schedule are either DirectQuery or LiveConnection. Based on the DataSourceType (pretty much exclude Power BI / Analysis Services), the returned records are datasets which have the default behaviour for caching enabled. Since you have to loop over all your datasets individually, you have a high risk of running into limits on this one, and an additional chance that someone disabled the caching behaviour.

To conclude this, the most durable solution I found there was to use the Dataset Information, but this has not yet been tested with Composite Models v1, or Direct Query over Azure Analysis Services and Power BI Datasets.

5. “GetScanResult” is the only API that returns Dataset endorsements

  • A – TRUE
  • B – FALSE

Answer: A

Explanation: At the time of writing (and airing), this is definitely the case! GetScanResult is part of the asynchronous mechanism to incrementally fetch all the lineage information on your Power BI Tenant. Essentially it’s the same that’s happening under the covers of Azure Purview, and this allows you to build your own solution.

To successfully do this, you need to handle the calls for modified workspaces, poll for the request state, and then get the results back. If you’re fairly new to APIs & PowerShell, this is definitely daunting. Luckily, there’s community resources out the there to help us get started.

Just Thorning Blindbaek – Extracting Power BI Metadata with Azure Data Factory

6. Using a Service Principal (SPN) I can use these read-only Admin APIs

  • A – GetDatasetsAsAdmin (Datasets)
  • B – AddUserAsAdmin (Groups)
  • C – GetDatasourcesAsAdmin (Datasets)
  • D – GetCapacitiesAsAdmin (Capacities)

Answer: C, D

Explanation: Read-only Admin APIs and Service Principal authentication were only announced in December 2020, but they made a really big difference for the type of things we’re doing. They allow us to use an Azure AD App Registration, and extract the metadata details on our Power BI Tenant we need, fully unattended! Right now, there’s only an initial batch of APIs that are allowed for SPN authentication, but I expect more to arrive over time.

The only thing I find a bit wonky right now is that we can call GetDataSourcesAsAdmin, which requires a DatasetID. Yet we can not call GetDatasetsAsAdmin to help us get started. Meaning, if we want to iterate over datasets unattended, we have to call a different API (such as GetGroupsAsAdmin, $expand datasets, or GetDatasets) to help us get started. Using the GetCapacitiesAsAdmin, we can return all the info we need on the capacities we have in our organisation.

AddUsersAsAdmin is not allowed, which makes sense as it’s technically not a read-only Admin API.

Microsoft Docs Page that explains Read-only Admin APIs with Service Principal Authentication

7. Using “GetGroupsAsAdmin” I can use the $Expand parameter for

  • A – datasets
  • B – datasources
  • C – users
  • D – apps

Answer: A, C

Explanation: GetGroupsAsAdmin is my bread and butter for getting most details out of my Power BI Tenant, especially in combination with $Expand. Before this existed, we had to individually loop over all the object (nested calls most of the time) to return the required results. For larger tenants, this meant bumping into API Limits all .. the .. time .. The GetGroupsAsAdmin API is called once for every workspace, and will return all the specified information in a single go. There’s a limit for 5000 workspaces per call, but you can work your way around this by intelligently looping over this.

The $expand parameter can be used for users, reports, dashboards, datasets, dataflows, workbooks. Meaning datasources and apps are not a part of this.

GetGroupsAsAdmin with datasets expanded
GetGroupsAsAdmin with users expanded (only for v2 / new workspaces!)

8. I can use the (Datasets) UpdateDatasources API to change a parametrized Connection String

  • A – TRUE
  • B – FALSE

Answer: B

Explanation: We can use API calls to alter connection strings, for instance to change between DEV/PRD environments, or perform a migration. One of those options is using the UpdateDatasources API, to do so. When using DirectQuery datasources, I’ve had some real issues when using a parametrized ConnectionString, as it would not allow it. When reading the Docs page, it actually does outline this a restriction, and tells us to use the UpdateParameters API to change the parameters, and not the connection string. In full transparency, I’d look at Rebind Report operations, or External Tools to assist in this process, as I had some kinks to work out when doing this.

9. To safely unassign a Workspace from a Capacity using APIs I can

  • A – Delete the workspace, it can not be unassigned through the API
  • B – Use “CapacityAssignmentStatus” with -UnAssign and Workspace GUID
  • C – Use “UnassignFromCapacity” with the workspace GUID
  • D – Use “AssignToCapacity” with an empty GUID (0000..00) for CapacityID

Answer: D

Explanation: Off the bat, let me emphasise on the word “safely” in the question. When deleting a workspace it’s no longer assigned to the workspace, but we also lost the content that was included in this..

The only real solution to this is using the AssignToCapacity API, with an empty GUID (00000000-0000-0000-0000-000000000000), and thus assigning to a default capacity (the shared one for Power BI Pro). CapacityAssignmentStatus is an actual API, but it’s only used for getting the status reports on certain workspaces, when making the switch.

UnassignFromCapacity does not exist as an official API, and is something I completely made up 😃

10. To inventarise who CAN use PPU (Premium Per User) features, I have to:

  • A – Check the Power BI Activity Log
  • B – Refer to the PPU Tab in the Power BI Admin Portal
  • C – Grab licensing information (ie. through Graph API)
  • D – Check Power BI Workspace information

Answer: A, C, D

Explanation: First off, I have to explain ‘inventarise’, as it caused some confusion during the quiz. My meaning for this is the assemble a list of users who have the licenses and workspace access to perform PPU activities. To get the license information, we have to look at 2 different sources. The easiest one is the licenses that were assigned by a License or Power BI Admin, and these be extracted through the Graph API for instance. The hardest one is the In-Product Trial Experience, where users opt-in for the paid trial and get a free 60 day access pass to take it for a spin. The only way we can extract that information is a part of the Power BI Activity Log (or Office 365 Audit Log). Specifically we need to look for the ‘OptInForProTrial’, and ‘OptInForExtendedProTrial’ actions.

Then, we need to look at workspaces that are assigned to the Premium Per User reserved capacity, which we can do with the GetGroupsAsAdmin API, preferred with the $expand on users, to return the access list for the workspace. Luckily, I made a write-up on this process a while back, that holds some information on what you can do to prevent it, and steps to figure out who’s doing it.

One remark, which was stressed by Sir Saxton of Cubes as well, is the essential purpose of storing the Activity Logs as soon as you can. Due to the limited retention (30 days) you can not go back in time to when these licenses were made available. This is just one of the use cases where the Activity Log comes to the rescue, and I run into different uses on a daily basis. If there’s anything you should learn from this, it’s to get that extraction and retention up and running, ASAP!

Power BI Premium Per User: Who’s using it in your tenant?

Conclusion

I had lots of preparing the questions, and co-hosting the quiz with Just. I’m hoping people have learned something new on this, and that they’ll dabble into some of these things themselves as well. But, why not join us for the real deal next time?
Next time we’re doing the Power BI Quiz is on Wednesday April 7th at 8PM UTC+1, with the co-host to be announced

Subscribe to Just’s YouTube channel, and you’ll get the notifications for it as well

, , , ,

Leave a comment

T-SQL Tuesday #135 – My Tools for the Trade

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

T-SQL Tuesday #135

After making my first contribution to T-SQL Tuesday last month, I figured I couldn’t stay behind when it’s a topic I have loads to share on.

T-SQL Tuesday is the brainchild of Adam Machanic (Blog | Twitter). December 2009 was the first T-SQL Tuesday invitation that went out by Adam. It is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog | Twitter) organises the event and maintains a website with all previous posts. Everyone is welcome to participate in this monthly blog post.

The Ask

This month’s T-SQL Tuesday is hosted by Mikey Bronowski( Blog | Twitter ). Mikey says: “Without tools, most of the work would be much harder to do or could not be done at all. Write a blog post about the most helpful and effective tools you use or know of.”

The original post is here.

My contribution

Which tools are essential to my working day?

When thinking about which tools I use most often, I quickly drew the conclusion that my job content changed drastically over the past few months. Yes, some spiky microscopic creature causing havoc with a global pandemic naturally contributes to this, but it’s also the nature of projects I’ve been taking on lately. These days, I spend the majority of my time in Microsoft Teams, hopping through tenants & accounts, and jumping from one call/conversation to the other. And yes, I’ve lost many moments of my life figuring out in what channel I was at exactly, and who I was supposed to be talking to.

Microsoft Teams, Edge and Outlook

Bluntly put, Teams has some room for improvement to allow users to easily change between tenants and accounts, and without wreaking havoc on the related Azure ADs. Right now, my best working solution is that I have a Microsoft Edge-profile setup for every customer, and other types of organisational accounts I have (ie. dataMinds.be). Every single one of these profiles is synced in Edge, and tied into my LastPass vault. Each of those profiles usually has a Teams Tenant coupled to it, that I install as an Edge Desktop Application. It works pretty smooth, for most of the use cases you might have. The main reason for switching over to the ‘fat client’ of Teams is when I need to take over screens in a call, or to go dig deep into settings windows. Marc Lelijveld has written an excellent write-up on how to set this up, although I didn’t go as far as creating custom icons for each single one.

One thing I’ve also started doing for some clients (the ones I work for on a very regular basis), is to set up e-mail forwarding from that customer account to my own work account, with an extra incoming Outlook rule to triage that to a separate folder immediately. It saves me a lot of time when I quickly need to check if something has been sent to that account, rather than jumping through the hoops of VPN, Multi-factor authentication, etc. Most clients remember to include my work account if they want a quick response from me, but it still helps to track down some things that I may otherwise miss.

The Office Suite

When I mentioned my job contents changed a lot, it also means that I find myself more in the ‘writing documents’ part of the job. Writing out assessment, audit, governance, presales, .. documents is an important part of what I do these days. So yes, my best friends are Word, Excel, PowerPoint and OneNote. Each has their specific use in what I need to do, and over time I’ve built a whole library of stuff I can reuse. OneNote has definitely grown on me, and it’s now an inevitable part of my process.

And do I do some ‘actual’ work?

Define ‘actual’ work 😉. I still get plenty of opportunities to build out some technical stuff, or help some colleagues/clients when they’re in a jam. For the sake of time, I’ll just limit myself to some of the Power BI things I do, and leave out the rest of the Microsoft Data Platform stack. For once, I’d like to publish a blog post under 3000 words 😊.

When External Tools were released (release blog) to the general audience in July 2020, a shock wave hit the BI landscape. Before, a lot of things were ‘kinda’ possible, but they mostly resulted into going on unsupported terrain, and potentially tricky results. All fun and games when you’re a more technical person trying to keep up with things, but definitely nothing to hand over to a client that is learning their first steps with Power BI.

The External Tools (and the Enhanced Metadata format enabling it) allow end users of Power BI Desktop to call on custom built applications, scripts, .. to augment their developer/designer experience. These days, there’s over 40 (I stopped counting) external tools available, each with their own use case and focal area. When showing off some of the capabilities to my clients, it amazes me to see how quickly they pick up these things, and start building out their own ways of working.

Depending on the client, their IT Compliancy rules, the business and technical requirements, my actual tool belt tends to vary. Not every IT organisation allows user to freely install an application, digitally signed or not, so this is definitely an important one to take into your conversations early on.
In a nutshell, my possible weapons of choice are:

  • Power BI Desktop & SQL Server Management Studio
  • Tabular Editor: My main go to when I need some more flexibility for ie. setting up calculation groups, content for documentation, advanced scripting, mass editing objects, .. This allows
    • PowerBI.tips has a 4-part series with Daniel Otykier, the main developer.
  • DAX Studio: Whenever I need to write a more complex DAX measure, or go digging into how DAX measures are performing against my model, DAX Studio comes to my aid. Simply put, this has saved me many hours when figuring out things.
    • PowerBI.tips has a 2-part series with Darren Gosbell and Marco Russo (Part 1, Part 2)
    • SQLBI.com has a full playlist on DAX Studio and Vertipaq Analyzer
  • Vertipaq Analyzer (Excel): Yes, I’m aware DAX Studio holds a version of Vertipaq Analyzer. Yet, the Excel version allows you to go a bit more into detail on encoding specifics etc.
  • ALM Toolkit: When working with Incremental Refresh or larger models, ALM Toolkit allows you to compare and publish metadata changes to the Power BI Service. Deploying datasets without needing to do a full refresh every single time, that’s where the magic is at ♥
    • PowerBI.tips held a webinar with Christian Wade on ALM Toolkit
  • Power BI Helper: Reza Rad has put incredible effort into this tool,
  • Power BI Cleaner: Lightweight solution by Imke Feldmann (The BIccountant) that checks which fields are used in your .pbix file.
  • Power BI Sentinel: PBI Sentinel is a paid tool, but a downright impressive one. The sheer fact that they are able to capture a tremendous amount of information on how Power BI is used in your organisation can be a YUUUGE! timesaver. On top of that, they’re able to perform table and column level lineage for some data sources in Power BI. The fact you can’t even do this (yet) with the tools at hand by Microsoft, is a very impressive feat.
    • Reid Havens held a livestream with Alex Whittles in December 2020, going through some of the most important features.
  • Power BI Field Finder v2: Lightweight solution by Stephanie Bruno to help analyze how a Power BI file is constructed, and how visualisations are used.
  • DAX Beautifier: 1 click, 1 call to DAXFormatter.com to format every piece of DAX code you have in your model. Because yes, my eyes burn when I see poorly formatted code 😂

Which tools am I going to add to my toolbelt? (Soon, I promise ..)

After seeing some folks like Julie Koesmarno (twitter), Aaron Nelson (twitter) and Rob Sewell (twitter) show off Notebooks in Azure Data Studio, I’m keen in rewriting some of my scripts and processes into a fancy Jupyter Notebook. I’m convinced this will really help me in some of the assessments I do, and easily share my work and results. Other than that, it’s fancy toys I want to try out for myself.

Something I’m looking improve upon fairly soon is my remote whiteboarding setup. When in workshop meetings where I had actual people in the same room, I usually ended up at a whiteboard to quickly draw out some things. For me, this is still the hardest thing to adapt to in our remote way of working we have these days. I’m currently digging into some options for external drawing pads, to allow these to sync to the Teams Meeting I’m in at that time. Yes, my drawing skills will still be terrible, but it’ll be huge improvement of what I’m doing now.

I’ve been trying out some options for a to do list, and I’ve not found one that actually works for me. Currently I’m experimenting with Microsoft To Do, but it’s not catching on as I expected. For some odd reason, I keep ending back up at the pieces of paper that are always lying in front of me at my desk. The physical act of writing it down helps me to remember it best, which has been an issue with some of the tools I’ve tried. Who knows, I may even try to build out an actual kanban board on my wall as a next experiment ..

Conclusion

Writing out my train of thoughts made me realise I use plenty of different tools, as there’s still quite a few I haven’t touched upon. I’ll need to reflect on how I’m using most of them, and if it’s to their proposed strengths. If there’s option for improving my process, it’ll definitely be worth it. But, I’m looking forward to reading some other posts and see what other gems people are using.

Stay safe, take care!

3 Comments

Speaking At : Data Event Vienna 2021 (SQLSaturday #1015)

Speaking At : Data Event Vienna 2021 (SQLSaturday #1015), January 15th 2021

This Friday, I’m coming out of my hibernation for presenting at remote events, and it’s a special one. The final SQL Saturday as we know it will be held virtually in Vienna, on Friday January 15th 2021. PASS is dissolving that same day, and the future of the SQL Saturday brand is unsure. SQL Saturday has been a very important part of my community engagements throughout the past years. From attending in Utrecht, to speaking at my first SQL Saturday in Munich, to helping to organise our own SQL Saturday Belgium. It’s been one heck of a ride, to say the very least.

I’ll be talking about Impactful Data Visualisations, and some things you can keep in mind to design them (Session Details). Kicking off at 10:15AM, you can find me in the “Power BI & Power Platform 1” room. Apart from my usual ramblings, there’ll be a stellar lineup with interesting topics to cater to your every needs. Registration is free, and open until Friday. Head over to their registration page, to join in on the fun!

SQL Saturday Vienna (1015)

Event Link : SQLSaturday #1015 – Vienna 2021 (Remote)
Event Date : Friday January 15th 2021
Session Time : 10:15 – 11:15 (UTC +1 / GMT)
Session : Designing impactful visualisations for your data

, ,

4 Comments

T-SQL Tuesday #134 – Give me a break!

It’s T-SQL Tuesday!!

T-SQL Tuesday #134

T-SQL Tuesday #134

This is actually the first time I’m contributing to T-SQL Tuesday, after having read many of the entries in the years before.

T-SQL Tuesday is the brainchild of Adam Machanic (Blog | Twitter). December 2009 was the first T-SQL Tuesday invitation that went out by Adam. It is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog | Twitter) organises the event and maintains a website with all previous posts. Everyone is welcome to participate in this monthly blog post.

The Ask

This month’s T-SQL Tuesday is hosted by James McGillivray ( Blog | Twitter ). James wants to know how we’re managing to give ourselves some breaks, to keep ourselves from going even more bonkers.

The original post is here.

What do you do to take a break when you’re stuck at home?

Arguably so, I’ve always been terrible at setting aside time for breaks when working on my own. When working at a customer site, or in the office, things flowed a bit more naturally when grabbing a coffee, and having a chat. The reality of the past few months has been that I’ve been stuck behind my desk for hours on end, mostly being dragged into numerous Teams Meetings or Zoom Calls. I quickly realised that I needed to get this under control, to be able to last.

These days, my break times are mostly consumed by the doggo of the house, Pixie. She’s a nearly 2 years old Briard, who have a heritage as French shepherd dogs. Long story short, these dogs are incredibly active and fun to have around. Most days, I take her for a walk around the block before getting started. Then, during one of my coffee breaks, I go out in the garden and kick around some of the Jolly Balls I bought for her. At times like these, I can tell that she’s got some frustrations of her own to kill as well.

Playing with Pixie in the garden

Playing with Pixie in the garden

But in the weekends, when I have some more time on my hands, we usually go out for some bigger walks of up to 10-12 in the woods near where I live. This gives me some more time to clear my head, and gives the doggo some direly needed attention. Especially with the weather being a bit colder and some more rain, it’s mostly her diving head first into the first puddle she sees, and then continuing to do so for the rest of the walk. All good fun, but a long haired dog takes a looooong time to dry off :).

Walking the dog at Drieboomkesberg

Walking the dog at Drieboomkesberg

Pixie refusing to come out of a puddle

Pixie discovering some local water

Then, at evening times, I try to switch between doing some reading or studying for things I’m working on, or simply blowing up stuff in a video game. I’m keeping my sh*t together, but I’m frantically counting down the days for when I can go back to my regular activities to get some peace of mind.

In normal times, I’d have a Shin Kyokushin Karate training 2 – 3 times a week, depending on my schedule for that week. It’s a full contact sport, which means it’s physically demanding and exhausting, and I’ve never ever even had as much as a lost thought on stuff I was working on.
Ahhh, to be able to hit some people again 😃

If money was no issue, what would be your bucket list vacation?

I’d have to split it into two, and I’d be torn if I ever had to choose between those two.

I’ve always wanted to go to Japan to visit some of the heritage sites and the Honbu Dojo. Preferably in the same time span as the World Championship to be able to witness the insane atmosphere in the Tokyo Dome with 15000 spectators watching some of the greatest kumite matches our sport has to offer. Followed by a tour of some cities like Kyoto, Nagasaki, .. to make this a splendid trip with some of my karate friends.

Then, competely on the other end of the spectrum, and I’m doubtful that I’ll ever be able to do this. For years, we’ve always got together with some of my Chiro friends (compare it to Boy Scouts, to a certain extent), and do a weekend where we pull the same idiotic stunts we used to do when we were younger. We’re all a bit older now, and many of us settling in to work/life situations. Getting together isn’t as easy as it used to be. For that reason, I’d be thrilled if we were able to get away for a week or so, and go camping with the boys only. Pure nostalgia, to some of the most fond memories I have.

Conclusion

I’m managing to keep things afloat, but I’m ready for things to slowly get back to normal. Especially because I’ve not been able to go the practice 2-3 times a week, I’ve felt some built up frustrations that needed to get out.
Here’s to hoping we can all reconvene soon, and get ready for some more epic stories!

Stay safe, take care!

1 Comment

Power BI Premium Per User – Who’s using it in your tenant?

Before digging into this, let’s be clear about 1 thing. When Power BI Premium Per User (PPU for short) and Power BI Premium v2 were announced at Ignite 2020, the collective user base of Power BI rejoiced, and I am happy to be among them. PPU is going to be an excellent addition to leverage Premium features, without having to pay for an entire P-sku, or deal with spinning up an A-sku. Left, right, and center, we’re seeing interesting new use cases pop up to make the most out of PowerBI. And I .. LOVE IT!

I do have some concerns right now, which will likely be taken away as we get closer to General Availability. An obvious one is that as of now (January 2021), there’s no public details about the pricing. By default, users can assign themselves a PPU license, and start building things as they see fit. Self-Service BI is great, especially when there’s some guard rails in place, and when a Data Culture is actively stimulated. Matthew Roche’s series, goes into this in a splendid fashion.

The other day, I was chatting with one of my clients about Premium Per User, and I gave them the practical guidance to not build any production level dependencies based on PPU features or workspaces, until some of the unknowns have been cleared up. If there’s end users relying on this for their actual daily job, then I’m calling it a production level dependency. Right now, these are preview features, and this client is not actively monitoring changes in the Power BI Landscape.

Shortly after, I got a message that some of their business users did build actual production reports and dataflows in PPU workspaces. And, they were not sure who in the company actually has access to PPU. And that’s where chase down the rabbit hole began 😃

Who can use PPU Features in my tenant?

By default, every user will be able to assign themselves a PPU trial license, and start experimenting. Users can also be assigned a PPU License through the M365 Admin Center, as outlined in the Power BI Premium per user public preview now available post by Chris Finlan.

This behaviour can be allowed, disabled, or scoped to a specific group of users. By default it will look the same, as shown in the image below.

Tenant Setting to allow users to try paid features

Tenant Setting to allow users to try paid features

In combination with the tenant setting for who can create workspaces, this will control the PPU experience, and how freely users can create experiences for themselves. By default, every user in your organisation can create new workspaces, and these will automatically be in the New Workspace Experience (v2 Workspaces).

Tenant Settings for Workspace Creation in your tenant

Tenant Setting for Workspace Creation in your tenant

Okay now, but who’s actually doing something with PPU Features in my tenant?

Attempt 1 – List out PPU Workspaces, get Capacity Information

My first thought was to grab an overview of workspaces that are on Dedicated Capacity using the Power BI PowerShell cmdlets (Get-PowerBIWorkspaces & Get-PowerBICapacity), and filter out workspaces that are linked to a PPU Capacity.

Then, for those workspaces, get the Users and/or Groups that have access to them using an InvokeRestMethod to the Admin API (GetGroupsAsAdmin, expand=users)

This works, to a certain extent. A few of the exceptions I thought of so far.

  • Just because a user is in a group that has access, doesn’t mean they actually have a PPU Subscription.
  • A user could have a PPU Subscription, and have left the PPU Workspaces.

Attempt 2 – Get License Information

We can grab the license information through the MS Online cmdlets (Get-MsolUser), AzureAD cmdlets (GetAzureADUserLicenseDetail), or the Graph API (https://graph.microsoft.com/v1.0/me/licensedetails).
The MS Online cmdlets don’t support authenticating through Service Principals, so don’t actually build any dependencies on this.

All these options give me a nice overview of :

  • Power BI Standard (Free) with ServicePlan: BI_AZURE_P0
  • Power BI Pro with ServicePlan : BI_AZURE_P2
  • Power BI Premium Per User with ServicePlan: BI_AZURE_P3
  • Office 365 E5 Subscriptions

My real concern is that this only lists the users that have been assigned this as a (purchased) Product, as part of the “skuPartNumber” PBI_PREMIUM_PER_USER.
Meaning, only the users that were assigned this subscription by an admin will show up this way. Usually, they will be tied to groups as well, which can easily be exported to get an overview.

Right now, I’m still missing the most important group of all, which is those that have assigned themselves an in-product trial version for Premium Per User from within the Power BI Service.
These are the users that are potentially flying under the radar, and exactly the ones that we want to identify

Attempt 3 – Microsoft 365 Admin Center

After being hinted by Jan Pieter Posthuma to read through the Self-service purchase FAQ | Microsoft Docs, there’s a screen where you can get an overview of paid trials. Alas, this isn’t giving me any results I expect to see, and seems to be tailored specifically to M365 products.
My own user has a trial license from within the Power BI Service, and this also doesn’t show up in the personal overview of Subscriptions & Licenses. Which explains to me why it doesn’t show up in the Admin Center either.

Attempt 4 – Track the Power BI Activity Log

Similar to the “OptInForProTrial” and “OptInForExtendedProTrial” activities that appear in the Power BI Activity Log when user assign themselves an in-product trial Power BI Pro Subscription, I was hoping to see the same for Power BI Premium Per User subscriptions. I’ve been using this method to track users getting a Pro Trial, and potentially will be needing an actual paid license within 60 days.

I assigned a test user an in-product trial subscription, and grabbed the activity logs the day after. This activity actually also shows up as “OptInForProTrial”, which made sense as soon I actually read through the post from Chris Finlan again, specifically the How To Get Started, Existing Users section.

Existing Free users – You will be given access to the PPU capabilities during the public preview period if you choose to opt-in to a Pro trial as an added benefit.  Since it will happen behind the scenes, the UI in the portal will still reflect that you have a Pro trial, but you will be able to access all the PPU features.

For the set of customers that have disabled their in-product trial experience, your Microsoft 365 tenant admin can opt-in to a trial experience there.

Going out on a limb, I’m assuming the in-product trial functions the same way for both Pro and PPU subscriptions, and this is why there’s no distinction possible in the User Interface or Activity Logs. Likely we’ll see some changes be made in the future, but I’ve no guarantees to back this up.

Bringing the attempts together

To circle back to an exhaustive overview, I’m rolling with a combination of Attempts 1, 2, and 4.

  • Getting the actual licensing information will give us the details on the users that have been assigned a license by an admin. (Attempt 2)
  • Monitor the Activity Log for “OptInForProTrial” activities

Right now the trial experience is the same for Pro and PPU Subscriptions, which means this is about as exhaustive as the list is going to be.  If you don’t have an extraction set up for Activity Logs, I suggest you do it as soon as possible.

Then, having identified the PPU Workspaces in Attempt 1, we can specifically track these in the Activity Log. If we’re seeing some specific activities in there by certain users, this could point out there’s usage going on beyond testing and development.

Conclusion

Getting this question, I underestimated the steps to get a complete answer, which is why I thought it could prove to be useful for other people. My biggest learning here, was that free users can actually assign themselves a PPU Subscription, but that it looks like a Pro Subscription in the user interface. Definitely something to keep in mind when determining your licensing strategy for Power BI.

Most importantly, this outlines once more why grabbing and storing the Power BI Activity Logs as soon as possible is crucial to understanding the usage in your tenant, and being able to act on that. Looking for an example on how to extract these logs? There’s some examples on Github by Melissa Coates, Alex Powers, or Aaron Nelson which can easily help you get started.

The hardest part to track are the in-product trials, and there’s varying opinions on these. I’ve seen organisations disable the “Allow users to try paid features” tenant setting, where users always have to be assigned a subscription, meaning they have to be available as well.
Additionally, you want to make sure your corporate workflows are fluid and fast enough to process incoming requests for subscriptions.

It’s all about walking the fine line between Self-Service BI and Managed Corporate BI, and finding the strengths your organisations can play to.

Thanks for reading!

, , , ,

3 Comments

A (Possible) Communication Plan for Data Platform Events

Wall of Text, Incoming!

The last few years, I have been one of the lucky volunteers to handle outbound communications with speakers and attendees for our event, dataMinds Connect. I am a stickler for communication, so I try to have our team put a lot of effort into this.
When all was said and done after the events, it has been wonderful to receive compliments about it.

I’ve also had a few people reach out to us, to ask for our ‘communication plan’. It has never been more than a few bullet points jotted down into a shared OneNote, so I decided that now is as good as time as any to write it out a bit more detailed.
Do keep in mind that this is a theoretical plan, and we would have a very boring community if everything always went according to plan 😃. For obvious reasons, I’ve left out some of the more private details specific to our event.

With PASS dissolving soon, I think there will be more events that need to figure out what to do. With this, I hope to reach other organisers in our Data Platform space, and have it be of some value to them. Likely, a lot of this can be reused for Tech event outside our Data Platform space, but I have no practical experience do back up that statement.

You will never see/hear us claim that is ‘the golden way’ of doing things, and that everyone should follow our example. This is a compilation of some of the things we have done in the past, and reiterated on over the years, specific to our events. And yes, we have had mistakes in the past, that needed to be improved on, that is just how things go.

It is more than likely, that you are will need to tailor specific parts of this to you own requirements, or add some things for your own event.

But as always, if you have some questions or remarks, do not hesitate to reach out.

Who are the stakeholders?

To me, there are 4 main groups of stakeholders for our outbound communication, and 1 group with ‘others’. Each one of these groups has their own need for information, with a timing attached to it as to when they need to receive this.

Main groups :

“Other” group, really depends on what event you’re running :

  • Venue
  • Catering
  • Suppliers
  • Other organisations
  • ..

What do we send to whom?

I won’t lie, for dataMinds Connect we work together with an external partner (Sidekick), to help us write the communications to attendees, and do some of the graphical/visual stuff for us. I think there’s a big difference in communicating in an informative way to speakers, volunteers, and partners, as to trying to convince attendees to join your (paid) event. For that specific reason, we get people to help us.

Throughout the years, I’ve found that sending communications to all the groups, except attendees, works best if you send them short bullet points with actions needed, or something they have to read. Then, if they want more details, they can always read the more descriptive version below.
I’m aware that I have a tendency for Walls of Text (this post is Exhibit ZZZ514), which often leads to parts being neglected or not read at all.

Mind you, just because your message is clear and concise, this does not mean everyone will read (and respond to it). Cat herding is a large part of organising events, so you need to prepare yourself for it

How do we contact these groups?

For us, we’re using a mixed approach. All communications with attendees happen through MailChimp, with specific segments for the audiences we’re targeting, as we’ve built up an extensive attendee mailing list in our 14 years of existence.
When communicating with volunteers, partners and/or speakers, it happens through plain old e-mails, sent from our personal dataMinds accounts.

When do we need to send them the relevant information?

Again, this really varies for the event you’re planning, if it’s online/in-person, ..
I still base myself on in-person events mostly, as that is what I hope to return to over the course of the next year.

When I’m referring to dates, I’ll refer to them as “T-x”. You don’t have to agree with the timings, these are just the numbers I have in my head. Whether we actually achieve those dates, I’ll graciously leave in the middle. 😃
For example:

  • T-20 means 20 days prior to the first day of your event.
  • T+5 means 5 days post the last day of your event

At a glance

For your convenience, I put everything into a single table to get an overview. You can use the subjects with links to navigate to that specific segment.

Target Group Subject When
Speakers Call for Speakers Open T-210
Speakers Reminder : Call for Speaker open T-115
Speakers Session Selection T-100
Speakers Reminder : Session Selection T-90
Speakers Speaker Update 1 T-60
Speakers Precon Speakers – Room requirements T-30
Speakers Speaker Update 2 T-10
Speakers Thank you T+1
Speakers Reminder : Session Materials T+5
Speakers Session Feedback T+10
Partners Call for Partners Open (+ Call for Speakers) T-210
Partners Reminder : Call for Partner open T-120
Partners Partner Update 1 T-60
Partners Partner Update 2 T-10
Partners Thank you & Raffle Winners T+1
Volunteers Call for Volunteers Open T-30
Volunteers Practical info T-10
Volunteers Thank you T+1
Attendees Announcement (Date + CfS) T-210
Attendees Schedule T-80
Attendees Highlight (Tracks, Newcomers, Speakers, Keynote, ..) T-80..-7
Attendees Final Update T-2
Attendees Thank you T+1
Attendees Session Materials T+5

Speaker Communications

Call for Speakers

Before you launch your Call for Speakers, there are a few things you should consider. In general, it’s a lot easier to get information from people, when they’re submitting to your event, rather than when they’re selected and they need to respond back in an e-mail to you.

Some of the things to consider, for online and/or in-person events

  • Do sessions need to be recorded up front?
    • Will these be made available for public use, or attendees only?
    • Will these be made available at a cost, or for free?
    • Who has ownership of these recordings?
  • Will you be recording sessions, and how are they going to be distributed after?
    • Will these be made available for public use, or attendees only?
    • Will these be made available at a cost, or for free?
    • Who has ownership of these recordings?
  • What time zone/location are they located in (mostly applies to online events)
  • Are you handing out optional Speaker Shirts (Shirt Size, Unisex/Male/Female model, do they want one Yes/No, ..)
  • Are they a new speaker, or want some help on preparing their session?
  • Are they a seasoned speaker, and willing to help out new speakers?
  • Add a notes field, where speakers can input anything they feel is important

The list will need to altered, depending on the specifics of your event. In any case, try to think about as many of these things as possible, to get all the information you need, as soon as you can.

Our Call for Speakers for an in-person event usually ends about a bit more than 3 months prior to the event. Specifically, to allow speakers to plan their travel when ticket prices aren’t through the roof yet. For a virtual event, you can move back the selection date about a month and a half. That is still a long time yes, but there are some speakers out there that submit new sessions, and starting tweaking them as soon as they get selected.

Call for Speakers opened (T-210)

When your Call for Speakers is ready to be released into the wild, that’s when you send out a short e-mail to your speaker list to inform them about it. My expectation is that we won’t be seeing in-person events in the next 4 – 5 months, but I’ll still discuss the options for in-person and online events.

We typically include :

  • Date(s) of the Event
  • Link to the Call for Speakers
  • Date when the Call for Speakers is Closing
  • If this will be an online or in-person event
  • If you’re open to precons or not
  • Session Types and Length
  • Brief explanation of the different tracks (definitions vary, hence this is a good idea)
  • Content we’re looking for
  • Do sessions need to be prerecorded?
    • Will these be made available for public use, or attendees only?
    • Will these be made available at a cost, or for free?
    • Who has ownership of these recordings?
  • Will sessions be recorded during the event, and how will they be distributed after?
    • Will these be made available for public use, or attendees only?
    • Will these be made available at a cost, or for free?
    • Who has ownership of these recordings?
  • Ask for the speakers to amplify the reach
  • A clear reference to our Code of Conduct
  • If it’s an in-person event, list out some events in the same time span and region. Speakers coming from across the pond will always try to combine a few events. Mainly for this reason, I’m really happy with the way we communicate with the Data Saturday Holland folks.

Additionally, there’s new community initiatives popping up, like the Call for Data Speakers by Daniel Hutmacher. Sessionize is a popular option for Call for Speakers, and they’ve recently introduced a discovery system for speakers and events.
These are quick no-brainers to get some extra reach on your Call for Speakers.

With about two weeks left on the Call for Speakers, it might be a good thing to specifically target a few speakers that have submitted/presented before, but have yet to do so on this Call for Speakers.

Session Selection (T-100)

When your Call for Speakers is closed, it’s really important to send out selection feedback as soon as possible, to respect the time other people put into it. In the past, we’ve had our selection meetings 3-5 days after the Call for Speakers was closed. Everyone prepares in their own way, but I try to have gone through all the abstracts, and define my personal lists of sessions I’d like to select.

When sending out the selection feedback, thank the people for the time they’ve taken to submit, and that they can always request feedback on the specifics of your decision. And please, whatever you do, send out a message to every speaker that submitted, so they know their session was not accepted.

Speakers that get selected will get more information in the following Speaker Updates. How many you send, and when varies mostly on the input you need, and what you need to inform your speakers about. We tend to keep it to two, unless there’s other input we need to send out.

It does make sense to inform speakers if you’re planning on hosting a pre/post event extracurricular activity (ie. Speaker Dinner, Guided Trip/Tour, ..), to accommodate their planning.
Most likely, you will have to chase down a few speakers to get confirmation that they will present at your event.

Especially for online events, it really pays off to send out a personalised calendar invite to your speakers, with the information for their session. Yes, it does take some time, but it drastically reduces communication mishaps due to timezones, dates, overlaps in their own schedule, ..

Speaker Update 1 (T-60)

Your event is still a long way off, but it does pay off to inform well ahead of time. If you’ve informed about the important topics in your Call for Speakers briefing, this is mostly a repeat.

  • Will you ask speakers to share information about housekeeping rules, sponsors, .., before starting their talk?
    • A common construction is a limited set of slides, but there are viable alternatives
  • Are there other specifics speakers need to keep in mind?
  • What platform are you using, in case of an online event?
  • Remind the speakers to make their session materials as accessible as possible
  • Remind the speakers of your Code of Conduct
  • What extra curricular activities are you hosting?
  • Some updates about attendees, venue, ..

Precon Speakers – Rider (T-30)

When having precon sessions planned, it’s interesting to ask your precon speakers for any specifics they need/want to have their room set up. Some speakers like a classroom, or U-style for the tables/chairs, some would like flipcharts and whiteboard markers to be their. Some would to have an ice cold beer delivered to them at 3PM in the afternoon. Whatever it is that helps your precon speakers present their session in an optimal way, is good to discuss this with them up front, so your venue is aware of this.

Final Speaker Update (T-10)

This is your final update for your speakers, and now it’s mostly reiterating what they’ve been told a few times

  • When/Where they’re expected to arrive
  • What extra curricular activities you may have planned
  • Details on the online platform or physical rooms they’ll be using
  • Reminder of the slide deck template you may or may not have, and to make content as accessible as possible
  • Reminder of your Code of Conduct
  • Ask to use their social reach to amplify your event
  • Ask to provide session materials ahead of the event, or directly after their session.
    • Having a dedicated place for this, that’s communicated up front really helps.

Thank you + Reminder for Session Materials (T+1)

The day after your event, it’s time to explicitly thank your speakers for their participation in your event. You’ve probably already done this directly after their session, but it won’t hurt to do it again.

  • Ask for their feedback on other sessions, your event, ..
  • Inform on the timeline of recordings (if any), session feedback, ..
  • Remind speakers to send session materials, if they’ve not done so already
  • Some general statistics about your attendees
  • Inform on a possible next event, and Call for Speakers

Session Feedback (T+10)

To close off communications to your speakers, send out a brief update on the session feedback received for their session, and how many attendees were in their session.

Optionally, you can include the link to the collected session materials. Speakers will most likely have attended other sessions as well.

Partner Communications

Call for Partners opened (T-210)

You’re contacting partners in order to secure funding for your event. Typically, this needs be budgeted on their end before they can commit to anything. Having a recurring event makes this a bit easier, as you’ll have recurring partners too. Finding new partners will be a lot easier if it’s well ahead of your event.

Your main point of update will be your event prospectus (or partner file), to inform potential partners on the options that are available, at which costs.

And yes, you’ll most likely have to send out a few reminders to get people to respond.

Partner Update 1 (T-60)

Similar to the Speaker Updates, you’ll want to inform your partners about any specifics on your event platform, (online) expo hall, attendee raflle, and any particular things you may have. Partners will need time to prepare something on their end, and it’s possible your event is not the only one they’re participating in.

For example, we gave our partners the option to display a ‘commercial’ video during the breaks in our online event. A video like this has to be prepared or fine tuned.

Outlining once more how partners will be able to interact with attendees, how they gain attendee contact details with respect for GDPR, these are all specifics they need to be aware of, well ahead of the event. Usually, it’s also a good idea to have partners inform you of the raffle prizes they’re planning on handing out.

Partner Update 2  – Final Update(T-10)

This is your final update for partners, where you give them all the practical details needed for the event itself.

  • When can they access the venue to set up?
  • When do they have to be out of the venue?
  • Who can staff their booth, and thus needs a ticket/account to get in.
  • What are the hours the expo hall is open?
  • ..

Thank you & Raffle Winners (T+1)

Partners provide you with funding for your event, as you likely cannot cover everything with attendee tickets alone. Meaning, they play a very big role in the longevity of your event. Explicitly thank your partners, and send over the contact details of attendees that opted in, and their raffle prize winner as soon as you can after the event.

This will help your partners close off the administration on their end sooner as well, and they’ll be ever so grateful for it.

Volunteer Communications

Call for Volunteers opened (T-30)

Most likely, you’ll need volunteers for a wide range of tasks in your event. Be it moderating online sessions, staffing the reception booth at your in-person event, room monitoring, .. There’s always plenty of things that need to be done, and more hands definitely makes this a lighter task.

When opening up a Call of Volunteers, you can decide to go the private route by contacting people you know, or you can set up a public call. Both have their pros and cons, but I feel a combination of both works best. You have your ‘regulars’, that know their way around, and some fresh blood coming in as well.

Describe the different tasks you have, how many people you need, and when they need to be executed. This way, people can sign up for the tasks that interest them.

Practical Information (T-10)

Brief your volunteers with :

  • A clear expectation of their tasks
  • A timetable of who’s expected when/where
  • Who they can contact in case of questions/remarks/issues/..
  • Some general information (where’s the speaker room, session rooms, expo hall, ..)

I once saw the volunteer briefing Data Saturday Holland sends out to their volunteers, and I was quite impressed with that.

Thank you (T+1)

Your volunteers have put in their personal time, to help make your event a success. The least you can do, is send them a heartfelt thank you.

Attendee Communications

Event Announcement + CfS opened (T-210)

Inform your prior attendees, and/or mailing list that your event will take place, and that the Call for Speakers has been opened up. It’s always nice to have some of your local attendees step up to the Call for Speakers, so they’ll definitely benefit from being informed early on.

Schedule Announcement + Registration opened(T-80)

To officially open your registrations, it pairs nicely with the announcement of your event schedule, or some names for precon sessions. Most people only decide to attend a specific event when they know who will be presenting. Thus, having this information available as soon as possible, can definitely benefit you.

Especially when you have a paid event, some attendees have to ask permission and/or funding from their employer. This can take some time, so it pays off to give them all the time you can.

Highlights (T-80 to T-7)

Depending on the specifics of your event, you’ll want to spark interest in some of the things you have to offer. Convincing people to attend your event usually takes a few tries. For your event, it helps to define some things you want to highlight, and then create a schedule for this.

Some examples:

  • Your (closing) keynote speaker
  • A specific track you introduced
  • New speakers you’re introducing
  • Spotlighting your different session tracks
  • Spotlighting some of your precon sessions

Written down very simplistic, and exactly the reason why we have someone help us. Some examples here and here.

Final Update (T-2)

As we updated our partners, speakers and volunteers of the final practical details, the attendees should get similar information.

This is where you inform your attendees about:

  • How they can reach your (online) event venue
  • Parking, public transportation, some things to keep in mind
  • Any specific details on what they need to before attending your event
  • When the venue opens, and closes
  • Remind the attendees of your Code of Conduct, and who they can contact with anything related to this.
    • It’s helpful to not only have males as a point of contact

Thank you (T+1)

Specifically thank your attendees for attending, and inform them of what they can expect in the coming time.

  • When/Where will session recordings be made available
  • When/Where will session materials be made available
  • Allow attendees to give general feedback on your event, schedule, communications, ..
  • Remind attendees to provide session feedback through the medium you’ve selected

Session Materials (T+5)

It’s possible not all the session materials have been uploaded so far, but it is important to allow attendees to review the materials they’ve seen at your event. By sending out a part of the materials you have, attendees can already start their process. Then mention that more content will be added as we move along.

To conclude

A long article, I know .. Yet, I hope it can prove of use to anyone putting on a Data Platform in the near future.
Good luck on your endeavours!

1 Comment

So long, 2020!

Looking back ..

I’ll just put it out there .. 2020 was a weird year for mostly anyone, and we’re collectively rooting for 2021 to pick up the slack. Looking back, I realise that I have hardly got reasons to complain, and I count my blessings for it. What started out as a year filled with plans and ideas turned out, a little different..

The extracurricular goals I set out for myself boiled down to this :

10 -15 in-person sessions presented, with at least 8 outside of Belgium

I landed at 5 sessions presented in-person, with the last one being at our own SQL Saturday Belgium. Out of those 5, with the only talk I held abroad was at the Scottish Summit in Glasgow. I had good fun with all of these talks, and will always prefer the in-person ones. When they’re allowed again..

However! With most of the events, user groups and conferences going virtual, it’s been easier than ever to submit anywhere in the world. I ended up at a total of 15 online talks held, spread out across Europe and North America. I probably could’ve done more sessions, but I had to put limits on myself, to keep my other activities from suffering from this.

My speaking activities for 2020

My speaking activities for 2020

 

 

 

 

 

 

Complete the DP-.. certification series for Azure Data Engineering

In short. I failed, badly .. Having read too many of those stories where proctored exams turn out to be a huge time sink, with nothing to show for it, I always tried to plan my exams in the test centre at our company HQ in Belgium. I had to cancel and move it around a few times, due to our country swinging from 1 lockdown to the other.

Right now, I have both DP-200 (Implementing an Azure Data Solution) & DP-201 (Designing an Azure Data Solution) planned for January 4th as a proctored exam. Here’s to hoping I don’t have to go sit in the restroom to take it .. Depending on how these go, I might get some more exams planned.

On the other hand, I did manage to sneak in both DA-100 (Analyzing Data with Power BI), and PL-900 (Power Platform Fundamentals) in the summer break, so I did get a few certifications this year.

Pick up blogging again, averaging 1 post a month

Seeing as this is the first blog post I actually publish this year, let’s just conclude that I didn’t get this one. I kept putting it off for various reasons, and never really got into the writing mood. I’m usually the one that writes the Mailchimp newsletters for dataMinds, and I kept it at that.

I’ve got some ideas and rough drafts noted, mostly as supporting materials for my talks. We’ll see if I get in the zone in 2021 😊.

Keep aiming for the same high standard in our dataMinds User Group activities (especially when it’s online)

Personally, I’ like to think that our dataMinds Team nailed this one. We set out the year planning for 10 user group evenings, 1 SQL Saturday, and dataMinds Connect. What we achieved as a team, is something I’m absolutely proud of!

Up until March, we were perfectly on track. We had user group sessions planned until April, with a good mix of speakers and topics. SQL Saturday Belgium was perfectly on target for attendees and speakers. A few days after, Belgium went into total lockdown..

Online User Group Sessions

At first, we were hesitant to do virtual user group sessions, as you pretty much saw a new user group or conference spawn up every week, and now they were all hitting parts of the same target audience. Combine that with our little experience with streaming, OBS, .. We decided not to go virtual for the time being.

Come April, we noticed that our own Belgian audience wasn’t really picking up on most of the international online activities, and we decided to give it a try. I’ll never be a YouTube star, and really need to learn there’s more than 1 way to mute yourself, but it actually worked out rather well. Since then, dataMinds had 12 online user group evenings with 21 different speakers. Attendance from our local attendees has been similar, with new faces popping up every time, but due to anyone online being able to jump in, attendance has actually been a lot higher for us. Most likely, we’re going to keep running online events only until the summer break in Belgium. As always, we’re assessing the situation with the information we have, and going from there ..

Our dataMinds sessions for 2020

Our dataMinds sessions for 2020

 

 

 

 

 

 

dataMinds Connect

Our pinnacle event, dataMinds Connect, would also have to change drastically, as spending 2 days cooped up with 500 people in Mechelen would not be possible, nor advised. Early on in April, we decided to move to an online event and start planning for that. In hindsight, that has been an excellent decision for us. Since we had no restrictions on physical rooms, and their respective sizes, we decided to bring in some extra tracks for more session variation. We had already planned for the Expert Track in the in-person event, so we added Go To Cloud as the second extra track.

dataMinds Connect 2020 was wild for us, as we had no idea what we were getting into, and there were little to no references at the time being. We ran it a low budget, and free to attend. In the end, we stopped registrations at 1800, and saw a little over 1300 people effectively show up during the day. With 48 sessions and 53 speakers during the day, and our closing keynote by Rohan Kumar, we can look back at a successful event, and we’re darned proud of it.

Storm myself through the exam for my next kyu grade in Shinkyokushin Karate

2020 would have been the year that I was going to prepare myself to take on my next kyu grade. These exams usually take a very long time, and are designed to drain you physically and mentally, to see how well you can still react to things. But, as our training time has pretty much been slashed due to lockdowns, my exam got moved away until 2021. A pity, but it does give me more time to prepare.

Anything else to note about 2020?

Absolutely! 2020 was the year in which I was awarded the Data Platform MVP Award, and was able to attend the virtual MVP Summit 2 weeks after. I can honestly say I didn’t expect to receive that illustrious e-mail on Sunday March 1st 2020. That day was the final lap of some of the worst planning I’ve done to date, and it definitely did get me through that last day.

To illustrate.. I had a boys weekend (we all know how that goes ..), from which I directly went to a week of being technical crew for a camp with 90 teens (16-17 year old). Driving up to Mechelen on one of the evenings to go present a session at the Thomas More College. Leaving early on Friday to drive to Schiphol (150km), so I could go present at the Scottish Summit. Getting up at 2AM, to get the 5:20 AM flight to Schiphol, to then drive to La Louvière (270km). To then spend the afternoon judging kids running the same kata, over and over again. Then drive home again (130km). To conclude, 9 days with too little sleep, and a lot of fun. I don’t think I’ve ever slept so solid as that night 😂

What followed was the virtual MVP Summit, which was a bit of an eye opener for me. Throughout the year, I’ve had more opportunities to connect, share, and learn. The MVP Program has already allowed me the soak up a bunch of interesting knowledge, and meet some international people. For that, I’ll be ever so grateful.

2020 has been the year I had a lot of ‘firsts’ in my speaking career. I presented at the Community Summit, SQLBits, PASS Summit, SQLDay Poland, DBCC International, a few SQLSaturdays (Belgium, Göteborg, Montreal, Atlanta BI, Slovenia), and a few other events. Yes, they were all online, yet I still liked presenting (or recording) for all of them.
That said, I can’t wait to actually go back there in-person, as it really suits my style a lot better.

2020 has been the year where I met a lot of new people during activities I would not have been doing otherwise. Every week, there’s livestreams and YouTube channels happening with content that helps keep me up to date. To name a few, Guy in a Cube, Two Alex’s, Advancing Analytics, SSBI Polar, Geeks on Screens with Coffee, ..

My Danish friend, Just Thorning Blindbaek, started organising the Power BI Quiz (YouTube Playlist) on a regular basis and I’ve actually learned a lot whilst doing these quizzes. I’ll never forget how terrible I performed on Will Thompson’s Power BI Desktop quiz, and still haven’t figured out how I narrowly won Season 2.

Before I forget ..
2020 has also been a year where I had the chance to work with some lovely new clients, who definitely put me up to a few very exciting challenges to figure out. I love that EUREKA! moment when figuring something out after spending a good amount of time analysing it!

What about 2021?

I’ve not defined any ‘set in stone’ goals for the next year, as it’s pretty hard to tell what will happen right now. More or less, it’s a continuation of the soft goals I had for 2020.

However, .. I would love to get my hands on more with some Python and PowerShell. I’ll have to figure out how I can include that some more in my activities.

As for dataMinds, we’re rooting for an in-person dataMinds Connect on October 11th & 12th. If that’s not possible, we want to take our decision early on. We’re also trying out something new instead of SQL Saturday Belgium, called dataMinds Saturday. For this first edition, we’ve decided to limit the speakers to people residing in the BeNeLux. This way, we hope to nudge some people in an ‘aggressively friendly’ way to get into speaking. As a local user group, fostering local talent is still one of our primary goals.

In any case, from me to you, all the best wishes for 2021. I hope it’ll be a year we can all look back on with joy.

Leave a comment

Custom Year To Date in Tabular Model using DAX

The situation

Some time ago, when working on a pilot project for a client, I was presented with an interesting requirement for the SSAS Tabular Model + DAX, being a non-sequential way of looking at dates in the previous year. I was working on transactional sales data, which required some of the everyday hierachies (Date, Product, Geography, ..) and measures (Year to Date, Month to Date, ..). Things that are all brilliantly document by the SQLBI.com guys at DAX Patterns.

Based on those sales transactions I had to define time-related calculations on both a regular calendar, and a fiscal calendar. Nothing some trial-and-error, and a quick glance at the Time Patterns won’t solve. However, given the extra requirement of comparing the previous year in a non-sequential way, I had some thinking to do ..

Non-sequential way?!

First off, let me explain what comparing in a non-sequential way means. When comparing the previous year, usually a date gets compared to the same date in the previous year (ie. April 18, 2015 to April 18, 2014). In this situation it could be totally different, as shown in the image below.

Every month, the last day of that month gets compared to the first date of that month in the previous year, and pretty much all other dates are compared to (year – 1) + 1 day, unless there’s a holiday in that month. For this example I’ve chosen April, because the Easter holiday is in it, and it adds some complexity to the mapping. Sales transactions usually are a lot less on holidays, so it makes sense to compare those to each other.

Belgium (The small country known for waffles and beer) has an official holiday for both Easter, and Easter Monday, which means a lot of companies (and even more so for non-industry companies) don’t have office hours on those days, and a lot less sales transactions in the traditional way.

However, there was a slight mismatch on my provided data. I only had a date mapping available for 2014 and 2015, while my demo data pretty much stops at 2014. The mapping is done on a manual basis, and I altered it rather straight forward by deducting 2 years, rather than making it myself for those time periods. I pretty much created my own holidays, that’s how special I am!

The setup

Talking about mappings is all fun and games, but we all want to see the solution in the end. Talking about sales transactions, I couldn’t think of a setup any better than AdventureWorks to explain how I did it. For training and demo purposes I have access to an Azure VM, which allows me to do some experimenting.

One of those experiments is an SSAS Tabular Model based on the Internet Sales. I chose not to use the demo models, but create one myself to get some practice while I’m at it. An extra addition I made, is using my own date dimension over the standard AdventureWorksDW date dimension. It contains a lot, if not all, the same fields and some more, which help me perform DAX-calculations in an easier way. Personally I prefer expanding the date dimension with a few extra fields, over having to create extra complexity in the measures. For this specific case I added DayOfSale (OrderDate), and DayToCompare to the date dimension.

The solution

The first thing I had to take care of, was making sure the comparison to the previous year on the day level is done correctly. The pre defined time-intelligence are pretty much useless, as they require a sequential date table without gaps. Using the DAX-calculation, shown below, it worked like a charm! (Thanks, DAX Formatter!)

DAX_daymap_PY

With the parallel period lookalike out of the way, I still had to make sure that the ‘Previous Year to Date’ calculation respected the date mapping. I had some trial runs, in which correct date was used, but the running total wasn’t calculated in the correct order. This was mainly because I still took the regular total as a basis.

When I realised that I had to use the custom Previous Year calculation, I figured it out pretty quick, and I was able to perform a custom (Previous) Year to Date, on non-sequential dates. I’ve done some more experimenting, and by using this kind of calculation it’s possible to compare dates in multiple months, quarters, years, .. The image below shows the calculation that performs the Previous Year to Date in the correct way.

DAX_daymap_PYTD

Verifying that the results matched with the correct dates was very important, so I used my buddy Excel to get an overview.

DayMap_Result

The conclusion

This little snippet was particularly amusing for me, because it forced me to go beyond the basic time intelligence functions in DAX. I was pretty pleased with myself for solving this. A few days later I stumbled upon an article by Marco Russo on Custom Year-Over-Year calculation in DAX, which pretty much explains the same, I just take it a step further by calculating the Year To Date. I strongly encourage you to read the article, as it’s a real beauty.

This wraps up my official first blog entry, and I sure do hope you’ve enjoyed it! All comments are more than welcome, and I hope you’ll visit again 🙂

, ,

Leave a comment

Hello World!

Welcome to my blog!

Pondering long and hard about starting to blog and thinking of lame excuses for not doing so, I finally decided to step up to the plate. I’ve been following the SQL Community for about 4 years now, and I’ve learned heaps by doing so. By blogging I hope to contribute the community, and maybe even inspire a few people.

Working as a Data Insights Consultant, I spend my days working with data, and using tools to make working with that data a lot easier. I pretty much work exclusively in the Microsoft Data Platform stack, which is expanding at an insane velocity. I’m tech savvy by nature, so I really enjoy trying to keep up with all the new shiny toys (Cortana Analytics Suite anyone?).

I’ll find inspiration for my topics in my day-to-day job, and any issues, oddities or challenges I encounter, but most of all about data related things I’m trying to grasp in my spare time.

Enjoy reading!

Leave a comment