Inserting/UpsertingData To Entities From External App

Hi Guys, We're building an app to house a range of entities, and one of our core datasets (products) is processed/updated each day for each site, by an internal local application which currently loads the data to Azure SQL. 

Because its a core dataset our preference would be to load this data directly to the corresponding 'Products' entity in Outsystems as the source of truth, and ensure that every time we load each day, we're adding any new product, and updating the existing records with the new data.

Can anyone recommend an approach for this, or steer us towards some helpful posts/documentation. (or tell us if we're barking!)

Some additional info in case relevant...

  • The products are seperated by a site id, 10 sites apx 30k products per site
  • The internal app exports to Azure SQl, but can load it almost anywhere/any which way
  • The Azure SQL database we are loading to is exposed to a query via odata.
  • Each site has its own DB. Same DB server.

Thanks in advance

W

Hi Dubya,

If I understood correctly, your Products Entity has been created within an OutSystems module. If this is the case, I see two approaches:

a) Create a webservice and use it as a single point of external contact. The main advantage is having a single point of contact with your Entity. It's also flexible - right now you have one internal app, but if you ever need other apps writing data to this Entity, they just need to invoke the webservice. Performance might be rough depending on just how much data you'll be synchronising every day. Do you have an idea of how much data you'll be processing every day? Would it be those 300k records, or it would be a smaller subset?

b) Create a separate table and consume it as an external Entity in OutSystems with an Extension. Everyone who needs to insert data would write to this table, and your OutSystems logic would be processed throughout the day.

Both of these share one thing in common, and it's that you need to create a common point between your applications. It's easier to either choose a webservice or a known table in your database, because OutSystems Entities will be created with different names in each environment, and it will be much harder to manage them as a direct common point.

Looking at both of the approaches I'd actually consider a mix of both: having a webservice that writes to a temporary Entity, alongside a Timer or a BPT that would process this data in an asynchronous manner.

Do you think you could share a little more about the quantity of data you'd have to process? Timeframes would help as well - does the synchronisation happen throughout the day, or would you have a specific period where it would have to start and finish?

Afonso Carvalho wrote:

Hi Dubya,

If I understood correctly, your Products Entity has been created within an OutSystems module. If this is the case, I see two approaches:

a) Create a webservice and use it as a single point of external contact. The main advantage is having a single point of contact with your Entity. It's also flexible - right now you have one internal app, but if you ever need other apps writing data to this Entity, they just need to invoke the webservice. Performance might be rough depending on just how much data you'll be synchronising every day. Do you have an idea of how much data you'll be processing every day? Would it be those 300k records, or it would be a smaller subset?

b) Create a separate table and consume it as an external Entity in OutSystems with an Extension. Everyone who needs to insert data would write to this table, and your OutSystems logic would be processed throughout the day.

Both of these share one thing in common, and it's that you need to create a common point between your applications. It's easier to either choose a webservice or a known table in your database, because OutSystems Entities will be created with different names in each environment, and it will be much harder to manage them as a direct common point.

Looking at both of the approaches I'd actually consider a mix of both: having a webservice that writes to a temporary Entity, alongside a Timer or a BPT that would process this data in an asynchronous manner.

Do you think you could share a little more about the quantity of data you'd have to process? Timeframes would help as well - does the synchronisation happen throughout the day, or would you have a specific period where it would have to start and finish?

Thanks for responding so quickly and thoroughly Afonso. Im a bit of a Outsystems Noob and I'm going to need to process that, so forgive any naiveity. Products are mixed bag.

A site will have anywhere between a 10 and 100k products, and there'll be a few different scenarios. 

The data itself is essentially a feed of product info from the catalogue. Fairly standard stuff. Code, title, description,price,category,stock level etc. 

The products wont change completely every day, rather for each site, it will be a handful of new products showing up for the first time as they've been added to a catalogue feed, so those will need to be added as new rows. This could be anywhere from a few to a few 000 new rows, per site.

The rest of the rows, will likely have already been added in previous data loads. They wont need to be reloaded, but certain important fields which are more temporal (like stock and price) will need to be updated.

Sync needs to happen at a specific point ie around 0600 and ideally be done by 0800.

The hope was that our app could insert/upsert the data into the entities but I can see how this will place a lot of load and a more articulated system of update might be required.

I think your main issue is going to be your sync window - two hours might be enough for a few dozens/hundreds of thousands of records, but anything above that might be complicated.

If it's a non-negotiable window and you only have a few hours to do the update, I'd be more inclined with the shared database table, since everything would happen within the database and you wouldn't have to wait for the data to arrive into the OutSystems context:

1) Your internal apps write to a common database table (let's call it the ProcessProducts table) with the data that you need to sync;

2) OutSystems imports the ProcessProducts table through an extension;

3) You create a Timer that reads through ProcessProducts and processes things in a batch manner (say, X thousand records for every process), marking off things that have been processed and the time of the process for your audit needs;

This should be enough for your current problem, but I honestly am not too fond of it - I feel that "hiding" things in an external table adds more complexity to your maintenance and your overall solution.

If you think the current window can be extended or if there's some leeway to the duration, I'd investigate the webservice solution - it's roughly the same to what I described above, but your internal apps would call the webservice and OutSystems would write to an internal Entity. The only problem is the performance overhead, because data needs to flow from your internal apps into the OutSystems context through a webservice and then it must be written into the database before being processed.

Sorry if I'm going too fast, if you have any questions about implementing any of this just let me know.

Afonso Carvalho wrote:

I think your main issue is going to be your sync window - two hours might be enough for a few dozens/hundreds of thousands of records, but anything above that might be complicated.

If it's a non-negotiable window and you only have a few hours to do the update, I'd be more inclined with the shared database table, since everything would happen within the database and you wouldn't have to wait for the data to arrive into the OutSystems context:

1) Your internal apps write to a common database table (let's call it the ProcessProducts table) with the data that you need to sync;

2) OutSystems imports the ProcessProducts table through an extension;

3) You create a Timer that reads through ProcessProducts and processes things in a batch manner (say, X thousand records for every process), marking off things that have been processed and the time of the process for your audit needs;

This should be enough for your current problem, but I honestly am not too fond of it - I feel that "hiding" things in an external table adds more complexity to your maintenance and your overall solution.

If you think the current window can be extended or if there's some leeway to the duration, I'd investigate the webservice solution - it's roughly the same to what I described above, but your internal apps would call the webservice and OutSystems would write to an internal Entity. The only problem is the performance overhead, because data needs to flow from your internal apps into the OutSystems context through a webservice and then it must be written into the database before being processed.

Sorry if I'm going too fast, if you have any questions about implementing any of this just let me know.

I think Im following you Alfonso. Is it not possible to simply write to the outsystems entity in some way. Im thinking of a saleforce'esque bulk load type thing. That way its more a push from outside of outsystems by the application doing the work, rather than trying to orchestrate outsystems to consume it.


It's possible - an OutSystems Entity is an underlying table in your database, and in theory anyone can write to it. It's just easier to either use a webservice or create a "normal" table in your database than it is to directly expose your OutSystems Entity. 

In Service Studio, your Entity might be named "Products", but in your development environment, the actual table name within your database might be something like "OSUSR_ZAB_Products", and this name is not guaranteed to remain constant between environments. This means your integrations need to bypass this simplification and start managing table names, and that's a bad idea.

If this sounds like too much work it's because it is - the platform abstracts a lot of the complexity and trying to go around it is a bad practice. That's why your best bet is to create a common point between applications that I mentioned.

Thanks Alfonso. Having spent much of yesterday agonising about working with external entities and internal ones due to the inability to join them, it sounds like I'm better off keeping all my tables externally, as it sounds complex to continuously sync/replicate large amounts of data into outsystems, and awkward to split the data model across internal and external objects as they cant be joined. Seems like you have to go all in or not to avoid bending and burdening the system with additional complexity. Am i reading this right.

Solution

I don't think you have to go all in, but you do need to consider how your different application contexts are going to communicate. There's no bad solutions, it always depends on your circumstances.

I would say that your particular goal is a little more complicated because it involves both OutSystems entities and external tables - that combined with your short timeframe for the daily sync means you have less reasonable options.

Re-reading your use case I feel that a common database table as a queue for data to be read and processed by the platform would still be the better option, but it's understandable if you decide to just keep all of it externally. Splitting the data model is a valid concern, and that's why I mentioned a webservice, as it would give you a clear line of separation between contexts - you'd have the OutSystems side clearly delimited, and your existing internal apps would communicate with the platform through that webservice, keeping things manageable. But then you're forced to consider performance and how to fit everything into the necessary timeframe.

I think you're going to have to consider how to handle this core dataset in OutSystems eventually, so why not test it out? If you feel that creating this additional common table would have a big impact on your architecture, you could write up a test with a webservice and try to measure performance - I might be overestimating the problem. Assuming it's all internal communication, it's possible a webservice would fit your needs and still make it into the timeframe.

Solution