CODEWITHSUNDEEP
databasetableau-apidata-synchronizationoracle-cdc
Noor A Shuvo
Noor A Shuvo

Reputation: 2807

How to push data from a on-premises database to tableau crm

We have an on-premises oracle database installed on a server. We have to create some Charts/Dashboards with Tableau CRM on those data on-premises. Note that, tableau CRM is not Tableau Online, it is a Tableau version for the Salesforce ecosystem.

Tableau CRM has APIs, so we can push data to it or can upload CSV programmatically to it. So, what can be done are,

  1. Run a nodeJS app on the on-premise server, pull data from Oracle DB, and then push to Tableau CRM via the TCRM API.
  2. Run a nodeJS app on the on-premise server, pull data from Oracle DB, create CSV, push the CSV via TCRM API

I have tested with the 2nd option and it is working fine.

But, you all know, it is not efficient. Because I have to run a cronJob and schedule the process multiple times in a day. I have to query the full table all the time.

I am looking for a better approach. Any other tools/technology you know to have a smooth sync process?

Thanks

Upvotes: 0

Views: 549

Answers (1)

nanu146
nanu146

Reputation: 97

The second method you described in the questions is a good solution. However, you can optimize it a bit.

I have to query the full table all the time.

This is can be avoided. If you take a look at the documentation of SObject InsightsExternalData you can see that it has a field by name Operation which takes one of these values Append, Delete, Overwrite, Upsert what you will have to do is when you push data to Tableau CRM you can use the Append operator and push the records that don't exist in TCRM. That way you only query the delta records from your database. This reduces the size of the CSV you will have to push and since the size is less it takes less time to get uploaded into TCRM.

However, to implement this solution you need two things on the database side.

  1. A unique identifier that uniquely identifies every record in the database
  2. A DateTime field

Once you have these two, you have to write a query that sorts all the records in ascending order of the DateTime field and take only the files that fall below the last UniqueId you pushed into TCRM. That way your result set only contains delta records that you don't have on TCRM. After that you can use the same pipeline you built to push data.

Upvotes: 1

Related Questions