Typing with Your Tongue – Voice to Text Technologies

This is the second 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 actually spend typing. I had previously used Logitech split keyboard which I loved. I consider myself a fairly 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 actually stop working. I would be typing and then I couldn’t type anymore. Some of it is definitely related to physical exhaustion in the effort required given my condition. The first time this happened, was the first time I was concerned about my career.

As my condition has worsened, I have try to variety of software solutions that supported voice to text. In this blog I’m going to separate my voice to text solutions into two primary groups. The first group is those tools which I can use for dictation like creating this blog post or working with documents. The primary focus of this group of tools is to support the ability to add text while working on a computer with a mic. The second group of tools is primarily focused around note taking and using mobile tools on my phone or similar devices where I may not have access to the dictation tools I would you use in my normal work day. the one area that I am not going to cover in this blog post is related to voice automation tools or those tools which provide voice command capability. What I have found is that they are not the same. Currently I have not found a voice command solution that I like. As I do some more discovery in that area, I will share what I find.

Dictation tools

When my condition first surfaced, I immediately started thinking about how to do voice to text. The first software that came to mind with Dragon by Nuance. I started using Dragon as soon as we were able to get a professional account through work. The first thing I noticed about Dragon was that it felt like I had went backwards in time as it was not a updated piece of software or modernized. Dragon has been around a long time and services a lot of different areas of business including law and medical. It is a highly valuable tool in those spaces and has specialty products for some of those with specific terminology support.

DragonBar

What I liked about Dragon is that it has an extensive editing capability built into the software. This is particularly true if you use their special dialog box to create most of your content. That being said, you really need to have a good microphone to efficiently run Dragon. The other issue that I had was when we upgraded to Windows 11, it was not supported. This will likely change as Microsoft has purchased the product in recent months and will likely incorporate a lot of it into its own platform. I reverted to Windows 10 to determine how much I would use it. The biggest issue I had was the requirement for a high-quality microphone that would likely need to be on a headset to operate well.

With the switch to Windows 11, I needed to find alternative options and I turned to Microsoft to see what they had available. It turns out that Microsoft has two voice to text solutions that work in Windows 10 and 11. (These solutions may work in other versions of Windows, but I don’t use them.) The first tool I explored and worked with was Dictate that is available from Microsoft 365.

Dictate In Word

In particular, Dictate inside of Word. I immediately liked this tool because it is built-in to the Office platform. It also seemed to learn more quickly than Dragon did through general use which is likely to do the AI behind it. I also appreciate the fact I could use the open microphone effectively without making changes to my environment. I am writing this blog post in Word first because of the capabilities of Dictate. It is not without flaws, and the biggest issue I have with Microsoft 365 Dictate is that it does not know how to capitalize mid-sentence or to choose a word to capitalize. This seems like a significant oversight that many have complained about through the years of using this product. Hopefully Microsoft will resolve this soon as it seems like an oversight. I did discover that there is a change case option in text editing available in Word that has allowed me to handle this situation easily.

Change case in Word

I’m still learning Dictate and its capabilities but overall, it has been the most fluid solution I’ve used to date.

When Dictate is not available outside of the Office 365 suite. In that case, I use the Microsoft voice typing that you can find by hitting Windows+H.

Windows voice typing box

This will allow you to do voice dictation to any text box well, most text boxes. I use this for dictating messages in Teams, forms on websites, and similar type of functionality. This is not as capable as Dictate in Office, for example delete does not work the same way in the two tools. However, it too seems to learn my speech and respond well to the open mic which is why I have chosen to use it.

Before I move away from the dictation tooling, I want to add that in the Office suite I’ve been able to effectively use Dictate in Outlook. This has been very helpful in creating emails. Depending on where you are in Outlook you may or may not have Dictate available to you in which case you can always use voice typing. Dictate also works effectively in OneNote. The functionality in PowerPoint is severely lacking and I don’t know why. It does not seem to figure out what I’m trying to say most of the time when I’m working with this in PowerPoint. So, this is kind of frustrating when creating presentations but overall, the effectiveness in Outlook and Word have kept me quite productive.

In summary, if Dragon works for you and how you work it is likely the best tool for the job. With Microsoft purchase of Dragon, we can expect to see some of that functionality move into this Office suite is my expectation or into Windows directly. If you are like me and prefer using an open mic, you will find that the Microsoft 365 Dictate and Windows voice typing tools are more likely a better fit but still have significant gaps to fill.

Notetaking and mobile

I kind of grouped these together because of how I function. One of the immediate impacts of my condition is that I am no longer able to take handwritten notes. This has been a huge hit as most of the time I used a lot of pen and paper for design work, notetaking, etc. Losing this capability was a significant hit to my productivity. As a result, I needed to find alternatives.

Otter on Android

The first tool I added to my toolbox on my phone was Otter. This product was introduced to me by a peer at 3Cloud. It allows you to record and transcribe conversations so that you have notes from that conversation as well as the recording. It does are pretty good job in transcription frankly. I’ve used it to take notes during meetings, to take notes while working with my doctors, and just self-transcribed notes. I use exclusively on my phone and then transfer the notes to OneNote when I want to use them with other tools. This has been a lifesaver in particular in regard to doctors’ appointments. It has helped me keep track of that information and because of the transcription we can transfer that into other documents or even onto my CaringBridge site when we need exact details.

On my phone, I also use Google’s built in voice text technology and Samsung’s technology as I have a Galaxy phone. I will say this as hit or miss and often and it’s a little bit of fun to my text with my family for sure. However, it is still easier to use voice to text as opposed to typing on the device itself. So, I’m thankful that it works even if it stumbles a lot more than some of these other tools. Dragon does have a mobile option as well, but I did not get it working so I can’t really speak to its functionality at this point.

Summary of my new world

I still need to type to do my job. Part of my job entails building some technical labs which require coding. Coding is not easily done with voice to text or maybe we should say should not be done with voice to text. However, as intellisense and similar functionality has become more prevalent in the tools, it has reduced the stress on my hands when creating code. There’s new functionality from Microsoft in GitHub called copilot and similar tools that use AI to suggest code. For the moment I haven’t had a chance to test these functionalities out but I’m looking forward to seeing how they to improve my work environment. I would always recommend that you let people know that you’re using voice to text in particular when you’re using it in Teams or other chatting type environments. This means you don’t have to go back and correct everything you do all the time. People are forgiving and occasionally we get some really good fun like calling “Dennis” “dentist”. He wasn’t one, or so he says.

Before I end, I would like to say that this is not just helpful for those of us who struggle typing. You may find the dictation tools for example in Word to be a way to generate documents rather quickly. Just keep in mind:

  • plan to edit some
  • take your time
  • learn the tool
  • find success

I hope this helps someone out there. If you have found a tool that uses voice to text more efficiently or differently than when I’ve talked about I’d love to hear about it. Just add it in the comments below. Thanks for reading!

When is a mouse not a mouse?

This is the first 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 (more on that later) and what feels like cramping in my hand when holding a mouse for too long. I was using a nice Logitech mouse for most of my work. I have also used the Surface Arc mouse from Microsoft. I liked Arc mouse because it traveled very well because you can flatten it. The Logitech mouse and split keyboard were a part of my standard home office setup.

An older picture of my setup with the Logitech mouse and keyboard

As a condition worsened, I found myself struggling to use a mouse longer than half hour to an hour at a time. I would work with the mouse and then eventually I would start unintentionally clicking the buttons and was unable to actually move it. The level of frustration caused by working that way is pretty high.

Large trackpad try out

Because the problem appeared to be around the fact that I was holding the mouse, my first thought was to try a larger trackpad. This would allow me to use both hands and fingers effectively to manipulate the mouse on the computer. What I found was it just transferred the problem. My lack of finger control and the cramp in my hand still existed while using the trackpad.

Enter the roller bar mouse

A friend of ours works with hand specialists back in Minneapolis. She suggested we check out something called the RollerMouse. It’s an interesting tool. (One thing to note here is that all of these tools cost something. Trying to find the right tools to work is not inexpensive. Hopefully some of this information will help you save some money. ) I went online and did some research about how it’s supposed to help. A lot of the reviews talked about folks with arthritis or other similar conditions which impact their ability to work with a mouse for an extended period of time. Including the fact that they could have shoulder issues and so on. That being said, I decided to give it a try.

The RollerMouse Red that I purchased

It is a completely different way to work with the mouse functionality on your computer. Many of the reviews talked about the two-week learning cycle that was required to be effective. I found that I was affective using the mouse within a couple of hours. I do think this was related to the fact that I struggle so much to work with a traditional mouse or trackpad. The roller bar effectively supports a three-screen solution including two 34-inch monitors. The side-to-side motion of the bar navigates seamlessly from one screen to the other.

Beyond that the buttons are awesome! Naturally there are the left and right click buttons in the center of the bar. The bar itself functions as a left click which is a very natural function when working with the mouse. There is the scroll wheel in the middle which works like a scroll wheel on a normal mouse. Now it gets interesting. At the center in the bottom is a double click button. I did not know if I’d actually use this, but I find myself using it especially when switching hands while using the mouse. The steepest part of my learning curve has been effectively using the buttons. I must keep in mind that the cursor placement is where the clicks will occur, not the last place I had clicked. Up next, they have the copy and paste buttons right above the left and right clicks. In a later blog I will talk about speech to text functionality but having shortcut buttons for copy and paste means I did not have to do the Ctrl+C or Ctrl+V patterns on the keyboard. Which is great! All the buttons are programmable. This means you can change them if you have a better pattern that works for you. So far, I’ve only changed one button, the scroll button click. I use it to turn on dictation in Word.

It changed the way I work

To say that this has changed the way that I work and allowed me to work longer is the understatement of the year. I am still working through other tools and devices to continue to help me be productive. But to date this is by far, the best investment I have made. If you’re experiencing issues manipulating a mouse in the traditional fashion or anything that requires you to potentially either switch hands to give yourself a break or just because it’s hard sometimes to use a mouse and move it, this is a great solution for you. The mouse is stationary and has a nice wrist pad for you to work on. The ability to switch hands allows me to get the break I need on either hand at any given time. With this mouse I am able to stay more productive than I ever thought I was going to be able to win the started. As you can see, I’m a real fan.

My new desk set up with the RollerMouse in the middle

For those of you that aren’t dealing with issues and are wondering should you use it? If you want to experience a different method of working with a mouse and get rid of moving around mouse is on your desk or touching a trackpad this is a great solution. I personally think I would have fallen in love with this solution much earlier had I known about it. Now you know about it!

I love to hear from you if this was helpful and if you’ve decided to give this a try. For those of you suffering with carpal tunnel or arthritis come on give this a serious consideration. It is on the pricey side, but I will tell you in this case it may be worth it.

The Impact of Change

I started this summary on LinkedIn. Check out that article here. I only used LinkedIn to introduce what is happening, if you’re interested in learning more keep reading.

Kristyna Hughes

First, I have to say thank you to my daughter Kristyna, who has been contributing regularly to our Data on Wheels blog in my absence. I’m amazed at quality and depth of the content she has provided to the community including blogs on the tabular object model and C#. I’m glad she is able to provide great content for the data community.

3Cloud has been a key partner in my journey so far. Early in the diagnosis we were not sure if this was ALS. Frankly, we are still not sure if it will lead to that eventually. 3Cloud has stepped in and helped me find a place where I can contribute and support our teams as we continue to grow our business in data and analytics. For that I can say I am supremely thankful. This has allowed me to adapt to my new circumstances and continue to contribute in meaningful way.

Now we get a little more personal, I can say nothing but great things about my wife, Sheila. She has had to step up as a caregiver in ways that we were not expecting. Sheila has been my rock through this as well as my support when I really needed it. From helping me get ready for the day to keeping me going through the day, she has been magnificent through it all. What we do not know is what God has planned for us or what the next stage will be for me as we move forward. We can only trust him every day for what is next.

As a part of this process, we have moved from Minnesota to Kentucky to be closer to my family. That move was interesting. We had a lot of ups and downs as we went along, and I have my adult children to thank for a lot of the help throughout those weeks. We also had many friends help us with packing and prepping because I could not do much at all. They were very gracious and gave us time on their weekends and helped us make this move. When we got to Kentucky my kids did the lion’s share of unloading which was unexpected. With their help we were able to get moved in quickly. They have helped with painting, clean up, and unpacking throughout it all. I have watched my wife and daughters pick up new skills to fill the void where I would have normally done the work. It’s pretty impressive really. Other family members have also stepped up to help as needed which has been great as well.

We are in Frankfort

So, what does that mean for the blog? Well, Kristyna will continue to write on topics that she loves. I will contribute when I can on technical content. But I will also be providing reviews of various tools and technologies I have tried or used through this journey. I hope that some of these reviews will help some of you out there who may have similar or related issues. And maybe someone will find a tool that will be super helpful to them. I look forward to sharing this journey as we move forward.

If you made it this far, thank you for giving me some of your time and lending me your ear. If you want to know more about the personal side of this journey, you can check it out on CaringBridge. I look forward to getting back out in community and connecting with many of you along the way.

Power BI: Calculating Network Days in M Custom Columns

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.

Please note, this blog will not cover how to calculate business days (week days that are non-holidays) because that should be done upstream from Power BI in either an application or SQL stored proc that can get/set holidays dynamically (the date of Easter changes every year). International clients often have different holidays which can infinitely complicate the definition of a business day. Also, many coding languages have built in functions to get business days while Power BI does not (sadly, the Excel NETWORKDAYS() function is not currently in Power BI) . So for this, we will stick to excluding weekends and getting a count of the week days between two events.

The Final Equation

To make it easier to read, SD means start date and ED means end date. SWD will mean the work week day of the start date and EWD means work week day of the end date. For example, if the order date was May 2nd 2022 and the ship date was May 4th 2022, then the SD = 5/2/2022, SWD = Monday/day 2 of the week, ED = 5/4/2022, and EWD = Wednesday/ day 4 of the week. But let’s say the SD was May 7th which is a Saturday. We would adjust this to be Friday/ day 6 of the week. I’ll put code below to show how to adjust the weekends to Friday before and how to adjust to the Monday after, but the GitHub file will align weekends to the Friday before.

Week days between = ( if SWD > EWD then (5 + EWD – SWD) else (EWD – SWD)) + (5 * ( Actual Days Between /7 rounded down))

In plain English: the week days between two dates starts by looking at if the start date is earlier in the week than the end date. If the start date is later in the week than the end date, then add five to the difference between their day of the week (for example, end day of Monday to start day of Wednesday would be 5 + 4-2). If the start date is earlier in the week than the end date, then take the difference between those two days of the week (start day Monday to end day Wednesday would be 4-2).

Next, take the actual days between your end and start date including weekends, divide that by seven then round down. That will give you the number of full weeks between your two days. Multiple that number by 5 to represent the five days within each of those weeks.

Finally, add those two numbers together to get your week days between the start and end dates.

Building the Variables

To create that final equation, we will need to build a few extra columns in the Power Query Editor. For each one, we will click on “Transform Data”, go to the “Add Column” tab, then select “Custom Column”. This will open a window to type in some custom M code (yay another coding language!).

Don’t worry though, I have ya covered with the code you need for this demo. To start, identify your columns with your start date and end date. For this demo, we will have Start Date = Order Date and End Date = Ship Date. The code below will use StartDate and EndDate for these fields, but the GitHub file will use Order Date and Ship Date.

The code below will turn Saturday and Sunday into Friday:

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],-2) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],-1) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],-2) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],-1) else [EndDate])
,Day.Sunday) +1

The code below will turn Saturday and Sunday into Monday

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],1) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],2) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],1) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],2) else [EndDate])
,Day.Sunday) +1

Here’s the formula with some comments added in:

Work Weekday = 
Date.DayOfWeek( //sets us up to get the day of the week at the end of the formula
(if (Date.DayOfWeek([Date],Day.Sunday)+1) = 1 //if it's Sunday 
then Date.AddDays([Date],-2) else //then go back two days to get Friday's date
if (Date.DayOfWeek([Date],Day.Sunday)+1) = 7 //if it's Saturday
then Date.AddDays([Date],-1) else [Date]) //then go back one day to get Thursday's date, otherwise give me the date 
,Day.Sunday) +1 //now give me the day of the week for the date left from the previous if statements

Alrighty, the next building variable we will need for our equation is the actual days (including weekends).

Days Between = Duration.Days([EndDate]-[StartDate])

In our case though, there is a possibility that the orders have not shipped. To handle any errors where there is no EndDate or StartDate, use the equation below:

Days Between = if [EndDate] is null then null else
if [StartDate] is null then null else
Duration.Days([EndDate]-[StartDate])

Okay great, now we can put it all together using our new fields!

Weekdays Between = 
( if [Start Date Work Weekday] > [End Date Work Weekday] 
then ( 5 + [End Date Work Weekday] - [Start Date Work Weekday] )
 else ( [End Date Work Weekday] - [Start Date Work Weekday] )) 
+ ( 5 * ( Number.RoundDown ( [Days Between] / 7 )))

Boom all done! You made it through a lot of complicated M code, nice work! Below is the M used in the GitHub file so you can see how it will look in the advanced editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\KristynaHughes\OneDrive - DataOnWheels\GitHub\AdventureWorks Sales.xlsx"), null, true),
    Sales_data_Sheet = Source{[Item="Sales_data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sales_data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SalesOrderLineKey", Int64.Type}, {"ResellerKey", Int64.Type}, {"CustomerKey", Int64.Type}, {"ProductKey", Int64.Type}, {"OrderDateKey", Int64.Type}, {"DueDateKey", Int64.Type}, {"ShipDateKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"Order Quantity", Int64.Type}, {"Unit Price", type number}, {"Extended Amount", type number}, {"Unit Price Discount Pct", Int64.Type}, {"Product Standard Cost", type number}, {"Total Product Cost", type number}, {"Sales Amount", type number}}),
    #"Added Order Date" = Table.AddColumn(#"Changed Type", "Order Date", each Date.From( Text.Middle(Text.From([OrderDateKey]),4,2)&"/"& Text.End(Text.From([OrderDateKey]),2)&"/"& Text.Start(Text.From([OrderDateKey]),4)),type date),
    #"Added Ship Date" = Table.AddColumn(#"Added Order Date", "Ship Date", each Date.From( Text.Middle(Text.From([ShipDateKey]),4,2)&"/"& Text.End(Text.From([ShipDateKey]),2)&"/"& Text.Start(Text.From([ShipDateKey]),4)), type date),
    #"Added Order Weekday" = Table.AddColumn(#"Added Ship Date", "Order Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 1 
then Date.AddDays([Order Date],-2) else
if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 7 
then Date.AddDays([Order Date],-1) else [Order Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Ship Weekday" = Table.AddColumn(#"Added Order Weekday", "Ship Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 1 
then Date.AddDays([Ship Date],-2) else
if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 7 
then Date.AddDays([Ship Date],-1) else [Ship Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Days Between" = Table.AddColumn(#"Added Ship Weekday", "Days Between Order and Ship", each if [Ship Date] is null then null else if [Order Date] is null then null else Duration.Days([Ship Date] - [Order Date]),Int64.Type),
    #"Added Weekdays Between" = Table.AddColumn(#"Added Days Between", "Weekdays From Order to Ship Date", each if [Days Between Order and Ship] is null then null else
( if [Order Date Weekday] > [Ship Date Weekday] then (5 + [Ship Date Weekday] - [Order Date Weekday] ) else ( [Ship Date Weekday] - [Order Date Weekday] )) + (5 * (Number.RoundDown([Days Between Order and Ship]/7))),Int64.Type)
in
    #"Added Weekdays Between"

Additional Resources

Power BI Adding Dynamic Hierarchies – XMLA, TOM, C#

This post is a continuation of my adventure into the Tabular Object Model and how we can use it to make Power BI scalable and incorporate it into existing .NET applications. Quick refresher, the Tabular Object Model can be accessed through the XMLA endpoint in Power BI Premium workspaces. My previous posts have covered code around adding, deleting, and adjusting columns and measures, but this one will address how to manipulate hierarchies.

Power BI hierarchies are a powerful and easy way to enable end users to dig deeper into their visuals and data. While hierarchies can be a useful resource for self-serve analytics, maintaining hierarchies can be a pain as new levels get added or removed. Thankfully, if you have Power BI premium you can use the XMLA endpoint to add code into existing .NET applications to dynamically add or remove levels from hierarchies as they are created/removed in your application.

Unfortunately, while we can manipulate, add, and delete hierarchies and their levels, visuals already containing the hierarchy will not be automatically adjusted with any new levels/ordinals.

Microsoft TOM Documentation

If you are new to using C# and the Tabular Object Model (TOM), please check out the previous blog post (https://dataonwheels.wordpress.com/2021/10/15/power-bi-meets-programmability-tom-xmla-and-c/) for both an introduction to the topic and detailed instructions on getting the C# portion of this demo stood up. Please reference the DataOnWheels GitHub page for sample PBIX files and C# packages, but note you will need a Power BI Premium workspace with XMLA endpoint write-back enabled in order to run this entire demo.

Power BI Hierarchies

To start out, let’s make sure we understand the components of a hierarchy that we will need to replicate using our TOM script. In the Power BI Desktop app, creating a hierarchy is fairly simple. For example, let’s say I want to have end users drill down from category to subcategory. To do this, I would hover over the category column then click on the three dots next to the category column and select “create hierarchy”.

Next, go to the subcategory column and you’ll notice a new option called “add to existing hierarchy”. Select our newly created hierarchy (default will be named after the top level in the hierarchy), and it will add subcategory underneath category within the hierarchy. Pretty neat stuff but also very manual.

From this, we can see that there are a few components to a hierarchy that we will need to address in our TOM script:
1. Name
2. Levels
3. Order of levels (Ordinal)
4. Column in each level
5. Name of level

Using TOM to See Hierarchies, Levels, Ordinals, and Source Columns

Now that the data model contains a hierarchy, we can publish it up to a Premium enabled workspace in the Power BI service and see it using our TOM script. I won’t go into details on building out this script from scratch, so please reference this blog post for a complete walk through on connecting to your workspace and building a simple C# application to use with this demo.

To list out the hierarchies in the data model, you will need something like this script in your code (entire zip file is in the DataOnWheels github for reference):

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

And poof there it is! Our Category Hierarchy! Next we will have our script list out the levels within the hierarchy.

// List out the levels in our Category hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy"];
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Levels: {level_categoryhierarchy.Name}");
            }

Great, and the next piece will be the ordinal, or the order that the hierarchy levels should be placed. I’m going to adjust the last code so now it will tell us the ordinal/order of each level before it gives us the name. Notice that this starts at 0, not 1.

// List out the levels in our 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}");
            }

And for our final piece of the puzzle, the column name that this level of the hierarchy comes from.

// List out the levels in our 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} from {level_categoryhierarchy.Column.Name}");
            }

Editing a Hierarchy Using TOM

Let’s switch it up and begin editing our existing hierarchy by changing the name of the hierarchy, the name of the levels, the source columns, and swap the ordinances. Typically you will not need to do any or all of these things, but it may be useful in rare use cases.

To start, we will rename the hierarchy itself. Now it will be important to reference the Category Hierarchy by the lineage tag after we rename it. The lineage tag won’t change even after you change the name property of the hierarchy itself. Please note your lineage tag will be different from mine, so run the script that will list the lineage tag next to the name (below) first then replace that portion in the rest of the code where there are references to the reference tag.

// 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
            Hierarchy hierarchy_category = table_product.Hierarchies.FindByLineageTag("9aeadacd-d48d-48cb-948f-16700e030fe7");
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} from {level_categoryhierarchy.Column.Name}");
            }

In the Power BI service, we can check if this rename effort was successful by entering edit mode.

Success! Let’s try changing the name of a level next then swap the order around.

 //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                hierarchy_category.Name = "Category Hierarchy Rename Test"; //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("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category Test";
                level_Subcategory.Name = "Subcategory Test";
                Console.WriteLine($"Category Hierarchy Levels Renamed");

Awesome! Okay now for the final piece of the puzzle – switching the ordinances to make subcategory the top of the hierarchy. Note, you will need to start at level 0. Also, if you are experiencing errors in saving the model, make sure you are out of edit mode in the Power BI Service. While it’s helpful to be in that mode to see your changes, it will be impossible to make additional changes via XMLA until you are out of it.


            //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                hierarchy_category.Name = "Category Hierarchy Rename Test"; //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("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category Test";
                level_Category.Ordinal = 1;
                level_Subcategory.Name = "Subcategory Test";
                level_Subcategory.Ordinal = 0;
                
                Console.WriteLine($"Category Hierarchy Levels Renamed & Reordered");
            }

            // List out the levels in our 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}");
            }

Boom now we have proven we can reorder the levels as well as rename them and the hierarchy itself.

Adding Hierarchy Levels & Hierarchies via TOM

Now we are finally ready to add a brand new level into our hierarchy! In the sample data, the model column should go below subcategory in my hierarchy. To add a level to the hierarchy we will need a few items – the name of the level, the ordering of the level, and the column it should reference. You can add a lineage tag as well (Power BI will not add one unless you made this level in the desktop application). Don’t forget to add the level you’ve created to the hierarchy or else it will stay in cache and never get added.

            //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                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("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category";
                level_Category.Ordinal = 1;
                level_Subcategory.Name = "Subcategory";
                level_Subcategory.Ordinal = 0;
                
                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");

Let’s try making our own hierarchy from scratch. To review, we will need to have a name for our new hierarchy, the name of the levels, the order of the levels, and the column of the levels. We will also need to explicitly add the new hierarchy to the model then add the levels to that hierarchy.

//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");

                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");
            };

Awesome! Now we know we can programmatically add hierarchies, add levels, rearrange levels, rename levels, and point levels to different columns. This won’t apply to many use cases of Power BI, but for those of you embedding a Power BI solution into your application, this should offer greater flexibility and integration with your existing .NET applications.

Additional Resources: