DATA Robots FOR EXCELLENCE
A DATA DISCOVERY TOOL WITH EXCELLENT MS OFFICE INTEGRATION
DOWNLOADS AND RESOURCES
CHOOSE A DOWNLOAD OPTION: (By downloading our software, you hereby agree to the conditions of use above.)
- D4E Secure Package (Download, save, then rename file as d4e.msi - before executing.)
- D4E Self-installing File (Register and install automatically.)
(If your anti-virus software prevents installation, please ensure you have the latest anti-virus definitions and try again.)
Try our free demo service package of a reporting tool for Oracle eBusiness Suite Financials, MYOB AccountRight Premier, QuickBooks from Intuit, or Microsoft Dynamics GP 2010. The package includes a Simple GL database that you can use to learn how to navigate menus, extract data, run formatted reports, create new reports and create your own data marts for new reports.
Default installation will copy files to XLSTART folder so D4E addin will automatically execute when Excel is started. Requires Excel 2007 or later, and relevant ERP package (Oracle eBS, MYOB, Quickbooks, or MS Dynamics GP) with ODBC driver installed. This package is a working demonstration of a reporting tool and if installed completely, can extract GL transactions and balances from your current General Ledger. Make sure you have a working ODBC connection to your Oracle Financials (or MYOB or QuickBooks or MS Dynamics GP) before running the demo. For more information, please read notes below on How to Use D4E with Oracle eBS Financials, MYOB, QuickBooks, or MS Dynamics GP.
To install Oracle ODBC driver, download the Oracle Database Client Release package from Oracle website (Oracle Database Software Downloads).
The Oracle ODBC client is NOT part of Datamart4Excel, but is required. The package contains other Oracle clients, just select only the option for ODBC client drivers. Then copy the TNSNAMES.ORA file to the c:\oracle\...\network\admin folder. You may need to register for free to get into the Oracle download site.
To install MYOB ODBC driver, download the AccountRight Premier trial version from the MYOB website for AccountRight Premier.
To install Quickbooks ODBC driver, download the QODBC.com ODBC driver.
If you do not have any ERP/GL package, but would still like to see Data Mart for Excel (D4E) in action, we recommend for you to install MYOB AccountRight Premier, as they provide a complete free trial edition with ERP/GL package and ODBC drivers. Download MYOB AccountRight Premier free trial edition here.
Data Mart for Excel (D4E) can be used on many ERPs and databases. Below are various examples of Data Mart for Excel's usefulness. Peruse through them and see if you find something applicable for your situation.
How to Use D4E with Oracle eBS Financials
To automatically read Oracle eBS Financials data, D4E needs to know the internally-used key ids, aka Flex Keys, of each account segment (up to 10). At the same time, this information must be provided in a generic manner so that D4E can be used to access other eBS instances. D4E expects to find this information by sorting and reading the DESCRIPTION field of the Flex Value table APPLSYS.FND_FLEX_VALUE_SETS. There are several ways to implement this. One easy way is to create special descriptions of each segment. For example, follow the steps below:
1. Log on to eBS, with supervisor credentials, to update the segment flex value descriptions. For each segment, start the description text with:
"XYZ01 - rest of Segment 1 description", thru
"XYZ10 - rest of Segment 10 description".
where XYZ is a unique short string not used in other descriptions. D4E will try to sort the relevant flex values table based on the descriptions, and automatically, the 10 segments will be easily identified.
2. Indicate to D4E, your chosen prefix "XYZ" to identify segment records. Open the text file "prefix.txt" in the main library folder (default is "C:\d4e\") and put the string "XYZ" in line 1 (without quotes.)
That's it. D4E should understand the segmentation, and you can try to extract records using the Oracle GL menus. Go to option:
Apps -> Oracle GL - > 1. GL Account Balances -> Enter Oracle ODBC as per defined in the ODBC Admin -> LOGON USERID and PASSWORD as per user -> Click on the Choices (3 dots) to get valid prompt values.
How to Use the Simple GL Database in the Trial Version Package
Download the Simple GL documentation here.
Download and install the trial version and use the Simple GL menu options that reads a sample GL database table stored in MS Access (.mdb). Goto Excel menu option:
Apps -> Simple GL -> Show GL Transactions -> Enter date parameters and click OK
This will create simple reports and also illustrate how to navigate the D4E menus and options.
How to Use D4E with MYOB Accounting Package - AccountRight Premier
MYOB provides a free trial for the AccountRight Premier. Download the free trial here.
Download and install MYOB AccountRight Premier trial to your PC. This will install the ODBC driver as well as the complete database file ClearWtr.MYO. Create a System DSN (eg, MYOB_DSN) that points to the ClearWtr.MYO file. Use userid=Administrator and password=blanks to access the file as read-only. Install the D4E trial version and go to the Excel menu option:
Apps-> Analysis Tools for MYOB -> Show Customer Sales -> Enter ODBC as MYOB_DSN -> LOGON USERID as Administrator -> LOGON PASSWORD as blanks.
Click OK and a report of Customers will be produced. Use other parameters to limit the contents of the report.
How to Use D4E with QuickBooks Accounting Package
Intuit Inc provides a free edition of the QuickBooks Accounting Package - QuickBooks Simple Start Free Edition. Download the free edition here.
QODBC.com provides an evaluation version of their ODBC driver to read QuickBooks Accounting Package. Download QODBC evaluation version here.
Download and install QuickBooks Simple Start Free Edition to your PC. This will install the company files for QuickBooks. Open QuickBooks application for the Sample company. If you have an existing QuickBooks company, open your own company instead. Download and install QODBC ODBC driver to your PC. This will install a System DSN that can read the QuickBooks sample company. Install the D4E trial version and go to the Excel menu option:
Apps -> Analysis Tools for QBOOKS -> Customer Sales -> For USERID ODBC, enter QuickBooks Data -> Click OK to extract data.
A report of Customers Sales will be produced from the sample company. Use other parameters to limit the contents of the report. To extract details for another company, close QuickBooks and open another company. D4E and the QODBC driver will extract details from the currently opened company only.
How to Use D4E with Microsoft Dynamics GP 2010
The report suite created in the D4E trial version was based on schema definitions of MS Dynamics GP 2010 as found here.
Microsoft Dynamics GP uses MS SQL as its database, so the ODBC drivers used are already installed. Create a System DSN with Microsoft Dynamics GP as the default schema. Install the D4E trial version and go to the Excel menu option:
Apps -> Dynamics GP Show Tables -> Financial -> Account Summary History -> Acct Summary History -> Enter the correct System DSN and userid/password -> Click OK to extract data.
A report will then be produced from the SQL database.The menu options contains other report extracts sutiable for the General Ledger module. Additional analysis reports can be created easily.
How to Use D4E with Salesforce.com
You can register for a free trial period to use Salesforce.com from here. You can also download a free trial ediltion of an ODBC driver for Salesforce.com such as Progress DataDirect Connect XE from here.
You can access your Salesforce.com databases just like any SQL92-compliant database, by installing an ODBC driver such as Progress Data Direct Connect XE. Register for Salesforce.com to get your userid and password. Then install DataDirect Connect XE ODBC driver for Windows which presents the Salesforce.com tables similar to other SQL-92 DBMS such as MySQL.
Install the D4E trial version and then go to the Excel menu option:
Apps -> System Builder -> MySQL DM Builder -> Enter the Salesforce.com ODBC DSN name, logon userid, password -> Select your schema and tables to generate your datamarts in Excel.
With datamarts defined for Salesforce.com in your Excel menus, you can generate Excel reports directly from the cloud!
How to Use D4E with SAP Netweaver BW
This SAP whitepaper explains how to use D4E to connect to SAP Netweaver BW using Excel PivotTables. Download the SAP whitepaper here.
Datamart4Excel is very good in creating/refreshing PivotTables. Using MDX, PivotTables can be pointed to MDX-compliant data sources such as MS Analysis Services and SAP Netweaver BW. Setting up the MDX connection is as simple as entering your SAP userid and password. Once the connection is established, Datamart4Excel can provide the Excel menus and enterprise reporting for any SAP information provided in the SAP Netweaver BW portal. Using SAP ABAP and other custom SAP programs, SAP Netweaver BW can provide a portal to SAP Financials and other SAP ERP components. Consequently, Datamart4Excel will extend this portal access onto Excel.
How to Use D4E with the System Builders
System Builders can be found in the trial version menus. There is a System Builder for each data base platform (DBMS). They are a quick way to generate Data Mart viewers and implement as Excel menus. All you need is to nominate each of the corresponding table (and table owner) of your ERP or GL package. Basically, this means that if there are 10 tables in your schema that you nominate (by giving a pattern name), then there will be 10 new Excel menu items created in your D4E menu - each menu item will extract data from each table.
For example, if the table names of your General Ledger package all begin with "GL", and they are stored in the MS SQL Server DBMS; then you could go to the System Builder for SQL Server and generate your new Data Mart viewers:
1. Goto Apps -> System Builder -> MS SQL Server DM Builder -> enter ODBC/userid/password -> enter MSSQLTABLE as GL -> OK
This will generate a parameter sheet where each row corresponds to details of the final Data Mart viewer. Save this spreadsheet as c:\DMart.xls for importing into the current Data Mart program library, as follows:
2. In the Excel ribbon, goto Maintain Settings (for Excel 2003, goto dmTools -> d4e Options), then -> Manage -> Import to Library -> OK
This will upload the file and use it to add menus into Excel.
3. Restart Excel and access the newly created menus.
4. Repeat procedure for every group of tables from your ERP, GL, or any application databases that you would like to incorporate as menus in Excel.
Download complete documentation for System Builder.
How to Use D4E to view Joined Tables with the System Builders
System Builder can also create viewers for joined tables. An example of joined tables is a list of account transactions (Transactions table) with account descriptions (Account table) based on the account number. Any related table can be joined in any ODBC-compliant DBMS. For example to join tables found in the MYSQL system:-
1. Goto Apps -> System Builder -> Join Builder -> MySQL DM Join Builder -> Enter ODBC/logon user/logon password/schema ->
2. Enter table names to join (use the choices to see available tables from the selected schema)
3. Enter WHERE conditions, eg A=B AND C=D etc (use the choices to select the column items one-by-one
4. Enter column fields to display in the result (use the choices to select each display column).
Use the normal steps above to create an Excel menu item to execute the newly created viewer for joined tables. Download complete documentation for System Builder.
How to Use D4E with MS SQL Stored Procedures
Datamart4Excel is capable of handling MS SQL stored procedures and turning them into virtual data marts. It does this by reading the stored procedure source code and identifying the parameter variables. It presents these to the Excel user as data mart parameters. All the features of Datamart4Excel then become available in extracting data via the stored procedure. Therefore, existing sophisticated stored procedures can be used which would otherwise be difficult or impossible to create within Datamart4Excel. Also, existing MS SQL programming resources can be employed to assist in customising Datamart4Excel applications. There is no additional training required from MS SQL programmers, except to ensure that the stored procedure is correctly written and that the "@variable" names are presented at the top of the source code without comments, as in the following example:
CREATE PROCEDURE spu_Report_Large_Loss_Status
... no comments in the CREATE PROCEDURE clause
... rest of procedure and/or comments
How to Use D4E with Hadoop on Azure (Big Data)
To use Hadoop on Azure with Excel, follow the instructions here to set up Excel ODBC before running D4E. You can find more information on Hadoop on Azure (Microsoft Azure)
Datamart4Excel can connect to Hadoop on Azure as easy as any Excel connections via ODBC. Follow the instructions above to connect Excel to Hadoop on Azure. After successfully connecting this way, install D4E trial version and use the SQL Server menu options in System Builder as follows:
Goto Apps -> System Builder -> MS SQL Server DM Builder -> Enter ODBC as MyHiveData -> Enter your Azure Logon Userid/Password -> Click on Choices for MSSQLTABLE to see available tables to extract -> Click OK to generate your datamarts in Excel.
In a production scenario, you can build an Excel menu/option that extracts information from Hadoop On Azure, using any number of parameters with flexible ranges and filter lists.
+61 (0) 418 217 114
DATA MART FOR EXCEL
Caratrel Consultants Pty Ltd © All Rights Reserved.