Business Intelligence Solution for National Association Dramatically Improves Data Management and Decision-Making Capabilities
Project At a Glance
RDA created a business intelligence solution that allows our client to efficiently upload, sort and report on vast amounts of data to more accurately track trending statistics in order to focus their decision making, advocacy and lobbying efforts as well as membership and recruiting initiatives. The solution leverages Microsoft SharePoint, SQL Server and SQL Server Reporting Services.
About Our Client
Headquartered in Washington, D.C., our client is a national association with over three million members. They have affiliate organizations in every state and in more than 14,000 communities across the nation.
Background
Our client wanted a business intelligence solution that would enable them to make better use of internal membership data and external government data to improve process efficiency and help drive new revenue by increasing association membership.
The association uses its data to understand more about its members. For example, by looking at membership density (where members are and how they are grouped), they can better organize members and determine where to focus promotional and recruiting efforts.
Data also helps our client track trends that show changes over time, such as salaries, membership numbers, revenue sources, and funding levels in specific locations of the U.S. Access to trending data helps our client streamline its political lobbying efforts, serving as an advocate for its members.
Prior to this project effort, our client wasn’t able to easily combine its data with meaningful results. Much of the process was arbitrary and time-consuming. They were looking for a solution that would enable them to extract data from multiple sources and create automated reports and dashboards for a variety of needs.
Solution Detail
RDA developed and implemented a Research Data Mine (RDM) system for our client leveraging Microsoft SharePoint and SQL Server. This business intelligence solution allows them to gather, sort, and report on membership and other critical data in an efficient manner, enhancing their decision-making capabilities and processes.
The solution allows users to upload large datasets acquired from external sources (in Excel format) to a relational SQL database. (This accommodates data that was previously stored in SharePoint lists on their internal portal.) In addition to external data, other internal data is extracted from Oracle databases via a similar process using linked servers and ported into the same SQL database.
The solution allows administrators to asynchronously upload large datasets into a SQL Server 2008 database, where it is combined with membership data extracted from internal Oracle databases and salary data manually collected from all 50 state affiliates.
To accommodate this, RDA created several custom extract, transfer, load (ETL) modules using SQL Server Integration Services (SSIS) to validate and process source data and transform it into the relational model. This relational data is then combined and aggregated into a number of SSAS cubes that serve data to reports and dashboards using SQL Server Reporting Services (SSRS) and PerformancePoint Services.
Benefits
Our client is now able to upload its data more efficiently and access the information from a central source. By taking advantage of accurate trending statistics and other data, they can more effectively target recruitment of potential new members and also focus their lobbying efforts on the regions that make the most sense. There is less wasted time and improved results, from an advocacy standpoint as well as member growth.
Technically Speaking
The project originally leveraged SQL Server 2005 and SharePoint 2007. As the solution matured to accommodate requested enhancements, the technology was upgraded to SQL Server 2008 and SharePoint 2010. Even after the upgrade, the project continued to evolve and requirements became more sophisticated. To meet these new requirements, RDA transitioned from using a relational model that relied on stored procedures to serve data, to a multidimensional model based on SSAS cubes. This gave our client the ability to process larger sets of data more efficiently. RDA also created PerformancePoint dashboards that incorporate maps, charts, and gauges for improved visual effect as well as Excel Services pivot tables that provide additional ad-hoc reporting capabilities.
Administrators have enhanced data upload capabilities as well. While the original system had limitations on the amount of external data that could be uploaded into the system due to timeouts, the latest release resolves this issue by asynchronously uploading Excel formatted data using WCF services and AJAX controls that communicate with SSIS packages during processing.
Maintenance has been automated as well. Jobs that extract data from internal systems are scheduled nightly and are followed by jobs that automate cube processing to ensure the latest combined data from all sources is made available to users every morning.
Following are several screenshots of the solution RDA developed:
This dashboard displays national and state by state membership density, calculated by dividing members by organizable staff.
This dashboard displays membership density and organizing potential for a given state. It also includes the top and bottom districts and UniServs in each category. A UniServ encompasses several districts within a state. High potential targets are identified by using a complex algorithm with several variables including membership statistics and population growth over the past five years.
This dashboard displays memberhip trends over the past five years at a national level.
This dashboard displays memberhip trends over the past five years at a state level.
cs 308