Best practices to design datawarehouse for a Fraud Detection/Transaction Monitoring Application

Hi All,

This thread relates to following thread of Architecure forum.

http://forums.microsoft.com/msdn/showpost.aspx?postid=1908857&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

Because after reaching at decision that application would require datawarehousing the members suggested that it would be nice if it is posted on datawarehouse forum.

I would appreciate if could go through the mentioned thread that would state application's business requirement.

Now i am continuing the from the thread where we were discussing about SCD.

In our Application customer address is defined in 6 fields (Street,city,State,Country,Pincode,ContactNo) so if we are to maintain 3 type of addresses like Office,Communication,Residence then we have to add 18 fields in our Customer Master dimension table in our dataware house.

Suppose after some time customer office address is changed so what should be done in Customer Master Table at time of synchronization, Should ETL process insert a new record into dimension table with updated address in office address fields or it should update the existing record fields without adding new one.

If for Changing dimensions we are adding a new record then we have to maintain a flag field where status of record would be kept like 'current record' or 'old record' , this would help us to identify the main record.Every time whenever a new customer record is added old record status is changed to 'Old record'.

Should we add new record for changing fields like address,designation,employer,annual income etc or update the existing record only.Because in our analysis these all fields would play major because after each profile updation a risk category is assigned to customer.So we can not afford to overwrite any field information because at any time old information can be asked by management/regulator.

If every time for each field updation a new record is updated then customer master datawarehouse table would become 5 times if each customer record is updated five times during the year.

Suggest us the efficient approach to handle changing dimensions.

Thanks & Regards,

Sameer Gautam

Hi,

You don't have to create a record each time a change happens, just depends if you want to capture the history for that field or not. For example you may want to capture history for change of address, so you would add a new record, but you may not want it for surname, so just update all records for that customer with the new surname.

SCD are a pain to say the least when data warehousing, the principle is nice but coding it is sometimes complicated especially if you try and use snowflakes. And space soon becomes an issue, if you data changes regually. And you are right that if you make a change to a customer 5 times in a year then you will get 5 new records. Although you may code it so it only captures the end of year snapshot, rather than every change.

I would have a look through some of kimballs articles:

http://kimballgroup.com/html/articlesArchitecture/articlesAdvancedDim.html

Good luck

Matt

sql

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
DerekDerek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP
10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexe
d
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
>|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60E
E-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:
>

Best Enterprise Datawarehouse

We currently run a MASSIVE enterprise data warehouse on SQL Server 2000 and it is struggling a bit with both user queries and daily loads.
Need opinions on
1. Is SQL Server the best product for the job
2. What are the alternatives
CheersWhat is massive ? Also, what is your hardware specs ?|||And what are you doing with it? Are you creating cubes off of it?|||Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software|||What type of cubes ? What version of sql are you using ?|||What window do you have available to perform the daily loads? Approx how long does each load take?|||At that size I am sure MS would love to help you (and use you in their marketing :) )|||You can easily deploy SQL Analaysis Services in this case, check ver 2000 for more enhancements and information on MS SQL homepage.

HTH|||No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!|||Originally posted by aldo_2003
Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software

Gotta ask...what's the subject of the data?

3rd party software?

I don't think (damn it, again) that ANY 3rd party software plans for anything that massive...

And EVERYONE is writting adhoc queries...right?

"This sounds like a job for "Super-Silver bullet""

And yes, MS would love to talk to you....|||Originally posted by aldo_2003
No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!

Be happy using Sybase IQ (http://www.sybase.com/products/bi/sybaseiq) !
=> T-SQL compliance
=> DSS database
=> performance++|||You have given very few information regarding your environment and the reasons you are struggling, other blaming SQL. We would be glad to help you if you could tell us more about your data warehouse. So we can see where your bottleneck is. But if you are looking for the answer of 'Yes, you need to switch to O.... and S.. platform', please go to O.... forum. Thanks.