Your ‘To-Do’ list:
- We’ll use Oracle for this example. In future posts, I’ll discuss other databases (including SQL Server).
- Download and install the ODAC (Oracle Data Access Components) from here.
- Make sure you have PowerShell v3 installed.
Step 1: Set up the link between PowerShell and Oracle
- Copy the attached ‘GetOracleData’ text file and save it in a similar path: Users
- Whenever you want to use the function, just run Import-Module DataAccess, and you’ll have the function available to you in the shell:
- You will want to adjust the variables $conString and $sqlString to suit your specific needs.
- Go ahead and test your $sqlString query in the Get-OracleResultData function. Ideally, you want to look for a table that can tell you if the source data is updated. If so, you can then bring back the necessary column for the Tableau refresh trigger. If the query brings back some data, you’re in luck and can now move to the next step.
NOTE: We’re using the data-adapter portion of this because it only connects to the database to populate the necessary dataset used for the query results. What’s more, it allows us to avoid the permanent (open) connection to the database.
Step 2: Write a script and bring Tabcmd along for the ride
- Copy the ‘OracleData_TableauTabcmd’ file and save to a location you’ll remember (don’t forget to save it as a .ps1 file).
- Adjust the necessary variables, usernames and passwords.
Step 3: Schedule the script and watch the refresh glory happen
- Follow the steps in the ‘powershell_check_job_status’ file to schedule your Tableau extract on your Tableau server.
- For full details on all cool things regarding scheduled jobs, see this.
That’s it! You’ve now got an event-based Tableau extract!