Best technique for small CRM datamart ?

[Exie]

Hi,

I've been given the job of building a small CRM datamart. So far I've mapped most of the tables from a few different source systems (Oracle, DB2, MSSQL), but now I'm stuck.

There is a requirement to snapshot the data at particular points in time (eg. Monthly), and I'm not sure what the best way to do this is.

In the past I've seen people use effective to/from dates on every record and pop views on top. But not sure if there is a better way ?

My target DB is MSSql, and I was thinking of just using SAS as an ETL tool to get it in there (until I figure MS DTS out).

 

[mrdenny]

SAS probably won't make a good ETL tool, as it's not really an ETL tool. DTS will work or, If you can get SQL 2005 the DTS replacement SSIS will be even better.

I would recommened looking at SQL Server Analysis Services. If your datamart is build correctly in a standard OLAP method SSAS can build the data, and do some of the summarizing for you.

If you can't get SSAS I'd recommend that you setup weekly, monthly, quartly, annualy, etc summary tables to hold that data. While this will require a little bit of extra storage and processing each week, month, quarter, year, etc it will save your users a large amount of time when they want to query for that data. Imagine the speed difference pulling annual rollup data from a table that holds it instead of having to pull it from the detail table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johnherman]

I agree with mrdenny. Although SAS can be used as an ETL tool, and I believe they have actually added a "true" ETL tool to their suite of products, I would use DTS or preferably SSIS. You will find that it is very easy to learn and very powerful as far as making transformations and standardizations to the incoming data.

The logical time to take data from your source systems is immediately following the financial close of the month. This would help insure consistency across the data from a transactional point of view. There could be challenges, for instance, if the source systems close the month on different days.

You should also consider that even though the data is needed in the datamart on a monthly basis, there is no reason that you shouldn't consider loading the datamart on a weekly or daily basis.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright