You only confirm my first-blush opinion: keep separate databases. Regarding
your second issue (joining across databases for reports), I would suggest
that these operations are best done against an OLAP database not the
numerous OLTP databases. That is, create an additional database whose sole
purpose is reporting, and which accumulates all the data from the numerous
OLTP databases, on some scheduled basis (schedule dictated by stakeholders
-- how recent should the reports be? Will one week do? One day? One hour?
The basic idea here is that reporting does aggregates and therefore
necessarily does table scans, especially in your case. To place this burden
on the OLTP databases is an error in design and more important, a bottleneck
in performance. My advice would be to separate the reporting tasks from the
data-entry tasks. Aggregate the data periodically in the OLAP database and
base all your reports on this, not on the OLTP databases. This way you
maximize data-entry and update speed, while also maximizing the reporting
speed (since it won't cause contention with the data-entry activities).
This would mean that the aggregate db is very large, but OTOH interrogating
it won't impair the OLTP databases in the slightest. So the big problem this
scenario suggests is the granularity of the updates to the OLAP version of
the data. That's not for me to decide. Ask the stakeholders how recent the
data must be and proceed from there.