Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Best technique for small CRM datamart ?

Best technique for small CRM datamart ?

Best technique for small CRM datamart ?


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).

RE: Best technique for small CRM datamart ?

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.

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

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

RE: Best technique for small CRM datamart ?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close