Reporting and Dashboards Blog
Reporting and Dashboards Blog - January 2008
-
Lotus Notes and the Emailed Dashboard feature in Spring'08
Jan 31, 2008As a number of you in the pre-release program pointed out, dashboards don't show up right in the Lotus Notes client. The images show up as a Red X box.
I'd mentioned the problem when talking about Scheduled and Emailed Dashboards in salesforce.com.
PTFs and things
IBM works apparently by assigning long strings of numbers and letters to things. The problem in Lotus Notes is detailed in a Technote about PNG images display red X in Lotus Client.
According to this post about PNG and Lotus Notes (that's served by a Domino server! Woot!), no customers are supposed to have requested the enhancement - denoted by the SPR PBAO65AKFU. He asks that you do.
Apparently this problem has been going on since 2006 - in this post about PNG images it already doesn't work.
Maybe the plan of assigning long strings of numbers and letters to things is not enough by itself to resolve them :-)
Anyway, if you access the mail using a browser, it will work. I know that's no help to users of the Notes client, but in the short term, that's all we can do. -
What do you want all these reports for? Why use reporting in salesforce.com? What should be on my sales dashboard?
Jan 25, 2008This time, I'm going to get away from the details about features.
When people get started buying salesforce.com for salesforce automation, they are usually asking the questions:
- What are all these people working for me doing?
- Are we going to hit our number this quarter?
Starting with salesforce.com
When many companies are looking at a CRM system for the first time, they are asking basic questions:
- What are all these people doing all day?
- using salesforce.com, you can report on activities by department or person, to see how many calls or meetings were held
- How much have we sold this month?
- closed opportunities, where the close date is set to "this month". Closed deals might be done and signed, but your other systems might not reflect that until payment is made.
- How much did we sell last month?
The evolution of questions
The questions managers ask evolve. After getting the answers to the 3 questions above, you might ask:
- It's the beginning of a month. What will we sell this month?
- use reports showing sales per day for multiple months
- What open business should we close next month?
- What does our trend of sales for all products look like?
- sales per month for last 3 months
- We did a promotion in September, did it have any effect on sales of the product?
- per-product version of the 2ndchart above
- We did a promotion in September, did it have any effect on sales of other products?
- similar to sales cumulative per day, but with the two products as different lines
Now there's a big difference between the first 3 questions above and the last 2. The first 3 are asked pretty much every day. That should scream one thing at you:
DASHBOARD!
If you are asking questions every day, the answers go on a dashboard.
Does the question have one number as an answer (e.g. total sales this month):
- Yes: there should be a Metric or Gauge object on the dashboard with your answer
- No: you need a graph or table
Next time
We'll talk in a future post about the next set of things to track. In the meantime:
Decide what numbers are important to you, for each project, product, or month
- Is it one number or a set of numbers?
- Do they change over time?
- Are the history and comparisons important?
My question to you:
Can you comment on:
- Do you think salesforce.com needs to take you through these steps to build one of these?
- Or to keep giving you the tools to make it, and you decide what to show?
More chart features? or more features to help business people build their own charts and make them understandable?
-
Outer joins - why and how
Jan 15, 2008I talked in the last post about the Outer Joins feature, and I'd like to explain:
- how the filtering and reporting over the "with or without" relationships works
- why we did the filtering the way we did
- what that means to you
- what you can do with the feature
- what you can't do
and all that in the next 8 minutes!
Have you read Types of Joins - a Primer ? If you have, then we are ready to begin.
The join, not considering filtering
From the "Types of Joins" post - let's look at a dataset.
Accounts Opportunities Account Name Account Revenue Account Owner salesforce.com $497,098,000 Marc Benoiff Widgetplex $25 Tom Tobin startup.com $0 Tom Tobin Account Name Opportunity Name Opportunity Amount Opportunity Owner salesforce.com Plants for salesforce.com $20,000 Drew Sechrist Widgetplex Plants for Widgetplex $20 Carl Schachter salesforce.com Plants for incubators $20,000 Drew Sechrist So now we have these two things, and when we outer-join them, we can get back all accounts, even if there is no opportunity:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 Plants for Widgetplex $20 startup.com $0 How the filtering works
The filtering on outer joins is different because of way the outer joins works.
If we filter on opportunity, we still have to take all rows from Account, because that's always what we do.
So, if we filter on "Opportunity Amount>$10,000", we'll get:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 startup.com $0 But why?
Because we have to show all the Accounts. If we remove accounts just because there is no opportunity over $10,000, we are back to the "with" report that we already had since Summer'07 in Custom Report Types.
Why we did the filtering that way
When we did the filtering, we were worried about one thing - keeping the list of Accounts there. That's why we do the filtering the way we do.
Now, you may run into the restriction on filtering across an outer join... you can't use OR.
In an inner-join report using "A with B", you can filter across both elements. In this example, you can say "Account Revenue>$10,000 or Opportunity Amount>$10", and get:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 Plants for Widgetplex $20 What could that mean on an outer-joined report?
We can't remove the Account when the Opportunity doesn't satisfy the criteria, because our basic rule is "the accounts always come back".
We can't include rows from Opportunity if the Account has Revenue larger than $10,000, because there may not be any. But then if a row doesn't satisfy either criteria, how can it be included?
Do you understand it? I sure as heck don't. It makes your head hurt just to think about it.
So you can't use OR across the outer join.
What that means to you
When you filter, the filter on the outer-joined part of the query only affects the outer-joined part of the query.
And if you had a report that was based on an advanced filter, using OR, then if you change your CRT, you'll have an error when you try to run the report. If you can edit he report, you'll be taken to the criteria page to patch up the report. Normally, using AND will do something close to being right.
What you can do
So now you can report even when there are no children. This is key in cases like:
- Campaigns that might not have members
- Opportunities that might not have Products
- Cases that might not have Solutions
Anything that can be used to build a Custom Report Type, can be used when building an outer join.
What you can't do
You still can't do full "Exception reporting". This feature can get you some things, and some exception reporting is possible already. I'll talk about exception reporting and what you can do in a another blog post.
You can't do exception reporting because if you filter on the child - that may or may not be there, it doesn't make a difference to the parent records we return. We always return all the parent records, so filtering the children doesn't get you anywhere, apart from removing child records.
Takeaways
The new joins are a useful tool in the hands of administrators or power users that can create Custom Report Types. You can use them to let report builders make reports they could never build before, allowing them to see data in different ways.
So, wait for production, and I'll have packages of some examples. Until then, sign up for pre-release and try it out there, and tell me if you aren't able to get the same answers as I am.
-
Public Appearance & Pre-release info
Jan 14, 2008I'll be at the San Diego User Group's next meeting on Wednesday, talking about the features since Summer'07 (Custom Report Types) and into the future, covering Spring's released features - Outer Joins, Scheduled and Emailed Dashboards, and High-precision aggregates (that I didn't write about yet). I'll also be talking about whatever people want me to talk about in the Q&A session.
Also there will be people from AppExchange partner Lucidera, to talk about how they extend the analytics in the product.
Pre-release
Pre-release is available, if you want to start to try out the new features.
You can sign up here:
http://prerelwww.pre.salesforce.com/form/trial/prerelease_spring08.jsp
you can get more information at the Spring'08 start page.
You can then play with all the new features.
Whoa! There's so many links in this post it looks like something from Gizmodo!.
-
Outer Joins and 154
Jan 8, 2008The more attentive, or those subscribed via the RSS feed to the list of ideas might have seen this idea go by:
Outer joins as "with and without" relationships in Custom Report Types
And to clear up any confusion, this post is going to cover what you can and can't do. Also, some of you will start very soon to have access to the prerelease environment for the Spring'08 release, and I'd like to make sure you know what will be coming.
What are outer joins?
I dealt with different join types in the blog post:
so you may want to go back and read that to make sure we are both thinking the same things when we read the same words.
What is the Outer Joins feature
The changes we're making are to allow the use of what the database world calls outer joins (there is no good RDBMS way of talking about the "Exception reporting" that is one of the most popular ideas)
The concept is that you should be able to ask for all records of a parent, whether or not it has child records. For instance, you can list all opportunities with or without products. You can already do that specific report today in the standard Opportunity reporting, so I'm going to cover another example for the remainder of the post.
Accounts and activities
Lets look at Accounts. Accounts are an object that can have a list of cases. They can have lists of many other things, but for now, cases is what we're going to look at. Activities track tasks and events, in this case, related to a given Account.
Accounts might or might not have activities. As of Winter'08, I can create a Custom Report Type, based on Accounts with Cases
and when I run that, I will see the list of accounts that have cases:
but I don't see any results of accounts that don't have cases. And I'm sure that I have more than these few accounts. This is the Developer Edition demo data, and there are many accounts.
Also, the lack of other accounts might be more than annoying if I wanted to see what % has cases - for example, in the past month.
So, what if I wanted to see what percentage of accounts that had cases in the past month?
I'm glad you asked that question.
Well, let's go back to the Custom Report Type, but hit the "Time Machine" button on my special salesforce.com computer that makes it be spring. Now I have access to the Sping'08 release. I'm now going to edit the same Custom Report Type:
Well, I have a brand new option. I'm going to click it, and make this CRT have a "with or without" relationship between Accounts and Contacts. Cool!
I can see that once I changed the relationship at the Contact level, it changed it at the Case level. Now if there are Accounts with no anything, I'll see them, but also I'll see contacts with no cases, but then if there are cases, I should see them.
Now I can run my report again. As a user, I see no difference, no change in the report wizard. Also, as a user I can't change the relationship semantics. Since most users won't know what relationship semantics are, this might be a good thing. Of course, if you change the definition of a report type, all reports based on it will use the new definition when they are run by your users. This might be bad to change a report definition without telling your users
Which is nice. Once, when I was living in Paris, I flew to the south for a holiday, to a city on the Mediterranean renowned for it's Promenade des Anglais. Which was Nice.
But anyway, now I see all the accounts, whether they have activities or not.
Getting to numbers
First I'm going to group by account.
Now we're going to use the technique covered in "the power of one". Here, I'm going to put in count fields on the cases and Accounts, to let me count them, and then a custom summary formula to total this and get my percentage.
More wierdly, I'm going to :
Hey! So what's Case.Account.num of accounts?
It's a lookup from case, to the account, and the "num of accounts field" which is 1. I added it using the "Add fields via lookup" when doing the page layout of the CRT.
But I'm getting it twice?
Well, the first is if you go get the field from the case. The second is just getting a count of accounts from the list of accounts returned.
Hey presto, 1 for each account for which there is a case, 1 for each account listed, I should get the percentage of accounts with cases!
and let's see the result:
of course, this view isn't that great because there's a line for each Account. But I can see that I get 100% when there was a case, and 0% when there was not. But if I Account Type, and add a count of the accounts, I can see:
Which is a much more useful view for me. Those direct people are ok, but I better talk to Elay about bringing up a partner portal to track channel support cases. And find out why custom type is empty on some of the records.
In a future post, I'll cover why we did this, and why the behaviour is like it is for filtering.
Best practices
Although as you play in pre-release, you can go around changing things and setting whatever you want, in your real environment, things are different:
- don't change all your CRTs to be outer-joined
- you will confuse your users and change their results. Additionally, some things (like doing an OR in an advanced filter across the outer join) are not allowed, and your users will have to fix the report.
- changing results on users is bad
- clone the CRT, and change the clone, and roll it out to your users with a new name and a new description warning them of the change
- make sure your description says where the outer joins start
- e.g. explain that this CRT - "accounts with contacts, with and without opportunities" - has a description of: "Report made using this report type will list all accounts with contacts, whether they have opportunities or not. If they do have opportunities, this will return opportunity data. Filtering on opportunities will not restrict the list of accounts and contacts"
- don't deploy CRTs with Outer Joins until you are ready
- use the "in development" status to keep these CRTs to users with "Manage Custom Report Types" permission
- build and test in sandbox if you need to test what other users will see
- build reports on the outer-joined CRTs to give users a starting point
- as well as the CRT with the description, create an example report to help your users see what data they will get
- don't change all your CRTs to be outer-joined
-
Scheduled and emailed dashboards - the feature
Jan 4, 2008So I talked in the last post about the Scheduler. Now I'd like to run you through the feature. You'll be able to get a lot more detail when the Admin Preview is released, when pre-release is available, and in the documentation on the Admin Preview, which, unlike this blog, is in all the languages we support, and written by somebody who doesn't pepper his prose with inane pop culture references, or talking about people you don't know.
New features
New features are one of the benefits of multi-tenancy. We build it, turn it on, everybody gets it. Of course, given that everybody gets it, we have to restrict access to it, to make sure it isn't overloaded. I talked about that in the last feature post: Scheduled and Emailed Dashboards coming soon
This new feature
Scheduled and Emailed dashboards is designed to let a user set up a dashboard's data to be refreshed every day (or less) and then (optionally) send out the new snapshot of the dashboard as an email.
Cloning a dashboard won't clone the scheduling and emailing - because the schedule might not be available. Also, when you install a package from the AppExchange, there will be no schedule - again, the schedule might not be available.
You can schedule personal dashboards, if you have the right (the permission on your profile). You can only send it to yourself though... otherwise, other people might see what they are not supposed to.
New permission
There is a new profile permission to control if a user can schedule. For instance, you can set up departmental managers to schedule initially, until most of the scheduled slots are full. Then start to examine which dashboards are scheduled, and whether they need to be, and whether they are time-sensitive.
Users who can schedule cannot delete schedules (because they could well belong to other people), and since we don't have CRUD on the jobs, only people with "Modify All Data" can go around deleting other people's jobs.
Scheduling a dashboard to refresh
To schedule a dashboard to refresh, it's a lot like asking a dashboard to refresh now:
Then just ask for a schedule, like every weekday:
And ask for a time:
By default, we'll select the earliest times available. In this case, midnight, 1am and 2am have been taken, so we're showing 3am as first available time, and chosen it. You can change the selection, of course.
Of course, if you've been busy, there might not be any times...
What it looks like when many slots are used
When every single time slot is used, you can see something else:
This means that it's time to look at the management interface and see which ones you no longer use.
Emailing
We can also email the output of the dashboard. Then you won't have to login to see it when it's refreshed.
You get a choice of recipients:
If you choose "Me", then you'll be the only one to get the mail. "Me" is enabled by default, because we think:
- you probably want to know that it did refresh
- if you are the owner of the dashboard, you probably care about the data
- if you are the admin of a smaller company, you also want to see that your user's data is right
You can choose others, and it will let you choose users who can see the dashboard - in fact, the list on the left is taken from the list you set up when deciding who could see the folder that the dashboard is saved in:
The dashboard you receive
So then you are sitting around, and get the email. What does that look like:
Well, it looks pretty much like the view on the screen (with one big exception see below). There is no branding on this email - we've had a lot of feedback that if we send emails out, they should either have controllable branding or no branding. For now, I'm going with "No branding" unless I get an Idea on IdeaExchange asking for templating.
The management view
In the 1980's there used to be a comedy duo on UK TV called "Hale and Pace" - if you've seen the last episode of "Extras", you'll have seen them (much older) trying to get into The Ivy restaurant in London, and being refused. They were "The Management":
You can see them in action at: http://www.nationalarchives.gov.uk/films/1979to2006/filmpage_fireworks.htm
Don't worry if you think you are missing the joke, they weren't that funny at the time. And this is a public information film, so it's probably less amusing.
But there is a screen to manage the set of scheduled items:
And the answer to one of your questions is "No, there aren't any other types of schedule jobs for just now". Dashboards are the only scheduled type of job you can have in Spring'08.
Why I hate Lotus Notes pt 6
I'm old. Old enough to remember cc:Mail. cc:Mail was kind of cool, in the way that Model T Fords were probably cool, and the first dishwashers were probably cool. I'm not old enough to remember those. cc:Mail worked, and it wasn't too weird, or clunky. The client had icons, and windows, rather than pressing r to mark the mail as read, and you could click and see things and send mail. And it could be run by a sysadmin who only knew about Novell, and had a password of "racer2".
Then came Exchange for me, and Outlook as the UI, and I was happier. It worked the way the rest of the Windows computer did. But for many people, upgrading from cc:Mail meant Lotus Notes. Notes was a database, offline synchronization, a development environment, a cross-platform tool, scripting, different databases, mailboxes, all kinds of things. Some IT consultants even argued you could have Exchange for mail and Notes for your knowledge base and some applications, and synchronizing and moving data. I had to use Notes once when I was a data warehousing application consultant at a client site. As much as I disliked Notes as an QA engineer, and a Product Manager, when I was a user, I disliked it more.
What Notes isn't is new and clever. It is not "teh new hotness" as they say on teh internets. And so it doesn't know what to do with a .png file. PNG files are an image format. Like GIF, or JPEG. Unlike GIF, it wasn't mired in a legal morass for years. Unlike JPEG, it's lossless, so computer graphics of graphs look clean and precise. We use PNG for our graphs, because it's small, all browsers support it, and it makes the graphs look clean.
Notes doesn't read PNG files. Not even the new V8 Notes. So, no graphs for you.This isn't a personal grudge that I don't want to make it work. We have Notes to test it, and we can make it work by sending jpeg files (so the graphs look fuzzy) or sending BMP files, which makes each email over 2MB, which is probably not acceptable.
BTW, This post was build differently than all the other ones. Tell me if you could not see the images before, and can see them now, or vice-versa.
-
SimpleDB
Jan 4, 2008you may have seen many blog posts about SimpleDB... some people asked me whether "I can use SimpleDB as my database for anything" and I thought I'd reply here.
Some of the blog posts I've seen are
http://www.b-eye-network.com/blogs/ploshin/archives/2007/12/amazon_simpledb.php (talking about why it's so cool)
and
http://www.25hoursaday.com/weblog/2007/12/21/AmazonSimpleDBTheGoodTheBadAndTheUgly.aspx (talking about why it's not cool)
SimpleDB's storage
SimpleDB has one way of putting things in, and it's programmatic. From their doc:
Action=PutAttributes &Attribute.0.Name=Color&Attribute.0.Value=Blue &Attribute.1.Name=Size&Attribute.1.Value=Med &Attribute.2.Name=Price&Attribute.2.Value=0014.99 &ItemName=Item123
And then some other stuff which is related to you as a user (http://docs.amazonwebservices.com/AmazonSimpleDB/2007-11-07/DeveloperGuide/SDB_API_PutAttributes.html).
In fact, this is all you need to do as your first call. You don't need a schema, you don't need declarations of types, or anything. No setup, no relationships, no defining picklist values. You might think it's your lucky day.
It isn't your lucky day
All those pieces of data are stored as strings. See the leading zeros on the number? They are there to make 14 smaller than 1200. If they weren't there, the string value of 14 would be larger than 1200, because 4 is later in the alphabet than 2.
There is no schema because it's a set of name-value pairs, for a given item. It's like writing an index card for each record, and each card can have different attributes - the names - for each value. So you can't say "Size can only be Large, Medium, Small" or "Price must be a number and can't be blank". It's the old problem of complete freedom, lack of rules, anarchy, and there are downsides.
There are other restrictions, like the fact that queries cannot run longer than 5 seconds, or that it is not (despite what some people think) an RDBMS.
An RDBMS?
A long time ago, relational databases were deemed to be better for many things than other forms of storage. People like Englishman Edgar Codd built rules about how to manage relational databases that made them really useful.
Relational Databases have relationships between the tables. You can say that a column in the database is related to lines from another table. A DB is a database. An RDBMS is a Relational Database Management System. You have to tell me if I'm telling you things you already know.
Amazon didn't call it SimpleRDBMS. In fact, RDBMSes are notoriously not simple. SimpleDB doesn't have joins. You can't query across more than one of their "domains" of data. Their domains are where you put data that you don't want to find in the same search query. So if you have a domain of orders, and a domain of products, the fact that it's not relational means that product data must be listed out in every order, and then there is no way to see how many orders there are per product.
So Amazon's service is probably not the answer to questions about storing salesforce.com data somewhere else and being able to get it back later. It's probably not going to be the system of choice if you wanted to get data out of salesforce.com and query it better. Better to use tools from the AppExchange to do that. It might be the system you want if you want a web application to store comments, or items to track where you can control all the access (and so enforce all your data rules somewhere else). Or to be able to push single-object small datasets over for something, then query to get them back.
-
Scheduled and Emailed Dashboards coming soon
Jan 1, 2008So, you may have read
http://sfdc.arrowpointe.com/2007/12/12/coming-in-spring-08/
That I'd changed the status of the idea to "coming soon" and it's targeted to Spring'08.
If you are extra-perceptive, then you may also have seen this notice on SalesforceWatch:
http://www.salesforcewatch.com/2007/12/spring-08-pre-r.html
telling you the dates for the Spring pre-release, which is soon, and getting sooner every day.
The feature:
This will deliver on the idea:
http://ideas.salesforce.com/article/show/70864/Schedule_Dashboard_Refresh
as well as half of:
http://ideas.salesforce.com/article/show/22241/Scheduled_and_Emailed_Reports__Dashboards
which would give it:
5400 votes from 70864
1/2 of the 10233 votes from 22241
a massive 10518½ votes!
What we enable is in two parts - scheduling and emailing. I'll cover the scheduling for now, then go into the emailing in the next post.The scheduling idea:
Think of a system that limits the number of things you can do.
Fun? No! But unfortunately, necessary, unless we want to follow the tragedy of the commons.
It could do a few things:
- Restrict the number of jobs per customer
- Restrict the total number of jobs per user
- Restrict refreshes to only outside of business hours
We'd rejected the first - the load being too unpredictable. We would see everybody running their dashboards all at 9am. Or 5pm. Or some time. Anyway, it would make the service spin out of control like trying to drive a fork lift truck fast backwards. My friend Dave tried that once. That one wheel didn't let him steer fast enough.
The second seems nice, but the only solutions to limit the load involve giving users less than one dashboard each. And then who will decide what gets scheduled? And there's still the same problem - if we gave each user 1/20th of a dashboard, and you have 4000 users, you are probably going to spend the 200 dashboards all at 9am or 5pm, because that's when people wanted the dashboard.
The third is something we couldn't see being very popular. If you are a large global enterprise, there is no real off-hours, and also you might not be able to wait until the evening, or you might be monitoring something outside your time zone, and when they finish is during your day.
Ok, we are pessimists. The usual fix to avoid the commons problem is to divide up the common so a small group can talk amongst themselves and decide who gets what, and make sure people don't go over some limits.
Scheduling a dashboard
Think about something that does have inbuilt limiters on what you do...
Your DVR is one.
(this is a picture of a Tivo V3 HD, which I don't have, so it might be somebody else's DVR)
(Tivo is a trademark of TiVo Inc )
The DVR can record TV programs, but it has 2 tuners, so it can record 2 at the same time. Most TV programs fit into slots, because it's easier if programs start and finish on hourly (or, for funny shows, 30-minute) boundaries. So you can record 2 programs at the same time, but some start and end at different times. And the DVR can tell you that you can't record Grey's Anatomy because CSI:Little Rock and Oprah are on at the same time. Once it's told you that you can't record all 3, you can change to record the 11am re-run of Oprah, and be able to record all your shows.CSI:Little Rock? What has this got to do with Dashboards?
DVR Scheduled Dashboards You can schedule when programs are on You can schedule every hour The number of slots depends on how much you spend for your DVR The number of dashboards per hour depends on your edition. Some people don't have or want DVR. Scheduling and email is for Enterprise and Unlimited Edition customers in Spring'08. You can't record if your slots are full You can't schedule at that time if the slot is full You might have every hour of every day full, so you can't record anything.
You have to go delete some scheduled programsYou might have every hour slot full with dashboards, and might not be able to schedule anything more.
You need to delete some scheduled refreshes.
So, next time I'll cover the feature in more detail.
Bookmark
