Showing posts with label records. Show all posts
Showing posts with label records. Show all posts

Friday, March 30, 2012

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.

Predict % completed

I'm doing a web search page on a db that has about 1 million records, and
the query is quite complicated. I would like to provide the user some
feedback will the db server is performing the search.
What is the best way to do this? any examples or tutorials?
The only way I can think of is doing 10000 records at a time and add 1% to a
counter.
There must be a better way.
Any input is greatly appreciated,
AaronWhat about firing first the Query to the Optimizer and sshowing the
execution plan for it, you might see there how long (CPU cycles) it will
last and how many rows are estimated (Requirement of maintained statistics
:-)
See for this one:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select CompanyName from Customers where CompanyName LIKE '%A%'
GO
SET SHOWPLAN_ALL OFF
GO
(Result gonna spread over the page, best thing you execute in on your own.)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
> I'm doing a web search page on a db that has about 1 million records, and
> the query is quite complicated. I would like to provide the user some
> feedback will the db server is performing the search.
> What is the best way to do this? any examples or tutorials?
> The only way I can think of is doing 10000 records at a time and add 1% to
> a counter.
> There must be a better way.
> Any input is greatly appreciated,
> Aaron
>
>|||what's the Optimizer ? is that an addon to sql server?
i don't think i have that
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eu5VT4YQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> What about firing first the Query to the Optimizer and sshowing the
> execution plan for it, you might see there how long (CPU cycles) it will
> last and how many rows are estimated (Requirement of maintained statistics
> :-)
> See for this one:
>
> USE NORTHWIND
> GO
> SET SHOWPLAN_ALL ON
> GO
> Select CompanyName from Customers where CompanyName LIKE '%A%'
> GO
> SET SHOWPLAN_ALL OFF
> GO
> (Result gonna spread over the page, best thing you execute in on your
> own.)
> HTH, Jens Smeyer
>
> --
> http://www.sqlserver2005.de
> --
> "Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
> news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||On Fri, 15 Apr 2005 16:06:44 -0700, Aaron wrote:

>what's the Optimizer ? is that an addon to sql server?
>i don't think i have that
(snip)
Hi Aaron,
The optimizer is an integral part of SQL Server. It's the part of the
server that reviews your query and determines the execution plan (the
order and methods in which data will be fetched and combined to satisfy
your request).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 12, 2012

Possible To Tell Who Deleted Records In My SQL Table?

Hello All.

Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.

I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.

Thank you.

Best regardsQuietly run a trace to capture anything with textdata like %delete%. If you don't get anything there, "they" may be using a stored procedure. You would have to trace all activity at that point.|||Why not place a trigger on the table?|||Ran into the same issue a while back. We found that the process of deleting was actually in an ADP file deleting based upon a connection to a EXCEL file.

The process was requested to delete records based upon dates.

The process did what was asked the problem was that the numeric date in EXCEL and ACCESS and SQL server are all different. Where relieved to find that it wasn't malicious.

This will not help you find a userid deleting records but may help you determine a cause.|||Hello All.

Thank you for your advises. If this person is using query analyzer to delete the records from a remote connection, will the trace be able to detect it? I have discovered 35 records missing from my table this morning.

Best regards|||Always has for me.

Friday, March 9, 2012

Possible to Insert Records into ODBC Data Source from Stored Procedure?

I would like to insert records into a non MS-SQL database that I can connect
to with an ODBC data source.
This is possible? If so, can you give me an example of how to connect?
Thanks,
MikeYes, it is possible, and there are numerous ways of connecting to an ODBC
data source. If you are using C#, take a look at class OdbcConnection in the
System.Data.Odbc namespace --
http://msdn2.microsoft.com/en-us/library/at2sk77y.aspx.
Linchi
"Mike" wrote:
> I would like to insert records into a non MS-SQL database that I can connect
> to with an ODBC data source.
> This is possible? If so, can you give me an example of how to connect?
> Thanks,
> Mike
>
>

Wednesday, March 7, 2012

Possible tempdb problems

I have a simple TOP X select statement that is returning
one less than the TOP X number. If I check the records
affected verses records returned it is always 1 less. I
ran DBCC CHECKDB on all databases and I got some errors
when I ran it on tempdb. I can't set single user access to
tempdb so I can't repair it. Any suggestions? Here are
the errors:
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB
processing.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:106) in database ID 2 is allocated in the SGAM
(1:3) and PFS (1:1), but was not allocated in any IAM. PFS
flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3096) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3176) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 3 allocation errors and 0 consistency errors
not associated with any single object.
CHECKDB found 3 allocation errors and 0 consistency errors
in database 'tempdb'.
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKDB (tempdb ).Restart SQL Server, and tempdb will re-create itself from scratch.
"Brian Tax" <brian.tax@.milliman.com> wrote in message
news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
> I have a simple TOP X select statement that is returning
> one less than the TOP X number. If I check the records
> affected verses records returned it is always 1 less. I
> ran DBCC CHECKDB on all databases and I got some errors
> when I ran it on tempdb. I can't set single user access to
> tempdb so I can't repair it. Any suggestions? Here are
> the errors:
> Server: Msg 8999, Level 16, State 1, Line 1
> Database tempdb allocation errors prevent further CHECKDB
> processing.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:106) in database ID 2 is allocated in the SGAM
> (1:3) and PFS (1:1), but was not allocated in any IAM. PFS
> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3096) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3176) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> CHECKDB found 3 allocation errors and 0 consistency errors
> not associated with any single object.
> CHECKDB found 3 allocation errors and 0 consistency errors
> in database 'tempdb'.
> repair_allow_data_loss is the minimum repair level for the
> errors found by DBCC CHECKDB (tempdb ).|||We rebooted twice yesterday and it was still doing the
same thing. Now this morning the SELECT TOP X was working,
but it still gave errors in tempdb.
>--Original Message--
>Restart SQL Server, and tempdb will re-create itself from
scratch.
>
>"Brian Tax" <brian.tax@.milliman.com> wrote in message
>news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
>> I have a simple TOP X select statement that is returning
>> one less than the TOP X number. If I check the records
>> affected verses records returned it is always 1 less. I
>> ran DBCC CHECKDB on all databases and I got some errors
>> when I ran it on tempdb. I can't set single user access
to
>> tempdb so I can't repair it. Any suggestions? Here are
>> the errors:
>> Server: Msg 8999, Level 16, State 1, Line 1
>> Database tempdb allocation errors prevent further
CHECKDB
>> processing.
>> Server: Msg 8906, Level 16, State 1, Line 1
>> Page (1:106) in database ID 2 is allocated in the SGAM
>> (1:3) and PFS (1:1), but was not allocated in any IAM.
PFS
>> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3096) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3176) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> not associated with any single object.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> in database 'tempdb'.
>> repair_allow_data_loss is the minimum repair level for
the
>> errors found by DBCC CHECKDB (tempdb ).
>
>.
>