June 15, 2009 at 6:48 am
Hi all.
I'm new to the DW/SSAS scene and I'm trying to come up with some ratio reports for our clients.
I have a dimension table storing employees with the following information - business unit, start date, and end date. I also have a fact table storing suggestions sent into management by anonymous employees with the suggestion type (cleanliness, ideas, etc), location the suggestion was made (the business unit), and date it was made.
What I want to do is, by year, display the ratio of suggestions to employee count. I am having trouble determining the best way to do this with the ever-fluctuating employee count for a given business unit at any given time. To make it easier - I have a list of suggestions from 2004 -> present. I want to get the total number of employees in each year along with the suggestion count for those years.
Is my employee dimension and Suggestion fact table the appropriate design? I can't figure out, when creating my cube, how to get the information I want.
Thanks in advance!
August 12, 2009 at 9:15 am
Create view Employees as
(
Select sum(employees) as EmployeeCount,
2001 as year
From YourEmployeeTable
Where ’01-jan-2001’ between start date and end date
Group by Year
UNION
Select sum(employees) as EmployeeCount,
2002 as year
From YourEmployeeTable
Where ’01-jan-2002’ between start date and end date
Group by Year
UNION
……
)
This will provide a view containing number of employees per year
Apply same logic to suggestions
Create view Suggestions as
(
Select sum(suggestions) as SuggestionCount,
2001 as year
From YourEmployeeTable
Where SuggestionDate between ’01-jan-2001’ and ’31-dec-2001’
Group by Year
UNION
Select sum(suggestions) as SuggestionCount,
2002 as year
From YourEmployeeTable
Where SuggestionDate between ’01-jan-2002’ and ’31-dec-2002’
Group by Year
UNION
……
)
Now roll the two into one
Create view olap_fact_employee_suggestions
As
Select e.year, e.EmployeeCount,s.SuggestionCount
From Employee e, Suggestions s
Where a.year = s.year
Create a dimension for year
Create view olp_dim_year and insert values 2001 as Year, ‘Year2001’ as YearName and all the other years you need,
The two important results are the view olap_fact_employee_suggestions which will have data such as
Year Emp Sugg
2001 20 10
2002 30 11
2003 25 5
and the view olp_dim_year which looks like
Year YearName
2001 Year 2001
2002 Year 2002
The first view provides your fact of which the two counts will become measures and the year will be the dimension. The second view is what prodies the data for the dimension.
Hope this helps.
Ells.
😎
PS Create dimension first then the cube.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply