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()
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 have developed a simple logic for finding the trend based on least squares method.Want to know whether a built in function already exists for finding the trend or is being developed for future versions of SQL server
I was hoping to elicit some feedback on a trend I am seeing in the Portal market, and specifically with SharePoint development.
If you are not familiar with SharePoint, there is a data table abstraction within SharePoint called a "List". Lists are used for storing data (duh!). However, they are built using the SharePoint front end, and the data entered into all lists is stored in a few tables in the SharePoint content database.
What I am seeing happening is SharePoint gurus reccomending AGAINST storing your relational data within database tables, and within SharePoint lists instead. I am not sold on this approach, and it actually makes me think we are taking a step backwards with regards to persistent data storage and best practices.
- Lists cannot be natively related to one another, however they support "lookups" - Anyone can create a list...and repeat the same data all over the enterprise. - Lists are maintained in two tables within the SharePoint content database using meta-data patterns. - Portals contain a multitude of sites. Users and portal admins can create lists all over the place, thus spreading related data over a wide swath of the enterprise.
Is it just me, or are SharePoint pundits absolutely CRAZY to be recommending persistent data storage using lists? I see nothing but problems arising from this approach.
I apologize beforehand if you have not worked with SharePoint and Lists, as this post may not make much sense to you. ;)
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As ( SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
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]]]
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...
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.
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!
