Difference between revisions of "Consolidating data from multiple data files"
(Created page with "== Overview == ODBClink can consolidate data from multiple data files and accounting software. == Consolidating data from multiple data files == In order to consolidate dat...") |
|||
| Line 5: | Line 5: | ||
== Consolidating data from multiple data files == | == Consolidating data from multiple data files == | ||
In order to consolidate data from multiple data files, you need to setup multiple data file connections. See [[ | In order to consolidate data from multiple data files, you need to setup multiple data file connections. See [[Managing Data Files]] for more information. | ||
Once multiple data files are connected and the [[Get Data|get data]] process has run, ODBClink will have downloaded the data from each data file to each data warehouse you created. | Once multiple data files are connected and the [[Get Data|get data]] process has run, ODBClink will have downloaded the data from each data file to each data warehouse you created. | ||
Revision as of 19:25, 14 January 2015
Overview
ODBClink can consolidate data from multiple data files and accounting software.
Consolidating data from multiple data files
In order to consolidate data from multiple data files, you need to setup multiple data file connections. See Managing Data Files for more information.
Once multiple data files are connected and the get data process has run, ODBClink will have downloaded the data from each data file to each data warehouse you created.
How to determine where the data came from
ODBClink will also include an additional field in each table, the CompanyRecordID field. This field contains the identifier of your actual data file. As example, data from first data file may have CompanyRecordID of 1. Data from second data file may have CompanyRecordID of 2, etc.
ODBClink may also include an additional field in each table, the CompanyName field. This field contains the name of your data file as defined in the manage data files dialogue.
To make sense of which ID represents which data file, you have three approaches:
- If the CompanyName field is present and populate, you can use that field to determine the source.
- ODBClink may automatically include a AA_tblMYOBDataFiles which you can join to get the name you used during your setup.
- Alternatively, including the MYOB_DataFileInformation or MYOX_DataFileInformation table (or similar named table for other accounting software) and joining data onto this table will allow you to get the name as defined within MYOB (or you accounting software).
Consolidating data from different accounting software
The tables to which the data gets depends on your accounting package.
- Data from MYOB up to v19.10 will get downloaded to MYOB_ tables;
- Data from MYOB AccountRight Live 2013 or higher will get downloaded to MYOX_ tables;
- Data from Reckon Accounts and QuickBooks get downloaded into QuickBooks_ tables;
- Data from Xero gets downloaded to XERO_ tables.
If you consolidate data from multiple accounting software, be mindful that you may then need to union multiple tables together to get the complete picture. This is because MYOB_ tables will not contain XERO data and vice versa.