Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable
way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?mike,
From a security point of view, and probably also performance, two
connections from the web server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferab
le way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?

Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?
mike,
From a security point of view, and probably also performance, two
connections from the web server.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?
sql

Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?mike,
From a security point of view, and probably also performance, two
connections from the web server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?

Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

Pre-execute Hangs at 50%

Hello,

I did a search and found no answers. I have a simple project; one data flow task reading from one DB2 tabel and writing to another. My OLEDB source is a sql command that returns 97,000 rows and my OLEDB destination's data access mode is "table or view".

For my OLEDB source SQL command, I run it through the "build query" panel and I get all 97,000 rows back within 90 seconds everytime.

I don't know what is so different since I currently do SQL commands that bring back 2.5 million rows from DB2 with no effort in SSIS. I even took my data flow task and placed into a known good solution - but still hangs at 50% forever. I tried attaching to SSIS server and executing through there, again no luck. Still hung!

Any ideas? Thanks.

On your OLE DB Source component in the data flow, set its property, "ValidateExternalMetadata" to false and see if that helps.

One other thing to try is to set the property of the DB2 connection manager object, "DelayValidation" to true.|||

Thanks for your reply Phil.

I went ahead to set both properties accordingly and reran. After 16 minutes of running it was still on the pre-execute phase stuck at 50%.

Just to test I changed my DB2 SQL to FETCH ROW FIRST ONLY and it still hangs at the pre-execute 50% level. So obviously it's not the number of rows causing the problem.

What I don't understand is using the "preview" panel in the OLEDB source, I get the first row in about 6-7 seconds.

Thanks.

|||Do you have any other connection managers defined in the package? Validation will attempt to validate all connections.

What DB2 driver are you using?|||

No, that is the only connection I have in my package/solution.

I am using "Native OLE DB\IBM OLE DB Provider for DB2".

|||

Is there any way to see a log of where it's getting hung? Maybe its my OLEDB Desitnation...since everything is perfect with my source.

Any ideas on how to verify my idea? Thanks.

|||

I went ahead and changed my destination to a temperary SQL 2005 table and now it works fine...so It is my destination. It must be failing on something (connection/rights?) and not telling me why?|||

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

|||

Zach84 wrote:

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

Be careful... You aren't using a Microsoft driver. It isn't doing any selects or inserts -- it's just trying to validate metadata and such.

You could always try the Microsoft OLE DB for DB2 driver, which I've had better luck with.

pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql

Pre-execute error in simple import/export

I am trying to copy data from a SQL Server 2000 DB to SQL Server 2005 DB using the import/export wizard in SQL Server Management Studio. The two databases are not identical with different table names and different columns but I thought I had set up all the right mappings and had set the 'Enable identity insert' option. When I ran the wizard it errored at the Pre-execute phase. I simplified the wizard down to one table and only as couple of varchar columns and this also errored in the same way. The error report is detailed below.

For reference the SQL2000(ent. edn) DB is on a windows 2000 server and the SQL2005(dev. edn) DB and the management studio are both on my WinXPSP2 workstation.

Could somebody explain why these errors have occured and more importantly how to rectify the problem?

Many thanks,
Michael.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)

Messages

Warning 0x80047076: Data Flow Task: The output column "DateAdd" (23) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "DateChg" (26) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

... NOTE: I have removed the rest of the warnings as they were the same as above (many of them).

- Pre-execute (Error)

Messages

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - Nrs_NewsArticles" (112) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

- Executing (Success)

- Copying to [NereusV2_1].[dbo].[Nrs_NewsArticles] (Stopped)

- Post-execute (Stopped)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - Nrs_NewsArticles" (112)" wrote 0 rows.
(SQL Server Import and Export Wizard)

Michael,

this is most likely a truncation problem. Could you check the sizes of used source and destination columns?

Thanks.

pre-emptive locking solution

Hey all,

I've got a very long stored proc that runs intensive updates on a particular table. The locks are always escalated from Intent eXclusive to eXclusive. After some reading online, I've decided to implement this (http://support.microsoft.com/default.aspx?scid=kb;en-us;323630#kb2) . The idea is to start a transaction with another spid, and hold an incompatible lock on that table so the stored procedure that I'm running isn't able to escalate the lock. The solution works, but it unfortunately means that I've to lock this table with an update lock for the whole stored proc, which I would rather not do.

Is it possible to spawn another stored proc/function/transaction under another spid from within my stored proc ? I'm hoping the answer to my question isn't here (http://www.dbforums.com/t994076.html).

Is it maybe possible to open another connection within my stored proc ? On a similar note, would it be possible to communicate somehow between connections without using a table ?

Thanks,
-KilkaThere are many ways to do this, but Transact-SQL is a bit limited in this area. It can be done, but it is brute force and ugly at best.

Have you investigated DTS? At least in my experience, it handles this kind of processing much better than Transact-SQL can.

-PatP|||I've worked with DTS and I know the only way it could potentially help me was if I used scripting (activeX or something) to acheive the same thing.

I'm trying to keep everything in a scheduled stored proc. If at all possible, I want to do everything in T-sql for performance reasons. This is something that takes hours to run, so any little performance hit has a big impact.

PredVariance for NN

Hi,

We're building a model using the NN algorithm, and had a question about the how the PredVariance value is computed. Our testing data set has ~28K cases, but for some reason when we run our prediction query there are only 10 unique PredVariance values generated. Why doesn't each case with a distinct predicted value have its own PredVariance value?

For e.g. here are 3 different PredValues that all have the same PredVariance (229985900) and each has a TrueVal of 0:

15307.6681537296
17759.1791905724
1843.85682442577

If you need more specific info please let me know.

Thanks.

Hello

PredictVariance outputs the error variance for the subnetwork used in executing the prediction. This is detected during training for each subnetwork and, for all predictions executed on that respective subnetwork, PredictVariance will return the same value (same applies to PredictStdev).

If your target variable is a continuous one, then there are at most 2 subnetworks built for the variable (one for the value, one for the probability of Missing state, assuming your training data contains missing values).

If your target variable is discrete or discretized, then one subnetwork is trained to predict the probability of each individual state (including Missing).

Assuming that a prediction returns TargetValue1, the associated PredictVariance should return always the same value (the error variance of the TargetValue1 subnetwork)

You mention that there are 10 unique PredVariance values generated -- it seems that your target variable is discrete with at least 10 distinct states, is this correct?

thanks

|||

Thanks Bogdan,

Our target variable is actually continuous. That's what initially prompted the question, as we expected a PredVariance to be computed for all distinct target variable values (of which there are as many as there are cases).

Does that clarify the question?

Thanks.

|||

The error variance is not computed for each distinct target variable value, but for each subnetwork.

Here are the steps for one continuous target:

- partition the training set in two blocks (training and holdout -- the HOLDOUT_PERCENTAGE and SAMPLE_SIZE parameters control the size of the partitions)

- Iteratively train one subnetwork based on the training partition, and estimate the error (at each step) based on the holdout partition

- at the end -- compute the error variance of the trained subnetwork over all the cases the holdout partition

Therefore, there is a single variance value for the whole subnetwork that predicts one continuous target.

At prediction time, Predict(Target) runs the subnetwork for the target and returns the result, PredictVariance simply returns the (pre-computed) variance for the respective subnetwork (mapped from the normalized training space to the original input space). Therefore, the value returned by PredictVariance should always be the same.

You mentioned that there are 10 distinct variance values being returned?

|||i'll read your feedback more thoroughly this evening, but I wanted to answer your question...yes, there are 10 distinct PredVariance values.|||

Hi,

It's now clear, based on your response, why we have 10 distinct PredVariance values. We were using 10-fold cross-validation and each fold has a corresponding PredVariance.

Going to follow-up with one additional post/question before we close it out....stay tuned.

Thanks,

mike

PredictSupport

Hi,

Can anyone explain what this function returns? What does the support value represent?

Thanks,

Dave

PredictSupport(<attribute>, <state>) returns the number of cases in the training set that support the predicted state for this attribute. If the state is not specified, the state with the highest predict probability is used. The general idea is that a high probability prediction with a larger support value may be more reliable.|||

Ah right, that makes sense.

Thanks!

sql

PredictProbability with Association Rule model..

I have run into a .. somewhat of a "duh" question. I'm running association rule to run a basket analysis, and I'm trying to get probability of each prediction. I know this is wrong, but how do I go about running PredictProbability on each ProductPurchase prediction?

When I run the below DMX query, I get this error message...

Error (Data mining): the dot expression is not allowed in the context at line 5, column 25. Use sub-SELECT instead.

Thanks in advance...

-Young K

SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], 3)) as [ProductPurchases]
, (PredictProbability([Basket Analysis AR].[Training Product].[ProductName])) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]

You can actually get the statistics directly from the Predict function call:

Note the extra flag for the Predict function (INCLUDE_STATISTICS) and the removal of the PredictProbability call. PredictProbability will not work for nested table columns

SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], INCLUDE_STATISTICS, 3)) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]

The results results will include the probability/support and adjusted probability for each of the predicted items

|||Thanks so much. :-D

Predictions

Hello all,
I was handed a new project and I'm not quite sure how to
do it or even what technology to use. Now, I think that
SQL Server's Analysis Services could help (or even do the
entire thing) but I'm not sure. So, basically, I'm
looking for recommendations and ressources on the subject.
Here's my scenario:
We have a Data-Warehouse that stores information about
the production of our company. Around this fact table, we
have multiple dimensions including one about the origin
of the production (who) and a time dimension (when).
We are building cubes and linking Excel to them for
future analysis. Everything is done. Now, the all-mighty
supervisors would like to have some predictions built-in
to the system for the rest of the year based on the
previous years. Basically, we would be adding production
values for every possible time (the step is every hour,
so the sum of the production comming from a certain
origin is set as the fact and linked to a time dimension
row for that hour).
As you can see, it's not very difficult and it seems to
me like Analysis Services might already have that
functionnality although I couldn't find it (seems like
predictions can only be made by looking at relations
found by the data mining algorithms and these predictions
need to be made by a human because they are not really
registered as "new" facts or rows but more like new
columns).
So, in short, I'd like to have a tool calculate future
productions for the rest of the year and set a new column
(or dimension) to a certain value indicating that this
fact row has been predicted and is not certain. Of
course, the refreshing of data would not create duplicate
rows for predictions, newer predictions (probably based
on more facts) would replace the last equivalent
prediction.
I hope that's clear.
What do you recommend I use? Is it a case where I will
need to build my own separate program (that updates the
data-warehouse) or is it possible to achieve this using
data mining?
Thanks a lot,
Skip.You could use the datamining prediction task in DTS. I have used this before
and it seems to work well. The accuracy of your model (and suitability of y
our data) can be tested directly by applying the prediction task to the sour
ce data. This task as far a
s I used it is suitable for Y/N type questions - eg given a male living in L
ondon in a certain wage-bracket, would they be likely to join our gym. I can
't see how this type of prediction tool can be used for numerical data apart
from bracketed ranges. If
this IS suitable for you, then it's a simple matter to take the predicted da
ta and load it into the fact table and then do an incremental update of the
cube - eg in DTS.
Regards,
Paul Ibison

Prediction with many attribute states

I have a large dataset of around 3 million records with accounting data for 2 years. Attributes are transaction amount (cont. / predict), account, cost centre, project, month and a few others. I want to predict any future transaction amount for a certain combination. For example; what will the next salary cost transaction amount in cost centre 123 probably be?

I have tried Decision trees and Neural nets. But the predictions are not good enough even if there should be clear patterns in normal accounting data.

I guess the problem is that many of the input attributes have many states. There are around 500 account, and 1000 cost centres, and 2000 projects etc. And the Decision tree doesn’t seem to be able to capture all the business rules in the company. I have tried to group the attribute states into groups based on their average amount, their parent account etc, but it doesn’t seem to solve the problem.

Please post any suggestion you might have how to improve the prediction. I will try them all and post back my findings!

/Erik

You may need to structure your model so that it creates independent models for all scenarios. Also if things like "Project" only have a few rows/state, there's likely not alot to learn from them.

To create independent models you need to move one of your attributes to a nested table. For example, if you thought that "accounts" were the most important you would create a model like this

CREATE MINING MODEL CostByAccount
{
Transaction LONG KEY,
AccountAmount TABLE
{
Account TEXT KEY,
Amount FLOAT CONTINUOUS PREDICT_ONLY
}
CostCenter LONG DISCRETE,
Project LONG DISCRETE,
Month TEXT DISCRETE,
...
} USING Microsoft_Decision_Trees(params)

This will create a different tree for each account based on input only for that account. To create this table in the UI, you will mark the source table as case and nested tables and then add Account as Key of the nested table.

|||

Thanx Jamie,

Seems like a good idea. Creating a forrest instead of a tree. The result looks as expected when browsing the created tree structres in the model viewer.

1. But is this kind of model supported by the accurancy chart? Can't seem to get it working. I add the case table, and the nested table (same table twice). But the drop-down "Predictabel column name" is empty.

2. How to write the predict query? Have used the query builder but it dosn't seem to work.

/Erik

|||

Actually, no, it doesn't work with the accuracy chart, so you would have to create your own accuracy test queries.

For predict, you should be able to do Predict(<Nested Table Name>,3) for example to get the 3 most likely categories. There are also additional tricks you can play, for example to get statistics you can do

Predict(<Nested Table Name>,INCLUDE_STATISTICS)

This will return all possible states with descriptive stats for each state. Since these functions return tables you can select from them, e.g.

SELECT (SELECT * FROM Predict(<Nested Table>, INCLUDE_STATISTICS) WHERE $Probability >0.25) as Result FROM MyModel ...

Will return all states with a 25% probability or higher.

|||

Can't follow you,

This is approx. what I would like to do. But it dosnt work. (A simplified version of the real model).

/Erik

SELECT
t.[TransactionID],
t.[Account],
t.[CostCentre],
t.[Project],
(t.[Amount]) as [ActualAmount],
(SELECT ([Amount]) as [EstimatedAmount] FROM [DesTree].[Transactions])
From
[DesTree]
PREDICTION JOIN
SHAPE {
OPENQUERY([Adb2],
'SELECT DISTINCT
[TransactionID],
[Account],
[CostCentre],
[Project],
[Amount]
FROM
[dbo].[Transactions]
ORDER BY
[TransactionID]')}
APPEND
({OPENQUERY([Adb2],
'SELECT
[Account],
[Amount],
[TransactionID]
FROM
[dbo].[Transactions]
ORDER BY
[TransactionID]')}
RELATE
[TransactionID] TO [TransactionID])
AS
[Transactions] AS t
ON
[DesTree].[Cost Centre] = t.[CostCentre] AND
[DesTree].[Project] = t.[Project] AND
[DesTree].[Transactions].[Account] = t.[Transactions].[Account] AND
[DesTree].[Transactions].[Amount] = t.[Transactions].[Amount]

|||

I think you want to do your nested select like this

SELECT FLATTENED

t.[TransactionID],
t.[Account],
t.[CostCentre],
t.[Project],
(t.[Amount]) as [ActualAmount],

(SELECT Account, Amount FROM Predict(Transactions) WHERE Account='MyAccount') as Prediction

FROM ...

The only problem here is that you can't compare the nested account to your input - only to a static string or parameter. E.g you can do WHERE Account=@.Account, but you can't do WHERE Account=t.Account.

Prediction Query in MS Association Rules

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'

All the rules are used when you use AR for prediction. The first place to look is the prediction query builder. There is a button on the top to switch the mode from batch to singleton. With a singleton prediction you can manually specify the inputs for your query.

The prediction function you need to specify is something like "Predict(<my nested table name>, 5)". To build such a prediction in the query builder, select Prediction Function, then Predict, then type the name of your nested table, comma, then the number of recommendations you want into the parameters box.

To see the query select the SQL mode from the toolbar.

Let me know if this helps or if you were looking for some other type of answer

THanks

-Jamie

|||

Hi!

Thanks for interesting in my question!

My domain has two tables: Customer (Customer_ID, Name, ....) and Purchase (Customer_ID, Product_Name, Quantity,...)

Creating Mining Model:

Create Mining Model ProductPredict{

Customer_ID long key,

Purchase Table Predict {Product_Name text key}

}

So, when i buid a query such as:

Select Predict(Purchase, 3)

From ProductPredict Prediction Join

(Select 'A' As Product_Name

) as customer

On [ProductPredict].[Purchase].Product_Name = customer.Product_Name;

Result as all item in the right side of the rules contain 'A' in the left side.

But I want to buid a query that result as all item in the right side of the rules contain 'A' and 'B' in the left side.?

Summary: I want to buid a query that result as all item in the right side of the rules contain some items in the left side?

|||

You need a query such as

Select Predict(Purchase, 3)

From ProductPredict Prediction Join

(select

(Select 'A' As Product_Name UNION Select 'B' AS Product_Name)

as Products

) as customer

On [ProductPredict].[Purchase].Product_Name = customer.Product_Name;

This will cause rules with A and B to fire. You may still get predictions based on A alone and B alone, though, depending on their probability and lift.

|||

Hi!

Thank you very much! That's interesting, but when i run that query, it has error, so the correct query is:

Select PredictAssociation(Purchase, 3)

From ProductPredict Prediction Join

(

select

( Select 'A' As Product_Name

UNION

Select 'B' AS Product_Name

)as Products

) as customer

On [ProductPredict].[Purchase].Product_Name = [customer].[Products].Product_Name

|||Predict is a polymorphic function - DMX maps it to the appropriate function based on the model that's being queried. In this case, it maps to PredictAssociation so you should get the same results either way. What errors did you see with the earlier query?

Prediction Query for a "weighted" clustering model

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?

to be most theoretically accurate, yes, however, I would check to see how the results change for your particular model as you change inputs. If you don't have any missing data, it may not make a significant difference.sql

prediction on multi columns

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

You can use PredictAssociation() in a DMX statement to get the rules. In your example, the satement will look like:

Select

PredictAssociation(Ai, INCLUDE_STATISTICS, n)

FROM

[Model]

NATURAL PREDICTION JOIN

(SELECT Value as Ai) AS T

Where Ai is replaced with the specific A column you're providing as input and the Value is the Value of the Ai column.

Hope this helps

Prediction Join to MDX with nested table

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]]]

Typically, for building models on top of cubes, it is much easier to use the tools (BI Dev Studio). This way you can define your model directly on top of the cube and lots of optimizations occur. With such models, you can even use the MDXPredict function to get prediction results inside MDX queries over the source cube.

The DMX SELECT statement supports as input rowset-returning Analysis Services statements (MDX or DMX). That means that dataset-returning statements are not supported. But many MDX queries can be flattened. Have you tried something like SELECT FLATTENED in the MDX query?

|||

Bogdan-

Thanks for the reply. Yes, BIDS worked great for building the model. I've got it trained. Now I want to do a prediction based upon data from a cube. From what I can tell, you can't do prediction queries off a cube using BIDS because it only lets you predict off a relational table source. Right?

I've been researching the MDX function "Predict" which you mentioned. But I'm having terrible trouble finding example queries using that function...

Here's what I'm looking for... we've built a clustering model to cluster our stores. Some of the attributes are just Store dimension attributes... some are from a nested table (stats about the sales volume from each product category). We trained the model with all the stores. Now we want to extract the cluster name for each store and save that to a table. So is there a straight MDX query using the Predict MDX function which will get me the cluster name for every store? I was having trouble seeing how the Predict MDX function was able to know how to do a prediction join to the Store dimension.

As a side note, we could almost do a natural prediction join back to (select * from Model.CASES) except that we don't want the Store Key to influence the clustering model so we didn't add that as an input to the model. (And marking Store Key as Ignore excludes it from the Model.CASES resultset.)

By the way, we're only talking about a couple hundred rows, so the performance of the SHAPE/APPEND syntax below is fine for my purposes... just seeing if there's a more elegant way to do it.

Thanks!

|||

Oh... and to answer your other question about trying "SELECT FLATTENED"...

It's my understanding that "SELECT FLATTENED" is DMX. I'm not sure how to write an MDX statement that starts with "SELECT FLATTENED". And I'm struggling to see how using the DMX "SELECT FLATTENED" would help me. The output of DMX prediction query I used in the examples at the beginning of the thread work fine. I suppose I could flatten the output, but that wouldn't help me much. It's the input to the prediction join that I'm concerned with.

Or did you mean that you can use an MDX query which is written to be flat and use that as input to a prediction query which expects nested tables? I just tried that but may not have been using the right syntax cause I couldn't get it to work. Suggestions?

|||

You kind of need to do it brute force -we use the flattening semantics of MDX when executing the query, so you have to reshape using SHAPE.

There is a little trick to help you out in building the queries. You can use DMX to examine the flattened structure of the MDX query. Just issue a query like this:

SELECT t.* FROM AnyModel NATURAL PREDICTION JOIN <My MDX Query> AS t

then you will be able to see how the DMX processor sees your MDX results.

|||

Jamie-

That trick is helpful for seeing how it refers to the results of an MDX query.

But how do I take a flat MDX query and shape it so it can be consumed by a prediction join which expects a nested table. See the MDX example at the top of this post. Is that the only way (tying two separate MDX queries together with SHAPE/APPEND)?

|||

Yes your original SHAPE/APPEND would be the way to go.

The implementation of SHAPE in the AS engine will cause the MDX query results to be automatically returned in a flattened manner without requiring any explicit flattening syntax in the query itself (in fact, there is no such syntax - flattening is requested as either a command property in XMLA or by requesting a rowset interface in OLE DB)..

prediction in ms sql server 2005

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.

SmileykeYou could probably do this with an INSERT trigger on your SQL Server database table that makes a singleton prediction query via a linked server to the AS server that holds your mining model.|||And how would this query look like?
I mean, you put me in the right direction I think, but I can't make it work.

Thnx|||Please see this article I just posted for details on how to do this: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx|||Ok, I tried this, and it was very helpful, but it still doesn't work here.

Do you have any idea why the first query here works, but the second one doesn't? The error is given at the end:

1st working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings from [Abalone Training Half]')

2nd not working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

The error is:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMServer" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T" against OLE DB provider "MSOLAP" for linked server "DMServer".

As you notice, the predicted class is here Rings, and the input attributes are sex, length diameter and height.

I really hope you can still help me.

Smileyke|||

I may be wrong, but the Sex column seems TEXT. In this case, shouldn't "SELECT I AS Sex" be actually "SELECT 'I' AS Sex" ?

In this case, your OPENQUERY should look like below (2 single quotes around I )

SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT ''I'' AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

|||Thank you. That was indeed the problem.

Now the complete trigger works, so thank you all.

smileyke

Prediction Accuracy

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.

To get the standard deviation as a percentage, you just need to get the predicted value and divide e.g.

PredictStdev([Perf])/Predict([Perf]) // of course Predict(Perf) could be 0.

However, I'm not sure what you meant by "From the Previous", though