2

I am importing and cleaning data from a datasource that have records like this:

Source Data

The data needs sorting and rearranging based on the Event Type.

The destination schema would be something like this:

Create Table dbo.Destination
(
DestinationId int not Null primary key clustered,
EventType int not null,
fkyCustomerId int not null,
         Constraint FK_Destination_Customers References dbo.Customers (pkyCustomerId),
fkyCategoryId int not null
         Constraint FK_Destination_Categories References dbo.Categories (pkyCategoryId),
fkyCompanyId int not null
         Constraint FK_Destination_Companies References dbo.Companies(pkyCompanyId),
fkyLocationId int not null
        Constraint FK_Destination_Locations References dbo.Locations(pkyLocationId),
Amount money not null
        Constraint DF_Destination_Amount Default(0)
)
-- Lookup table
Create Table dbo.Categories
(
pkyCategoryId int not null primary key clustered,
SourceCategoryId char(10) Not Null,
CategoryName varchar(50) Not Null
)
-- Lookup table
Create Table dbo.Companies
(
pkyCompanyId int not null primary key clustered,
SourceCompanyId varchar(10) Not null,
CompanyName varchar(50) Not Null
)
-- Lookup Table
Create Table dbo.Locations
(
pkyLocationId int not null primary key clustered,
SourceLocationId varchar(10) Not Null,
LocationName varchar(50) Not Null
)

For each Event Type, and there could be hundreds of event types, there are rules on what data exists in each of the col columns.

So for Event Type 1234, Col0 has the mapping for the Categories lookup and Col1 has the mapping for the Companies lookup.

However, for Event Type 5000, Col0 contains the mapping for the Companies lookup and Col1 contains the mapping for Locations.

Also, there are situations when there will be null values for all Col columns which means I need to map these to default category, company etc values.

I need to design an import process that can handle the existing rules and potentially new rules. The import will handle approx 2.5 millions rows per day and increasing.

What would be a good way to implement this kind of import. Nothing is set in stone here, apart from the the source data, so I am more than willing to entertain the idea of changing the schema etc.

The final goal is to transform the source data so it can be easily used for reporting purposes.

3
  • 1
    This dynamic references are not exactly what relational databases are known for... you'll be constantly fighting the system rather than using the system. Oct 11, 2012 at 11:26
  • Is this a one time import or is this something you will have to do on a regular basis?
    – Zane
    Oct 11, 2012 at 16:58
  • @Zane: This will be a daily import process. Oct 12, 2012 at 7:37

2 Answers 2

2

I think the biggest challenge will be doing the event type transformations efficiently, so I'm going to focus on that and ignore the schema completely.

My first thought (read: I've never actually tried this) is to use dynamic SQL to build up a SELECT statement that will do the data transformation such that values end up in their correct columns (i.e., Col0 --> CompanyId, or whatever). This boils down to a mapping table consisting of the event type, the source column, the target column, and a default value if required.

Once you have the mapping table (or tables, depending on how complex this actually is), you can use it to dynamically build a SQL statement of the form:

SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

Build the statement as part of the ETL process. If you need to add a new event type, just add the required column mappings to the mapping table, and they'll be handled the next time the ETL process runs.

Once the source data is in this kind of state, the rest of the import process should be pretty standard.

5
  • I think this is similar to where my own thought process led me to. (Well it involved a mapping table & dynamic SQL :) I might give it a try and see how it performs. I was hoping there would be a better way but as the incoming data is a bit of a mess I can't see how there can be any other way. Oct 11, 2012 at 15:41
  • @Barry: This form of statement should only scan the source table once. I'm not sure if there's a limit on the number of WHENs in a CASE statement -- with "hundreds of event types" that may be a concern, depending on how common certain columns are among the different types. That said, if you do bump into a limit with that, you can rework things to run multiple SELECTs like this, but only handle part of the events in each once (add a WHERE clause to filter the source rows), then UNION ALL everything together.
    – Jon Seigel
    Oct 11, 2012 at 15:54
  • @Barry: Can you comment on how well this approach worked? I'm curious to hear your experience.
    – Jon Seigel
    Oct 19, 2012 at 15:34
  • Sure, I will post it as an answer. It uses slightly different logic from your case example but your answer certainly fueled my initial thoughts :) Oct 19, 2012 at 15:44
  • Answer posted! :) Oct 19, 2012 at 16:03
1

OK so after a bit of jiggery pokery I finally came out with this solution.

I created a mapping table like this:

Create Table dbo.MappingTable
(
EventTypeId int
IsCompanyPresent bit,
CompanyLocation varchar(50),
CompanyDefault varchar(50),
IsCategoryPresent bit,
CategoryLocation varchar(50),
CategoryDefault varchar(50),
...
)

And then for each Event Type populated the mapping table with the relevant values.

As an example if the Category was present then the IsCategoryPresent value would be 1, the CategoryLocation value would be Col2 and the CategoryDefault value would be 1.

The CategoryLocation value corresponds to the column in the source table and the CategoryDefault value corresponds to the default value used in the new lookup table.

If the Category is not present then the IsCategoryPresent value would be 0, the CategoryLocation value would be SourceTableId and the CategoryDefault value would be 1. I have to set the location to a valid column in the SourceTable or SQL Server throws an error.

The SQL that I now have is this stored procedure (which I execute for each event type in the mapping table) The load times for about 2.5 - 3 millions rows have been less than 1 minute - which I am more than happy with.

Create Proc [dbo].[XXXXXXX]

@EventType int

As Begin

Declare @Sql varchar(max)

Select @sql = 'Insert Into dbo.Destination
                Select  re.SourceTableId,
                        et.pkyEventTypeId,
                        c.pkyCustomerId,
                        bci.CompanyId,
                        rec.pkyCategoryId,
                        s.pkyLocationId,
                        Case 
                        When rem.IsAmountPresent = 0 Or Len(IsNull(re.' + rem.AmountLocation + ', space(0))) = 0 Then rem.AmountDefault 
                        Else re.' + rem.AmountLocation + '
                        End as [Amount]

                From dbo.SourceTable re
                Left Join dbo.MappingTable rem on re.Event_Type = rem.EventType
                Join dbo.EventTypes et on rem.EventType = et.EventTypeId

                Join dbo.Companies bci on bci.SourceCompanyId =  Case 
                                                                        When rem.IsCompanyPresent = 0 Or Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0 Then rem.CompanyDefault 
                                                                        Else  re.' + rem.CompanyLocation + ' 
                                                                        End

                Join dbo.Categories rec on rec.SourceCategoryId =   Case 
                                                                        When rem.IsCategoryPresent = 0 Or Len(IsNull(re.' + rem.CategoryLocation + ', space(0))) = 0 Then rem.CategoryDefault 
                                                                        Else  re.' + rem.CategoryLocation + ' 
                                                                        End
                Left Join dbo.Customers c on c.CustomerId = Case 
                                                        When rem.IsCustomerIdPresent = 0 Or Len(IsNull(re.' + rem.CustomerIdLocation + ', space(0))) = 0 Then rem.CustomerDefault
                                                        Else re.' + rem.CustomerIdLocation + '
                                                        End

                Join dbo.Locations s on s.SourceLocationId = Case
                                                        When rem.IsLocationPresent = 0 Or Len(IsNull(re.' + rem.Location + ', space(0))) = 0 Then rem.LocationDefault
                                                        Else re.' + rem.Location + '
                                                        End


                Where rem.EventType = ' + Cast(rem.EventType as Varchar)

From dbo.SourceTable re
Join dbo.MappingTable rem on re.Event_Type = rem.EventType
Where rem.EventType = @EventType

Exec (@sql)

End

I am joining on to the Lookup tables and then using a case statement along with dynamic SQL to determine which column to use. So if a value is present then I pick out the column from the source table. If a value isn't present then I use a default value which ensures that I always get a FK value to insert in to my destination table.

You will also note that I have this check in my join too

Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0

This is because I found out that some columns weren't null but blank values. Which caused me a bit of grief too.

I have been running this for a few days and it seems to work pretty well.

Having said that, any comments would be greatly appreciated!

2
  • The SELECT @sql statement has FROM dbo.SourceTable -- this should be removed as with a denormalized mapping table, you only need to select a single row to set the SQL statement. If the goal was to check for existence of the event type, use EXISTS instead. Interesting approach, though. I suspect the single-statement solution I offered would be faster if you need more speed.
    – Jon Seigel
    Oct 19, 2012 at 17:15
  • @Jon Ah yes, you are totally right there. I am not sure how I didn't notice that. I may have used the SourceTable at some point and forgotten to remove it. Still tinkering and monitoring at the moment so I may well change the approach. I am processing ~100 event types which is taking ~2minutes which is well within my time constraints. Thanks for your input :-) Oct 19, 2012 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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