JesseBizInt (12/12/2016)

---

In my environment I have many different departments that need to be kept separate from each other, yet share some data elements such as calendars and employee profiles. I work for a tribal government, which means I have to deal with various regulations re: data confidentiality and access. I implement just one data warehouse (SQL Server database) to store all the data, and then I use other databases that just hold views to expose the data to various end user applications. For example, let's say the accounting department and the health clinic both need access to employee data and to the calendar, but each have their own fact tables.

In the data warehouse, you would see:

Core_Dim_Employee

Core_Dim_Date

Accounting_Fact_GrantHours

Health_Fact_PatientEncounters

There would also be two separate databases: HealthDM and AccountingDM (DM standing for data mart)

HealthDM would have these views:

Core_Dim_Employee (WHERE Division='Health' to only include Health division employees)

Core_Dim_Date

Health_Fact_PatientEncounters

AccountingDM would have these views:

Core_Dim_Employee

Core_Dim_Date

Accounting_Fact_GrantHours

For me, this allows me that single source of truth and place to enforce data standards, and for the end user, they can only see the data that they need to see and that interests them. They can point Excel or whatever other tool they want to use at their DM and get the data they need without seeing the other departments' data. If a end user needs a cube built, then I use the appropriate DM for the data source that feeds the cube. Because the DMs only hold views, the data is always matching the data in teh data warehouse.