Effectively Integrating FHIR Data from Azure Health Services

This blog is intended to be a follow up from the SQL Saturday 2022 in Oregon & SW Washington. In that session I presented an introduction to FHIR and JSON data produced from the Azure Health Services API’s.

With the recent updated mandates in the healthcare environment in the United States, Microsoft has continued to expand its capability to support the FHIR standard for integrating healthcare data. While the standard is well documented and Microsoft’s capabilities are expansive, it falls on data professionals to interpret that data and build meaningful reports and produce meaningful insights from the data as it is collected and integrated across environments. This requires a good working knowledge of JSON in SQL to manipulate complex data models. In the session, we did a short review of the FHIR standard and the overall implementation of FHIR in Azure. From there we reviewed the resulting data in the data lake and in Synapse. That was followed up with an overview into the heart of complex SQL using JSON functions in Synapse. Whether or not you are active in healthcare today, this will be an enlightening session on how to use JSON SQL functions within the Azure SQL platforms.

What is FHIR and why should you care?

FHIR stands for Fast Healthcare Interoperability Resources. this is the latest specification for interoperability in healthcare produced by HL7. To be clear the word fast has nothing to do with performance, but more about the ability to implement and integrate data quickly. With the latest regulations around the world in health care, this standard is the established standard for integrating healthcare data and we’ll continue to be on the forefront of this work. If you do any work in health care, you will need to understand FHIR because you will likely run across data formatted to the standard from many different sources.

FHIR is very well documented. In many ways when the standard is properly followed the JSON documents or other supported formats are effectively self-documenting. It is commonly understood that the core FHIR specification handles about 80% of the use cases in healthcare. It is designed to be flexible so that it can support specialized needs within regions or healthcare areas. For example, in the US there is a need to support race and ethnicity. The U.S. Core Implementation Guide provides guidance on the specification enhancements to support this need for U.S. healthcare organizations. You will find similar support for other countries as well as specific implementations for healthcare vendors such as Epic.

Neither the notebook, the presentation, or this blog is expected to be and exhaustive coverage of FHIR. before we move on to some of the other implementation pieces, it is important to understand one key aspect of FHIR is the basic building block called a resource. A resource is the core exchangeable content within the specification. All resources share the following characteristics:

  • A common way to define and represent the resource including data types and patterns
  • A common set of metadata which can be discovered easily
  • A human readable part

For more detailed information on the supported resources and other details around FHIR implementation, you should visit the following website:

Azure Health Services and the FHIR API

I will not be digging into a lot of the health care services information nor the FHIR support within Azure in this post. The important things to understand is that Microsoft has made a concerted effort to support this specification which includes technology and architectures for the extraction of data from various healthcare systems which will then use the FHIR APIs to standardize that extracted data into the FHIR spec typically in JSON files in the data lake. Because of the standardized format, Microsoft is able to supply a set of common schemas that can be used in serverless synapse to create external tables and views to accelerate the implementation and usage of data produced from the APIs. It is from this starting point that we are able to start working with the data in reporting and analytics solutions.

At this point I want to put a plug in for the company I work for. If you're interested in learning how Azure health services and the FHIR specification can be implemented at your company, we have FHIR Quick Start and FHIR Data Blueprint solutions. These solutions have been used by many other customers to achieve high levels of integration in their health care data estate. If you're interested in learning more, please reach out to us at: https://3cloudsolutions.com/get-started/

Working with the data from the FHIR API using JSON in SQL

As noted in the previous section, Azure Health Services comes with setup serverless tables and views to be used with the extracted data. However due to the complexity of FHIR, there are a number of columns within those tables and views which still contain JSON snippets. For example, there is one field for name which has several objects and arrays to support the specification. You cannot simply select the name from the table and use that as you move forward. There are many different fields like this throughout the data. For the rest of this blog and in the notebook, we will work through a number of scenarios to build a view of the patient resource that can be used for simple reporting. This view will contain a few JSON functions from SQL Server and solve simple to complex scenarios in the illustration.

The functions we will be using:

  • ISJSON
  • JSON_VALUE
  • OPENJSON

In addition to these functions, we will also be using the CROSS APPLY operator in SQL to join our data with relational data.

The examples in the notebook are built on the tables resulting from working with the Azure FHIR API. I am unable to provide a sample of the data to use with the set of information in the notebook currently. However, the SQL will work if you have your own FHIR implementation and a Patient resource to work with. rather than rewrite the entire contents of the notebook in the blog post, here is a link to the notebook.

If you plan to implement this in the same way, you will need Azure Data Lake, Azure Synapse serverless, and Azure Data Studio. the notebook can be opened in Azure Data Studio. If you are unfamiliar with working with notebooks inside of Azure Data Studio, you are not alone. Check out this post which discusses how to implement your first notebook in Azure Data Studio.

Building our view and SQL with JSON functions

If you decide not to open the notebook but are curious what the view looks like here is a finished product that we created in the notebook.

SELECT TOP (20) p.resourceType + '/' +  p.id as PatientResourceID
    , p.resourceType as ResourceType
    , p.id as ResourceID 
    , cast(p.[meta.versionId] as int) as VersionID 
    , cast(p.[meta.lastUpdated] as DATETIME2(7)) as LastUpdated 
    , JSON_VALUE(p.[name], '$[0].family') as LastName
    , JSON_VALUE(p.[name], '$[0].given[0]') as FirstName
    , cast(p.active as bit) as IsActive
    , p.gender as Gender 
    , CAST(p.birthDate as date) as BirthDate
    , CASE WHEN p.[maritalStatus.coding] is null THEN NULL
           WHEN  JSON_VALUE(p.[maritalStatus.coding], '$[0].system') = 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus' 
                    THEN JSON_VALUE(p.[maritalStatus.coding], '$[0].code')
           ELSE NULL
           END as MaritalStatus 
    , CASE WHEN JSON_VALUE(p.[address], '$[0].use') = 'home' THEN JSON_VALUE(p.[address], '$[0].state')
            WHEN JSON_VALUE(p.[address], '$[1].use') = 'home' THEN JSON_VALUE(p.[address], '$[1].state')
            WHEN JSON_VALUE(p.[address], '$[2].use') = 'home' THEN JSON_VALUE(p.[address], '$[2].state')
            WHEN JSON_VALUE(p.[address], '$[3].use') = 'home' THEN JSON_VALUE(p.[address], '$[3].state')
            ELSE NULL
            END as HomeStateOrProvince
    , e.Ethnicity
    , r.Race
FROM fhir.Patient p
INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
    ON p.[meta.versionId] = cp.currentVersion
    AND p.id = cp.id
LEFT JOIN 
    (SELECT p.id
        , CASE WHEN JSON_VALUE(ext.value,'$.extension[0].url') = 'ombCategory'
            THEN
            CASE WHEN JSON_VALUE(ext.value, '$.extension[1].valueString') IS NOT NULL  THEN JSON_VALUE(ext.value, '$.extension[1].valueString')
                    WHEN JSON_VALUE(ext.value, '$.extension[0].valueString') IS NOT    NULL THEN JSON_VALUE(ext.value, '$.extension[0].valueString')
                    ELSE JSON_VALUE(ext.value, '$.extension[0].valueCoding.display')
                    END
            ELSE JSON_VALUE(ext.value, '$.valueCodeableConcept.coding[0].display')
            END AS Ethnicity 
        FROM 
        (
            SELECT fp.id, fp.extension FROM fhir.Patient fp
            INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
                ON fp.[meta.versionId] = cp.currentVersion
                AND fp.id = cp.id
            WHERE ISJSON(fp.extension) =1
        ) p 
        CROSS APPLY 
            OPENJSON(p.extension,'$'
            ) as ext
        WHERE JSON_VALUE(ext.value,'$.url') = 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity'
    ) e on e.id = p.id 
LEFT JOIN 
    (SELECT p.id
        , CASE WHEN JSON_VALUE(ext.value,'$.extension[0].url') = 'ombCategory'
            THEN
            CASE WHEN JSON_VALUE(ext.value, '$.extension[3].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[3].valueString')
                    WHEN JSON_VALUE(ext.value, '$.extension[2].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[2].valueString')
                    WHEN JSON_VALUE(ext.value, '$.extension[1].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[1].valueString')
                    WHEN JSON_VALUE(ext.value, '$.extension[0].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[0].valueString')
                    ELSE JSON_VALUE(ext.value, '$.extension[0].valueCoding.display')
                    END
            ELSE JSON_VALUE(ext.value, '$.valueCodeableConcept.coding[0].display')
            END AS Race 
        FROM 
        (
            SELECT fp.id, fp.extension FROM fhir.Patient fp
            INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
                ON fp.[meta.versionId] = cp.currentVersion
                AND fp.id = cp.id
            WHERE ISJSON(fp.extension) =1
        ) p 
        CROSS APPLY 
            OPENJSON(p.extension,'$'
            ) as ext
        WHERE JSON_VALUE(ext.value,'$.url') = 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-race'
    ) as r on r.id = p.id 

Here is a sample of the results from that view:

PatientResourceIDResourceTypeResourceIDVersionIDLastUpdatedLastNameFirstNameIsActiveGenderBirthDateMaritalStatusHomeStateOrProvinceEthnicityRace
Patient/d8af7bfa-5008-4a0f-85d1-0af3448a31ddPatientd8af7bfa-5008-4a0f-85d1-0af3448a31dd22022-05-31 18:07:03.2150000DUCKDONALD1male1965-07-14NULLONNULLNULL
Patient/78cf7725-a0e1-44a4-94d4-055482781afbPatient78cf7725-a0e1-44a4-94d4-055482781afb12022-05-31 18:07:30.7490000GretzkyWayneNULLNULL1990-05-31NULLNULLNULLNULL
Patient/9e909e52-61a1-be50-1878-a12ef8c36346Patient9e909e52-61a1-be50-1878-a12ef8c3634642022-05-31 18:39:58.1780000EVERYMANADAMNULLmale1988-08-18MNULLNon Hispanic or LatinoWhite+Asian
Patient/585f3cc0-c727-4989-9214-a7a7b60a2adePatient585f3cc0-c727-4989-9214-a7a7b60a2ade12022-05-31 13:14:57.0640000DUCKDONALD1male1965-07-15NULLONNULLNULL
Patient/29a819c4-f553-8189-2354-9441b86d37efPatient29a819c4-f553-8189-2354-9441b86d37ef12022-05-18 15:18:40.1560000FORDELAINENULLfemale1992-03-10NULLNULLNULLNULL
Patient/d5fe6802-a680-e762-8f43-9659340b00acPatientd5fe6802-a680-e762-8f43-9659340b00ac32022-05-18 14:39:52.2550000EVERYMANADAMNULLmale1961-06-15SNULLNULLC
Patient/4d661053-a8d0-148c-7023-54508fd04a52Patient4d661053-a8d0-148c-7023-54508fd04a5212022-05-21 13:48:24.9720000EVERYMANsamNULLmale1966-05-07MNULLNot Hispanic or LatinoWhite

Wrapping it up

As you can see, understanding the specification well enough to build a complex SQL statement using JSON functions is required to work within FHIR effectively. Due to the complex nature of the nested JSON, you may not be able to reconcile this in tools such as power BI. Being able to build this out in SQL guarantees that you have provided you will report writers and analysts with a solid result set which can be used with confidence.

Resources summary:

250th Blog Post

Kristyna and I were working through some updates to our site and realize that this was going to be our 250th blog post on Data on Wheels. I thought this would be a good time to reminisce about where we have come from, what has happened through the years, and what is next for Data on Wheels.

DataOnWheels

Where We Have Come From

On December 7th, 2010, I created a new blog called Data on Wheels on WordPress. My first blog was appropriately named “Time to start that blog.” It was a nice little paragraph that I’m sure no one actually read. so now is your opportunity check it out and like that first blog. I think more interestingly, my first series of blogs was on the nature of SQL Azure. This is back when SQL Azure only had web and business SKUs. Not sure how many of you remember those days, when the max size was 50 gigabytes, and we weren’t sure if it would go anywhere. The promise of greatness was there but there was still much to come.

Before we reflect on what has happened and what has changed through the years, I would like to emphasize how my blogging has started and matured through the years. Whenever I’m asked by others “Why do you blog?”, my response is “I blog for me and no one else.” This is important because we want to continue writing about something we enjoy. I turned my blog into a reference dictionary of things I’ve done, things I’ve heard, and things I did not want to forget. I have also written numerous tribute blogs through the years which highlighted mentors and others who have shaped my career.

What Has Happened Through the Years

We are coming up on year 13 of blogging. There was a period of a few years where my readership was really high because everyone wanted to figure out how to make Excel cool when working with SharePoint. Some of those posts are still being hit regularly today. I’ve also posted on many events that I have attended or presented at. I use my blog as a location for code and presentation materials.

I started when I returned to Magenic. Since then, my journey has taken me to Pragmatic Works and from there to 3Cloud where I am today. Along the way I have written blogs on XMLA, Excel, SQL Server, Azure, window functions, and Power BI. I’m sure I’ve covered many more topics than that including highlighting mentors such as my parents and my wife. looking back to the various posts it is interesting to see the number of events I either wrote about or presented at.

Me and My Wife Sheila
My Father-in-Law Ed
My Parents Jeanine and Terry

As my role changed through the years from a highly technical focus to more of a people focus, my writing diminished in count. It seemed I moved away from the technology more than I would have liked but managing was definitely a new area for me to work on. On June 8th of 2020, in the middle of a pandemic, I asked my daughter Kristyna to join me in writing this blog. She was just starting her career in Power BI and had done some great things that she wanted to write about. Having her join the Data on Wheels team did a couple things. First, it gave us great fresh technical content for the blog readers. Second, it gave Kristyna an opportunity to work with a large audience from the beginning. She actually has the record now for the most hits in one day for a post for Data on Wheels. That is correct, she’s beat my all-time record! Which is great!

As you may have read on LinkedIn or in some of my posts, I have been diagnosed with ALS. This has obviously made writing blog posts a little more challenging as I have to use voice to text technology in most cases. I have decided that part of what I will be writing about as we move forward is what technology is working for me to keep me active in the workplace. Some days it’s very much a changing environment as the disease takes over more of my body and we try to figure out how to accommodate what’s next.

What’s Next

We look forward to continuing to provide relevant technical content into our data community. It is an awesome place to be for me to have my daughter ready to take over the leadership on this blog and keep it moving forward. She will continue to write about things that excite her and that she wants to share with this community as will I. I will focus a lot more on workplace enablement, but as you can see from the notebooks post sometimes that bleeds over into more technical topics as well.

We are so excited that you have chosen to join us in this journey and look forward to putting together hundreds of more posts that have value or at least some fun for everyone. Just recently we updated our site and cleaned up some of the artifacts from the last few years. We also added a direct link to Working with ALS at the top of the page as there are others who are not focused on technology or are only interested in the products and services I am discovering in my journey. Right next to that link is a link about Distilleries. Kristyna and I both enjoy our whiskey and in her case some cigars, you should ask her. With our recent move to Kentucky, it gave us a unique opportunity to visit distilleries and try new whiskeys along the way. So, our first sharing with us is just which distillers you have visited and whether we have actually done a tour or tastings. We’ll provide more opinionated information as we move along. This is purely for fun, and we hope you enjoy it and provide feedback along the way as well.

Thanks again for joining us and we hope that you all either get a good laugh or learn something new!

Memphis SQL Saturday 2022 & a Notebook

Back in person again! It is awesome to be able to get back into the SQL community and see fellow data professionals. A huge shout out to the Memphis data community leaders in particular Zach Golden and Rob Demotsis who put on a great event for their first one out of the pandemic. I was also able to get together with fellow 3Clouders – Dawn Clement and Kristyna Hughes.

Steve, Dawn, Kristyna

A new but different opportunity

For me this was a very special event. Not only is it the first event I’ve been able to do in person since COVID started, but it is also the first event that I have presented at since being diagnosed with ALS. There are times I think I talk about this too much, but it is front and center of who I am now. I want to encourage others who have similar disabilities to remain active as they work in their new reality.

So how did this change for me? Well, having presented on SQL many times through the years, I typically use a method of highlighting code in management studio and executing it. That however would not work in this case. I moved all my code over to a notebook in Azure Data Studio. This allowed me to execute the code a step at a time with a simple button push. To read more about the experience of creating a notebook, check out my previous blog post here.

The other key thing that changed for me was having my wife, Sheila, join me on the platform to push the buttons that I needed for the presentation and the demo. This was definitely a new experience for her and me. She did a great job following my cues and sometimes a lack thereof. She was able to get us through the demos and leveraging the clever new notebook I used. This is the new normal for us and I look forward to presenting for as long as I am able.

Sheila and I co-presenting

Azure SQL Elasticity

This was the topic that I spoke on. We covered elastic queries, elastic jobs, and elastic transactions. As promised to the attendees and those of you who are reading this or are following up on my post about notebooks, I have published the notebook on the Data on Wheels GitHub which you can find here.

After you have downloaded the folders from GitHub, Open Azure Data Studio and browse to the Notebooks section. Click the Open Jupyter Book button has shown below.

This will open a File Explorer dialog. Choose azure SQL database elasticity folder and then click Select Jupyter Book.

This will open the Jupyter book which contains the markdown files with information and the notebooks you need to set up and run the demos. Enjoy!

Thanks to those of you who are able to attend. I hope you enjoyed the event as much as I did!

My experience working with notebooks in Azure Data Studio

I’ve seen notebooks used in Azure Data Studio on multiple occasions. I really like the concept of notebooks, having done some work within Azure Databricks notebooks, but not extensively. As I go into the process that I went through, it’s important to understand that I am not a data scientist and have not done extensive development or spent a lot of time in Python or Jupyter notebooks. Furthermore, my interest in the notebooks was elevated when I realized I wanted to continue presenting while working through my current ALS diagnosis. I have limited use of my hands and arms so highlighting and executing code, especially in front of a crowd, was going to be problematic. (If you want to learn more about my condition and tools I’m using to maintain my ability to work, please check out this series of articles on our blog.)

Let’s start with the core problem that I’m trying to solve today. I will be presenting a session on elastic queries in Azure SQL database. Most of the code is ready to go since I have done this presentation a few times. As I was working through testing my demo, I found executing code by highlighting and pushing “run” in either Data Studio or in SQL Server Management Studio was difficult because I struggled to control highlighting the code. I was also looking for better ways to automate the process, but more about that later. I watched a couple of demos on using notebooks and found some of the notebooks that have been created by Microsoft. I realized I could put together my entire demo package to share with the attendees and build the demo so that I could execute it a step at a time without highlighting. Now that you have the background of what I was trying to accomplish, let’s look at the process I went through getting this done.

How in the world do you work with notebooks in Azure Data Studio?

One of the interesting things about working with notebooks, is that if you want to work with notebooks, it’s likely that you already have and you prefer to use them. This means that the instructions for how to create, organize, and use notebooks within Azure Data Studio is a bit lacking. For example, it was not entirely clear to me that one part of the process is creating a folder to store your notebooks with your markdown files and other content. So, let’s go through the process of creating your first notebook step by step with explanations about what’s happening.

The organization of notebooks and files in Azure Data Studio

Part of my struggle in understanding what was happening is each time I tried to create a notebook it asked me for locations and files. I thought it should know where they should go. So, as a newbie with notebooks and organization with Azure Data Studio, I created a notebook and a Jupyter book so I could see how the files are organized. Then I could go back and create the Jupyter book correctly from the beginning. While I may not get all of the terminology correct in this process, this is my discovery as I move forward through the process.

Once I started working with the notebook process in Azure Data Studio, I realized there were multiple components involved:

  • Jupyter book
  • Markdown file
  • Notebook
  • Section

While I am sure there are simple ways to create what we would like to do, I’m coming at this entirely from Azure Data Studio as a data developer not a data scientist. Each time I tried to create my first Jupyter book, I didn’t understand what its purpose was in the beginning. When you create a Jupyter book, it looks like you’re creating a folder. That folder will also contain several helper files to organize your notebooks, markdown files, and sections. Before we leave the structure and organization section here, I want to clarify that the book is the parent folder, and the section is a sub folder within the book. Markdown files and notebooks are files created that are organized for particular purposes. The markdown file is effectively a document that allows you to create a nicely formatted informational component for your notebook. The notebook files are actual Jupyter notebook files which are split into sections for code and text.

Here is the high level organization of the Jupyter book we are going to create:

  • Jupyter book: Azure SQL database elasticity
    • Markdown file: README
    • Section: Setting up the demo
      • Markdown file: Set up instructions
      • Notebook: Prepping the demo
    • Section: Elastic query demo
      • Markdown file: Elastic query demo instructions
      • Notebook: Elastic query demo
    • Section: Elastic job demo
      • Markdown file: Elastic job demo instructions
      • Notebook: Elastic job demo

For the purposes of this blog post, we will walk through the process of creating the original Jupyter book and the elastic query demo section. That section has a good mix of code and text to illustrate the power and capabilities of notebooks.

Creating your first notebook in Azure Data Studio

Let’s begin creating our first notebook in Azure Data Studio. Before we dive into this process too deeply, I want to be clear that we are going to create a Jupyter book to add our notebooks to. This is not required as you can create a new notebook from the file menu or with the shortcut as noted on the screen in Azure Data Studio. What confused me about this initially is that you cannot create a simple notebook from the notebooks section in Azure Data Studio. When you create your notebook, you can save it as a file in the location of your choosing, but it will not show up in the notebook section. Once you create a notebook, if you are not using a Jupyter book to host it in, you can reopen it just by choosing Open File from the menu. While this may make sense to others, it was not entirely intuitive to me in the beginning. I had to do some mucking around to figure out that process.

So, we will start our process by creating a Jupyter book to host all our notebooks and markdown files. This Jupyter book will also be readily displayed in the notebook section on Azure Data Studio. Using the to get to the More Actions menu, choose Create Jupyter Book.

Create new Jupyter book

In the dialogue give your new Jupyter book a name and specify the location you want to store it in. I have not used the optional content folder for this exercise and will recommend that you do not either.

New Jupyter book dialogue

If you go to the folder location you created your Jupyter book in, you will see that it also created three files in the folder named the same as your Jupyter book:

  • _config.yml
  • _toc.yml
  • README.md

In the notebook section of Azure Data Studio, you should see your Jupyter book with a README markdown file in it. For now, we will leave the README file as an introduction to what is in your notebook. (Be aware, that you can remove the file by deleting it, but you will need to update the TOC file to reflect the changes you made. If you do not update the TOC file, you may see missing file error messages in Azure Data Studio.)

New Jupyter book with README

I will not take time in this post to review what is possible in a markdown file. The key here is you can update the README file that was created with headers and formatting to provide instructions on how to use the various contents of your Jupyter book. If you double click within the README file, it will open up the readme.md file in a new tab in Azure Data Studio. This has a line number and will allow you to update and add content.

The following code gives you an example of some markdown syntax:

# Welcome to the Jupyter book on Azure SQL Database elasticity
This book contains 3 sections
* The first section contains instructions on how to set up the demo
* The second section contains the demo for elastic queries
* The third section contains a demo for elastic jobs

This will result in the following look and feel in your README file

Formatted README markdown file

Adding a section

The next thing we will do is add a section where we will host the executable demo code. Right click on your notebook and choose Add Section. We will add the title as Elastic query.

Adding the notebook

Up to this point, we have been building the framework to support our first notebook. While all these steps are not required, this is the most complete approach. Right click on your section and choose New Notebook. This will create a Jupyter notebook in the subfolder of your section.

New section with a notebook

Once you create the notebook, it will open a tab in Azure Data Studio with the notebook. You will notice that it has something called Kernel. The kernel allows you to set the default language used for the notebook. For the work that we are doing we will be using the SQL kernel. This will allow us to execute SQL code against a database. In the Attach to dropdown, you will see databases that you can use to execute code. The Cell dropdown allows you to add cells which can contain code or text.

Azure Data Studio supports other kernels that can be used for executing code against various workloads. These include Python, Spark, PySpark, and PowerShell.

Now let us get down to the business of creating a notebook with executable code. Before we add executable code, let us add a text cell as an introduction to the code. You can do this by clicking the cell dropdown and choosing text. Once you add the text cell you will notice there is a formatting bar which ironically is missing in the markdown files editor. This means it is easier to create formatted text in a cell in a notebook rather than in the markdown file itself. Keep this in mind as you create your notebooks and add content to your Jupyter book. These cells are easier to work with at times than the full file. This is particularly true if you are not knowledgeable on formatting markdown.

At this point, let us add a quick introduction to what we are about to do in the in the following code cells.

Formatted text cell

Next, we will add a code cell. From the dropdown menu for cell, choose Code Cell. This will add a code cell to your notebook which uses the language selected in your kernel. There is also a play button which allows you to execute the code.

Empty code cell

I am going to add the code that is required to clean up the tables for the demo. The resulting code cell will look like the following:

Code cell with DDL code

As a last step to understanding how notebooks and code work in the environment, we can execute the code by pushing the play button in the code cell. This will return the result of that execution as shown below:

Code cell with results

Congratulations, you have created your first notebook with executable code against a SQL Server database! You can continue to add more text cells and code cells as needed. One of the reasons I like this pattern is that it allows me to execute the code without having to highlight it while doing demos. Each cell can be run independently. You will also notice there is a Run All button if you choose to run all the scripts at the same time that you have in your notebook. This could be valuable if you have a set of maintenance operations or related items you want to run and you have collected in a notebook for use.

Another key thing to remember is that notebooks are shareable. Because the connection is outside of the notebook, once you share the notebook, they will have to connect to an environment that allows them to execute the same code. You can add your notebooks to GitHub or similar source control to manage change and allow you to share common resources easily without just distributing SQL files.

Before we wrap up

I feel I would be remiss if I did not also demonstrate what happens when you get data results in a notebook. In my case I have a database I can connect to which has WideWorldImporters loaded into it. I am going to select the top 1000 rows from the DimSupplier table. Once I run the code cell, I get the rows affected, the execution time, and a table with results as shown here:

Code cell with data results

As you can see in the results window, you have several export options and a chart option that you can use to further visualize or work with the data that you have retrieved. I would encourage you to explore these options as it depends on the type of data you are working with whether they work well for you or not. For example, supplier data does not chart very well, whereas if I had used fact data there may have been some interesting charting options. A notebook could be a straightforward way to demonstrate some simple reporting for a technically savvy audience.

Wrapping it up

There are many more functions that I did not cover around notebooks, and I assume that Microsoft will continue to make improvements to the overall capabilities here. I look forward to using notebooks more as a terrific way to share code and run demos. I hope you find this as valuable as well.

For those of you who are not sure about using notebooks, this is an effective way to build your skills while not trying to learn a new language if you are familiar with SQL. My first exposure was using Python in a Databricks environment. That was much to learn while also trying to understand how notebooks functioned. As the data environment continues to expand and require new skill sets, understanding how to use and leverage notebooks on a regular basis is a good skill to have. Microsoft has done us a great favor by using standard Jupyter notebooks which are used in data science, Databricks, and other areas of data practice.

If you are following my work enablement series, you know one of the things that I am passionate about is simplifying how I work, in order to stay working while continuing to lose functionality in my arms. Notebooks help with this by allowing me to execute code without highlighting it when doing demos. Because highlighting code and executing it in a tool like SQL Server Management Studio requires multiple touches on the keyboard and mouse, I struggle to do it efficiently. The ability to organize my demo around code cells and then have a self-documenting notebook to pass along to attendees is a huge win for me. I hope this helps others who struggle in the same way. And I hope this was helpful to those who have not used or seen notebooks in their current work environment but may in the future.

I will be creating and sharing a completed notebook for the demos related to my presentation on elastic capabilities with Azure SQL. Look for that presentation follow up from the Memphis SQL Saturday in October 2022. I will publish a follow up blog post with a link to the completed notebook used with that demo.

Fast Fingers-Function Keypads

This is the third in the series of tools and technologies that I use to deal with the loss of functionality in my hands and arms. Check out this article for the lead up to this series.

Setting the stage

The issue I’m dealing with involves muscle atrophy in my hands and my arms. As a result, I’ve lost a lot of strength in my hands and arms including my fingers. Some of the unintended or unplanned impacts included the inability to successfully type at times or diminished amount of time I can spend typing. I had previously used a Logitech split keyboard which I loved. I considered myself a good typist and used to be able to type and a code very effectively. With the onset of the atrophy, I encountered situations where my hands would stop working. I would be typing and then I couldn’t type anymore. Some of it is related to physical exhaustion or fatigue from the effort required given my condition. I also experience a situation where my fingers curl making it nearly impossible type on a keyboard. The first time this happened was the first time I was concerned about my career. As I noted in a previous article, I am using voice to text for the bulk of my typing including this blog post. However, voice to text does not work that great for coding and frankly I have issues with any multi-key functions that require my hand to stretch across the keyboard.

Discovering a solution

I was watching a show with my wife and daughter when an ad came up that showed the Quick Keys solution from Xencelabs. This was part of a video editing package including a tablet and pens. I was intrigued because I had not seen a solution that allowed me to program keys with text. It also had a wheel on it that could be used for other tasks. I went and looked this up and I was able to buy just a Quick Keys device.

And I started doing some more research and looking into what this tool did, I realized that the space I needed to look more closely at was related to video editing and streaming. They have a series of tools which support macro keys that they use to optimize applications, shortcut keys, and game actions. The variety of these tools is substantial. Shortly thereafter, while at church working with the tech team, I saw a Stream Deck. This was even cooler because each of the buttons have a programmable LCD screen behind it. Now I knew what to look for and started determining what I wanted to do as I move forward.

Xencelabs Quick Keys

I purchased the Xencelabs Quick Keys device first. It has 40 programmable functions and a physical dial that I was able to program.

Xencelabs Quick Keys

I programmed some basic functionality that I really liked to have available with the ease in of a pushing a button close to me such as delete, backspace, a shortcut for speech to text, undo, redo, cut, @, and ctrl. This is my generic set of functions that in addition to the copy, paste, double click, and dictation shortcuts I had on my mouse applied to most of the applications that I worked in. I next set up a screen that you can go to by pushing a function button on the device to support specific functionality within Microsoft Word. The big one that I needed to have in there was a shortcut to change case as Microsoft dictation does not have cap capability at this time. I also added home and end along with a couple of other functions to be helpful.

The Quick Keys device has five customizable screens of eight buttons each. So, I used the first one for my generic set as noted above. My second one was for Word. I added a third screen that contained the web addresses of common locations I needed to go to such as the Azure portal and my blog. This allowed me to open a browser, push a button, and go to that site easily. What I quickly discovered was that I was going to need more functionality for this to be effective in the long term. Before I go to the next solution a couple other things I did on this device included using the physical wheel for moving the cursor and for volume control as it too had five settings.

My default screen setup with Quick Keys

Elgato Stream Deck

Because I had a device already, I wanted to research the Stream Deck before purchasing it. One thing I quickly noticed is that it is a favorite tool among streamers and has not had a significant upgrade because it just works. The device has been a solid device, easily programmable and customizable in a multitude of environment. If you go to YouTube, you will find a number of streamers, gamers, and content creators walking through demos of how to setup and use the Stream Deck. It has a lot of built-in functionality for variety of editing and streaming tools.

 To start with, I was unsure if this would be a good solution for me as most of what I needed was not what they were using it for. So, I dug in. What I discovered was that the deck was highly programmable with effectively an unlimited number of options that you could program. I thought I’d give it a try.

I purchased the 15 key Stream Deck pictured here:

Elgato Stream Deck

Once I got the Stream Deck and uploaded the software to program it, I quickly realize there are number of addons for the Stream Deck to support programming and Windows functionality. These are addons that give you shortcuts to things like locking your computer something that Quick Keys could not do. I added these in as well as some icon sets because icons are cool. Once I had this in place, I programmed my initial set of functionality to enhance what I was doing with Quick Keys. Because I already had Quick Keys, those two screens provided me the generic starting point for most functionality I would need outside a specific programs like Word. This also allows me to keep Quick Keys on the generic set of common functions and use Stream Deck to be more reactive to programs and needs.

I have taken the Stream Deck and programmed it for a couple of specific use cases I really am happy with. Let’s start with the first one which is Word. Stream Deck can detect the application that you’re in and set the keys up with specific profiles that you create. In my case whenever I am in Microsoft Word, it has the editing keys and other functionality that make working with documents easier on the Stream Deck. Because I still have basic keys sitting on the Quick Keys solution I’m able to have a combined set of 23 function keys readily available without switching screens.

Default screen on my Stream Deck
My Word profile

I have also set up a similar set of functionality when working with Microsoft Outlook. While I’m still working through which functions make the most sense for me to work with in each scenario and if I need more than one screen, the amount of functionality available at my fingertips as a result of programming these two devices is substantial. It makes it easier for me to work through a variety of commands without struggling with the keyboard. I am using the Word functionality as I edit this blog post after getting the content created via voice to text.

Optimizing functions for code

Now for the more interesting use case. It’s part of my job occasionally I still need to do some coding. In this case, I was working with T-SQL code. I needed to create some tables, add some keys, and work with data. Coding is one of the most typing intensive activities I do where voice to text does not help me. So, how can Stream Deck help me out? It turns out you can actually send keystrokes through both devices. However, the capacity of Stream Deck is substantially larger than that which is available in Quick Keys (500 versus 24). More importantly with the unlimited number of keys that can be programmed combined with folders to allow you to group together commands in Stream Deck, it was a natural choice. I created a folder on my screen for the work called SQL. In there I created a folder for CREATE commands and will likely add more as they go along. In the first folder of SQL, I have commands such as SELECT, FROM, WHERE, INNER JOIN, and similar common commands used when working with data in SQL. While it may seem at first glance these are short commands, I must call out that the goal for me is to reduce the amount of typing I do as much as possible. When I added the CREATE commands, I had the full syntax for creating a table where I just had to fill in the name and field list. I also added a folder that gave me the data types most used so I wouldn’t have to type those either. I also had the syntax for primary and foreign keys and add indexes in the future. My point here is I was able to reduce the amount of typing required by 30 to 50% depending on what I was doing. This reduces strain on my hands and allowed me to be more productive for a longer period of time.

Here are some screenshots from the Stream Deck programming surface to show you how I set it up for SQL so far.

My SQL default menu
My SQL create menu

I really enjoy a Stream Deck because some of it is just fun. Part of the fun of this is finding icons that you can use that include gifs on the screen. I’m completely planning to continue to extend what I’m doing with my Stream Deck.

Wrapping it up

Finding these tools have been extremely important to maintaining productivity in my work. What I’m learning so far is the tools that I’m discovering are beneficial for me but also for others who might want to build shortcuts out for things they can’t remember or to make working generally easier. These tools are not without cost, but the increased productivity is seriously worth it. And frankly it makes my setup at work look really cool. Hopefully you find this information helpful, or it could be helpful to someone else. Feel free to pass it on.