In the summer we will be moving from managed hosting with ASR to SaaS and the DDA.
Can anyone offer me any guidance on using DDA - what client are you using to run queries, any help guides etc?
We are also looking at automating some queries, has anyone else done this with DDA?
All the solutions I've found so far require some write privileges which I obviously don't have!
I am (was) an OpenDB user and started using DDA when we moved to SaaS. I am still trying to figure out the best client configuration to use.
With OpenDB, I was using a combination of Oracle SQL Developer, SQLPlus, and Tableau to access the tables.
At the moment, I can issue queries using Oracle SQL Developer, PGAdmin, Talend, and Tableau, however, I have encountered several challenges:
- Due to the way that DDA is implemented with PostgresSQL, queries which used to work in my original environment typically time out. Temporary table space isn't supported by the architecture.
- PostgresSQL syntax is different, particularly with date datatypes
To get around the performance issues, I've been trying to create local replicas of the tables but at the moment, I haven't been successful at moving data I want from the DDA tables to local copies.
Fortunately, I was able to get Tableau to work - but I am still working to create a activity_accumulator file that covers the duration of a term.
- DDA can access to a delayed copy of the live tables. However, again, many queries against these tables fail if they take too long and/or bring back too much information.
- DDA includes a set of stats tables which are taken shortly after midnight. This one is a little more forgiving, but I also encounter query failures.
- SAAS tables are slightly different. For example, the activity_accumulator messages field was moved.
- If using SQL Developer, you need to manually edit the connections.XML file (C:\Users\sor\AppData\Roaming\SQL Developer\system18.104.22.168.21\o.jdeveloper.db.connection.22.214.171.124.42.151001.541) to specify the database name. I am still trying to figure out if I can define a database link
- Postgresql database names are case sensitive
- SQL syntax is different from Oracle. One example: current_date vs sysdate() .
I would be very happy to collaborate.
Retrieving data ...