Sql-server – How to normalize a database with lots of many-to-many relationships

Well... I figured it out!

Here's what I did:

• Deploy all the reports from the BIDS project to a main folder, say Source Reports.
• On the ReportServer, create site-specific folders in another folder, say Site-Specific Reports. Also in that folder, create a Master Reports folder.
• Copy the Master reports to the Master Reports folder.
• For each site report, create a linked report in their site-specific folder under Site-Specific Reports.

Now here's the "tricky" part... if you edit the report in the Source Reports folder in Report Builder, you can set up the sub-report to point to a specific folder structure on the ReportServer. This enables you to use sub-reports anywhere on your ReportServer, not just in the same local directory. My assumption is you can also type in the full path of the deployed report you wish to use as a sub-report into BIDS, but with Report Builder you can browse and click.

So, in this case, I have made all of the site-specific reports point to the Master report in the Master Reports folder.

This is a great solution for us. Now we can grant user access at the folder level without creating excessive burden on the report deployment process.

My initial thoughts would be solution of two DBs per client, one for the datamart and one for the ODS. The ODS would be in full recovery mode as I don't think you can recreate the data here without retrieving it again from the upstream sources at your clients, if at all. The datamart may be in simple recovery mode as all the source data is coming from the ODS and can reloaded from scratch if needed, but the trickle-in fact table makes it complicated. I'm inclined to move the fact table into the ODS to have better options to enforce RI and other data cleansing needs before it goes to the datamart (via a DML trigger or seperate scheduled ETL perhaps). Also, if you can make the datamart DB read only in between ETL cycles, you'll benefit from not having SQL have to manage locks during the report requests.

I don't think you have an option to use a central schema/DB for the non-sensitive content. From a scalability perspective, having each client's data structures in their own containers allows you migration options to faster disks or another host if needed without impacting the other clients or sustaining a larger downtime. From a business logic perspective, if a particular client needs a business rule change that impacts the schema, will the central DB be able to accomodate it without negative impact? Also from scalability, think of the risks caused by locks on a table caused by one client impacting other clients concurrently connected.