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.

Storytelling with Data – Enertia User Conference 2022

Thank you to everyone who attended my session at the Enertia User Conference in Las Vegas earlier this month! It was a blast meeting everyone, and I can’t wait to see your BI solutions come to life. Big shout out to my dad, Steve Hughes, for introducing me to this amazingly talented group of people, and to Fernando Salazar, for welcoming me into the Enertia community. Below is all the research used in my presentation on Storytelling with Data. Please use this link for the GitHub folder containing the presentation, sample data used, and a sample report that we worked on together. Happy coding!

In 2015, the average company spent $7.4 million (USD) a year on data-driven initiatives (IDG Big Data & Analytics Survey). In 2021, the average company expected to spend $12.3 million (USD) on data initiatives in 2022 and 55% expect their budgets to increase (Data & Analytics Study). Data and analytics budgets have grown nearly 40% in five years, yet Foundry found in their 2022 study that “lack of appropriate skill sets is a top challenge” (2022 Study). It’s clear that data is a priority to many organizations, but without the proper training on how to use data to deliver insights that data sit like a box of nails without a hammer.

To turn our millions of dollars worth of data into actionable insights, we need to tell a story with it. Why a story? Storytelling has been and is a vital way humans understand and share the world around them. If numbers were as interesting and memorable as stories, our classic works of art would be the Pythagorean theorem instead of the Iliad and Odyssey. Listening to a story engages multiple parts of the brain, which is why listening to your great aunt’s story for hours is exhausting, but also why stories are more memorable. Here are the parts of the brain engaged when you hear a story:

  • Wernicke’s area (language)
  • Amygdala (emotions)
  • Minor neurons (empathizing)

The hippocampus (short-term memory storage) is more likely to push stories into long-term memory than numbers because they engage multiple areas of your brain. This is why so many math classes include word problems. Thankfully, visualizing your data doesn’t need to be like a math test.

There are a few main elements to be successful at data storytelling:

  1. Data
  2. Story
  3. Visualizations

Data powers the visualizations that can communicate your story clearly and memorably. Similar to a picture book, a good report will quickly communicate a story and a great report will help you change that story through actionable insights. So how do we go from numbers to a story? From math class to lit? Through connecting visuals and numbers to elements of a story. Every story contains a few common elements: characters, setting, conflict, and resolution.

For example, imagine you have seen a downward trend in sales from Texas for smart cars. You characters would be your previous and current customers in Texas, setting is the timeline of the downward trend and the customers across the country, conflict may be that news stations in Texas ran a story about hackers getting into smart cars, and resolution may be to advertise a security patch through local news stations in Texas. There may not be a conflict in your story (maybe you’re exceeding your goals), so that section can be skipped in favor of focusing on what is going well.

Always remember that applying analytical techniques to managerial problems requires both art and science.

Jan Hammond, Harvard Business School Professor

Creating Data-Driven Decisions

Storytelling with data can leave an audience asking questions such as “how do we make our sales go up?” or “what is xyz branch doing to increase profit percentages higher than everyone else?” This is where the resolution portion of the story comes into play. A lot of good reports will “tell the news”, that is they will display a current state of affairs retroactively. To generate a true return on investment (ROI) from our data, we need to use it to proactively drive decisions.

For example, in 2008 Starbucks closed hundreds of locations. Howard Schultz returned as CEO and declared they would use data to place their stores strategically going forward. Starbucks now consults with an analytics company called Esri to analyze retail locations for various variables that are proven to drive coffee shop traffic and overall success (reference).

The key to building a data-driven culture is to avoid deceiving your audience. Visualizations are a powerful tool, but they can be used to trick the viewer into disproportionate understandings of data. For example, New York Times came out with a visual in 1983 that showed the mandated fuel economy standards changing from 18 to 27.5 miles, an increase of 53%. The graph in the article, however, had an increase of 783%! A good way to build trust in your data is to avoid the Lie Factor.

The representation of numbers, as physically measured on the surface of the graphic itself, should be directly proportional to the quantities represented. The Lie Factor = the ratio of the size of an effect show in the graphic to the size of the effect in the data.

Tufte

This all sounds well and good, but how much money can using data instead of your gut actually save you? According to a study by Harvard Business Review, Fortune 1000 executives have seen the most value by aiming to decrease expenses. The second highest is by finding new innovation avenues. Decreasing expenses is great, it allows your company to lower overhead costs of operation and increase profit margin. However, there is only so much overhead you can eliminate which is why finding new innovation avenues will be the long term ROI from using big data.

Building a Data-Driven Culture

One of the biggest blockers to using big data effectively is adoption by the larger business. According to HBR, more than half of Americans depend on their gut to make decisions even if there is evidence that disproves their theory meanwhile data-driven organizations are 3x more likely to report significant improvements in decision-making. There are many ways to become more data-driven, even in your daily life. HBR summaries a few easy steps to take to begin building a culture of cultivating insights from big data:

  1. Look for patterns everywhere
  2. Tie every decision back to data
  3. Visualize the meaning behind data
  4. Consider furthering your education to learn more data analyst strategies

Thankfully, this approach does not need to be all or nothing. It can be as simple as looking for patterns in your personal life like spending more money on ice cream deliveries after watching sad movies. A simple cost-saving would be to stock up on ice cream in advance and avoid the delivery fees. Finding a new innovation source may look like watching romantic comedies instead of sad movies or switching to popcorn which is much more cost effective.

Additional Resources

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.

SQL Saturday Baton Rogue Recap

Thank you to everyone who came out and saw me present at the SQL Saturday in Baton Rogue! It was a blast to see smiling faces and not be the only one laughing at my jokes haha. HUGE thank you to the team of volunteers who made this event possible and to the sponsors for enabling such an amazing event.

My presentation of Power BI Meets Programmability had 58 people in attendance, the largest crowd of the whole weekend! Out of that came many great conversations about the potential shift in IT teams from separating reporting teams from application development to playing to each other’s strengths to deliver sustainable and scalable reporting to the end-users of your application. As promised, below is all the code used in the session. Thank you again to everyone who attended, and happy coding!

using System;
using Microsoft.AnalysisServices.Tabular;

namespace TOM_Testing_Live
{
        internal class Program
        {
        static void Main(string[] args)
        {
//-------------- Connect to PBI Premium Workspace ---------------- //

            // create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/Happy%20Coding";
            string connectString = $"DataSource={workspaceConnection};";

            // connect to the Power BI workspace referenced in connect string
            Server server = new Server();
            server.Connect(connectString);


//---------------- List out current state before we make changes -------------------//

            // enumerate through datasets in workspace to display their names
            foreach (Database database in server.Databases)
            {
                Console.WriteLine($"ID : {database.ID}, Name : {database.Name}, CompatibilityLevel: {database.CompatibilityLevel}, Last Updated : {database.LastSchemaUpdate}");
            }

            // enumerate through tables in one database (use the database ID from previous step)
            Model model = server.Databases["5d161e6b-697a-49b6-a3e6-7d19b940a8fd"].Model;

            //if you don't specify a database, it will only grab models from the first database in the list
            foreach (Table table in model.Tables)
            {
                Console.WriteLine($"Table : {table.Name} IsHidden? : {table.IsHidden}");
            }

            // Specify a single table in the dataset
            Table table_product = model.Tables["Product"];

            // List out the columns in the product table
            foreach (Column column in table_product.Columns)
            {
                Console.WriteLine($"Columns: {column.Name}");
            }

            // List out the measures in the product table
            foreach (Measure productmeasures in table_product.Measures)
            {
                Console.WriteLine($"Measures: {productmeasures.Name}");
            }

            // List of translations on the model
            foreach (Culture culture in model.Cultures)
            {
                Console.WriteLine($"Existing Culture: {culture.Name}");
            }

            // List out the hierarchies in the product table
            foreach (Hierarchy hierarchy in table_product.Hierarchies)
            {
                Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag = {hierarchy.LineageTag}");
            }

            // List out the levels in our category hierarchy
            if (table_product.Hierarchies.ContainsName("Category Hierarchy"))
                {
                Hierarchy hierarchy_category_ = table_product.Hierarchies["Category Hierarchy"];
                foreach (Level level_categoryhierarchy in hierarchy_category_.Levels)
                {
                    Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} Lineage Tag: {level_categoryhierarchy.LineageTag} from {level_categoryhierarchy.Column.Name}");
                }
            }
            else
                {
                Console.WriteLine($"No Category Hierarchy");
                }


        
 //----------------Deleting columns and measures ----------------//
   /*         
           // Deleting a column if it exists
             if (table_product.Columns.ContainsName("Testing"))
             //this looks to see if there is a column already named "Testing"
             {
                 Console.WriteLine($"Column Exists");
                 table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
                 Console.WriteLine($"Column Deleted");
             }
             else
             {
                 Console.WriteLine($"Column Does Not Exist");
             } 

             // Deleting a measure if it exists
             if (table_product.Measures.ContainsName("Test Measure"))
             //this looks to see if there is a measure already named "Test Measure"
             {
                  Console.WriteLine($"Measure Exists");
                  table_product.Measures.Remove("Test Measure"); //if the measure exists, this will remove it
                  Console.WriteLine($"Measure Deleted");
              }
              else
              {
                  Console.WriteLine($"Measure Does Not Exist");
              }

*/
//--------------- Adding columns and measures --------------------//            

              // Adding our column if it doesn't already exist
                if (table_product.Columns.ContainsName("Testing")) //this looks to see if there is a column already named "Testing"
                {
                    Console.WriteLine($"Column Exists");
                    //table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
                    //Console.WriteLine($"Column Deleted");
                   // Column column_testing = new DataColumn() //this will add back the deleted column
                    //{
                  //      Name = "Testing",
                  //      DataType = DataType.String,
                  //      SourceColumn = "Product"
                  //  };
                  //  table_product.Columns.Add(column_testing);
                  //  Console.WriteLine($"Column Re-created!");
                }
                else
                {
                    Column column_testing = new DataColumn() //this will add the column
                    {
                        Name = "Testing",  //name your column for Power BI
                        DataType = DataType.String, //set the data type
                        SourceColumn = "Product" //this must match the name of the column your source 
                    };
                    table_product.Columns.Add(column_testing);
                    Console.WriteLine($"Column Created!");
                }

                //Get the partition sources for each table
                foreach (DataSource datasource in model.DataSources)
                {
                    Console.WriteLine($"Datasource : {datasource.Name}");

                };

                // Add a measure if it doesn't already exist in a specified table called product
                if (table_product.Measures.ContainsName("Test Measure"))
                {
                    Measure measure = table_product.Measures["Test Measure"];
                    measure.Expression = "\"Hello there\""; //you can update an existing measure using this script
                    Console.WriteLine($"Measure Exists");
                }
                else
                {
                    Measure measure = new Measure()
                    {
                        Name = "Test Measure",
                        Expression = "\"Hello World\"" //you can also use DAX here
                    };
                    table_product.Measures.Add(measure);
                    Console.WriteLine($"Measure Added");
                }


//------------------- Manipulating Hierarchies ---------------------//

            //Editing an existing hierarchy originally called Category Hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy Rename"];
            {
                hierarchy_category.Name = "Category Hierarchy Rename"; //this renames the hierarchy, note the lineage tag will remain unchanged
                Console.WriteLine($"Category Hierarchy Renamed");
             }
            
            //Editing an existing hierarchy level 
              Level level_Category = hierarchy_category.Levels.FindByLineageTag("ca792793-d3c3-4b5d-9bee-2b46c01833bb");
              Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("4304e645-bb8f-4d7e-b25c-f629be2110d8");
              {
                  level_Category.Name = "Category";
                  level_Category.Ordinal = 0;
                  level_Subcategory.Name = "Subcategory";
                  level_Subcategory.Ordinal = 1;
                  Console.WriteLine($"Category Hierarchy Levels Renamed & Reordered");
              }

            //Adding a new level to the hierarchy if it doesn't already exist
              if (hierarchy_category.Levels.ContainsName("Model"))
              {
                  Console.WriteLine($"Hierarchy Level Exists");
              }
              else
              {
                  Level level_Model = new Level()
                  {
                      Name = "Model",
                      Ordinal = 2,
                      Column = table_product.Columns.Find("Model")
                  };
                  hierarchy_category.Levels.Add(level_Model);
                  Console.WriteLine($"Hierarchy Level Added");
              }
          
 
             //Add a new hierarchy if it doesn't already exist
               if (table_product.Hierarchies.ContainsName("New Hierarchy"))
               {
                   Console.WriteLine($"New Hierarchy Exists");
               }
               else
               {
                   Hierarchy hiearchy_new = new Hierarchy()
                   {
                       Name = "New Hierarchy",
                   };
                   table_product.Hierarchies.Add(hiearchy_new);
                   Console.WriteLine($"Hierarchy Added");
             //Creating levels to add to the new hierarchy
                   Level level_one = new Level()
                   {
                       Name = "Model",
                       Ordinal = 0,
                       Column = table_product.Columns.Find("Model")
                   };
                   Level level_two = new Level()
                   {
                       Name = "Product",
                       Ordinal = 1,
                       Column = table_product.Columns.Find("Product")
                   };
                   hiearchy_new.Levels.Add(level_one);
                   hiearchy_new.Levels.Add(level_two);
                   Console.WriteLine($"Levels added to new hiearchy");
               };

         
//-------------------------- Translations ------------------------------//

//Translations can be used to rename existing columns without rebuilding the model. This also updates any visuals that use that column.          

            // List of translations on the model
            foreach (Culture culture in model.Cultures)
            {
                Console.WriteLine($"Existing Culture: {culture.Name}");
            }
            // Let's get a list of the existing translations within the en_US culture
            Culture enUsCulture = model.Cultures.Find("en-US");

            foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
            {
                Console.WriteLine($"Translated Object: {objectTranslation.Value}");
            }
            // Narrow down what column within this culture/language you would like to add the translation to
            MetadataObject dataColumn = table_product.Columns.Find("SKU"); //this needs to always be the original column name within the data model.
            ObjectTranslation proposedTranslation = enUsCulture.ObjectTranslations[dataColumn, TranslatedProperty.Caption];

            // Only one translation per entity per culture.
            if (proposedTranslation != null)
            {
               Console.WriteLine($"Translation Exists for this Culture & Column combo");
               enUsCulture.ObjectTranslations.Remove(proposedTranslation); //need to remove the existing translation to overwrite it
               ObjectTranslation overwriteTranslation = new ObjectTranslation()
               {
                   Object = dataColumn,
                   Property = TranslatedProperty.Caption,
                   Value = "Cool Stuff"
               };
               enUsCulture.ObjectTranslations.Add(overwriteTranslation);
            }
            else
            {
               ObjectTranslation newTranslation = new ObjectTranslation()
               {
                   Object = dataColumn,
                   Property = TranslatedProperty.Caption,
                   Value = "Total Rad"
               };
               enUsCulture.ObjectTranslations.Add(newTranslation);
            }             

           // List out the translations to see what they are now that we have run the script    
           foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
           {
               Console.WriteLine($"Final Translated Object: {objectTranslation.Value}");
           }

//------------------- List out end state --------------------------------//

            // List out the columns in the product table one more time to make sure our column is added
            foreach (Column column in table_product.Columns)
            {
                Console.WriteLine($"Columns: {column.Name}");
            }         

            // List out the measures in the product table one more time to make sure our measure is added
            foreach (Measure productmeasures in table_product.Measures)
            {
                Console.WriteLine($"Measures: {productmeasures.Name}");
            }

            // List out the hierarchies in the product table
            foreach (Hierarchy hierarchy in table_product.Hierarchies)
            {
                Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag: {hierarchy.LineageTag}");
                foreach (Level level_hierarchy in hierarchy.Levels)
                {
                    Console.WriteLine($"Level {level_hierarchy.Ordinal}: {level_hierarchy.Name}, Lineage Tag: {level_hierarchy.LineageTag} from {level_hierarchy.Column.Name}");
                }
            }

//-------------- Refresh our version of the data model then push/save changes back to the model in the PBI service -------------//
            table_product.RequestRefresh(RefreshType.Full);
            model.RequestRefresh(RefreshType.Full);
            model.SaveChanges(); 
            //make sure this is the last line! Note, this line will not work unless your dataset has proper data source credentials connected in the service

            Console.WriteLine($"Script Complete!");


        }
    }
}