Multiple Ties between a fact row and a dimension
2 posters
Page 1 of 1
Multiple Ties between a fact row and a dimension
We're a mid sized (1800 people) civil engineering consulting firm just getting started with DW. When we prioritized out business processes, we decided to begin with Labor Transactions as the first process to model. Our facts are pretty basic:
The grain is the individual labor transaction - what appears in the timesheet, where rows are projects and columns are dates.
One of our dimensions is Organization, which is hierarchical: Legal Entity/Division/Region/Cost Center.
The problem is that we need to capture multiple Organizations for each fact row:
For example, if I (an IT guy, sitting in the Austin office) were to charge a billable project originally opened in the Los Angeles Delivery cost center, but now managed by a PM in the San Francisco Cost center, then the Orgs would be:
So here's my question / confusion. Organization is an attribute of the Employee, Project and Work Location dimensions in addition to being a dimension in its own right. In the fact table, do I just have an EmployeeID, ProjectID, ProjectManagerID and WorkLocationID, and infer the Organization from that, or should I, in addition to the above, also have EmployeeOrganizationID, etc.?
Then, what happens if the Project Manager changes? Do I need to have both a "CurrentProjectManagerID" and a "HistoricalProjectManagerID" in the fact table so that I can report it both ways?
Any insight / guidance would be appreciated!
Bill
- Hours Worked
- Hours Paid
- Dollars Costed
- Dollars Paid
The grain is the individual labor transaction - what appears in the timesheet, where rows are projects and columns are dates.
One of our dimensions is Organization, which is hierarchical: Legal Entity/Division/Region/Cost Center.
The problem is that we need to capture multiple Organizations for each fact row:
- Employee Organization ( the cost center the employee who performed the labor belongs to)
- Project Organization (the cost center the project they're working on belongs to)
- Project Manager Organization (the cost center that the project's Manager belongs to)
- Work Location Organization (the cost center that the office the Employee works out of is assigned to)
For example, if I (an IT guy, sitting in the Austin office) were to charge a billable project originally opened in the Los Angeles Delivery cost center, but now managed by a PM in the San Francisco Cost center, then the Orgs would be:
- EO: 09003 (IT)
- PO: 51265 (LA Delivery)
- PMO: 51365 (SF Delivery)
- WLO: 51763 (Austin Delivery)
So here's my question / confusion. Organization is an attribute of the Employee, Project and Work Location dimensions in addition to being a dimension in its own right. In the fact table, do I just have an EmployeeID, ProjectID, ProjectManagerID and WorkLocationID, and infer the Organization from that, or should I, in addition to the above, also have EmployeeOrganizationID, etc.?
Then, what happens if the Project Manager changes? Do I need to have both a "CurrentProjectManagerID" and a "HistoricalProjectManagerID" in the fact table so that I can report it both ways?
Any insight / guidance would be appreciated!
Bill
Re: Multiple Ties between a fact row and a dimension
If the number of organizations are fixed, I would add a relationship for each type of organization, so 4 FK's in your fact table. If the organizations are variable, then you are looking at a bridge table, which is well documented but more complex.
A common requirement for Type 2 dimensions is the ability to view the historical as well as the current values. The easiest modeling solution is to kick the problem to the reporting layer. Here the reporting folks will do a self join to retrieve the current dimension values. Here is some discussion around more options, http://forum.kimballgroup.com/t955-current-and-historic-dimensions-one-table-or-two
A common requirement for Type 2 dimensions is the ability to view the historical as well as the current values. The easiest modeling solution is to kick the problem to the reporting layer. Here the reporting folks will do a self join to retrieve the current dimension values. Here is some discussion around more options, http://forum.kimballgroup.com/t955-current-and-historic-dimensions-one-table-or-two
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Multiple Ties between a fact row and a dimension
Thanks for the links on the options for showing current vs. historical dimension values. I must admit to still being unclear about what I should put in the fact table. Here's a simplified version of our draft dimensional model:
dimEmployee
EmployeeID
EmployeeName, etc.
EmployeeOrganizationID (FK to dimOrganization)
EmployeeLocationID
dimLocation
LocationID
LocationName, etc.
LocationDefaultOrganizationID (FK to dimOrganization)
dimOrganization
OrganizationID
OrganizationName, etc.
dimProject
ProjectID
ProjectName, etc.
ProjectManagerID (FK to dimEmployee)
ProjectOrganizationID (FK to dimOrganization)
factLaborTransaction
LaborTransactionID
HoursWorked
HoursPaid
DollarsCosted
DollarsPaid
DateWorked, etc.
ProjectID (FK to dimProject) {the Project worked on by the Employee for this transaction}
EmployeeID (FK to dimEmployee) {The Employee that worked on this transaction}
So the business might want to view a group of labor transactions by various organizations:
Is this how I should be doing this, or should I be putting the various OrganizationIDs (Project, Employee, Project Manager and Location) in factLaborTransaction itself. Since Organizations can change, I was planning to model the Organization attributes of the various dimensions as Type 2.
dimEmployee
EmployeeID
EmployeeName, etc.
EmployeeOrganizationID (FK to dimOrganization)
EmployeeLocationID
dimLocation
LocationID
LocationName, etc.
LocationDefaultOrganizationID (FK to dimOrganization)
dimOrganization
OrganizationID
OrganizationName, etc.
dimProject
ProjectID
ProjectName, etc.
ProjectManagerID (FK to dimEmployee)
ProjectOrganizationID (FK to dimOrganization)
factLaborTransaction
LaborTransactionID
HoursWorked
HoursPaid
DollarsCosted
DollarsPaid
DateWorked, etc.
ProjectID (FK to dimProject) {the Project worked on by the Employee for this transaction}
EmployeeID (FK to dimEmployee) {The Employee that worked on this transaction}
So the business might want to view a group of labor transactions by various organizations:
- The Project's Organization: factLaborTransaction.ProjectID => dimProject.ProjectOrganizationID => dimOrganization.OrganizationName
- The Employee's Organization: factLaborTransaction.EmployeeID => dimEmployee.EmployeeOrganizationID => dimOrganization.OrganizationName
- The Project Manager's Organization: factLaborTransaction.ProjectID => dimProject.ProjectManagerID => dimEmployee.EmployeeOrganizationID => dimOrganization.OrganizationName
- The Employee's Location's Organization: factLaborTransaction.EmployeeID => dimEmployee.EmployeeLocationID => dimLocation.LocationDefaultOrganizationID => dimOrganization.OrganizationName
Is this how I should be doing this, or should I be putting the various OrganizationIDs (Project, Employee, Project Manager and Location) in factLaborTransaction itself. Since Organizations can change, I was planning to model the Organization attributes of the various dimensions as Type 2.
Re: Multiple Ties between a fact row and a dimension
If the organizations can be different I would create four FK's on the fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Multiple Ties between a fact row and a dimension
Thank you for your feedback.
If we modify factLaborTransaction as suggested:
factLaborTransaction
LaborTransactionID
HoursWorked
HoursPaid
DollarsCosted
DollarsPaid
DateWorked, etc.
ProjectID (FK to dimProject) {the Project worked on by the Employee for this transaction}
EmployeeID (FK to dimEmployee) {The Employee that worked on this transaction}
ProjectOrganizationID (FK to dimOrganization)
EmployeeOrganizationID (FK to dimOrganization)
ProjectManagerOrganizationID (FK to dimOrganization)
EmployeeLocationOrganizationID (FK to dimOrganization)
Then what happens when the Employee changes Location? since EmployeeLocationID would be a Type 2 attribute of dimEmployee, it would update there; would we then have to retroactively update factLaborTransaction to reflect the change?
If we modify factLaborTransaction as suggested:
factLaborTransaction
LaborTransactionID
HoursWorked
HoursPaid
DollarsCosted
DollarsPaid
DateWorked, etc.
ProjectID (FK to dimProject) {the Project worked on by the Employee for this transaction}
EmployeeID (FK to dimEmployee) {The Employee that worked on this transaction}
ProjectOrganizationID (FK to dimOrganization)
EmployeeOrganizationID (FK to dimOrganization)
ProjectManagerOrganizationID (FK to dimOrganization)
EmployeeLocationOrganizationID (FK to dimOrganization)
Then what happens when the Employee changes Location? since EmployeeLocationID would be a Type 2 attribute of dimEmployee, it would update there; would we then have to retroactively update factLaborTransaction to reflect the change?
Re: Multiple Ties between a fact row and a dimension
Absolutely do not update the fact tables. The dimension change is tracked in the dimension. Re-read the linked post for techniques to see "current view" of the dimension.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» 1 Dimension used multiple times in 1 fact table?
» How to Handle a value in a fact table that can have multiple dimension values
» Using a dimension in multiple fact tables with different grain and support SCD
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» How to Handle a value in a fact table that can have multiple dimension values
» Using a dimension in multiple fact tables with different grain and support SCD
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|