Archive for Data Visualization

Coolest New Power BI Features Revealed!

Recently the Microsoft Business Applications Summit 2019 highlighted new Power BI features and these are the coolest features to note IMO:

1. New Power BI App Workspace Experience in Preview Power BI App Workspaces were introduced to enable collaboration amongst the data/business analysts within an organization. The new experience introduces numerous improvements to better enable a data-driven culture including:

•   Managing access using security groups, distribution lists, and Office 365 Groups
•   Not automatically creating an Office 365 group
•   API’s for Admins, as well as new tools for Power BI Admins to effectively manage workspaces

2. Printing Reports via Export to PDF
You can now easily print or email copies of reports by exporting all visible pages to PDF.

3. Bookmark Groups
Now you have a way to organize bookmarks into groups for easier navigation.

4. Python Integration in Preview
Now data scientists can use Python in addition to R within Power BI Desktop.

5. New Visual Header
More flexibility and formatting options have been added to the header section of each visual.

6. Tooltips for Table and Matrix Vizs
Report page tooltips are now available for the table and matrix visuals

7. Many to Many Relationships in Preview
You will now be able to join tables using a cardinality of “Many to Many” – prior to this feature, at least one of the columns involved in the relationship had to contain unique values.

And now I’ve saved the best for last!

8. Composite Models in Preview
With this feature, you’ll now be able to seamlessly combine data from one or more DirectQuery sources, and/or combine data from a mix of DirectQuery sources and imported data. For example, you can build a model that combines sales data from an enterprise data warehouse using DirectQuery, with data on sales targets that is in a departmental SQL Server database using DirectQuery, along with some data imported from a spreadsheet.

As you can see there are many new features to digest but it would be well worth your while to follow the links provided.

On a closing note, I’d like to give you a teaser for two new features coming up soon that will have a huge impact on self-service data prep and querying for big data:

  • Dataflows
  • Aggregates

Stay tuned!
Mario Di Giovanni, BASc, MBA, CBIP
Director, Business Analytics

More about Mario

 

Data Informs Decisions: ASAE and Association Analytics Aim to Predict Engagement

ASAE’s goal was to bring all member engagement data together for cross analysis to build a rich picture of overall engagement and participation. ASAE uses multiple systems—AMS, email marketing, and online community, among others—to collect member data, but it couldn’t compare or analyze valuable member activity across systems. There was no clear way to track retention, understand satisfaction, or analyze member engagement. ASAE partnered with Association Analytics to integrate all data into a consolidated architecture for analysis. Connecting the Higher Logic community platform, branded as “Collaborate,” is the latest addition to this long-term project.

Both organizations got to work consolidating ASAE’s data from all the different systems. There was preexisting data available using visualizations with Tableau and Power BI. Association Analytics developed an integration with Higher Logic’s Activity Sync to bring community activity data into its data platform, Acumen, in near real time, where it’s linked to member data from ASAE’s multiple systems. Additional data includes community membership and discussion content for text analysis. This will enable future plans to automatically categorize discussions and derive other valuable information.

“We built an analytics platform and ASAE helped us build a baseline,” said Julie Sciullo, President and CEO of Association Analytics. “Since Higher Logic is a standard integration, we were able to quickly and easily integrate community data into the rest of this new ecosystem, bringing to light different trends, ways to look at preexisting data, and a plan to prompt further actions. And that’s a big part of it – collecting data is cool, but what are you going to do about it?”

In other words: ASAE wants to understand the “aboutness” in its member conversations. At a basic level, Acumen helps the organization visualize search terms and subject lines. On a broader scale, it helps to see what members, volunteer groups, and people in the community talk about.

“Since starting this partnership with Association Analytics, we’ve aimed to make data analytics top of mind for associations,” said Reggie Henry, Chief Information Officer of ASAE. “We want to create some thought leadership around full data analysis. Don’t solve just one data problem – let’s get as much data as we can into this warehouse, so we can continuously solve problems. And let’s bring the community along for that ride.”

Community Data Results
ASAE knew Collaborate was a strong member benefit, but insights gleaned from Association Analytics’ Acumen tool proved the community extends beyond just a place for members to interact. Click HERE to see the full case study and three actionable engagement trends that came to light.

 

Case Study: Analyzing Member Engagement – How ASAE Captured the Unicorn

The American Society of Association Executives (ASAE) uses multiple systems—AMS, email marketing, and online community, among others—to collect member data, but it couldn’t compare or analyze valuable member activity across systems. There was no clear way to track retention, understand satisfaction, or analyze member engagement.

ASAE partnered with Association Analytics to integrate all data into a consolidated architecture for analysis. Connecting the Higher Logic community platform, branded as “Collaborate,” was the latest addition to this long-term project. ASAE’s goal was to bring all member engagement data together for cross analysis to build a rich picture of overall engagement and participation.

Both organizations got to work consolidating ASAE’s data from all the different systems. There was preexisting data available using visualizations with Tableau and Power BI. Association Analytics developed an integration with Higher Logic’s Activity Sync to bring community activity data into its data platform, Acumen, in near real time, where it’s linked to member data from ASAE’s multiple systems. Additional data includes community membership and discussion content for text analysis. This will enable future plans to automatically categorize discussions and derive other valuable information.

blog-aug14

In other words: ASAE wants to understand the “aboutness” in its member conversations. At a basic level, Acumen helps the organization visualize search terms and subject lines. On a broader scale, it helps to see what members, volunteer groups, and people in the community talk about.

⇒ Click here to read more including three major insights

4 Ways Data Visualization Drives Change

Data visualization amplifies your data storyThink of the elements that make up a strong and compelling story. They contain a tantalizing plot, well-developed characters, a climax, and satisfying conclusion. You read books or watch movies that keep you captivated from start to finish and leave you wanting more.

Storytelling with data plays a relevant role at organizations these days. What distinguishes storytelling at your organization from your favorite movie or book though is using data to tell your narrative. Without data to aid your story, it is challenging to prove your point, get buy-in for software, and instill confidence because these days trusting your gut isn’t sufficient enough to run an organization. However, you can’t use data alone to tell your story.

You need a visual representation to deliver your message so that it creates a lasting impression on your colleagues. When you combine data with a visual, you are giving your staff the power to take action and better align themselves with your organization’s overarching goals.

Here are 4 ways data visualization can ultimately drive change at your organization

 

 

1. Data becomes the natural and normal way to help make informed, data-guided decisions.

The human brain processes images 3X faster than text, and your brain is excellent at detecting patterns and interpreting the meaning. The visual system is extremely powerful because you process the visual field at once, and you spot trends and outliers very quickly. The visual cortex is part of the amygdala – the original reptilian part of our brain responsible for your survival. The cerebral cortex, or thinking part of your brain came much later in our evolution. Seeing images occurs quicker than processing the story embedded within the data.

It’s been said that a picture is worth a thousand numbers. So, what is it exactly about visual images that make data so much easier to digest? It’s your ability to quickly see the most important information and to detect relationships. Pictures and colors allow you to take mental shortcuts. Data visualization allows you to see important correlations quickly without getting lost in the details. Armed with the data story, decisions pertaining to your organization are easier to make since you’re using data to guide your strategy. Data visualization opens up a new and more powerful way to look at your organization and identify what’s working and what isn’t.

Remember though, not all visualizations are created equal. It varies with every organization because each one is unique in their own way. You should choose the best representation for data (such as a bar, line, or bubble chart), and consider the relationship of the elements. Testing different visualizations might need to happen and that’s okay to do. What works for one department or organization might be less suitable for you.

2. You have a better chance of getting buy-in for other data analytics tools.

Data visualization gives your data a narrative that you can readily share with your executive team. It’s important to know your audience before you create your visual though. Consider a use case that’s relevant and interesting to your audience so it demonstrates the value of your data and gives them actionable insights that ultimately help them do their jobs better.

There will be instances when you present your data visualization to people who are either skeptical of data or investing in analytics tools. Chances are, their doubts stem from the lack of a single source of truth at your organization. While processes have been established so this is less of a pain point among your team, sometimes the executive team might be less aware of that because they aren’t “in the weeds” like you are. It’s up to you to prove to them that data has always been an asset at your organization, and data visualization is a great way to reaffirm that mindset.

Using data visualization carries more authority and credibility rather than simply going with your gut. You have to show how the data impacts them and in turn the organization as a whole. As humans, we tend to ask the question “How does this impact me?” People need to understand how it benefits them and how it will help them succeed in their roles.

When you showcase a data visualization that reveals a compelling story, you’re more inclined to see staff members embrace the idea of investing in other data analytics tools. They’ll be able to recognize the positive impact that data storytelling can have on their ROI and will be open to incorporating data analytics tools into their activities.

3. You’re more empowered in your role.

Data visualization should be presented in a non-technical way that anyone at your organization can decipher. When you’re not accustomed to using data to support your decisions nor have you seen it in a visually appealing way, it creates intimidation and casts doubt around its effectiveness. Making your data narrative easy to digest cuts through those doubts and makes it accessible to anyone. When something is accessible and easy to understand, you’re more likely to see people embrace it and incorporate it into their roles. Your colleagues are more empowered because they can thrive in their respective positions and unite behind the goals and mission of your organization.

4. It inspires a data-guided culture overtime.

Establishing a specific type of work culture doesn’t happen overnight. It takes patience, and it’s typically a multi-step approach. One step to take is to encourage departments to create their own data visualizations to tell their data stories. It’s also beneficial to provide organizational-wide access to data reports so that everyone has the opportunity to view and analyze the data themselves. Then, data becomes more shareable across teams and creates transparency around what your colleagues are doing. Knowledge-sharing becomes a normal part of your organization’s culture. Data literacy becomes more wide-spread because you’re referring to it on a routine basis to help inform your strategy and decisions. Your brain becomes accustomed to synthesizing the information, spotting trends, and identifying patterns because you’re seeing it in a visual representation. Organizations that cultivate a data-guided culture reap the benefits of their efforts.

If done well and with company-wide buy-in, data visualization helps your organization flourish because you’re using data to guide your decisions and empowering your team to have visibility into how their activities are performing.

Ready to Plan?

Contact us at info@AssociationAnalytics.com or (800) 920-9739 to discuss your association’s analytics strategy and roadmap.

Custom Visuals in Power BI

Out of the box, Power BI gives you access to many common data visualizations. Sometimes the basic charts don’t get you to where you need to go. Luckily, Microsoft and the Power BI community created custom visualizations that will be available to you. I am going to give you a quick primer on how to access those and then let you know what my favorite visualizations are.
Downloading Custom Visuals
There are two different ways of accessing custom visuals straight from Power BI.

    • On the visualizations pane, you can click on the ellipsis icon and you will see the option to Import from store. Once you click on this a pop-up will appear and allow you to search and add custom visuals. Please note, this method works in both the Desktop application and online with Power BI.

 

 

    • On the Home tab, there will be an option From Store under Custom Visuals. This method is only applicable when using Power BI Desktop.

     


  • Favorite Custom Visuals
    There are many options out there for custom visuals and it is constantly being updated with more. Below are my three favorite custom visuals.
    Bullet Chart by OKViz
    A bullet chart is a great visual introduced by Stephen Few which allows you to show performance against a target. One use of a bullet chart is revenue and expense tracking against budget. This chart allows you to track your target, your actuals, and then qualitative reference bands. The bands can be used to track the number’s state, such as Bad, Acceptable, Good.

    Brick Chart by MAQ Software
    This visualization is a different way to look at percentages. It has 100 squares which are colored by a categorical field depending on the percent of total. For our example below, we are looking at overall Profit by Region and we can see that the Central Region has the highest profit.

    Hierarchy Slicer
    This really isn’t a visual, but a slicer, however it allows you to add hierarchal fields to a slicer and it will retain the hierarchy. For instance, if you used a date field in this slicer, you can have a hierarchy of year, quarter, and month.

    There are many custom visuals available and it can be overwhelming at times. Hopefully this will help you as you start to explore custom visuals.
  • By the way, be sure to checkout last week’s post about the October 2017 release of PowerBI in case you missed that!

Moving the Needle on Member Data at NCACPA

Nikki Vann, CPA, is the Director of Finance & Administration at the North Carolina Association of CPAs (NCACPA).  She, along with Jennifer Rowell, Director of Member Engagement (also from the NCACPA) and I were honored to deliver a presentation entitled, “Moving the Needle on Member Data” at the American Institute of CPAs (AICPA) Annual Conference in Key Biscayne, Florida during July 2017.

“Our journey with business intelligence started as our Board discussed our strategic priorities,” Nikki explained. “They realized we couldn’t move forward with any of them without data. To better serve our members and their needs, we needed to understand the story of their actions through data.  Most recently, our team created 12 operational goals, and decided how to measure them.  Now we go through them at the staff meeting and we tie our efforts to those goals. We have made so many improvements to the way we use data as an asset.  For example, we used to go through the entire budget process with our executive committee and talk about each line item.  We realized we were dealing with highly intelligent leaders, so instead we decided to talk about the reason why we built the budget the way that we built it. Let’s talk about the changes we are making to how we do things because the data is telling us to.  Now at every board meeting, this is what they want. “

I see this trend continuing in high performing associations – the time that used to be spent pulling all the data together, can now instead be spend on deciding what to do about it and taking action.  Plus we all know the saying, what gets measured, gets done.   The idea is to use data to set a goal, then make a plan, and use data to measure results.  As Nikki says, “There is no success if you cannot measure it, and if it’s not quantifiable.”
One of the other significant accomplishments demonstrated by NCACPA during the presentation is the ability to visualize and analyze member engagement.  Jennifer added, “We are proud that during our work with Association Analytics we have connected our Abila AMS data with Higher Logic to get a wider view of what is important to our members, and what they are talking about.  We now have more confidence when we make decisions, and we’re also making better decisions because of it!”
 
 

Refreshing Tableau Public Data


Tableau Public is a free tool to share your visualizations with the world.  One question we often here is how do you refresh data for a Tableau Public visualization.  Tableau Public will do an automatic, nightly refresh data of Google Sheets data, no extra work on your side.   However, there is a bit of additional work required in order to automatically refresh your Google Sheets data.  In our example we are pulling data from a AWS Redshift database using Python version 3.6
Google Sheets Set-Up
This set-up will create the credentials necessary to refresh your Google Sheets data.

  • Create a project in Google Developers Console https://console.developers.google.com/flows/enableapi?apiid=drive. Create an account if you have not already done so.
  • Click on Library and search for the Google Drive API, and verify the API is enabled.
  • Next, click on Credentials. Then click on create credentials -> OAuth Client ID, choose “Other” as the application type and enter a name for your credentials.  You will need these credentials to connect to Google Sheets.
  • Now, download the client ID you just created. Move this file to your working directory and rename it client_secret.json.  For example, C:\xxxx\xxxx\.credentials
  • Go into your Google Drive account and create a blank google sheet and copy the File ID from the url. https://docs.google.com/spreadsheets/d/1kadJ8y1_VZnKGqPx6ohA3F-OdHtablmyzr2ZF6nuH18/edit#gid=0.  You will need this ID for your Python script.

Python Scripts

  • For this script you will need to install both psycopg2 and google-api-python-client libraries.
  • Now create your Python script which begins with getting your credentials. You can find this at the Google Sheets developers guide on Step 3.
  • Once you have your get_credentials function in place, you will need to connect to your Redshift environment and insert your SQL query. You can connect to Redshift using the psycopg2 package.
  • We then use csv_writer to write the database query to a CSV file.
  • Now overwrite your Google Sheets file with the new data. This requires the Google Drive API.
  • When you run your script, a browser window will open and ask you to enable authentication. This will ensure your code has rights to write to Google Sheets.  This will only happen the first time this is run.
  • Once you have this set-up, you can schedule the Google Sheets refresh to happen prior to the Tableau Public refresh, which occurs around 6 am ET.

I hope this helps you get started with utilizing Tableau Public’s built in data refresh so you can share your visualizations with the world.
 

How To Choose the Right Visualization

Creating a report or dashboard in Power BI is quite easy.  A few mouse gestures and a couple of clicks and voila, we have a report!
Creating a report that tells a story at a glance, however, is hard.  The ultimate test of a good report/dashboard is if someone without any prior knowledge can quickly understand it. Our audience should be able to see what the page is about and what each visual is about — at a glance!
We also want to consider whether we have more “sizzle” than “steak”.  Is our visualization attractive but without substance?  Don’t get me wrong, “pretty” is important.  We just need to find a balance between “pretty” and “useful”.
Power BI has about 30 built-in visualization types.  Add to that all the custom visuals that we can get at the Office Store and we literally have hundreds of visuals to choose from.  What’s a data analyst to do?
So here’s a one-page cheat-sheet that I use all the time.  It gives us a helping hand when trying to figure out which visual is best for the type of story we are trying to tell.  Here are a few examples:

When we want to display a measure and compare it by magnitude, a Clustered Bar or Column chart will do the trick.  Alternatively, a custom Bullet chart is very effective.  But we probably shouldn’t use a Gauge or Bubble chart.

Clustered Column Chart

Line Chart

Trends over time are best visualized using a Line chart or custom Sparkline.  However, an Area chart is probably not a good choice.
When we want to display a single value, use a Card or KPI visual.

Card

 
This cheat-sheet is a great starting point for telling your data story.  Browsing the Power BI Data Stories Gallery is also a great way to get some inspiration.  I’ll also experiment by switching between visualizations until I find one that best suits the story I’m trying to tell.  The good thing about using Power BI is that it can support this kind of “speed of thought” analysis.
Download this guide and keep it handy when you are trying to put some “steak” into your reporting “sizzle”.

We are overwhelmed by information, not because there is too much, but because we don’t know how to tame it. — Stephen Few

Power BI vs Tableau

I am often asked, “Which tool is better: Power BI or Tableau?”
Microsoft has done a great job with Power BI over the last two years. The features in Power BI have evolved so much that we’re now recommending it first to our clients. Microsoft identified the most important and useful features for a data visualization tool set and embedded them within Office 365. Power BI now has the distinct advantage of being familiar and easily accessible to non-technical users, which improves its adoption rate.  Microsoft's Power BI vs Tableau
In addition, Gartner’s 2017 Magic Quadrant ranks Power BI higher on “Completeness of Vision” than Tableau. However, to be fair, both Power BI and Tableau are matched for “Ability to Execute.” Tableau was first to market and has deeper capabilities, however, in our experience, the advanced functionality offered by Tableau happens to be the functionality that associations use the least.
If you are a power user, you may still want to consider Tableau. But, if you’re responsible for rolling out a new data analytics initiative within your association, Power BI is an affordable option with the added benefit of being “front and center” when staff log in to Office 365.
This list of pros and cons will continue to evolve, so check with us if you want to know what’s new with either solution.

 Power BI

 Tableau

 
Pros

  • Affordable
  • Integrated with O365
  • New features weekly
  • Familiar to Excel users
  • Self-serve data prep
  • Office store apps and custom visuals available
  • Extensive data connectivity
 
Pros

  • Excellent user interface
  • Fast and easy to create visuals
  • Good customer support
  • Story-telling feature
 
Cons

  • Data Analysis Expressions (DAX) knowledge required for advanced calculations
 
Cons

  • Cost
  • Self-serve data prep

Workbook Performance Recording – Tableau Server

The first and most important rule about making workbooks more efficient is to understand that if it loads slowly in Desktop on your computer, then it will be slow on the server too once it is published. Desktop and server each have their own way to enable, record, and analyze performance. The focus here is on performance recording for workbooks published to Tableau Server.

Enabling Tracking

  1. Administrators must enable the feature. This is located under settings, for each site.
  2. Check the box and save for Workbook Performance Metrics.
  3. It is a good idea to leave this disabled when you are not using it since recording metrics can also impact performance.

tableau_performance_enable

Create the Recording

      1. Navigate to a view on the server.
      2. Remove the iid=xx from the URL.
      3. Enter in its place record_performance=yes. Your full URL should now look something like this: https://data.associationanalytics.com/#/site/AA/views/AAEmailActivity/MessageStatisticsSummary?:record_performance=yes
      4. After the page reloads, you’ll notice the ID is added automatically back to the URL and that a performance button appears within the View’s toolbar. Don’t click on the performance button yet.
      5. Do some filtering and some clicking within the workbook such as applying filters, selecting marks/rows, and clicks that cause actions to other elements of the visualization.
      6. Then click the performance button.
      7. Now you’re ready to click on the Performance button which will launch a new window with the collected statistics (see next image).
      8. The analysis and follow up actions are a whole other topic, but to quickly mention that you want to make sure your timeline slider is all the way to the left and then you’ll be able to see the different events and which takes the longest: executing query, sorting data, building view, connecting to the data source, geocoding, or computing layout.
      9. The provided workbook is not directly sharable, but the capability to download the resulting workbook is provided. Further, it is possible to use the download to publish it to another location.
      10. Don’t forget to disable the performance recording in the admin settings when you are finished.

tableau_performance_viz
Now that you know you can record and view the results of a workbook published Tableau Server, you can start to analyze the results so they load faster. In separate posts we’ll cover performance recording in Desktop and how to interpret the provided visualization.