Create and fill Age dimension

Case
Is there an easy way to create and populate an age dimension with age groups?

Solution
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.

Employee table

Absence table

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

fact absence

Note: this is a simplified situation to keep things easy to explain.