Ike Ellis's thoughts on data, SQL Server, and software development.
Out of curiosity, how do you handle "common" data when you have one database per application? Do you use synonyms or cross-database queries? We have a multi-tenant scenario with several tables that contain data about customers and are used in other databases. What do you do when apps have the need for some of the same tables but break out details in different ways? For instance, if you have a main set of People that are referenced throughout multiple databases? Do we give up referential integrity and hope that the apps can reference the data properly? Do we use schema in cases like that?
Common data between applications can be handled in several different ways:1) The application can request the common data from a Web API. Going through the front door.2) If you're concerned with reporting, you can pipe data from the transactional database into an ODS, DW, or DM.3) If the data absolutely must be present in individual databases, than pipe it into the ODS and pipe it out again to the other databases. At least that way, you have data lineage and you know where the system of record is. The problem with piping it directly from the source to the destination is that you lose lineage. Everyone should get in the habit of piping it from the ODS. That way the ODS has one way in and we don't lose who's responsible for it.I would avoid using cross-database queries and synonyms.
Ike,Can you be more specific about what you mean regarding piping the data in and out? I know I need to create an ODS, but I am not sure where to start, and how to best transport data across the apps.
Hi Geo,Did I respond to this yet? You create an ODS by creating ETL processes to extract the data from transactional system and loading them in a central location with little to no transformations. You do this so that the processes won't fail, will be light on the source system, and will run quickly and keep the ODS up to date.
Nice. Thanks!I hope Silencing cell phones wasn't #1. I never do that.
Sorry, Steve! I just updated the deck. Now you can see #1.