Oracle Analytics Cloud and Server
OAC Dataset feeding back into an Oracle table (Tableau access workaround)
Summary:
OAC Dataset feeding back into an Oracle table (Tableau access workaround)
Content (required):
Much of our company is heavily vested in Tableau. We are in the process of retiring a SQL Server data warehouse and rebuilding in OADW. However, our "system of record" logic and our security reside in the RPD. Since Tableau doesn't connect directly to an OAC PRD Presentation layer, Tableau can, however, connect to an Oracle table in an Oracle database.
If we create a custom Dataset, add it to a Dataflow to be scheduled to run on an iterative basis, is there an automated way to grab the OAC Dataset and push it to a custom table in OADW? As there is a way for Tableau to connect to OADW, this would solve our problem.
Version (include the version you are using, if applicable):
6.3
Code Snippet (add any code snippets that support your topic, if applicable):
https://www.oracle.com/a/ocom/docs/database/adw-connection-instructions-tableau-desktop.pdf
Answers
-
Tableau can connect via JDBC and the OAC RPD can be accessed via JDBC Connection
-
Hi Wayne,
Thanks for the quick response. I found the YouTube video you linked to regarding this on the exact same question, but this doesn't explain how Tableau connects directly do it. I only see a demo for a SQL client tool.
-
Can Tableau connect to data sources via JDBC?
If so then create the JDBC Connection to the Presentation layer as the documentation shows in the link shows.
-
Aside from Wayne's point - as part of the Dataflow 'Save Data' object - there is an option to save to a database table...
-
Additionally, if you're interested in doing a full migration of your SQL Server environment to Oracle Autonomous Database, you should consider leveraging Relational Junction.
-
HI Joshua,
I was just trying the Dataflow to a Database Connection. I am receiving an error of invalid table. I am assuming a table must be created in OADW first, as this method does not create the table for you? Have you done this before? Just wondering what the change management steps are (i.e. - Adding new fields to the table, etc).
Thanks,
Rob
-
It should create the table on the fly.
Does the ID have correct permissions.
-
Hi Wayne,
I will check. Thanks for the tip. Will post back with my results.
-
Hello again,
I successfully was able to create the table on the fly in OADW. However, Tableau does not have a connector directly to OADW tables. Our OADW connection credentials utilize the Wallet file. Tableau does not recognize a wallet file. Any suggestions here?
-
Would need more insight to the error message you're receiving - chances are you need to configure your TNS_ADMIN environment variable.
-
An error occurred while communicating with the data source
Bad Connection: Tableau could not connect to the data source.
Error Code: 37CE01A3
No details available.
I copied the Server, Service and Port names from the TNSNAMES file located in the Wallet file.
-
TNS_ADMIN Environment Variable has been set properly now. I have tried every combination I can of server names, services, and ports from the TNSNAMES to try to connect to Tableau. This is the Tableau Connection to Oracle screen. Can anyone let me know if you have successfully connected your Tableau Desktop to OADW Cloud? And what should I be entering in each field here? Thanks in advance.
-
Do you have the OCI Instant Client installed?
-
Yes, it is installed. What are we supposed to do next?
-
Here are our results:
Also added optional service name and port:
-
Try to follow the steps outlined here: https://www.oracle.com/a/ocom/docs/database/adw-connection-instructions-tableau-desktop.pdf
Otherwise, you can try to open an SR- or hire a private contractor to help get you started.
-
Change your server to the TNS name you want to use
-
Hi Joshua,
Thanks for all your help. Looks like we will creating an SR. Tried _high, _medium, and _low TNS name entries, all of which are located in the TNSNAMES file.
-
Last thing to check - did you add you Wallet contents to the OCI Client network/admin folder?
-
Also - check that the database user you are signing in with has 'Resource' and 'Connect' privileges
-
We were able to get this worked out a few months ago. I will post our document here so others can be helped.
