E9.1 Connection between Excel/Power BI to JDE

[leech64]

Hello!
How can you make a connection between Excel/PowerBI and JDE?
I am trying to query data from JDE so I don't have to keep logging in, running a report, and finally exporting it to my desktop.

Thanks in advance!

 

[Larry_Jones]

Your connection would be to the JDE Database (Oracle, SQL Server, ...) not directly to JDE.

 

[glen7x]

You could try invoking ReST calls from Excel to the AIS.

 

[Rauf]

Your connection would be to the JDE Database (Oracle, SQL Server, ...) not directly to JDE.

What is the usual procedure to convert the JDE specific data formats (like Julian date, numeric data decimal places etc) to Power BI formats? We will manage this is JDE DB side or in PowerBI side ?

 

[alfredorz]

You could connect excel/PowerBI to JDE database, rest services API's orchestrator, or export excel from JDE and embeb.

 

[harishvarum]

Connecting Excel or Power BI to JD Edwards (JDE) for seamless data querying can be achieved through several methods. Here are some common approaches:

Using ODBC Connection​

1. Set Up ODBC Driver:
   • Install the appropriate ODBC driver for JD Edwards on your machine. This driver will act as a bridge between JDE and your data querying tool.
2. Configure ODBC Data Source:
   • Go to Control Panel > Administrative Tools > Data Sources (ODBC).
   • Add a new data source and select the JDE ODBC driver.
   • Configure the connection with the required JDE credentials and connection details.
3. Connect Excel to ODBC:
   • Open Excel and go to Data > Get Data > From Other Sources > From ODBC.
   • Select your configured ODBC data source and establish the connection.
   • Import the data you need and set up your queries.
4. Connect Power BI to ODBC:
   • Open Power BI Desktop and go to Home > Get Data > More... > ODBC.
   • Select your configured ODBC data source and establish the connection.
   • Load the data into Power BI for analysis and visualization.

Using APIs​

1. Set Up JDE API Access:
   • Ensure that JD Edwards is set up to allow API access. You may need assistance from your IT or JDE admin to configure API access and obtain API keys.
2. Use Power Query in Excel:
   • Open Excel and go to Data > Get Data > From Other Sources > From Web.
   • Enter the JDE API endpoint URL and any necessary authentication details.
   • Transform and load the data as needed.
3. Use Power BI API Connector:
   • Open Power BI Desktop and go to Home > Get Data > Web.
   • Enter the JDE API endpoint URL and provide any required authentication.
   • Load the data and use Power Query Editor to shape it according to your requirements.

Using Third-Party Connectors​

1. Install Third-Party Connector:
   • There are third-party connectors available that simplify the connection between JD Edwards and Excel/Power BI. Examples include CData, KingswaySoft, and others.
   • Install and configure the connector following the provider’s documentation.
2. Connect Excel/Power BI:
   • Use the third-party connector to link Excel or Power BI directly to JD Edwards.
   • Follow the specific steps provided by the connector documentation to set up your data queries and import data seamlessly.

Automating Data Export​

1. Scheduled Data Export from JDE:
   • Use JD Edwards' built-in scheduling tools to automate report generation and export.
   • Configure the system to save the reports to a shared location or a database.
2. Automate Data Import in Excel/Power BI:
   • Set up Excel or Power BI to automatically pull data from the shared location or database where JDE exports reports.
   • Use Power Query to set up scheduled refreshes to ensure data is up-to-date.

By following these methods, you can create a more efficient workflow that minimizes the need to manually log in and export reports from JD Edwards, integrating your data directly into Excel or Power BI for analysis and reporting.