Hi All,
A number of users here at the RA have found that having direct access to the database would greatly facilitate their work. I know that there are security issues involved, but none of us here are Microsoft server experts, so I was wondering if someone could explain why server partitioning for different organisations as described here https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning, coupled with use of asymmetric keys (https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-server-certificates-and-asymmetric-keys?view=sql-server-ver15) would still cause issues.
The main use cases our team has found are:
Directly querying data with python through a SQL connection would greatly streamline workflow for analysis, and might actually be necessary to create self-updating algorithms.
The software development team here use DataGrip (https://www.jetbrains.com/datagrip/) - an IDE that allows access to MySQL, PostrgreSQL and MSSQL in one place. We can now work on all our other databases in one place and it would be a huge help to access Tessitura's MSSQL server in the same way.
Is there any way we could have this access now? Is this a functionality that is being considered for future releases?
Thanks,
Carlo
Carlo Palazzi,
You mention a potential solution. I'm not clear what is actually keeping you from accessing the database over ODBC? Are you a RAMP organization? Part of a consortium?
I know back in the day at BAM we solved the kind of problem you are describing by creating a specific subset of views and giving certain Active Directory accounts access to these views over and ODBC connection. This worked well with MS Excell and other R & Python approaches. (This would only work for a local instance of SQL Server that was part of the Active Directory Domain the clients are using to log in to their workstations.). But this worked very nicely. The DBA created secure data views. And those who were granted rights to the data could access.
I happen to be a RAMP user at this time. Because we do not share an Active Directory and firewall configuration this is tough. I'm working on ways to solve this issue. If you are on RAMP let me know and we can discuss.
--Tom
Hi ,
We are indeed on RAMP. Your approach of creating a subset of views accessible via an ODBC connection sounds very promising. I've seen in the docs that custom (LT_?) tables can be made available through the REST API. I'm guessing this means that the REST API has ODBC capability. Is this functionality available to RAMP users? If so it would be a start for now to run scheduled procedures to update custom tables that can be accessed through the REST API.
You can reference Views LV_[view] via the REST API. ODBC is not available to RAMP users. I've got a current project using the REST API approach. We were thinking or doing an analytic Coffee Special Topic on this on Friday of this week. Noon EST, or 5:00pm your time. Are you interested?
Very interested. I'll see if our tech lead Julian Jans would also be able to make it.
Hi Carlo Palazzi - You can certainly expose local database objects via the REST API. You can do this using endpoints under the /TessituraService/Help/Resources/Custom resource in the REST API.
This is our recommended approach for accessing any database resource, vs. direct database access.
There are endpoints allowing you to retrieve data from local tables, and endpoints allowing you to interact with local stored procedures - those procedures can of course query local views, tables, and perform database operations as needed, and return the resulting data, if any.
Chris
Hi Chris Szalaj, thanks so much, that's really promising. Just to clarify, by local database objects do you mean any tables that have been created in the RAMP-hosted SQL Server by the Royal Academy team? So we couldn't access tables such as T_CUSTOMER through the REST API, but we could access some view LV_T_CUSTOMER that we create?
Carlo Palazzi Many of the standard tables, such as T_CUSTOMER are part of data returned by standard API requests, so you may not need custom work to access them. I encourage you, as you explore using custom work to solve your puzzles, to start by looking at what standard API requests are available for retrieving things like customer data, or groups of customers. That way, as the technology evolves, we can better support you. You can access standard resources via the local data and local procedure API methods, I just recommend that you start by looking at the standard API methods already available to work with them -
Hi Chris, thanks for the response. I'll look into the API functionality - although I fear the best we could do with this would be similar to just doing scheduled data dumps from the server to our local machines - as we couldn't connect DataGrip via an API.
You might look at
https://www.tessituranetwork.com/Passthrough?itemUri=/tlcc/2019/Pres/02_32_DVP_Custom_Resources.pptm starting at Slide 9.