Note how all OLAP information (MDX parameters) are stored including - server name (LOCALHOST), schema name (FoodMart 2000), cube name (HR), dimensions and facts. Filters are used for slice-and-dice operations.
The user-designed Excel report can look like this:
Step 12: Using D4E to JOIN tables from different databases and platforms.
D4E can easily be used to JOIN tables from different databases and platforms, e.g., transactions tables from Oracle EBS General Ledger joined with tables containing product details from IBM DB2 based data warehouse (DWH) tables. To do this, start by creating two separate D4E apps to extract from the source databases into 2 separate Excel tabsheets. Then using Excel ODBC drivers, create a third JOIN SQL to read the 2 Excel result sheets and output a new tabsheet containing the JOIN result set. Finally, the 3 apps can be invoked from one script app added to the My Apps menu. This is equivalent to Linked Tables in MS Access but implemented using modern and easy-to-use Excel menus and options.
This demonstration shows how easy it is to create datamarts. 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.
Step 7: 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.
Step 6: Creating pivot-style Excel reports from OLAP sources.
Apps can also be used to create pivot-style reports from OLAP sources such as SQL Server Analysis Services (SSAS). All MDX-compliant OLAP cubes can be accessed by D4E. To create the app, we specify OLAP source information in the datamart fields as in the following example for the Foodmart HR sample application (MS Analysis Services). The following is a view of an existing OLAP-sourced datamart Foodmart Cube HR. Change Current App to Foodmart Cube HR -> Datamart Tools -> File -> View, to see below:
In cell A18, we nominate the value "6000" for col3, while in cell E1, we nominate the value "BRA" for col4 and the fact variable name SALES. When this report is refreshed (by clicking on the Build icon), the summarised amount of SALES for all col3=6000 and all col4=BRA will be placed in the intersection cell E18. This sheet is recognised by D4E as a report sheet because of the app name EXAMPLE Extract XYZ found in cell A2, as well as the intersection parameters found in row 1 and column A.
App EXAMPLE Extract XYZ can still be used as an extract tool and will return a list of rows in a spreadsheet when executed via the menus, as follows:
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:
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:-
Note that the filter information is specified in the report and will change automatically after each slicing-and-dicing. To slice-and-dice, click on the Filter Local icon and choose from the hierarchical-style filter parameters, as below:
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.
Step 11: 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:
Similar to creating a native Excel pivot table, we specify in the Object Editor Pivot fields how we want to use the database columns in an Excel report. By providing actual values of col3 and col4, the D4E app will return summarised amounts of SALES, in cell locations nominated. As a result, the report designer can have full control of the Excel report in terms of formatting; and can add graphs, charts, pictures, and other widgets. D4E app will supply all the summarised amounts directly from the database in real time.
The user-designed Excel report can look like this:
Step 8: Creating Excel reports from stored procedures.
Apps can also be created by simply nominating stored procedures, as in the following example:.
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.
Step 9: 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.
Step 10: 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:
Step 5: Creating pivot-style Excel reports.
Apps can also be used to create pivot-style reports, not just lists or spreadsheet extracts. These pivot style reports can have charts and graphs. When the pivot-style app is executed, specified cells in the report sheet are automatically populated with summarised data, similar to a pivot table. This is analogous to assigning a GETPIVOT formula to a specified cell. Let us a look at an example:
First we modify our example app as follows: (Click on SQL Code Pivots to expand as below.)
Note that the hierarchical-style of the filter parameters (+ to expand), shows that the data source is an OLAP cube.
Click on Refresh button to complete the slice-and-dice operation and the user-written report will be refreshed with new values:
DATA Robots FOR EXCELLENCE
A DATA DISCOVERY TOOL WITH EXCELLENT MS OFFICE INTEGRATION
DEMONSTRATION - HOW TO CREATE ADVANCED APPS
DATA MART FOR EXCEL
Caratrel Consultants Pty Ltd © All Rights Reserved.
+61 (0) 418 217 114