Business Intelligence is not just reporting

Whenever I start a conference presentation, I lead with “Business Intelligence is not just reporting!”  This comes about as a call to get new Power BI users to look more broadly. If all you are doing with Power BI is replicating the Excel spreadsheets you already have, you are missing out on significant value. If your users want their new dashboards to look like a spreadsheet, a conversation is warranted.

Let’s discuss three ways that Business Intelligence is so much more than reporting.

BI is your first step towards AI

A lot of companies today are intrigued by the potential of artificial intelligence. Some of them have started projects to implement AI. The issue that many companies will run into as they start the path toward AI is data quality. For AI to be successful, you must have good quality data to train the models. If you haven’t started down the BI path yet, chances are, your data quality is poor at best. Jumping into AI with poor data quality will lead to incorrect outcomes.

BI forces you to clean up your data and processes

Another positive outcome of BI projects is a rationalization of entity and business rule definitions. For example, in a recent project, the client wanted to monitor product profitability across the board. The issue was that the definition of a product was different based on the division producing it. Consequently, we had to find common data ground for such an analysis to be made.

In other projects, we encounter timing discrepancies, dependencies on manual effort for key data, and incomplete data. It’s very difficult to have useful near real-time analysis over data that is received once a month from Finance. These timing issues must be worked out across processes.


Do you really want to bet your career on the numbers within a manually maintained spreadsheet?

BI can eliminate a lot of hidden manual effort

Manual data gardening of spreadsheets is taking up a lot of time in organizations and creating potential problems. Do you really want to bet your career on the numbers within a manually maintained spreadsheet? The sad truth is that many managers do just that.

Also, the manual effort is robbing you of your most precious resource, time. There was one organization where every one of their twenty project managers were spending six hours a week manually creating status reports. Products like Power BI and Flow can be used together to automate the collection and dissemination of data within the organization, freeing up time for more valuable work. A small project like this can easily justify the license expense of Power BI.

BI can set the stage for further successes

In the end, a successful BI journey creates fertile ground for a potentially successful AI implementation. In the end, we will achieve Collaborative Intelligence (CI) where the AI tools augment the human and make them more effective. Many of the most amazing uses of AI are focused on shortening or removing the learning curve, instead of replacing the human.

BI can’t do it alone

Lastly, one of the other challenges we see is the idea that simply bringing in a new BI tool will magically produce results. Time and again, organizations make software investments without the requisite investment in people. To get the highest benefit from a BI tool, your organizational culture must make data part of their day to day activity. Creating a data culture requires active investment and time.


One sign of a company that has achieved a data culture is that data has a “voice.” You’ll hear people ask in meetings, “what does the data say?”

The rise of Data Culture

One sign of a company that has achieved a data culture is that data has a “voice.” You’ll hear people ask in meetings, “what does the data say?” While upgrading software is easy, upgrading people’s habits is much harder. If you are starting the journey, you should consider how you will invest in training and in a community of practice to sustain the change long term. We’ll discuss data culture in more detail in a subsequent post.

We can help you

Without the accompanying culture change, your BI tool implementation may simply be the latest “change tsunami” to wash upon your corporate shore. These waves create massive chaos today as they impact people’s work habits. Then the project washes out after a few months, never to be heard from again. The damage to your co-workers’ trust will remain for a long time.

I hope you can avoid these issues. If you need help navigating the change, please reach out to us as we can help you.

Controlling Power BI Filter pane visibility with Bookmarks

I was working with some bookmarks in a Power BI model earlier when I noticed that in some cases the Filter pane was expanded and in other cases, it was collapsed. I was intrigued so I started investigating.

It seems when you save the bookmark, it now retains the open/close state of the Filter pane. I believe the Display option within the bookmark has to be set in order to control this. So, if you wish to show the Filter pane by default on a report, expand it in the PBI Desktop and update your bookmark. Collapse it and save bookmark if you want it collapsed by default.

If you have a question or comment, post it below.

Useless Solutions: Windows 3.1 Hot Dog Stand Theme – Power BI style

On occasion, useless solutions like this will be posted as they may not be directly useful in production, but they are educational in value and can lead to useful work.

Oh goodness, what have you done?

This post originally started as a joke at dinner with Adam Saxton of Guy in a Cube and Jason Himmelstein of the BiFocal podcast. It was worth it to see the look on their face when I mentioned I was bringing the Hot Dog Stand Theme to Power BI. And now I have.

What is “Hot Dog Stand?”

For those who have no idea what this is about, Windows 3.1 shipped with a theme called Hot Dog Stand. Coding Horror has a nice write up with screen shots here. It was hideous and no one knew why it was there. It became a joke to change someone’s theme to Hot Dog Stand if they left their workstation unlocked. Hot Dog Stand never made it into Windows 95 and faded, for the most part into history.

An opportunity arose

As luck and work would have it, clients were asking for custom themes and a deep dive into the Power BI themes format was necessary. Hence, the idea to combine wacky fun with learning the ins and outs of theme JSON descriptors by recreating Hot Dog Stand.

Getting Started

I started where likely most people start, the Microsoft docs on Power BI themes here. It’s a helpful document but wow, there’s a lot of potential coding to do here. I needed to get rolling more quickly.

Cool, a theme generator

Next stop was the PowerBI.Tips theme generator. This tool is fairly easy to use to generate a quick theme file. it creates the JSON but has limited options on what can and can’t be changed. The results were ok, but I wasn’t feeling the serious ugly of Hot Dog Stand yet.

Even cooler, a GitHub repository!

After some web searches, I can across this GitHub repository of Power BI theme snippets. David Eldersveld put this repository together, probably due to the same reasons that brought me here. I needed to do more customization but I didn’t want or was able to hand code all of the particulars.

The free VS Code made this pretty easy to assemble. You will likely want to use a JSON formatter as you are doing this. Code may have one but in the interest of moving fast, I found this site that did the job well.


One tip is that if you are going to merge many of the snippets into a theme file, ignore the first three and last two lines of every snippet. Otherwise, you’ll get errors importing it.

The Result

To reiterate, Hot Dog Stand started as a theme generator generated file that I edited in VS Code and augmented with snippets from GitHub. The result is this.

Isn’t it hideous?

If you would like a copy of the theme file, to do your own terrible things with, download it here. If you have questions or comments, please post them below. Check out our Twitter feed at @tumbleroad.

The easiest way to do small-multiples in Power BI!

Recently, there was a post on the Power BI blog about how to do How to do multivariate reporting with Power BI or what you may know as “Small-Multiples.” Small-multiples allow you tell a big story via a chorus of little stories, that would otherwise be obscured by the large number of plotted points. There are many uses of this technique in the data visualization literature. If you are unfamiliar with small-multiples, please read this article as a primer.

In October 2016, Microsoft Research released the Infographics Designer custom visual for Power BI. Much of the attention was focused on how you can use graphics to create custom charts. However, buried in all of this was the ability to create small-multiples easily, using the Infographics Designer.

The Infographics Designer allows for four types of small-multiple visualizations.

  • Cards
  • Columns
  • Bars
  • Lines

In the video below, I’ll demonstrate to you how to use the Infographics Designer to create multiple types of small multiples.

If you have comments or questions, please post them below!

In Data We Trust? Part Two

Part 2 of this series explores the difficulty in determining whether your business intelligence content is using authentic data. To illustrate the point, let’s examine a recent Seattle Times article about the Measles outbreak happening in Washington.

An Example

The article in question, “Are measles a risk at your kid’s school? Explore vaccination-exemption data with our new tool,” presents a story filled with data charts and tables and made some conclusions about the situation. Many internal reports and dashboards do the same, presenting data and conclusions. Unlike internal reports, newspapers list the source and assumptions in small print at the end of the story. Knowing the data comes from an official source adds authenticity.

The following note is supposed to increase authenticity.

“Note: Schools with fewer than 10 students were excluded. Schools that hadn’t reported their vaccination data to the Department of Health were also excluded.

Source: Washington Department of Health (2017-18)”

But does it really? Documenting any exclusions and note sources is a good practice. However, it’s not very prominent and if you search for this data, you’ll likely find several links. There’s no link or contact information.

Data authenticity is crucial to making successful decisions. In order to do so, key data questions should be answered.

What data was used?

Many content creators don’t bother to document the source of their information. Many would not have the same level of confidence about the new financial dashboard if the viewer knew the data came from a manually manipulated spreadsheet, instead of directly from the finance system. How would the reader know anyway? In many cases, they wouldn’t. The Seattle Times provided a hint, but more is needed.

When you buy items like wine, you know what you are buying because the label spells it out. A wine bottle is required to have a label with standard data elements to ensure we know what we are buying. For example, a US wine label must have the type of grape used to make the wine.  Even red blends must list the varietal and percentage so that the customer is clear on what is in the bottle. Having the equivalent type of labeling would improve transparency about data authenticity.

Who owns the data we are consuming?

This is very important, especially if we spot incorrect or missing data. Who do we contact? The Seattle Times lists the Washington Department of Health as the data owner. This is a good starting point but doesn’t completely fill the need. For internal reports, all data sources should include an owning team name and a contact email. The data vintage example below also includes the site urls and a contact email.

Data Vintage Example

How old is the data?

It’s one thing to know when’s the last time the data was pulled from the source but that’s not the need. Data age can strongly influence whether it can be used to make a decision. In our Marquee™ products, we include a data freshness indicator that shows proportionally how much of the data has been updated recently. Recently becomes a business rule of what constitutes fresh data. With some companies, the entity must have been updated with in the last seven days to be considered fresh.

Data Freshness indicator for time dependent data.

How to address?

We took the liberty of creating a Power BI model that analyzed the same immunization data used in the Seattle Times story. We’ll use this model to illustrate the simple technique. The following steps were performed to enable a simple “data vintage” page.

Procedure

  • Create a Data Vintage page (you may need more than one, depending on how many datasets and sources you have)
  • Add a back button to the page. We put ours in the upper left corner
  • Add the following information to the page using a consistent format that you’ve decided upon
    • Name of dataset
    • From where is the data sourced and how often
    • Which team owns the data
    • How to contact the data owner, if available
  • Create a Data Vintage bookmark for the data vintage page so that it can be navigated to via a button.
  • Go back to the report page that you created from this data
  • Add an Information button to the upper right corner of the page.
  • Select the button and navigate to the Visualization blade
  • Turn on Action
  • Set Type to Bookmark
  • Set the Bookmark to the one you created in Step 4.
  • Ctrl + Click the Information button to test
  • Ctrl + Click the Back button to test

That’s it. Anytime a user or fellow Power BI Author has a question about the underlying model data, it can be accessed very easily. You’ll also improve impressions of data authenticity by implementing this label in a consistent manner across all content.

A Working Example

We’ve created a different analysis of the Washington State Immunization exemption data, where we also added a data vintage page. You can try it out below. Click the i Information button in the upper right of the screen to display the data vintage.

In Part 3, we’ll examine the problem of data integrity and how can you be sure your data has implemented the proper business rules for your organization.

Have a question or comment? Feel free to post a comment below.

In Data We Trust? A multi-part series.

I finished the demo of Microsoft Power BI dashboards and reports that we had built for a client. I looked at the room and asked, “What do you think?” This proof of concept hopefully created excitement, by showing what was possible with the client’s data. As we went around the table, people were generally thrilled. The last person’s feedback, however, caught me off guard. “It looks great, but how do I know I can trust the data?”

“It looks great, but how do I know I can trust the data?”

That question rattled around my brain for days. In the rush toward a data-centric future, clients weren’t asking if their data was trustworthy. I researched the problem further, finding some whitepapers and such on the topic, but no clear recommendations on how to address this issue.

Three Areas of Data Trust Issues

Data Authenticity

Is the data you are using authentic, in that is it from a trusted official data source? How do you know? Imagine your executives making decisions about your project portfolio based on a manually maintained spreadsheet instead of from data retrieved directly from their Project Management software.

A trusted data source is one aspect to consider. You also need to know if that data source actively managed? It’s one thing to have data from an official source but if it’s a one time extract versus an ongoing process, the value declines quickly.

Lastly, is this data coming from the official system of record? Imagine getting project cost values from a non-accounting system? Is this system the official system of record for cost data? Many reporting solutions obscure the source of their data, making it impossible for end users to determine if the source is the correct and official authoritative

Data Integrity

Data integrity is knowing that the agreed upon business rules within your organization are consistently applied to your data. Integrity also looks at how close are you pulling data from the official data source. Is the data being taken as is from the official data source or is it being derived? If it is derived, does it officially defined internal business rules to achieve the outcome?

For example, you are using the Project Health from your project management system. Is the value following the standard Project Management Office definition for Project Health or is it using some specialized logic that maybe was used for a one-time analysis? How do you know? Deriving data is not bad if the process adheres to the established internal rules but you have to have a way to gauge this.

Data Timeliness

When you go to the grocery store to buy fresh fruit or meat, the ability to assess food freshness is vital to your buying decision. Old fruit isn’t very appealing and can have detrimental health effects. The same could be said about old data.

In data, we should be going through a similar assessment of freshness. Is this data representative of recent activity? This is not when was the data last refreshed into the report, but rather when was the data last modified. Data that has been modified this morning is likely to be more representative than data that was modified three weeks ago. How do you gauge the freshness of your data?

Next Up

In this series, we’ll explore each of these areas, first examining the challenges end user face determining if the data they are using is trustworthy. We’ll then explore a future where these issues are addressed. Lastly, a presentation of techniques to overcome each of these challenges will follow, enabling you to address these trust issues in your own organization.

The one surprising thing about Visio Integration in Power BI

I was introduced to the new Visio custom visual for Power BI during the Microsoft Inspire convention. After a few minutes, I was impressed with the power and simplicity of it. It helped solve a problem that we’ve had when building out Power BI reports.

Telling a Complete Digital Story

In my Power BI classes, I talk about the importance of creating complete digital stories. They are complete in that you have three components, which allow the story to be understood in a standalone fashion. The three components are

  • Where are you
  • Where do you need to be
  • What is the path or connection between the two states

Think of Visio integration as the easiest way to show your data road map. The Visio diagram can add needed context to the overall picture. Adding proper context with a great diagram makes it much easier to interpret the results, make critical decisions, and take necessary actions.

Quick Power BI Example

Imagine you are a banker and you are trying to assess the current state of your loan process. Throughput is a very important to this process and you want to avoid things getting hung up as this impacts profits. Clients also get upset when they miss closing dates as they can lose real estate deals.

Today, Showing Data without Context

Today you have a Power BI report with various visuals that provide health metrics. You can easily see things like which step has the highest average age of items. You can even see with the bubble chart the overall distribution of steps by Average Age and Item Count.

However, the story isn’t very compelling and it doesn’t answer a key question, what else will be impacted if I don’t fix process step X? Do you clearly know where to focus your attention?

Tomorrow, Your Data In Context

Compare to this report where we’ve added a Visio diagram of the process. The diagram serves as a heat map. Areas that have high aging average values will be in Red. Those in danger are in Yellow and everything else is green. I can still answer the questions I had before. However, now I can see in a glance where I have too many “old” loans in process and what will be impacted downstream.

As I click on any visual on the report, the Visio diagram will zoom to the related step. If I click on the red process step in the Visio diagram, all other visuals on the page are filtered. These behaviors encourage further exploration of the data.

Surprisingly Easy to Implement

The one thing that surprised me about this visual is how easy it is to incorporate Visio diagrams you already have into your Power BI reports. The mechanics are such to make it very easy to map data to the shapes.

Scenario

I want to replace the Visio Diagram above with an existing one that I have. It shows the four major phases of the process. I want to use this diagram on an Executive version of the report, where I don’t need great operational detail.

Prepare Your Diagram

Step Action Diagram
Take your existing diagram and do this:
Design, Size, Fit to Drawing.
This helps reduce the white space around the drawing
The canvas will appear as shown.
Save your diagram using File, Save
If the diagram is not already in an Office 365 SharePoint folder, upload the diagram to a location that the consumers of the report would have access.
    
Click on the diagram to view it in the browser
Copy the URL as you’ll need this later in Power BI to insert the diagram.

Replace the Existing Visio Visual with a New Instance

Step Action Diagram
Open the model in Power BI Desktop
Select the Visio custom visual that shows the existing diagram
Go to the Visualization area and select another visual type. This resets the Visio custom visual
Click the Visio icon in the Visualization area to change it back
Paste in the URL of your diagram that you saved earlier.
Click Connect and login

Map Your Data to the Diagram

There are two tasks that are generally required when adding an existing diagram to a Power BI report.

  1. Replace the column value in the ID field.
  2. Map each shape to a data value in the ID column.

The procedure below will take you through the steps to do both actions.

Update the Column Values in the ID Field

Step Action Diagram
In Power BI Desktop, go to the Fields tab for the Visio visual. Drag the new ID column value over the existing column value.
Now Phase is in the ID Field.

Map Shapes to Data Values

Step

Action

Diagram

Click the < on the Field Mapping bar in the Visio Custom Visual
You will see the ID: field highlighted in yellow
Click the dropdown next to the ID field name. You’ll see the list of data values from the ID column shown.
To map a shape to a data value, select the shape, then select the data value to map to it.
Repeat for each shape and data value.
When you are done, collapse the ID field
Review the Values Settings below.
If you want to show the actual value, change the Display As to Text
OR
If you want to show the value in the form of a heat map, change the Display As to Colors. Set the colors and range accordingly.
Save and Publish Your Power BI model.

Live Example

When you see your report online, you can either click any box in the Visio diagram to filter all other visuals or you can click another visual to filter the Visio diagram.

An example of this report can be found below.

Conclusions

As you’ve seen, the mapping feature makes it quite easy to incorporate any existing Visio diagram into a Power BI dashboard. You can now add things like Org charts, process maps or other visual data for filtering in your reports.

More Information

If you want to know more, check out these links.

Want to Learn More? Register for one of our virtual training classes today!

Creating Beautiful Power BI Slicers

This post addresses one of several common challenges for new Power BI users face. We’ve compiled a list of challenges, based on Our Real World Power BI training series.

Making your Power BI slicers visually distinctive.

Many new users can create slicers in Power BI to enable the end user to dynamically explore their data. However, many don’t know about the styling options that can make your slicers visually distinct and finger friendly for touch devices.

The video below takes you through the steps to beautify your slicers.