Apex Code Development You need to sign in to do that
Don't have an account?
Vonvee Related Objects, lookups, etc -- general perspective
Apologies for opening with a broad scale question -- (my background = 12+year veteran developer/DBA .NET/Java/SQLServer/MySQL/Oracle, so talk tech = ok).
I am exploring mirroring some of our data storage into Salesforce (Enterprise) directly so that we can maintain some syncronization of data between local systems and the cloud. We have SQL objects/tables that hold what I'd call "after-sales facts" that is "true" data the company runs many metrics on.
I'm considering mirroring that existing structure as-IS for the objects/fields I want and then pushing up data to those custom objects from our SQL prod DB. There are relationships to SObjects (Opportunities, Contacts, etc) that we carry in the datasets, so mapping the relationships to the new objects is easy.
My question is -- what's the "Gotcha" here? Is there a limit to the capacity or usefulness within Salesforce reporting if the relationships get complex. Example Opportunities relates to CustomObj -- reports fine. But if Opportunities relates to CustomObj1, which is related to CustomObj2,3,and4, where the relationship between CustomObj1 & CustomObj2 is 1-to-many.... see where this is going?
Is it better to push things in a "Relationship" design, keeping both systems so they're 1-to-1 between cloud & local, or should the cloud system be customized (i.e. flattened out for example) so the GUI knows how to use it. Etc. Also keep in mind how the answer affects standard IT things;
1. Time to deliver
2. Interface ease of use
3. Code complexity & supportability
4. Long term use & disaster recovery.
Hopefully some experienced Dev/DBA people know where I'm coming from here. (Also if there's a good resource for reading I'm glad to take that as well). In answering, assume most datasets will range from 100k to 500k tops, with an occassional million record table.
Hi,
First of all as you must be aware the cloud works on the concept of multi-tennacy , hence in order to ensure equal services and preventing blockage of services by few ~ the salesforce is implements the concept of "Governor Limits" ~ to ward off what is called denial of service like situation.
Please refer to the following link for finding out in detail about governor limits and why they are imposed in salesforce:-
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm
Also salesforce works on pay as you use model.. hence there are different editions with different storage capacity w.r.t. data ~ one subscribes to more storage spaceas his/her need increases.
Hope this helps....
Ispita -- while I appreciate the response, actually no -- that doesn't help because it doesnt seem on topic at all. Yes I am aware of gov limits, storage, etc, which is why I noted our Enterprise version. Capacity is not our problem as our instance has plenty, our data record size is small (from a DB point of view), and can always be cost adjusted as necessary.
This is a design question -- I am trying to get a feel for how well SF will handle an object-relationship model (star schemas, 1-N-N, etc) which is what we have several time over in other systems, or if we need to flatten out the data model before it is pushed into Salesforce.
The data prep operations will happen outside the cloud; ideally I would like to push them up in the same model (hence lowering any need for various triggers/custom code on inserts, etc.). That also makes time-to-deliver quicker, code simpler, and consisitancy of facts. BUT if I do that and the App/Reports cant use related tables together well past 2 or 3 relationships, then it doesn't do me much good, isn't usable, and therefore shouldn't be how I proceed.
The limitation that is often encountered with Salesforce reporting is across unrelated siblings. E.g. if you have a parent object A with two child relationships, B and C, you wouldn't be able to report across these objects - a real world scenario would be the accounts parent object and opportunity and contact child objects. Multiple levels of children tends not to be a problem, for example you can report on Accounts with Contacts with Activities as standard, which is 1 -> Many -> Many in terms of the data model. Custom report types can go to four levels.
Flattening the data model tends to lead to a better user experience, imho, as users aren't usually keen on having to click through multiple child relationships to access the data they are interested in - the less degrees of separation the better.
Bob -- thank you; reply seems straight on topic. Let me expand further on the thought then: If it is a better practice in some cases to flatten out the model, does that come into play when it comes to number of fields? I'm looking for advantages/disadvantages in design still.. follow this example:
Suppose I have Opportunities with "Sales-related fields " (standard stuff). Now relationally that translates to 1:N actions in our environment. Each of those actions (and child, child-child, child child child....) have different fact columns. (cost, discount, time, whatever).... Let's say the # of facts is significant; 15 or more.
To flatten it out means that in my world I'll aggregate up the data and can attach that back to the opportunity (single row). Here's the rub: do I:
a. add 15 columns to the Opportunity that map to the data. Push up simple.
b. add a new 15column object called OpportunityFacts and relate that back to Opportunity (which would be one-to-one ALWAYS to Opportunity.)
Points to consider still: GUI, ease of use, permissions, reporting, etc. (perhaps the 15facts are say Executive level only facts... does that affect the decisions..)
The number of fields does come in to play for governor limits, in that there is a limit of 500 for Enterprise Edition and 800 for Unlimited Edition. My view on this is that I wouldn't normally create a relationship to a child object if it was a one to one relationship and was purely extending the parent object data, as it would (a) use up a custom object (which are limited, but I think its 200 so not that big an issue) and (b) separate the data, so that the user would have to scroll down to a related list to view that data. The flip side to that is that if you are having to do a lot of work to uplift the data into the opportunity, then the separation of data for the user may not be such a big deal.
If the data is rarely accessed then the users may not have a problem in clicking through to another level in order to view it - its something we reconsider for every customer rather than a one-size fits all. In terms of reporting, you'd be fine as long as you were following grandparent -> parent -> child type object graphs. I wouldn't see security as an issue - if you had master-detail fields available you could set the security to be controlled by the parent which makes it a non-issue.
Another way to approach this is to leave the data model as is and then build Visualforce pages that pull in the parent and child object information to a single page. The downside to this is that you'd have to maintain these pages along with everything else!
Agreed; I had this thought / approach possibility as well, but I discounted it as not particularly viable in our current capacity based on other human factors -- staffing, time-to-deliver, maintenance, etc..
I also agree, splitting a 1 row relationship into 2 1-to-1 objects isn't ideal as well; it provides seperation of data from an "easy to find" aspect, but then creates other issues with reporting wizards & custom report type, so on.. good feedback.
I think as this has moved along what we may end up with is a hybrid of BOTH -- the relational structure in a 1-to-1 fashion as well as flattened out fields attached to objects as well. It sounds more complicated to do both, but I'm starting to see it from three perspectives;
1. 1-to-1 mapping is cheap, easy, maintainable, and the resources are there to build off of with new custom objs, force pages, etc.
2. BIG ONE--(correct me if i'm wrong please)-- Having rollup fields (say serviceCalls-to-Opportunities) becomes very nice, but if they are conditional in nature then they don't work. Example if I do four "things" related to an Opportunity (2 Install, 2 Service call), and I want a field called TotalInstallRevenue, then that's only a rollup of the two "things", not all four. (Think complex WHERE clauses in SQL).
3. Users who need to both report/react quickly (as well as simple-to-use issues), can handle looking for what they need in about one or two places. Past that, it becomes harder to do on the fly, and then increases our custom support work. Ideally we want our users doing as much as they can themselves...
I very much appreciate your assistance.
1 and 3 are pretty much how I see things.
I'm not sure I quite understand point 2 though. You can define criteria for roll up summary fields - we've used that in a couple of places where we store actual, target type information in the same record but with different record types, then have 2x roll up summary fields - 1 for targets and 1 for actuals. This might be totally irrelevant depending on what you are actually asking though :)
Doesn't the rollup field though have a basic limitation though as to how complex it can be as to whether it's calculated? Assume some of the basic things that you come across as a developer --
1. IN Clause (where salestype in ('A', 'B', 'Z') [and therefore NOT IN ('C', 'D', ..)...
2. CASE clause -- IF something, something, something THEN (A) Else (B) End as Value...
Truthfully I wasn't aware of conditional rollup at all until your post, so that was a good takeaway. But from examining the page I see plenty of times where our complex logic won't fit neatly into a few simple AND statments that the GUI gives me. (If we could solve the IN Clause, that'd be a big one)...
I suspect as a designer I could add some custom Value fields (results of the complex calculations) to the child object that are filled in during data push up from our systems, and have the rollups run just off those field conditions. But if I'm going to do that work anyway, why not just push the rolled up value as a true column/value on the anyway (if the children don't have multiple uses).
You can use rollups on formula fields too, as long as the formula isn't calculated on the fly - based on the current date, for example. I seem to recall hitting issues with cases and rollups, but I think that the case was in the formula rather than the rollup definition.
I often end up performing calculations in apex triggers or workflow that is then used in the rollup, but you are right - if you can do that before loading the data that may save you some grief in terms of performance and scalability, Salesforce being a shared platform and all that.