MDX / Analysis based on the for Employee-Project-ProjectTeam Data.

Question

MDX / Analysis based on the for Employee-Project-ProjectTeam Data.

vineet_dubey1975

SSCrazy

Points: 2306
More actions
September 22, 2014 at 5:41 am

#151377

Hi All,
I have below tables :
Employee ( ID, EmployeeCode,fname,lname)
Project(ID,ProjectCode,ProjectName)
ProjectTeam(ID,Employeecode,ProjectCode,StartDate,EndDate)
How do I design fact and dim table for the analysis of the data (Year->Quater->Month->Day)
A) TotalEmployee count, B) Total number of Employee On various projects <fromDt> <toDt> Etc.
What is the approach i should follow should i generate full calender year fact data for all the employees
daily ?
Kindly help to get the correct direction on this
I have already populated the DimDate dimension.
Thanks for the help I am getting here ..
Vineet D

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

vineet_dubey1975 (9/22/2014)
Hi All,
I have below tables :
Employee ( ID, EmployeeCode,fname,lname)
Project(ID,ProjectCode,ProjectName)
ProjectTeam(ID,Employeecode,ProjectCode,StartDate,EndDate)
How do I design fact and dim table for the analysis of the data (Year->Quater->Month->Day)
A) TotalEmployee count, B) Total number of Employee On various projects <fromDt> <toDt> Etc.
What is the approach i should follow should i generate full calender year fact data for all the employees
daily ?
Kindly help to get the correct direction on this
I have already populated the DimDate dimension.
Thanks for the help I am getting here ..
Vineet D

It all depends on whether or not an employee can be on more than one project. If this is the case then you will need to utilize Many-To-Many relationships. Have a read of the paper here: http://www.sqlbi.com/articles/many2many/

If not then I would suggest that you have an employee fact table with relevant surrogate keys to project and start date and end date (of the project).

Then the total employee count would just be a record count of the employee table and the total number of employees on projects could be achieved a couple of ways - you may have a flag in the project table (that you could update as a calculation in the dsv) for if the project was "Active" and then just filter. Or you could use an MDX calculation to work out if an employee is on an "Active" project by looking at the date range and comparing it to "Today".

I'm on LinkedIn

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

1) The employee can be on more then one project.

2) The StartDate and EndDate of ProjectTeam table are dates , when an employee is active on the project.(Not the project start and enddate)

e.g.

StartDate : Date the employee is added to the project.

EndDate : End Date of employee on the project

Thanks

Vineet D

PB_BI SSCoach Points: 17463 More actions · Answer 3

In that case you'll want to look at implimenting a Many-To-Many solution. Read the article that I linked to, that will give you a good grounding on the concepts and help you to make a decision that best suits your situation.

I'm on LinkedIn

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 4

Link is a nice exhibition of M-To-M complexities but my points of challenge - what should be my fact table the employee start date and end date are stored in the range. Will be great if i get the ideas on fact table design.

Step 1- Loop through all the employees (Between start and end date)

Step 2 -Fill fact table data daily for individual employee

Or any other trick will work;

Thanks for the help i getting in this forum

Vineet D