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.
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...
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?
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
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?
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
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
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 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.
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
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.
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?
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?
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]]]
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.
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??
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?
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.
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.
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()
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.
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.
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?
I have a question about writing a prediction query against a clustering model that has the same column added more than once.
Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is at a day level in my database. But I am aggregating it to month level in the DSV of the mining model.
I am required to make only historical predictions using the above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count and Historical_Model_Gap parameter values to 24 and 10 respectively, and trying to predict for the past few months (PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
Error(Data Mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, Sales Forecast, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how these two parameters affect the historical predictions. I kind of understand that these two parameters are important for historical predictions but don€™t know why or how.
