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

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

sql

Prediction Accuracy

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

StudIdDatePerf

00101/01/200190

00102/01/200189

00103/01/200187

00201/01/200159

00202/01/200170

00303/01/200147

If I write my Prediction Query to predict for 100 th time step.Its giving me out put like

DatePerf

03/01/201547.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?

Thanks,

Karthik.

The time series algorithm in SQL Server 2005 - ARTxp is designed for near term prediction accuracy, not far term - e.g. 100 steps. You can get details on the research behind the algorithm at http://research.microsoft.com/~dmax/publications/dmart-final.pdf