normalizing data warehouse

Here’s a story. I’m in a warehouse and I’m to normalize 8M records
The employee dimesion is composed of the following fields
Employee_key, employee_no, center_id, date_hired and other fields
What I want is to
1. Select disctint employee_no, centerid, datehired
Plus
2. the “top 1 employee_key” per group if grouped by
(employee_no,centerid,datehired)
3. no cursor pls.
the resultset
employee_no, centerid, datehired, employee_key --<--top 1
thank you, thank you…
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787does this help? You could consider using min() function instead...
select
max(employee_key) as EmpKey,
employee_no,
centerid,
datehired
from
table
group by
employee_no, centerid, datehired
MC
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:AF491592-2324-42A0-B397-FBA301F3DD6F@.microsoft.com...
> Here's a story. I'm in a warehouse and I'm to normalize 8M records
> The employee dimesion is composed of the following fields
> Employee_key, employee_no, center_id, date_hired and other fields
> What I want is to
> 1. Select disctint employee_no, centerid, datehired
> Plus
> 2. the "top 1 employee_key" per group if grouped by
> (employee_no,centerid,datehired)
> 3. no cursor pls.
> the resultset
> employee_no, centerid, datehired, employee_key --<--top 1
> thank you, thank you.
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787

Normalize your database: NOT OPTIONAL!

I've been away for a while but now that I'm back one if the first things I noticed is how many of the problems on this forum could have been easily avoided by simply normalizing the database in question.

For those of you without formal SQL training, know this: Youhave to normalize your database. This is part of the process of database development. You can not just whack together a few tables in whatever way is the easiest for the problem at hand, because eventually your requirements will expand and then you need your data to be flexible.

Seriously, in the last 3 weeks I've seen many many questions where it is obvious that the problem is a flawed DB design, but all the so-called SQL gurus here will just answer the question without addressing the fact that the problem lies much deeper.

Here's a link I found on Google:
http://www.cse.unsw.edu.au/~cs3710/PMmaterials/Tutorials/normalise.htmGood point, but a qualifier :

sometimes de-normalizing is required or at least advisable from a performance point of view, though your point holds in thevast majority of cases. Your statement holds for virtually anoyone asking questions in this forum though.

j|||Yes, although your starting point is always a normalized DB. If you then realize you need to de-normalize for performance, then so be it. Never the other way around though.|||::sometimes de-normalizing is required or at least advisable from a performance point of
::view

Yes, and professionals call this thn a datawarehouse, where completly different rules apply.

Interesting nough I have never seen questions about data warehousing here, evryone is just dealing with transactional (OLTP) databases.|||It's a given to normalize your database to a certain degree. It's faster to select on an integer or bit based where clause than it would a float or varchar.

However, Atrax is correct, you can't really say it's the best choice since it's not ALWAYS the case. Especially when you have a normalized table of more than 15 tables, and some of those tables only hold 3-5 records of two fields, one being an identity.

Let's take for instance states. States don't change, and a char(2) is all you really need to store that data. Why would you have another field that takes up 4bytes to store an identity field FK of the normalized table holding "states"?

So Pierre, and valiant as your intentions were, it's still considered at the disgretion of the developer/dba to maintain how these tables will be designed for optimal storage as well as retrieval.|||I'm not saying take your DB to 5NF or DKNF, just 3NF is fine. What you're illustrating is still a 3NF normalized DB, and thus is still OK for just about any purpose out there. I'm not saying your DB should always be 100% normalized. I'm just pointing out that many developers simply skip that altogether.

All I'm saying is no normalization is BAD. E.g.

UserID int, Languages varchar(100)
(1, '1, 2, 5, 7')

I agree with your example, I personally use non-integer fields as PKs often. And if updates are possible but unlikely, you can even use cascade updates. But the DB is still normalized and should still have FKs etc.|||The problem is, many of the people on this forum don't even know what normalization is...

Heck, in the professional world there are a lot of people that don't understand this.

I am on a project where a programmer needed to know what what type of item something was.

Instead of adding a table of the types, and a foreign key to his original table, he added a column for each of the types to the table, and had a "Y" in the correct one and "N" in all the incorrect ones...|||Probably someone with COBOL experience...|||I think that Pierre's original point, and his intention, are solid. Even though there are certainly exceptions, many people who post here would benefit significantly from learning the basics of normalization, and the link looks like a solid tutorial. Learn the rules, then learn how (and when) to break the rules.

Normalize this data

How would you normalize this data into a useable database?
Customer Name City Contact Address
Contact First Name Contact Phone Number Contact Fax Number
Billing Address Country Contact Email
Customer Number Contact Title User Name
Contact Last Name Shipping Address Password
Postal Code Contact Cell Phone User Role PrivilegePut it in a table?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>|||On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
> Put it in a table?
> <billsahi...@.yahoo.com> wrote in message
> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>
> > How would you normalize this data into a useable database?
> > Customer Name City Contact Address
> > Contact First Name Contact Phone Number Contact Fax Number
> > Billing Address Country Contact Email
> > Customer Number Contact Title User Name
> > Contact Last Name Shipping Address Password
> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
> - Show quoted text -
Two or more tables, and indicate primary and foreign keys. This was a
test item and that is all the information given.|||<billsahiker@.yahoo.com> wrote in message
news:1192908678.033282.164490@.k35g2000prh.googlegroups.com...
> On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
>> Put it in a table?
>> <billsahi...@.yahoo.com> wrote in message
>> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>>
>> > How would you normalize this data into a useable database?
>> > Customer Name City Contact Address
>> > Contact First Name Contact Phone Number Contact Fax Number
>> > Billing Address Country Contact Email
>> > Customer Number Contact Title User Name
>> > Contact Last Name Shipping Address Password
>> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
>> - Show quoted text -
> Two or more tables, and indicate primary and foreign keys. This was a
> test item and that is all the information given.
>
How about doing your own homework...
--
David Portas|||Why do you assume it is school work? It was a job application test I
took yesterday.
> How about doing your own homework...
> --
> David Portas- Hide quoted text -
> - Show quoted text -|||Ah, because you told the truth, I will help. And there is little difference
between an employment test and homework. You should have said so in the
first place.
Basically, it looks like they got together and looked for ways to screw
applicants up. They probably thought they were being cute mixing up and
eleminating necessary attributes too.
The first thing that jumps out is that you're storing people and addresses,
so that's two tables:
People is 1-to-many to Addresses (more on addresses later).
Second (and it's glaring): "Customer Name" vs. "Contact First Name" &
"Contact Last Name"
These columns should be: "Prefix", "First Name", "Middle Name",
"Last Name" & "Suffix".
(Mr. John Adam Smith, Sr)
Third Customers have "User Role Privilege", "User Name" and Password,
Contacts do not. Therefore, some people have attributes that others do not.
This suggests storing Customers and Contacts seperatly.
--
If you take the list of attributes they provided and arrange it, more things
become apparent:
Customer Number
User Name
Password
User Role Privilege
Customer Name
Billing Address
City
Postal Code
Country
Shipping Address
Contact Title
Contact First Name
Contact Last Name
Contact Address
Contact Phone Number
Contact Cell Phone
Contact Fax Number
Contact Email
Comments:
"Billing Address" and "Shipping Address" are clearly attributes of the
Customer. However, "City", "Postal Code" (Zip Code) & "Country" are vague.
Are they for Billing, Shipping, or Contact. In fact, you really need those
for each address. Same for attributes that appear in Contact, you should
have those for Customers too. In general, they really messed with the
attribute list.
Also, what "State" is the address in?
People (not including addresses):
There are two approaches here: store the "Contact" as attributes in
Customer, or create three tables: Customer, Contact & People, where "People"
is the parent table. You'll probably have to define your own PK for people
too and store it as a FK in Customer alone with the Customer Only info.
Addresses:
Create a seperate address table with all approporiate attributes. Then
you need a way to link people to addresses. You can do this by adding a
column to one of the people tables, or creating a many-to-many relationship
between people/customer/contact tables and addresses.
Since you have three distinct address types, you might want an AddressType
column in addresses constrained either by a lookup table, or a check
constraint. There are pluses and minuses for each. I prefer lookup tables,
but I'm a 3NF+ bigot.
Well, that covers the basics. Can you normalize the tables now?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>

Normalize multivalue field?

Hi,
I have a table I'm importing from access; the application that used
the table has some fields that are multivalued, separated by either
a ; or the sequence /* Ugly.
I can write a C# app to take the columns and stuff the data into my
normalized schema, but I was wondering if there was any good way to do
it in sql.
Thanks,
AndyOn Sep 7, 12:57 pm, Andy <an...@.med-associates.com> wrote:
> Hi,
> I have a table I'm importing from access; the application that used
> the table has some fields that are multivalued, separated by either
> a ; or the sequence /* Ugly.
> I can write a C# app to take the columns and stuff the data into my
> normalized schema, but I was wondering if there was any good way to do
> it in sql.
> Thanks,
> Andy
Look up Erland Sommarskog's article "arrays and Lists in SQL"|||The best approach is to clean up the data to represent one value per column.
If, by any chance, you are forced to deal with such designs, you'll have to
resort to one of the string parsing approaches. Some of them can be found
at:
www.projectdmx.com/tsql/sqlarrays.aspx
www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith

Normalize data?

I have two columes F1 and F2
Both are NUMBER but were orginally imported from a TXT file.
Is there any way to ENSURE, that there are no spaces and such inside the
fields to ensure proper matching? i.e. if I am trying to match F2 with F2
and one records is "1" and the second is "1 " (space added to the end) it
won't match, how do i remove spaces like this?
DennisIf both columns are numeric datatypes then they won't have leading or
trailing spaces. I don't know what you mean by "Both are NUMBER". There
is no built-in datatype called "NUMBER". Maybe you have a user-defined
type in which case you could just change the column to be a proper
NUMERIC or INTEGER for example.
David Portas
SQL Server MVP
--|||db wrote:
> I have two columes F1 and F2
> Both are NUMBER but were orginally imported from a TXT file.
> Is there any way to ENSURE, that there are no spaces and such inside
> the fields to ensure proper matching? i.e. if I am trying to match
> F2 with F2 and one records is "1" and the second is "1 " (space added
> to the end) it won't match, how do i remove spaces like this?
> Dennis
If your NUMBER columns are actually a character datatype, you should
trim your values before you insert into the table. You can also issue a
trim on the columns now to remove any leading white space.
David Gugick
Imceda Software
www.imceda.comsql

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__Stephen
I got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid = (
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid => (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

Normalize data issue.

Friend has his part as PartNum. He manufactures replacement parts, and he
wants to be able to search on the OEM # and come to his part #.
PartNum Description
ABC123 800100-1 -2 -3 -5 -9 -11 -12.
Final output in my cross-reference table should look like:
ABC123 800100-1
ABC123 800100-2
ABC123 800100-3
ABC123 800100-5
ABC123 800100-9
ABC123 800100-11
ABC123 800100-12
ABC234 800101-1
ABC234 800101-2
ABC234 800101-3
Can that extraction be done via T-SQL, or will I have to do it in a .NET
job?
TIA
__Stephen>> Can that extraction be done via T-SQL, or will I have to do it in a .NET
One common trick in t-SQL is to use a table/view/derived table/CTE with
sequentially incrementing numbers. There are several ways you can generate
one, search the google archives of this newsgroup to find some or post back.
Once you have such a table, assuming the string format is consistent, the
cleanup can be done like:
SELECT p_num,
prefix + '-' +
SUBSTRING(value, n, CHARINDEX('-', value + '-', n) - n)
FROM ( SELECT PartNum, LEFT( descr, CHARINDEX( '-', descr ) - 1 ),
REPLACE( RIGHT( descr, LEN( descr) -
CHARINDEX( '-', descr )) , SPACE(1), '' )
FROM tbl ) T ( p_num, prefix, value ),
Nbrs
WHERE SUBSTRING( '-' + value, n, 1 ) = '-'
AND LEN(value) + 1 > n;
Here Nbr is the table of numbers with column name n and tbl is the table
where you have the original data.
Anith|||Thanks.
__Stephen
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23t$YdwEVGHA.6112@.TK2MSFTNGP10.phx.gbl...
> One common trick in t-SQL is to use a table/view/derived table/CTE with
> sequentially incrementing numbers. There are several ways you can generate
> one, search the google archives of this newsgroup to find some or post
> back.
> Once you have such a table, assuming the string format is consistent, the
> cleanup can be done like:
> SELECT p_num,
> prefix + '-' +
> SUBSTRING(value, n, CHARINDEX('-', value + '-', n) - n)
> FROM ( SELECT PartNum, LEFT( descr, CHARINDEX( '-', descr ) - 1 ),
> REPLACE( RIGHT( descr, LEN( descr) -
> CHARINDEX( '-', descr )) , SPACE(1), '' )
> FROM tbl ) T ( p_num, prefix, value ),
> Nbrs
> WHERE SUBSTRING( '-' + value, n, 1 ) = '-'
> AND LEN(value) + 1 > n;
> Here Nbr is the table of numbers with column name n and tbl is the table
> where you have the original data.
> --
> Anith
>

Normalize data

Hi,

I have a table like this:

Pk_QuestionID

Question

Choice1

Choice2

Choice3

Choice4

correctChoice

1

XYZ?

a

b

C

d

3

This has to be transferred to a more normalized schema like this

Pk_QuestionID

Question

1

XYZ?

And

Pk_OptionID

Fk_QuestionID

Choice_Description

isCorrect

1

1

a

0

2

1

b

0

3

1

c

1

4

1

d

0

We can use either the unpivot-merge or a SQL Union ALL statement to achieve most of this requirement. I am unable to figure out how to transfer the ‘correctChoice’ column. Is there a way to achieve this?

Thanks

This looks like a classic unpivot operation to me. Have you looked at the UNPIVOT component?

-Jamie

|||

Jamie Thomson wrote:

This looks like a classic unpivot operation to me. Have you looked at the UNPIVOT component?

-Jamie

I believe he has... The issue is his "correctchoice" column in that it's a column number, not a value. (WHY?)

Never-the-less, this is easy. Load your destination table first, without regard to the iscorrect column.

NOTE: I assume that in the dest table, the Pk_OptionID and Fk_QuestionID fields are the table's primary key, otherwise, you're in for a world of hurt and this advice won't work.

Now, in a separate dataflow, pull the data from your original source table, only carrying two columns, the primary key (Pk_QuestionID) and correctchoice. Next up, is the LOOKUP transform. Simply join Pk_QuestionID to the foreign key field (Fk_QuestionID) of the dest table, and also join the correctchoice field to the pk field of the dest table (PK_OptionID). (If you have a match in the lookup, that row is the correct choice)

Send the flow to the OLE DB COMMAND transform and perform an update like:
update destination set iscorrect = 1 where Pk_OptionID = ? and Fk_QuestionID = ?. Map the fields accordingly with your input columns.|||

Hi Phil,

Unfortunately only pk_OptionID is the primary key for that table.

Thanks

|||

kasi124 wrote:

Hi Phil,

Unfortunately only pk_OptionID is the primary key for that table.

Thanks

Well, then, I think you need to reevaluate the structure of your tables/data. If your "correctchoice" column was a value, and not a column indicator, you'd be okay, but alas you're not. If you simply add the FK in the dest table to its primary key structure, and then reset the Pk_OptionID field everytime the Fk_QuestionID field changes, you'd be in a perfect world.

The way your data is structured now, it sounds really complex. You might be able to achieve it with a custom script, but I don't even know where to start. Perhaps someone else here might have an idea on that.

As it is, your destination table hasn't reached full normalization yet.|||

You are correct, both my source and destination data stores are bad designs (not just specific to this issue), but there is very small scope for a redesign.

The pk_optionID column is an identity. I dont know how this will help but may be a different set of values will help someone strike gold in this issue.

Source:

Pk_QuestionID

Question

Choice1

Choice2

Choice3

Choice4

correctChoice

101

XYZ?

a

b

C

d

3

This has to be transferred to a more normalized schema like this

Pk_QuestionID

Question

134

XYZ?

And

Pk_OptionID

Fk_QuestionID

Choice_Description

isCorrect

533

134

a

0

534

134

b

0

535

134

c

1

536

134

d

0

|||I hear ya on the issues, and I completely follow your structure.
I do have a solution though, and it doesn't require any scripting. Fun having free Fridays at work to learn other people's issues and trying to solve them. Stretching the brain!
Anyway, here goes:
Throw your source into a derived column transform. You will be adding a new column, altered_correct_choice, which has the following expression:
correctchoice == 1 ? choice1 : correctchoice == 2 ? choice2 : correctchoice == 3 ? choice3 : choice4
Then, perform an unpivot on the choicen fields. I called the output column for each field 'data'. Next, throw on another derived column after the unpivot. In here, we compare the altered_correct_choice value to the value in the data column. If they match, we set 'iscorrect' (a new column) to 1 else 0:
altered_correct_choice == Choice_Description ? 1 : 0
Feed all of that into your destination and map appropriately.
Here's what I did at a high level if that didn't make sense. Take the source in and use a derived column transform to lookup the "correct" value for the correctChoice field instead of using the column number. So using your new data above, the value coming out of the derived column transform would be 'c' instead of 3.
Next we unpivot the data. You already have this working.
Next we take all of that and make a comparison against each row (4 now instead of 1). If the value of the Choice_Description column matces that of the output column from the first derived column transform, set iscorrect to 1, otherwise 0.
Next up is the destination adaptor. Map your fields appropriately.
Let me know if that doesn't make sense as I was able to get it working for me.
Phil
|||

Thanks Phil, that worked like a charm.

However my problems are not yet. I am trying to figure out how to unpivot using multiple keys, if that is even possible.

sql

Normalize data

Hi,

I have a table like this:

Pk_QuestionID

Question

Choice1

Choice2

Choice3

Choice4

correctChoice

1

XYZ?

a

b

C

d

3

This has to be transferred to a more normalized schema like this

Pk_QuestionID

Question

1

XYZ?

And

Pk_OptionID

Fk_QuestionID

Choice_Description

isCorrect

1

1

a

0

2

1

b

0

3

1

c

1

4

1

d

0

We can use either the unpivot-merge or a SQL Union ALL statement to achieve most of this requirement. I am unable to figure out how to transfer the ‘correctChoice’ column. Is there a way to achieve this?

Thanks

This looks like a classic unpivot operation to me. Have you looked at the UNPIVOT component?

-Jamie

|||

Jamie Thomson wrote:

This looks like a classic unpivot operation to me. Have you looked at the UNPIVOT component?

-Jamie

I believe he has... The issue is his "correctchoice" column in that it's a column number, not a value. (WHY?)

Never-the-less, this is easy. Load your destination table first, without regard to the iscorrect column.

NOTE: I assume that in the dest table, the Pk_OptionID and Fk_QuestionID fields are the table's primary key, otherwise, you're in for a world of hurt and this advice won't work.

Now, in a separate dataflow, pull the data from your original source table, only carrying two columns, the primary key (Pk_QuestionID) and correctchoice. Next up, is the LOOKUP transform. Simply join Pk_QuestionID to the foreign key field (Fk_QuestionID) of the dest table, and also join the correctchoice field to the pk field of the dest table (PK_OptionID). (If you have a match in the lookup, that row is the correct choice)

Send the flow to the OLE DB COMMAND transform and perform an update like:
update destination set iscorrect = 1 where Pk_OptionID = ? and Fk_QuestionID = ?. Map the fields accordingly with your input columns.|||

Hi Phil,

Unfortunately only pk_OptionID is the primary key for that table.

Thanks

|||

kasi124 wrote:

Hi Phil,

Unfortunately only pk_OptionID is the primary key for that table.

Thanks

Well, then, I think you need to reevaluate the structure of your tables/data. If your "correctchoice" column was a value, and not a column indicator, you'd be okay, but alas you're not. If you simply add the FK in the dest table to its primary key structure, and then reset the Pk_OptionID field everytime the Fk_QuestionID field changes, you'd be in a perfect world.

The way your data is structured now, it sounds really complex. You might be able to achieve it with a custom script, but I don't even know where to start. Perhaps someone else here might have an idea on that.

As it is, your destination table hasn't reached full normalization yet.|||

You are correct, both my source and destination data stores are bad designs (not just specific to this issue), but there is very small scope for a redesign.

The pk_optionID column is an identity. I dont know how this will help but may be a different set of values will help someone strike gold in this issue.

Source:

Pk_QuestionID

Question

Choice1

Choice2

Choice3

Choice4

correctChoice

101

XYZ?

a

b

C

d

3

This has to be transferred to a more normalized schema like this

Pk_QuestionID

Question

134

XYZ?

And

Pk_OptionID

Fk_QuestionID

Choice_Description

isCorrect

533

134

a

0

534

134

b

0

535

134

c

1

536

134

d

0

|||I hear ya on the issues, and I completely follow your structure.
I do have a solution though, and it doesn't require any scripting. Fun having free Fridays at work to learn other people's issues and trying to solve them. Stretching the brain!
Anyway, here goes:
Throw your source into a derived column transform. You will be adding a new column, altered_correct_choice, which has the following expression:
correctchoice == 1 ? choice1 : correctchoice == 2 ? choice2 : correctchoice == 3 ? choice3 : choice4
Then, perform an unpivot on the choicen fields. I called the output column for each field 'data'. Next, throw on another derived column after the unpivot. In here, we compare the altered_correct_choice value to the value in the data column. If they match, we set 'iscorrect' (a new column) to 1 else 0:
altered_correct_choice == Choice_Description ? 1 : 0
Feed all of that into your destination and map appropriately.
Here's what I did at a high level if that didn't make sense. Take the source in and use a derived column transform to lookup the "correct" value for the correctChoice field instead of using the column number. So using your new data above, the value coming out of the derived column transform would be 'c' instead of 3.
Next we unpivot the data. You already have this working.
Next we take all of that and make a comparison against each row (4 now instead of 1). If the value of the Choice_Description column matces that of the output column from the first derived column transform, set iscorrect to 1, otherwise 0.
Next up is the destination adaptor. Map your fields appropriately.
Let me know if that doesn't make sense as I was able to get it working for me.
Phil
|||

Thanks Phil, that worked like a charm.

However my problems are not yet. I am trying to figure out how to unpivot using multiple keys, if that is even possible.