Normalization question

I was asked by a client how I would normalize some data that they had. An
example follows:
XIBMH002602382,XIBMH005,D:\005\00002382.TIF,
XIBMH002602383,XIBMH005,D:\005\00002383.TIF,
XIBMH002602384,XIBMH005,D:\005\00002384.TIF,
XIBMH002602385,XIBMH005,D:\005\00002385.TIF,
XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
XIBMH002700003,XIBMH005,D:\005\00002388.TIF,
XIBMH002700004,XIBMH005,D:\005\00002389.TIF,
XIBMH002700005,XIBMH005,D:\005\00002390.TIF,
XIBMH002700006,XIBMH005,D:\005\00002391.TIF,
XIBMH002700007,XIBMH005,D:\005\00002392.TIF,
Where:
a. the first piece of data (e.g. XIBMH002602382) is the control
number identifying a scanned image.
b. the second piece of data (e.g. XIBMH005) is the volume name
for the CD the images are stored on.
c. the third piece of data (e.g. D:\005\00002382.TIF) is the
fully qualified filename of the image file.
d. the fourth piece of data (which is Y if it is present) says
whether this image is the first page of a multiple-page document.
Would you put the Volume name in another table with a keyed field and maybe
the Drive and Folder into another, or should the data be kept into one
table?
Just asking for your thoughts on what you would do with this data
ThanksI assume that the table is modeling a filename. The only concern I see is
if the directory name is functionally dependent on the volume name , it
would be better to have two tables:
file
===
control_number (pk)
--
volume (foreign key to volume table)
filename
first_page_of_multipage
volume
=====
volume (pk)
--
root directory
What isn't clear is the multipage thing:

> XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
> XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
>
Dos this mean there are multiple pages stored in the tif? If so, a better
way to do this would be to have an attribute of number_of_pages, but the yes
or no thing will work. If the pages are different files, then I would think
storing these filenames in the table would be better.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:%23qmK7zlCFHA.4072@.TK2MSFTNGP10.phx.gbl...
>I was asked by a client how I would normalize some data that they had. An
>example follows:
> XIBMH002602382,XIBMH005,D:\005\00002382.TIF,
> XIBMH002602383,XIBMH005,D:\005\00002383.TIF,
> XIBMH002602384,XIBMH005,D:\005\00002384.TIF,
> XIBMH002602385,XIBMH005,D:\005\00002385.TIF,
> XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
> XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
> XIBMH002700003,XIBMH005,D:\005\00002388.TIF,
> XIBMH002700004,XIBMH005,D:\005\00002389.TIF,
> XIBMH002700005,XIBMH005,D:\005\00002390.TIF,
> XIBMH002700006,XIBMH005,D:\005\00002391.TIF,
> XIBMH002700007,XIBMH005,D:\005\00002392.TIF,
>
>
> Where:
> a. the first piece of data (e.g. XIBMH002602382) is the
> control number identifying a scanned image.
> b. the second piece of data (e.g. XIBMH005) is the volume name
> for the CD the images are stored on.
> c. the third piece of data (e.g. D:\005\00002382.TIF) is the
> fully qualified filename of the image file.
> d. the fourth piece of data (which is Y if it is present) says
> whether this image is the first page of a multiple-page document.
>
>
> Would you put the Volume name in another table with a keyed field and
> maybe the Drive and Folder into another, or should the data be kept into
> one table?
>
> Just asking for your thoughts on what you would do with this data
>
> Thanks
>
>|||>> I was asked by a client how I would normalize some data that they had.
If this data is in a table with three columns, I would suggest you ask your
client why he considers this data not normalized. You should have the clue
right then.
It all depends on how the business model distingushes one set of attributes
as a single fact from another. Do you have a need to update/insert/delete a
volume name without affecting the drive and folder information or vice
versa? Based on the consideration that a volume name determines the
directory, Louis' suggestion is a reasonable one.
Anithsql

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,
It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,
|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.

Non-additive measure - Actual and Average

I am working on a project at a manufacturing client. The measure I'm having trouble with is LeadTime (the number of days from manufacturing schedule to completion). It is used in a calculation to determine over/under inventory levels.

I need it to use the actual LeadTime for the calc at the SKU level and the average LeadTime at all of the aggregate levels.

Thanks for the help,

Dave

Hello! I am not sure about what you mean with SKU level but if you have two dates in the fact table, ManufacturingScheduleDate and ManufacturingCompletionDate, you can make a named calculation in the data source view(Analysis Services 2005) or a calculation in the ETL-process with SSIS.

Use the TSQL function DATEDIFF() for that and the difference between the MScheduleDate and the MCompletionDate.

I am not sure about your problem with the averages and what you are doing averages of?

You should be able to solve this with the MDX AVG() function.

HTH

Thomas Ivarsson

|||

SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product.Here is a table that I hope helps:

LeadTime

Product

Actual

Average

1001

3

4

1002

4

4

1003

5

4

Sub-total Sum

12

Sub-total Avg

4

When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level.If I set the measure to Average then the calc is wrong at the product and right at the aggregate level.

I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level.

Dave

|||

Hello! Can you also indicate the expected values in the table for Actual and Average Lead time?

From what I have seen with other clients is that you have a manufacturing order id for each production of a product.

It is not a part of your table but can it be a part of the problem?

Regards

Thomas Ivarsson

|||

Here is a little description of the table from my last post.Three product ID’s (1001, 1002, 1003) with actual LeadTime for each product in days (3, 4, 5 – respectively).

The LeadTime measure is used with other measures (On-hand, Daily demand, Safety stock, etc.) in an inventory over/under calculation.But, LeadTime is the only non-additive measure.Because of that the calculation is correct at the Product ID level (3, 4, 5), but wrong at the aggregate level (12).If I set the measure to Average LeadTime at the Product level is wrong (4, 4, 4), but the aggregate is correct (4).I’m looking for a solution that gives me the correct calculation at all levels

This project is only dealing with inventory levels.Orders are not a part of the calculation except as a part of the Daily Demand figure.

non default port - client cannot connect

I have changed the default port away from 1433 on sql server 2005. Now I
cannot connect from pc. What do I need to change on the client pc?
Thanks
Connect by adding a comma and port number after machine name, like:
machinename,1456
Or use cliconfg.exe to add an alias and specify the port number in that alias.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:0A38D0A1-E40F-4A97-93D6-5C288E867802@.microsoft.com...
>I have changed the default port away from 1433 on sql server 2005. Now I
> cannot connect from pc. What do I need to change on the client pc?
> Thanks

non default port - client cannot connect

I have changed the default port away from 1433 on sql server 2005. Now I
cannot connect from pc. What do I need to change on the client pc?
ThanksConnect by adding a comma and port number after machine name, like:
machinename,1456
Or use cliconfg.exe to add an alias and specify the port number in that alia
s.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:0A38D0A1-E40F-4A97-93D6-5C288E867802@.microsoft.com...
>I have changed the default port away from 1433 on sql server 2005. Now I
> cannot connect from pc. What do I need to change on the client pc?
> Thanks

non default port - client cannot connect

I have changed the default port away from 1433 on sql server 2005. Now I
cannot connect from pc. What do I need to change on the client pc?
ThanksConnect by adding a comma and port number after machine name, like:
machinename,1456
Or use cliconfg.exe to add an alias and specify the port number in that alias.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:0A38D0A1-E40F-4A97-93D6-5C288E867802@.microsoft.com...
>I have changed the default port away from 1433 on sql server 2005. Now I
> cannot connect from pc. What do I need to change on the client pc?
> Thanks

No such interface supported

I receive "No such interface supported" in SQL Server 2005, when:

1. on server, from my desktop client: attempting to create a maintenance plan within Management Studio without the wizard (with the wizard, works fine)

2. trying to copy, using SMO, an entire database from the server, to my local machine, from again, my desktop client.

Any thoughts?

No solution, I'm afraid, but this is happening to me, too.

Did you find a solution yourself by any chance?

|||Unfortunately, no |||Verified by me too.|||A good candidate to keepup the details under http://connect.microsoft.com/ and might check someone already reported. I presume you have applied SP1 on that SQL 2005.|||it's been a while, several installations ago, several months now, since I've had this issue, sicne applying SP1 I believe ...

No such interface supported

I receive "No such interface supported" in SQL Server 2005, when:

1. on server, from my desktop client: attempting to create a maintenance plan within Management Studio without the wizard (with the wizard, works fine)

2. trying to copy, using SMO, an entire database from the server, to my local machine, from again, my desktop client.

Any thoughts?

No solution, I'm afraid, but this is happening to me, too.

Did you find a solution yourself by any chance?

|||Unfortunately, no |||Verified by me too.|||A good candidate to keepup the details under http://connect.microsoft.com/ and might check someone already reported. I presume you have applied SP1 on that SQL 2005.|||it's been a while, several installations ago, several months now, since I've had this issue, sicne applying SP1 I believe ...

No such interface supported

I receive "No such interface supported" in SQL Server 2005, when:

1. on server, from my desktop client: attempting to create a maintenance plan within Management Studio without the wizard (with the wizard, works fine)

2. trying to copy, using SMO, an entire database from the server, to my local machine, from again, my desktop client.

Any thoughts?

No solution, I'm afraid, but this is happening to me, too.

Did you find a solution yourself by any chance?

|||Unfortunately, no |||Verified by me too.|||A good candidate to keepup the details under http://connect.microsoft.com/ and might check someone already reported. I presume you have applied SP1 on that SQL 2005.|||it's been a while, several installations ago, several months now, since I've had this issue, sicne applying SP1 I believe ...

No such interface supported

I receive "No such interface supported" in SQL Server 2005, when:

1. on server, from my desktop client: attempting to create a maintenance plan within Management Studio without the wizard (with the wizard, works fine)

2. trying to copy, using SMO, an entire database from the server, to my local machine, from again, my desktop client.

Any thoughts?

No solution, I'm afraid, but this is happening to me, too.

Did you find a solution yourself by any chance?

|||Unfortunately, no |||Verified by me too.|||A good candidate to keepup the details under http://connect.microsoft.com/ and might check someone already reported. I presume you have applied SP1 on that SQL 2005.|||it's been a while, several installations ago, several months now, since I've had this issue, sicne applying SP1 I believe ...