Reporting and Dashboards Blog
Reporting and Dashboards Blog - February 2008
-
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!
-
Overriding created date when importing records
Feb 13, 2008When you are importing data into salesforce.com, one of the things you ought to watch out for is the creation date. Now creation date may be very important to you - for instance, if you are migrating support cases from your legacy system into salesforce.com, you may want to be able to have the creation dates of of the salesforce.com cases. If you are importing leads, they you want to have them "back-dated" to set their dates to be the dates you got them.
What is the creation date set to?
The creation date is normally set to the date and time of when the record is created. If you create a record, the value (just like the "last modified date" and "last modified by") is set automatically. Your user created the record, you are down as the creator, and the moment you pressed save. That's normally good - want to know what leads you got last week? Just filter on created date in last week. To know what cases were created per month, just group by date, set the resolution to month, and it will find the month by month trend.
So I don't have control over that field!
You don't normally. It isn't there when you are editing a new record, but it's not even there on the "new" screen.
But you can ask to be able to insert data you want into the field, by asking support nicely. The special perm you need is called "Create Audit Fields"
So I can control the field after all!
Yes, you can. But you can only do this
- when loading through the API
- you have to ask support
- you have to be importing old data
so there are some restrictions...
The role of support and why it's like that.
It's like that so that normally we don't have to do extra work and check that you might have meant to backdate a record. It's controlled by support because having it on all the time is bad. To most companies, when records were created is important. You don't want people changing historical data. Like changing the amount in a closed opportunity, or changing the created date of a case to make it look like it only took 20 minutes to close, it's a big no-no.
So unless you are working to import old date or to restore damaged data, we want the dates to be really when the record was created.
Why the date is important in reporting
Filters on dates often run by default on the created date. Also, if users don't know what day data imports started, or even in the future that there was a legacy system, they expect the create date to be the date that the record was first created.
Many users will see queries return "wrong" results if the create dates are not back-dated when entering data - alternatively they would also see "wrong" results if the dates on normal new records were not set with the create date of the record when they were created.
Since the created date is so often used, and it's fairly selective as a filter, we also optimize around it when building queries. So if everything looks like it's created on the same date, that's normally a bad thing. Creations are supposed to happen over time, so if all records are created on the same day, then
What you need to do
If you are loading large sets of historical data, think about how users are going to use it. If they:
- Will expect the created date to really be when the information was retrieved from a customer (e.g. lead or case creation time) even if it was in another system
- Need to report on the set of data imported as the primary object in a report
- Are using salesforce.com as the replacement for a legacy system and want an uninterrupted history of customer interaction
Then you'll need to ask support about the "Create Audit Fields" perm on your org, and tell them how long you'll need it. Good luck with the importing! And hey - at least you won't have 3 people stressing full-time just trying to keep your legacy system working after the changeover!
-
420 scheduled dashboards after 1 day
Feb 11, 2008So, after 1 day of real usage, on the 3 nodes that were upgraded (na0 or ssl,na1,na5) around 421 dashboards are scheduled at the moment.
About 10% of people accepted the default choice in the times, which, for people with nothing scheduled already, is midnight local time.
The time breakdown of the dashboards is: (all times are your local times)
hour in day % of dashboards scheduled to the hour 0 9.98 1 6.41 2 4.28 3 3.33 4 5.46 5 7.36 6 11.16 7 13.54 8 8.31 9 5.23 10 3.09 11 2.38 12 3.33 13 2.14 14 2.85 15 0.95 16 2.14 17 1.43 18 1.90 19 1.19 20 1.43 21 0.71 22 0.71 23 0.71 So most of the dashboards seem to be scheduled to come at the start of the work day.
P.S. Remember, although I can see that a dashboard is schedule for a given customer, I can't see the details of the dashboard, or what data is there. This data is actually taken from internal monitoring that tells us dashboards to be executed in the next day.
pretty graph
-
Exception reporting today in salesforce.com
Feb 4, 2008Currently, one of the most popular ideas on the ideas.salesforce.com site is exception reporting.
You can find the idea here - Exception reporting (outer joins)
You may say "hey - but you talked about outer joins before - you even delivered it as a feature!"
To which the answer is, as described in the Types of Joins blog post - outer joins is not exception reporting.
What is exception reporting?
Exception reporting is reporting on when there is something not expected or normal. For instance:
- Opportunities with no activities
- Accounts with no activities
- Opportunities with no products
- Campaigns with no members
Now, you might have other examples - for instance, if you are looking for Opportunities, in your usage of salesforce.com, they should always have products.
Using standard fields
There are a set of standard fields that can help you:
- last activity date
- This is on account, contact, opportunity, lead. Want to find one of these that doesn't have any activities? Create a report and then create a filter:
- This method lets you get lists of:
- accounts with no activities
- contacts with no activities
- opportunities with no activities
- leads with no activities
- campaigns with no activities
- Has products
- This is a standard field on the opportunity object. Want to find an opportunity with no products? Just create a filter:
- self-service comments
- This is a field on cases. Want to find cases that have no self-service comments? Create a filter to check this field is FALSE.
Using RSFs
Roll-up summary fields were a new type of field introduced in Winter'08. They are expanded in Spring'08 to cover the relationship between an opportunity and it's products.
But using these fields, in Spring'08, you can:
Create a field on Account that counts the number of opportunities:
Here, I've asked to count the number of opportunities for each account.
I can now make a filter to check that the number is 0 in my report:
and then my report will give me the accounts with no opportunities.
You can use roll-up summary fields to get a number of different reports:
- accounts with no opportunities
- opportunities with no products
Using RSFs with filters
You can also use Roll-up Summary fields with filters. This allows you to filter out the data you don't want from the count. For instance, if you look at the "Accounts without Opportunities" report, you might say "I know some accounts have Opportunities, but they are years old! and Closed! You idiot!"
You can use a filter in RSF to filter out closed opportunities:
Now your count will be only of open opportunities!
This allows you to get
- Accounts with no open opportunities
- Accounts with no closed opportunities
Afterword
Between the standard fields, and the Rollup-Summary Fields, you can get many of the exception reports you need. You might need to create new fields, but these techniques allow you to get many of the reports looking for exceptional Accounts or Deals (exceptional good, or exceptional bad :)
Bookmark
