For a data warehousing project I ran into the following:
Custom fields that users can create, modify and delete, that should be loaded into the data warehouse as they are when the ETL happens.
On the profiling part of the area in the database that holds this data I discovered that theses fields are not columns in tables, but are rows in a "field definitions" table and rows in a "field values" table. Another aspect is that, in the actual source data, there are hundreds of columns that belong to several dozen groups.
So far the only way I have found do deal with this is using a cursor and dynamic SQL to create my dimensions from this data. I am interested if there is any other way to do this, and what others think of the code I have written to handle this.
Update: The datawarehousing project uses the MS BI stack (ssis/ssas/ssrs) with this solution as a script in the ssis ETL.
The first block is a script to create demonstration tables with values as they are on the source system, the second block is the code I am using to generate the desired result. This code actually does everything that is required but as you can see it is rather bulky. Any thought on improvements (or a completely different method) are appreciated.
This creates a situation like the source data:
CREATE TABLE groups (
group_id INT identity(1, 1) PRIMARY KEY,
group_name NVARCHAR(100)
)
INSERT groups (group_name)
VALUES ('group_1'),
('group_2'),
('group_3'),
('group_4'),
('group_5')
CREATE TABLE tickets (
ticket_id INT identity(1, 1) PRIMARY KEY,
group_id INT FOREIGN KEY REFERENCES groups(group_id) NOT NULL,
some_foreign_key INT NULL,
some_timestamp DATETIME NULL
)
WHILE SCOPE_IDENTITY() < 50
BEGIN
INSERT tickets (
group_id,
some_foreign_key,
some_timestamp
)
VALUES
(1,CAST((RAND() * 100) AS INT),DATEADD(DD, CAST((RAND() * 100) AS INT), GETDATE())),
(2,CAST((RAND() * 100) AS INT),DATEADD(DD, CAST((RAND() * 100) AS INT), GETDATE())),
(3,CAST((RAND() * 100) AS INT),DATEADD(DD, CAST((RAND() * 100) AS INT), GETDATE())),
(4,CAST((RAND() * 100) AS INT),DATEADD(DD, CAST((RAND() * 100) AS INT), GETDATE())),
(5,CAST((RAND() * 100) AS INT),DATEADD(DD, CAST((RAND() * 100) AS INT), GETDATE()))
END
CREATE TABLE custom_field_definitions (
field_id INT identity(1, 1) PRIMARY KEY,
group_id INT FOREIGN KEY REFERENCES groups(group_id) NOT NULL,
field_name VARCHAR(100) NOT NULL,
show_in_export BIT NOT NULL DEFAULT 0,
active BIT NOT NULL DEFAULT 1,
deleted BIT NOT NULL DEFAULT 0
)
INSERT custom_field_definitions (
group_id,
field_name
)
VALUES
(1,'field_1'),
(2,'field_2'),
(3,'field_3'),
(4,'field_4'),
(5,'field_5'),
(1,'field_6'),
(2,'field_7'),
(3,'field_8'),
(4,'field_9'),
(5,'field_10')
CREATE TABLE custom_field_entries (
entry_id INT identity(1, 1) PRIMARY KEY,
field_id INT FOREIGN KEY REFERENCES custom_field_definitions(field_id) NOT NULL,
group_id INT FOREIGN KEY REFERENCES groups(group_id) NOT NULL,
ticket_id INT FOREIGN KEY REFERENCES tickets(ticket_id) NOT NULL,
value FLOAT
)
DECLARE @ticket INT,
@group INT,
@field INT
DECLARE entries CURSOR
FOR
SELECT ticket_id,
t.group_id,
d.field_id
FROM tickets t
INNER JOIN custom_field_definitions d
ON t.group_id = d.group_id
OPEN entries
FETCH NEXT
FROM entries
INTO @ticket,
@group,
@field
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT custom_field_entries (
field_id,
group_id,
ticket_id,
value
)
VALUES (
@field,
@group,
@ticket,
RAND()
)
FETCH NEXT
FROM entries
INTO @ticket,
@group,
@field
END
CLOSE entries
DEALLOCATE entries
GO
This is the actual code that transforms the model and loads the data into the new model:
DECLARE @group NVARCHAR(100)
DECLARE cursor_groups CURSOR
FOR
SELECT group_name
FROM groups
OPEN cursor_groups
FETCH NEXT
FROM cursor_groups
INTO @group
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @maxcols AS NVARCHAR(MAX) = STUFF((
SELECT DISTINCT ',MAX(' + QUOTENAME(v.field_name) + ') AS ' + QUOTENAME(v.field_name)
FROM (
SELECT d.field_name
FROM tickets t
INNER JOIN groups g
ON t.group_id = g.group_id
INNER JOIN custom_field_entries e
ON t.ticket_id = e.ticket_id
INNER JOIN custom_field_definitions d
ON d.field_id = e.field_id
WHERE (g.group_name = @group)
) v
FOR XML PATH(''),
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DECLARE @cols AS NVARCHAR(MAX) = STUFF((
SELECT DISTINCT ',' + QUOTENAME(v.field_name)
FROM (
SELECT d.field_name
FROM tickets t
INNER JOIN groups g
ON t.group_id = g.group_id
INNER JOIN custom_field_entries e
ON t.ticket_id = e.ticket_id
INNER JOIN custom_field_definitions d
ON d.field_id = e.field_id
WHERE (g.group_name = @group)
) v
FOR XML PATH(''),
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
DECLARE @sql NVARCHAR(MAX) = 'USE demo
SELECT ROW_NUMBER() OVER (
ORDER BY ticket_id
) AS [generated_' + @group + '_pk],
ticket_id,
MAX(group_name) group_name,
' + @maxcols + '
INTO demo.dbo.[table_' + @group + ']
FROM (
SELECT *
FROM (
SELECT t.ticket_id,
t.some_timestamp,
g.group_name,
d.field_name,
e.value
FROM tickets t
INNER JOIN groups g
ON t.group_id = g.group_id
INNER JOIN custom_field_entries e
ON t.ticket_id = e.ticket_id
INNER JOIN custom_field_definitions d
ON d.field_id = e.field_id
WHERE g.group_name = ''' + @group + '''
) sub
) p
PIVOT(MAX(value) FOR field_name IN (
' + @cols + '
)) s
GROUP BY ticket_id'
PRINT @sql
EXECUTE sp_executesql @sql
FETCH NEXT
FROM cursor_groups
INTO @group
END
CLOSE cursor_groups
DEALLOCATE cursor_groups
GO
