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 DB in Warm-Standby

Can anyone help me with this? I tried to use the query
analyzer to bring the Model DB back on-line:
restore database model
with recovery
But this is what I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.I am assuming that no one else has an open connection to
the model database. You will get this error if you have
the model database "open" in Enterprise Manager and
simultaneously try to execute the RESTORE command from
query analyzer. Hope this helps.
Tim
>--Original Message--
>Can anyone help me with this? I tried to use the query
>analyzer to bring the Model DB back on-line:
>restore database model
>with recovery
>But this is what I get:
>Server: Msg 3101, Level 16, State 1, Line 1
>Exclusive access could not be obtained because the
>database is in use.
>Server: Msg 3013, Level 16, State 1, Line 1
>RESTORE DATABASE is terminating abnormally.
>.
>sql

missing tables

I have a database that I have been working on that is on a remote server for a website. I recently had them back up the database so I can work on it locally. After some time I managed to get the backup working, well sorta.

Originally the database was a access 2000 database. This soon proved not to be up to the task. The server people converted the database to sql 2000 and everything is working on the site. However my backup has a problem. In the manager I can see all the tables that were there before. But when I go to run a query on the database through CF the only tables that it can see are tables that were created after the access conversion. I have gone in and checked permissions and set every table and every column to public and still cannot see the missing tables. Any help?

What is CF?

How did the the server people move the Access tables to SQL Server?

Are you having this problem with the backup that you mention or with the original copy on the server?

Mike

|||CF is Cold Fusion.

I have no idea how they converted the Access to SQL

I am only having this problem with the backup.|||

Well, I have no idea how Cold Fusion works, it's always possible that they read the file is some "special" way that is causing this. But there is also the fact that the backup is "sort of" working.

As a first test, it would be worth checking if you can see the tables using some other tool. Try creating linked tables to your database in Access using the Link Table wizard and see if the behavior is any different. If it works in Access, then I'd say pursue the problem with the Cold Fusion people, if it behaves the same in Access, then it we should investigate the backup it self and did into the "sort of" part of the equation.

Mike

|||I had allready thought of that. PHP cannot see the hidden tables either. My access wont let me do linked tables to sql. I only have access 2000 and I guess that it doesnt do that.|||

Odd, Access has supported linked tables to SQL since 2.0.

I'm assuming that you can see the tables using SQL tools such as Management Studio, right? Could you check what Schema the tables that you cannot see from other programs belong to?

If you can see tables that are created in SQL, the answer might just be to recreate these tables directly in SQL. You could script out the problem tables, change the names of the original, recreate the tables using the scripts and then move the data. I don't know how many tables we're talking about here, but it's worth looking into if it gets you up and running. (Try it with one table to see if it works.)

Mike

missing tables

I have a database that I have been working on that is on a remote server for a website. I recently had them back up the database so I can work on it locally. After some time I managed to get the backup working, well sorta.

Originally the database was a access 2000 database. This soon proved not to be up to the task. The server people converted the database to sql 2000 and everything is working on the site. However my backup has a problem. In the manager I can see all the tables that were there before. But when I go to run a query on the database through CF the only tables that it can see are tables that were created after the access conversion. I have gone in and checked permissions and set every table and every column to public and still cannot see the missing tables. Any help?

What is CF?

How did the the server people move the Access tables to SQL Server?

Are you having this problem with the backup that you mention or with the original copy on the server?

Mike

|||CF is Cold Fusion.

I have no idea how they converted the Access to SQL

I am only having this problem with the backup.|||

Well, I have no idea how Cold Fusion works, it's always possible that they read the file is some "special" way that is causing this. But there is also the fact that the backup is "sort of" working.

As a first test, it would be worth checking if you can see the tables using some other tool. Try creating linked tables to your database in Access using the Link Table wizard and see if the behavior is any different. If it works in Access, then I'd say pursue the problem with the Cold Fusion people, if it behaves the same in Access, then it we should investigate the backup it self and did into the "sort of" part of the equation.

Mike

|||I had allready thought of that. PHP cannot see the hidden tables either. My access wont let me do linked tables to sql. I only have access 2000 and I guess that it doesnt do that.|||

Odd, Access has supported linked tables to SQL since 2.0.

I'm assuming that you can see the tables using SQL tools such as Management Studio, right? Could you check what Schema the tables that you cannot see from other programs belong to?

If you can see tables that are created in SQL, the answer might just be to recreate these tables directly in SQL. You could script out the problem tables, change the names of the original, recreate the tables using the scripts and then move the data. I don't know how many tables we're talking about here, but it's worth looking into if it gets you up and running. (Try it with one table to see if it works.)

Mike

Missing SSAS Diagrams

I've created multiple SSAS diagrams and saved them back to the server. The next day, I connected to the server and the diagrams are gone. Rather than recreate all of them, I recreated one and tried saving it back to the server. I get the message stating "updating server", but when I close out and get back in the diagram is missing again. We have so many cubes we really need these so everyone can have a visualization of the structure. I even tried saving the project and reprocessing the database to no avail. Please help.

Many Thanks.

Scott,

When you say diagrams, are you referring to diagrams within the DSV for an SSAS project? Or something else?

Dave Fackler

|||

Hi Dave,

Thanks for your reply. I've figured it out. The diagrams were just on the server. I had to create a new project and download a more recent copy of the SSAS database. Feeling really smart:)

Thanks again,

Scott

missing SP when I query for it's text

I have code below to extract a table name and a verb. I receive 4 rows
back. I think fine. Unfortunatly my programmer says that the SP in
question is not part of the return set.
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%PreNote_Transaction_Lookup%'
and Routine_definition like '%insert%'
I change the query to fit the same code he is looking at:
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
and I get 0 rows back.
This is from the SP itself:
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where
Prenote_Transaction_ID = @.Prenote_Trans_ID
I have even put insert into in all caps. Still no rows returned?
Any ideas?Below work fine on my machine:
USE tempdb
GO
CREATE PROC A
AS
DECLARE @.Prenote_Trans_ID int
DECLARE @.transaction_id int
DECLARE @.Count int
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transaction_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID =@.Prenote_Trans_ID
END
GO
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
Is your database case sensitive? Do you have > 4000 charactes in the procedure?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"_Stephen" <srussell@.electracash.com> wrote in message
news:O%23Ee6sHRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I have code below to extract a table name and a verb. I receive 4 rows back. I think fine.
>Unfortunatly my programmer says that the SP in question is not part of the return set.
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%PreNote_Transaction_Lookup%'
> and Routine_definition like '%insert%'
> I change the query to fit the same code he is looking at:
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%insert into PreNote_Transaction_Lookup%'
> and I get 0 rows back.
> This is from the SP itself:
> IF @.Prenote_Trans_ID > 0
> BEGIN
> INSERT INTO PreNote_Transaction_Lookup Values (@.Prenote_Trans_ID,null,cast(@.transaction_id as
> varchar(10)))
> Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Transaction_ID => @.Prenote_Trans_ID
>
> I have even put insert into in all caps. Still no rows returned?
> Any ideas?
>

Missing Records in Linked Table

I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
an MDB file. The db is used by about 30 users on a LAN, and an additional 10
or so on a WAN.
Recently, one of the WAN users complained of intermittently not being able
to find certain customers in the customers table (contains around 40,000
records). There are specific "problem records" of customers that can't be
found, and they all start with the letter "F" (the table has a numeric
customer ID as its primary key).
For these three or four customers that have been identified so far as
problem records, the customer exists in the customer list (a drop-down based
on a front-end table), but, when trying to go to the record, are told that
the record's not found.
Similarly, when the user searches for one of these problem records using the
custom search function, which searches the linked tables, the search
function says the record cannot be found.
Note that this is an intermittent problem with this single WAN user, and no
other users (on WAN or LAN are having this problem).
Any ideas about what might be causing it, or what can be done to this single
user's machine to alleviate the problem?
Thanks!
NeilAre they accessing the table directly or through a View that might be
limiting records?
--
KARL DEWEY
Build a little - Test a little
"Neil" wrote:

> I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
> an MDB file. The db is used by about 30 users on a LAN, and an additional
10
> or so on a WAN.
> Recently, one of the WAN users complained of intermittently not being able
> to find certain customers in the customers table (contains around 40,000
> records). There are specific "problem records" of customers that can't be
> found, and they all start with the letter "F" (the table has a numeric
> customer ID as its primary key).
> For these three or four customers that have been identified so far as
> problem records, the customer exists in the customer list (a drop-down bas
ed
> on a front-end table), but, when trying to go to the record, are told that
> the record's not found.
> Similarly, when the user searches for one of these problem records using t
he
> custom search function, which searches the linked tables, the search
> function says the record cannot be found.
> Note that this is an intermittent problem with this single WAN user, and n
o
> other users (on WAN or LAN are having this problem).
> Any ideas about what might be causing it, or what can be done to this sing
le
> user's machine to alleviate the problem?
> Thanks!
> Neil
>
>|||No, directly, the entire table. Also, note that the functionality is fine,
and on one (and now I found out two) computers the data for those records is
intermittently not there. But on other computers it's there. Nothing in the
functionality that would be limiting it.
"KARL DEWEY" <KARLDEWEY@.discussions.microsoft.com> wrote in message
news:3D9F901F-12EB-479D-9981-AE808A4E76EB@.microsoft.com...[vbcol=seagreen]
> Are they accessing the table directly or through a View that might be
> limiting records?
> --
> KARL DEWEY
> Build a little - Test a little
>
> "Neil" wrote:
>|||hi Neil,
Neil wrote:
> Note that this is an intermittent problem with this single WAN user, and n
o
> other users (on WAN or LAN are having this problem).
Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
the client.
mfG
--> stefan <--|||Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
(msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
as the other users. Are there any other files you suggest I check?
The fact that these records all have names starting with "F" leads me to
wonder if it's a paging issue, even though the PK is the customer ID, not
name, though there is an index on last name.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:uEy7$tM2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> hi Neil,
> Neil wrote:
> Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
> the client.
>
> mfG
> --> stefan <--|||hi Neil,
Neil wrote:
> Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), J
et
> (msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the sam
e
> as the other users. Are there any other files you suggest I check?
No.

> The fact that these records all have names starting with "F" leads me to
> wonder if it's a paging issue, even though the PK is the customer ID, not
> name, though there is an index on last name.
Can you browse the linked table on that specific client?
mfG
--> stefan <--|||
>
> Can you browse the linked table on that specific client?
>
> mfG
Good question. I'll have to check on that tomorrow.
BTW, what's mfG?

> --> stefan <--|||hi Neil,
Neil wrote:
> BTW, what's mfG?
"mit freundlichen Gren" means "with kind regards"
mfG
--> stefan <--

Missing Records in Linked Table

I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
an MDB file. The db is used by about 30 users on a LAN, and an additional 10
or so on a WAN.
Recently, one of the WAN users complained of intermittently not being able
to find certain customers in the customers table (contains around 40,000
records). There are specific "problem records" of customers that can't be
found, and they all start with the letter "F" (the table has a numeric
customer ID as its primary key).
For these three or four customers that have been identified so far as
problem records, the customer exists in the customer list (a drop-down based
on a front-end table), but, when trying to go to the record, are told that
the record's not found.
Similarly, when the user searches for one of these problem records using the
custom search function, which searches the linked tables, the search
function says the record cannot be found.
Note that this is an intermittent problem with this single WAN user, and no
other users (on WAN or LAN are having this problem).
Any ideas about what might be causing it, or what can be done to this single
user's machine to alleviate the problem?
Thanks!
Neil
Are they accessing the table directly or through a View that might be
limiting records?
KARL DEWEY
Build a little - Test a little
"Neil" wrote:

> I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in
> an MDB file. The db is used by about 30 users on a LAN, and an additional 10
> or so on a WAN.
> Recently, one of the WAN users complained of intermittently not being able
> to find certain customers in the customers table (contains around 40,000
> records). There are specific "problem records" of customers that can't be
> found, and they all start with the letter "F" (the table has a numeric
> customer ID as its primary key).
> For these three or four customers that have been identified so far as
> problem records, the customer exists in the customer list (a drop-down based
> on a front-end table), but, when trying to go to the record, are told that
> the record's not found.
> Similarly, when the user searches for one of these problem records using the
> custom search function, which searches the linked tables, the search
> function says the record cannot be found.
> Note that this is an intermittent problem with this single WAN user, and no
> other users (on WAN or LAN are having this problem).
> Any ideas about what might be causing it, or what can be done to this single
> user's machine to alleviate the problem?
> Thanks!
> Neil
>
>
|||No, directly, the entire table. Also, note that the functionality is fine,
and on one (and now I found out two) computers the data for those records is
intermittently not there. But on other computers it's there. Nothing in the
functionality that would be limiting it.
"KARL DEWEY" <KARLDEWEY@.discussions.microsoft.com> wrote in message
news:3D9F901F-12EB-479D-9981-AE808A4E76EB@.microsoft.com...[vbcol=seagreen]
> Are they accessing the table directly or through a View that might be
> limiting records?
> --
> KARL DEWEY
> Build a little - Test a little
>
> "Neil" wrote:
|||hi Neil,
Neil wrote:
> Note that this is an intermittent problem with this single WAN user, and no
> other users (on WAN or LAN are having this problem).
Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
the client.
mfG
--> stefan <--
|||Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
(msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
as the other users. Are there any other files you suggest I check?
The fact that these records all have names starting with "F" leads me to
wonder if it's a paging issue, even though the PK is the customer ID, not
name, though there is an index on last name.
Thanks,
Neil
"Stefan Hoffmann" <stefan.hoffmann@.explido.de> wrote in message
news:uEy7$tM2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> hi Neil,
> Neil wrote:
> Check thw WAN connection and the service packs (OS, Office, MDAC/Jet) of
> the client.
>
> mfG
> --> stefan <--
|||hi Neil,
Neil wrote:
> Hi, Stefan. I am tracking the version of Windows, Access (msaccess.exe), Jet
> (msjet40.dll), and the SQL driver (sqlsrv32.dll), and they are all the same
> as the other users. Are there any other files you suggest I check?
No.

> The fact that these records all have names starting with "F" leads me to
> wonder if it's a paging issue, even though the PK is the customer ID, not
> name, though there is an index on last name.
Can you browse the linked table on that specific client?
mfG
--> stefan <--
|||
> Can you browse the linked table on that specific client?
>
> mfG
Good question. I'll have to check on that tomorrow.
BTW, what's mfG?

> --> stefan <--
|||hi Neil,
Neil wrote:
> BTW, what's mfG?
"mit freundlichen Gren" means "with kind regards"
mfG
--> stefan <--