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)

Question

Hi , 
 
 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) . 
 
 In terms of Performance and New features of Records which approach will be better ? 
 
 1. Use Store Proc ( Creating a new table > Select required columns from 4 different tables > Insert into new table ) 
 
 But in this case , everytime if our 4 tables get updated in Prod, We have to run this Store Proc 
 
 2. Create a materiliazed view . 
 
 but I guess in this case also , we have to update our view if there are entries in those 4 tables 
 
 3. Create a normal view from 4 tables 
 
 But Record Sync and other data fabric features won't work here I guess

Can anyone please help here ? Thank You in Advance

Mike Schmitt · Answer

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.

Mathieu Drouin · Answer

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).

Mike Schmitt · Answer

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 · Answer

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 !

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)

Top Replies