Modify column order

Hello,
I currently have a table with 2 primary keys, and 6
columsn following that. I want to add 2 new columns, that
will also be primary keys. I want to insert these new
columns in position 3 and 4.
Is there a way to do this without dropping the table and
recreating it in the correct column order ?
eg:
Table : dbo.TMCLASS has columns as follows
COUNTRYCODE (pk)
CLASS (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I want a new structure (PROPERTYTYE and SEQUENCENO added)
Table : dbo.TMCLASS
COUNTRYCODE (pk)
CLASS (pk)
PROPERTYTYPE (pk)
SEQUENCENO (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I am tryign to avoid dropping and recreating the table as
there is data at client sites.
Thanks,
AlisonDon't think so. I also don't know why you would want to. I see a few
people asking for this aesthetic change. If you qualify all your statements
instead of using * then column order is irrelevant. May look prettier in EM
or other tool but that is about it I thnk.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison Bell" <abell@.cpaglobal.com> wrote in message
news:047f01c36552$898f9d40$a601280a@.phx.gbl...
> Hello,
> I currently have a table with 2 primary keys, and 6
> columsn following that. I want to add 2 new columns, that
> will also be primary keys. I want to insert these new
> columns in position 3 and 4.
> Is there a way to do this without dropping the table and
> recreating it in the correct column order ?
> eg:
> Table : dbo.TMCLASS has columns as follows
> COUNTRYCODE (pk)
> CLASS (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I want a new structure (PROPERTYTYE and SEQUENCENO added)
> Table : dbo.TMCLASS
> COUNTRYCODE (pk)
> CLASS (pk)
> PROPERTYTYPE (pk)
> SEQUENCENO (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I am tryign to avoid dropping and recreating the table as
> there is data at client sites.
> Thanks,
> Alison
>|||I don't think there is a way either. Reason for wanting
the specific order is to match "alltables" script
generated by ERwin for data transfer at a later date. It
just means modifying the generated script each time we
make a DB change, as ERwin places the columns in pos 3
and 4 (non modifyable).
Thanks,
Alison
>--Original Message--
>Don't think so. I also don't know why you would want
to. I see a few
>people asking for this aesthetic change. If you qualify
all your statements
>instead of using * then column order is irrelevant. May
look prettier in EM
>or other tool but that is about it I thnk.|||Problem being I must do this via SQL scripting. It is
part of an upgrade script being shipped to client sites.
>--Original Message--
>easiest way is use EM's (pet's tool) table designer. Sql
>2000 will drop and create with your requirements. You
>don't have to do anything.
>>--Original Message--
>>I don't think there is a way either. Reason for
wanting
>>the specific order is to match "alltables" script
>>generated by ERwin for data transfer at a later date.
It
>>just means modifying the generated script each time we
>>make a DB change, as ERwin places the columns in pos 3
>>and 4 (non modifyable).
>>Thanks,
>>Alison
>>--Original Message--
>>Don't think so. I also don't know why you would want
>>to. I see a few
>>people asking for this aesthetic change. If you
qualify
>>all your statements
>>instead of using * then column order is irrelevant.
May
>>look prettier in EM
>>or other tool but that is about it I thnk.
>>.
>.
>|||Then you have to re-create the table (this is what EM does under covers anyhow). There is no
functionality in TSQL (or ANSI SQL, as I remember) to change column order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alison Bell" <abell@.cpaglobal.com> wrote in message news:0fe901c366bd$edc2e210$a001280a@.phx.gbl...
> Problem being I must do this via SQL scripting. It is
> part of an upgrade script being shipped to client sites.
> >--Original Message--
> >easiest way is use EM's (pet's tool) table designer. Sql
> >2000 will drop and create with your requirements. You
> >don't have to do anything.
> >
> >>--Original Message--
> >>I don't think there is a way either. Reason for
> wanting
> >>the specific order is to match "alltables" script
> >>generated by ERwin for data transfer at a later date.
> It
> >>just means modifying the generated script each time we
> >>make a DB change, as ERwin places the columns in pos 3
> >>and 4 (non modifyable).
> >>
> >>Thanks,
> >>Alison
> >>
> >>--Original Message--
> >>Don't think so. I also don't know why you would want
> >>to. I see a few
> >>people asking for this aesthetic change. If you
> qualify
> >>all your statements
> >>instead of using * then column order is irrelevant.
> May
> >>look prettier in EM
> >>or other tool but that is about it I thnk.
> >>
> >>.
> >>
> >.
> >

Modelling Time Span with OLAP tools

This is more of a design question. I am trying to create a fact table for ti
me spans. For example an order might go through the following status of Crea
ted, In-Progress, Delivered. The time span between any 2 states could range
from minutes to days to mon
ths. I want to be able to see the current status and also the status as of l
ast month, or last quarter or some other previous date.
Some of the recommendations by extert sites are to have a date in your fact
table for each status accompained by a field
for storing a value 1. So the order fact by look like
Order Id
Order Create Date
Order Created Value
Order In-Progress Date
Order In-Progress Value
Order Delivered Date
Order Delivered Value
Also I am creating my own time dimension. The above approach will work very
well when you use SQLs to access your star schema, but I am not so sure this
approach will work for olap tools like MSAS.
Is there an alternative?
ThanksWhat you have here looks like an accumulating snapshot fact table. These
are best employed IMHO where you have a business process/relatively short
with milestones which you want to track.
It means that you have less rows in your fact table but that you will
revisit the row to perform UPDATEs, something not typically done.
This type of fact table is good for measuring time lags between business
processes i.e.
Time from Order -> Sale
Time from Sale -> Ship
The way I do this is to create a view over the top of the fact table which
calculates these figures for me. This way i can then use it as a measure
just like any other. I usually do it to difference in hours.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Break It" <anonymous@.discussions.microsoft.com> wrote in message
news:13CB4DFA-500D-4268-B850-5EE207F917CD@.microsoft.com...
> This is more of a design question. I am trying to create a fact table for
time spans. For example an order might go through the following status of
Created, In-Progress, Delivered. The time span between any 2 states could
range from minutes to days to months. I want to be able to see the current
status and also the status as of last month, or last quarter or some other
previous date.
> Some of the recommendations by extert sites are to have a date in your
fact table for each status accompained by a field
> for storing a value 1. So the order fact by look like
> Order Id
> Order Create Date
> Order Created Value
> Order In-Progress Date
> Order In-Progress Value
> Order Delivered Date
> Order Delivered Value
> Also I am creating my own time dimension. The above approach will work
very well when you use SQLs to access your star schema, but I am not so sure
this approach will work for olap tools like MSAS.
> Is there an alternative?
> Thanks

Model Information

Is it possible to get hold of the fit parameters out of the prediction models in order to use them without going back to the database.

E.g. With the linear fit model, y = ax + b, is it possible to get hold of a and b?

This way I could use the fit equation and parameters directly within my C# code, rather than making many costly connections back to the database.You can get all of the parameters from the model's content rowset. You can see the rowset by using the generic viewer (there's a viewer drop-down above the mining viewers) or by executing the query SELECT * FROM [My Model].CONTENT.

At www.sqlserverdatamining.com there is an additional plug-in viewer that makes it easier to view the content rowset as well.

You can query the content rowset through code using standard connection and command objects.|||Thanks of replying, but this still doesn't seem to give me the actual parameters of the equation. I can see them in the 'mining legend' window, though I can only get this to come up for the linear fit model.

Also it seems to be giving me strange fit parameters. I created a table of data with x = 1 - 20 and y = 2x. When I performed a linear fit on this, the equation I was given in 'Mining Legend' was Y = 21.000+1.999*(X-10.500). This seems a bit odd, especially when I pasted the data into excel and got the expected answer of a zero intercept and a gradient of 2.|||

Greetings, MagorGirl:
what you are seeing in the Mining Legend is the algebraic form of the linear fit you are looking for.
Because 1.99999*(-10.5) is -21 [up to roundoff], once you expand the parentheses you indeed get Y = 0.000 + 1.999*X
Regression formulas are represented in such form in order to highlight the mean values of the each regressor and of the regression target.
In this case 10.5 is obviously the mean of x and 21 is the mean of y.
So by looking at this format you can take in all the essential stats in one glance.

If you use a regression formula in this format for numeric prediction, the numeric resultsare going to be indentical of course to when you use the equivalent linear fit in the form of a*x+b

|||Thanks Alexei

There's still a problem with the accuracy of this fit though, the intercept does not cancel out fully unless you use quite a lot of rounding. Is there a tendency in the model not to overfit? Does the SQL regression require more data than the excel regression?

I was also looking at the neural networks model and trying to see what equation it produces.

Is there a table anywhere that contains the explanations for the different valuetypes? I've found out that in the linear regression output, valuetype of 3 = intercept and 7 is gradient (Thanks to Jamie for pointing out the code for the improved viewer). I've yet to find a table explaining all the values and the general content of the node_distribution table. Is there any more technical documentation out there, there doesn't seem to be much in books online.

Thanks|||> Is there a tendency in the model not to overfit?

That's exactly right. There is a mechanism of Bayesian priors to reduce the risk of overfitting. The more data you have the less noticeable the influence of the priors is going to be.

I am not sure where the valuetype descriptions are published...|||

I know this is not the level of detail you're looking for but you can at least see the symbolic names for the value types (and other constants used in content) in "\Program Files\Microsoft SQL Server\90\SDK\Include\oledbdm.h" - e.g.:
#define DMMVALUETYPE_COEFFICIENT ( 7 )

We hope to have more technical information available in web updates to Books Online.

|||The GenericContentTreeViewer downloadable from sqlserverdatamining.com decodes all the content node types and value types for you to make it easier to understand. I recommend giving it a try.|||Ok, so I've installed the generic viewer and I can see that some values_types are continuous, some coefficients etc. I can see how the linear fit equation can be built up from this.

I would still like to create an equation from the Neural Network model. I used the same data set as for my linear fit, y = 2x. I told the model to have zero hidden nodes. I basically get two value_types back, which are both continuos. X = 5.5 and Y = 11. Obviously this shows that y = 2x, but how do I construct an equation from these?

Thanks for all the help so far

Model Information

Is it possible to get hold of the fit parameters out of the prediction models in order to use them without going back to the database.

E.g. With the linear fit model, y = ax + b, is it possible to get hold of a and b?

This way I could use the fit equation and parameters directly within my C# code, rather than making many costly connections back to the database.You can get all of the parameters from the model's content rowset. You can see the rowset by using the generic viewer (there's a viewer drop-down above the mining viewers) or by executing the query SELECT * FROM [My Model].CONTENT.

At www.sqlserverdatamining.com there is an additional plug-in viewer that makes it easier to view the content rowset as well.

You can query the content rowset through code using standard connection and command objects.|||Thanks of replying, but this still doesn't seem to give me the actual parameters of the equation. I can see them in the 'mining legend' window, though I can only get this to come up for the linear fit model.

Also it seems to be giving me strange fit parameters. I created a table of data with x = 1 - 20 and y = 2x. When I performed a linear fit on this, the equation I was given in 'Mining Legend' was Y = 21.000+1.999*(X-10.500). This seems a bit odd, especially when I pasted the data into excel and got the expected answer of a zero intercept and a gradient of 2.|||

Greetings, MagorGirl:
what you are seeing in the Mining Legend is the algebraic form of the linear fit you are looking for.
Because 1.99999*(-10.5) is -21 [up to roundoff], once you expand the parentheses you indeed get Y = 0.000 + 1.999*X
Regression formulas are represented in such form in order to highlight the mean values of the each regressor and of the regression target.
In this case 10.5 is obviously the mean of x and 21 is the mean of y.
So by looking at this format you can take in all the essential stats in one glance.

If you use a regression formula in this format for numeric prediction, the numeric resultsare going to be indentical of course to when you use the equivalent linear fit in the form of a*x+b|||Thanks Alexei

There's still a problem with the accuracy of this fit though, the intercept does not cancel out fully unless you use quite a lot of rounding. Is there a tendency in the model not to overfit? Does the SQL regression require more data than the excel regression?

I was also looking at the neural networks model and trying to see what equation it produces.

Is there a table anywhere that contains the explanations for the different valuetypes? I've found out that in the linear regression output, valuetype of 3 = intercept and 7 is gradient (Thanks to Jamie for pointing out the code for the improved viewer). I've yet to find a table explaining all the values and the general content of the node_distribution table. Is there any more technical documentation out there, there doesn't seem to be much in books online.

Thanks|||> Is there a tendency in the model not to overfit?

That's exactly right. There is a mechanism of Bayesian priors to reduce the risk of overfitting. The more data you have the less noticeable the influence of the priors is going to be.

I am not sure where the valuetype descriptions are published...
|||

I know this is not the level of detail you're looking for but you can at least see the symbolic names for the value types (and other constants used in content) in "\Program Files\Microsoft SQL Server\90\SDK\Include\oledbdm.h" - e.g.:
#define DMMVALUETYPE_COEFFICIENT ( 7 )

We hope to have more technical information available in web updates to Books Online.

|||The GenericContentTreeViewer downloadable from sqlserverdatamining.com decodes all the content node types and value types for you to make it easier to understand. I recommend giving it a try.|||Ok, so I've installed the generic viewer and I can see that some values_types are continuous, some coefficients etc. I can see how the linear fit equation can be built up from this.

I would still like to create an equation from the Neural Network model. I used the same data set as for my linear fit, y = 2x. I told the model to have zero hidden nodes. I basically get two value_types back, which are both continuos. X = 5.5 and Y = 11. Obviously this shows that y = 2x, but how do I construct an equation from these?

Thanks for all the help so far

Model generation (for Report Builder)

I have had to add Calculated columns to some tables in my Data Source View in
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'

Model Builder Field Order and Inheritance

When I create a report model, it is easy to change the order in a given entity. Does anyone know how to order the fields in an entity when you are using inheritance or inlining? I want to have the fields in alphabetical order so that the end user doesn't have to hunt for the correct field.

The other item I need to find out is how to customize the prefixes that inlining uses. Any ideas there?

Ron

Specifying field order across entities is not supported in this release. One thing you can do to mitigate this is use field folders so the user has a "tree" to search instead of a long list that is not ordered very well. In the case of inlining, you can actually place the role in a folder by itself, and all its fields will show up there when the role is expanded.

By "prefixes" I assume you mean the contextual naming that is applied based on the Role.ContextualName and Attribute.ContextualName properties. These are the mechanism for controlling this behavior.

Hope this helps!

|||

That was right on the money with both of the questions. That folder trick is really helpful in cleaning up some seriously unmanageable lists of fields.

R

sql

mixed mapping & OPENXML

Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) statement I need to use in order to get the values out of the following XML text:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran Djuranovic
Of course, other than:
SELECT * FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
and checking whichever one is not NULL, to take that one.
-----
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:OI0fseJPGHA.1696@.TK2MSFTNGP14.phx.gbl...
Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) statement I need to use in order to get the values out of the following XML text:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran Djuranovic
|||And, other than:
SELECT
Name = IsNull(Name1, Name2)
FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
TIA
Goran Djuranovic
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:OI0fseJPGHA.1696@.TK2MSFTNGP14.phx.gbl...
Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) statement I need to use in order to get the values out of the following XML text:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran Djuranovic
|||I took "the below" as a solution.
-G
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:O3sMlvJPGHA.3888@.TK2MSFTNGP12.phx.gbl...
And, other than:
SELECT
Name = IsNull(Name1, Name2)
FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
TIA
Goran Djuranovic

mixed mapping & OPENXML

Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) st
atement I need to use in order to get the values out of the following XML te
xt:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran DjuranovicOf course, other than:
SELECT * FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
and checking whichever one is not NULL, to take that one.
----
--
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:OI0f
seJPGHA.1696@.TK2MSFTNGP14.phx.gbl...
Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) st
atement I need to use in order to get the values out of the following XML te
xt:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran Djuranovic|||And, other than:
SELECT
Name = IsNull(Name1, Name2)
FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
TIA
Goran Djuranovic
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:OI0f
seJPGHA.1696@.TK2MSFTNGP14.phx.gbl...
Hi All,
Does anyone know what comination of OPENXML(blah, blah) WITH (blah, blah) st
atement I need to use in order to get the values out of the following XML te
xt:
<Root>
<Contact Name="username1" />
<Contact>
<Name>username2</Name>
</Contact>
</Root>
TIA
Goran Djuranovic|||I took "the below" as a solution.
-G
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:O3sM
lvJPGHA.3888@.TK2MSFTNGP12.phx.gbl...
And, other than:
SELECT
Name = IsNull(Name1, Name2)
FROM OPENXML (@.idoc, '/ROOT/Contact',3)
WITH (
Name1 varchar(10) '@.Name',
Name2 varchar(10) 'Name'
)
TIA
Goran Djuranovic