Step Four: Create External Data Source and Define Relationships in Salesforce (4 of 4)

This post will be the last of a 4 part series in which I will step you through what is necessary to expose and access a multi-table SQL Server Azure database with built-in relationships. These steps were covered at a high-level in my talk , but in this series, I will go through each step in greater detail. The steps will consist of the following:

1.) Create a SQL Server Azure Database

2.) Create ASP.NET application to expose data as OData

3.) Publish Web Application to Windows Azure

4.) Create External Data Source and Define Relationships in Salesforce (covered in this final post – see links above for other posts in the series)

TIP: Click here to access the code for this post, along with the Powerpoint slides from my talk at Dreamforce 2015 (which is what this post series is based on). But, if you follow along with tutorial in this post, you will not need it since you will be generating the code for yourself.

Step 4: Create External Data Source and Define Relationships in Salesforce

In the last step, I walked you through how to publish your ASP.NET application to Windows Azure, which would expose the SQL Server Azure data as read-only OData. Now it is time to setup Salesforce to consume that data.

To begin you will need to login to Salesforce and create a new external data source by going to Setup and typing data source in the Quick Find box. Select External Data Sources and then click New External Data Source. Keep in mind that if you are using a Development org, you will be limited to creating only one external data source at a time.

Enter a label and name for your external data source and select Lightning Connect OData 2.0 as the type. The URL should be the one you created when you published your web application in step 3.

As of Winter 16, the parameters for creating new data sources has increased (see image below). You now have some additional checkboxes and one that specifically allows you to let users create, edit and delete data on the external data source. Prior to Winter 16, you could only access data one way. For the purposes of this walk through, we will be dealing with read-only data, so there is no need to check the Allow Create, Edit and Delete checkbox. I will be covering the topic of write access in an upcoming post.

CreateDataSourceOnce the data source has been saved, you will need to click Validate and Sync on the page that follows. You should also see a list of tables with checkboxes next to then. Select them all and click Sync. Depending on the size of your database, this may take a while to complete. When it is done, you should see a status of success.

SyncDatabaseThe database I am using for this tutorial only has 4 tables, so when the sync is done, I will see 4 new external objects – one that corresponds to each table. I can click any of the links and be brought straight the object definition page (see image below).

CustomersTableDefinitionYour external object definition will look very similar to the definition for any custom or standard sObject. Notice that in the image above, the API Name for this object is sarahasnolimits_Customers__x. The sarahasnolimits part is just a namespace prefix that my development org uses. If you are following along with your own org, you may have a different prefix or none at all.

You should also take note of the __x portion of the name. You will see this for all external objects and it is the main way of distinguishing an external object from a regular sObject.

Even though the metadata for our web service lists the relationships associated with our SQL Azure tables, these relationships are not automatically setup in Salesforce. You will have to define them yourselves by editing the definition for one of the fields in your external object.

But which field do you use?

If you are unsure which fields should be used to define these relationships, you can always look at the metadata for your service by using a browser, entering the url for your publicly exposed web service and adding $metadata to the end of the URL. Such as in the image below, you should see which field names are used to define the relationships between your tables.

MetadataRelationshipIn the example above, I can see that the field used to link Customers to Invoices in CustId. Therefore, I can return to the object definition in Salesforce and edit the field definition for the field that has an external alias named CustID. When I do, I should then click Change Field Type and select External Lookup Relationship as the new type (see image below).

ExternalLookupRelationshipExternal Lookup Relationships are used to link external objects together and in this case, the CustId field will be related to the Invoices object. I know this because that was the same relationship it had in the metadata for my web service. Note that you will need to specify a length for your relationship field. I am just going to select a length of 18 and click Save to complete adding the relationship.

I will have to add relationships for all of the relationships specified in my metadata (which in my case is 3): One for the FK_Invoice_ToCustomer association, one for the FK_InvItem_ToInvoice association, and one for the FK_InvItem_ToProduct association.

When I am all done creating the relationships, I will be able to query these objects just like I would any other standard or custom sObject. For example, the following query could be executed in the Query Editor of Developer Console:

Select s.sarahasnolimits__LastName__c, s.sarahasnolimits__FirstName__c, (Select sarahasnolimits__CustId__c, sarahasnolimits__InvDate__c, sarahasnolimits__Status__c, sarahasnolimits__TotalPrice__c From sarahasnolimits__Invoices__r) From sarahasnolimits__Customers__x s

The results from the query should look similar to what you see in the image below:

QueryResults

WARNING: If you go back to your External Data Source and re-sync the external objects for which you have defined relationships, they will be removed and restored to their original data types.

External objects work very much like custom objects and so you can create tabs, list views, and even complex Visualforce pages with them. What makes them so special is that unlike standard or custom objects, the data does not reside on Salesforce servers. It just looks like it does.

Step Three: Publish Web Application to Windows Azure (3 of 4)

This post will be part 3 of a 4 part series in which I will step you through what is necessary to expose and access a multi-table SQL Server Azure database with built-in relationships. These steps were covered at a high-level in my talk , but in this series, I will go through each step in greater detail. The steps will consist of the following:

1.) Create a SQL Server Azure Database

2.) Create ASP.NET application to expose data as OData

3.) Publish Web Application to Windows Azure (covered in this post)

4.) Create External Data Source and Define Relationships in Salesforce

TIP: Click here to access the code for this post, along with the Powerpoint slides from my talk at Dreamforce 2015 (which is what this post series is based on). But, if you follow along with tutorial in this post, you will not need it since you will be generating the code for yourself.

Step 3: Publish Web Application to Windows Azure

In Step 2, I walked you through creating an ASP.NET web application that exposed SQL Server Azure data as OData through a web service. As it is now, that web service will run only on your local machine. In order for Salesforce to be able to access the data exposed by the web service, it must be published to an external website.

Create Web App in Windows Azure

You can publish the web application easily to Windows Azure, but you will first need to create a website/ web app in the Windows Azure Management Portal. To do so, log in to Windows Azure and click on the Web Apps icon in the left menu bar. Click New (next to a big plus sign) in the bottom right corner of the web apps page. From the new menu, click Quick Create and enter a unique name that will be used in the URL for this website (see below) and click Create Web App.

AzureWebAppsIt should take a few minutes for the web app to be created. When it is done, it should show a status as Running. Click on the web app to go to the quick start page and from there, select the Download the publish profile link under Publish your app. Save the file it generates to you local machine.

Use the Publish Web Wizard

Visual Studio has a nice wizard that you can use for publishing applications to external sites. You can access it by right-clicking the project in Solution Explorer and selecting Publish. On the first page of the wizard, click Import and the browse to the location on your machine where you saved the publish profile.

The next page of the wizard should have all the settings entered for you as it got this information from the publish profile. Make sure you click the Validate Connection button (see image below) to verify that your connection is good.

PublishWizardOnce validated, click Publish and wait for the publish to complete. When it is done, you will see a message in the status bar of Visual Studio indicating it is finished.

TIP: Be prepared for the initial publish to take several minutes to complete. If you try to access the web app before it has finished, you will get errors telling you that the resource cannot be found.

You can then access the web service using a web browser. The URL should be something like the following:

http://<your website name>.azurewebsites.net/<your service name>

If successful, you should see the same XML results that were displayed when you browsed to the service on your local machine.

Query your OData in the Web Browser

Because your data has been published to the web as OData, you, or anyone else can access the data directly using any web browser. For example, if you wanted to see all the data in the customers table, you could add the entity name Customers (note that it is case sensitive) to the end of the URL and hit enter. If you are using a browser such as Google Chrome, this will display for you ALL the customer data in XML format.

Great, but what if you just wanted to see data for a certain customer?

To accomplish this, you can add query string parameters to the end of the URL, such as the following:

Customers?$filter=Addr1 eq ‘123 Main St’

The query string above can be used to return all data from the Customers entity where the Addr1 field is equal to ‘123 Main St’. For the PetSupplies data, this should return a result such as what you see below.

CustomersQueryResultOk, but what if you wanted to see the data in a nicer format than XML?

Well, luckily there is a wonderful free online tool called XOData, that can be used to explore publicly exposed OData. The tool is made by a company called PragmatiQa and to access xodata, go to the following URL:

http://pragmatiqa.com/xodata/

From there, click Choose Access Option and select Metadata URL. You can then enter the URL of your service, including the /$metadata at the end of the URL. So for the PetSupplies database, the URL would be:

http://petsuppliesrelational.azurewebsites.net/petsuppliesrelationaldata.svc/$metadata

Once entered, click Get Details and you should see results similar to the following:

pragmatiquaYou can then select the Data Explorer tab and be able to easily select, filter and order data from any of the exposed entities. The results will be displayed in a nice table format that is much easier to read than the XML you saw earlier (see image below).

CustomerQueryInXOData

Isn’t that beautiful? I really think the guys at PragmatiQa did a GREAT job with that tool.

And finally, stay tuned for the final post (in about a week), where I will show you how to access the oData in Salesforce by creating an External Data Source.

Step Two: Create ASP.NET Application to Expose Data as oData (2 of 4)

This post will be part 2 of a 4 part series in which I will step you through what is necessary to expose and access a multi-table SQL Server Azure database with built-in relationships. These steps were covered at a high-level in my talk , but in this series, I will go through each step in greater detail. The steps will consist of the following:

1.) Create a SQL Server Azure Database

2.) Create ASP.NET application to expose data as OData (covered in this post)

3.) Publish Web Application to Windows Azure

4.) Create External Data Source and Define Relationships in Salesforce

TIP: Click here to access the code for this post, along with the Powerpoint slides from my talk at Dreamforce 2015 (which is what this post series is based on). But, if you follow along with tutorial in this post, you will not need it since you will be generating the code for yourself.

Step Two: Create ASP.NET application to expose data as OData

I will be walking you through creating an ASP.NET application that utilizes a WCF (Windows Communication Foundation) data service, along with the WCF Data Services Entity Framework Provider to render the data created in Step One as an OData endpoint.

You will need a copy of Visual Studio to complete this next step. In this tutorial, I will be using Visual Studio 2015 Community version, which you can download for free by going to this link and clicking the Download button under Visual Studio Community.

Once you have, open it up and create an ASP.NET Web Application using the Visual C# empty template. Name the project PetSuppliesRelationalSvc and save it somewhere on your local drive (see image below).

VSCreateProject

Add the Entity Data Model

The first thing you will need to do is to add the Entity Data Model to your project by right-clicking the project in Solution Explorer and selecting Add | New Item. Select the Data Node in the left pane and from there select ADO.NET Entity Data Model. You can name the model PetSuppliesRelationalModel and click Add to continue (see image below).

VSAddADONET

A wizard will start and first ask you to choose the model contents. Since we are working with an existing database, you will want to select the default option of EF Designer from Database and click Next to continue. On the Choose Your Data Connection page, click New Connection. This is where you will enter the server name for your SQL Server Azure Database server, which should have been assigned when you created the database back in Step One.

If you do not know the name of the server, you will need to log back in to your Microsoft Azure account and go to the Databases quick start page. It should be listed at the bottom of this page and will look something like the following:

holla9999m.database.windows.net,1433

This entire string (including the comma and 1433 port assignment) should be copied into the server name textbox. You should also select the Use SQL Server Authentication radio button and enter the credentials you used to create the SQL Server Azure database. If the credentials are correct and your local IP Address has been configured to have access to the Azure firewall, you should be able to select PetSupplies as the Database (see image below) and when you click Test Connection, you should be get back a Connection successful message.

VSNewConnection

Once the credentials are entered, click OK to return back to the Choose Your Data Connection Wizard page. Since this is just a tutorial, you can go ahead and click “Yes, include the sensitive data in the connection string“, but if this was a real-world application, you may want to consider something more secure.

Click Next to continue and on the page where you choose your version of Entity Framework, leave the default selection as Entity Framework 6.x and click Next. On the Choose Your Database Objects and Settings page, select the checkbox next to Tables and enter PetSuppliesModel as the Model Namespace. Click Finish to complete the wizard.

VSChooseDatabaseObjects

You might see a Security Warning, but you can go ahead and click OK to run the template. It may take a few seconds for the wizard to build out all your code, but when it is done, you should see a schema diagram of the PetSupplies database. All the code that is needed to access the database has been generated for you. Wasn’t that easy?

Add the WCF Data Services Entity Framework Provider

To get your SQL Server data into the OData format, you will need to install the WCF Data Services Entity Framework Provider (which as of this posting is still in beta). This is done by going to Tools | NuGet Package Manager | Manage NuGet Packages for Solution and clicking the Include Prerelease dropdownbox. You can then search for “WCF Data Services EntityFramework Provider” and once you find an entry for Microsoft.OData.EntityFrameworkProvider, click the Install button next to it (see image below).

VSInstallWCFEntityFramework

You should get a preview box that lists all the changes that will be made to your project. Click OK and also click I Accept to accept the license agreement.

Add the WCF Data Service

You are now ready to add the WCF Data Service that will be used to expose your OData. To do this, right-click the project and click Add | New Item. This time, select the Web node in the left pane and select WCF Data Service 5.6.4 as the Item template. Name the service PetSuppliesRelationalData.svc and click Add (see image below).

VSAddDataService

The code that is added to your project should contain TODO comments. The first change you will need to make is to add the data source class name where indicated in the class declaration. If you followed the naming I suggested in the tutorial, this should be PetSuppliesEntities.

You will also need to change the type declaration from DataService to EntityFrameworkDataService. When you do this, you should get a red squiggly line under the code, indicating that the type could not be found. To correct this error, you will need to hover over the line of code (see image below) and click Show Potential Fixes. You can then select the line that says, “Using System.Data.Service.Providers” to add the reference and correct the problem.

VSCorrectError

I suggest that you add a line of code above the public class declaration that will apply the ServiceBehaviorAttribute and set the IncludeExceptionDetailInFaults property to a Boolean value of true. This will instruct the service to return unhandled exceptions as SOAP faults and will be helpful for debugging purposes. When you are done, the code should look like the following:

[System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class PetSuppliesRelationalData : EntityFrameworkDataService

TIP: Make sure to set the value of the IncludeExceptionDetailInFaults property back to the default of false before deploying your solution to production.

The next TODO involves setting the rules to indicate which entity sets and service operations are visible or updatable. Since Lightning Connect only allows for read-only connections at this time (although I will be covering writable connections in later posts so stay tuned for that) the changes we will make will only involve un-commenting the line of code that sets the entity set access rule. We will then replace the word “MyEntityset” with “*” and leave the rights as AllRead. This tells the service that we want to expose all the entities as read-only.

After all the code changes are made, the PetSuppliesRelationalData.svc.cs file should look like the following:

using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Data.Services.Providers;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;namespace PetSuppliesRelationalSvc
{
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class PetSuppliesRelationalData : EntityFrameworkDataService {
         // This method is called only once to initialize service-wide policies.
         public static void InitializeService(DataServiceConfiguration config)
         {
            // TODO: set rules to indicate which entity sets and service operations...
            // Examples:
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation",
                     ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = 
                     DataServiceProtocolVersion.V3;
          }
     }
}

And that is about it. The only thing left to do is to test that everything is working right. But before you do that, be sure and save all your changes.

You can then right-click the PetSuppliesRelationalData.svc file in Solution Explorer and select View in Browser. When you do this, a browser window will open and load up your data service, listing all the entities exposed in XML format. The result should look similar to the image below. If you do not see this or see an error, then go back to see where you might have made a mistake and try again.

WebServiceLocalXML

Final Words

I know this post may seem long and the steps complicated, but it really is a pretty straightforward process with 99.9% of the code being built for you. The longest part of this should be downloading and installing the latest version of Visual Studio.

Click here to see the third post in this four part series.

Step One: Create a SQL Server Azure Database (Post 1 of a 4 part series)

At this years annual Dreamforce conference, I had the honor of speaking about SQL Server Azure Database MeSpeaking2relationships using Lightning Connect. The talk was based on an article I wrote for DeveloperForce earlier this year titled, “Accessing a SQL Server Azure Database using Lightning Connect“. The original DeveloperForce article detailed the steps for creating a WCF Data service that exposed a single table SQL Server Azure database as OData.

This post will be the beginning of a 4 part series in which I will step you through what is necessary to expose and access a multi-table SQL Server Azure database with built-in relationships. These steps were covered at a high-level in my talk , but in this series, I will go through each step in greater detail. The steps will consist of the following:

1.) Create a SQL Server Azure Database (covered in this post)

2.) Create ASP.NET application to expose data as OData

3.) Publish Web Application to Windows Azure

4.) Create External Data Source and Define Relationships in Salesforce

TIP: Click here to access the code from this post, along with the Powerpoint slides from my talk at Dreamforce.

Step One: Create a SQL Server Azure Database

You will need a Microsoft Azure account to create a SQL Server Azure database, but you can sign-up for a free one-month trial through the following link. You will also need a way to build the database. If you do not have access to Microsoft SQL Server Management Studio, then you can use the built-in SQL Server tools that come with the free Community version of Visual Studio.

But the first step is just to create the database and the simplest way to do that is to go to the SQL Databases tab in Windows Azure Portal and click the big plus sign next the word New in the bottom right-hand corner of the Portal. Click Custom Create and enter a Database name along with a secure login name and password.

After the database server has been allocated, you can click here to download the latest version of Visual Studio, 2015. Keep in mind that it will take quite a long time to download and install. If you don’t want to wait, you can use an earlier version of Visual Studio, such as Visual Studio 2012 or 2013 (with latest update), but just NOT Visual Studio 2010.

Once installed, you can access it from the Windows Azure Portal by clicking the Open in Visual Studio link at the bottom of the database quick start page (see image below).

PetSuppliesPortal

When you click this link, you should be prompted to add your local IP address to the Windows Azure firewall rules. Go ahead and click “Yes”.

One thing to be aware of here is that unless you have a static IP address, you will need to do this every time the address changes (which for some DHCP environments could be daily). This is done by returning to the quick start page and clicking the Set up Windows Azure firewall rules for this IP address link under Design your SQL Database.

TIP: If you know the allowable range of IP addresses you might be assigned, you can configure a range of IP addresses in the Azure Management Portal by going to SQL Databases and selecting the link for the server that your database resides on. From there, select the Configure tab to enter a new rule with starting and ending IP address.

If you are using a browser such as Chrome, you may also be prompted with an external protocol request asking whether you want to launch the application. Go ahead and click Launch Application. Doing so will launch the Visual Studio application and open up SQL Server Object Explorer. The first time you connect, you should be prompted to enter the login name and password you specified when creating the database in Windows Azure Portal.

Once connected, you can use the SQL Server Object Explorer to add tables and data to your new SQL Azure database. If you prefer the point and click approach, you could create a new table for your database by expanding the Databases node, followed by your specific database and then right-clicking Tables and clicking Add New Table (see image below).

SQLObjectExplorerNewTable

If you are more comfortable working with SQL script, you could right-click on your database node and select New Query to bring up a window where you can type or paste in SQL script directly. Once entered, the script can be executed against your SQL Server Azure instance by clicking SQL | Execute.

Click here to see the second post in this four part series.

Come see me talk about SQL Server and Lightning Connect at Dreamforce 2015

LightningI am happy to say that I will be presenting again at this years annual Dreamforce, Wednesday September 16, 2015 at 9:15am. The session is titled, “Exploring SQL Server Azure Database Relationships Using Lightning Connect, and I should be in the Mobile Theatre in Moscone West.

Lightning Connect is what I consider to be a game changer in the world of Integration. It allows you to easily (and the key word here is easily) setup a real-time connection between your external data and Salesforce.

After setting it up, you can query external data from within Salesforce just like you would any other sObject. No exhausting setup of some overly complicated publishing/subscriber model (which is something I myself have unfortunately been involved with). No, you just publish your data as oData, setup an external data source in Salesforce, define some external relationships, and off you go.

Now, as with any new technology like this, there are a few gotchas to be considered. I will be covering some of these, as they pertain to setting up a connection to communicate with a SQL Server Relational database hosted on Windows Azure. The key takeaways I hope to communicate during this session will be:

  • What you need to know to work with Visual Studio and SQL Server Azure
  • How quick and easy it is to setup a connection and query data one-way in real time
  • What you need to know to define relationships in Salesforce

Hope to see you there!

Gotchas encountered when working with Lightning Connect and SQL Server Azure Databases

ILightning recently published my first article for DeveloperForce about Accessing a SQL Server Azure Database with Lightning Connect. I had a lot of fun creating the database and application, but I did encounter several gotchas as I was working with this fairly new technology. This posting lists some of the issues I uncovered while working with SQL Server Azure and what you can do to avoid the problems I encountered.

  • Adding firewall rules
    • DSL will likely change your IP address, so you will have to set this everyday most likely (unless you are fortunate enough to have a static IP address). You do this through the Set up Windows Azure firewall rules for this IP address link on the Quick Start page.
    • Connection may get lost occasionally, even though your IP address has not changed. If you start getting weird problems trying to publish, you can go back to Azure portal and click the Setup Windows Azure Firewall link again. Even though it is already added, doing this somehow refreshes the connection and will resolve your problems.
  • Connection String user needs to use an unrestricted DB user that has access to the Master DB. Refer to this article for more information http://www.odata.org/blog/got-sql-azure-then-youve-got-odata/. Real-world applications exposing their sensitive data via oData will need to take this into consideration and try to use a more restricted user.
  • If you try to use the latest version of the Entity Framework with web services, you will have to install some additional NuGet packages as outlined in the article. See article for more about specifics on that.
  • Depending on your internet connection, the initial publish may take a long time to complete and if you try to access the hosted website before then, you will get errors (probably that the resource cannot be found). Make sure the output in Visual Studio shows that the publish was complete and with no errors (such as a timeout error, which happened to me several times) before attempting to see the results online. If successful, a browser window should be spawned and you will be directed to the home page of your new hosted website.
  • Unfortunatley, Lightning Connect does not yet (and notice the hopeful word yet) automatically create relationships when you create your external data source. You will have to manually create these yourselves. It gets even more complicated in that you MUST edit the existing synced fields to create these relationships and DO NOT create new fields instead. If you do create new fields then you will encounter errors such as the ones I got and reported about here.  The error had to do with how I defined the external lookup relationship. I went to the external object definition page and clicked New to create a new field and then define the relationship. In order to avoid the error, you will need to instead edit one of the existing SYNCED fields and specifically Change the Field Type so that it is of the External Lookup Relationship type and that it points to the correct external table object. Then, you can perform your SOQL query and get no errors.

Let me know about your experiences with Lightning Connect. Despite all the above issues, I think it is a FANTASTIC new development for integration and I am excited to see it develop even further.