#formlink

[nic-accounting-addons]

Solution: Creating SQL-based solutions for MYOB and other accounting packages.

While MYOB comes with many standard reports, it was enlightening for me to find out that many users were out there which did not have the reports they needed.

As such, these users typically exported various reports to excel and re-coupled them together with many complex formulas.

The AddonsLink platform we created allows you to create those reports using more rigorous software development practices.

Overview

AddonsLink has a module called Data Warehouses. This module allows you to define various Microsoft Access or Microsoft SQL Server databases which will store data downloaded from MYOB.

The standard get data process that BudgetLink, TimesheetLink and FormLink uses also downloads data to custom data warehouses. In fact, those products use the exact same technology.

The data warehouses can be programmed to pull the necessary tables simply by ticking them in the list. The get data process will flesh out the fields and populate the data.

Once this process has run, the data warehouse can be enhanced with custom SQL queries to shape the data as required.

The performance of those queries is surprisingly good since the data is cached locally to disk. On top of this, certain queries can be created to compute and store the results similar to the way stored procedures work.

It does not only work with MYOB but also integrate with some tables from Xero and Quickbooks. This means that both Quickbooks and Xero get an ODBC/OLEDB like access using this technology.

If you've ever wanted to create your own reports, knew a bit of SQL (or could design basic queries using the query designers) and feel up to the challenge, the data warehousing capacity of the AddonsLink platform could be for you.

N.E.

[nic-accounting-addons]

Architecture: The Get Data process explained

The AddonsLink Get Data process is broken down into a series of manageable steps. While it does take some time to run, it does do a tremendous amount of work.

At it's core, the process is broken into the following steps:

Download raw data from the data file;

Transform raw data into standardised data using data file interfaces;

Transform raw and standardised data using data warehouses;

Load data into excel's memory.

This step varies from data file format to data file format. For MYOB data files, the data is transferred using SQL. For Quickbooks, we download the data using QBXML and XSLT. For Xero, we use both an OAUTH client and XSLT.  Ultimately, the intention of this step is to get the raw data and provide it to the data file interface.

Transforming the data using the data file interfaces.

Early on in our architecture, we realised that to support multiple data file formats, we would need to standardise the data some way. To achieve this goal, we designed the concept that we call the data file interface.

This interface acts like a contract between the raw data file format (MYOB, Quickbooks, XERO, etc.) and the standardised AddonsLink format.  It allows us to pull data from various formats, shape it and provide the data to other parts of the application in a standardised format.

This contract can be fulfilled in any way required. As such, the AddonsLink platform doesn't really know how each data file format will be processed. It simply knows what the inputs are and expect the outputs to be provided.

Transforming the data using data warehouses

Once the data file interface has shaped the data, the data gets stored into the various data warehouses.  These data warehouses have access to both the raw data or the standardised data depending on the problems they are trying to solve. They can also shape the data further in order to calculate data for reporting purposes.

While both the data file interface and the data warehouses shape and transform data. They do so with complete different goal in mind. The data file interfaces' purpose it to provide standardised data. The data warehouses' purposes is to fulfill precise business rules for specific reports.

Loading the data into excel's memory

Finally, the get data process loads the required data into memory for reporting purposes. This part is mostly specific to BudgetLink but some other AddonsLink-based applications (FormsLink and TimesheetLink) also use this step.

Once the data is loaded into memory, functions can be used to extract the data and place it on the worksheets.

While the whole get data process may seem a pretty dry read, it is an amazing piece of work from a technology's point of view.

N.E.