Can you add a little more context to your need?
Do you need these to be hosted data sources?
Do you just need to create local tds's with these saved so that others can use them?
Are you just making a single workbook and each one of these queries needs to be its own data connection in that workbook?
As far as I know, each sql file just needs its own workbook so it can be run on an annual basis. It seems likely that I will be the only viewing the resulting data so I can access data and then feed that data into a renewal contract.
Since you're having to create a workbook for each connection....it isn't that much work to just follow the normal workbook creation flow.
1. Start New Workbook.
2. Connect to data
3. Define Custom SQL connection
4. Build workbook
I'm pretty sure you can't use Oracle variables in Tableau custom SQL.
Ahh. What variables must I use?
You just can't use variables. The SQL needs to be standard select statements.
Are you allowed to share your SQL (Possibly anonymize it) so that I can see the structure of what you're trying to connect to?
The file is a Risk Classification Profile showing location count, location premium, policy count, and policy premium for in-force business as of 6/30/2014, segregated by:
Policy count; and
This is the objective, if that helps.
You're trying to call functions with variables in your SQL.
pr.policy_revision_seq = f_in_force_revision(pr.policy_seq,NULL,f_end_of_day(:as_of_date))) l,
You can't do this with Tableau.
You would need to modify your query so that it performs the logic that the function would be doing from within the statement.
If you're not a DBA I would reach out to whoever created this SQL and explain to them your situation.
They might be able to convert this for you.
Alright, Thank you!!
So the query ran upon setting as_of_date to a constant. We (the DBA and myself) attempted setting it to a parameter because we want to be able to input a date value or a sysdate/today value each time we refresh or open up the workbook.
Tableau didn't like that however (setting as_of_date to sysdate or to a parameter), and started collecting the data before prompting us for the date. Are you aware if there exists a way I can work around this?
Im not familiar with a way to change that parameter before you open the workbook. (Besides editing the twb XML in a text editor)
Are you planning on using extracts?
Yes. That is the situation I am describing above. I set the as_of_date to a parameter and it tried running the entire query without prompting me for a value.
The refresh I'm referring to is just opening up the document on an annual basis..In other words, every year, we will access this Tableau workbook and expect it to give us only the previous year's data.