Thanks

It's Clear now.

What do you think about current framework performance & scalability.I know it depends on rulesets logic & design.Suppose rules are as simple as it is mentioned in initial threads,how much time it might take to process 1 million transactions daily for suspicious act detection using100 rulesets along with fact table updation after analysis.

Thanks

__________________
Prasanna Vignesh
MCPD | Web Developer

If the rules are as simple as in the initial threads:

1. You would just have the SSIS pull out the releveant data (e.g. Just the deposits that are greater than a certain sum). 2.You would them move this data to denormalized tables in a star schema in the OLAP database.

3.The cubes would synchronise with the data.

4. The reports built on the cubes would show the suspicious accounts depending on the measures specified.

The time taken would be mostly for Step 1 and Step 3. I don't see that if we start with 1 million transactions, the data pulled from the OLTP, if properly filtered will greatly reduce this number (because you will filter out withdrawals and small deposits).

The actual amount of time it will take to do the analysis will depend on the server specs and table indexing. But I think the solution mentioned is about as optimized as possible. If this is all done overnight,and you have a run of the mill server doing the analysis, I do not see a problem.

__________________
H2O

Without us, no one can survive..

Hello Guys,
I would be thankful if could respond my following queries/doubts:


1. What should be the ideal Datatype for a primary key column ?

Should we go for Auto-increment Int or unique identifier GUID datatype for our OLTP database considering performance,clustering support,Database Mirroring,online indexing service.

2. Is OLTP database is just like Northwind or any other DB that we create from SQL Server Management Studio/Enterprise Manager Databases ?

3. What's the diffrence between Datawarehouse and OLAP DB.Are they same ?.

Is it true when SQL Analysis Services are applied on a datawarehouse it is called OLAP DB ?

4.What's the procedure to create a Datawarehouse & OLAP DB ?

Thanks

__________________
Prasanna Vignesh
MCPD | Web Developer

Question1:

I think GUID would be great if possible (specially if the user will not view this ID).

If you need tp make the ID human readable yet Unique then Int and Autoincrement is the way to go, but for Mirroring purposes you will need to make it a composite key of 2 things:

1. Server ID

2. Autoincrement ID

Question 2:

Yes that is true.

Question 3 and 4:

Here is a great link that explains the difference in schema between normal relational and star schema needed for OLAP. This will explain the very basic principles

Designing the Star Schema Database

Also listen to this web cast by Ron Jacobs:
ARCast - SQL Server and Business Intelligence

I hope this was helpful, please tell me if you need more info

__________________
H2O

Without us, no one can survive..

If you are looking for info on GUID's versus Integers for primary keys check out the following article, it applies to sql server 2000 but provides some good info about the topic.

http://www.informit.com/articles/art...p?p=25862&rl=1

Hi All,

As per our discussion so far you may have noticed that most our tasks are Analysis intensive.

ETL processes would import customer data as well as their transactional data from various sources like Banking solution,Credit Card,Insurance application. So we need to create a Datawarehouse for that.

I am bit confused about datawarehouse & an OLTP database in SQL Server.Correct me if i wrong if we design an OLTP database in Denormalized form for analysis purpose that is called Datawarehouse.Because in SQL 2005 i have not found any interface where Datawarehouse can be created.

I think it is created just like northwind in SQL Server Management Studio's Database Engine and if it's tables are designed in denormalized form to cater Analytics that is called Datawarehouse.

Would appreciate if you could let me know how datawarehuse is created in SQL Server 2005.

Thanks

__________________
Prasanna Vignesh
MCPD | Web Developer

What you wrote is exactly how I have seen it done by our team guys. A datawarehouse is just like any other database, but denormalized and set in a star schema. So creating one in SQL 2005 is the same as any other database in SQL 2005, you just need to denormalize your tables and design them in a star schema.

Thanks for your reply.

One thing i would also like to clear about Analysis Services project.

In SQL 2005 OLAP project's Data Sources we build connection with Datawarehouse DB to do some analysis work.

We create diffrent Cubes there according to analysis need.

Does cube store data physically or it only shows data in a dimensional view after retrieving from Datawarehouse.

If this fetches data datawarehouse so whenever datawarehouse is updated or new data is loaded cube would be also refreshed.

Can we build our application that uses only Datawarehouse not an OLTP because most of the work is analytics/rule based we need asp.net presentation to provide alerts to users that are investigated by rule engine.

Thanks

__________________
Prasanna Vignesh
MCPD | Web Developer

Yes they do for performance reasons, and so you can perform powerful analysis without crippling your database.

Absolutely not, OLTP is optimized for writing data while OLAP is optimized for reading and analysis.

I cannot think of any case where you can skip writing to the OLTP first, this is a major recipe for bad design.

I do agree that fast data writing is possible only through OLTP.But in our application all the required transaction would be imported by ETL package from different sources & application would perform analytics on that, here transactions would not updated or modified by the application they are only analyzed.

We would provide Insert/update functionality in our application for only customer master where it is required. Otherwise it would also be imported from other financial applications.

So to manage application login/access permissions as well as alerts presentation to user we can create an small OLTP but all customer data & transaction would be kept in Datawarehouse.Suggest us the right approach to achieve it efficiently.

__________________
Prasanna Vignesh
MCPD | Web Developer