Friday, 3 May 2013
Thursday, 14 February 2013
The company which provides these drivers is called Progress|DataDirect
Their JDBC/ODBC Salesforce Connect XE drivers, acts as translators between SQL and SOQL (The Salesforce proprietary query language). So you can write your joins, use expressions like SUBSTRING on your Salesforce data as if the data was in a relational database.
I found the concept quite interesting. If you already know SQL -and there are many people which do- you can just query data in any Salesforce standard objects like Account, Contact and custom objects with the use of a SQL Client tool.
For my post I used SQuireL SQL Client and the JDBC driver. You can easily point your own SQL Client tool to these drivers too. ODBC and JDBC are data access standards many tools comply with.
To get started
1. Download Progress|DataDirect Salesforce.com JDBC driver the file will be called something like this PROGRESS_DATADIRECT_CONNECT_JDBC_5.1.0.jar ( I have downloaded the 15 day trial version)
2. Install the driver as per instructions found here and more generic info like User Guide is here
3. Configure SQuireL SQL Client to use the driver (to install SQuireL go here)
Once you have downloaded the driver and you have installed it. Start the SQuireL Client tool and register the Salsforce.com JDBC driver with SQuireL like this:
Go to Drivers tab on the right and click the + sign.
Add a driver by using the plus sign (+) and fill in the driver details like below. You can find out the Class Name and the Website URLs, Extra Class Payt for the Progress|DataDirect Salesforce.com JDBC drivers here. You can find out more about connection properties here DataDirect Connect Series for JDBC User's Guide
Once you have created the alias (connection) is time to test your alias and connect to Salesforce with the new JDBC driver. Provide your Salesforce credentials like this:
Once you connect then you can issue a SQL join and write any SQL statement to your Salesforce Org as below. Below I am writing a SQL join between Account with Contact Salesforce standard objects.
To read more about the JDBC and other drivers go to the company site Datadirect.com
There is potential here, imagine all the data profiling, data quality operations and data integrations, you can do 'in place' in SaaS and Cloud systems with SQL, without having to move the data around a lot.
More blogs and resources on SQL Access to Salesforce can be found below. There is a very interesting blog post which shows you how you can access the Salesforce data from within Oracle directly here too.
Thursday, 7 February 2013
Saturday, 26 January 2013
Maybe the DELETE command should be banned from SQL. It was probably invented when the disks were tiny and it might not be suitable for our times, especially nowadays when we talk about things like BIG DATA.
But, one can argue that not deleting can be dangerous, too! One can claim that by refusing to delete we might end up with a lot of old, duplicated, bad quality, uncleaned, irrelevant and untrustworthy data.
Maybe the worse thing that can happen to data is to refuse to delete it enough. Perhaps because of this lack of enough deletes and fear of deletes we end up with all the 'bad data' found in some database systems today.
Whatever you choose to do, delete or not delete, one thing's for sure and that is that you should know what you are deleting or not deleting. Choosing confidently what to delete comes from understanding and knowing your data model, and the implications a delete will have on the data model and never because the data is not required for your project!
Sunday, 6 January 2013
Data is related with other data based on relations and associations. For example, as in Salesfoce's data model One Account can have Many Contacts. In the model these relationships/associations are indicated by drawing lines from one object to the others as seen in the diagram below, where the one side line is a single line whereas the many side of the line has multiple lines like crows feet.
Below is how this is shown in Schema Builder application of Salesforce
This is a standard way of showing relations between entities in databases and thus objects in Salesforce.
Walking through some of the Standard Objects and their relations to other objects in Salesforce
There are 2 dozen Standard objects in Salesforce, below I have drawn with Schema Builder few core standard objects of the core CRM implementation, I list them here:
How to read the Entity Relationship (ER) diagrams
- http://en.wikipedia.org/wiki/Entity-relationship_model - Wikipedia
- http://www.databasedesign.co.uk/bookdatabasesafirstcourse/chap3/chap3.htm - Database course
Salesforce done a great job with Schema Builder. To obtain a Salesforce free developer account and have a look at it and start creating objects and see relationships go to: http://developer.force.com/