Data Warehousing for Not-for-Profit Organizations using Dynamics GP

In this article, we’ll continue discussing data warehousing, generally and particularly, for not-for-profit organizations that are hoping to develop their Microsoft Dynamics GP experience.

In modern business, data is ubiquitous, perhaps particularly for not-for-profits (NFPs).  Since data directly supports decision-making, with NFP organizations actively working to remain on budget and/or on track with members, donors, grants and so forth, data warehousing can be helpful to your Business Intelligence (BI) tasks.  If you’re anything like I was when I first heard of a data warehouse solution, you probably have questions.  The goal for this article is to deliver the answers for NFP teams to understand how a data warehouse can improve management and analyses of your information from Microsoft Dynamics GP and other data sources.

A definition.  Let’s start conceptually: a data warehouse (DW) is a multi-dimensional database.  More specifically, DWs are virtual storage units or server-staged databases, which can run either on their own server or a shared one.  To compare, an external hard drive offers you the space to store assorted types of files and applications while today’s commercial DW delivers a place to house diverse transactional and operational data sets.  Another way to understand DWs: if you could three-dimensionalize an Excel spreadsheet, you could organize all of your transactional and operational information in aggregated, streamlined, and effective ways, which is precisely the purpose of the solution, eliminating errors with business user friendly technology.  Management of modern commercial DWs involves a technical database management system, like Microsoft SQL Server Visual Studio or Solver’s application for managing the warehouse, called the BI360 Data Warehouse Manager, complete with a business user friendly interface and part of the BI360 Suite.
Installation.  When it comes to deployment and automation, commercial DWs usually come “out of the box,” which translates to an easy implementation, followed by replicating your organizational information from Dynamics GP and any additional data sources.  A consultant who specializes in extracting, transforming, and loading (ETL) of your information will deploy your DW, then automate the ETL process from your data sources.  It would be wise to invest in a DW that comes with a pre-built integration directly to GP.  After ETL, a consultant who can optimize and streamline your financial reporting, planning and dashboard processes will show you how to query data from your warehouse for the financial statements, budgets, and data visualizations  you depend on to inform your decision-making about the direction your NFP should take.  Once your DW is set up and your team is trained, business end users at all levels of the organization can manage the solution without involving the IT team.
When?  There’s not an exact threshold that an NFP can cross where you know for sure that you need a DW.  Think about it this way: fundraising has evolved significantly for NFPs in the past couple of decades, as you have expanded beyond direct mail and special event fundraising to include mobile and online giving, social media campaigns, e-newsletters, and more, all producing data that you have to consolidate, typically from separate sources with an application like Excel.  DWs can help you to save time and money, eliminate errors as well as the general tedium of manual documentation.  Furthermore, if your Dynamics GP server is sluggish because you have users pulling substantial data sets, at times simultaneously, a DW provides a higher performance and stability without slowing down the ERP system or operational databases.  The question of when might be best answered at the time you are ready to design richer, more well-rounded reports, budgets, and data visualizations consisting of data from multiple systems like CRM, grants management, membership, and payroll systems – all from the DW.
What about OLAP cubes?  In regard to housing various types of data, plenty of BI tools recommend or require an online analytical processing (OLAP) cube, so you might be wondering why not an OLAP cube over a DW?  OLAP cubes are simply not transactional SQL server databases, so managing the technology involves staff with hands-on, OLAP-specific experience and skills, such as MDX query language fluency, due to their complex technical nature.  Additionally, analytical information is the general output for a cube, as opposed to transactional data.  DWs are structured by subject in a single database, and you can replicate multiple, varied types of data to the easy-to-use, dynamic interface for accessible management and analysis of your information with your BI tools.
Managing the technology.  Business end users can manage today’s commercial DWs.  Setting up and automating the replication process of your NFP organizational information is easy, whether you are doing a one-time push or more of a routine.  You can also query data with the click of a button.  Since many DWs are Microsoft SQL Server relational databases, ordered by topic, like administration costs, donors, grants, and volunteers, you don’t have to involve IT.  Data warehousing organically offers cross module financial and analytical functionality by storing a number of diverse data types from multiple sources.  They usually do not involve concurrency control mechanisms, transactional processing, or recovery, with the exception of backing up your database, since they are stand-alone tools.  DWs typically come with built-in attributes and dimension trees, adjustment functions like currency conversions, integration techniques, eliminations, and data cleansing to deliver more streamlined processes.  You can also utilize a DW to help migrate your data from an older ERP to Dynamics GP, or from Dynamics GP to a future ERP system.  As opposed to converting years of historical data over to Dynamics GP, you can store this information in a data warehouse and perform a historical analysis there, bringing the last period’s closing balances, as an example, over to GP.
Data will continue to steadily grow in size and importance for organizational decision-making processes, maybe particularly for NFPs.  Therefore, DWs will also steadily become more prevalent, especially because you can aggregate your diverse data and systems from where your information comes into one space to expand your financial reporting, budgeting, and dashboards.  If you’d like to rely on one system to consolidate all of your data into a singular, high performing place without depending on IT to manage the technology, DWs can provide you that solution.  Solver offers a fully built, configurable Microsoft SQL Server-based data warehouse stand-alone and as part of the comprehensive suite of BI modules and would be happy to answer questions and generally review BI360’s easy-to-use Data Warehouse solution that enables collaborative, streamlined decision-making capabilities for your Microsoft Dynamics GP experience as a not-for-profit organization.