Warning: A non-numeric value encountered in /homepages/0/d741589923/htdocs/clickandbuilds/AssociationAnalytics/wp-content/themes/Builder/lib/builder-core/lib/layout-engine/modules/class-layout-module.php on line 499

Warning: A non-numeric value encountered in /homepages/0/d741589923/htdocs/clickandbuilds/AssociationAnalytics/wp-content/themes/Builder/lib/builder-core/lib/layout-engine/modules/class-layout-module.php on line 499

Archive for business intelligence

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


Planes, Trains, Automobiles… and Meetings

Each Labor Day weekend, analysts discuss the impact of gasoline prices and other factors on travel driven by an extra day off and the symbolic end of summer. Some estimates indicate that a greater number of travelers by car saved a collective $1.4 billion this year, much of which was spent elsewhere during trips.map-clipart-travel-map
Deciding if and when to travel greater distances is a more complicated process since it often requires relatively expensive air travel with a commitment in advance. The price of airline tickets is significantly determined by the timing of purchase relative to travel. Analytics indicate that the optimal time to purchase tickets is generally between three and eight weeks prior to travel. The specific timing changes based on the time of year and also varies significantly by departure and arrival airports. It unfortunately does not require advanced analytics to observe that prices tend to rapidly increase beginning two weeks prior to travel.
Associations effectively create similar opportunities for customers through meetings and events. Associations spend considerable time and effort on planning and marketing activities based on a range of customer data including prior event participation, topical interests, individual engagement, meeting sessions, organization characteristics, continuing education requirements, and demographics. Registration patterns are closely monitored against prior year’s activity and current goals. Sometimes these patterns are not consistent with historical data. In some cases a trend of early registrants fails to continue and in other cases a late surge of registrants follows a period of marketing efforts.
Associations can supplement their AMS data with external sources to help explain such patterns. The U.S. Census Bureau makes very useful geographic data available including zip codes with latitude/longitude mapped to several other data points. The Census Bureau also offers an application programming interface to obtain detailed geographic data for specific addresses. This data can be used to calculate the distance between two points (it’s kind of a long story that involves the trigonometry I never thought I would use such as sine, cosine, and inverse tangent). Tableau data visualization software provides out-of-the-box geographic visualization capabilities based on data including zip code, area code, city, metropolitan/micropolitan statistical area, and even congressional district.
Various surveys and other analytics estimate that a common comfortable distance for car travel is around 200 miles.  Here is a map created using Tableau showing the population density by zip code within a 200 mile radius of Phoenix:
Here is a similar map based on the same color scale for Chicago:
A far greater number of individuals are likely to consider driving to a meeting in Chicago than a meeting in Phoenix. It is important to note that this straight line distance is clearly a rough estimate and encountering a large body of water such as Lake Michigan would challenge even the most dedicated meeting attendee. Fortunately you can leverage more advanced data sources such as the Google Maps API to estimate driving distances.
This data provides associations with great analytics opportunities to drive meeting participation by aligning with customer travel decisions. For example, marketing campaigns can consider travel decision timing and target specific airport markets. Registration pricing deadlines for individual events can incorporate location analytics. Your association can also create opportunities for strong customer engagement through efforts to help groups of potential meeting attendees organize buses together or even carpool.
Deriving travel distance analytics from customer location data is an example of gaining value from data. This demonstrates why it is important to consider analytics when designing business processes throughout the organization and not just as part of analysis after the fact. Increased meeting attendance often contributes to other benefits including member retention, publication revenue, engagement, and membership recruitment.
Understanding the impact of customer travel scenarios is a great way to leverage association analytics to create the future and grow your number of happy conference attendees.

Association Analytics: Begin with the End in Mind

Often association leaders ask me, “Where is the best place for us to begin our data analytics initiative?”  I like this question and am reminded of Steven Covey’s expression that before we undertake a new initiative, we should “begin with the end in mind”.  When it comes to data analytics for associations, this means starting with your strategic plan.
One association’s strategic plan, for example, specified that they would grow meeting attendance by 10% in 3 years.  This was their largest source of revenue.
In the past the association made decisions by looking at historical trends and was unable to confidently estimate expected attendance, revenue, venue amenities needed, onsite staffing levels and more.  In fact, the more events they held, the greater the risk of an incorrect estimate.
Yet because of the strategic initiative to increase attendance, the first thing they tried was to increase the number of events and market them by email campaigns and direct mail.
The visualization below is a simple bar chart showing the number events by year. The higher the bar, the more events. The color shading of the bars indicate the number of registrants. The darker the green, the more registrants. We can quickly see 2010 had the most events, but not the most registrants.  In other words, although the association increased the number of events, this did not increase the number of attendees. In fact, holding more events actually caused a decrease in meeting profitability as a whole, since there were more expenses associated with fewer registrants.
By reducing the number of events by combining smaller, similar events to a larger meetings, the organization was able to dramatically exceed the target set by the board.  In other words, by holding fewer events, they had more participants.

What is the Cost of Not using Data?

When making business decisions it’s important to weigh the costs of:

  1. Bad decisions
  2. Decision delay
  3. Lost opportunities
  4. Damage to image/reputation

Often these costs are much greater than the costs associated with analyzing data. Taking a “decision tree” approach can be a logical way to evaluate where to start:

  • Are there areas in your strategic plan where data can answer questions, provide insight and direction?
  • Which areas do our customers care about? Will it make a difference to them – add more
    value, improve their experience – advance our mission?
  • Do we have the data – or can we get it?
  • Will we be able to take action on the results of the analysis?


For Nonprofits and Associations, the Time is Now for BI

Business Intelligence can improve nonprofit and association decision making.  Most of us are familiar with the scenario where we extract information from a variety of different systems that each serve their own purpose, but are not integrated.  In recent years, there has been a push to integrate systems or to try to put everything into one multi-purpose system, like an AMS (Association Management System) or CRM (Customer Relationship Management).  There are certainly benefits to this especially for the front-end users and customer service representatives.  However, there are challenges as well.  For example, it often will slow down a transactional system to store every email that was ever sent to a member.  It may also be cost prohibitive to have a real-time integration between all of your systems.

A Better Way

time_to_goTo evaluate if this situation is optimal, ask yourself if right now you could accurately measure engagement across all channels and programs.  The goal is to not only enable you to increase that involvement, but also to clearly demonstrate the positive impact your association or nonprofit has on your sphere of influence.  I’ve often found that this broad level of analysis can be elusive.  When your board asks for this information, you may have to spend days pulling information from all of your systems and trying to come up with some form of analysis.  But don’t forget to save those complex spreadsheets and don’t forget how they were created so that they can once again be recreated next month!  This kind of activity is what makes me cringe and think, there has to be a better way!  The exciting news is that there is a better way and it’s not as hard as you might think.

Private Sector Paved the Way

The private and commercial sector has already set the stage.  It’s like having a big brother or sister that pushes the boundaries, makes mistakes, and figures out the best way to excel.  Now associations and nonprofits can learn from their mistakes, modify their path to meet our unique needs, and off we go.  The commercial sector has proven the value of Business Intelligence (BI).  Which is, simply stated, transforming data into meaningful information that can be used to make good decisions.  However, remember that the technology is just the enabler – the “tools” to get the job done – it’s people who are the differentiators.
BI can be used to bring together disparate data sets so that historical analysis and predictive modeling can guide decision-making.  It can also be used to supplement your standard customer data with social media content, including customer’s interests, purchasing behavior, and social circles.  What if you could find those customers who spend very little money with your association, yet enjoy being a part of it, and also have a huge circle of influence?  You could reach out to them to help promote your organization.  BI can also identify the key metrics which will help determine overall effectiveness in your space.

BI is Expected

I’m sure you’ve heard about Business Intelligence by now – it’s been a buzz word for a long time and in fact is almost overused.  But the time to act is now.  If your competitors aren’t using it, they will be soon and have you stopped to think that the private sector is a competitor these days?  Especially with respect to content, education and networking.  Chances are your customers are already using BI (or analytics), even if they don’t realize it.  Each time they purchase something at a large online retailer and see things like “other customers have bought this” or “you might also like”, they are reaping the benefits of BI.  It’s something they’ve come to expect.  They also want a custom, user-centric experience from you.  In order to provide this to them, you must first understand them.  You likely already have that information, you just need to harness it.

Get Started

To get started, take a look at my previous blog, Preflight Checklist for Association Business Intelligence. This blog outlines a few preliminary steps you can take now on your path to using BI and making data-driven decisions.

Top Trends in Business Intelligence for Associations for 2014

Top Trends in Business Intelligence for Associations for 2014
Tableau software recently compiled their set of Top Ten Business Intelligence Trends for 2014. Not all of these trends will be impacting the association space. Some of these trends are more relevant in the corporate space, but could become more relevant as more time passes and technologies are less expensive and accessible. In this blog we will review the trends compiled by Tableau as well go in depth on the trends that will directly affect you and what you should do about it.
Below is the Top Ten list as published by Tableau.

  1. The end of data scientists. Data science moves from the specialist to the everyman. Familiarity with data analysis becomes part of the skill set of ordinary business users, not experts with “analyst” in their titles.
  2. Cloud business intelligence goes mainstream. Organizations that want to get up & running fast with analytics drive adoption of cloud-based business intelligence. The maturation of cloud services helps IT departments get comfortable with business intelligence in the cloud.
  3. Big data finally goes to the sky. Cloud data warehouses like Amazon Redshift and Google BigQuery transform the process of building out a data warehouse from a months-long process to a matter of days.  This enables rapid prototyping and a level of flexibility that previously was not possible.
  4. Agile business intelligence extends its lead. Self-service analytics becomes the norm at fast-moving companies. Business people begin to expect flexibility and usability from their dashboards.
  5. Predictive analytics. Once the realm of advanced and specialized systems, will move into the mainstream as businesses seek forward-looking rather than backward-looking insight from data.
  6. Embedded BI begins to emerge, in an attempt to put insight directly in the path of business activities.  Analytics start to live inside of transactional systems.  Ultimately, embedded BI will bring data to departments that have typically lagged: for example, on the shop floor and in retail environments.
  7. Storytelling becomes a priority, as people realize that a dashboard deluge without context is not helpful. Stories become a way to communicate ideas and insights using data. They also help people gain meaning from an overwhelming mass of big and disparate data.
  8. For leading-edge organizations, mobile business intelligence becomes the primary experience, not an occasional experience. Business users begin to demand access to information within the natural flow of their day, not back at their desks.
  9. Organizations begin to analyze social data in earnest, gaining insight beyond number of their likes and followers. Social data becomes a proxy for brand awareness and attitude, as well as fertile ground for competitive analysis.  Companies begin to use social data to understand how relevant they are to their customers.
  10. NoSQL is the new Hadoop. Organizations explore how to use unstructured data. NoSQL technologies become more popular as companies seek ways to assimilate this kind of data.  But in 2014, the intelligent use of unstructured data will still be the exception and not the norm.

Of these trends, only some will impact your association in 2014.  Below we have provided you information on the relevant trends to take to your executives to make sure you can stay ahead of the Business Intelligence curve.

  1. The end of data scientists. Most associations don’t have the resources for full time data scientists. Instead associations are putting the power of data-driven decisions in the hands of staff across departments. Staff will be trained on analytical skills that can be applied to their daily routine and need for information.
  2. Cloud business intelligence goes mainstream. Cloud-based solutions help associations to more quickly and often more economically begin using business intelligence. It also puts the data in the hands of everyone and anyone with internet access. Data belongs to the association business staff and the IT staff facilitates the management of this important asset.
  3. Agile business intelligence extends its lead. In the association space, DSK incorporates key aspects of agile processes to business intelligence initiatives because it dramatically increases the probability of a successful outcome. Typically, associations do require an estimate up front for budgeting purposes, but the scope and development are best performed in an agile, iterative fashion. See our blog post from last year about Agile Business Intelligence.
  4. Storytelling becomes a priority. We often believe data is a flat object, but viewed in context it has the power to tell a story about what is really happening.  We believe that through data discovery, association staff can learn to understand the story their data is telling. And once the story is understood the story, you have the power to can change the ending! Through the use of interactive data visualizations, your data can be analyzed in ways not thought of previously. 

Can you see your association following these business intelligence trends in 2014?

7 Business Intelligence Mistakes Associations Should Avoid

failure_successThe private sector and government organizations have been using Business Intelligence (BI) for over 15 years. The good news for those of us in the association space is that we can learn from their mistakes and avoid them when starting BI programs for our associations. Below is a summary of common mistakes and how they can be avoided.

1. Not Solving a Real Business Problem

It’s easy to get caught up in the hype of business intelligence. It’s cool, it’s great for your resume and you may be getting pressure to just get it done. However, too many organizations fail to focus on the real business problems and opportunities they should address. Even if IT is ready and willing to take on a BI project, the initiative will fail deliver a return on your investment if it is not directly connected to addressing the problems and opportunities the association needs to address.
First identify and prioritize the business situations that need attention. We advise our clients to begin small.  Start with an area that will yield measurable benefits (such as Finance or Membership) but initially select an aspect of the area that can be analyzed in a relatively short time-frame.  A skilled analyst can collect requirements, conduct interviews, and facilitate group sessions focused on identifying and describing the challenges and opportunities facing the association and how they tie to the association’s strategic plan. Working as a team the analyst, business users, and technical resources locate and document the available data sources as well as identify the gaps in which essential data is not being collected. Once this information is gathered, requirements are grouped into logical areas.

2. Focusing on the Dashboard

You may have seen impressive examples of flashy dashboards from BI vendors, media outlets, or personal websites. It is easier than ever before to grab data from any source and present it in a visually pleasing matter. However, to make that visualization meaningful and actionable for your association, you will need to determine what will help your specific organization. Even in the association space, what makes sense for one group can be completely meaningless for another. Spend your time and resources to find and prepare data that will provide meaningful information to allow business users to make better decisions.  You can use the flashy visualizations and dashboards as inspiration once you have the underlying data and business questions formalized.

3. Not Including Business Users when Data Modeling

Data modeling is a task performed by the technical team. However, how quickly business users embrace and adopt BI solutions often comes down to how the data is organized. The data needs to be organized in a way that makes sense to the business so that they can interact with it by having “conversations” directly with the data.   The technical complexities of a highly normalized transactional data model should be abstracted so that the business user interacts with familiar objects and attributes with friendly names. Discussions with business users should be conceptual, not technical. In addition to technical documentation, documentation should also be created to capture the concepts and components of the data model in a way that business users can understand.

4. Lack of Communication

Too often business intelligence projects start off strong. Requirements are gathered, discussions with business users are held and then IT goes off into their private corner to design and build a solution. They may be working hard and making progress, but the business is left in the dark. Regular communication with the core team, including in-person meetings, frequent demos and status reports are vital. When IT and the business users communicate regularly about the progress, adjustments can be made which improve the outcome.  BI is a business initiative and technology is the enabler.  It is not a technical project that should be foisted on the business.  Our experience has proven to us that collaboration and communication is the most essential ingredient in a successful BI program.

5. Not Providing Education and Documentation

Although many BI visual interfaces are built to be intuitive and use features users are already familiar with, training is absolutely necessary. At a minimum, the staff need to know what data is available and how it is organized. They also need to know how the data and the analysis applies to their day-to-day work. Staff members also must be provided with technical training on how to use the tool. Just like any software or technology, ongoing training is needed to provide continuity in the association as people move to new positions or are new to the association.
Documentation should also be provided. First, documentation about the published dashboards should be created. This includes topics like the name, location, business description and the business owner. Documentation about the data is also important. This includes the business name for each attribute and measure, business description, and the calculations used to create the measures. Develop documentation as you go. If you leave it until the end of the project, it has a way of falling to the wayside as deadlines near.

6. Not Providing Support after Development

Everyone is clamoring for more self-service. This term means different things to different people, but in the BI world this often means that the business users can go get the information they need without asking technical staff for help. This could involve the ability to drill-down for more details and/or change the filters or even create new visualizations. However, self-service does not mean that once the datamart is built and the BI tool is installed, IT is finished. IT must continue to be involved to ensure that the offerings continue to meet the needs of the business. Often, more data can be brought in and more analysis can be performed after the initial offering is a success. The needs of the association will evolve and the BI solution must also evolve.

7. Not Measuring Results

After the investment of time and energy to create a BI environment, sometimes organizations neglect to track the results. What you want to determine is if the initial business questions have been answered and if the business situation is improving. For example, if your initial business question was “How can I increase my membership retention?”, then evaluate if the BI solution added value by determining the improvement in the retention rate after BI enabled you to identify and connect with “at-risk” members. It is essential to track successes by quantifying and documenting when BI has resulted in an improvement in a key result or key performance indicator (KPI). This should be shared on a regular basis during staff meetings. Success stories justify the original investment and support future phases.


If you’ve made these mistakes before or see them happening now in your association, you are in good company. Some of these mistakes have been made by the best run and smartest companies in the world. The great news for associations and  nonprofits is that because we know what these common mistakes are, we can take deliberate measures to avoid them.
“A smart man makes a mistake, learns from it, and never makes that mistake again. But a wise man finds a smart man and learns from him how to avoid the mistake altogether.” –Roy H. Williams

Data Discovery – a “Bicycle for the Mind”

Steve Jobs said that computers are like a “bicycle for the mind” allowing us to go further faster with less effort.  Within the field of business intelligence, I believe data discovery should be the same, amplifying our intelligence and creativity, allowing us to see patterns and insights which we can use to create the future of our organizations.

Data Visualization is Key

A key feature of data discovery is the ability to present data visually in order to quickly convey the information to our brains.  The goal is to stimulate our minds and then to allow us to interact and have a direct “conversation” with the data.  We “ask” follow-on questions by clicking through and moving elements on the screen, “re-presenting” the data many ways.
We are inspired when we see data visually and notice how it changes based upon the questions we ask.  We can see the power as we interact with the data and the analysis process becomes a natural extension of the activity of thought, enabling us to drill down, drill up, filter, bring in more data sources, or create multiple visual interpretations.  Interactivity supports visual thinking.  We can work with the data visually at the speed of thought, rather than writing queries to the databases.  We can learn and reach insights faster.  When we interact with the data visually, we are participating in the data discovery process and data becomes our partner in gleaning the information that becomes business intelligence.

How Does your Association Make Decisions Today?

An MIT study shows that most organizations still rely on instinct, intuition, politics and tradition.  But according to Harvard Business Review, top performing organizations are 5x more likely to use data to make decisions.  If we know that decisions based on data tend to be better decisions, why don’t we use it all the time?  Research says that only 15-20% of organizations believe they have access to the data they need in order to make good decisions.  Why is this?
IVeniceMonet once heard it explained this way – imagine you are Monet and you are explaining to a friend how to create a painting of Venice at twilight – like this one.  You might say, start out with a church steeple on the left sort of a brownish color and surround it by a golden light, with deep blue in the sky.  Oh yes, and maybe add some little purple/violet touches in the water to the right.  Do you think your friend would create a painting that looks like this one?  Probably not.  And yet that is what is happening daily between business and IT.  Business tries to describe in words the data that they want to see.  And IT tries their best to represent in reports and dashboards what they heard the business say.
The way it typically works is you have a question and ask IT to create a query or report and then when they deliver it, usually you have another question, or want another piece of data or have it grouped a different way.  Traditionally this change request has been considered a BAD thing!  And then you have to wait for IT to revise it.  At least that’s what used to happen to me when I worked for an association.  This process doesn’t really work well for either the IT department or the business users in the association trying to make decisions.

Change the Ending

Association staff want access to their data!  They don’t know their questions until they start to see the data.  Data discovery enables them to have a conversation with the data directly.  Data discovery allows all of us to shift perspective quickly and change the way we look at a problem.  We can cycle through different views deliberately trying to get the insight to pop out!
This is how we learn to understand the story our data is telling.  And once we understand the story, we can change the ending.

The Value of Data Discovery for Associations

The Magic Quadrant

In February 2013, Gartner Inc. released an important report entitled Magic Quadrant for Business Intelligence and Analytics Platforms which details the current state of the business intelligence (BI) market and evaluates the strengths and weaknesses of several of the top vendors. It’s interesting to note that in this report, Gartner emphasized the emergence of data discovery into the “mainstream business intelligence and analytics architecture”, something we have been highlighting at DSK Solutions for years.
What is data discovery? Associations and nonprofits are sitting on large quantities of data and don’t always realize the value of this powerful asset. The old days of spray and pray are gone. Remember direct mailing blasts? How ineffective! Associations were shooting in the dark and wasting resources that could have been allocated to better serve members. Unfortunately, some associations still rely on this marketing approach, but there is a better way: segmented target marketing based on data.
All of your data – including CRM or AMS (customer data), general ledger and budget (financial data), and Google Analytics (Web data), can be pooled together to illuminate your member strategy. Think of each data source as a small flashlight that reveals a little bit of the path in front of you. When your data sources are pulled together, the path becomes much clearer. When analyzing your data with data discovery, it becomes possible to discover things you did not know before.

Necessary Steps

Clients frequently come to us seeking guidance on how to begin the task of leveraging their data to inform better decision making. Before you can embark on data discovery, you have to do two things:

  • Ask the right questions.  What is meaningful to your organization? What are you trying to find out about your members, prospects, products, services and profit?
  • Clean your data. If your data is filled with duplicates, inaccuracies, inconsistencies and other forms of noise, your analysis will be flawed. Remember: Garbage in, garbage out.  Quality data as an input allows for accurate analysis as an output, which results in the improved ability to make good decisions.

These two steps form the foundation of the data discovery process. Almost always, the answers you derive from your data will lead to more questions. It’s okay to ask why. In fact, you should be asking why! Start by asking questions like these:

  • How dependent is your association on dues revenue?
  • What is the price elasticity of membership (Full Rate v.s. Discounted Rate)?
  • Which members are at risk for not renewing?
  • How far (in miles) will registrants travel to attend a meeting?
  • Which products or services have the highest profit?

Then start asking “why”.  Remember the idea of the Ishikawa (or fishbone) diagram?  It’s an easy and useful way to begin thinking in terms of cause and effect – you ask “why” 5 times, until you arrive at the root cause of an effect.  Now with interactive data discovery you ask these questions directly by interacting with the data in a visual way!  At DSK we describe it as “having a conversation with your data”.  For example, a certification department of an association wanted to look at their pass/fail ratio for an exam.  Using data discovery, they discovered many more college-aged people were registering and doing poorly than in the past.  In the process of asking “why” the failure rate was increasing, they discovered an opportunity not only to publish a new study guide, but also they located an entire new source of prospective members and created a new membership type to serve the college market.
Data discovery is an iterative process where you ask questions of your data in an interactive way. Drilling down both vertically and horizontally into your data allows you to not only answer the questions you know you have, but shed slight on those unknown-unknowns and enables associations to make better decisions.

 SS 2 Filtered on Type 2

How Can Associations Use SQL 2012 Data Quality Services (DQS)?


This is what bad data is like…

Data Quality

How valuable is the Ford Pinto brand?  How about a patent for a cat scuba suit?  Like other intangible assets, the value of data is rooted in its quality.  Nonprofits have an even greater motivation to maintain the integrity of their information, because their organizational success is dependent on effectively communicating via membership data.  One of the data quality management tools we deploy at DSK Solutions includes SQL Server 2012 Data Quality Services.  DQS is especially valuable because it offers an efficient, semi-automated means for associations to create a data quality foundation to build their enterprise analytics.  By identifying data attributes and functional dependencies, DQS can effectively correct bad entries (cleanse) and eliminate duplicate records (match).

The Benefits of Data Quality Services

First, DQS has the powerful ability to automatically discover knowledge about your data.  Even with only a sample of the larger data set, DQS can identify inconsistent, incomplete, and invalid data.  For example, using Term-Based Relations (TBRs), DQS can identify strings that are inconsistent with the rest of the entries in that column.  So, if ninety-nine of your entries use “123 Oak St” as the street address and one uses “124 Oak St,” DQS will correct the odd entry to be consistent.  Additionally, developers can build domain rules that define the correct format or value.  For example, if a user email does not follow the pattern “something@somthing.com”, DQS can either mark the entry as invalid for later review or automatically update with the missing characters.
Next, DQS can check for consistencies throughout the record.  Using third party reference tools or user-defined rules, associations can validate that data is logical.  For example, if an entry lists a member city as “Chicago” and member state as “DC,” DQS can identify the inconsistency and either mark it as invalid or correct it to “IL.”  Another valuable feature is that users can develop matching rules to determine duplicate entries.  For example, if two records are 95% similar (again, based on user-defined rules), DQS can eliminate duplicate rows and consolidate the data into one unique entry.

2 types of data profiling

Two types of data profiling

Finally, DQS has an effective user-interface for controlling the discovery and cleansing process.  A DQS project steps through mapping the fields to rule domains, creating results that rate data on completeness and accuracy, and managing the project results.
Unfortunately, DQS is not a magic bullet.  There are some challenges to implementing DQS for large databases.  For example, the implementation of DQS for an AMS/CRM involves many important steps.  First, analysts, like DSK Solutions, consolidate problem data into a single table or view (DQS transformations work on one table, not entire databases).  Next, associations cleanse and match the data using a combination of DQS SSIS transformation and manual data verification.  Finally, data experts reintegrate the groomed data back into the original table structure (including considerations for timing, normalizing, and other SQL scripting).
DQS implementation

DQS Implementation for netFORUM

To conclude, it is important to note the DQS is knowledge-driven, meaning that it will take data-oriented managers to develop a strategy for a final asset.  As the non-profit world embraces data, DQS will play a pivotal role in creating the level of quality necessary to build an effective business intelligence infrastructure.

Business Intelligence Trends 2013

Although the term “Business Intelligence” is so overused that it is almost meaningless, what is important to know is that advances in data science and analytics are affecting everyone – every day.  Tableau outlines important trends in this field for 2013. What do these trends mean to your association?

Warning: A non-numeric value encountered in /homepages/0/d741589923/htdocs/clickandbuilds/AssociationAnalytics/wp-content/themes/Builder/lib/builder-core/lib/layout-engine/modules/class-layout-module.php on line 499