Announcing a New Book on SQL from 3Cloud Authors

I am proud to announce the launch of a new book, centered around the importance of SQL, authored by six consultants from 3Cloud. Our new book is called “SQL Query Design Patterns and Best Practices.” This project was brought to us by PACKT and it’s a unique opportunity as we have five brand new authors on the project. Steve Hughes worked with the publisher to create a plan and recruit authors from within the talented data practice at 3Cloud.

The focus of this book is primarily to help those citizen developers who have limited experience in SQL to expand their knowledge to the next level. While the book can be read end to end, it is designed in multiple parts to support different subject areas that meet the needs of these new developers. In the first part of the book, we spend most of our time dealing with best practices on query design. We follow that up with the implementation of some difficult features that have a great use case in report writing and similar types of queries.

The second half of the book guides developers through using query tuning tools and techniques, including indexes. While our focus is not on creating the indexes, it is important to understand how the indexes can impact and improve your queries as you build your queries. We wrap up with some coverage on modern data estate solutions, including working with JSON, querying files, and creating Jupyter notebooks to manage and share SQL solutions.

About the Authors:

This is not the first book that Steve Hughes has worked on, but that is not true for the rest of the authors. Chi Zhang, Leslie Andrews, Dennis Neer, Shabbir Mala, and Ram Babu Singh worked on a few chapters each in this project, which gives them their first opportunity to coauthor a published book. “As lead on this project, I (Steve Hughes) must say I’m very proud that they tackled this project in such a short timeline. I hope this gives them the opportunity to author more books in the future as we look forward to seeing great things from them.”

I am truly excited to have helped 5 new authors get started!

About the Publisher:

We would like to thank PACKT for their support as we worked through this project. They did a marvelous job supporting this group of new authors on their first project! The project leaders and editors were patient and thoughtful throughout the entire project.

About 3Cloud:

We are all grateful to be working for a company who supports our efforts to become the best consultants in the market and look forward to more opportunities to share our expertise.

If you’re interested in reading the book, please feel free to check us out on Amazon.

Power BI – Empowering Users to Disable Tooltips

Let’s set the scene. You’ve built a wonderful, useful, and descriptive report for your stakeholders with a variety of tooltips that offer deeper insights. They love the tooltips, and have requested more for other visuals, but when they get to the boardroom to present, they are unable to hover over any numbers without the tooltip blocking the context of that data point. Because they love the tooltips for their personal exploration, they don’t want them disabled permanently and have requested a way to temporarily turn them off for presentations. This blog was inspired by Steve Hughes (you may know him) complaining about “too many tooltips in the way of my data”.

This one is for you, Dad.

Tooltips are useful for personal exploration, but not shared exploration.

Steve Hughes

Process

1. Download the images from GitHub:

2. Open your Power BI report and create a duplicate page for the page you would like to disable tooltips on. To do this, right click on the page and select “Duplicate Page”.

3. Rename the new page something like “[original page name] – No Tooltip” by right-clicking on the new page tab. We are also going to hide this page.

4. Next, navigate to the View tab in the upper ribbon and select the “Selection” pane. This will bring up a list of all the visuals on your page.


5. Click to your first visual in the list with a tooltip and in the “Visualizations” pane navigate to the “General” setting to toggle “Tooltip” to off.

6. Repeat by clicking down the list in the selection pane and Power BI will keep opening the Visualization pane to the General tab for easy toggling. Pretty slick. Keep repeating until all your visuals have this toggle turned off. Keep in mind, not all your all your visuals have a tooltip like titles, text boxes, etc.
To close the selection panel, select “Selection” in the upper ribbon to close that panel or select the “X” at the top of that panel.

7. Time to add our icon! Go to “Insert” in the top ribbon and select “Image” then choose the downloaded icon for magnifying glass without check box. This will signal to end users that there are no tooltips turned on for this page.

8. After resizing and positioning the magnifying glass, in the “Visualization” pane turn on Action and select Type “Page navigation” & set Destination for your original page (Overview in this example). I also turn on Tooltip (the only one for this entire page promise!) for this and instruct users to “Click here to turn on tooltips”.

9. Finally, go back to your original page and repeat steps 7 and 8 with the magnifying glass with a check mark to signify tooltips are on. To your end users, this will feel like a button and by keeping visuals on a separate page, you’ll see a minimal impact to performance.

PRO TIP

To have a true button experience, use the “Properties” section of the “General” tab in the “Visualizations” pane to have the visuals align perfectly and be the exact same size.

Working with ALS – ALS Awareness Month 2023

I have been living with ALS for the past two years. I was diagnosed in September 2022 but had my first symptoms and started researching what was going on in July 2021. As I begin to understand my diagnosis more, I realize that this is a progressive disease, and my needs and situation will change over time. In my situation, I lost the use of my arms and hands first and I’ve begun to lose functionality in my legs and torso over the past few months. To learn more about my journey to this point check out this video on YouTube which tells my personal story.

Launching the YouTube Channel

Now that we’ve mentioned YouTube, Data on Wheels is launching its YouTube channel focusing first on my ALS story. While this YouTube channel will support technical content like the Data on Wheels and the Data on Rails blogs, it will also support ALS awareness and accessibility technology.

I realized early on that a blog post was not the best way to share the story or tools that I use while working with ALS. We will have a couple of playlists that are directly related to this part of my story. We will have a playlist called Living with ALS that focuses on my personal journey and various things we’ve learned or experienced in the process. We will also have a playlist that’s called Working with ALS that will focus on the tools I use to keep me working and how I keep working as my disease progresses.

While I wish we could have started this much earlier, the disease itself has slowed my ability to complete some of these tasks as quickly as I wished. It is with a lot of help from my family who have supported me through video editing, content support, and motivation that we’ve been able to get this launched. I hope you enjoy it for what it is, and I look forward to having a number of you follow and learn more about accessibility.

Global Accessibility Awareness Day 2023

I had the opportunity to work with the Voice Access team from Microsoft on a story video that was used on Global Accessibility Awareness Day, May 19, 2023. I am truly impressed with the work Microsoft has done with Windows and Office to create more accessible experiences for those of us who need help doing our day-to-day job. When I think about how Voice Access has truly helped me be productive in my day-to-day work needs such as IMs in Teams, it was a pleasure to work with them to do a video to promote this tool. You can check out the video in the Voice Access area here. You can also learn more about the other accessibility options available in Windows 11.

Supporting the Cause -ALS Walk Lexington, August 2023

As part of the effort we’re doing around ALS Awareness this month, Data on Wheels is the team name for our team that will be doing the ALS Walk in Lexington KY this year. This is an opportunity for us to do our part to help the local community continue to do great things for the ALS community. Check out our team site and support if you can. Every little bit counts. If you’re in the area and want to join us for the short walk that happens in August feel free to sign up on the team.

3Cloud – Inclusive and Supportive Workplace

I was employed at 3Cloud when my disability first surfaced. As you can imagine, it was an emotional roller coaster and I really needed to determine how much it would affect my ability to do work for the company. As you have seen above or in one of my stories, this disease first affected my hands and arms and my ability to type. Without knowing the speed or rate of the disease and the impact to my current workload, I was able to work with my bosses to find a good place for me in the company which allowed me to contribute while I sorted this out. With their help, I have been able to find a place to continue to contribute and participate in the technology that I love to work with. For example, just this past week, Microsoft released a new product called Microsoft Fabric that I’ve been working with them and our team on to understand how to best work with it in our field. The company has trusted me with this and other initiatives that have allowed me to continue to be productive and generally contribute to the growth and success of 3Cloud.

Wrapping It Up

ALS has changed a lot about how I work and how I view life. I hope that what I share about what I learn helps others in similar situations. I will continue to be as active as I can both through work and in the community. Each day is a new day. Some days bring good things and some days bring bad, but God is in control of it all. Thank you for your continued support!

Power BI Cruise Recap – Deep Data Dives

Thank you so much to those of you who were able to attend this three-hour, C# packed session! The Power BI Cruise was an incredible conference, and I cannot recommend it enough if you’re looking for an opportunity to dig deep on Power BI topics with other passionate individuals. From the first ever Power BI bingo to a three hour session on TMDL from Mathias himself, this was a trip that will stick with me for a while. In this post, I’ll include notes from my session and the sessions I attended plus links to the GitHub. Let me know if anything really piques your interest and you’d like to see more detailed blog posts about it!

  1. My Session – Power BI, C#, and TMDL!
  2. TMDL & Source Control – Mathias Thierbach
  3. Hacking the Visuals – Stepan Resl
  4. Ask Me Anything – Jeroen (Jay) ter Heerdt

Huge shout out to the other speakers and of course the incredible organizers who stuck with this amazing vision through the pandemic and made it happen. Thank you all so much for making this event full of passion and unparalleled learning. I don’t know how you all made this happen without sponsors, but it was truly an incredible event. So thank you again Asgeir, Erik, Johan, and Just!

My Session – Power BI, C#, and TMDL!

Now you may have noticed I give this session – Power BI Meets Programmability – quite often. Bringing programmability to the world of Power BI is something I greatly enjoy, but this session was truly unique. Having three hours to take a deep dive into what C# can do for Power BI development was the opportunity of a lifetime. With two more hours than usual, we explored creating calculation groups in this session for the first time as well as creating automatically generated set of measures based on data types. Not only that, but the attendees were good sports and stuck around for an extra half hour to create calculation groups in C# using TMDL! I plan on doing a very detailed blog on this in the future, but the GitHub folder for this conference (link below) contains TMDL code live coded by the creator himself – Mathias Thierbach! The script contains a section that will create a TMDL version of your data model, allow you to interact with TMDL files within your solution, and publish those changes back to the data model. It blew my mind, and I hope it blows yours as well!

Additional Resources

The final code can be found here: https://github.com/Anytsirk12/DataOnWheels/tree/main/Power%20BI%20Cruise.

TMDL & Source Control – Mathias Thierbach

Speaking of TMDL, this conference hosted the incredible Mathias Thierbach for a session on TMDL & Source Control. Below are my notes from that session, but if you ever have a chance to hear him speak on the subject I highly recommend it. The notes below are in bullet point format for now, as I dig further into this language I look forward to writing more detailed blog posts about this incredible language and what we can do with it.

  • Key goals of TMDL: readable, editable, and allows collaboration
  • There will be a VSCode extention for TMDL coming soon (we were given a special preview in the session) that will have syntax highlighting for both DAX and M as well as TMDL
  • Shortcut to get to the TOM docs: goto.pbi.tools/tom-docs or pbi.onl/tools
  • TMDL is completely case insensitive!
    • Default export of .tmd file is camel case
  • Boolean properties: you only have to say the Boolean property to enforce the default value
    • To explicitly set it, syntax is:
      property: true/false
  • No matter how many times you serialize TMDL, the order of the objects will always be the same
    • Tables: partitions, calc groups, columns, hierarchies…
    • You can reorder objects
    • You can reorder objects to your specifications (aka no longer alphabetical)
  • Meta data IsParameterQuery = true makes it a parameter
  • Ordinal is not a table property in TOM, but you can create one in TMDL! It won’t show up in this order in Desktop…yet. But it will allow consistent order. It’s a weak ordinal, in cases of conflict it won’t cause any errors. It should default to alphabetically on conflict.
  • Indentation for expressions is not white space sensitive (DAX and M)
    • It finds a shared whitespace to the left to create indentation in the final model
  • Indentation only matters for nested objects
  • To fold levels in VS Code use Ctrl + k + [level_number]
  • To unfold levels in VS Code use Ctrl + k + j to unfold all levels
  • Unicode is supported
  • Shared expressions = parameters
  • Preview 1 (what we have), perspectives can’t be read back but can create them
    • Will be fixed in Preview 2
  • 4 previews total, goal is for GA at end of the year
  • tableName.columnName etc.
    • If there are spaces, use single quotes as a delimiter
  • Description is added by using /// text on top of the object declaration. Can be multi-line and line break is maintained.
  • No comment support but you can comment within M and DAX
    • Triple ‘’’[formula] ‘’’ will be a safe way to mark M/DAX expression sections. Likely coming in preview 3 or 4 later this year
  • Language spec will be published
  • Default properties are available to find in Rui’s documentation
    • Default property is what is after the = for the object
    • This can be found on the main TMDL overview page

How to create a TMDL folder from Tabular Editor (need 2.18.1 version or later)

Open the folder you’ve created within VS Code.

Hacking the Visuals – Stepan Resl

Hacking = use various available means to get better insights from data.

This session absolutely blew my mind. Stepan does an incredible job showing the art of the possible by hacking into expression properties within tabular editor as well as calculation group formatting. We had an awesome time connecting at this conference and I hope to see him again at future conferences! In the meantime, below is a link to his GitHub as well as a blog post that covers a lot of the same material as his session. Be prepared to have your mind blown on this one.

Stepan’s GitHub for this session:

https://github.com/tirnovar/public-speaking/tree/main/Power%20BI/Power%20BI%20Cruise%20-%202023

Additional blog post by Stepan on this subject: https://datameerkat.com/conditional-formatting-calculation-groups

  • For bar charts – to easily highlight what is over target, change the color within the bar at the target with grey below the target
  • Visual Vocabulary = a great reference for chart types
    • Ft.com/vocabulary
  • / used in a format string will escape the wildcard characters
  • Keep in mind audience, green is not positive in Japan
  • topProductByLocation = TOPN(1, ALLSELECTED(Products[ProductName]),[# Total Quantity])
  • # sold quantity by Top Product = VAR _product = [topProductByLocation] RETURN CALCULATE([# Total Quantity], Products[Product name] = _product
    • This allows us to use a measure as a filter by precalculating it in a variable
  • Best whitespace option is to use UNICHAR 8203, it’s a very very small dot
  • Color picker: Just Color Picker
  • Use smart text in subtitles in charts to tell people when we hit a target or how close we are
  • You can use format to change numbers to text with a thousands delimiter! See the subtitle measure
  • You can use ; within format to do an if statement! BUT don’t forget to escape any special characters using \
    • Example: “Sales target was set to ” & FORMAT(_target,”#,,,,,,,,,,#”)
      & ” and we have sold ” & FORMAT(_sold,”#,,,,,,,,,,#”)
      & ” which means we ” & FORMAT(_sold-_target,”\have;\haven’t”) & ” fulfilled our target.”
    • In this case “h” is a special character that is trying to get an hour value and will show 0 if you don’t escape it
  • If you create conditional formatting in bar charts, the colors will stay if you switch to a line chart even though conditional colors are not available in the UI
    • Same thing happens if you have an x constant line in a line chart and switch to bar chart
  • You can customize spacing below titles and subtitles.

I’ve added the final PBIX from this session to the GitHub, keep in mind a lot of the features that make it possible are only visible through tabular editor: https://github.com/Anytsirk12/DataOnWheels/blob/main/Power%20BI%20Cruise/Hacking%20the%20Visuals.pbix

Ask Me Anything – Jeroen (Jay) ter Heerdt

Meeting Jay and chatting during various free time on the ship was definitely a highlight of the trip. He’s incredibly knowledgeable on Power BI (especially the elusive DAX language), and has a deep understanding of the Microsoft strategy on new feature creation. It was a pleasure getting to laugh and brainstorm together, and I can’t wait to hang out again at future conferences. Below are some questions that were asked during the AMA session as well as answers provided and links I found while Jay was talking.

  • Why can’t we use RLS and USERELATIONSHIP() together?
    • RLS blocks USERELATIONSHIP() since USERELATIONSHIP() can (but doesn’t always) go around the RLS relationship and creates a security risk
  • Different people own different visuals which is why the options and customizations vary so widely
    • Miguel Myers did a session on the future of visuals in Power BI. Huge plans with him on board to own visuals and align everything
    • There is a linked in group called PBI Core Vision, put comments there on what can be improved in the future. Twitter and LinkedIn
  • When we change the hierarchy name or order via XMLA, it now breaks the visual. Can we get this to update in the visual in the service with adjusted level orders
    • SEND TO JAY
    • Also ask him about Top N + Others
  • Tableau is the primary competition for PBI, Looker is up and coming as Google, and third is Qlik because it’s still getting new clients in Europe
    • These are taken into consideration when making a case for new items
    • Cannot blatantly copy items
    • Tableau has specific people in their agreement that cannot even look at Tableau (can only look as a user, not at the debugger nor dll file)
  • In the future, hoping to have functions that could be shared within a community for DAX
  • Could we get Rulers or aligned grid lines in PBI Desktop?
    • Rosie or Ree-ann would own this
    • Can we customize grids?
    • Not part of visual team, it’s placed under on-boarding
  • Advancements to API?
    • Hardening of PBIX will help with getting better Scanner APIs to get deeper API’s (visual level and page level details on what’s available within the tenant)?
  • DataZen got killed and unsure why
  • Jay would investigate how to get rid of filter direction in DAX
  • Why can I not put a measure name in smart narratives?
    • You can do this by creating a shape and using fx to put in the text you want

Typing with Your Tongue Part 2: Voice Access

A few months ago, I wrote a post on how I use voice technology to continue working with my ALS condition. Since that post was written, Microsoft released a new voice technology called Voice Access as a part of the Windows 11H2 update. I’m going to talk a little bit about my experience using it. It has changed how I interact with my PC and which tools I choose for voice to text.

Voice Access

You can turn Voice Access on if you’re running the latest version of Windows 11. (Voice Access is not available in Windows 10). You simply go to the Accessibility settings and choose the Voice Access option under Speech. Once you have turned on Voice Access, you will see a bar across the top of your primary screen as shown in the image below. This is how you know you have Voice Access ready to go.

Voice Access is much more than a voice to text tool. Voice Access includes many command tools including a mouse grid option which allows you to grid your screen and select items on the screen using only your voice. Voice Access supports commands such as Open and Close for application windows. You can find out the full list of commands that are available to you in Voice Access here.

How do I use Voice Access?

I typically have Voice Access on all the time except when I’m in meetings. (It will try to type everything that’s said in the meeting, so it needs to be turned off at that time.) There are only two tools where I use Voice Access less often. That would be Outlook and Word. More on that in a bit.

Because Voice Access alternates between dictation and commands, I able to use it when working with most tools. For example, with Windows Mail I will use it to dictate an e-mail, and then click Send to send the same e-mail. When I say “click send”, Voice Access finds the Send button on the window. If there are more than one, it will give me an option to select which Send I meant to click. I find the overall experience pretty good as it allows me to switch between dictation and commands without issue.

I use Voice Access a lot when working with Teams, WhatsApp, and other chat-based tools. Voice Access allows me to have a good voice to text tool in applications that typically do not have great accessibility support. At times, I have used Voice Access instead of Dictation in Office 365, especially when working with PowerPoint and Excel. Neither of these are particularly voice to text friendly and Dictation in PowerPoint is significantly lacking.

Using Office 365 Dictation

I primarily still use Office 365 Dictation when working with Word and with Outlook. Dictation in both tools responds quicker than Voice Access. It also handles some of the issues that are currently being worked on in Voice Access such as punctuation. For example, the bulk of this blog post was authored in Word using Office 365 Dictation because it’s quick, simple, and works well within the context of voice to text.

Other Insights into How I Work with These Tools

Office 365 Dictation is fully online. This means if you lose your internet connection, you lose the ability to use that function. Voice Access on the other hand does not have this dependency and will continue to work without a connection.

The commands between these two tools still vary quite a bit. For example, text formatting is very different between the two tools. In Word, they use “capitalize” to capitalize a word using Dictation. (By the way this has been improved since the last time I wrote my blog post. This improved capability in Office 365 Dictation is huge). In Voice Access you have to say “capitalize that” to capitalize a word or selected words. When you use that same command in Office 365, it will capitalize the whole sentence. This is true of other formatting commands between the two tools.

There are enough variances in those commands to cause frequent issues when working with the two tools interchangeably. I find myself using the wrong command to create a new line or change the case of words regularly. This is something you will have to work through if you go back and forth between the two tools.

I started working with Voice Access while it was still in preview. I am actively working with the Voice Access team and providing feedback about the tool. While many updates have been made, there are still issues with the tool as it continues to mature. If you’ve been working with Windows Speech Recognition and Voice Typing, I would still recommend switching to Voice Access as the experience is much better. Just keep in mind it is still new and you may still run into issues here and there as they continue to improve the product.

When Voice Access became available, I switched immediately to Voice Access as my primary voice command and dictation tool when not using Office 365 (Word and Outlook). I think that this technology will continue to improve and make voice accessibility better for those of us with limited functionality in our hands and arms. As you know from previous conversations, I’m not a huge fan of Dragon because of how it changed my workflow and made things more difficult for me to do in general. Voice Access is a more natural fit into my workflow, and I like it considerably better. I do find myself going back and forth between my roller bar mouse and using a complete voice solution when my hands get tired. That speaks volumes of the work done in Voice Access to help someone like me continue to function by having options in the tools I can use. I look forward to the improvements as they come along.