Overview
Are you looking to harness the power of Oracle Analytics Server (OAS) with your MySQL database? You're in the right place! This guide will walk you through the process of connecting OAS to MySQL, allowing you to create stunning visualizations with your data.
Prerequisites
Before we dive in, make sure you have the following:
1.
MySQL server 5.6.24 installed on a Linux machine
2.
3.
BI Developer Client 12.2.4.1.0 for OAS installed on your local machine
4.
MySQL Connector - ODBC (recommended but optional)
5.
Access to Oracle Analytics Server (OAS)
Step-by-Step Guide
Step 1. Set Up MySQL Server and Sample Data
•
Install MySQL server 5.6.24 on your Linux machine.
•
Download and populate the sample employee database from GitHub.
•
Install MySQL Connector - ODBC (recommended).
Step 2. Configure ODBC Data Source on Client Machine
1.
Install BI Developer Client 12.2.4.1.0 for OAS on your local machine.
2.
Configure Data Sources (ODBC) in the client:
•
Open ODBC Data Source Administrator.
•
Add a new User DSN for MySQL.
•
Configure the connection details.
ODBC Data Source Administrator - System DSN
ODBC Data Source Administrator - MySQL Driver Setup
Step 3. Import MySQL Metadata into OAS
1.
Open the Repository (RPD) file from OAS.
2.
Import the sample database metadata from MySQL.
3.
Save the updated RPD on your local machine.
4.
Import Metadata dialog
Connection Pool - selecting data source name for MySQL
Step 4. Configure ODBC in OAS
a. Locate the odbc.ini file in OAS:
/u01/data/domain/fmw/user_projects/domains/bi/config/fmwconfig/bienv/core/odbc.ini
b. Before editing, gather required ODBC setting parameters from:
/u01/app/4.3.0.0.0-20180417212544-0.0.6/fmw/bi/modules/oracle.bi.datadirect.odbc/7.1.6/odbc.ini
c. Edit the odbc.ini file in OAS, adding the MySQL connection details:
Sample odbc.ini configuration for MySQL 1/2
Sample odbc.ini configuration for MySQL 2/2
Step 5. Update Data Model in OAS
1.
In OAS, navigate to the Data Model section.
2.
Replace the existing Data Model with the one saved on your local machine.
3.
Wait for the changes to take effect. You may need to sign out and sign in again for quicker application of changes.
Data Model replacement in OAS
Note: After replacing the Data Model, it may take some time for changes to apply. Logging out and logging back in can help expedite this process.
Step 6. Create Visualizations
Now that the connection is established, you can start building visualizations using your MySQL data source.
1.
Create a new project or open an existing one.
2.
Add data from your MySQL source.
3.
Build various visualizations such as charts, graphs, and tables.
Troubleshooting Tips
•
If you encounter connection issues, double-check your ODBC configuration on both the client and OAS sides.
•
Ensure that the MySQL server is accessible from OAS (check firewall rules and network settings).
•
Verify that you have the necessary permissions to access the MySQL database from OAS.
•
If changes don't reflect immediately after replacing the Data Model, try logging out and logging back in.
Conclusion
By following these steps, you should now have a working connection between Oracle Analytics Server and your MySQL database. This setup allows you to leverage OAS's powerful visualization capabilities with your MySQL data, enabling you to create insightful dashboards and reports.
Remember to keep your data model and connections updated as your MySQL schema evolves. This ensures that you're always working with the most current data in your analyses.
We hope this guide has been helpful in successfully connecting MySQL to OAS. If you have any additional questions or encounter issues, don't hesitate to reach out to Oracle support or consult the OAS community forums.
Happy data analyzing!