Normalized Data, Not

My boss whats me to collect data in a way that will allow him to view it in a non-normalized view...

name phone_type_1, sn_phone_type_1, phone_type_2, sn_phone_type_2, phone_type_3, sn_phone_type_3...

He does NOT want...

name, phone_type, sn_phone_type
Bill ATT XX44YY
Bill ATT QQ66TT
Bill DELL 77JJ88

So how can I collect the data in a normalized fashion, then display it in the format he desires? If possible I really didn't want to write code to step through the records splitting them up, Id like to use SQL.

Thanks in advance for your suggestions.Collect it normalized like you want to. Then go like this:

declare @.foo varchar(8000)

select @.foo = coalesce (@.foo + ',', '')
+ phone_type + ',' + sn_phone_type
from persontable where name = 'Bill'

select @.foo

or something like that.

Normalized Data base

I have been arguing with my partner if we shoud or not design a normalized
data base or denormalized. How, can we know when we should choose one or the
other?
Thanks a lot!
Lina,
I assume you are referring to designing a data warehouse since you are
posting in this group. There are three basic ways of designing the model.
Third Normal Form - This is the Inmon approach and works well data mining
and building down stream data marts. It's draw backs are usually higher
development, hardware, and software costs. It's should be used for large
data warehouses with multiple sources.
Star Schema - A special from of denormalized schema. This is the Kimbal
approach. It good in situations of combining multiple sources and the use
is intended to be more direct user access.
Denormalized - This should be used in data warehouses that really only have
one significant source and the warehouse will be used from more direct
reporting than anything else. It's relatively quick simple and cheap but
has issues with integration.
Whichever approach you choose to take realize that once reports and other
uses are written against it, changing the general approach is expensive.
Just opinions...
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
>I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
> the
> other?
> Thanks a lot!
|||I guess it depends on what type of denormalization. The more denormalized a
datawarehouse model is, the harder it will be to keep the data consistent as
new data is inserted or updated incrementally from the transactional
database. For example, if a person's zip code changes or a product is
associated with a new category, these changes must be updated in multiple
locations. However, if the datawarehouse is reloaded from scratch
periodically (similar to a reporting database), then it is less of an issue.
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
> I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
the
> other?
> Thanks a lot!
|||Hi Lina,
if you go to my downloads page on www.peternolan.com there are some
presentations, one of which is about data modeling and some newsletters
one of which is about data modeling.
There is something of a 'reglious' debate over modeling techniques in
DWing that has been going on for many years. I put my public opinion on
my FAQs page...
Current 'best practice' of publicly available information is to have an
archive layer in 3NF + Time variant model (plus stability analysis if
you need it) as well as an analytical later (dimensional). This is also
the most expensive practice......not everyone needs an archive layer
as described here...
The 'next generation' models I work with make the separate archive
layer redundant...I've written on this topic many times and the posts
are archived on http://www.datawarehousing.com/list.asp.
Best Regards
Peter Nolan
www.peternolan.com
sql

Normalized Data base

I have been arguing with my partner if we shoud or not design a normalized
data base or denormalized. How, can we know when we should choose one or the
other?
Thanks a lot!Lina,
I assume you are referring to designing a data warehouse since you are
posting in this group. There are three basic ways of designing the model.
Third Normal Form - This is the Inmon approach and works well data mining
and building down stream data marts. It's draw backs are usually higher
development, hardware, and software costs. It's should be used for large
data warehouses with multiple sources.
Star Schema - A special from of denormalized schema. This is the Kimbal
approach. It good in situations of combining multiple sources and the use
is intended to be more direct user access.
Denormalized - This should be used in data warehouses that really only have
one significant source and the warehouse will be used from more direct
reporting than anything else. It's relatively quick simple and cheap but
has issues with integration.
Whichever approach you choose to take realize that once reports and other
uses are written against it, changing the general approach is expensive.
Just opinions...
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
>I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
> the
> other?
> Thanks a lot!|||I guess it depends on what type of denormalization. The more denormalized a
datawarehouse model is, the harder it will be to keep the data consistent as
new data is inserted or updated incrementally from the transactional
database. For example, if a person's zip code changes or a product is
associated with a new category, these changes must be updated in multiple
locations. However, if the datawarehouse is reloaded from scratch
periodically (similar to a reporting database), then it is less of an issue.
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
> I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
the
> other?
> Thanks a lot!|||Hi Lina,
if you go to my downloads page on www.peternolan.com there are some
presentations, one of which is about data modeling and some newsletters
one of which is about data modeling.
There is something of a 'reglious' debate over modeling techniques in
DWing that has been going on for many years. I put my public opinion on
my FAQs page...
Current 'best practice' of publicly available information is to have an
archive layer in 3NF + Time variant model (plus stability analysis if
you need it) as well as an analytical later (dimensional). This is also
the most expensive practice......not everyone needs an archive layer
as described here...
The 'next generation' models I work with make the separate archive
layer redundant...I've written on this topic many times and the posts
are archived on http://www.datawarehousing.com/list.asp.
Best Regards
Peter Nolan
www.peternolan.com

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.