When you look at salesforce.com, there are a couple of things that are difficult to report on:

• data that doesn't exist in salesforce.com
• data that isn't in salesforce.com any more

you might say "Hey! these are the same thing! this is some marketing gimmick where you sell the same thing twice!"

I would say to you "If marketing say that, it's probably because some people won't understand one of the ways of saying it, so they'll say it twice so the two sets of people understand." But, here, I do mean two different things.

Why those are two different things

They are two different things because the first is about data that is never in salesforce.com. This might be orders in an SAP financials system, contracts held in an Oracle fulfillment management system, or phone records in a billing system.

The second is about data that has changed. You did have a copy of the lead as open - but when you converted it, it kind of went away and became 3 things. You did have the case as an open case, but now it's shut.

A short-cut - getting data into salesforce.com

If you are dealing with the first case - data that is not in salesforce.com, you could just move the data into salesforce.com to report on. For instance:

1. If the data to be imported is less than the data to be exported (e.g. less orders than opportunities) then it might make sense to push the data into salesforce.com.
2. If the other records are needed by salesforce.com users (e.g. contracts should be visible by reps because when they sell they need to know what's already sold), also move the data to salesforce.com
3. If it's integral to a process in salesforce.com - for instance, whether the customer has a current maintenance contract,
4. If you don't have anywhere else to put it - you might not have a sharable, accessible place to put the data.

Another shortcut - using a partner

(remember, this isn't an endorsement of particular partner - best to check the reviews on AppExchange, and see if there's a review solving a problem like yours)

If you want to report on some segments of data, you may be able to use a partner to do that - for instance, to report on your financials and salesforce.com pipeline data, you can use Lucidera. If you want to get other sources, other vendors like VisualSmart may be able to help you. To report across other sources in-house, you can choose the driver for Crystal Reports, or the Cognos 8 connector. Both let you build reports across multiple systems including salesforce.com. JapserSoft might also let you get those reports.

Getting data out of salesforce.com

You can use a number of solutions to get date out of salesforce.com (most also work to bring data in) at the AppExchange listing for Data Integration partners.

If you are looking for free, you can also look at the sforce data loader. You could also start working without the ETL tool, just using the Weekly Data Export service files from the Data Export service.

Once you have the tool or files, you can get a section of data out. salesforce.com data is usually easy to deal with because there is a unique primary key for everything - the salesforce.com record ID.

Putting it into a Data Warehouse or Data Mart

If you're a follower of the great Ralph Kimball, or, heaven forbid, the dark prince of warehousing, Bill Inmon, you've probably got some big database you put all or some of your data in. You've decided to skip ideas about having the data in salesforce.com or another on-demand service. You now are responsible for a large amount of hardware and software...

Most of the salesforce.com data is slowly changing dimensions - Accounts, Contacts, Leads. You are probably going to have to make your dimensions congruent with the others from other systems - after all, that's the whole point of this. You will need keys available in both systems - such as DUNS numbers for the accounts, maybe email addresses for the contacts. These objects may also be counted as parts of the dimension bus. The dimension bus will take your data to data mart-land.

One good point is that the key in salesforce.com is unique, lives over the lifetime of the record, and is available, so whether you are importing, looking for duplicates, merging data from multiple systems, doing a match-merge on multiple lists, or just looking for new records that may have changed since the last time, you can use the salesforce.com ID.

Most of the extraction tools also do bulk data loading. For things like activities, you'll probably need to do partition-organized tables to allow you to drop old months of activities after a while.

You can then point your standard BI tools at the warehouse and query away!