We can now pass dynamic values to linked services at run time in Data Factory. This enables us to do things like connecting to different databases on the same server using one linked service. Some linked services in Azure Data Factory can be parameterized through the UI. Others require that you modify the JSON to achieve your goal.
Recently, I needed to parameterize a Data Factory linked service pointing to a REST API. At this time, REST APIs require you to modify the JSON yourself.
In order to pass dynamic values to a linked service, we need to parameterize the linked service, the dataset, and the activity.
I have a pipeline where I log the pipeline start to a database with a stored procedure, lookup a username in Key Vault, copy data from a REST API to data lake storage, and log the end of the pipeline with a stored procedure. My username and password are stored in separate secrets in Key Vault, so I had to do a lookup with a web activity to get the username. The password is retrieved using Key Vault inside the linked service. Data Factory doesn’t currently support retrieving the username from Key Vault so I had to roll my own Key Vault lookup there.
I have parameterized my linked service that points to the source of the data I am copying. My linked service has 3 parameters: BaseUrl, Username, and SecretName. The JSON for my linked service is below. You can see that I need to reference the parameter as the value for the appropriate property and also define the parameter at the bottom.
I have defined these three parameters in my dataset, along with one more parameter that is specific to the dataset (that doesn’t get passed to the linked service). I don’t need to set the default value on the Parameters tab of the dataset.
On the Connection tab of the dataset, I set the value as shown below. We can see that Data Factory recognizes that I have 3 parameters on the linked service being used. The relativeURL is only used in the dataset and is not used in the linked service. The value of each of these properties must match the parameter name on the Parameters tab of the dataset.
In my copy activity, I can see my 4 dataset parameters on the Source tab. There, I can write expressions to provide the values that should be passed through to the dataset, 3 of which are passed through to the linked service. In my case, this is a child pipeline that is called from a parent pipeline that passes in some values through pipeline parameters which are used in the expressions in the copy activity source.
And that’s it. I can run my pipeline and have it call different REST APIs using one linked service and one dataset.
I built this pre-con to help people better approach report design as an interdisciplinary activity where we are communicating with humans, not just regurgitating data or putting shiny things on a page. There are many misconceptions out there about report design. Some people see it as just a “data thing” that only developers do. Many BI developers avoid it and try to focus on what they consider to be more “hardcore data” tasks. I often hear from people that they can’t make a good report because they aren’t artistic. This hands-on session will dispel those misconceptions and help you clarify your definition of a good Power BI report. You will see how you can apply some helpful user interface design and cognitive psychology concepts to improve your reports. And you’ll leave with tips, tricks, and a list of helpful resources to use in your future report design endeavours.
Your report design choices should be intentional, not haphazard or just the Power BI defaults. We’ll review guidelines to help you make good design choices and look at good and bad examples. And we’ll spend some time as a group creating a report to implement the concepts we discuss.
Basic familiarity with Power BI is helpful for attendees. If you know how to add a visual to a report page, populate it with data, and change some colors, that’s all you need. If you feel like you lack a good process for report design to ensure your reports are polished and professional, this session will share an approach you can adopt to help accomplish your design and communication goals. If you feel like your reports are luckluster or not well-received by their intended audience, join me to learn some tips to improve. If you are a more experienced report designer and you want to learn some new techniques and see the latest Power BI reporting features, you’ll find that information in this session as well.
So far, I’m scheduled to present this session at two SQLSaturdays in Q1 2020:
Every once in a while, someone asks a question like “Can Power BI be accessible?” or “Is Power BI WCAG compliant?” It makes me happy when people recognize the need for accessibility in Power BI. (I’ll save the discussion about compliance not automatically ensuring accessibility for another day.) But most people don’t appreciate the answer to either question.
The answer is that WCAG compliance and accessible design are highly dependent upon the report creator. Microsoft has added many built-in accessibility features such as keyboard navigation, high contrast view, and screen reader compatibility. But they can’t make your report automatically accessible as there are accessibility features requiring configuration by the report designer. We need to set the tab order and alt text and use descriptive chart titles – there is no artificial intelligence to do this for us (yet?). Beyond that, things like color contrast and colorblind-friendly design are almost entirely the responsibility of the report designer.
Accessible design used to be solely the domain of UI developers. But as we democratize analytics to have everyone building reports, we now have to create awareness and a sense of responsibility among Power BI report creators, especially those who don’t consider themselves developers.
There is a similar challenge going on with data security. It used to be that people thought of it as a concern only for the IT department. Now, it is widely accepted that everyone in an organization plays a role in maintaining data security. I hope the same attitude will be widely adopted when it comes to accessibility in data visualization and analytics.
This challenge is present in any low-code environment with users of diverse backgrounds and technical expertise, which means it is relevant to the entire Power Platform and other similar tools. There is a white paper on PowerApps Accessibility Standards and Guidelines that has a great description of the situation.
PowerApps embodies the idea of “democratization of development”—anyone in your organization can quickly and easily create a powerful app and share it broadly. But the app maker has an ethical, and sometimes legal, obligation to support “democratization of usage” as well—any user of your app must be able to use it as it was intended.
Based upon the popularity of the Power Platform, I’d say the democratization of development has been a wild success. But we still have some steps to take to democratize usage. Microsoft is doing their part to make their products accessible and to fix accessibility bugs quickly. Now we need to recognize and honor our obligation to design inclusively.
The Microsoft Docs on accessible report design were recently updated to provide more guidance. I hope you’ll check them out and start implementing the recommendations in your reports.
DCAC recently had a custom PowerPoint template built for us. We use PowerPoint for teaching technical concepts, delivering sales and marketing presentations, and more. One thing I love about working at DCAC is that each of the 6 consultants is also a speaker at conferences. So we all care about making presentation content understandable and memorable.
In addition to the general desire to be an effective presenter, I have a special interest in accessible design. I speak and consult on accessibility in Power BI report design. So I feel a responsibility to try to be accessible in all areas of visual design. In addition, I know that making things more accessible tends to increase usability for everyone.
PowerPoint templates in general reduce manual efforts to assign colors, set fonts and font sizes, and implement other design properties such as animations. I’m all for making a decision once, templatizing it, and making it easy to instantiate. But most of the templates available within PowerPoint and sold online don’t follow good presentation design practices. That is to say, the formatting gets in the way of the content.
Having a template made
Anyone can make a PowerPoint template. You just have to learn how master slides work. Lots of marketing and graphic design companies offer to create PowerPoint templates. But they are mostly concerned with staying on brand or making something shiny. And people make presentations because we want to communicate information. I wanted our template to make it easy for us to follow some good design practices, including accessibility.
So I reached out to my friend Echo Rivera who has her own company, Creative Research Communications, that specializes in making visually engaging presentations about highly technical topics. I came across one of her tweets a couple of years ago and visited her website. Some of the things I have learned from Echo’s courses and blog posts include:
Echo worked with me to make a template that followed good design practices but was flexible enough for a variety of content and audiences. And we also made the template fairly accessible! I’m pleased with the way our template turned out. A template can’t do everything for you, but it can give you a good start.
The process of presentation template design
I found the process of designing the template interesting, so I thought I would share a bit about that.
First, I sent Echo some samples of the presentations we give and some ideas on a color palette. We met to discuss our corporate personality, our presentation needs, and our goals for the template. Then a few members of the DCAC team helped me pick some potential images for the title slide.
Next we picked icons and colors for sections that we thought we would commonly include in presentations. We stuck with a white background on some content slides to make it easier to use images and diagrams from online (so we wouldn’t have to expend effort removing image backgrounds when they weren’t transparent). It also made it easier to choose colors with good contrast.
Once we got the full draft of the template, the team reviewed it and tried it out on some slides. And because things were going too smoothly, we decided we didn’t like the image we selected for the title slide, and chose another one. We made a few adjustments to default font sizes so that slides would fit our company name and slide headings with long technology names on a single line. We also adjusted the position of the slide heading text on slides with the line and icon at the top (shown below) so the text didn’t sit directly on the line.
We made a version of the slide template that has a small single-color logo on each slide. I plan to use this for sales slides and other situations where we need to ensure our name is on a slide when taken out of context (like a screenshot of a single slide). But the logo is small and not distracting so it doesn’t negatively impact design.
We made the template expecting that team members would adjust the master slides and use the provided multi-purpose layouts as needed for each presentation. We will change icons and use blank slides to add diagrams and big images. We also have multiple options for title and thank you slides.
Benefits of our new PowerPoint template
There is a lot to the new template, and examples below are shown without a ton of background explanation. But I couldn’t write this blog post and not show some examples, and I didn’t want this post to turn into a novel.
Our slides use color contrast to create visual interest while staying close to our corporate color palette.
The spacing and font sizes allow for about 6 lines of text max in an all-text slide. Our default font size for text content is 32pt.
We used icons and color to denote sections in our presentations.
I think the best thing that this process has done is encourage us to use less text and more images. Check out the recently created slides from one of John’s presentations below.
color contrast that meets WCAG standards (3:1 for large text, 4.5:1 for smaller text)
avoiding using color as the only means of conveying information
avoiding color combinations that are problematic for people with color vision deficiency
Echo worked patiently and diligently with me to measure and adjust colors to meet these criteria. It was great to have someone that understood these goals.
Feedback from other team members
To be honest, I think it took some time for the template to grow on people. But most of us have warmed to it. If you are used to small images, lots of text, and extensive use of clip art and smart art, switching to the template can be a bit difficult. But that’s kind of the point.
Echo gave us some videos on how to use the template and some slide makeover examples when she delivered the template, and I asked the team to watch this TEDx talk. That reinforced the design goals and choices.
I asked the team for comments, and this is what they said.
“I like having a professional-looking, themed template that isn’t just something out of the box that looks like everyone else’s. The template, plus the helpful background info force you to think about what you are puking onto a single slide. It will lead to some semi-major rewrite/re-evaluation of about every presentation I do.”
“The template, in part with its notes and reminders, helps me to ensure that I put the proper amount of context on a given slide. It’s a balancing act with font size and content. If I have to reduce the font size below 32 pt and I still have content to provide, it goes on another slide.”
Will every slide we make be perfect and beautiful? No. But this template will help us avoid common mistakes like having too much text on the slide, and it provides some built-in accessibility without us having to remember to make adjustments.
I really enjoyed working with and learning from Echo, and I recommend her for your presentation design needs. I want to note that she prefers that you take her training before she does custom design work for you so you have a mutual design foundation to build upon. But her courses are great, so that’s not a hardship.
It’s also a privilege to work with people that share and support my goals of effective presentation delivery and accessible design. PowerPoint templates are part of the branding and marketing collateral in an organization, and I love what this says about DCAC.
I’m happy to be speaking at Microsoft Ignite this year. I have an unconference session and a regular session, both focused on accessibility in the Power Platform.
The regular session, Techniques for accessible report design in Microsoft Power BI, will be Wednesday, November 6 at 2:15pm. In this session I’ll discuss the features available in Power BI for making accessible reports and demonstrate techniques for making your reports easier to use. This session will be recorded, so if you can’t make it to Ignite, you can catch it online.
My unconference session, Accessibility in the Microsoft Power Platform, is a chance to have a discussion about accessibility in Power BI and PowerApps. It will be held on Thursday, November 7 at 10:45 am. Unconference sessions at Ignite include facilitor-led discussion and exercises that encourage audience participation where everyone can share their experiences and opinions. If you will be at Ignite and want to share struggles or successes in improving accessibility or raising awareness of accessibility issues, please join me.
This year at Ignite there is a reservation system for unconferences. You can RSVP while you are building your schedule on the website. Walk-ins will be accepted just before the session, assuming there is room. But please RSVP if you want to be sure to get a seat in an unconference session. Unconference sessions are not recorded, so this will be an in-person session only. But I will post materials through the Ignite website once the session is over.
If you will be at Ignite, please stop by and say hello and meet Artemis the Power BI accessibility aardvark.
We often focus on deep analysis and insights generated by machine learning when we talk about Power BI these days because it’s super cool and very fancy. But I think it’s important to remember that you can also use Power BI for simple communication of data. As humans, we are hard wired to process visual information faster than text alone. And it’s often more efficient and concise to convey information in a visual rather than text. We can show in one image what it takes paragraphs to describe. Outside of historical analysis and operational reporting, we can use data visualizations as an engaging way to convey simple facts in a communication or marketing effort. Many people do this with infographics.
Visualization can make simple numbers and facts more memorable and engaging. I enjoy using Power BI for this marketing communication purpose, so I made a report about the people I work with at Denny Cherry & Associates Consulting. I knew most of them before I started working at DCAC, so I was already aware that they were exceptional. But now you can see it, too, with our new Power BI report located at https://www.dcac.com/about-us/learn-more-about-us.
Data is some people’s preferred language. We can use it to discuss specific points as well as broader trends and comparisons. I have a sticker on my laptop that says “Please Talk Data To Me” for a reason. I can make sweeping statements about how my team is full of accomplished consultants, speakers, and authors. But it is much more impactful when you see the numbers: 27 Microsoft MVP awards and 8 VMWare VExpert awards over the years, a combined 113 years of experience with SQL Server, 86% of us having been a user group leader.
And because Power BI is interactive, you get to interact with my report, choosing which categories you want to learn more about. There is also a little guessing game built into the report. (Edit: Although there is currently a Power BI bug that I had to work around, so it’s helpful to know that the answers are limited to values 1-2500). But that is a blog post for another time, and for now you get a hint as to possible answers.) If I had just told you these stats, you might have listened, but you probably would have tuned out.
Our team even had fun browsing the report, seeing our collective achievements, and guessing who provided which answers.
Building the report
It was quick and easy to use Microsoft Forms to gather the data and then dump the responses to Excel. From there, I made a quick Power BI data model and put together my visualizations. The report is embedded on our website using a Publish to Web link. A few people have said they would like to do something similar for their organizations, so I’ll offer a couple of tips:
Make sure the questions you ask aren’t violating any HR rules, and that employees know that some or all questions are optional. For example, I asked personal questions in the Fun Facts section about number of children employees have. This question was optional and our team felt comfortable answering it.
Try to group your questions into categories so you can easily the separate sections and pages like I did. Then you can associate a color with each category.
If you do make something similar, I’d love to see it. Tweet me a link or screenshot at @mmarie.
I’m busy building presentations for some upcoming conferences, so in lieu of a full blog post, please read my twitter thread about making your presentations more accessible. All but one of these tips are applicable regardless of the software that you use to build presentation content.
Why lose the engagement of a single person in your audience because of poor design choices? Most of the design tips I list are not that difficult to implement, and many of them can be built in to your presentation template, which I hope you are customizing to fit your content (and yes, I’m aware of the struggle of using templates provided by conference organizers). See below for my thoughts on the built-in templates in presentation design software.