August 18, 2009 at 6:37 pm
Hi,
I have a SQL 2008 table with format:
ID (Unique per employee)
EmployeeName (Not unique as multiple people can have the same name)
AreaManagerName (Not unique as multiple people can have the same name)
RegionalManagerName (Not unique as multiple people can have the same name)
I would like to create a dimension that has a hierarchy of:
--RegionalManager
----AreaManager
------Employee
How would I go about doing this? Or a possible link to a tutorial 😛
Thanks
August 20, 2009 at 12:01 pm
The data source you will need is going to be like the one below. Key things are that the employee number is tracked to a unique number for the area manager and onto a unique number for the region manager.
How you create a unique identifier is up to you there are all sorts of ways to do it. Maybe the combination of regional managers name and area managers name is unique ie 'FredSmithDaveJones' Really you do need an id which should be a number. If you have a name for the region that could help so if the manager for the North Region is Dave Smith you could have North as your unique id or NorthDaveSmith if you have two North regions
Hope that helps.
create view employee
AS
SELECT
1 as EmployeeNumber,
'Fred' as EmployeeName,
1 as AreaManagerNumber,
'Dave' as AreaManagerName,
1 as RegionalManagerNumber,
'Reginald' as RegionalManagerName
UNION
SELECT
2 as EmployeeNumber,
'Fred' as EmployeeName,
1 as AreaManagerNumber,
'Dave' as AreaManagerName,
1 as RegionalManagerNumber,
'Reginald' as RegionalManagerName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply