How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Sameer Gautam

Hello Guys,

I know this is basic and different people do that in different ways according to their needs; but I would like to know about the best practices to design a highly scalable real time enterprise databases/application for financial institutions.

Here is a scenario. This application would be import Customer’s Transactional as well as updated profile from Bank’s Core Banking Solution or Data warehouse daily in a batch mode.

Now our application has to determine the suspicious activities by running some rule sets on the transactions. Let me let you some example of rule sets :

1. generate suspicious activity alert if customer does debit/credit transactions amount sum exceeds 1 million dollars within a month.

2. Generate alert if Customer Debit transaction amount > X% of A (where A=maximum debit transaction amount over y1 period)

This application should be able to find suspicious activity for at least 5 millions customers and 1 million transaction would be imported daily to our engine DB.

I would appreciate if could suggest me how this application should be designed on (VS.2005,SQL 2005) platform. Should we have two DB ,one OLTP for keeping customer profile & his transactions and another OLAP DB where analysis services, business intelligence, data mining would store customer past history in fact tables so at the time of rule processing those result can be queried from here. Should we build rule engine in VS.net or in SQL 2005 analysis services or in Biz Talk server or web services or data mining.How analysis services, business intelligence, data mining of SQL/.NET can be used to achieve this application.

Thanks & Regards,

Sameer Gautam

Ollie Riches

wow thats a question and a half

i think you need to do some analysis work around your data and the business problem you are trying to solve before thinking about implementation. Some questions I have:

1. Is there a finite number of rules

2. Are new rules identified often and if so how quickly are they to be implemented

3. Do existing rules ever need modification

4. How accurate do the rules have to be, as in if an account is identified as being suspicous and it is not what will the effect be

5. Are there any performance constraints or is it purely you have to process all new data daily

6. Are the rules to run daily to produce reports are they design to be run ad-hoc

7. Are all the rules to be customer centric or are there every rules to be run across several accounts or across transactions

8. Are you modifying any of the data

9. Is all the data loaded daily, as in the system is 'flushed' and the reloaded before running

10. How much historical data are you required to store

My initial thoughts would be that you require a read-only data respository that allows you to efficiently search and read as required and then run a set of known rules over the data - which makes me think you require some kind of data warehouse with the rules possibly implemented outside of the database in a dedicated application developed in .Net but that depends on the questions above.

HTH

Ollie Riches

Sameer Gautam

Thanks Ollie for your reply. Well we already invested 6-7 months on analysing this business problem.Might You got this feeling because i could not explain entire application into the thread in a summarised manner. My answers are given in bold against each of your questions.

1. Is there a finite number of rules

Ans: No rules would be added to system as per country regulators requirement as well as bank's compliance department.Some rules may be based on simple calculations and some based on Data Mining where customer current transactional behaviour is compared against previous behavoiur.For example customer average withdrawl is 10 K dollars.if he withdraws 90 K dollars that system should generate alert.These would be implemented by us in to system as a update pack not buidable by the client in application.

2. Are new rules identified often and if so how quickly are they to be implemented

Ans:One/Twice in Year new rules are identified and it must b implemented in 3-4 months.

3. Do existing rules ever need modification

Ans:No rule's logic would not be modified.All rules would be parameters based so input parameter would be defined by application admin.

4. How accurate do the rules have to be, as in if an account is identified as being suspicous and it is not what will the effect be

Ans: 100 % otherwise bank would be penalised.System must raise alert as soon as any suspicious activity happens into the system.

5. Are there any performance constraints or is it purely you have to process all new data daily

Ans:Processing would be on daily evening after importing all the data.System must throw its output by next morning.Because 3 days are given to bank for reporting.

6. Are the rules to run daily to produce reports are they design to be run ad-hoc

Ans: Daily.

7. Are all the rules to be customer centric or are there every rules to be run across several accounts or across transactions

Ans:Both type of ruleset would be into the system. For example if a large remittance is transfered in account A and on the same day it moves to account B,C,D..so on.then system should be able to track this interrelated transactions.

8. Are you modifying any of the data

Ans:No.

9. Is all the data loaded daily, as in the system is 'flushed' and the reloaded before running

Ans:Yes.i am wondering If data is flushed then how analysis services would store customer behavious into OLAP fact tables.

10. How much historical data are you required to store

Ans:Maximum up to 1 Yr.

Thanks

S.Gautam

Ollie Riches

So the end client will not be responible for building new rules, your company will do this and provide an update\hot fix to the client application.

If this is the case I would start to thinking about implementing a data warehouse application with a custom .Net application on top that has the ability to check for updates on a per client basis, obivously the 'thing' being updated are 'rules'.

Now whether a rule is a sql scripts, a compiled .Net assembly or part of a BizTalk Orchestration I am unsure about, but all implementations would have advantages and disadvantages - they should be obvious.

Another area of concern is how the rules will be 'hosted' - as in what application runs the rules Sql Server, BizTalk or .Net application. The reason I ask this is because I presume some rules will take a long time to run, greater than 2 minutes lets say, in which case you want to start the process asynchronously and then allow the end user to check the status of the process as and when required.

You mentioned the use of a web front, so this would indicate to me something like an ASP.Net UI that communicates to the host application, this highlights the fact that you need to have some kind of database to persist state (i.e. results and client settings) so the front end is decoupled from the application running the rules.

HTH

Ollie Riches

Sameer Gautam

Currently we have an ASP.NET application with an OLTP Database already installed at client premise.This rule engine would be module of this application,rule engine would be a background process what ever the alerts are generated would be integrated (by web service,database integration) to this web UI for user's review.

Now i am in a Fix how should we design this component . What should be the ideal option (SQL server Jobs/.NET Application/BizTalk etc) to achieve the performance & scalability.Do we need to create an OLTP along with OLAP or we can do this with datawarehouse only.I do not have prior experiance in handling such large applications.

Would appreciate if you can suggest us how it should be designed.

Thanks & Regards,

Sameer Gautam

LivetoCodeCodetoLive!

I suggest you should have 2 databases:

1. An OLTP database for writing data (transactions).

2. An OLAP database for data analysis and rules application.

Database synchronization should be done using SQL 2005 mirroring asynchronously so as not to affect performance.

Since the rules are applied as a batch at night and the rules only change like twice a year and a report is sent the next day, I would create a scheduled job to sun at night.

The job could either run a SQL stored procedure that checks the data and creates a report. OR you could get Biztalk to apply the rules as the transactions are recorded to the database (OLAP).

I hope this helps you, please provide me with your feedback and comments.

Martin Platt

It would seem immediately that the choice of a data warehouse of some sort would be a good idea, due to the highly transactional nature of the problem.

How to implement these rules Well, it depends on frequency of change, maintenance requirements, etc. You could set up cubes in OLAP for the rules, but this isn't probably the most maintainable way to do things, tightly couples the rules to the technology, but it would be quick. There are trade-offs. You need to be more specific in the order of importance, or the performance requirements, etc.

Difficult question to answer exactly without more information, but I hope this starts the thought process.

Cheers,

Martin.

Sameer Gautam

Thanks dear for your suggestions.I do think this architecture should work.

As per suggestions if design this in following way (Correct me if something is better than that)

1. Suppose we create an OLTP database & an OLAP database.

2. Daily SQL integration services project (DTS) would load data into OLTP database.

3. At the time of rule engine execution if particular rule requires some historical figures (like max. debit in last six months) than it would be fetched from OLAP Facts tables, where all required figures are kept.

4. After rule engine execution this all transactional data would be transfered from OLTP to OLAP DB for analysis (Data Mining) after analysis services would update fact tables if requires.

5. OLTP database would be flushed.

6. If we are to implement rule engine which one is better option SQL Jobs(stored proc),BizTalk Server or.NET application in terms of performance & scalability.

Once again thanks to all of you for sharing your experiences hope we would be able to achieve an architecture that is highly scalable & high performance.

Thanks & Regards,

S.Gautam

LivetoCodeCodetoLive!

Sameer,

After consulting with some friends in the BI dept. we came to some conclusions:

1. Your data should be moved with SSIS (not DTS) .

2. Your rules are applied by defining the appropriate measures (Date/Time, amount/transaction, account number).

3. The reporting uses these measures to implement the rules you need (and not a stored proc or Biztalk).

So if the rule is to not have a transaction of > 1 million in 1 day it will work, if over a period of 1 week, it will work also. This will be done by drilling down in the report.

4.When you need to analyse patterns for client transactions, that is where datamining comes in and this is done using the cube with the measures you have already created.

I hope this answers your question. If you still have concerns, please communicate them and I will be glad to help.

TA123

Reg point number 6, Biztalk server should give the best in terms of performance and scalability as it is running on Rete algorithm and very much scalable.Also using biztalk, you can put rules in the best disconnected fashion.

Reg point number 2, i have a query.Are you planning to load OLTP from OLAP or load OLAP from OLTP.As per my understanding, we put transactions in OLTP and load in OLAP for processing.Correct me if i am wrong.

Cheers

Sameer Gautam

Many Thanks you spared some time with BI team to find out the right solution.

As per you conclusions following are my comments against each of them

1.I do agree with SSIS.It must be used for ETL purposes.

2.Transaction record contains TransactionId,CustomerId,DateTime,Amount,Type(Debit/Credit) fields.

3.You mean to say we should go for SQL reporting services to find out the suspicious transactions.

4.Some of rule set require data mining result which is stored in fact tables ,can reporting services fetch this data for rule execution .

Now almost you are fully aware what we want to achive ,I would be thank ful if could suggest us a final design on the basis of our & your team discussion.

Thanks & Regards,

S.Gautam

Sameer Gautam

Thanks dear for your participation in this interesting thread.

Reg point no.6

I do not have too much knowledge about Biz Talk Server.Can these type of rule sets simple/Mining based can be implemented with Biz talk.

Reg point no.2

You are right we are looking to transfer all transactional data from OLTP database to OLAP for analysis.

Regards,

S.Gautam

LivetoCodeCodetoLive!

Sameer Gautam wrote:

Many Thanks you spared some time with BI team to find out the right solution.

As per you conclusions following are my comments against each of them

1.I do agree with SSIS.It must be used for ETL purposes.

2.Transaction record contains TransactionId,CustomerId,DateTime,Amount,Type(Debit/Credit) fields.

3.You mean to say we should go for SQL reporting services to find out the suspicious transactions.

4.Some of rule set require data mining result which is stored in fact tables ,can reporting services fetch this data for rule execution .

Now almost you are fully aware what we want to achive ,I would be thank ful if could suggest us a final design on the basis of our & your team discussion.

Thanks & Regards,

S.Gautam

Sameer,

here are my answers:

1. Agreed.

2.Agreed

3.You could go with SQL Reporting Services or Excel or any client for MS SQL, you find the suspicious transactions by drilling in the report and defining your aggregates.

4. Data mining is done on top of the cubes, there is a data mining service that comes with MS SQL enterprise and it will be used to for the rule execution. For more details on this here is an excellent resource:

http://www.sqlserverdatamining.com/DMCommunity/

Please tell me if you need any more answers, otherwise please mark as answered if we have answered your question.

Ollie Riches

sorry but BizTalk is never going to give the best performance (out of the box), especially since it is based around a single repository for messages - the 'message box', which is a sql server db. Yes it can scale when processing orchestration but it suffers from the usual performance constraints for a guaranteed messaging solution.

HTH

Ollie Riches

Sameer Gautam

Hi Ollie,

So what do you think how it should be designed to achieve performance & scalability.Can you suggest a design framework as per your understanding.

Thanks,

S.Gautam

How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization

Re: Architecture General How to design (.NET Application & SQL Database) Fraud/Suspicious Activity Detection Application for Financial Organization