July 10, 2019 at 4:19 am
The general synopsis is that, apart from our company’s core products, we are now offering our customers a generalized data integration service in the form of an SQL Database, to sit on their own servers, which will accept data from many different sources, massage it appropriately, and then send it off to different destinations.
We have multiple sources, being financial systems, ordering systems, CRM systems, reporting systems etc. There are also multiple components to the integrations, such as suppliers, vendors, purchase orders, invoices, projects, customers, products, all of which could come from different or go to different sources or destinations, including mixtures for the one customer. Customers will have a specific combination of components, and may even have custom work required, particularly when a product has generic fields.
We then have the management requirements for this, which include: -
1. The product should be version controlled – we have Redgate Source Control linking to TFS for this.
2. Core components should be common and kept synchronized for the various customers.
3. Customers should only receive the objects of the database that they require to do the integration work that they require – we shouldn’t distribute the entire database to all customers.
4. Customers may have custom versions of some of the components – this also needs to be managed.
Whilst I have tried desperately to pass this to someone else, they keep giving it back. Added to this is that we rushed out and set this up for four customers already, thus we have versions of this in the wild that we will need to bring back in sync, without disadvantaging any customer.
The general overview is that objects should be named thus: -
· Schema prefix to be “Dropzone_”, for data coming in, “Staging_” for data going out, and “Etl_” for intermediate tables
· Schema suffix to be an abbreviation of the data source/destination, e.g., “ebuild”, “myob”, “finance1” etc.
· Object suffix to be an abbreviation of the customer, for custom variants to objects.
So, for example, we would import (say) Purchase Orders data from ordering system ABC directly into tables, “Dropzone_ABC.PurchaseOrder” and “Dropzone_ABC.PurchaseOrderItem”, which would (try to) match the structure of the data export from ABC. We would then, via the use of the “Dropzone_ABC.uspPurchasingProcess” stored procedure, into the “Etl_XYZ.PurchaseOrder” tables, where it may be merged with other data, such as supplier information from a CRM system, which would then be placed into a “Staging_XYZ.PurchaseOrder” table which would match the format required by application XYZ for import into their system. Clear as mud?
We then have the added complication in that we need to somehow “componentalise” these objects, so that we can say that Customer Bloggs needs the following set of components only, so that is all we deliver to them, plus the fact than some of those components may include specific customization.
Has anyone had to do anything like this before, or have any thoughts as to how we could make this easier? How to we “package” sets of objects, and maintain which customers have which packages? How do we maintain custom variants of objects, whilst ensuring that code isn’t duplicated? How can I gracefully pass all of this onto the Support Team as their problem, and stop it coming back to me?
I need help.
July 11, 2019 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 11, 2019 at 11:54 am
I guess you'll need different branches and/or different builds that deploy the appropriate procs for each case. Not sure how best to achieve that with RedGate and TFS. Alternatively maybe you could redesign with a more metadata-based model where the procs are common to all and the customer specifics are defined in configuration rather than in code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply