May 6, 2016 at 5:17 pm
Hello, I seem to be running into this issue a lot, and wondering if there's a better way. I'm not even really sure of the terminology, so not sure what to google. Here's my dilemma:
Say I have a large source table like (ReferralSource):
referral_id, referral_type, authorization_type, category_type, category_name, referral_date, referral_expiration, status_name, status_type, etc.
I want to pull out the dimension columns, and add my Surrogate Key (dimension_key) like:
dimension_key, referral_type, authorization_type, category_type, category_name, status_name, status_type
Then I want to pull out these columns to create my fact table and add the Surrogate Key like:
referral_id, referral_date, referral_expiration, dimension_key
BUT....
What methods do I have to find the surrogate key when creating my fact table? I find myself writing sloppy looking code like:
SELECT
a.referral_id, a.referral_date, a.referral_expiration,
Dim.dimension_key
FROM ReferralSource A
INNER JOIN Dim ON
isnull(Dim.referral_type,0) = ISNULL(A.referral_type,0) AND
isnull(Dim.authorization_type,0) = ISNULL(A.authorization_type,0) AND
isnull(Dim.category_type,0) = ISNULL(A.category_type,0) AND
isnull(Dim.status_name,0) = ISNULL(A.status_name,0) AND
isnull(Dim.status_type,0) = ISNULL(A.status_type,0)
Which works, but seems to add extra overhead, and doesn't seem very clean. This is especially bad if this is a ProfileDim, the JOIN ON conditions can get very long, and seems to be prone to errors.
Are there other ways of doing this? What might I google, or methodologies can I look up to learn my options, or is this pretty much the best and only way? I'm familiar with SSIS and BIDS but wasn't sure if either had a built in tool to do this sort of looking up.
Thanks!
May 9, 2016 at 5:40 am
Hello russds,
Do you use the SSDT?
In a package you can use the lookUp transformation-task. In my opinion it's the best way to get the surrogate key from dimensions.
If you use the lookUp task, you additionally are able to seperate source and destination from each other by using different connections.
Was this helpful?
Best Regards
May 9, 2016 at 9:52 am
Henning Rathjen (5/9/2016)
Hello russds,Do you use the SSDT?
In a package you can use the lookUp transformation-task. In my opinion it's the best way to get the surrogate key from dimensions.
If you use the lookUp task, you additionally are able to seperate source and destination from each other by using different connections.
Was this helpful?
Best Regards
Very helpful thank you! Do you by chance know of a BIDS alternative to this SSDT tool? I'm currently using BIDS. But this definitely gives me something to start googling. Thanks!
May 9, 2016 at 11:21 pm
Sorry, I started with SQL 2008 R2 and BIDS in 2012. 3 years ago we migrated to SQL Server 2012 (and SSDT). So, I know none alternative but I think it's unnecessary.
Also in BIDS take a dataflow task. In the dataflow you are able to take sources, destinations and transformations. In the lookup transformation task you are able to select the dimension or to write a select statement also. I think it's all you need.
Before I started in SQL Server I used IBM InfoSphere - I'll never go back 🙂
May be it takes some time, to feel like home in BIDS 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply