HOW TO USE D4E WITH SERVERS AND (ODBC-FREE) THIN CLIENTS

Step 2.  Establish your Outlook email to receive Alexa messages.


Alexa commands like "Alexa, add To-do, Loans" create entries in a To-do list, while commands like "Alexa, show my To-do list" can send the To-do list to your email address.  (You may have to configure this Alexa skill using IFTTT at ifttt.com.)  Once Alexa is configured and able to send emails, you can expect to see in Outlook, new emails with subject line "Items on your Alexa To Do List".  The body of this email contains a list of key words such as Loans,  Balances, etc.   These keywords will be converted by the Scheduler to report app names, such as Alexa Loans or Alexa Balances, which will then be executed if found in My Apps list of applications.  In this example, Alexa Loans app runs a script:

           setprompt CHOOSE YYYYMM IN? = ~~201712
           getnow SIMGL Show GL_BALANCES
           showget SIMGL Show GL_BALANCES
           email myname@email.com, D4E Results, [[F1]] [[F3]] has [[M1]] value of [[M3]]


Step 3. Converse with Alexa to generate a report.


Say:

"Alexa, add To-do, Loans", "Alexa, show my To-do list.", "Alexa show my Priority message"


You will then hear Alexa read the results of running the report app Alexa Loans:

"Premium_Class TRN has MTD value of -6463.93"


Conclusions

This sample project illustrates that you can use Smart Home devices to get real-time information using D4E automated background processing.  Alexa devices such as the Echo Dot are particularly suited for D4E to deliver critical real-time data using your voice.

Step 2. Set up all Client PCs with D4E add-in only.

On the client PC- you only need to have Excel and D4E add-in. ODBC drivers and database connections are not required!  The client PC Excel/D4E setup will submit D4E commands/scripts to the server PC, so that execution will happen in the server.  After completion, the server PC will simply send the results back to the client PCs. This can save processing time, setup effort, and resources in the client PCs.  There can also be big productivity boosts and/or cost savings if direct database access is limited to a few restricted and/or expensive user licenses. To submit job requests to the server, the client PCs must use the Submit button (Thunderbolt icon) instead of the OK button, as below:

Click on a D4E application to submit a request to the D4E server.  A link will be provided to view the results.

You can build ASP.NET web server applications using Microsoft's WebMatrix - a free development tool that can be downloaded here.  Excellent web tutorials can be found in www.w3schools.com


To build an ASP.NET web site for interfacing to D4E server:

1. Install Microsoft WebMatrix.  Open WebMatrix -> Open -> Folder -> Navigate to D4E installation folder \D4ESITE -> Select Folder.   The website will have 3 files and 1 folder:

    - \RESULTS\    (contains HTML result files for display)

    - default.asp

    - CheckFile.asp

    - RunD4E.asp


2.  Run the WebMatrix site and the browser will open a home page with a list of D4E apps to execute, as below.

Click on Check File repeatedly, to ensure the extract is complete and results are available.

Step 3. Alternatively, set up your own non-Excel programming language or web system to interface with D4E.

You can interface with D4E by submitting command files to the D4E server and the D4E server will return results in an output file.  You can use any non-Excel programming languages (eg, Java, ASP, VB, C/C++, DOS commands) or systems (MS Access, .NET,  web apps)  to create/submit the command files and then process any returned results  On the client PC, Excel and D4E are NOT required as the client only needs to submit a plain text file to the D4E server location.  After completion, the server PC will send the results back to your own programming language or system.


For example, consider the following D4E commands, to be executed by the D4E server:             
getnow SIMGL Show GL_BALANCES                     
savetab SIMGL Show GL_BALANCES, C:\Users\Antonio\AppData\Roaming\d4e\WAIT\output\180616105613_Antonio.html  

(This will run the app "SIMGL Show GL_BALANCES" with default parameters and then save the results for web viewing.)


You can use any method to write the above commands to a file in the FILEWAIT folder location (<<\:>>WAIT\INPUT\FILEWAIT\). For example, the following DOS batch script can do this:

echo on
rem SAMPLE API CALLS TO USE D4E ACTIVE SCHEDULER
rem Start a D4E scheduler before executing this file
set fname=%date:~12,2%%date:~7,2%%date:~4,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%_%USERNAME%
set "fname=%fname: =0%"
set cmdfile="%appdata%\d4e\WAIT\input\filewait\%fname%.txt"
set outfile=%appdata%\d4e\WAIT\output\%fname%
set dmart=%1 %2 %3 %4 %5 %6 %7 %8 %9
set "test=%dmart: =%"
if "%test%" == "" set dmart=SIMGL Show GL_BALANCES
title Running ... %dmart%
rem Create command script directly into the FILEWAIT folder for execution  
echo getnow %dmart%                     > %cmdfile%
echo savetab %dmart%, %outfile%.html    >> %cmdfile%
echo off
:findfile
if NOT exist %outfile%.html goto :findfile
PING localhost -n 6 >NUL
start %outfile%.html

Save the above DOS script to file playapp.bat and execute. D4E server will process the command file, extract and write the results to a HTML file. Then the HTML file will open in a browser as below  You can convert the above process into your own programming language or system (e.g., ASP script and web programming) .


Note: playapp.bat script can be called repeatedly using below script (saved as playapps.bat):

rem No parameters allowed. Create a dmart script if you want to use parameters
call playapp SIMGL Show GL_BALANCES
call playapp SIMGL Show GL_LINES

Repeat process to choose another D4E app.

3.  You can publish the D4ESITE web site, so long as the web server has direct connection with D4E server folders.



Conclusions


By interfacing ASP.NET with the D4E server, you can have a fully digital platform for your UDA's .  Excel reports powered by D4E can be accessed via the web.  This also means that Excel based UDA's, whilst utilizing the best that Excel can offer, can be fully insulated against code corruption or misuse ("Excel hell"). 

You can set up D4E in a server machine (or any stand-alone PC) dedicated to D4E database access. When a server machine is successfully able to access all databases using D4E apps, that machine can now be used to serve many client PCs and provide database results.  This reduces burden on the client PCs in terms of processing resources and connection activities.  More importantly, the client PCs do not even need ODBC drivers or credentials to access the databases.  It is only the server that will connect using its own credentials.  The client PCs only need to connect securely to the server using LAN profiles.

Step 1. Set up the Server PC with D4E.

On the server PC- install Excel, D4E, ODBC drivers, and all database connections as per normal.  Execute the command "StartScheduler" via a menu item, script command, or a macro command. This will hide the current instance of Excel and enter a continuous process of waiting for job requests from client PCs. The scheduler will appear as below.  Click on "less" for a smaller view.

SAMPLE PROJECT - ASP.NET WEB SERVER INTERFACING WITH D4E SERVER

You may want to try the following voice conversation with Alexa Smart Home devices (such as the Amazon Echo Dot):


1. To request Alexa to add a report using the To-do List, say:

         "Alexa, add To-Do, Loans"

2. To request Alexa to send a list of reports to generate, say:

         "Alexa, show my To-do list"

(Previously, you configured D4E to wait in background mode to receive emails, where the body of the email contains a list of reports to execute.  D4E will execute these app reports, extract information from the database, then send an email showing a summary of the extracts.)

3. You can then request Alexa to read the report summary from your latest email:

         "Alexa, show my Priority message."


Alexa Smart Home devices can be configured easily to work with Data Mart for Excel because D4E can interface with Outlook emails when automatically generating reports or application extracts.  The following steps illustrate how this can be done:


Step 1: Establish D4E automatic Scheduler mode.


Execute the built-in app PERMMDB Scheduler.  Enable SAMPLE_23 schedule (set up to wait for Alexa), then start the Scheduler.


Play App --> PERMMDB Schedule

Settings -->  Server Mode --> Open Scheduler --> START

SAMPLE PROJECT - USE D4E WITH ALEXA SMART HOME DEVICE

In this sample project we will show how to write an ASP.NET (Active Server Pages) application that can send commands to the D4E server and return Excel D4E results to the browser.  This means that D4E will execute any of the stored apps, extract data from databases and pass the results to the web server for display in a browser.  A user will only need the browser to get database results from D4E and Excel.


Conclusions


By using D4E servers, end-users can operate D4E in thin environments like standard PC configurations (SOE), Citrix installations, or browser-based ASP apps. By doing this, it is possible to have zero footprint; save on per-user database license costs; or boost productivity in environments with limited data access.

To manage long running requests, you can right-click on the Submit button to retrieve results from completed jobs previously submitted.  Shift-click on the Submit button to check if a server is available.

A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION