MS SQL Server DTS - SSIS and populating fact table surrogate keys

Author

SSIS and populating fact table surrogate keys

KoryS

2005-09-21, 8:24 pm

I've scoured the 'net and can't find a solution to my problem.

Using SSIS, I am trying to load a fact table with surrogate key values.
But the key is based on $ amounts, so my dimension table contains a
"Floor" and "Ceiling" column for defined ranges.

Example of dimension table (11 rows):

Key Floor Ceiling Description Sort
--- ----- --------- ------------- ----
1 0 500 $0 - $500 1
2 500.01 1000 $500 - $1,000 2
3 1000.01 2000 $1,000 - $2,000 3
....
10 10000.01 99999999 > $10,000 10
-1 Null Null Unknown 999

Because the fact table stores values to the penny, there are
theoretically millions of possible values, so creating a dimension key
for every discrete value is impractical.

The Lookup Transformation only works with exact matches, so that option
is out.

The frustrating thing is, our company uses both Informatica and Data
Integrator (from Business Objects), and they have lookup functions that
handle this without custom programming. Because I use Analysis
Services, SQL Server, and Reporting Services, I wanted to standardize
on a BI toolset so would prefer to use SSIS when it becomes available,
but little issues like this make the case harder to justify.

Any suggestions would be greatly appreciated!

Thanks

Kory

Allan Mitchell

2005-09-21, 8:24 pm

OK So what I see you needing to do is this

As the row comes in you need to take a monetary value from your source
and look up a key in a key table. The keys are allocated to ranges of
values not discrete values.

Let's go through how to do this

Build the tables

CREATE TABLE KeyProvider
(
KeyValue int not null primary key,
floorvalue decimal(10,2),
ceilingvalue decimal(10,2),
Description varchar(255),
Sort int
)

GO

CREATE TABLE DataTable(col1 int , ColValueToFindInKeyT
able
decimal(10,2))

GO

Add some "key" data

INSERT KeyProvider(KeyValue
,floorvalue,ceilingv
alue,description,sor
t)
VALUES(1,0,500,'0-500',1)

Insert a "source" row

INSERT DataTable VALUES(1,15.2)

I will presume the only part that has you foxed is the Lookup.

On the front page add a ref to the KeyProvider table
Now in the columns tab join the ColValueToFindInKeyT
able field to the
floorvalue field. This just gives us a SQL Statement which we need
next, it is not important what this is as we will change it now.
In the Advanced tab enable "Enable memory restriction" and only check
"Modify SQL Statement"

Change the statement to

select * from
(select * from [dbo].[KeyProvider]) as refTable
where [refTable].[floorvalue] < ? and ceilingvalue >?

Now hit the parameters button and map the ColValueToFindInKeyT
able field
to the two parameters.

Should work (did for me)

Allan

"KoryS" <kskistad@hotmail.com> wrote in message
news:1127334753.925815.58620@g14g2000cwa.googlegroups.com:

> I've scoured the 'net and can't find a solution to my problem.
>
> Using SSIS, I am trying to load a fact table with surrogate key values.
> But the key is based on $ amounts, so my dimension table contains a
> "Floor" and "Ceiling" column for defined ranges.
>
> Example of dimension table (11 rows):
>
> Key Floor Ceiling Description Sort
> --- ----- --------- ------------- ----
> 1 0 500 $0 - $500 1
> 2 500.01 1000 $500 - $1,000 2
> 3 1000.01 2000 $1,000 - $2,000 3
> ...
> 10 10000.01 99999999 > $10,000 10
> -1 Null Null Unknown 999
>
> Because the fact table stores values to the penny, there are
> theoretically millions of possible values, so creating a dimension key
> for every discrete value is impractical.
>
> The Lookup Transformation only works with exact matches, so that option
> is out.
>
> The frustrating thing is, our company uses both Informatica and Data
> Integrator (from Business Objects), and they have lookup functions that
> handle this without custom programming. Because I use Analysis
> Services, SQL Server, and Reporting Services, I wanted to standardize
> on a BI toolset so would prefer to use SSIS when it becomes available,
> but little issues like this make the case harder to justify.
>
> Any suggestions would be greatly appreciated!
>
> Thanks
>
> Kory

Allan Mitchell

2005-09-21, 8:24 pm

As an aside to this

For rows that do not have a lookup value then by deafult the component
will fail so you have two options

Change the error handler to ignore the error. In this case the rows
with the NULL as the key value will flow with the good rows. You can
use a conditional split to filter these out.

Change the error handler to redirect row (error output) and handle it on
that stream.

Allan

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OkWg$QwvFHA.2516@TK2MSFTNGP12.phx.gbl:
[color=darkred]
> OK So what I see you needing to do is this
>
>
> As the row comes in you need to take a monetary value from your source
> and look up a key in a key table. The keys are allocated to ranges of
> values not discrete values.
>
> Let's go through how to do this
>
> Build the tables
>
> CREATE TABLE KeyProvider
> (
> KeyValue int not null primary key,
> floorvalue decimal(10,2),
> ceilingvalue decimal(10,2),
> Description varchar(255),
> Sort int
> )
>
> GO
>
>
> CREATE TABLE DataTable(col1 int , ColValueToFindInKeyT
able
> decimal(10,2))
>
> GO
>
> Add some "key" data
>
> INSERT KeyProvider(KeyValue
,floorvalue,ceilingv
alue,description,sor
t)
> VALUES(1,0,500,'0-500',1)
>
> Insert a "source" row
>
> INSERT DataTable VALUES(1,15.2)
>
>
> I will presume the only part that has you foxed is the Lookup.
>
> On the front page add a ref to the KeyProvider table
> Now in the columns tab join the ColValueToFindInKeyT
able field to the
> floorvalue field. This just gives us a SQL Statement which we need
> next, it is not important what this is as we will change it now.
> In the Advanced tab enable "Enable memory restriction" and only check
> "Modify SQL Statement"
>
> Change the statement to
>
>
> select * from
> (select * from [dbo].[KeyProvider]) as refTable
> where [refTable].[floorvalue] < ? and ceilingvalue >?
>
>
> Now hit the parameters button and map the ColValueToFindInKeyT
able field
> to the two parameters.
>
> Should work (did for me)
>
> Allan
>
>
>
>
>
> "KoryS" <kskistad@hotmail.com> wrote in message
> news:1127334753.925815.58620@g14g2000cwa.googlegroups.com:
>
>
> values.
> option
> that

KoryS

2005-09-22, 3:23 am

Now I am intrigued Allan... because this is the first approach I tried
and got an "Invalid" transformation error. I will try this again
tomorrow and let you know what happens.

Thanks!

Kory

KoryS

2005-09-22, 11:24 am

Well, that worked! What initially tripped me up was a combination of
things: data type conversion issues (I couldn't link the tables
initially because the fact table was using a numeric(6,0) and I tried
to link to a double) so I used a Data Conversion transformation, which
allowed me to link them, but then I was getting errors for some other
reason (invalid input/output columns?)

I finally decided to change my datatype in the lookup table to match
the reference key in the fact table, then deleted my lookup
transformation and readded it (SSIS was persisting the metadata and did
not recognize I had changed the datatype in the source) and everything
seemed to work!

Thanks again Allan.

Kory

Sponsored Links