Multiple Ties between a fact row and a dimension

View previous topic View next topic Go down

Multiple Ties between a fact row and a dimension

Post  billcorley on Tue Jan 29, 2013 3:05 pm

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:
  • 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

billcorley

Posts : 3
Join date : 2013-01-29
Location : Austin, TX

View user profile http://www.linkedin.com/in/billcorley

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  BoxesAndLines on Wed Jan 30, 2013 10:02 am

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
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  billcorley on Wed Jan 30, 2013 11:40 am

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:
  • 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.

billcorley

Posts : 3
Join date : 2013-01-29
Location : Austin, TX

View user profile http://www.linkedin.com/in/billcorley

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  BoxesAndLines on Wed Jan 30, 2013 12:23 pm

If the organizations can be different I would create four FK's on the fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  billcorley on Wed Jan 30, 2013 12:48 pm

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?

billcorley

Posts : 3
Join date : 2013-01-29
Location : Austin, TX

View user profile http://www.linkedin.com/in/billcorley

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  BoxesAndLines on Wed Jan 30, 2013 3:30 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple Ties between a fact row and a dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum