Howto: Save Prediction Query Results To Relational Table

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


ADVERTISEMENT

Save Prediction Query Results To A SQL Table

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

Save OLAP Query Results To Relational Table

Nov 15, 2007

I have cubes that hold quite a few calculations and so creating Excel pivot table views from it take a long time. This is even true for Excel 2007.

Now I wonder if it would be possible to write back all the calculation results to a relational table - maybe one that exactly matches the report format - so creating another report would be much faster?

SSRS seems to be a way to go but it does not speed up my Excel case.

I read about write-back in ROLAP and MOLAP but I don't think any of these concepts help me to really speed up my reports.

The closest thing I was able to find so far, which besides seems to do exactly what I want is Microsoft's new PerformancePoint 2007. It's just it seems overkill for my projects and the price is at $20K.

Any suggestions are appreciated.

Dirk

View 1 Replies View Related

How Do I Save My Query Results Into New Table

Apr 29, 2008

How do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---

View 2 Replies View Related

Howto Use The Query Results As View Column?

Sep 17, 2006

Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua

View 2 Replies View Related

Howto: Historical Prediction In Sales Forecast Model

Aug 3, 2006

Hello...

 

I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?

 

Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...

The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)

View 1 Replies View Related

Save Results Of Query To CSV File

Aug 20, 1999

I want to write a small program (maybe just a stored procedure) that will save the results and column names of a simple query to CSV file. This will then be copied onto disk and distributed to user who'll use Excel or a similar package to open the CSV file.

Any suggestions as to the best way to achieve this? I was considering using a view and then BCP but I have problems then when trying to open the CSV file in excel (or other spreadsheet package). Doesn't like the datatypes and so on.

Is there a simple way to do this? And is it possible to save the results in such a way that Excel will choose the right datatypes for the columns. (not convert varchar's like '000122' to numbers.)

View 1 Replies View Related

Save Query Results Into Text Or CSV File

Apr 19, 2013

I found this topic from this link: Save MySQL query results into a text or CSV file | a Tech-Recipes Tutorial

I am try to create the text file from query results but it didn't work and got this error: "Incorrect syntax near the keyword 'INTO'.

SELECT sale, del
FROM order
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'

View 10 Replies View Related

Sql Task To Save Query Results To File ?

Sep 14, 2007

In sql mgt studio, I can save the query results to a file. How would I do this in an SSIS package?

View 2 Replies View Related

SQL 2012 :: Save Query Results Directly Using Variables And Date / Time

Sep 1, 2015

I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory

As an example I have created a (simple) stored procedure:

USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)

[Code] ....

What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)

The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"

The directory: D:SQLServerResults

View 9 Replies View Related

Dumbest Question Of The Day: Run A Simple Query And Save The Results Into A Text File

Jul 20, 2005

Can someone demonstrate a SIMPLE way to do this that does not requireadditional functions or stored procedures to be created? Lets say Iwant to execute the following simple query - "select * from clients" -and save the results to a text file, we will assume c:
esults.txtHow can I do this in one step?

View 3 Replies View Related

Mining Model Prediction Results

Feb 11, 2008

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.

View 5 Replies View Related

Trying To Save Editted Textbox Value In Table But Original Value Saves Instead - Trouble With Table Update Query

Jan 9, 2008

This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.

View 1 Replies View Related

I Want To Save Two Texts In One Column Of Table.What Is The Query For This?

Jan 21, 2008

I am trying to do one scheduling  website for my company. Its contains  tasks thats we scheduling  for ourself each one and the assigning task by the boss to everyone. I want to do this with two tables. I need to save the task and assigned task in one column named as "Tasks" and to gave after the task name assigned or myself scheduled. Also after deletion of each assigned and scheduled it must save in the table with some name like deleted or any symbol. How can I do this?please help me to solve this issue. 

View 1 Replies View Related

Save Image Into A Table Using Query Analyzer?

Oct 2, 2003

I have a table with two fields Part_num and Pic in SQL server 2000 Pic is of Image type. Is there a way I can save images for each part_num using Query analyzer?

View 6 Replies View Related

Regarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.

Mar 8, 2008

Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
Groups Gr,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Gr.G_ID_PK =Aud.Audit_Group and
Ad_ID_PK in (select Ad_ID_PK from Audits)
Now i want to edit these names.
when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table.
For example:
i have this data in my audits table:




Commercial83312

2
2
2
1
Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs.
when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table.
so the data may be changed in audits table as :



Commercial83312

4
2
2
1

 




so here the industry ID is changed
I need the stored procedure for this.
please help me,its very urgent...

View 4 Replies View Related

Programatically Query Database And Save Report In An Excel Table

May 2, 2007

Hello All,



I am working on constructing a software layer around some features of the RDL language that would allow me to programatically generate reports.



I am reading the RDL specification language, and I do not understand three things:



1) How the DataSet element is populated by the query or more precisely how do the <Field> elements capture all the rows inside the table that is being queries?



To my understanding I wilkl have to define several fields that correspond to all columns of interest in the query.



But that is only for one row (?!) How do the rest of the rows get populated? Does the server recursively create new rows based on my definitions until it matches row for row all the data in the table?



2) Once the elements are inside a DataSet how do make use of that data to render it in a table.



I understand how the DataSource, DataSet, and Table work individually, yet I do not understand how to establish a flow of data between DataSet and Table.



3) Do I even need to use a <Table> as an RDL element in order to organize the data in an excel table?





I would appreciate any help. Thank you!

View 1 Replies View Related

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,



I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.



With best regards,



Yours sincerely,



View 11 Replies View Related

How To: Save Form Results To Database

May 1, 2006

Hi !

I am creating a website with a form that users can fill up the
information. This form is about the school's information of the users.
After fill up this form, the users will have to click the submit button
that will submit the form to be saved in a database.

I have created the database for that form. My question is how can I
save the result of this form to my database. Let's say I have 5
textboxes in that form, name, school's name, school's address, major,
and comments.

thanks a lot in advanced!

View 3 Replies View Related

Create A Relational Diagram From Non-relational Database

Aug 4, 2005

Hi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)

But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.

'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.

What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty

View 3 Replies View Related

Historic Prediction And Prediction Steps

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

Error In Prediction Query

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

Prediction Query In MS Association Rules

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

How To Write Prediction Query For Two Algorithms?

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

I Can't Understand The Meaning Of A Prediction Query

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

Is There A Way To Get The Node Descrption In The Same Query Where You Get The Prediction?

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

Using CASE Statement In A Prediction Query

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

Getting Two Query Results Into 1 Table

Nov 29, 2007

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)



SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')

i want to select the results from the querys into one table how would i do this ?

View 6 Replies View Related

Help With Relational Query - Many To Many

Jun 21, 2005

Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused. I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item. A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID IN (10, 13, 17)But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull out ONLY items that have a AttributeID of 10 AND 13 AND 17.Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :SI've also tried:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID = 10 AND AttributeID = 13 etcBut obviously that won't work! :s

View 2 Replies View Related

Useing T-SQL Commmand Line Results Save Into A Text File

Mar 7, 2005

Hi,
Any body give me solution,

I've Executed DBCC CHECKDB in the T-SQL in the Query Analyser, then i got results in the Grid, but i wana to store that Information Immidiatly in the Text file, useing T-SQL Script, please help any one how to write T-SQL.

i know that going into menu-Query- Results to File. i don't want to do like that.

I want to save through writing T-SQL Script

Please help any body know....
Thanks in Advance....

View 1 Replies View Related

How To Write Prediction Query For TimeSeries Algorithm

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

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 View Related

Nested Case Prediction Query Question

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







Copyrights 2005-15 www.BigResource.com, All rights reserved