Data Mining In DMX: How To Get All NodeID With Probability +ve Result &&> X
Aug 19, 2007
Dear 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?
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.
I am wondering where can I store my mining results in data mining engine? For example, I got mining results like accuracy chart, decision trees, and other formats of results based on different mining algorithms I used for my data mining, so where can I actually store the results for reporting service use later? Is it possible to do that in SQL Server 2005?
Thanks a lot for any help and guidance in advance.
I would like to know if there is any way to migrate third-party data mining packages with SQL Server 2005 data mining algorithms together then we can have a comparison among all of them to get the best results for training models.
Hoping someone will have a solution for this error
Errors in the metadata manager. The data type of the '~CaseDetail ~MG-Fact Voic~6' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count.
Is the problem due to the data type of the column used in the mining structure is Long, and the underlying field in the cube has a type of BigInt,or am I barking up the wrong tree?
I'm a beginner with SQL 2012 SSDT & SSMS. I get this error message when I try to deploy my project:Â
"Error 6 Error (Data mining): KEY SEQUENCE columns are not supported at the case level. The 'Customer Key' column of the 'TK448 Ch09 Cube Clustering' mining structure contains content that is not valid. 0 0 " I am finding it hard to locate the content that is not valid. I've been trying to find a answer for this problem but can't seem to find anything. How can I locate the content that is not valid and change or delete it so that I can deploy this solution?
- a data mining structure with about 80 columns. - a data mining model using Microsoft_Decision_Trees with 2 prediction columns.Â
I thought I would then explore the possibility of have more than 2 prediction columns, in this case 20.
I get an error message and I can't work out : a) if this is because there's a limit to the maximum number of prediction columns and where that maximum is stated. b) if something else has become corrupted c) there's a know bug and if the error message is either meaningful or not.
Either way, I'm unable to complete the data mining wizardÂ
The error message is :Errors in the metadata manager. Either the mining structure with the ID of '[my model Structure]' does not exist in the database with the ID of 'DMAddinsDB', or the user does not have permissions to access the object.
I am using Microsoft_Time_Series and have set HISTORIC_MODEL_GAP to various values (from 1 to 21). I always get this error: Error (Data mining): The 'HISTORIC_MODEL_GAP' data mining parameter is not valid for the 'My Time Series' model.
In Algorithm Parameters window, this parameters is not there by default, so I have to add it.
Implementing data mining Add-in in an academic setting? We need to handle over 150 new students a semester and have their connection to Analysis Services survive for their four years at the college. We are introducing data mining to every freshman business student as a unit within their Intro to Excel class (close to a month of work to give them a sense of what is possible). Other courses later in their curriculum will expand on that introduction.Â
Once implemented, we would have as many as 900 connections to manage (four years from now). It is possible that multiple sections will be running at the same time, so 40 students may be accessing the data mining tools concurrently. Â
Is there a way to "bulk establish" the access credentials and establish those databases?
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining  taking long time to process, so how we can  reduce processing time ???
Hi, I have just run a simple data set through a model to predict a simple true or false value (i.e. binary output) The Lift Chart/Mining Legend in Analysis Services shows three results €“ Score, Population Correct (%), and Predict Probability (%)
Population Correct I beleive is the percentage of predictions it got right out of the total number of predictions it tried to make. Is this correct?
However, I can€™t work out how the other two are derived in particular the 'SCORE'. To give a live example the scores were as follows:
Model Score Pop Correct Pred Probability Decision Trees 0.83 76.59% 54.28% Neural Network 0.75 67.63% 50.05% Ideal Model 100.00%
Can anyone help with this and give a detailed explanation?
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
I perform data mining on all products and a specific product category. Do I need to create 2 data source views, one for all products and the other one for the specific product category? Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures. I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures. Is there any simple way to do it?
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
hi, 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.
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?
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
I am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions; $option_drop.ScriptDrops = $true;
I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?
I am looking forward to hearing from you and thanks a lot in advance for your advices and help.
I 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?
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.
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.
hi I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
I'm stucked in a problem and I thought if you would be so kind as to helping me to resolve it.
I'm implementing a clustering algorithm plugin for text mining. I've already read the tutorials and sample codes provided by the MSDN Library.
Well... My problem is: I can't go through the data when the Predict method is called. I've read that this method implements the "core" of the custom algorithms. Here is a small snippet of my code for you to understand my doubt:
As you can see, I'm going through in_rgValues to get its values, but i'm only obtaining the first register of the table on the database. I need to roll over a kind of resultset so I could access all the registers I need. Is there any way to do so ?
I expected Predict() received a matrix containing all my data, but the only thing I noticed that could represent the data is that in_rgValues vector. So I can go through this vector, but it holds only the first register of the table in the database (that's what's being saved on my log). I need all of the registers in order to pre-process the data and implement my clustering algorithm.
Well... That's it... I would be very pleased if you could help me.
I have very little experience with programming and data mining, but I am working on a project where I need to take data from one spreadsheet and place it in another. Since it is hard to describe what I would like to do, I will provide an example:
In this example, the data in Column 1 is always tied to the data in Column 2 (i.e., 100 in Column 1 means 200 in Column 2, etc.) However, the data for Column 2 is only available in SPREADSHEET 2; moreover, the data is not in the same order in both spreadsheets.
My question is how can I create some sort of program where I can transfer the data from SPREADSHEET 2 into SPREADSHEET 1?
I do have a problem with results of clustering algorithm with my categorical data. In reality I have a big table with one's and zero's and I try to cluster according to 60 attributes. I tries to cluster to two categories many times but I get only one cluster. What do you suggest. Which values can I change to the algorithm? Is there anything particular for categorical data ?
Thank you very much for your previous answers conserning clustering algorithm.
I change a bit my data and now I can get two and three clusters as I wish. But now I have a different question. I wish to keep my Structure and Model for further trials with different algorithm values, like cardinality. I tried the following I selected in excel Data Mining and then the option cluster button. Then in the cluster wizard I chose analysis service data source and I reform the query as I need adding only at the end where "STAFF_YES" = 1. Then I choose the needed columns and target value. Finally I select browse model, enable drill through only and I finish the wizard and get some results, rather good. But i need to try again with different parameters. I tried Business intelligent Studio to check my Analysis service. The structure were here but when I changed any parameter and I tried to reprocess I got the following messages
Processing Mining Structure 'try Structure_1' completed successfully. Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00 Processing Dimension 'try Structure_1 ~MC-__RowIndex' completed successfully. Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00 Processing Dimension Attribute '(All)' completed successfully. Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00 Processing Dimension Attribute 'EPI_CELL_SINO_GOOD' completed successfully. Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00 Processing Dimension Attribute 'EPI_CELL_SINO_LOOSE' completed successfully. Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00 Errors and Warnings from Response Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge'). Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed. Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_LOOSE' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed. Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge'). Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed. Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_GOOD' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.
And no change to the structure take place.
Please help me if you can Thank you in advnce Best regards. Manolis
I would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?
Thank you very much for your help. The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously
I tried the code but initially I have encounter the following problems.
1. In any line that have the declaration As Server, As Database like in Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.
2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.
3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'. I have some more problems but I thing that by solving the above that I referred I will solve the rest.
I just found that I am not able to view the accuracy chart for my mining model. The error message is: no mining models are selected for comparision. Which is quite strange.
