DEMONSTRATION - HOW TO CREATE ADVANCED APPS

The above script will extract data using the first D4E app (SIMGL Show GL_LINES) then automatically create 2 pivot tables using the second D4E app (SIMGL Pivot Curr Sheet).  No VBA code will be stored in user files.

TOPIC 1:  Creating Power BI Query reports by storing M code in D4E apps.

In this sample project, you can use Power BI to read data from a COVID-19 website.


D4E can store and execute M code (this is Power BI's query language) taken directly from Power BI queries.  You first develop and test the M code using normal Power BI queries. Once they are correct and tested you can extract the M code from PowerQuery Advanced Editor. Below is M code that has been developed in PowerBI query:

TOPIC 3:  Creating Excel reports from stored procedures.


Apps can also be created by simply nominating stored procedures, as in the following example:.

When referencing data sheets (e.g., to create pivot tables) generated by M code queries, please use only direct cell references instead of named tables; as tables may be lost/recreated when refreshing D4E apps.  For example, for pivot tables, you can go to PivotTable Analyse > Change Data Source > enter a range (not table).  Named Tables can also be converted to ranges:- highlight table then goto > Table Design > Convert to Range > click OK.

From the test library, the developer exports the two items (EXAMPLE Extract XYZ and @Simple GL) into a workfile c:\DMart.xls by clicking on Export to File. Then, IT change control procedures will import the workfile into the production library by clicking on Import to Library. The new items will then be migrated from test into production under strict audit controls.

TOPIC 7:  Using Secure ODBC userids and passwords with D4E.


Many corporations and database administrators are wary of allowing end-users to access production databases via ODBC because of the potential need to provide ODBC userids and passwords that directly access the production database. Once end-users are granted DBMS userids and passwords, they can use these in other non-standard ways by creating and running their own SQLs (eg, via TOAD, Microsoft Query, or Excel VBA ODBC commands).

Even if the SQLs  created by end-users are for read-only, they still pose a serious threat to the performance and integrity of the entire production database. D4E provides a way for end-user to access the databases using ODBC SQL without the need to be  issued with individual DBMS userids. This way - only SQLs created, managed and tested under strict SLDC controls will be used against production databases.

This is done by creating a single system userid/password for the D4E application and encrypting the plain text userid/password.  The encrypted userid/password is stored in secured files/folders along with secured D4E files.   Even though the encrypted passwords can be read by end-users, they cannot be used to access the database directly using other tools like TOAD or MS Query.   This setup allows only D4E to utilize the userid/password, even though the ODBC SQL is executed in the user PC.and uses shared folders.  End-users will only need their Windows credentials to access D4E files, but in the end will be able to access databases securely via D4E.


To encrypt passwords, go to D4E > Settings > Manage > Encrypt Plain Text Passwords.   Below is an example of encrypting a plain text password.  The encrypted text string can then be written to the password.txt file when generated.

To update the database, select the sheet row and enter modifications in the columns.  Put a "U" under Action column to send the modified row. Use "U" to update/insert or "D" to delete a record by key.  When all rows have been selected and modified, click on the Update icon (blue up arrow) in the Action ribbon tab.

Security Note: You Windows ID must have read/write access to the database for the ODBC SQL command in the app to succeed.    Alternatively, the app can be programmed with a secret userid/password that is stored in a secure location in a separate file.  This is done using variable substitution in the Connect field under Datamart Properties:-  Edit the DM > Data Mart > Properties > Connect.  In the ODBC Connect string, add a clause with file substitution variable like:-  USERID=DBUSER, PWD=<<G:\securedfolder\filename.txt*1>>.  This way endusers cannot access the database other than thru D4E and only when they have signed on successfully to Windows.

After successful update, a summary count of error and valid records will be displayed as below:

TOPIC 2:  Creating Apps that can update tables.


It is easy to create apps that can update database tables.  They are very similar to apps that read only.  The example below is an app (SIMGL Maint_GL_LINES) the can update / maintain a table.

Note that you can use D4E variable prompt parameters (.e.g., <<Choose Pivot ROWS as?>>) in the VBA code. This will customise the VBA code at run time in combination with other D4E apps that may use the same parameters.  You can use the above D4E app as part of a script with other D4E apps, as in the example below.:

Note that the first red circle above is the name of the stored procedure in an execute statement with the following format:-
       execute <stored procedure name>  @params=value, etc                   (the number 600 refers to the maximum time in msecs, before timeout)

The second red circle shows the @parameters needed by the stored procedure, and how they will be shown in a prompt screen when presented to the user.   When this app is executed, the following prompt screen will appear.   Prompts 3 through 7 will accept values from the user, and pass these to the stored proc as named parameters.  When the app (and stored procedure) completes execution, it will return the last result set from the stored procedure into a spreadsheet.

By storing M code in a secure central repository, end users will always execute the latest intended version, while developers can always upgrade the M code without needing to access individual files created by end users.  When executing M code, D4E will create temporary queries that are subsequently deleted.  The end user will be able to store the results of running the M code, but can not keep any M code source in their Excel file. 

To recap:
1. M code is created and tested by developers using normal Excel, PowerBI, and PowerQuery.
2. Once final, M code (text) is transferred to D4E app store and executed as an app within the D4E menu system.
3. The advantages of doing this are:
- End users can save space, manage Excel files better, and always get the most up-to-date copies of the M code.
- Developers can maintain the M code (Power BI queries) from a central location, which is automatically deployed to the end user. The M code source can be secured from prying eyes to protect intellectual property.
- The company can apply enterprise-wide governance (robust controls) to all M code.
- Additional functions automatically become available to M code when stored and maintained in D4E, e.g., invoked via ribbon menus, shortcuts, scripting, scheduling, encryption, and parameter prompts.

Below is an M code with additional parameter prompts when stored in a D4E app. Furthermore, as an app (e.g., "SIMGL covid rank"), the M code can be invoked in the ribbon menu Quick Access shortcuts. When app is executed in D4E, user will be prompted for a value that will be used to modify the M code prior to execution in PowerBI.

The same M code can be taken from the Advanced Editor and pasted to a D4E app.  The original Power BI query and Excel file should be deleted and thereafter D4E can execute the M code by invoking PowerBI on the fly.  Below shows the M code stored in the D4E app library (which is in a separate and secure enterprise app store):

This app is similar to other apps except for two things as highlighted above in red:

1) The key fields (eg, JE_HEADER_ID and JE_LINE_NUM) must be aliased to have a suffix of "_#".  This will indicate to D4E the keys to access a specific record in the table.

2) A dummy column must be created in the Excel output with a header title of Action.  This is used to indicate to D4E which actual records will be updated when the Excel sheet is processed.

Other app components are defined similar to a read-only app - including variable substitution parameters.  Running SIMGL Maint_GL_LINES results in a spreadsheet as below:

TOPIC 5:  Advanced Methods of Variable Substitution.


D4E allows for advanced methods of replacing user variables when contructing cascaded SQLs. These methods can provide the end-user with extra flexibility in defining the range of values they wish to extract. For example, consider the following app definition:
SELECT col1,col2,col3 from dbo.mytable where col3 like '<<Choose col3 in?>>%'

This allows the end-user to give an extract range when giving various parameter values for 'Choose col3 in?', eg;
1. XYZ  ->  col3 like 'XYZ%'   (select anything beginning with XYZ)
2. %XYZ  ->  col3 like '%XYZ'   (select anything containing 'XYZ')
as well as others that may be valid for the underlying DBMS.

An example of an advanced method of variable substitution is below:
SELECT col1,col2,col3 from dbo.mytable where
 col3 like '<<Choose col3 in?>>%'   
   OR  (col3 in (<<Choose col3 in::list?>>)  
          AND (<<Choose col3 in::validlist?>>)  
          AND col3 not in (<<Choose col3 in::notlist?>>)                 
   OR  col3 between '<<Choose col3 in::from?>>' and '<<Choose col3 in::to?>>' )

Instead of a simple WHERE clause, the variable parameter  Choose col3 in?  is reused many times in the datamart SQL definition, such that when the SQL is fully expanded, the WHERE test will allow for more flexible value ranges.  There will be only one parameter prompt presented to the end user at run-time - but, the end-user can now enter the following example values:
1. XYZ    -> will select anything beginning with XYZ
2. %XYZ    -> will select anything containing 'XYZ'
3. ABC,XYZ,list    -> will select anything found in the comma-separated list
4. KKK-MMM    -> select anything between KKK and MMM inclusive

To make coding the app even easier for the D4E developer, there are also D4E short-cuts (special substitution functions and variables) that can be used to provide the other advanced flexibility.

TOPIC 6:  Exporting and importing app definitions.


As shown above, the D4E developer can easily create powerful Excel extract functions (EXAMPLE Extract XYZ) and set these up as easy-to-use menus (@Simple GL) for the end-user.  In a corporate production environment, these program functions are typically found in a secured and shared app program library for the end-users.  If they need to be upgraded, the developer simply creates new app definitions and updates the shared library.  Once the shared library is updated, the functions are immediately available to everyone.

To do this, the developer initially develops and tests in a test environmnent, then exports the new app items into an .xls file and then import the .xls file to the production program library in compliance with change control procedures.   This can be seen from the Excel menu:  Go to Datamart4Excel -> Data Marts -> Settings -> Manage.  For example:

TOPIC 4: Convert normal VBA code into a D4E app to execute with other D4E apps.  


You can write normal VBA code (subroutines and functions) and store them as a D4E app as in the following example.  Instead of calling the VBA, you can call the D4E app and D4E will arrange for Excel to execute the VBA code.  This means that VBA code is not stored in user files as modules, subroutines or functions. Instead, the VBA code is stored securely in an external app store (with other D4E apps), and executed as VBA via D4E menus. The VBA code can be securely updated centrally without involving user files - therefore it is not a UDA anymore!

A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION