A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION

DEMONSTRATION - HOW TO USE QUICK SQL ASSISTANT

DEMONSTRATION - HOW TO USE APPS THAT UPDATE DATABASE TABLES

You can execute SQL immediately by using the D4E Quick SQL feature. Simply enter your SQL command in Column A of the current sheet, and set up ODBC parameters in Columns B through E as shown below.  By using the Quick SQL menus, you can export SQL code from an existing app and then edit the exported SQL code when shown in Column A. When you click on Run Quick SQL menu option, D4E will utilize a built-in app with the displayed tab sheet information to execute the SQL and ODBC connections. Open the built-in Quick SQL app to show choices for columns and table names.

You can also use replacement variables in Quick SQL commands so that run-time prompts will collect parameter values before executing the final SQL. You can also format and color-code the SQL commands for better readability. Database results will be generated as output tab sheets similar to a normal D4E apps. Edit the SQL code in Column A as often as required then re-extract data from database tables.

Data Mart for Excel can be used as a sophisticated downloader/uploader to databases. The illustration below shows some examples of how an Excel-based downloader / uploader for databases can be created using simple D4E applications.
 
A. USING EXCEL TO CONSOLIDATE COUNTRY RESULTS INTO HEAD OFFICE GROUP RESULTS
In this example, imagine we have a global company with multiple country operations (Country A, Country B, Country C, and so on) each with a local General Ledger application that allows each country to produce their monthly financial reports independently based on their local database server and accounting software. The Head Office is located in Country H, and requires that each country operation submit a monthly report of financial transactions (a trial balance) so that the Head Office can generate the group accounts.

One easy way to do this is to have an Excel-based application that allows each country to download their trial balances into Excel spreadsheets.  All the GL information can be found in the extracted sheet including a unique country code. This process can be implemented using Data Mart for Excel simple applications, such as the app in the trial version seen in Demo 2, named - SIMGL Maint_GL_LINES.

This demonstration shows how easy it is to create datamart applications. It also shows how simple datamarts can become advanced datamarts using parameters. The trial version ships with powerful datamarts that can read many ERPs and General Ledgers, as well as generic datamarts that read any ODBC database. You can look at these free supplied datamarts to learn more about how to create powerful datamarts.  

Here is another example of using D4E as an uploader application:

B. A BETTER WAY TO CAPTURE CLIENT ORDER INFORMATION USING EXCEL
Many organizations today save time by uploading client's order information (previously entered using Excel) automatically into back-end production systems. For example, some insurance companies provide underwriters, brokers and partners with Excel-based spreadsheet uploaders. The partners enter client order information that is instantly validated and formatted thru Excel macros in the spreadsheet. After data entry, the spreadsheets are emailed/sent back to the organization'a back-office for processing. Processing at the back-office will be simple and accurate because, the spreadsheet has additional VBA code to allow for writing the entered data directly into an SQL table. There is no re-keying required.   The same spreadsheet is used by all channels of business. Because of the prepared SQL code embedded in the spreadsheet, they will have a common way of updating the organization's production tables. This allows the organization to save time and effort in capturing client information onto powerful production systems. It also means that the capture of information is done by the most knowledgeable source - the intermediaries and/or the clients. This translates to increased competitiveness in the marketplace. Previously,  organizations have had to enter these information themselves. Now it is just a button to click on the Excel Uploader (containing VBA and SQL UPDATE code.)

WHAT IS THE PROBLEM WITH THIS?

Although efficient, this can pose a security risk for the organization. Whilst the spreadsheet was originally created and maintained by the organization, it is sent to the unsecured world of partners, intermediaries and public clients. There is no guarantee that the VBA SQL code is not compromised. When it returns to the organization, a compromised code may be executing against the production systems. For example, someone outside the organization using the uploaders may change the SQL VBA code and replace UPDATE statements with table DELETE statements.  Since the back-office userids have the ability to change the production data, this misdeed will cause havoc when executed.

Many organizations today attempt to develop these kinds of uploaders without realizing that they are introducing potentially compromised SQL code. Those who realize the dangers abandon these systems altogether and miss out on the benefits of a secure Excel-based uploader.
 
DATA MART FOR EXCEL IS THE SOLUTION YOU HAVE BEEN LOOKING FOR

Data Mart for Excel is completely secured against compromised code in VBA SQL. This is possible because no SQL code is written in the "traveling" spreadsheet - only validation code or helper functions. If the end-user compromised any validation code, then this is just similar to the existing risk of end-users entering incorrect data. D4E SQL functional code remains in the add-in installed only on the organization's in-house PC's. The back-office user just clicks on familiar upload buttons- but these buttons are found in the Excel ribbon (menus) outside the spreadsheet. The organization can be confident that no SQL code or database schema information is exposed to the untrusted world and the production systems are safe.

The diagram below illustrates and compares the traditional method of creating Excel uploaders versus the secure method of using Data Mart for Excel apps.

Every country operation will use the same extract format and use the same D4E app. (Using parameters, the D4E app can be customised for the different local databases, but will look and feel the same to the end-user.)  Each country operation then signs off on the spreadsheet and emails them to the Head Office without change. The Head Office accepts the signed off spreadsheets and without any re-keying simply click on D4E upload button and the individual country data are uploaded to the HO central servers. The HO will have the same D4E application add-in installed and will be able to upload the common data easily. As soon as all country data are accepted, the Head Office end-user can then immediately generate a combined report of all country operations.
In summary, the country operations will use the D4E app named SIMGL Maint_GL_LINES to download their individual trial balances. Because it will be a fully tested application, there is no need for verification and manual keying. They are confident that what they send to Head Office are directly sourced from the General Ledger accounts they maintain, plus, they can be confident knowing that HO will use the same tool they use to upload data - therefore knowing that the financial information they signed off will be incorporated into group reporting in the way they want it to be - without changes or errors. This is very empowering!  Meanwhile,  Head Office also uses the same Excel D4E application named SIMGL Maint_GL_LINES but uses it to upload data they receive. They can be confident that the data HO has just uploaded to the central server comes from the country operations local servers directly - because it loads easily and purposely using the same D4E application they have used previously. This saves a lot of time and provides more confidence in the validity of group reports and board decisions. The illustration below summarises the Data Mart for Excel downloader / uploader application.