When it comes to CRMs these days, Salesforce has maintained its industry leadership for quite some time. But its analytics and reporting capabilities surrounding the data it controls has always been a bit lacking. For creating cross cutting reports and integrating data in Sql Server, you’ve got a few options. One option is to bring Sql Server data into Salesforce, and then use their native reporting capabilities. Another option is to run reports and SOQL queries in Salesforce, and dump out of the data while temporarily enriching it with Sql Server data. The latter is typically the norm, and is automated with Composable.
But what if you need to do massive and complicated joins with your Salesforce data and with existing Sql Server databases?
How can you easily get your leads, contacts, accounts, and custom objects out of Salesforce and into your other databases?
Say Hello to a few Dataflows in Composable.
With Composable, you can automate the generation of SQL tables to store Salesforce data. And once these tables are created, you can then use Composable to automate the querying and insertion of the data.
For automating the generation of the SQL tables, we’ll be using 2 features in Composable (Forms and Dataflows). We’ll use a Dataflow to automatically generate a Form in Composable. And then the generated Form is used to store each Salesforce Object as a Form Container. A Composable Form is backed by an auto-generated Sql Server database.
The Salesforce Describe module is used in a Dataflow to find all the meta information about a particular Salesforce Object. This metadata is then piped into a code module, which maps all the field attributes to a Form container (see code below).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
using System; using System.Linq; using CompAnalytics.Contracts.Tables; using CompAnalytics.Contracts.Isolation; using System.Collections.Generic; namespace Test { public class TestClass { public Table TestMethod(Table table, string objectName) { ITableOperations ops = ExecutionOperations.Current.FindOperations<ITableOperations>(); Table ret = ops.CreateTable(new TableColumnCollection() { new TableColumn("Name", "VARCHAR"), new TableColumn("DisplayName", "VARCHAR"), new TableColumn("Description", "VARCHAR"), new TableColumn("Type", "VARCHAR"), new TableColumn("ControlType", "VARCHAR"), new TableColumn("StrMax", "INTEGER"), }); using(ITableWriter writer = ops.CreateTableWriter(ret)) using(ITableReader reader = ops.CreateTableReader(table)) { foreach(TableRow row in reader) { string name = (string)row["name"]; string label = (string)row["label"]; string type = (string)row["type"]; int length = Convert.ToInt32(row["length"]); string formType = "System.String"; string controlType = "Text"; int? strMax = null; if(type == "int") { formType = "System.Int32"; controlType = "Spin"; } if(type == "currency") { formType = "System.Double"; controlType = "Spin"; } if(type == "double") { formType = "System.Double"; controlType = "Spin"; } if(type == "boolean") { formType = "System.Boolean"; controlType = "CheckBox"; } if(type == "date") { formType = "System.DateTimeOffset"; controlType = "Date"; } if(type == "datetime") { formType = "System.DateTimeOffset"; controlType = "DateTime"; } if(type == "phone") { controlType = "Phone"; } if(formType == "System.String" && length > 0) { strMax = length; } if(name == "Id" || name == "ParentId" || name == "CreatedById") { name = objectName + name; } writer.AddRow(new List<object>() { name, label, "", formType, controlType, strMax }); } writer.Complete(); } return ret; } } } |
Now that we have the tables created in Sql Server, we can use another Dataflow to query Salesforce objects and insert the data into the tables.
A few interesting tidbits:
- Deleted objects are also included in the Salesforce results. There will be an IsDeleted column in the Sql table. Deletes will essentially act as updates (IsDeleted = true).
- It is not guaranteed that all values in a Salesforce field will have a length <= the length specified in the fields metadata. Why is this you ask? It is possible that the length is updated (shortened) after data has been inserted. Salesforce will not retroactively truncate the field. It will only truncate it on future updates. So when using the length as your max length in Sql Server, you may have to allow for truncation, or increase the size of the columns.
