Star Schema - Multiple Fact Tables with Dimension Tables Question

[ease20022002]

Hi,

I am attempting to develop a data warehouse for project management finance, forecast and budget reporting in SQL Server 2005. All I have is SSMS and SSIS BI ETL and I won't be able to get anything else.

I have 4 fact tables and the following are descriptive names for this post: Employee_TimeWorked, Finance_TimeWorked, Forecast_TimeWorked, and Budget_TimeWorked.

The problem I am having is how to handle multi-valued attributes to Employee across the 4 fact tables. My current design has all of the attributes across all Fact tables in their own Dimension tables. I believe that this is not an efficient design. Here is my dilemna, I want to set up a Dimension table as follows, which would consolidate a bunch of Dimension tables into one:

DimEmployees (EmployeeID, EmployeeName, ShortName, ResourceType, ResourceRole, ResourceSource, CompCode)

All of the fields in the above dimension besides the EmployeeName have multi-values and they relate perfectly to the Employee_TimeWorked table as all of the fields above are found in the Employee_TimeWorked table.

But for the Forecast_TimeWorked table only EmployeeName and ResourceRole are found. I obviously can't create foreign keys in the Forecast_TimeWorked table from the DimEmployees dimension table b.c I won't be able to accurately represent the data in that table.

I have the same problem for the Finance_TimeWorked and the Budget_TimeWorked. I know I could Join on EmployeeName for 2 other Fact tables but that still leaves the BudgetTable left out. Do I bite the bullet and create a somewhat duplicate dimensiontable to accomodate the BudgetTable?


What is the best solution to my problem? As I currently have 25 individual dimension tables to manage the differences but I know it isn't efficient, but I needed to get the data into the DB fast and begin development on the application that will pull the data from views that have been developed.

Also, the process I am currently using to process the data in the back end is to:
1) importing data into Import tables
2) caluclating cost, etc., in the import table,
3) inserting the records with foreign keys into fact tables.
How would I do #3 with a new schema?

Thank you very much for any help provided.

 

[RiverGuy]

Firstly, it should be part of your data cleansing process to get a good Employee dimension table. You should be using surrogate keys on your dimensions (with the possible exception of dimensions like Date dimensions). So, your source system EmployeeID does not need to be the primary key of your dimension.

That being said, if you can resolve a name from one source system to an employee record from another system, you could use the same EmployeeID business key. If you are unable to do so, you can always put attribute values like "Unknown Resource Code" into the dimension for records which do not come from your well-defined employee source. If you then group by Employee Name, you will be able to combine data from your three fact tables into a single query.

And finally, when I talk about foreign keys in star schemas--I never explicitly define the relationships. Why slow down your data load into the fact table with foreign keys? Your ETL should ensure that you do not have any orphaned fact records.