Reporting and Dashboards Blog
Reporting and Dashboards Blog
-
Using the Eclipse Plug-in to change reports in salesforce.com Winter'09
Nov 17, 2008One of the additions in the Winter'09 version of salesforce.com was the addition of reports, dashboards, and their folders t the metadata API.
The two kinds of API
There are two kind of different APIs in salesforce.com
- The data API
- This is the API that s-controls use, that Apex uses, and it lets you do many things. For instance, it allows you to create Accounts, change custom object records, get lists of Cases, and create workflow.
- The metadata API
- This is the API that can create objects, get lists of folders, create fields on objects, and now, thanks to the miracles of science, list, create, edit, and delete custom reports
In the Winter'09 release of Salesforce.com, the reports are part of that Metadata API, and you can list them, create them, delete them, and change them.
But what if you are not a programmer? What if you just want to mass delete?
The Eclipse IDE.
Salesforce.com has an plug-in for Eclipse. Eclipse is a standard IDE, developed by the community, and a set of companies. Before, every company made it's own IDE.
IDE - is an Integrated Development Environment. It's designed to let you do everything you'd want to do to develop. You might say "Hey! I'm an Admin, I don't want to develop". But you do - you want to develop reports!
Back when every company made it's own IDE, the world was less fun. Oh, the area behind my house was all trees and majestic herds of wildebeest roamed the land, but you had to learn a whole new way of doing things when you changed what you worked on. Unless it was the wildebeests.
What you'll need:
You'll need a two ingredients:
- The Europa EE build of Eclipse
- you can get this from the Europa Eclipse download site
- you need the Europa EE version because it includes everything you need, and we don't work with the newer versions yet
- You need to install Eclipse, just unzip the file somewhere
- The salesforce.com plug-in
- from the Eclipse IDE download page (with instructions):
Getting started in the IDE
The first thing you need is a project - which is, for a salesforce.com project, a connection to salesforce.com with a login:
Then you'll be able to fill in the login details. You should never need to change the "Environment" choice:
Next, you should see progress - a progress bar as it downloads the configuration behind your salesforce.com login.
Then, you'll see a new dialog box where you can choose what to download. The easiest way to do this for reports is to choose the option "Selected metadata components" and press the "Choose..." button.
Then, we get to choose the reports:
Here, I've chosen to get anything in the "Activityreports" folder, plus one of the reports from the "Campaign Influence Reports".
And now we can see what we're going to download:
Now the exciting part! Progress!
And now, we can see all of the project's contents in the folder/package view of Eclipse:
Ok, now what?
Well, first, let's do a mass delete. First, select some reports:
I've just selected the first, held Shift and clicked the last. I can also ctrl-click on reports to select random reports from the list.
Then press the delete key on my keyboard, and:
then the best bit:
Yes! Yes!
Ok, next time, using search and replace....
- The data API
-
New features in Winter’09 for Analytics in salesforce.com
Aug 28, 2008It's that time of year again… well, not year. It's the tri-annual time for – wait, isn't tri-annual every 3 years? Or is it three times a year? The quadmestrial unveiling of features. I've set these to "coming in Winter'09" and will be drilling into each one with more detail later in other blog posts.
Scheduling and Emailing Reports
Well, this one was top of the vote list. It's been there for a while even, so we thought we ought to do it.
In fact, there's no just scheduling. You have to email, because there's nowhere in salesforce.com to store the report results. The behavior is just like scheduling dashboards or analytic snapshots. It's available for Professional, Developer, Enterprise, and Unlimited Editions – different editions get different numbers of slots.
If you wanted a history of data in an object, you'd use Analytic Snapshots, rather than keeping a set of old reports. Then you'd actually be able to view the history in a set of different ways.
The output is the HTML of the report – the kind of HTML you'd see in the printable view, for instance. The output is emailed to you, if you can see the folder which contains the report.
Reports, Dashboards, Folders in the Metadata API
Well, this is a pretty big one. It allows you – from the Metadata API (you can't get to the metadata API in Apex, for instance, because things could get really weird) to create, read, update and delete reports, report folders, dashboards, dashboard components, and dashboard folders, as well as custom report types, via the API.
There isn't really one idea for this, but these ideas are the kinds of operations you'll be able to do:
Images in Dashboard Emails compatible with Lotus Notes
As I'd said before, the "Scheduled and Emailed Dashboard" feature Scheduled and emailed dashboards - the feature didn't support sending to Lotus Notes. Or at least, you could send to it, but a Lotus Notes client user couldn't see the images. We've watched IBM's progress on reading PNG files – see Lotus Notes and the Emailed Dashboard feature in Spring'08, and decided that we'd just build a switch to control the image format.
So, you might say "Hey, idiot, why didn't you just build it so it worked with everything when you built it the first time?".
Well, there are two reasons:
- We generate .PNG files for the on-screen version, and we wanted to keep the email looking the same as the on-screen version
- .JPEG files (the only other alternative for a few technical reasons) don't look the same as .PNG files, because .jpeg was designed for photographs (they put the P in jpeg!), and so don't deal with some computer-generated images very well.
So, if you are a Lotus Notes customer, you turn on the switch, and you'll be able to see the images, but there will be a slight fuzziness around the edges of the lines. We can't get rid of it (it's how jpeg compresses the images).
-
Want to speak at Dreamforce?
Jul 22, 2008Right now, the nomination and selection processes for speakers at Dreamforce is open.
If either:
- You feel like others could benefit from your experience of using the set of features to deliver value to your co-workers
or
- You want to school all these punks in how to really use salesforce.com
you can either talk to the employee you normally deal with, or go to:
http://www.salesforce.com/dreamforce/DF08/callforspeakers.jsp
where you can enter your details. Not everybody is chosen, you should enter what you are comfortable talking about, maybe with examples so that the coordinators can see what you are ready to cover.
Generally, we prefer people who have the former view of why they should be there over the latter. Speakers get not only a "tricked out namebadge" (worth less than the price of a new pair of shoes, or $581, whichever is lower) but also other stuff at DreamForce (like free entry).
You'll also get people coming up to you afterwards, asking how you did things.
If you want to know what goes on at Dreamforce, what it's like, and what to expect, you can also talk to one of the local user groups - you might also want to talk to your user group coordinator and ask them if what you are thinking of would go down well at Dreamforce, since the local user group meetings are like mini-Dreamforces themselves.
There aren't just sessions about analytics planned at Dreamforce this year, so pipe up and get ready to get on stage and screen.
(Image on the left is from one of progessive insurance's commercials, for everybody outside the US who hasn't seen it - it's the 5th one down on the list here)
-
Opportunity history - the snapshot you get for free
Jul 9, 2008When talking with some of you about Analytic Snapshots, I've found many people didn't know that there's already a snapshot done of your opportunities every 1st of the month. Working with this and looking at if it can solve your pipeline visibility questions will tell you much about what you need in a snapshot, and if you need one.
What is the opportunity history? Isn't that new in Summer'08?
Well, there is opportunity field history. That's new. It's like the extension of case history and custom object history to opportunities. But it tracks every field that changes (at least, of the ones you select), and each field changed is a new record in that field history.
So, change stage from 2 to 3, that's one line of opportunity field history
Change stage from 2 to 3, amount from 1000 to 2000, and the owner from Pat to Bobby, and that's 3 change rows in opportunity field history.
Opportunity history isn't like that. It will record 1 row, with all the changed values, for the values it saves.
Opportunity history is shown on an opportunity detail page as the related list "Stage history".
So what's opportunity history and opportunity trends?
If you've looked at the report type list for opportunities, there are now three different report types:
- Opportunity history
- Opportunity field history
- Opportunity trends
So, the first one is the list of things visible as the "stage history" on the opportunity, and the second is the field history. What's the third?
Opportunity Trends Report Type
The opportunity trends report type gives you the status of your opportunities at the start of each month.
You can see, for each 1st of the Month, for each Opportunity:
- Historical Amount
- Historical Stage
- Historical Close Date
- Historical Probability
- As of Date
- All opportunity fields as their current values
For instance, you might make a matrix report, showing by Opportunity Owner role (vertically) and by date (horizontally) what was the amount. That report would show the evolution, for each 1st of the month, the total amount of the opportunities.
So how is that different from an Analytic Snapshot?
There are 3 main differences
- It only works on the 1st of each month - so you can't see trends as of the 3rd, or every Monday, or every 1st and 15th of a month. With and Analytic Snapshot, you can choose when it runs.
- It only records a set of fields, and you can't change them. With an Analytic Snapshot, you can control which fields, and even bring in fields not on opportunity.
- If you can see the Opportunity, you can see the trend data. With an Analytic Snapshot, you can do this, but you can also have history available to more or few users.
What you can do:
If you haven't already tried it, make a report using the Opportunity Trends report type, and group by "as of date". That will let you see back into the past, what your pipeline looked like over time.
You might also want to make a report where you filter on "as of date" and then you can choose a month, and it will list the opportunities as they were at that date - whatever the values of stage, amount, close date, and probability were as of that date.
Now, if this isn't enough - you want more fields tracked, you want snapshots done more than every 1st of the month, then follow the guide to create an analytic snapshot, and start building up your own history.
-
CRT limits doubled
Jun 16, 2008If there's two things I like, it's cute pictures of cats, and cats in pictures that look cute.
As part of 154, we've doubled the numbers of Custom Report Types in salesforce.com for all the paid editions. This was in IdeaExchange (discussion in the comments on why):
Increase the limit on custom reports in Enterprise Edition
So they doubled.
There is this theory that if an idea is too complicated to be represented by a LOLcat, then it is too complicated to understand. I'm not sure about that yet.
This is dedicated to Kingsley, who feels that lolcats are wrong!
-
Analytic Snapshots - walkthrough
Jun 12, 2008So, you may have read the marketing page the for the feature:
Analytic Snapshots - Salesforce Features
And maybe even heard about it at Dreamforce Europe - the time machine enabling you to both look back over time, and to go back to a point in time and look at it.
So - how does it work?
You have to do 3 things:
- choose what data you want to snapshot
- choose how often you want to keep the data
- build a report on that data
- create the Analytic Snapshot
ok, there's 4. But 2 of those are just about thinking! You probably do that anyway...
What data do you want to snapshot?
Well, if you want to look at pipeline, you probably want to look at opportunities. You may also want to look at products.
If you want to look at how your support organization is doing, you want to store cases.
If are looking at how your marketing organization is dealing with incoming business, then leads might be a good choice.
You might also have custom objects that change over time - status changes, numbers change, you can snapshot it.
And what data do you want to store in the snapshot?
When looking at the set of data you have, you probably want to capture the data that's changing that you care about - for instance, the opportunity's amount, stage, and custom fields.
You might also want to duplicate the owner information - to make sure visibility on the snapshot data is the same as on the source data.
You might also want to copy across references to other records - for instance, the account for the opportunity, the contact for the case, or the campaign for the leads. This will let users using the snapshot for reporting get the historical data related to this contact or
If you are in EE or UE and can have many snapshots, then you could snapshot multiple levels of data - for instance, not just the top 50 open opportunities, but the products for those opportunities as well.
An example:
I want to store details of open deals, every Monday morning. I forecast and close business monthly, so I want to get a snapshot of how the pipeline is changing during the month. I have the following list of fields on opportunity that I want to copy across:
- Opportunity Name
- Opportunity Amount
- Opportunity Stage
- Opportunity Close Date
- A custom picklist tracking the type of install needed - "Installation Type"
- A custom field for "Number of Installers"
I want to make sure I get owner information and copy it across, and I want the account and opportunity to be linked to the snapshot record, so I can do reports like:
- Account and deal snapshots
- Opportunity and deal snapshots
as well as reporting just a snapshot or set of snapshots.
Step 1 - create a CRT
Since the report has to have many lookup fields with IDs, I make a Custom Report Type, with the primary object as Opportunity
I go to the page layout step, and add in fields available through lookups to get the owner ID, the opportunity ID and the account ID. To bring in lookups, you need IDs.
(for information on creating a CRT, see the help and training in salesforce, or CRT-related articles on this site)
Step 2 - create the custom object to store the data
The next step is to create the custom object to hold all this data. We are going to need 1 field for each of the fields named above (Opportunity Name, Amount, Stage, Close Date, a Picklist for my Installation Type, and a number field for "number of installers". I will also need:
- A date field for when the snapshot was run
- a lookup to Opportunity (to see the current state, and to make the history a related list of opportunities)
- a lookup to account
For the owner, I'm going to have the owner transfer across, so we don't need a custom field for that.
Step 3 - create a report
Now we make the report - I use my CRT and build a tabular report containing all those columns named above.
and save this report somewhere public
Step 4 - Create the analytic snapshot
Finally the new bit! Almost 2 pages later!
I create an analytic snapshot, and use the wizard to create an analytic snapshot
And create a new snapshot:
(here, I have a choice of running user because my user has "modify all data" in this org"). I save this and select to edit the field mappings:
So we've mapped all the fields. You can see the lookups had to have their values taken from the IDs of the corresponding objects.
The last field - snapshot date, I'm going to pick "Execution time" from the "about this snapshot" group of fields. That will give me the time this snapshot was run.
Now we save that.
Now we can schedule (this UI is just like scheduling everything else). We can schedule for every Sunday night, and we'll have the status of the pipeline as of that Sunday.
That's it, just go report on the snapshot!
Reports on the snapshot
Now we can go report on the snapshot. Here's an example of a crosstab with date across, grouped by stage. I can see the changes in stages as the opportunities move around:
Then if I want I can drill around an see the details of the history for that month, then using the lookup to Opportunity, get to the current state of those opportunities.
-
Analytic Snapshots - the overview
May 6, 2008If you've been watching the twitterverse or blogosphere, or just "teh internets" then you'll have seen the feature set for the Summer'08 release.
Feature list in salesforce.com Summer'08
Release notes for Salesforce.com Summer'08
The largest reporting feature is Analytic Snapshots.
Ideas I've set to "coming in summer'08"
I've set two ideas to "coming soon":
And I'm looking for more.
The first idea is about keeping copies of dashboards or reports, so that you can look at them later, and try to see what changed between then and now (or then and then, if the 2nd then is different from the first). The second is about an enhanced view on the opportunity trend report, where you'd want to see both opportunities, and products (at least product family).
Those ideas look nothing alike!
Well, those two ideas don't have a a lot in common. However, they are both about grabbing copies of the data in salesforce.com and saving it so that you have a point-in-time copy. The first imagines that working by saving copies of reports. The second wants a better version of a standard report that copies a set of fields from opportunity, and can report what those fields said every 1st of the month.
Saving the data in salesforce.com
So people want to be able to save old data. But wouldn't it be better to be able to report on the old data, rather than having to manually eyeball - compare the two reports visually?
And to keep history - shouldn't you be able to control which fields are copied? When the data is copied? You might want it every 1st of the month, every Monday, maybe every day at 10pm...
What Analytic Snapshots does
Analytic snapshots lets you take a tabular report, and set it to run on a schedule. Every time it runs, it will copy the report data into a custom object. The custom object is almost just like any other custom object - it can have lookups, it can have calculated fields, it can have default values...
We take the set of data in the report, and create a new record in the target object for each row in the report. You choose which fields are mapped where. Lookups can be created that map to other records. For instance, you can copy across the owner of records, so that you get the same visibility and security of records, or copy across a reference to the account so that you can report on the account and the set of snapshotted records.
Right now, the target custom object can't have Apex or workflow, or validation rules in the target object. But since in many cases those are used to manage manually-entered data, that shouldn't be a huge problem.
More detail next time on what you can do, and a walk-through of setting on up... apparently, blog articles are better shorter. I read that in a blog post somewhere.
-
Book Report - Competing on Analytics
May 1, 2008So I've been away traveling for business and pleasure, and we've put the final touches to the Summer'08 release.
When I was traveling I read "Competing On Analytics" by Thomas Davenport and Jeanne Harris.
This book, (the link alongside is to Amazon - other booksellers are available and may be equally good), covers "The New Science of Winning".
It talks about how it looks when you compete with analytics, what kinds of processes should be covered with analytics, then, how to compete on analytics. It's like the authors went to business school. In fact, they even teach there.
The book isn't a cookbook for KPIs - in fact, they authors don't like that term, since it's inextricably linked in their minds to balanced scorecards and a whole management methodology that involves transformational change for your company.
When it does talk about the how of competing on analytics, it talks about how to build an analytics center of competency. How to hire, build the team, who they should report to, and what deliverables they should have.
How do you know where you are?
There's a handy set of guides to being able to score your company on how it competes. Here's my summary:
Stage 1 You really don't know what's going on inside or outside of your company, and if you do know in one area, it's not linked to any other areas Stage 2 You are collecting transactions, but sometimes not all the data, and you can't get a real picture of what's going on (you should automatically be here with salesforce.com if you are using it at all) Stage 3 Departments know what's happening in their department, but most people can't access other departmental information, and ther eis no global overview (you should be at least here using salesforce.com) Stage 4 Overall analytics infrastructure in place, covering the whole company, and some in-process analytics (if you have integration partners or on-demand analytics partners, you can get here) Stage 5 Your company has achieved a state of Atmabodha, and is completely self-knowing. All decisions are based on in-process analytics hinting you to perfection. Only the person that runs the SAS system need work. When somebody open the doors to your accounting group, money comes spilling out like it used to from Scrooge McDuck's house. One point that they do seem to miss is that most of their success stories are where people invent new metrics that more accurately reflect success as they define it. From the sabermetrics in baseball, to Harrah's looking at guests and spending, to Wal*Mart and their metrics on stocking and shelf space/time. All relied on inventing a new metric or metric system.
If you don't want to rely on a new set of metrics you invent (that might be worse than the standard set), there's a fine set at:
Conclusion:
Great for CEO's, or the person the CEO just hired to make the company run on analytics.
Not much help to implement the strategy - choosing services, approaches, metrics.
What to say when you give it to your boss "Hey - you might want to look what happens when we use all the data we're collecting"
Next time, information on summer'08. Ice lollies all round! (or Ice Pops, or Ice Blocks or Ice Poles, depending on where you are from)
-
My view on Activities
Mar 10, 2008If you've ever looked at facebook, one of the addictive features is the list of what your friends are doing. You can press refresh a couple of times a day to see what Darren's status is now, what application Sharon has installed, or who Kendall is friends with.
That's pretty cool - but why isn't there a feed like that in salesforce?
I could put it in my dashboard, and see what's going on in the world around my team's work, or to the activities of people I can see.
For instance - My sales team's sales activities...
This is also the story of trying to get a report, and having it be a bit more complicated than the usual.
More apex than reports
So, a lot of the times that these posts appear, somebody, or many people, have asked me a question, and I think the answer would be useful to many more people.
As I was trying to get the report, I was stymied. I could not use a formula because the field values I wanted were not present in the activities object. I couldn't use the report formulas because I didn't want a summary formula field in a summary report. So what to do?
Apex
Arg! not a report?
No, you can't do this just with reporting. We're going to use Apex to put the string description of what happened in the event.
The apex below will fire each time there is a new event, or an update to an event. This will update a field that sets a "humand-readable summary". This is something like "Pat met with Bradley about Apex Generator sale" or "Casey did Phone Hayden".
1: trigger eventtrigger on Event (before insert,before update) {
2: String sSummaryText;
3: User uAssignee;4: String sWhoType;
5: String sWhoName;
6: Contact cWho;7: Lead lWho;8: Opportunity oWhat;9: Case cWhat;
10: String sWhatType;
11: String sWhat;
12: String sAbout;
13:14: for (Event eIterator : Trigger.new) {
15: sSummaryText=eIterator.Subject;16: uAssignee=[select name from User where Id=:eIterator.OwnerID];
17: sWhoType=eIterator.WhoId+''; //make sure it can't be null
18: if (sWhoType.length()>5) {
19: sWhoType=sWhoType.subString(0,3);20: if (sWhoType.equals('003')) {
21: // it's a contact
22: cWho=[select name from Contact where Id=:eIterator.WhoId];
23: sWhoName=' with '+cWho.name;
24: }25: else
26: {27: //lead is the only other choice
28: lWho=[select name from Lead where Id=:eIterator.WhoId];
29: sWhoName=' with '+lWho.name;
30: }31: }32: else
33: {34: sWhoName='';
35: }36: sWhatType=eIterator.WhatId+'';//make sure it can't be null
37: if (sWhatType.length()>5) {
38: sAbout=' about ';
39: sWhatType=sWhatType.subString(0,3);40: if (sWhatType.equals('006')) {
41: System.debug('opportunity what');
42: oWhat=[select name from Opportunity where Id=:eIterator.WhatId];
43: sWhat=oWhat.name;44: }45: else if (sWhatType.equals('500')) {
46: // case
47: cWhat=[select Subject from Case where Id=:eIterator.WhatId];
48: sWhat=cWhat.Subject;49: }50: sAbout=sAbout+sWhat;51: }52: else
53: {54: sAbout='';
55: }56: eIterator.summary__c=uAssignee.name+' had a '+sSummaryText+sWhoName+sAbout;
57: }58:59: }So, you can fix the English sentence if you want - to change the fixed words I used to get the pieces of the sentence together.
Won't this only update activities going forward?
Yes, but the ones we care about are new or updated, so that's fine - at least to start with. You aren't going to look at old activities to see new activity are you?
But now each task has a summary field that looks like:
What about tasks?
Well, yes the script above only works for Events. You can put the same kind of script on Tasks:
1: trigger tasktrigger on Task (before insert, before update) {
2: String sSummaryText;
3: User uAssignee;4: String sWhoType;
5: String sWhoName;
6: Contact cWho;7: Lead lWho;8: Opportunity oWhat;9: Case cWhat;
10: String sWhatType;
11: String sWhat;
12: String sAbout;
13:14: for (Task tIterator : Trigger.new) {
15: sSummaryText=tIterator.Subject;16: uAssignee=[select name from User where Id=:tIterator.OwnerID];
17: sWhoType=tIterator.WhoId+''; //make sure it can't be null
18: if (sWhoType.length()>5) {
19: sWhoType=sWhoType.subString(0,3);20: if (sWhoType.equals('003')) {
21: // it's a contact
22: cWho=[select name from Contact where Id=:tIterator.WhoId];
23: sWhoName=' with '+cWho.name;
24: }25: else
26: {27: //lead is the only other choice
28: lWho=[select name from Lead where Id=:tIterator.WhoId];
29: sWhoName=' with '+lWho.name;
30: }31: }32: else
33: {34: sWhoName='';
35: }36: sWhatType=tIterator.WhatId+'';//make sure it can't be null
37: if (sWhatType.length()>5) {
38: sAbout=' about ';
39: sWhatType=sWhatType.subString(0,3);40: if (sWhatType.equals('006')) {
41: System.debug('opportunity what');
42: oWhat=[select name from Opportunity where Id=:tIterator.WhatId];
43: sWhat=oWhat.name;44: }45: else if (sWhatType.equals('500')) {
46: // case
47: cWhat=[select Subject from Case where Id=:tIterator.WhatId];
48: sWhat=cWhat.Subject;49: }50: sAbout=sAbout+sWhat;51: }52: else
53: {54: sAbout='';
55: }56: tIterator.summary__c=uAssignee.name+' did '+sSummaryText+sWhoName+sAbout;
57: }58:59: }Because in API and Apex, Tasks and Events are different...
Report and dashboard component.
We can then do a report on the list of these human-readable activities, and a dashboard.
The report shows the list of the most recent 10 activities - closed tasks or past events - and then displays them (using a top-n report).
The dashboard can then show those 10 items.
Other components
For the package, I also added some more components - for instance, a display of how people are doing on their tasks - showing completed by person for tasks created in this month or last month.
I also added one on the number of activities per closed opportunity status - to show activities driving deals.
This was built using the power of one trick again to calculate the number of activities per opportunity, then the per-stage view averages that out.
Package
The package is available here:
And I'll probably have to update it when Simon Fell or somebody tells me that my method for testing to see whether the WhatId is an opportunity or Case should not rely on substring comparisons.
The package also includes test code that you might find useful if you are trying to test Apex Triggers (especially on Activities). I have 100% test coverage! W00t!
-
Integrating salesforce.com into a Data Warehouse
Feb 19, 2008When 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:
- 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.
- 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
- If it's integral to a process in salesforce.com - for instance, whether the customer has a current maintenance contract,
- 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!
Bookmark
