New Case Study: Automating XBRL Data Collection and Processing

Case studies are a great way to see how other organizations use Altova® technologies to develop unique projects that meet their business goals. We’re often asked, however, what comes next. Did the project take off? Has it grown since the case study was published?

We’re happy to bring you a follow up to a case study we published last year about the not-for-profit Maryland Association of Certified Public Accountants (MACPA).
In the original case study, MACPA converted General Ledger and other financial data from siloed systems into XBRL – in house and on a budget – with the Altova MissionKit®. At that point, they were mapping the XBRL file to multiple external targets, including an Excel spreadsheet used to feed their Key Performance Indicators (KPI) system. (You can read the original XBRL case study here.)

Since that time, they’ve built on the foundation they created and developed a business intelligence dashboard driven by the XBRL files they generate in MapForce®. They used Altova FlowForce®, an application designed to automate the execution of MapForce data transformations and other tasks on servers, virtual machines, and workstations, to automate the XBRL data collection and processing. Now the most recent data is available across the entire organization for custom reporting.

You can read the follow-up to the original XBRL case study here.

Read more about how they did it.

MACPA, like many organizations, uses more than one software application to manage their business, meaning that financial data is stored in more than one database. This configuration is able to support the information needs of individual departments. However, in order to connect the dots between these isolated data points and gain insight into the entire organization, MACPA needed to create a single data source.

MapForce provided them with a way to do that.

MACPA used MapForce to populate the XBRL Global Ledger, an XML Schema for chart of accounts, journal entries, and transactions, from siloed SQL Server® tables storing various chunks of financial data.

MapForce has drag and drop functionality for defining how data should be transformed between the source (in this case the SQL Server tables) and target (XBRL GL) files. User-defined data processing functions can also be built to simplify repetitive transformations.

Once they finished the mapping, MACPA was able to generate an XML output file directly from MapForce – the XML file contains all of the financial data they want to mine.

MACPA initially created batch files to run the mapping – each time the batch file was run, the XML file would be updated with the most recent data. The XML file in turn was mapped to different data targets including the XBRL GAAP taxonomy and Excel spreadsheets that feed the organization’s Key Performance Indicators (KPI) system. The latter allowed MACPA to review KPIs on a more regular basis without the onerous data collection process previously required.

This was just a start however. The ability to generate a single data source and then map that data to virtually any other target opened up a world of possibilities. Among other uses, MACPA envisioned a real-time business intelligence dashboard where employees could design and generate custom reports using not only their own data but data from across the organization.

The introduction of Altova FlowForce Server (now in Beta 2) helped make this a reality.
FlowForce Server is a job management and automation tool for data conversion and integration tasks. With FlowForce, users can create and schedule multi-step jobs based on a flexible schedule or by the presence of new or modified input files via an intuitive Web interface.

MACPA used FlowForce to automate the process so that it was no longer necessary to run batch files in order to generate the XBRL Global Ledger instance document – the mapping that generates the file automatically runs each night at a certain time, giving MACPA access to an up-to-the-day data source from which they can generate reports.

Once they automated the generation of a single data source containing all of their financial information, it was just a matter of building the dashboard. MACPA employees use PowerPivot, an add-in for Microsoft Excel®, through the dashboard interface to create custom reports which can then be saved and rerun from the dashboard.

MACPA has truly embraced the role of XBRL evangelist. Under CEO Tom Hood’s leadership, representatives from the organization fan out across the country to speak at conferences, meet with other thought leaders, etc. In fact, they’ve even established the Center for Transparency, Performance Management & Accountability using XBRL to provide practical resources, thought leadership and training that CPAs need to put XBRL to work.

However, it is their ability to showcase XBRL’s practical value as source of insight that makes them true trailblazers. MACPA has provided an ideal test case showing not only how a small organization can transform their financial data into XBRL – but why they should want to. Using XBRL, MACPA was able to both reduce data collection and aggregation costs and increase accessibility to financial information, putting them in the perfect position to gain greater insight into their operations. And the Altova MissionKit helped them accomplish all this in-house and within budget.

Read the complete case study here.