How To Write Prediction Query Using Clustering Algorithm?
Mar 2, 2007
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?
View 1 Replies
ADVERTISEMENT
Dec 1, 2006
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
View 12 Replies
View Related
Dec 12, 2006
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?
View 1 Replies
View Related
Dec 14, 2006
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?
View 1 Replies
View Related
Oct 29, 2007
Hi All!
I have few questions regarding Clustering algorithm.
If I process the clustering model with Ks (K is number of clusters) from 2 to n how to find a measure of variation and loss of information in each model (any kind of measure)? (Purpose would be decision which K to take.)
Which clustering method is better to use when segmenting data K-means or EM?
Thanks in advance!
View 4 Replies
View Related
Dec 5, 2006
Hi, all here,
Since we are not able to use accuracy chart for Clustering algorithms there. So how can we verify the accuracy of clustering algorithm models here in terms of its classification and regression tasks?
Thank you very much in advance for your guidance and advices for that.
With best regards,
Yours sincerely,
View 12 Replies
View Related
May 1, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I have a question on sequence clustering algorithm. As generally it is used for sequence analysis especially for web path visiting analysis. Besides that, what else scenarios could we apply this algorithm as well?
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Nov 24, 2006
Hi, 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,
View 7 Replies
View Related
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.
View 1 Replies
View Related
Aug 19, 2006
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
View 3 Replies
View Related
May 22, 2006
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'
View 6 Replies
View Related
Jul 27, 2006
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!
View 1 Replies
View Related
Feb 8, 2008
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
View 3 Replies
View Related
Aug 5, 2006
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
View 3 Replies
View Related
Feb 7, 2007
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
View 5 Replies
View Related
Jul 19, 2007
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?
View 1 Replies
View Related
May 29, 2006
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
View 6 Replies
View Related
Jan 24, 2008
Hi
I have three questions about several topics.
In this code:
public string ConnectionString
{
get
{
return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW";
}
}
What is data source and initial catalog and what does this code do? And if I want to use other database how can change this code? (This code is for data mining viewer client project)
And in this code:
SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True");
SqlCommand cm = new SqlCommand("Select AddressID,AddressLine1 from Person.Address", cn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cm;
DataTable dt = new DataTable();
da.Fill(dt);
this.comboBox1.DisplayMember = "AddressLine1";
this.comboBox1.ValueMember = "AddressID";
this.comboBox1.DataSource = dt;
what is comboBox1.DisplayMember and comboBox1.ValueMember ,and what is difference between those ?
and other question:
in adventure works dw project for data mining predicting ,in forecasting model ,if I want to show the result of this query in the combobox in c# how can I show that?
SELECT
PredictTimeSeries(amount)
From
[Forecasting]
And again in this code ,it has a result which has two culomns ,on of them is for amount and other column is for time ,in sql I can save this result in exsiting table or neew table with wizard,but I want to
Do this work in c#,that€™s mean with a adomdconnection I connect to forecasting model and write this query then in a datagridviwe ,Iwant to see the values of prediction in adventure works dw database.
Other question:
In €śdataminingviwerclient€? project I change this code and you can see it,for this code I have a form that give servername and catlogname in that and then with clcking on a button I want to show the chart of model in a child form ,but I can€™t.
public Form1 form1 = new Form1();
public string m_ServerName;
public string m_CatalogName;
public Form3()
{
m_ServerName = "";
m_CatalogName = "";
InitializeComponent();
}
public string ConnectionString
{
get
{
return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW";
}
}
public void button1_Click(object sender, EventArgs e)
{
connectingtoserver();
AdomdConnection conn = new AdomdConnection();
try
{
this.Cursor = Cursors.WaitCursor;
conn.ConnectionString = this.ConnectionString;
conn.Open();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Connection Failed");
}
this.Cursor = Cursors.Default;
conn.Close();
ShowModel(panel1, form1.comboBox1.Text);
}
public void connectingtoserver()
{
LoadCatalog();
}
private void LoadCatalog()
{
AdomdConnection conn = null;
try
{
conn = new AdomdConnection(this.ConnectionString);
this.Cursor = Cursors.WaitCursor;
conn.Open();
DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null);
foreach (DataRow row in ds.Tables[0].Rows)
form1.comboBox1.Items.Add(row[0].ToString());
}
catch (System.Exception)
{
/**/
}
this.Cursor = Cursors.Default;
if (conn != null)
conn.Close();
}
private void ShowModel(Panel panel, string modelName)
{
AdomdConnection conn = new AdomdConnection();
try
{
MiningModelViewerControl viewer = null;
MiningModel model = null;
MiningService service = null;
// Clear any existing controls from the panel
if (panel.HasChildren)
panel.Controls.Clear();
// Connect to server
conn.ConnectionString = ConnectionString;
conn.Open();
// Determine the viewer type based on the model service and
// instantiate the correct viewer
model = conn.MiningModels[modelName];
service = conn.MiningServices[model.Algorithm];
if (service.ViewerType == "Microsoft_TimeSeries_Viewer")
viewer = new TimeSeriesViewer();
else throw new System.Exception("Custom Viewers not supported");
// Set up and load the viewer
viewer.ConnectionString = ConnectionString;
viewer.MiningModelName = modelName;
viewer.Dock = DockStyle.Fill;
panel.Controls.Add(viewer);
viewer.LoadViewerData(null);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Model Load");
}
conn.Close();
when I run this code ,I have one error that say: the €ś object not found parametr name:index
Please see this code and answer my question.
If you just can answer one of my qestions ,please say.
Thanks a lot for your answers.With best wishes for you
View 3 Replies
View Related
Jun 18, 2008
Hi,
Is SAN a mandatory requirement for clustering?
I mean to implement SQL server clustering, do I have to have my dbs on SAN?
View 2 Replies
View Related
Mar 4, 2007
somebody help me??
View 4 Replies
View Related
Nov 13, 2007
We have 2 env. : Testing and Production, both are running Windows 2003 Enterprise Server with SQL Server 2005. The difference is Testing is NOT running Windows cluster but Production do so, what is the best way to transfer a database from testing to production?
We have another systems that both testing and production are running on NON-cluster and we use backup/restore to transfer the database, can it apply in this case.
And I found that there are a tools called DTC, which can transfer all DB objects from one DB to another, is it a best way to transfer between non-cluster and cluster env.?
View 2 Replies
View Related
Sep 13, 2006
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
View 1 Replies
View Related
Oct 28, 2006
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
View 4 Replies
View Related
Feb 10, 2008
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
View 8 Replies
View Related
Feb 11, 2008
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
View 1 Replies
View Related
May 21, 2008
I have two table named tbl_Scale and tbl_NGTrDAMaster
tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key
tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key
and we get CityTypeID from xml databinder.......
In my form thr is two drop down list one for scale name and another for city type id
this is the data form tbl_NGTrDAMaster
17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412
but i am expecting only one combination of set.....
like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that....
please help me....
i am in big trouble......Thanx in advance
If my qes is not clear for everyone...
plz tell me....
i try my lebel best for understand my prob to u.....
View 2 Replies
View Related
Jun 2, 2004
i have a table
tab
col1 col2 num
A a 30
A b 20
B a 10
B b 40
C a 50
C b 40
now i want get col1 by distinct col1 ,and order by num, as the result:
col1
C
B
A
so can someone help me to write this "select..."
View 3 Replies
View Related
May 17, 2005
Hi
I have 2 tables and I want to Get information from that tables by SQL Query but How Can I writ this SQL Query ? .. My target as Follow
Class Table
-------------------------------------------------
ClassID ClassName
1 AA
2 BB
Student Table
-------------------------------------------------
StudentID StudentName ClassID
1 Student 1 1
2 Student 2 1
3 Student 3 2
4 Student 4 1
5 Student 5 2
6 Student 6 1
How Can I Writ SQL Query to get result like the following ..
--------------------------------------------------
ClassID ClassName StudentCount
1 AA 4
2 BB 2
My SQL Query must get all Class table column plus column content the count of student in each class
And thanks with my regarding
Fraas
View 3 Replies
View Related
May 12, 2006
Hello,
I have a table with fields; T1: Dept, Name, Desc, ModificationDate
How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate
Can anyone write me this query?
View 3 Replies
View Related
Nov 26, 2003
CUSTOMER
Name City IndustryType
Abernathy Construction Willow B
Amalgamated HousingMernphisB
Manchester LumberManchesterF
Tri-City BuildersMemphis B
ORDERS
NumberCustNameSalespersonNameAmount
100Abernathy ConstructionZenith560
200Abernathy ConstructionJones1800
300Manchester LumberAbel480
400Abernathy ConstructionAbel2500
500Abernathy ConstructionMurphy6000
600Tri-City BuildersAbel700
700Manchester LumberJones150
800 Abernathy Construction Abel 75000
SALESPERSON
NamePercentOfQuotaSalary
Abel63132000
Baker3846200
Jones2649500
Kobad2739600
Murphy4255000
Zenith59129800
I have got the three tables above.
Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers.
Thank you very much!
View 1 Replies
View Related
Nov 26, 2003
CUSTOMER
Name City IndustryType
Abernathy Construction Willow B
Amalgamated HousingMernphisB
Manchester LumberManchesterF
Tri-City BuildersMemphis B
ORDERS
NumberCustNameSalespersonNameAmount
100Abernathy ConstructionZenith560
200Abernathy ConstructionJones1800
300Manchester LumberAbel480
400Abernathy ConstructionAbel2500
500Abernathy ConstructionMurphy6000
600Tri-City BuildersAbel700
700Manchester LumberJones150
800 Abernathy Construction Abel 75000
SALESPERSON
NamePercentOfQuotaSalary
Abel63132000
Baker3846200
Jones2649500
Kobad2739600
Murphy4255000
Zenith59129800
I have got the three tables above.
Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers.
Thank you very much!
View 1 Replies
View Related
Apr 5, 2006
User Page Name Permission
vijay customer.aspx 1
vijay customer.aspx 2
vijay customer.aspx 3
vijay user.aspx 2
Rajashekar customer.aspx 1
Rajashekar customer.aspx 2
Where Permission 1 = SAVE
2 = UPDATE
3 = DELLETE
Where I query on User and PageName I want the output as
User Page Name Permission
vijay customer.aspx 1,2,3
vijay user.aspx 2
Rajashekar customer.aspx 1,2
View 2 Replies
View Related
Feb 23, 2006
i am assuming there is a better way to write this query (since im not too proficient in SQL)
sql Code:
Original
- sql Code
select client_id from clients where client_id not in
(select schedule_det.client_id from schedule_det,
schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id
and schedule_mstr.status_code!='COMPLETE')
SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
View 2 Replies
View Related
