Clarity

Here in Australia, we use Juniper Network Connect to VPN into CA's network which then allows access to the Clarity database.  This allowed us to use database query tools like Toad and Oracle SQL Developer.

We have now been informed that VPN access will no longer be provided, and the only provided access for the SaaS environment is via CA JDBC driver and JasperSoft.

How our other organisations accessing the Clarity Database in a SaaS environment?  What tools are you using?

We have the same challenge. We have been informed that when our current access expires in a few weeks, we will no longer be able to get it extended. From our pow this is a huge problem.

I know we shouldn't promote third party tools. but can't think of a better solution if they won't let you connect directly (other than go back to on-premise!)

You could consider the XOG and Query bridge from IT-ROI.

CA PPM Software & Tools - XOG and Query Client 

http://kb.itroisolutions.com/pages/viewpage.action?pageId=2393135#USINGTHESQLTOOL►-runningawrittensqlquery 

Obviously you use this third party tool at your own risk

I think that that ^ works by creating NSQL queries on-the-fly (at least the documentation suggests so) - so in essence thats just the same as using studio to create your required query and then running it via the Query API.

(and thats fine - the front-end on the tool makes it all a much more "painless")

We can also paste SQL into a lookup and preview it ; useful for quick SQLs that need running.

That's right, it basically (ab)uses the lookup trick. 

I'm not sure on the reason this was removed, but I can bet it's now very painful for the developers. I can't imagine life without the "explain plan" to ensure we have optimised queries.

I would imagine that its kinda "strategic" ; because they want the SAAS to be "real-SAAS" - i.e. you are unaware of the backend at all and you only deal with the software. VPN-ing into the platform isn't very SAAS-like after all!

The direction the product is moving in seems to be less-configurable too, so the future may well not have such a need for the bespoke query building etc that we are used to - don't think its anywhere near there yet though.

( not defending this really though ; as a techie I'd be lost if I could not see the database.)

DISCLAIMER : all wild personal conjecture on my behalf, I don't know anything! 

I don't think the "lookup trick" will work when developing against the DWH, since we can't access the NSA on SaaS systems and see the name of the DBLINK.

And anyway, it is not really good enough as a developer tool.

It is very very unfortunate that we seem to lose this ability.

^ couldn't you select that from CMN_CONFIG?

But I agree - "hack" workarounds are no substitute for proper "developer" access.

Thanks for the link but not what I'm after.

Any other suggestions?  My VPN account is going to expire soon, and this will significantly reduce my ability to support users in Clarity, and develop portlets/Gel scripts as I can now cut/paste SQL to quickly verify syntax and results returned, with query access to the database.

The CA JDBC Adaptor is the recommended connection that is currently available and it should be used when developing report queries please see the documentation for installation and functional details. It will allow you to access the DB without the VPN connection. This is not as flexible as using a query editor but will allow for reporting queries to be generated. 

The VPN connection is being deprecated for security and stability reasons. It was originally made part of the service to support the use of Business Objects tools which have now been replaced by Jasper. 

Note it is not recommended that the DBLINK be used in any report but it is exposed via the new health report in 15.2 

The new health report is ported back to 14.4 patch 7 onwards and even on 15.1 patch 4 also. And we also have some out of box reports where we use DB link you can use that as reference point to build your reports 

The JDBC connection is a workaround, not a proper solution. It doesn't give the same feedback and debugging opportunities. It takes forever to develop complex queries without a proper DB tool.

Because access rights are not mapped 1:1 between Clarity and Jasper DWH, the DBLINK is the only way we can implement the same access rights to data in reports as we have implemented in Clarity.

Developing queries isn't just required for reporting. It's used for GEL scripts and portlets too. So giving users a workaround that is only accessible through a complex, hard-to-understand reporting tool is not really helpful. Jaspersoft + the removal of VPN access is why we are still on version 14.3 and aren't planning to move to 15.x anytime soon.

It's interesting to me that CA continues to push Jaspersoft when all the new UI releases I have seen so far provide no information at all about how this reporting tool will be incorporated into the interface. (It hasn't fully been incorporated into the classic interface either, for that matter. Not to the extent that BO reports were.) I'm questioning whether it's really around for the long haul.

A colleague shared this with me and while i am still trying to get used to this tool, you could try SQuirrel SQL Client. This gives access to not only PPM but the DWH as well. 

http://squirrel-sql.sourceforge.net/ 

Message me if set up help is needed. Took me some time to get it all set up, but runs great. 

Thanks, 

Keri

Thanks for sharing, Keri. I'm not quite sure, though, how to understand this tool. Does it somehow allow access to the database without using VPN?

^ I would assume its using the 'CA JDBC Adaptor' to connect and then providing a familiar SQL-navigator-type environment.

yes Amy, you just use the ca adapter and oralce java files like you do when you are attaching to jaspersoft. 

ie, ca_jdbc_adapter_6.2.1.3.jar

ojdbc6-11.2.0.4.jar

I also found SQL Workbench, which I like a little better because it functions more like SQL Developer. Find it here SQL Workbench/J -  Home 

I do find it easier to switch from prod to dev environments and between ppm db and dwh db.

One little trick they have is to export directly to excel, use this code:

WbExport -type=xlsx
         -file='C:\Users\taylk128\Downloads\SQL Workbench Export\export.xlsx'
         -delimiter=','
         -decimal=','
         -dateFormat='yyyy-MM-dd';

Please note that the adapter is only designed and supported to connect Jasper Studio to your PPM SaaS systems. There is a known issue in using the adaptor in this manner in that connections are not closed properly by query tools. This eventually causes all connections to be consumed and results in a Jasper not being able to connect resulting in an interruption to reporting functionality until Jasper services are restart CA is working to resolve this issue and, until it is, using this method to connect represents a stability risk to Jasper reporting. Please avoid using this connection method. 

CA Support just this week sorted out our JasperSoft database connection and I have got Squirrel SQL working with the CA JDBC drivers.  Looks like it is back to the drawing board, if the CA JDBC drivers will cause stability issues with Jaspersoft reporting.

Any other suggestions?  Are there other JDBC drivers that could be used?

Sharing some new feature development in dashboards, reporting, and analytics:

SaaS customers can access the data warehouse and develop dashboards and reports using OData REST endpoints. A Hybrid Data Pipeline (HDP) server in the CA PPM SaaS environment connects to the data warehouse and exposes OData endpoints for data warehouse entities. As a report developer, you can use a wide variety of business intelligence tools (Microsoft Power BI or Tableau) to access the OData endpoints.

See https://docops.ca.com/x/IaT4G.

The CA JDBC Adapter enables report developers to connect to the CA PPM SaaS transactional database and data warehouse to run queries from Jaspersoft Studio Professional without a VPN connection.

See https://docops.ca.com/x/DaT4G.

Kind Regards,

Damon

Hi All,

Can someone provide the steps to connect to CA PPM DWH schema via any SQL client (Squirrel/ SQL Workbench, etc..) ?

Thanks,

Anshuman