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.
