Loading Data into a new Table / View from 4 different tables (These 4 tables don't have Record , those are old tables They have CDT)

subhajitb1373 said:

We have a requirement of Loading Data into a new Table / View from 4 different tables (These 4 tables don't have Record , those are old tables They have CDT) . 

This sounds like a standard use case for a traditional View.

subhajitb1373 said:

But Record Sync and other data fabric features won't work here I guess

Why would this be an issue in this case, exactly?

subhajitb1373 said:

We have a requirement of Loading Data into a new Table / View from 4 different tables (These 4 tables don't have Record , those are old tables They have CDT) . 

This sounds like a standard use case for a traditional View.

subhajitb1373 said:

But Record Sync and other data fabric features won't work here I guess

Why would this be an issue in this case, exactly?

I have explored and found out that these 3 options are suitable for us . 

If we are going with Approach 3 : Using traditional Views , we will be missing out latest features of Data Fabrics like Record Sync , Custom Fields , Data Events and many more . 

If you can still suggest which features of Data Fabrics we can still use for a View ; that would really help .

Are the 4 old tables going to be maintained, or are you planning to copy their data into a whole new table for the sake of that new table now being used going forward?  In that case I would suggest you use a stored procedure to do the data copy into the new table, then use a Synced Record that relies on that new table alone, and leave the original 4 tables as-is for posterity.

But if the 4 tables are the ones that will be maintained going forward and your view will be a live aggregation of the values of those tables for informational purposes, then I suppose your choices are either 1) make a Synced Record Type for all 4 of them and use record relationships to link them all, or 2) create a traditional view and just use it like a view, including using a Data Store to access it, and Query Entity to query it into appian.

I'm not really clear what your use case is exactly so can't really comment much further on how you will make this decision.

Thank you for your support ! I would like to help you with more details that you have asked for . 

Yes those 4 tables will be maintained for seperate existing Old Application and we are creating a new Table / View  which will consume the Data from those 4 existing Tables .

Approach 3 ( Using Traditional View ) :

Let's consider we will not create a view from existing CDTs , we want to proceed with Records . For that we will be joining the tables and create a View . 

Using traditional Views will have updated Data in our new Application if there is any update on Old Application Data .

We will create a new Record out of traditional View that we have created . But if we update the Record in our new Application, Old Application Data will be updated ?

Also want to understand which Data Fabric Features we may loose if we go by this approach apart from Record Sync , Record Events , Custom Record Fields , Record Relationships .

Approach 2 ( Using Materialized View ) : 

Here we can leverage Data Fabrics . 

Whenever new Data are getting inserted in any of those 4 tables , we have to update our View also as Materialized View doesn't update automatically . How can we handle this in PROD? 

Approach 1 ( Using Store Proc ) : 

Here also we can leverage Data Fabrics . 

Step 1 : We will create an Record and related Table ( This will not be a part of Store Proc )

Step 2 : We will write SELECT Query from those 4 tables by joining them with Primary Key 

Step 3 : We will run INSERT INTO command to write our Data into new table 

When we deploy to PROD , we have follow these 3 steps first before our Deployment. Now again if there are new entries on those 4 old tables , we have to run our Store Proc as scheduler ? 

Thank You for reading this out ! 

Here are the features enabled by data sync: 

docs.appian.com/.../about-data-sync.html

There is not much benefit for using a synched Record Type on a single table IMO. The biggest gains in using DF lie in the relationships you can make between Record Types.

The only features you would lose are Record Level Security and Self Service analytics IMO. You could replicate custom record fields in your view if you wanted to.

Materialized View / Stored Proc are the same option. You'll likely be using a stored proc to populate the materialized view in most cases.

If you do go the materialized view route, the issue you'll encounter is synchronization. Appian only supports daily sync.

Like Mike mentioned earlier, I think your best bet is a regular View with a CDT. If you have performance issues, you could switch to a materialized view (still via CDTs).

Thanks a lot !!