11
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ I think you're right to question the use of cursors. Thank you for including the table definitions. Great first question! Welcome to CR! Feel free to drop by chat and meet the regulars. \$\endgroup\$ – RubberDuck Jul 9 '14 at 12:21
2
\$\begingroup\$

I noticed that you have a Select nested inside of a select twice, which looks kind of ugly...

  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, '')

This also brings up a formatting issue where there are no new lines in between these two variable declaration blocks which makes it a little hard to read.

but what you should do is use a Common Table Expression(CTE) on these or better yet since the inner most Select statement on both is the same, you should create a temporary table and select from it.

CTE:

WITH tableV (field_name)
AS
(
    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)
)
DECLARE @maxcols AS NVARCHAR(MAX) = STUFF((
    SELECT DISTINCT ',MAX(' + QUOTENAME(v.field_name) + ') AS ' + QUOTENAME(v.field_name)
    FROM tableV
    FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Temp Table:

CREATE TABLE #tableV (field_name);
INSERT INTO #tableV
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)

And then your Query would look like this

DECLARE @maxcols AS NVARCHAR(MAX) = STUFF((
    SELECT DISTINCT ',MAX(' + QUOTENAME(v.field_name) + ') AS ' + QUOTENAME(v.field_name)
    FROM #tableV
    FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

DECLARE @cols AS NVARCHAR(MAX) = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(v.field_name)
    FROM #tableV
    FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Looks like you could do something similar with the @sql variable as well, even if only to make it more readable and to nest it less.

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Hello Malachi, thanks for the in depth review. the 4 cols into 3 is not correct though. the entire part from DATEADD up to and including GETDATE is 1 value. (also this is just to ccreate some testdata so not really interesting imo). The temptable improvement is very nice and I will definitly take that to the live code. What I posted this question for though is any thought on not using the cursor/dynamic sql altogether. since you are obviously a competent programmer, do you have any thoughts on that? \$\endgroup\$ – Tristan Jul 30 '14 at 7:31
  • \$\begingroup\$ if you are using an application to gather this data you should loop in the application through the values in cursor_groups in the application and have it send the values to a stored procedure that calls the stuff that you now have inside the cursor. So many Parenthesis! \$\endgroup\$ – Malachi Jul 30 '14 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.