Spotfire Ideas Portal
We have many data sources that implement row level security using features such as Oracle’s VPD. This relies on the ability for Spotfire to provide the end user identity whenever running SQL on the database - which is easily achieved using the Open Session commands in Information Designer. We need an equivalent feature for Data Connections - without this it isn’t possible to use Data Connections to interact with these data sources.
Just to add that we're now seeing similar requirements for other types of database, such as Impala. Row level security is commonly implemented using some sort of session initialisation command, particularly where it's not possible to use Kerberos constrained delegation to pass the end user identity directly to the database.
Sorry, I have to correct myself. Tableau has Initial SQL, but there is no Initial SQL in PowerBI or Excel. I was mixing up with Power BI custom SQL to load data.
Power BI and Tableau have Initial SQL since forever. Even Excel has it.
I don‘t want to use or license an additional product for this basic functionality.
Please don‘t wait until you work on more general redesign of data connection layer, but deliver this feature quite soon, because it is so core and not really complex I would say.
Just a thought Holger, but one possible workaround would be to use Spotfire ADS (Advanced Data Services) to help access LSH? It's basically TIBCO's Data Virtualisation product dedicated to Spotfire so should be able to handle the PL/SQL and any other curation / merging of LSH and other data to enable your Spotfire users. It's virtual so acts more like a proxy to the requests in real time rather than replicating the data. A bit more flexible and dynamic than ETL.
I only suggest it as it sounds like this is causing a fair bit of pain and replication so it might be worth your while adding ADS.
Many thanks for taking your time to share your view on this idea Holger. Very much appreciated.
In the Pharma industry Oracle LSH is quite common clinical data repository. As part of the security and access concept of Oracle LSH execution of certain PL/SQL statement is required before data is shown in any table. To enable data exploration, this open session command is required.
We would like to use external Data Connection to enable data exploration use cases, but cannot because open session command is not available.
And Information Designer is not an alternative because it does not support data exploration. It requires quite some overhead and effort to connect to new tables and adapt to data table changes. And also Information Designer is nothing to be used by Business Analysts.
We need open session command for Oracle database. It is really a pity to not have this feature, because we are now forced to copy the complete content from Oracle LSH to another database to be able to use Spotfire then. And this is a high effort because the ETL pipeline needs to be developed and enhanced for each new clinical trial.
Isn't there any change to get open session command at least for Oracle databases? I see that the original entry here is also about Oracle.