It's easy to answer this question.
Imagine that in a software company who sells the product and also offers reporting solutions, the ERP tables will not have this additional column to all the tables.
Now the reporting department comes and needs to consolidate all that data from different databases (publishers) and create one multitenant database to have all the data.
So in an ERP like NAV or anything else you cannot suggest change all the code to all of the tables plus all functions to add one additional column to this table, even that was possible then you cannot work with integers but you need to work with GUIDs as this column should be predefined to each ERP. Then joining with GUID in the second phase for reporting definitely will slow down the performance.
In summary:
- Cannot touch the underlying source (important)
- GUID identifier column will slow down the reporting performance
On Wed, Nov 23, 2022 at 1:40 AM Stavros Koureas
<koureasstavros@gmail.com> wrote:
>
> Reading more carefully what you described, I think you are interested in getting something you call origin from publishers, probably some metadata from the publications.
>
> This identifier in those metadata maybe does not have business value on the reporting side. The idea is to use a value which has specific meaning to the user at the end.
>
> For example assigning 1 for tenant 1, 2 for tenant 2 and so one, at the end based on a dimension table which holds this mapping the user would be able to filter the data. So programmatically the user can set the id value of the column plus creating the mapping table from an application let’s say and be able to distinguish the data.
>
In your example, are different tenants represent different publisher
nodes? If so, why can't we have a predefined column and value for the
required tables on each publisher rather than logical replication
generate that value while replicating data?
--
With Regards,
Amit Kapila.