Is there an easy way to create and populate an age dimension with age groups?
Creating an age dimension is usually done once and probably not in SSIS, but with a TSQL script.
For each new assignment I use a script similar to this and adjust it to the requirements for that particular assignment.
-- Drop dimension table if exists IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_age]') AND TYPE IN (N'U')) BEGIN DROP TABLE [dbo].[dim_age] END -- Create table dim_age SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[dim_age]( [dim_age_id] [int] IDENTITY(-1,1) NOT NULL, [Age] [smallint] NULL, [AgeGroup1] [nvarchar](50) NULL, [AgeGroup1Sort] [int] NULL, [AgeGroup2] [nvarchar](50) NULL, [AgeGroup2Sort] [int] NULL, CONSTRAINT [PK_dim_age] PRIMARY KEY CLUSTERED ( [dim_age_id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- Enter unknown dimension value (in case a person's date of birth is unknown) INSERT INTO [dbo].[dim_age] ([Age] ,[AgeGroup1] ,[AgeGroup1Sort] ,[AgeGroup2] ,[AgeGroup2Sort]) VALUES (-1 ,'Unknown' ,0 ,'Unknown' ,0) GO -- Enter all ages declare @age smallint; set @age = 0; -- Loop through ages 0 to 130 WHILE @age < 131 BEGIN INSERT INTO [dbo].[dim_age] ([Age] ,[AgeGroup1] ,[AgeGroup1Sort] ,[AgeGroup2] ,[AgeGroup2Sort]) VALUES (@age -- Use the common age groups/categories of your region/branch/industry -- This is just an example , CASE WHEN @age < 15 THEN '0 till 15 year' WHEN @age < 25 THEN '15 till 25 year' WHEN @age < 35 THEN '25 till 35 year' WHEN @age < 45 THEN '35 till 45 year' WHEN @age < 55 THEN '45 till 55 year' WHEN @age < 65 THEN '55 till 65 year' ELSE '65 year and older' END -- Add value to sort on in SSAS , CASE WHEN @age < 15 THEN 1 WHEN @age < 25 THEN 2 WHEN @age < 35 THEN 3 WHEN @age < 45 THEN 4 WHEN @age < 55 THEN 5 WHEN @age < 65 THEN 6 ELSE 7 END , CASE WHEN @age < 19 THEN 'Juvenile' ELSE 'Mature' END -- Add value to sort on in SSAS , CASE WHEN @age < 19 THEN 1 ELSE 2 END ) -- Goto next age set @age = @age + 1 END
|The result: filled age dimension|
How could you use this dimension?
A while ago I also posted an example to create and populate a date dimension. So now you can combine those in a datamart. I have an employee table and an absence table with a start- and enddate.
I will use the date dimension to split the absence time periods in separate days and then calculate the employee's age of each day of absence. This will go in to a fact table and then I can use the age dimension to see absence per age group.
-- Split absence time periode in separate days, but go back 2 years max and 1 year forward if end date is unknown SELECT Absence.AbsenceId , Absence.EmployeeNumber -- Date of absence , dim_date.Date as AbsenceDate , Absence.ReasonCode -- Calculation of age at time of absence , DATEDIFF(YEAR, Employee.DateOfBirth, dim_date.Date) - (CASE WHEN DATEADD(YY, DATEDIFF(YEAR, Employee.DateOfBirth, dim_date.Date), Employee.DateOfBirth) > dim_date.Date THEN 1 ELSE 0 END) as Age FROM EmployeeApplication.dbo.Absence INNER JOIN EmployeeApplication.dbo.Employee on Absence.EmployeeNumber = Employee.EmployeeNumber INNER JOIN DM_Staff.dbo.dim_date on dim_date.Date -- change start date to lower bound if it's below it BETWEEN CASE WHEN YEAR(Absence.AbsenceStartDate) >= YEAR(GETDATE()) - 2 THEN Absence.AbsenceStartDate ELSE DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 2, 0) END -- change end date to upper bound if it's null AND ISNULL(Absence.AbsenceEndDate, DATEADD(yy, DATEDIFF(yy, 0, getdate()) + 2, -1)) -- Filter absence record with an enddate below the lower bound (perhaps a bit superfluous with the inner join) WHERE YEAR(ISNULL(Absence.AbsenceEndDate, GETDATE())) >= YEAR(GETDATE()) - 2
|Result of query that can be used in a fact package|
Note: this is a simplified situation to keep things easy to explain.