A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION

D4E is installed as a secure COM Add-in (.dll) environment hosted in MS Excel with Ribbon functions, where users can execute previously converted end-user computing (EUC) tools as an integrated computing system. All compiled objects created under D4E are managed once only under a single enterprise control (e.g., single LAN id).  As a development environment, D4E extends SQL's functional programming concepts by implementing higher-order functions.

By using industry standard ODBC and Python Libraries, D4E users and report developers can access complete databases and warehouses without going outside Excel. Users who will benefit greatly from D4E can be accountants, business analysts, data analysts, testers, business users, sales managers, database administrators, report writers and general knowledge workers.


By being fully hosted in Excel, the users do not see separate apps with logons, no "export to Excel", no intermediate steps. By delivering results directly to Excel, high-fidelity customized reports can be generated every month for top management from Oracle eBusiness Suite, SAP  BW or other General Ledgers.

D4E is a specialist form of Business Intelligence as it does not actually require (but can work with) the traditional BI components of Extract-Transform-Load (ETL) and Data Warehouse schemas, given the existence of ERP's and application databases. Instead, it assumes that the ERP or application database is the primary real-time source of truth and simply provides a data mart facility to directly access the ERP. For Finance and Accounting, this specialist form is also known as Accounting Intelligence. Below are some user types that can benefit from Data Mart for Excel services.

 

Sarbanes-Oxley Act (2002) Auditors  |  Accountants  |  Chief Financial Officers

Digital Data Analytics

Machine Learning  |  RPA Solutions  |  Data Managers / Data Owners

Business Users  |  Analysts  |  Sales Managers


(D4E will assemble the user menus based on the simple rules followed by the SQL developer.)

Business user submits values through prompts:

Two-Tier Collaboration Methodology for SQL Developers and Business Users:

1. Role of the SQL Developer:


SQL Developers are responsible for building and optimizing SQL queries. They work directly with the database, writing complex queries and optimizing them for performance. SQL developers create parameterized SQL queries or pre-defined templates that business users can easily interact with. They use the D4E App Store to define SQL commands that can be executed directly from the Excel interface, ensuring that the right data is pulled based on user needs.

2. Role of the Business User:


Business Users (non-technical) interact with the D4E app without needing to understand SQL. Business users can input parameters into predefined templates, which are then translated into valid SQL queries by the app. They use simple Excel forms and tools to query data and retrieve business-relevant information (e.g., sales reports, financial data) without needing to write SQL queries themselves.

3. How D4E Facilitates Collaboration:


Parameterized SQL Execution:   The D4E app allows business users to use easy-to-understand prompts (e.g., input fields for dates, customer names) to fill in the parameters for the SQL queries created by SQL developers.
SQL Templates:   SQL developers prepare templates with placeholders for business users. These templates are then populated with user-provided values when executing the SQL command.
Centralized Query Repository:   The app manages a repository of SQL queries, which can be executed directly from the Excel interface by both SQL developers and business users.
Real-Time Data Access:   Business users can interact with live data directly within Excel, retrieving real-time reports based on the SQL queries that developers have set up.

4. Workflow Example:

SQL Developer:   Creates a parameterized SQL query to fetch financial data for different regions and stores it as a reusable query in the D4E app.

Below, the developer writes SQL codes and stores them in a column on a spreadsheet:

Many enterprises today discourage Excel VBA because of security and maintenance concerns. Fortunately, D4E provides a way to convert your current and future user-developed applications (including Excel VBA, MS Access, SQL) into a security-hardened change-controlled apps repository with each app able to integrate with MS 365 ribbons and menus. As an enterprise-class data discovery tool, it can be used by anyone in the organization who may have a need for an easy-to-use reporting tool and self-service Business Intelligence. The D4E enterprise reporting tool can access enterprise data while inter-operating with MS 365 menus only and without programming assistance.

Replace all Excel/Access UDA's, SQL's, PowerBI M code, and more with D4E Apps.  Apps are created from SQL code by developers (EDIT Mode); then stored centrally within the secure enterprise environment (D4E Apps Store).  End-users (in RUN Mode) then access the D4E apps via the menus to produce reports, run dashboards, operate MIS reporting processes, and submit background/ batch processes. All outputs are instantly delivered in MS 365 through Excel integration.  

PRODUCT SERVICES

Note, the developer follows some simple rules in sections - 

  1. TITLE SECTION - The first row is a title in comments (YELLOW), 
  2. DRIVER SECTION - The next comments (enclosed in /* Driver in row 2 and */ in 13) are driver connection strings (PINK), and 
  3. CODE SECTION - The rest of the column are the working SQL code (BLUE).

By following these simple rules, every SQL can be stored in and referred to by the sheet name (and column char) - GL (C).  Or, in some cases can also be referred to by the sheet name (and column title) - GL (GL Bals SYD CY).

Note how the developer is able to use placeholder parameters (<<Choose Branch?>>) in the SQL code:
SELECT * FROM data WHERE Year = 2025
 and Branch = '<<Choose Branch?>>'
At runtime, the user will be prompted for the value of the placeholder. If the user submits "Sydney" then the final SQL will be:
SELECT * FROM data WHERE Year = 2025
 and Branch = 'Sydney'

Business User:   Uses the app's Excel interface, entering parameters like the region and date range to retrieve the data. The app converts these inputs into an SQL query and fetches the relevant data.

Without worrying about SQL skills, the business user clicks on the ribbon menu (SQL Versions) to find the option to extract data:

And receives reports instantly in Excel:

5. Benefits of Two-Tier Collaboration:

Efficiency:   Reduces the need for business users to involve developers for every query.
Transparency:   Business users can easily access the data they need without the complexity of writing SQL queries.
Security:   Developers can restrict the types of queries business users can run, ensuring that only authorized queries are executed.
Scalability:   The app allows complex queries and templates to be defined, supporting a variety of use cases.

Example of Complex Queries: The SQL developer could use this simple placeholder parameter that converts to complex WHERE clauses:


SELECT * FROM data WHERE Year = 2025
and  <<Choose Company$in$Company?>> 
and  <<Choose Branch$in$Branch?>> 
and  <<Choose Account$in$Account?>> 
and  <<Choose Premium_Class$in$Premium_Class?>> 
and  <<Choose RI_CC$in$RI_CC?>> 
and  <<Choose Source_Employee$in$Source_Employee?>> 
and  <<Choose UW_Year$in$UW_Year?>> 
and  <<Choose Intercompany$in$Intercompany?>> 

the business user will be prompted for parameters as follows:

D4E saves the SQL developer from having to code complex SQL and yet gives the business user a powerful self-service discovery tool.



From Complex to ultimate Simplification for the Business User with macro script


A business consultant can simplify the data extract process for the Finance end-user by using macro scripts. For example, the consultant can send the following email that contains a script:

Dear Finance User,
(Below steps assume you have installed the D4E Addin with Taskpane.)
To get your monthly report please paste the following script in the D4E macro box and press Submit.

addsqlfile D:\d4e20\Sample_SQL.xlsx
setparam "Choose Company$in$Company"=10-30
setparam "Choose Branch$in$Branch"=Sydney,Perth
setparam "Choose Account$in$Account"=1%,2%
setparam "Choose Premium_Class$in$Premium_Class"=
setparam "Choose RI_CC$in$RI_CC"=
setparam "Choose Source_Employee$in$Source_Employee"=
setparam "Choose UW_Year$in$UW_Year"=
setparam "Choose Intercompany$in$Intercompany"=
silent_mode ON
runsql GL A
clearstore

For any issues please call support.
Regards,
Support Team



The end-user pastes the message into the macro box and clicks Submit. (Note that message includes parameters for placeholders.)

TERMS OF SERVICE


Effective Date: March 2025
Thank you for choosing Data Mart for Excel (D4E). By accessing or using the D4E software, you agree to comply with and be bound by the following Terms of Service, which govern your use of the product. Please read these Terms carefully before proceeding.
1. Acceptance of Terms
By using or purchasing the D4E software, you agree to be bound by these Terms and Conditions and any other policies or documents incorporated by reference.
2. License Grant
Upon purchase of a valid license for D4E, you are granted a non-exclusive, non-transferable license to use the software for personal or enterprise use, subject to the restrictions set forth herein.
3. Use of the Software
You may use D4E for personal, business, or educational purposes. You agree not to copy, modify, distribute, or sell the software or any part of it without explicit permission.
4. Refund Policy
We offer a 30-day money-back guarantee. If you're not satisfied with the software for any reason, you may request a refund within 30 days of purchase. To request a refund, please contact our support team at [webmaster01@caratrel.com]. Refunds will be issued to the original payment method. After 30 days, no refunds will be issued.
5. Privacy Policy
Your privacy is important to us. By using D4E, you agree to the collection and use of information in accordance with our Privacy Policy, which can be accessed at www.datamart4excel.com. We collect personal information (such as email address) for account creation, subscription management, and communication purposes. We do not share, sell, or distribute your personal information without your consent, except as required by law.
6. Subscription & License Key
For personal users, a valid license key will be issued upon purchase. For enterprise customers, an enterprise license key will be provided. The software will check for the validity of the license periodically.
7. Updates and Support
D4E will provide updates and support during the period of your subscription or license. You can contact our support team at [webmaster01@caratrel.com] for assistance.
8. Prohibited Uses
You agree not to use D4E for any illegal, harmful, or unauthorized purpose, including, but not limited to: - Using the software in violation of any applicable laws or regulations. - Attempting to reverse engineer, decompile, or disassemble the software. - Distributing cracked versions of the software.
9. Termination
We may terminate your access to the software if you violate these Terms. Upon termination, you must cease all use of the software.
10. Limitation of Liability
D4E shall not be liable for any indirect, incidental, special, or consequential damages arising out of your use of the software.
11. Governing Law
These Terms are governed by the laws of the jurisdiction in which you reside, without regard to conflict of law principles.
12. Changes to Terms
We reserve the right to update or modify these Terms at any time. Any changes will be reflected on this page, and the effective date will be updated accordingly.
13. Contact Information
For any questions regarding these Terms, Privacy Policy, or Refund Policy, please contact us at:
Email: [webmaster01@caratrel.com]
Website: www.datamart4excel.com

Privacy Policy
Your privacy is important to us. This Privacy Policy outlines how we collect, use, and protect your personal information when you use D4E.
1. Information Collection
We collect personal information such as your email address when you register for an account or make a purchase. We may also collect non-personal information related to your use of the software, such as usage statistics.
2. Use of Information
We use the information we collect to: - Provide and improve our software. - Send you updates and promotional offers related to D4E. - Respond to your inquiries or support requests.
3. Information Sharing
We do not share your personal information with third parties, except as required by law or with your consent.
4. Security
We take reasonable precautions to protect your personal information from unauthorized access, alteration, or destruction.
5. Cookies
We use cookies to improve user experience and for analytics purposes. By using D4E, you consent to the use of cookies.
6. Changes to Privacy Policy
We may update this Privacy Policy from time to time. Any changes will be posted on our website.
Type your paragraph here.

Explanation of the commands:

addsqlfile - opens an SQL source file and adds SQLs to the internal D4E store (addin),

setparam - provides the parameters with values for the specific Business User so that he does not have to enter them,

silent_mode ON - tells D4E to use the provided parameters instead of prompting the user,

runsql GL A - runs the SQL code and return the results in Excel,

clearstore - clears the internal D4E store (addin).



After clicking on Submit, the Business User instantly gets the report in Excel:

and the final SQL will be smartly generated as:

SELECT * FROM data WHERE Year = 2025
and  ((Company >= '10' AND Company <= '30'))
and  (Branch IN ('Sydney','Perth'))
and  (Account LIKE '1%' OR Account LIKE '2%')
and  (1=1)
and  (1=1)
and  (1=1)
and  (1=1)
and  (1=1)

Did you know with D4E, you can...