Decision Trees, How Is Prediction Probability Calculated?
Jul 26, 2007How is the value of Prediction Probability calculated in the context of decision trees?
View 7 Replies
ADVERTISEMENT
Predict Probability In Decision Trees
Dec 13, 2006Hello,
I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):
SELECT
T.[Last Name],
[Bike Buyer],
PredictProbability(Predict([Bike Buyer])) AS [Probability]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY
(....... And so on..)
Now the result is surprising to me. In the resulttabel all the probabilities are equal.
Bike Buyer Probability
1 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
1 0.99994590500919611
and so on.
Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?
Thanx in advance,
Joris Valkonet
Decision Trees
Nov 6, 2006I am studying the behavior of 200.000 clients. With the use of decision trees I would like to know if my clients will abandon our service or not. I use a training set of 21.822 clients and I use a predict variable "aband" wich is a discrete variable and it can be 0 or 1. In my training set i have 21.597 cases in which aband is 0 and 255 cases in which aband is 1. Looking at the classification matrix obtained using as input table a testing set (unselected data) I can see that my decision tree doesn't recognize the cases in which aband is 1. Here is the Classification Matrix:
Counts for Dati Training on [Aband]
Predicted 0 (Actual) 1 (Actual)
0 21597 225
1 0 0
What should I do?
Chiara
Decision Trees, DMX And CONTAINS (T-SQL)
May 18, 2006I would appreciate answers to the following doubts I have regarding Decision trees, CONTAINS and using CONTAINS in a DMX query:
1. Does MS decision tree work only off equality/inequality conditions for the nodes? Is it possible to use a predicate as the branch criteria for a node?
2. Can the T-SQL predicate CONTAINS(...) be used in a DMX query? I need to check if a column-value is a substring of another column and create an intermediate column that will enable me to construct a decision tree with the phrase-present/absent branch.
3. Can CONTAINS(...) be used in a select clause? Like -
SELECT CONTAINS(JAT.column1, '"Good day"')
FROM JustAnotherTable;
4. Does CONTAINS(...) support both arguments to be column references? Or, is it mandatory that the pattern (argument #2) has to be a literal string or a variable? E.g.: I need to know the validity of the following expression -
SELECT * FROM JustAnotherTable JAT
WHERE CONTAINS(JAT.column1, JAT.column3);
MS Decision Trees Question
Aug 3, 2007Hi,
I'm new to data mining, and have created an MS decision trees model. The model has the columns age, call outcome, call reason, country name, employee name and gender - all as inputs.
In the mining model viewer, I only get nodes for the age, despite having data for all the other columns.
Can anyone help?
Thanks
Jeremy
Decision Trees Parameters
Dec 8, 2007
Hi,
I'm interested in understanding how the parametes work in the MS Decision Trees algorithm.
As far as I can tell, the MINIMUM_SUPPORT and COMPLEXITY_PENALTY parameters both control the number of splits and hence the depth of the tree.
Unfortunately the BOL descriptions are very brief - so can anyone tell me the difference between these 2 parameters?
Thanks
Jeremy
Calculating R-Squared From Decision Trees
Mar 19, 2007Hello.
I am trying to build a decision tree to predict prices. I have created the tree and looked at the lift charts, but I have not seen any of the traditional statistics I am used to from other programs (R-Squared, F statistics, etc.).
Does anyone have an example of how they calculated R-Squared for a decision tree on a continuous variable?
Thanks,
Brian
Forcing A Branch With Decision Trees
Sep 12, 2007
In a decision tree algorithm, is there a known way to force a branch at a top level? For exmaple, I have 30 known decision patterns that are going to be completely different and I don't want them to intermingle. I wanted to force a branch at the top node on one of the 30 patterns so I wouldn't have to create 30 mining models per client.
Brian
Question About Decision Trees And Neural Networks
May 9, 2006I have some accounting data, with some transaction attributes and amounts.
I'm using Decision Trees to try and predict the next month's amount for certain combinations of attributes.
I've tried two different structures for the model:
A: one with 9 discrete text input attributes.
B: And another with the same 9 attributes + a avarage Amount for all combinations of the nine attribute for every transaction.
When i've processed them and look in the dependency network, it says that the strongest link for the structure A is attribute "1".
And for the second its the avarage-Amount attribute.
Okey, that seems fine, but the second strongest link in structure B is attribute "2".
Shouldn't it be attribute 1 like in structure A?
Second question, if I run the same data in a Neural Network model, the prediction becomes much worst then the decision tree.
I get many predictions that are negative values even though all training data contains positiv values.
The StDev becomes the same for every row also..
What am I doing wrong with that one. I have alot of transactions and a read somewhere that a Neural Network should work better than a decision tree in a case similar to mine.
The score in the "Lift chart" for the Neural Network model becomes 0,00 and for Decision Trees with the same data I get around 110.
'Decision Trees Found No Appropriate Regressors For Model' Question
Dec 7, 2006Hi,
I am using MS Decision Trees algorithm and for a specific model i get the above warning.As a result of that i dont get any splits in my tree. Is there anything i can do to avoid this?
Thank you for reading
Error: Decision Trees Found No Splits For Model
Jan 5, 2007Hi,
I am trying to run one of the mining models from the book "Delivering BI using SQl Server 2005" but I am running into "Decision Trees found no splits for model". The mining structure has 4 columns, the fourth one being marked as "Predict Only". My Cube slice for the model has sufficient data in the cube. I am lost.. Help!!
Regards
Data Mining In 2005 - Same Input Columns Resulting In Different Decision Trees
Dec 12, 2007While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Thanks,
Joe Miller
Data Mining :: Informational (Data Mining) - Decision Trees Found No Splits For Model
Sep 29, 2015I followed the tutorial posted at [URL] ...
Everything was ok until the last step where I had to process the mining structure which resulted in a warning
"Informational (Data mining): Decision Trees found no splits for model, Tbl Decision Tree Example."
What does this error mean? How do I resolve it? Also, I only see the first level in the Mining Model Viewer, I don't see the levels 2 and 3.
Historic Prediction And Prediction Steps
Feb 22, 2008
Hi
After building a model in BI, I want to view the chart of model in mining model viewer, in the chart tab I can just see one prediction value that means for my model do prediction for some time slice and in prediction steps I can specify how many steps, I want to show this chart
In mining model viewer tab we can see the chart of prediction also decision tree and the chart is for showing all of value prediction, and with choosing prediction steps we can specify that show just one value prediction or two or several values. But sometime I can see just one value in chart and sometime I can see several values in chart,
This difference is for my data or no?
And also for viewing historic prediction I should choice €œshow historic prediction€? and before that I should set
Two parameters: Historic_ model _count and historic _model _count,
But I can€™t see historic prediction (sometime this happens)
Please help me.
The Mean Of Using Association With Importance And Probability
Apr 12, 2007hi,
i have a exercise using association datamining
my database have 350 records,
i use 90 records for datamining and it release some rules which i choose on top of mSOLAP_NODE_SCORE,
but when i use select statement to check my result i have 1 records, the same as my result, and 5 records not true;
for example:
rules A=a,B=b-> C=c
select * from <my_table> where A='a' and B='b' and C='c'; ==>1 record return
select * from <my_table> where A='a' and B='b' and C<>'c'; ==>5 records return
C with 3 values c1,c2,c
with the second statement C includes 2 c1 and 3 c2
i don't understand how they work.
i want to choose some best rules can present my database.
how can i choose importance and probability to get best rules.
with database have 90 records and a database have 350 records which values i should use for minimum_probability, Minimum_Support, Minimum_importance...
when i choose rules i should choose on importance or probability.
thanks for your help
DMX Where Clause: Filtering According To The Adjusted Probability
Mar 14, 2008I have a DMX query like this:
Code Snippet
select * from (
select flattened(*) from (
select att1, topcount(predict([Trans Predictor Unified], INCLUDE_STATISTICS), $Adjustedprobability, 7) as predictedstuff
from [Trans Predictor Model]
prediction join
SHAPE {openquery(DMSCS, 'select distinct CAST(att2 as nvarchar(100)) att1 from DMSCS.dbo.CartProducts order by att1 ')}
append
({openquery(DMSCS, 'select CAST(att2 as nvarchar(100)) att1 , att4, att5 as att3
from DMSCS.dbo.CartProducts order by att1 ')
}
relate [att1] to [att1]) as [Trans Predictor Unified]
as SHAPEQ
on [Trans Predictor Model].[Trans Predictor Unified].att3 = SHAPEQ.[Trans Predictor Unified].att3
) as s
) as t where [predictedstuff.$AdjustedProbability] > 0.5
It's working well. I would like to modify one thing. I would like to chang ethe constant in the where condition, so that it is configurable. That is, I would like to store the constant somewhere (SSAS or relational SQL). I was reading the DMX reference, but it doesn't provide much details about the where's "condition expression". And I looked at a document called "OLE DB for Data Mining Specification version 1.0" of July 2000, which does have in Appendix B the SELECT grammar. There it has
<expression> -> <value>
[...]
| ( SELECT <expression_list> FROM <expression> <where_clause>
[...]
<where_clause> -> WHERE <expression>
If I change the end to
where [predictedstuff.$AdjustedProbability] > (select 0.5 from [Trans Predictor Model] )
, however, just to force some form of query there I get a message saying "The specified column was not found in the context".
I'm running SQL Server 2005.
thanks,
Gustavo
PredictCaseLikelihood Returns Low Probability For Sequences That Are Very Frequent
Jun 28, 2007I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?
View 2 Replies View RelatedQuestion On Value Of Probability Of Value 1 Or 2 In Neural Network Viewer
Jul 16, 2007Hi, all,
I am confused about the value of Probability of Value 1 or 2 (on a particular attribute value) in Neural Network viewer. E.g. the value of Probability of value 1 is actually very low (the same to the value of Probability of value 2), but why the bar which shows the strength of the probability of these two values are still so strong even stronger than other values of probability of value 1 or 2 based on other attribute values which have a much higher probability of value 1 or 2?
And how does the algorithm calculate the Probability of attribute value in nerual network by the way?
Hope my question is clear.
I am looking forward to hearing from you shortly and thanks a lot in advance.
With best regards,
Yours sincerely,
Data Mining In DMX: How To Get All NodeID With Probability +ve Result &&> X
Aug 19, 2007Dear All,
In a data mining model with decision tree algorithm. For example I have the following train case table:
StudentID, IQ,EQ, IsPass.
I put all data in the table into the microsoft decision tree datamining model
StudentID is the key for datamining model
IsPass is prediction only data
IQ, EQ is the input.
1. How can I make a DMX selection to find out all NODE_UNIQUE_NAME with probability of IsPass >0.7.
2. How can I make a DMX selection to find out all the StudentID which belongs to the criteria defined by the Node?
Thanks and regards
Tony Chun Tung Siu
Why The Node_distribution.PROBABILITY Greater Than 1 In Clustering Algorithm?
Nov 24, 2006Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about the node_distribution.PRABABILITY. Some of the attribute values though have a small number of support for the specific node, but why it has a big node_distribution.probability even greater than 1? How can the node_distribution.PROBABILITY be greater than 1? How dose SQL Server 2005 data mining engine calculate the node_distribution.PRPBABILITY for its Clustering algorithm? Really confused and need guidance for that.
Thank you very much for your help.
With best regards,
Yours sincerely,
Strange Problem With Mining Model Node_distribution.Attribute_value And Its Probability
Nov 24, 2006Hi, all here,
Thank you very much for your kind attention.
I dont understand another problem within my mining model. When I query the mining model content ,finding that the same attribute_value have different support and probability for the same node within my clustering model. Why is that? Really confused. And really need help for that.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
CTE For Trees
May 28, 2008Hi,
My database looks like:
CategoryID ParentID Title Sort
1 -1 Cars 1
2 1 Honda 1
3 -1 Bikes 2
4 1 Ford 2
5 1 Toyota 3
6 3 Kawasaki 1
How can I retrieve the values in the following order:
1, 2, 4, 5, 3, 6
I have:
WITH MYCTE(categoryID, parentID, Title, Sort)
(
SELECT TOP 1 categoryID, parentID, Title, Sort
FROM Categories
WHERE parentID = -1
ORDER BY Sort ASC
UNION ALL
SELECT c.categoryID, c.parentID, c.title, c.sort
FROM Categories c
INNER JOIN MYCTE cte ON (cte.categoryID = c.parentID)
)
SELECT *
FROM MYCTE
It doesn't seem to work though? Help! hehe
Working With Trees And XML...
Oct 26, 2005Hi, for a new project i'm trying to build a tree structure in SQL using one table with 'Node' & 'ParentNode' fields along with 'title', etc.
Table = Tree
Node : ParentNode : Title : Show_Record
1 0 Root 1
2 1 Child 1
Then i'm trying to get SQL to return that in XML to my Tree Control 'oBout ASP TreeView'.
Now the tree control can accept XML fine as long as it's in a set format, which shouldn't be difficult and should cut my code from 200 lines to one.
However getting SQL to return the table records in XML is proving to be a total nightmare.
I've hunted the web but not getting very far, I've even got a couple of O'Reilly guides but still no luck, so any help would be excellent with this.
I wrote a sql query (basic 'select * from tree for xml raw') which returns the results in RAW XML, but when I run this in Query Analyser it returns the results as one long string broken up with '<' & '>' but gets to the third record and cuts off halfway.
<row node="1" parentnode="0" title="Root" type_image="book.gif" type_expanded="True"/><row node="2" parentnode="1" title="Service Delivery" type_image="page.gif" type_expanded="False"/><row node="3" parentnode="1" title="Business Support" type_image="page.
Anyone know why Query Analyser does that?
Any help in this much appreciated, as you can imagine i'm at my wits end.
:eek:
Predicting In Trees
Jun 30, 2006Hi! I have created a DMM using Trees. But when I go to the Mining Model Predition tab and select a Predict function, I get this in the criteria column: <Scalar column reference>[, EXCLUDE_NULL|INCLUDE_NULL][, INCLUDE_NODE_ID]. When select Result, I get this error: "An incorrect number of arguments are used in the function at line 3, column 3." I'm predicting a continuous variable.
But when I delete everything except <Scalar column reference> I get this error: "Parser: The syntax for '<' is incorrect."
When I delete everything in the criteria column, I get this: "Query execution failed."
If I change the criteria to "<Scalar column reference>,INCLUDE_NULL, INCLUDE_NODE_ID" I get the error again that the query execution failed.
I'm working from a data set I created. I had no problems with predictions using clustering, but can't seem to get Trees to work.
Autoregression Trees
Mar 6, 2007hi,
I am using Time series alogorrithm.I just wants to know about the autoregression tree.I am having data like
Studid Date Perf
001 01/01/2007 90
001 02/01/2007 95
001 03/01/2007 89
002 01/01/2007 79
002 02/01/2007 90
002 03/01/2007 95
Like that. when I use my Model Viewer --> Descision Tree --> It shows like
Perf = 90.0084 + 1.02 * Perf(-2) + 0.25 * Perf(-2).
What is this value and how its getting calculated?
SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables
Apr 20, 2014I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
Design Decision
Dec 10, 2007Hello everyone
I'm designing a new database for a project. In this database I have a calendar table with with the following columns:
id, dateValue, year, quarter, week, month, englishMonthName, day, englishDayName, dayOfTheYear, isWeekendDay
This database also has a session monitor that logs every access to the database, with a relation to the calendar row id. This way, I can make database access reports, without replicating the date value.
My question is: for a membership table should I follow the same principle and relate member row to the session monitor, which in turn, relates to the calendar or should I put the date just there?
Some of the tables of this database will have to handle some heavy load, both for updating and selecting. This said, my question is should I make a link or put the date just there to extinguish the need to make 2 joins just to know when something was registered / updated? If I only place the relation to know the date I'll have to do something like:
SELECT
DATEADD(ss, x.timeOffsetInSeconds, c.dateValue) AS date
FROM
<somewhere> x
JOIN sessionMonitor sm ON sm.id = x.sessionMonitorId
JOIN calendar c ON c.id = sm.calendarId
Instead of just doing a select x.lastUpdateDate
How would you gurus usually deal with these situations?
Best regards
Index Decision
Jan 17, 2008I've decided to put the clustered index on the edit date column on an audit table. As the edit date for a new record is always going to be higher (more recent) than the previous record, the value would go onto the end of the index. So is there still a value in (1) providing a fill specification of less than 100% and (2) padding the index?
View 13 Replies View RelatedPLZ HELP ME WITH THE DECISION TREE!!
Apr 16, 2007I'm having this problem.....
I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?
Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)
my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.
wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?
i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......
Thank you again.........
DTS Prediction
May 17, 2006Well thanx a lot because i've got the reply. And now i want to ask
about prediction data mining using DTS. Should i make some code to
perform in my vb application? and how to join my table case with the
tree result?
Thank you so much
DTS Decision Making & Email
Nov 13, 2001Playing with DTS for converting text data to SQL Server table and sending email messages. Package is to be scheduled hourly.
1. Is there any easy way to force DTS to choose Success or Failure based on a SQL Task? Wish to check contents of a table for new values and branch accordingly in DTS.
2. When new records are brought in I wish to build a custom email message rather than attaching a text file. Any Idea how to build a custom message?
I am attempting to auto scan an error log on a Unix based system and email myself when errors occur. This is an exercise to test scheduling of jobs, bridging between technologies and sending out meaningful messages based on the process via email.
Thanks,
Mike Hoyt
OLTP Vs Decision Support
Mar 2, 2004Whilst on the Nth hour (n = many) of my magical journey through MS Sql BOL I've come across OLTP Vs Decision Support. After a couple of searches here could someone shore up the following for me please...
A decision support database is the same as warehouse database.
This is for static data commonly used for reporting and analysis.
OLTP is a live database (accomodates inserts, deletes, updates etc).
Is that right?
Also would it be fair to assume that a decision support database is generally going to be spawned from the historical data of an OLTP database? Any real world examples of these two terms would be greatly appreciated too.
Cheers
Dan
Decision Tree In MS SQL Server
Jun 28, 2007Hi,
Can we represent the Decision Tree in a programatically way in an .NET application? I understand that the outcome of a Decision Tree model can be integrated into an .NET application but not sure if we can also visualize it. Does MS SQL Server support any API to render such a tree?
Thanks a lot!