I get to page 116 in "Data Mining with SQL Server 2005", but when I try to deploy my model, I get these errors:
Error 1 Error (Data mining): The 'Movie Bayes' mining model cannot have more than one predictable attribute. 0 0
Error 2 Errors related to feature availability and configuration: The 'Multiple prediction targets' feature is not included in the 'Standard Edition' SKU. 0 0
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.
The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.
Any ideas would be greatly appreciated.
thanks
John
I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?
My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.
I have consulted the following without finding an answer:
http://msdn2.microsoft.com/en-us/library/ms178445.aspx
http://msdn2.microsoft.com/en-us/library/ms175642.aspx
http://msdn2.microsoft.com/en-us/library/ms175678.aspx
http://msdn2.microsoft.com/en-us/library/ms175637.aspx
Thanks for your help,
Sam
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.
I am cutting my teeth on star schema design. I have a simple star schema I am building for Headounct analysis at work. I have a factless fact table where a row represents a head in the company. Each head is toed to a particulat week in a Date dimension tabel. There are additional dimensions for things like gender, ethnicity, marital status, age, etc. Now in my department dimension - it's hierarchical. In the DimDepartmnet there is a department which belongs to a company. Comapnies belong to divisions. Now the fun part. Each division has a headcount target for each year. Up to this point I am in a perfect star schema (no snow flaking). How would I integrate in this concept of a headcount target for each division for a given year?
We are using cognos on top of this star schema to provide reporting and analysis services if that is relevant. From the Star Schema design stand point... any thoughts?
Christian Loris
Well 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
Hi ,
I am a novice Data Mining Programmer.
I am using Time series algorithm for forecasting.
We are Quite concerned about the accuracy of Prediction output.
For Example Our Data is like this
StudId Date Perf
001 01/01/2001 90
001 02/01/2001 89
001 03/01/2001 87
002 01/01/2001 59
002 02/01/2001 70
003 03/01/2001 47
If I write my Prediction Query to predict for 100 th time step.Its giving me out put like
Date Perf
03/01/2015 47.000000115
We are not sure about the accuracy of the values. Is it possible to use trend information as input to my model and make my prediction based on that.
I don€™t know how to do that? Can anyone help?
Thanks,
Karthik.
Hello,
Here's the thing base on AdvantrueWorks: case - orders, nested - products.
I'd like to select-predict models and their identifiers.
So, I added new id column to the relational view.
And then I added the column to the nested table in my structure as predict only.
The problem - all predictions changed dramatically.
For example when I run my query again:
SELECT PREDICT([Association].[Products], 5)
From [Association]
NATURAL PREDICTION JOIN
(SELECT (SELECT 'Touring Tire Tube' AS [Model]) AS [Products]) AS t
I got the new column i added, but the results are far different then before (and incorrect).
Is there a easy way to workaround it?
hi,
I am using time series agorithm.I need standard deviation in %. I am using SELECT StudID, PREDICTSTDEV([Perf]) FROM [Stud_Model].This one is giving me the standard deviation like this
StudID stDev
001 2.891298978779
002 2.797288978779.
But I need like this
StudID stDev
001 +50%
002 +51%(From The Previous) like that.
Is it Possible.
Thanks,
Karthik.
Hi,
I have the following
association model Structure
Where I am trying to find out
the associations between various service activities so that when a customer
buys a service activity we can recommend him/her others
CaseTable
CustomerId à Key
IncidentId
Nested Table
ServiceId à Key
ServiceName à Input, Predict
IncidentId à Link to the case table
Firstly is the above
structure correct??
Secondly
I have the following prediction
query
SELECT
t.[ServiceId],
Predict
([ServicerossSell].[ServiceCross-SellRecommend],3)
From
[ServicerossSell]
PREDICTION JOIN
SHAPE
{OPENQUERY([Adventure
Works Cycle MSCRM],
'SELECT DISTINCT ServiceId
FROM Service ORDER BY ServiceId]')
}
APPEND
( {OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT ServiceId
FROM Service ORDER BY ServiceId]')
}
RELATE
[ServiceId] To [ServiceId]
)
AS
[Service]
AS t
ON
[ServicerossSell].[ ServiceCross-SellRecommend].[ServiceId]
= t.[Service].[ServiceId]
It throws the following error
€œThe DMX column was not found in the context€?
I am not able to figure out why, any help would be most
appretaiated
Mining Model Prediction... what for?
Which Data Mining algorithm use Mining Model Prediction?
Every algorithm has to use Mining Model Prediction for a final goal of a Data Mining Project?
hi,
when i make use of predicton functions, the output is formatted in its own.But I want to format that.How to do that?
Thanks,
Karthik.
Hey
Does anyone know if the following is possible:
I want to add a column to a table that contains the predicted value according to a decision tree mining model. (I know that this is possible). But now I would like that when a new row is added to this table, and every column except the prediction column is filled in manually, can ms sql server add the predicted value automatically for this row?
I know it is possible to execute a Singleton query for this kind of single prediction, but I would like to integrate this in my data table, because for now my steps would be:
- Create the table with one prediction column
- Add the known values of all columns for one row
- Use singleton query in Mining model prediction tab to know the predicted value
- Fill in the predicted value manually in my table.
I hope my question is clear.
Thanks in advance for the help.
Smileyke
Hi,
I am trying to deploy a Naives bayes network.But in spite of I am getting an dependecy network that relates 3 variables,
I've got an classification Matrix where all the predicted values are the same.
Counts for nai 1 on [Cod Conceito]
Predicted
Missing (Actual)
0 (Actual)
1 (Actual)
2 (Actual)
3 (Actual)
4 (Actual)
5 (Actual)
6 (Actual)
7 (Actual)
8 (Actual)
9 (Actual)
Missing
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
2
0
57578
2499
11292
102
2374
317
80
271
989
2
0
0
0
0
0
0
0
0
0
0
0
3
0
0
0
0
0
0
0
0
0
0
0
4
0
0
0
0
0
0
0
0
0
0
0
5
0
0
0
0
0
0
0
0
0
0
0
6
0
0
0
0
0
0
0
0
0
0
0
7
0
0
0
0
0
0
0
0
0
0
0
8
0
0
0
0
0
0
0
0
0
0
0
9
0
0
0
0
0
0
0
0
0
0
0
I tried to deploy the same data on Tanagra's software and I've got predictions on the other values.
I tried to change all the parameters but the results are always the same
thanks in advance
i have mining model with 20 columns; 10 columns are for data (A1,A2...A10)
and 10 columns are for prediction (B1,B2...B10) data is not in nest table, just one table
using Association Rules
A1 text
A2 text
...
A10 text
B1 text prediction only
B2 text prediction only
...
B10 text prediction only
i have rules as form Ai-->Bj.
i want to make a statement to prediction Bj values when i have Ai values, with Ai get from some textbox on screen, Can you show me some Examples.
Thanks
Hi All,
I was wondering if there was a way to specify a range when training a model to predict continuous variables. For instance, the predicted variable can only have a range of 1 - 10.
Thanks
Hi again....
I have tree models trained and now how do I perform predictions? I've read that i can use ADO in my vb language so could anyone give me some code lines about prediction using ADO and Vb language? and how to view the prediction in my vb application?
Thank's a lot
I have a market basket model using associations. It generated several dozen itemsets. However when I attempt to run a singleton prediction like this:
select (Predict(Orderproduct3q,INCLUDE_STATISTICS,10)) as [Recommendation]
From
[Case All]
NATURAL PREDICTION JOIN
(SELECT (SELECT '16407' AS [Pname])) AS t1
the resulting predictions don't take the itemsets into account. Instead, the predictions consist of the ranked products in the training set, ordered by frequency. This appears to happen regardless of the precise query specified within the "natural prediction join".
What's going on here and how do I generate a singleton prediction which makes use of the itemsets?
Hi!
I'm building a mining model wiht MS Association Rules. After processing this model, the result includes some rules(example):
E = Existing, C = Existing -> B = Existing
F = Existing -> E = Existing
C = Existing, B = Existing -> E = Existing
F = Existing -> B = Existing
B = Existing, A = Existing -> C = Existing
F = Existing, B = Existing -> E = Existing
F = Existing, E = Existing -> B = Existing
D = Existing -> A = Existing
C = Existing -> A = Existing
E = Existing, A = Existing -> B = Existing
I want to buid a query that has two or more items on the left of the rules, example: E = Existing, C = Existing -> B = Existing
->I want to buid a query to predict that: when a customer buy 'E' and 'C' then he likely buys 'B'
If your prediction join is to a SQL datasource, you can easily write a SQL query which returns a nested table like:
SELECT
Predict([Subcategories],2) as [Subcategories]
FROM
[SubcategoryAssociations]
NATURAL PREDICTION JOIN
(SELECT
(SELECT 'Road Bikes' AS Subcategory
UNION SELECT 'Jerseys' AS Subcategory
) AS Subcategories
) AS t
What about if your datasource is a cube? Is there some special MDX syntax similar to the SQL syntax above? Or do you have to utilize the SHAPE/APPEND syntax as follows?
SELECT t.*, $Cluster as ClusterName
FROM [MyModel]
PREDICTION JOIN
SHAPE {
select [Measures].[My Measure] on 0,
[My Dimension].[My Attribute].[My Attribute].Members on 1
from MyCube
}
APPEND (
{
select [Measures].[Another Measure] on 0,
NON EMPTY [My Dimension].[My Attribute].[My Attribute].Members
*[Product].[Product].[Product].Members on 1
from MyCube
}
RELATE [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
TO [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
)
AS [My Nested Table] AS t
ON [MyModel].[Product].[Product] = t.[My Nested Table].[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]
Hi I made an model based on MS time series
and want to see some result in Mining Model Viewer (SSAS 2005)
The chart displayed under tab "Charts" is as expected, but when i increase
"predictions steps" onlsy the shadowed part of chart becomes bigger, but the prediction curve behaves unchanged...
What can I do?
Thanks
I am trying to use the time series algorithm to predict responses to promotion mailings for subscription renewals. The problem i am having is that response is largely influenced by the number of mailings that are sent out. Can anyone give me any ideas on how i can structure the dataset so that it would take into account how many promotions were sent out? any help would be greatly appreciated.
Frank
hi,
Is it possible to use two algorithms together?I need to write prediction Query so that its should both models having clustereing algorithm and timeseries algorithm.
for example
I am having student information.I ve to predict performance of students for certain period.The students should be classified by their types like rich kids,poorkids..like that.I need to predict the performance of the rich kids??
can anyone help?
Dear friends,
I'm reading Wiley's Data mining with SQL Server 2005... There are MANY things I can't understand about MovieClick example (Chapter 3).
I hope someone is going to help me with this troubles...
WARNING (1): I'm a dummy both with sql server and data mining.
WARNING (2): My English is not good at all.
Just two questions for now:
1) When I create the model to predict the number of bedrooms for homeowners, the book says to check BEDROOMS as Predictable... question: is it also an INPUT for the model, or PREDICTABLE only?
2) I'd like to keep this model (number of bedrooms.......) and make a prediction query.
- Query builder
- select case table -> Homeowners
- Drag the Customer ID column from the Homeowners table and drop it on the grid
- Drag the BEDROOMS column from the mining model and drop it on the grid.
- On the last row: Source=PredictionFunction, Field=PredictProbability
- Drag the BEDROOMS column from the mining model and drop it into Criteria/Argument
- Add (i.e.) 'Two or Three' to the field Criteria/Argument
I execute the query and I obtain many rows in a table with the following colums: CustomerID, BEDROOMS and Expression: WHAT DOES THIS MEAN?
WHICH INFO DO I GET FROM THOSE NUMBERS? WHAT CAN I LEARN FROM THEM?
Thanx a lot in advance, please help me!
I am doing this right now this way:
1) I do the DMX prediction query where I get the PredictNodeId(predict_var), my query is like this:
SELECT PredictNodeId(predict_var), model_1.predict_var, t.var_1, t.var_2 FROM model_1 PREDICTION JOIN OPENQUERY([DATA_SOURCE_1], 'SELECT var_1, var_2 FROM table_1') AS t ON model_1.var_1 = t.var_1 AND model_1.var_2 = t.var_2
2)I do the DMX query to get the node_description from the model.content iterating each row from the result of my prediction query, this query is like this:
SELECT node_description FROM model_1.content WHERE node_name = 'node_name_var'
In this query node_name_var = PredictNodeId(predict_var) from my prediction query.
What I want to know if there is a way to merge Query 1 and Query 2 so I can get the node_description in the same query qhere I get the PredictNodeId.
Thanks
Juan José Jara
Hi,
Can i use a CASE statement in a prediction query.
the following query is throwing me an error
SELECT
CASE [Sales Forecast Time Series].[City Code]
when 'LA' then 'Los Angeles'
WHEN 'CA' THEN 'California'
ELSE 'OTHERS'
END,
PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5)
From
[Sales Forecast Time Series]
ERROR:
Parser: The statement dialect could not be resolved due to ambiguity.
Also
Is it possible to discretize the
Sales Value column using a the CASE statement, the output column of
PredictTimeSeries function.
Is there a link that can give me a
comprehensive info on what can be achieved and what cant be using DMX queries
Is there a way to display the actual predicted value for an output attribute for a particular model. For example, say I am trying to predict if a particular customer is going to take advantage of a promotion (0=no, 1=yes) and I use neural networks. I know that I can use "Predict" to give me the prediction "yes" or "no" for each customer. However, the neural network actually spits out a number as a result. For example, a 0.997 would be interpreted as a "yes" for life insurance promotion. I do not want the probability that the prediction is correct. I want the actual output for the network.
The reason being is that I want to compute an error rate between the predicted value and the acutal value (root mean squared error or some other measure). Is there a way to compute this using the mining model prediction tab design view? I do not want to write the actual query as I teach a course in data mining using SQL Server and my students do not know DMX queries.
Thanks for any help you can provide.
For a set of data points (x, y), this algorithm can be used to fit the data to any of the following curves:
1. Straight line (linear regresion); y = A + b*x
2. Exponential curve; y = A*EXP(b*x); nb a > 0
3. Logarithmic curve; y = A + b*LN(x)
4. Power curve; y = A*x^b; nb a > 0
The coefficient of determination is R2 (how well does the curve fit)
-- Prepare test data
CREATE TABLEcf
(
x decimal(38, 10),
y decimal(38, 10)
)
-- Calculate Linear regression
INSERTcf
SELECT40.5, 104.5 UNION ALL
SELECT38.6, 102 UNION ALL
SELECT37.9, 100 UNION ALL
SELECT36.2, 97.5 UNION ALL
SELECT35.1, 95.5 UNION ALL
SELECT34.6, 94
SELECT'Linear regression' AS Type, A, b, R2
FROMdbo.fnCurveFitting(1)
UNION ALL
SELECT'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROMdbo.fnBestFit()
-- Calculate Exponential regression
DELETE
FROMcf
INSERTcf
SELECT.72, 2.16 UNION ALL
SELECT1.31, 1.61 UNION ALL
SELECT1.95, 1.16 UNION ALL
SELECT2.58, .85 UNION ALL
SELECT3.14, .5
SELECT'Exponential regression' AS Type, A, b, R2
FROMdbo.fnCurveFitting(1)
UNION ALL
SELECT'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROMdbo.fnBestFit()
-- Calculate Logarithmic regression
DELETE
FROMcf
INSERTcf
SELECT3, 1.5 UNION ALL
SELECT4, 9.3 UNION ALL
SELECT6, 23.4 UNION ALL
SELECT10, 45.8 UNION ALL
SELECT12, 60.1
SELECT'Logarithmic regression' AS Type, A, b, R2
FROMdbo.fnCurveFitting(1)
UNION ALL
SELECT'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROMdbo.fnBestFit()
-- Calculate Power regression
DELETE
FROMcf
INSERTcf
SELECT10, .95 UNION ALL
SELECT12, 1.05 UNION ALL
SELECT15, 1.25 UNION ALL
SELECT17, 1.41 UNION ALL
SELECT20, 1.73 UNION ALL
SELECT22, 2 UNION ALL
SELECT25, 2.53 UNION ALL
SELECT27, 2.98 UNION ALL
SELECT30, 3.85 UNION ALL
SELECT32, 4.59 UNION ALL
SELECT35, 6.02
SELECT'Power regression' AS Type, A, b, R2
FROMdbo.fnCurveFitting(1)
UNION ALL
SELECT'Bestfit = ' + CAST(Type AS VARCHAR), A, b, R2
FROMdbo.fnBestFit()
DROP TABLE cf
Peter Larsson
Helsingborg, Sweden
If I use this code with an association model, it still returns itemsets for me - when it should be returning only nodes with rules associated with them (according to sqlserverdatamining.com). If I try adding 'AND $PROBABILITY > .25' to the where clause, it returns 0 results for every query I try. Any clue why this may be happening?
Code Snippet
SELECT FLATTENED
(SELECT * FROM PredictAssociation([Product],20,
INCLUDE_NODE_ID,INCLUDE_STATISTICS)
WHERE $NODEID<>'')
FROM
[ProductRecommend]
PREDICTION JOIN
OPENQUERY([ds],
'SELECT
[PRODUCTCLASSID],[DESCRIPTION]
FROM
[Product_Table]
WHERE
[PRODUCTCLASSID] = ''1234'' AND [DESCRIPTION] = ''DESC''
') AS t
ON
[ProductRecommend].[Product].[PRODUCTCLASSID] = t.[PRODUCTCLASSID] AND
[ProductRecommend].[Product].[DESCRIPTION] = t.[DESCRIPTION]
This query returns more relevant results than those lacking the filtering by $NODEID, however the results should have higher probabilities than .047! Please help! Thanks!
hi,I am a novice SSAS Programmer.I need a prediction Query in time series algorithm, so that it should predict for a particular date.I dont know how to use where condition in a prediction Query.
Thanks
Karthik
hi,
I am having data like this
Studid Date Perf
001 01/01/2008 90
001 02/01/2008 89 Cluster 1
001 03/02/2008 91
002 01/01/2008 75
002 02/01/2008 79 Cluster 2
002 03/02/2008 69
I wants to create two clusters cluster1 for studid 001. cluster2 for studid 002.
How to write Prediction Query using clustering algorithm?
Can anyone show me how to run a prediction query and save the results to a sql table without using the T-SQL OPENQUERY tip here http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx? I am looking for an example in vb.net that I can use in a SSIS script task.
Thanks
I have a question about what is possible with a prediction query
against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender],
27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?