Systems and methods for collection and consolidation of heterogeneous remote business data using dynamic data handling

• US 9,411,864 B2
• Filed: 10/14/2013
• Issued: 08/09/2016
• Est. Priority Date: 08/26/2008
• Status: Active Grant

• Current Assignee: ZeeWise, Inc.
• Original Assignee: ZeeWise, Inc.
• Inventors: Glider, Clark S., Hix, Josh, Zalewski, Bartosz J.
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US14/053,367
• Publication Number:
  US 20140040182A1
• Time in Patent Office: 1,030 Days
• Field of Search: 707/602, 707/796, 707/736, 707/668, 707/622, 709/201, 709/203, 709/213, 709/217, 709/238
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: G06F 40/151 Transformation
• CPC Class Codes: G06Q 40/12 Accounting

Abstract

Remote data collection systems and methods retrieve data including financial, sales, marketing, operational and the like data from a plurality of databases and database types remotely over a network in an automated, platform-agnostic manner. An Extract Transform and Load (ETL) data replication method for Chart of Account (COA) standardization includes receiving a request for remote data collection to extract data from a data source; extracting data in a non-intrusive manner from the data source, wherein the data comprises non-standard COA data; and transforming one of an entire set or a subset of the extracted data based on the request based on a template or a standardized form desired for comparisons.

Claims

1. An Extract Transform and Load (ETL) data replication method for Chart of Account (COA) standardization, wherein the method is performed by a processor, comprising:

receiving a request for remote data collection to extract data from a data source;

extracting data in a non-intrusive manner from the data source, wherein extracting data in the non-intrusive manner from the data source comprises extracting the data in a read only data manner, and the extracted data comprises non-standard COA data;

wherein the step of extracting data is comprised of

(a) accessing data from a plurality of accounting system files comprising heterogeneous data sets across different versions of an accounting application, different accounting applications or different types of businesses; and

(b) executing definition files for each of the heterogeneous data sets that comprise specific collection commands defined to perform one or more of the following steps;

extract all, extract deltas between the data, re-extract, extract specific items, transform the data locally, or transform the data on the server side; and

transforming one of an entire set or a subset of the extracted data based on the received request based on a template or a standardized form desired for comparisons.

2. The ETL data replication method of claim 1, further comprising:

extracting the data using a two phase process comprising a reconciliation phase and a collection phase.

3. The ETL data replication method of claim 1, further comprising:

performing the transforming as an agent process, a server process, or client process.

4. The ETL data replication method of claim 1, further comprising:

reformatting the extracted non-standard COA data so the extracted non-standard COA data conforms to a standard.

5. The ETL data replication method of claim 1, further comprising:

generating read only mapped views of the source data in order to extract the data; and

transforming the extracted non-standard COA data to conform to a standard COA without modifying the data source.

6. The ETL data replication method of claim 1, further comprising:

displaying a graphical user interface of a Visual Cash Flow model comprising a comparison between any two members or peers using the transformed data.

7. The ETL data replication method of claim 1, further comprising:

displaying a visual graph format starting with a zero axis and applying individual data elements as either positive or negative offsets as graphical elements from a neutral zero axis in order to compare one set of business results to a national average or peer group calculated value generated from the transformed data.

8. The ETL data replication method of claim 1, wherein the transforming comprises:

performing one or more steps of sorting, calculating, and normalizing the data based on the plurality of accounting system files, the heterogeneous data sets, and desired peer groups for comparison to provide a consolidated data set across the different versions of the accounting application, the different accounting applications or the different types of businesses.

9. The ETL data replication method of claim 1, wherein the extracting comprises:

accessing data from a plurality of accounting system files, the plurality of accounting system files comprising heterogeneous data files from different versions of an accounting application, different accounting applications or different types of businesses;

performing one or more steps of sorting, calculating, and normalizing of the data files from each of the different versions of an accounting application to create a first set of normalized data;

performing one or more steps of sorting, calculating, and normalizing of the data files from each of the different accounting applications to create a second set of normalized data;

performing one or more steps of sorting, calculating, and normalizing of the data files from each of the different types of businesses to create a third set of normalized data;

consolidating the first set of normalized data, the second set of normalized data and the third set of normalized data to create a fourth set of consolidated normalized data; and

performing one or more steps of sorting, calculating, and normalizing of the data for a plurality of peer groups on at least one of the first set of normalized data, the second set of normalized data, the third set of normalized data or the fourth set of consolidated normalized data.

30. The ETL data replication method of claim 1, further comprising:

generating mapped views of the source data in order to extract the data, transforming the extracted non-standard COA data to conform to a standard COA.

31. The ETL data replication method of claim 1, further comprising:

extracting data, via the agent, command and control metadata, in accordance with the generated definition files.

32. The ETL data replication method of claim 1, further comprising:

extracting data from one or more remote data sources, wherein the one or more remote data sources is an accounting data source or an LOB data source or both.

10. A remote data collection system for Extract Transform and Load (ETL) data replications for Chart of Account (COA) standardization, comprising:

a network interface;

a connection to a data source;

a processor communicatively coupled to the network interface and the connection; and

memory storing instructions for remote data collection that, when executed, cause the processor to;

receive a request for remote data collection to extract data from the data source;

extract data in a non-intrusive manner from the data source, wherein extracting data in the non-intrusive manner from the data source comprises extracting the data in a read only manner, and the extracted data comprises nonstandard COA data;

wherein the instructions to extract the data further cause the processor to

(a) access data from a plurality of accounting system files comprising heterogeneous data sets across different versions of an accounting application, different accounting applications or different types of businesses; and

(b) execute definition files for each of the heterogeneous data sets that comprise specific collection commands defined to perform one or more of the following steps;

extract all, extract deltas between the data, re-extract, extract specific items, transform the data locally or transform the data on the server side; and

transform one of an entire set or a subset of the extracted data based on the received request based on a template or a standardized form desired for comparisons.

11. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

extract the data using a two phase process comprising a reconciliation phase and a collection phase.

12. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

perform the transforming as an agent process, a server process, or a client process.

13. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

reformat the extracted non-standard COA data so the extracted non-standard COA data conforms to a standard.

14. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

generate read only mapped views of the source data in order to extract the data using a standard COA without modifying the data; and

transform the extracted non-standard COA data to conform to a standard COA without modifying the data source.

15. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

display a graphical user interface of a Visual Cash Flow model comprising a comparison between any two members or peers using the transformed data.

16. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

perform one or more steps of sorting, calculating, and normalizing the data based on the plurality of accounting system files, the heterogeneous data sets, and desired peer groups for comparison to provide a consolidated data set across the different versions of the accounting application, the different accounting applications or the different types of businesses.

17. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

access data from a plurality of accounting system files, the plurality of accounting system files comprising heterogeneous data files from different versions of an accounting application, different accounting applications or different types of businesses;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different versions of an accounting application to create a first set of normalized data;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different accounting applications to create a second set of normalized data;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different types of businesses to create a third set of normalized data;

consolidate the first set of normalized data, the second set of normalized data and the third set of normalized data to create a fourth set of consolidated normalized data; and

perform one or more steps of sorting, calculating, and normalizing of the data for a plurality of peer groups on at least one of the first set of normalized data, the second set of normalized data, the third set of normalized data or the fourth set of consolidated normalized data.

33. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

generate mapped views of the source data in order to extract the data, transforming the extracted non-standard COA data to conform to a standard COA.

34. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

extract data, via the agent, command and control metadata, in accordance with the generated definition files.

35. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

extract data from one or more remote data sources, wherein the one or more remote data sources is an accounting data source or an LOB data source or both.

36. The remote data collection system of claim 10, wherein the instructions, when executed, further cause the processor to:

display a visual graph format starting with a zero axis and applying individual data elements as either positive or negative offsets as graphical elements bars from a neutral zero vertical axis in order to compare one set of business results to a national average or peer group calculated value generated from with the transformed data.

18. Software stored in a non-transitory computer readable medium for Extract Transform and Load (ETL) data replication for Chart of Account (COA) standardization, and comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

receiving a request for remote data collection to extract data from a data source;

extracting in a non-intrusive manner data from the data source, wherein extracting data in the non-intrusive manner from the data source comprises extracting the data in a read only data manner, and the extracted data comprises non-standard COA data;

wherein the instructions cause the system to extract the data by performing operations comprising

(a) accessing data from a plurality of accounting system files comprising heterogeneous data sets across different versions of an accounting application, different accounting applications or different types of businesses; and

(b) executing definition files for each of the heterogeneous data sets that comprise specific collection commands defined to perform one or more of the following steps;

extract all, extract deltas between the data, re-extract, extract specific items, transform the data locally, or transform the data on the server side; and

transforming one of an entire set or a subset of the extracted data based on the received request based on a template or a standardized form desired for comparisons.

19. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

extracting the data using a two phase process comprising a reconciliation phase and a collection phase.

20. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

performing the transforming as an agent process, a server process, or a client process.

21. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

reformatting the extracted non-standard COA data so the extracted non-standard COA data conforms to a standard.

22. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

generating read only mapped views of the source data in order to extract the data, transforming the extracted non-standard COA data to conform to a standard COA without modifying the data source.

23. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

generating mapped views of the source data in order to extract the data, transforming the extracted non-standard COA data to conform to a standard COA.

24. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

displaying a graphical user interface of a Visual Cash Flow model comprising a comparison between any two members or peers using the transformed data.

25. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

displaying a visual graph format starting with a zero axis and applying individual data elements as either positive or negative offsets as graphical elements from a neutral zero axis in order to compare one set of business results to a national average or peer group calculated value generated from the transformed data.

26. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

performing one or more steps of sorting, calculating, and normalizing the data based on the plurality of accounting system files, the heterogeneous data sets, and desired peer groups for comparison to provide a consolidated data set across the different versions of the accounting application, the different accounting applications or the different types of businesses.

27. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

access data from a plurality of accounting system files, the plurality of

accounting system files comprising heterogeneous data files from different

versions of an accounting application, different accounting applications or different types of businesses;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different versions of an accounting application to create a first set of normalized data;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different accounting applications to create a second set of normalized data;

perform one or more steps of sorting, calculating, and normalizing of the data files from each of the different types of businesses to create a third set of normalized data;

consolidate the first set of normalized data, the second set of normalized data and the third set of normalized data to create a fourth set of consolidated normalized data; and

perform one or more steps of sorting, calculating, and normalizing of the data for a plurality of peer groups on at least one of the first set of normalized data, the second set of normalized data, the third set of normalized data or the fourth set of consolidated normalized data.

28. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

extracting data, via the agent, command and control metadata, in accordance with the generated definition files.

29. The software of claim 18, further comprising instructions executable by a system, and in response to such execution causes the system to perform operations comprising:

extracting data from one or more remote data sources, wherein the one or more remote data sources is an accounting data source or an LOB data source or both.