Error While Trying To Get Predictions From Relational DB
Sep 21, 2006
I am trying to get predictions and insert them into a DB table.
Following is the code I am using , but I am getting an error saying
'
An error occurred while preparing the query
,
Pl. help.
begin
declare @v_query varchar(5000);
declare @full_query varchar(5000);
declare @v_dbquery varchar (200);
set @v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);
set @v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @v_query
set @full_query = 'select * from openquery (DMserver,'+char(39)+ @v_query +char(39)+')' ;
print @full_query;
EXEC (@full_query);
end
View 4 Replies
ADVERTISEMENT
Aug 4, 2005
Hi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)
But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.
'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.
What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty
View 3 Replies
View Related
Dec 25, 2007
hello,
I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question:
relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting?
thanks for your helps...
View 3 Replies
View Related
May 17, 2007
Hi,
I've been playing around with the association mining model in SQL server 2005 and built a market-basket analysis of my data that I'm pretty happy with. The next task for me is figuring out how to run DMX queries against the data that I've just mined, so we may possibly use it in a web based application. This wouldn't necessarily be a difficult problem (and still may not be), but every example I've seen for the Mining Model Prediction Designer uses relational databases and I built my mining model off OLAP. Therefore, my predictable attribute is nested and when relating the mining model structure to the relational database that the cube was built off always gives me an error:
"Errors in the high-level relational engine. The 'CompanyName' column could not be found in the top-level clause of the SHAPE statement."
What I would like to do, and I'm not really even sure how I should structure any of my queries, is feed the model a product and have it return a listing of all the products it predicts. Currently, I've only been able to get the designer mode to process a singleton query, and even that didn't return any useful data. I know that this probably can be done pretty easily so any advice you may be able to offer would be greatly appreciated!!
So you may better understand my question, my association mining structure hierarchy looks as this..
[Model] ProductRecommend
[Case][Key]CustomerList
[Case][Attribute]CompanyName
[NestedTable]Product
[Nested][Key]PRODUCTCLASSID
[Nested][Attribute]PRODUCT
With that in mind, I'm trying to perform a query simliar to this:
SELECT
PredictProbability([ProductRecommend].[Product].[PRODUCTCLASSID]), <---- Throws Error for PredictProbability syntax no matter what I try to get to [PRODUCTCLASSID]
(SELECT [PRODUCT] FROM [ProductRecommend].[Product])
From
[ProductRecommend]
NATURAL PREDICTION JOIN
(SELECT 'test' AS [COMPANYNAME],
(SELECT '1234' AS [PRODUCTCLASSID],
'ProductA' AS [PRODUCT]) AS [Product]) AS t
Thanks again for any help!
View 5 Replies
View Related
Mar 13, 2007
I would like to use analysis services to analyze stock prices.
I want to find conditional probabilities:
P (YpriceChg >= 10% s.t. Ydate between A and B| X Price Chg >= 20%)?
€¦ Like given a price change of X percent or greater, predict the probability of a price change of Y percent or greater, within a specified time window (like 2 days, 3 months etc.).
I also want to add a support filter, like:
N > 30 cases (i.e., there have been at least 10 instances of a 10% or greater price change, for the chosen time window)
I have a database of prices, monthly, daily, etc. I also have a number of cols that compute statistics such as pChg1M, pChg-1M, vChg1d. Like price chg 1 month forward, price change 1 month backward, volumeChg1d forward. Ideally, I would like to minimize the column flags necessary for the experiment. Can you offer some hints, as far as setting up appropriate columns/flags and choosing a algorithm (maybe decision trees, association rules, or NB)?
View 1 Replies
View Related
May 2, 2007
After having built a decision tree model to predict a boolean output attribute using 64-bit SQL Server 2005 (build 9.0.3054), we have observed that predictions for some cases are being done at non-leaf nodes in the tree.
Specifically, after executing a prediction join which returns:
- CaseTable.CaseID
- MiningModel.OutputAttribute
- PredictProbability(MiningModel.OutputAttribute)
- PredictNodeId(MiningModel.OutputAttribute)
and comparing the values of PredictNodeID(MiningModel.OutputAttribute) with the mining model content column [NODE_UNIQUE_NAME] to determine the actual "rule" used to make the case-level prediction.
We have observed that for a subset of cases, predictions are being made at nodes in the tree that are not leaf nodes. Specifically, predictions are being made at a node that is 3 levels deep. The leaf nodes below this inner-tree node are 2 levels further down the tree.
Also supporting the fact that that predictions are being made at this non-leaf node is that the PredictProbability corresponds exactly with the output attribute distribution at this non-leaf node.
In this particular application, we would have obtained better results if the predictions were made at the leaf-nodes.
A few questions:
1. Why are predictions with decision trees made at non-leaf nodes?
2. Is there a way to "force" predictions to occur at leaf nodes via DMX?
Thanks in advance for any information or advice.
- Paul
View 1 Replies
View Related
Nov 24, 2006
Greetings,
I have a mining structure that I am using to perform a text-mining
classification task. The mining structure contains three models: a
decision tree, a naive bayes and a neural network.
Both the decision tree and the naive bayes models process without
any problems, but I am having significant difficulties with the neural
network model.
Initially when I processed the model, processing would fail altogether with the following error message:
"Memory error: Allocation failure : Not enough storage is available to process this command"
This was remedied by taking the steps prescribed in
http://support.microsoft.com/kb/917885 (I upgraded to SQL 2005 SP1 and
applied all available hotfixes listed in
http://support.microsoft.com/kb/918222/). This got me to the point
where the model (seemingly) processed correctly after restricting the
MAXIMUM_INPUT_ATTRIBUTES to a relatively low number. So after
processing, I went to try and browse the neural network model and view
the lift chart...
<error>
"Execution of the managed stored procedure GetAttributeScores
failed with the following error: Exception has been thrown by the
target of an invocation.Input string was not in a correct format.."
</error>
(see http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=935340&SiteID=17)
Also when I would attempt to view the lift chart and the
classification matrix the queries would time out with the following
error message:
<error>
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Execution of the managed stored procedure
GenerateLiftTableUsingDatasource failed with the following error:
Exception has been thrown by the target of an
invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.
</error>
Now, my poking around on Technet lead me to believe that this issue
could finally be resolved by uprading to the CTP release of SQL server
2005 SP2. Now I am still encountering problems. When I go to browse the
model in the Neural Network Viewer, I see the correct drop down menus
to select attributes and attribute values in the "Input" and "Output"
panes but I see no data displayed in the "Variables" pane at the
bottom.
Interestingly, while I cannot view the model contents in the
graphical viewer, the mining model contents viewer reveals model
contents that look to be pretty normal for a trained neural network.
Attempts to view the lift chart time out with the error message:
<error>
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Execution of the managed stored procedure
GenerateLiftTableUsingDatasource failed with the following error:
Exception has been thrown by the target of an
invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.
</error>
and when I run predictions against the trained NN model in the
"Mining Model Prediction" pane it predicts the same value for every
case in the testing set.
Any thoughts?
View 9 Replies
View Related
Sep 7, 2006
Hi all,
I am using SSAS 2005. The mining model works fine. But it crashes when I run the 'Mining Model Predictions' against large data sets.
I ran it against 5,000,000 records and it went fine.
But exactly same model failed for 5,100,000 records and beyound.
The message is 'Query Execution Failed' and then Visual Studio crashes.
Pl. let me know if anybody has the same experience or knows the solution.
Thanks,
Vikas
View 3 Replies
View Related
Oct 3, 2007
I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?
View 6 Replies
View Related
Jun 21, 2005
Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused. I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item. A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID IN (10, 13, 17)But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull out ONLY items that have a AttributeID of 10 AND 13 AND 17.Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :SI've also tried:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID = 10 AND AttributeID = 13 etcBut obviously that won't work! :s
View 2 Replies
View Related
Apr 22, 2006
Hi
I'm using VB.NET,ADO.NET in ASP.NET .
Microsoft .Net framework 1.0
Windows 2000
Visual Studio IDE
and SQLServer.
I have to create to tables as
Table1 contains ID,Name
Table2 contains ID,Marks,Foreign Key ie Primary key of the table Table1.
Give me an information how to create these two tables in SQLServer (I know how to create a table but i don't know how to create a table which includes Foreign Key.)
Then using Dataset i want to display the records as Name,Marks which are stored in two tables.
I have studied that in ADO Join query and record set object is used but it gives a problems and it is not good when we want to transfer the data between two applications or pages but dataset solves all those problems.
Give me an information about it.
Kindly help me
Thanks in advance
Regards.
View 3 Replies
View Related
Jun 18, 2001
what is the difference in making relational databases in SQL vs. Access? Do you need to make them if you will be using ColdFusion? What do the other fields in minor tables need to be in order to connect them to a major table when it will all be based on a number that is in the major table? i'm kind of confused on this issue...Thanks!
View 1 Replies
View Related
Nov 10, 2004
I have a database with 3 tables in a many to many relationship.
EmployeeGamingLicense
EmployeeDocuments
EmployeeRenewals
I created the third table to hold the employeerenewal dates, I did this because I cant have repeating vaules in the primary table. I am just making sure that my course of action is the correct one. The purpose of the Employeerenewals is to give the users an indication that a certain renewal has been processed, because in the past there was several hundred that were not processed. The problem was that the users had no way of knowing this. What I was thinking was having the primary table calculate a renewal date based off the finalsuit and show the results in the Employeerenewals with a yes of no drop down. Now the yes or no drop down box will be give the users their red flag if the renewals have been processed or not.
Does that make sense??
Thanks guys :)
View 1 Replies
View Related
Sep 4, 2006
Hi
I am developing a website current and i have a database that seems to be growing very rapidly..
I would like to ask if anyone knows of any good tutorials/examples on relational databases?
Also does anyone know of any good database design tutorials/examples?
If it helps i am using Microsoft Visual Web Developer
Thanks
If you think you know it all?! You know nothing!
View 3 Replies
View Related
Apr 26, 2007
If I have a table that contains default values or a constant values do I have to relate it to my other tables that might need them. Thanks in advance.
===============
JSC0624
===============
View 2 Replies
View Related
Jun 15, 2007
My question:
How to have an unknown number of authors returned for a list of books, with the multiple authors being returned on the SAME row as each book
My table structure:
Table A - Books
-------------------
BookID | BookName
-------------------
1 | Book One
Table B -
RelatingTable
-------------------
BookID | AuthorID
-------------------
1 | 60
1 | 61
Table C - Authors
-------------------
AuthorID | AuthorName
-------------------
60 | Bob
61 | Joe
My query:
SELECT * FROM Books, RelatingTable, Authors AS a
INNER JOIN Books AS b ON b.BookID = r.BookID
INNER JOIN RelatedTable AS r ON r.AuthorID = a.AuthorID
Output I am getting:
-------------------------------------------------------------------------
b.BookID | b.BookName | r.BookID | r.AuthorID | a.AuthorID | a.AuthorName
---------|------------|----------|------------|------------|-------------
1 | Book One | 1 | 60 | 60 | Bob
1 | Book One | 1 | 61 | 61 | Joe
-------------------------------------------------------------------------
Desired output:
-----------------------------------------------------------------------------------------------------------------------------------------------------
b.BookID | b.BookName | r.BookID[0] | r.AuthorID[0] | a.AuthorID[0] | a.AuthorName[0] | r.BookID[1] | r.AuthorID[1] | a.AuthorID[1] | a.AuthorName[1]
---------|------------|-------------|---------------|---------------|-----------------|-------------|---------------|---------------|----------------
1 | Book One | 1 | 60 | 60 | Bob | 1 | 61 | 61 | Joe
-----------------------------------------------------------------------------------------------------------------------------------------------------
The desired output does not have to be like an array - I only assume thats what it would need to be
If you can post anything to help me get in the right direction I would really appreciate it
Thanks for your time!
View 11 Replies
View Related
Feb 16, 2006
Hi,I have a very simple question.In what cases are relational databases necessary?Are they really necessary in cases where only asingle type of query is to be performed based on one uniquefield or can we just put all fields together in a single databaseand just access them through that unique field?
View 6 Replies
View Related
Jul 20, 2005
Hi,This question has been bothering me for some time. A lot of peopleseem to "think" XML is the king of data problems, and I've heard thatnext version of SQL Server is going to have a strong XML flavor,meantime, I seem to get the impression that a large number ofhard-core relational model gurus do not seem to be that impressed withXML (technical value of this extra layer seems to be limited whilebusiness value might be substantial for instance, more software work,more disk space requirement etc. etc.). What's your take on this?Generality or specifics, all welcome. One specific question is, howcan XML supplement relational model?Thanks.
View 12 Replies
View Related
Jun 27, 2007
Hi guys,
What is a "Relational Database"?
Thanks,
Aldo.
View 1 Replies
View Related
Aug 1, 2006
I have a few questions for you guys. I have a client
application that can be offline or online. While offline, records can be added and need to be later synced to production.
I will use rda to pull the table down, and this is working fine. Now what if I
have multiple tables where I want a foreign key relationship?
With rda I can only pull down one table at a time from
everything I've read. Now say create a constraint after pulling the two or more tables down. While in offline mode I create a new record on two seperate tables with foreign key/primary key relationship.
When I do the push to the server will it automatically
update the foreign key reference (locally) to the right one on the production
server? Or will I get a duplicate primary key error? On the production server the primary key will be different because of the identity. This is very important because I will have multiple clients.
Thanks in advance
View 1 Replies
View Related
Sep 22, 2007
Hi, not sure if this is the right forum for this question.
I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know....
Nick
View 2 Replies
View Related
Nov 14, 2005
I am just now starting the switch from .NET 1.1 to .NET 2.0. I really like the new way of using the SQLDataSource and setting up Views declaratively as opposed to doing it all in code, which brings me to my question.In some of my applications I have a single Stored Procedure return multiple result sets to a single DataSet where I have a DataRelation set up. Then I can have nested DataGrids that use the GetChildRows() method to filter the results to display the hierarchical data. I would like to do something similar with the SQLDataSource and GridViews but haven't found a way to get multiple result sets.One thought I had was to create a Strongly Typed Dataset and then use the ObjectDataSource object, but I still didn't see a way to get child rows out of the datasource. I've seen an example that uses a <FilterParameter> to get nested data, but there is an extra trip made to the server for each parent item as it just put an extra parameter in the WHERE clause of the query.
View 1 Replies
View Related
Sep 28, 2005
I am building a data warehousing solution without using analysis services or OLAP or OLTP....
Dont ask why ... but I have to....
Ok so which table structure would be better ?
dimention 1|dimention 2|dimention 3|matrix 1|matrix 2|matrix 3|matrix 4....
or
dimention 1|dimention 2|dimention 3|matrix name|matrix value
I prefer the second one....
View 14 Replies
View Related
Apr 2, 2008
Hello everyone
When converting From Relational data into XML which
method ie RAW, AUTO, PATH, EXPLICIT is mostly used and why please
Regards
Rob
View 5 Replies
View Related
Apr 24, 2007
This is a bit of a trivial question, since I know how to actually create the code in SQL. Hope this is the right section to post it in too, since it sort of concerns design I guess..
My problem is how to I represent the following SQL query in relational algebra:
SELECT *
FROM Staff;
Since I'm using SELECT, I assume I use selection to represent it, so would it be as "ó(Staff)" [using the sigma symbol, in case it doesn't show]? But as I'm not specifying a predicate since I wish to select all fields in Staff, would it be simply represented as "Staff"?
I appreciate any help... it's been baffling me for a while
View 2 Replies
View Related
Dec 12, 2007
Hotel(hotelno, hotelname ,city)
Room(roomNo, hotelNo,type,price)
i need the reltional algebra to list the price and type of rooms in the hilton hotel
thanx
View 5 Replies
View Related
Jul 20, 2005
Hi Please help for this simple problemDTS Transfer or any other method?I have Customer_Order Table and customer_Order_Details Table.For OrderID = 1, I have 3 rows of Order Details.I want to transferCustomer_Order Table for OrderID 1 in DTS, the system should transferOrder Table as well as Order details table Rows for ORderID =1.How to customize in DT or is there any way to get this data to transferfrom source DB to Dest DB?KAMAL KUMAR V--Posted via http://dbforums.com
View 1 Replies
View Related
Apr 7, 2008
I understand the basics for doing an insert. Their seems to be many ways of achieving the same thing in .net. I have used the tableadpaters and sqlcommand functionality to achieve this. My one question is how foregin keys should be created in associated tables. EG so if i create a new record entry in one table and the primary key for that entry is a foreign key in another table, do I need to call 2 table adapters, or run 2 sqlcommands(taking the primary key from the first traction and use this in the next transaction)?
Any help or direction for some good tutorials on this would be much appreciated.
Thanks
Mark
View 4 Replies
View Related
Jul 3, 2013
Is there a good, common way to migrate data from relational tables into hierarchical json?
I am asking this because, basically, I prefer not to write a lot of procedural code.
I am currently working with MS SQL Server db and I’ll soon need to do the same with My SQL db. I use java and java db connectivity for access. It would be great if any of these two db’s can somehow assemble the data server-side.
View 4 Replies
View Related
Mar 4, 2008
Hello friends .....What is the answer for this question in frame of SQL 2005 Management Studio or SQL Server 2000 ?
What are the properties of the Relational tables?
View 1 Replies
View Related
Nov 21, 2006
I've not found much guidance so far about the pros and cons of mining OLAP cubes vs their underlying fact and dimension tables. Can anyone offer advice in this area, or point me to more info about this?
Thanks,
Kevin
View 1 Replies
View Related
Jan 19, 2008
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks
SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
View 7 Replies
View Related
Dec 27, 2005
What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and
retrival?
-Chris
View 3 Replies
View Related