Excel™ Data Management Engine BPES_DME
One of the challenges we may face is to pick data apart from different sources in order to create an underlying worksheet representation that we can work on. This engine can work by using the interface files produced by applications (comma- or tab delimited data) to organize the data into a layout you can work with, and also report-image files where the software can position itself by using keywords to identify the content of report files that must be converted to a worksheet.
To use the pre-coded engines the student must understand that the code is locked and can only be controlled through data external to the VBA-code itself. This is to stop the code from being corrupted if the wrong code logic is applied. If you want to write custom code we suggest using the VBA training courses that explain the underlying nature of the VBA-code itself, which is a completely different focus from building an Excel™ worksheet application that is based on what the pre-coded engines deliver “out of the box” to make it easier to get started.
Compared to the other engines this application is completely utilitarian and designed to make sure that the correct contents are mapped to worksheet columns so that the extraction pulls the data from the source and places them into the correct place in the collector worksheet that has the role of a template definition as well as a data store definition. When your input data are in a number of different formats, you can use multiple “DME-xxxx” worksheets to pull the data into a standard format, and you can use a “SORT-xxxx” worksheet to collate the contents of multiple “DME-xxxx” worksheets into a single input table that you can then use for further processing.
There are two types of input. Structured input is already columnar and may values may be separated by commas, tabs, or blanks. Special options exist to deal with missing data values, to make sure the source columns do get matched to the input columns. Therefore, input data can also use the column-ranges on the source medium. The second option is unstructured based on report formats. This is a little more complicated, but it allows the logic to use identifiable report image characteristics to establish “anchors” that then help the logic to interpret the data and pull out data values that we can subsequently process.
The extract logic reads all data as character strings, and extends records if necessary to make sure we do not read outside the input record range. It uses the map to “understand” the data and to find data elements that we need for further analysis. Input data can come from reports, worksheets, database extracts, and various specialized applications, that each may have unique characteristics.
The best way to consider using the free-form data extraction logic is as a “reverse report image” where we use constants in the report image pages to “understand” the variable content of these pages and how the data are buffered. Typically we run each “DME-xxxx” worksheet separately, until we have all the extracts completed. During an extract we create a tag column at the extreme right limit of the worksheet based on the columns highlighted as primary, secondary, etc., keys.
The collate logic will copy the tag column contents from each “DME-xxxx” file to generate a composite of the data across multiple extracts into one tag column on “SORT-xxxx”. We use the Excel™-sort to create a single list in sorted order, and then cut and paste records from source worksheets into the “SORT-xxxx” worksheet. Since there are no parameters in that “SORT-xxxx” worksheet we end up with a clean source data worksheet to be used with any application dependent on the external data being in the predefined column structure.
There are many systems that can produce interface files consistent with an Excel™ format, but that does not mean the columns are arranged the way we need it in the target application. With the Data Management Engine it is not difficult at all to convert the data from various source format into the format that we need for processing.
Learning Formats BPES_DME
This course is currently available in a classroom setting (public or company private) with approximately 15 contact hours.
PDF – Certificate Of Completion
Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.
PDF – Course Notebook
Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.
PDF – Program Overview
An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.
PDF – Current Training Schedule
A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.
Registration – Service Providers
To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.
|