Wednesday, June 24, 2015

Salesforce Analytics Cloud Explained

Salesforce made a splash at Dreamforce ‘14 with Wave and there has been a lot of excitement on topic since then. Couple frequent releases of new Analytics Cloud innovations with limited access to a developer environment means confusion still persists.


Analytics Cloud vs. Wave

A good place to start is the difference between Analytics Cloud and Wave. While many times, these terms have been used interchangeable, there are key differences. Wave is the platform while Analytics cloud is the product. A good analogy to draw is Sales Cloud and Service Cloud are products that run on the Salesforce 1 Platform. So let’s take a look at the features of each.



As the underlying technology platform, Wave is a schema free, non-relational database. Consisting of columnar based key-value pairs, Wave relies on search based technologies and inverted indexes to quickly deliver results. While that’s a lot of big data buzz words, what it means is Wave it not like your traditional data warehouse. It is built to handle large volumes of structured and unstructured data to quickly provide results based on search queries. If you would like to learn more, take a look at Redis, which a data structure server that will help you better understand Wave.


Analytics Cloud

As the flagship product on Wave, Salesforce Analytics Cloud is a mobile-first solution that allows data to be sourced from multiple locations, manipulated in real-time, and shared with other users. The key elements the Analytics Cloud delivers are the following:

  • Explore – allows everyone, not just data analysts, to find answers in data
  • Collaborate – one place for users to get and share answers on the business
  • Cloud – as a native cloud solution, it is up and running quickly, easily scales and one source of the truth
  • Mobile – all the answers you need in the palm of your hand


Learning More

To learn more about the Salesforce Analytics Cloud, check out the following resources. As part of a recent Lehigh Valley Salesforce Developer User Group, I explained the Salesforce Analytics Cloud, including a demonstration of the technologies. The presentation can be found on Slide Share while a recording  of the session is available on YouTube.

Monday, June 15, 2015

Setting Up Lightning Connect in Salesforce

Salesforce Lightning Connect
Now it's time for the fun part of trying to get Microsoft Dynamics AX 2012 data into Salesforce.  My previous post covered the details of setting up the OData Service In Dyanmics AX.  With an external, https URL to our Dynamics demo environment in hand, it only takes a couple of clicks, and no code, to setup the connection.

External Data Source

First an external data source needs to be setup in Salesforce.  This is done from Setup with the following steps.  If you would like to learn more on Lightning, check out the Lightning Webinar Series.
  • Go to Setup | Develop | External Data Sources and click New Data Source.
  • Provide a label and a name.  I used DynamicsAX for both values
  • From Type, select Lightning Connect: OData 2.0
    • Take note of the 2.0, as this is the minimum OData version that Lightning Connect supports
    • There may additional options, especially if in a Summer '15 or higher org, which introduced the ability to create Lightning Connect Custom Adapters
  • After selecting this option, additional Parameters are available
    • URL - the Dynamics OData Source in the form of https://<host>.<domain>:8101/DynamicsAx/Services/ODataQueryService/
    • A series of options, which the default values are okay
    • Format - our source is AtomPub
    • Special Compatibility - Default is appropriate
  • Authentication details for our source must be specified
    • Identity Type - Name Principal to specify a static username/password to Dynamics AX
    • Authentication Protocol - Password Authentication
      • Supply the Username and Password to use to connect
      • As we are providing credentials, our URL must be https
  • After Saving, there is an option to Validate and Sync
    • Salesforce has made quite a few improvements between Spring '15 and Summer '15 in this area. 
      • When testing in Spring '15, I had cases where no error messages were displayed or only generic messages.  
      • After the Summer upgrade, I saw more consistent messages with additional details to help troubleshoot.
    • Ensure you have a valid SSL certificate, or you will not be able to validate and sync
  • A list of OData tables is presented to sync.  In my case, I selected ListofUSStates.  This is a simple listing of the US States and Names used in Dynamics. 

External Objects

    Now, the selected External Objects can be modified.  In order to make the object more useful within Salesforce, a few properties can be adjusted.  A meaningful label, like US State and plural version can be specified.  I kept the other options at default values until I proved out the concept.

    Custom Tab

    The only task standing in the way of viewing the Dynamics AX data is creating a custom tab to view the list of records.  Go to Setup | Create | Tabs and click New.  From the Object tab, select US States, pick a Tab Style.  Select the Profiles to show the tab in as well as the app. Save the new tab and its time to see our data.


    Now navigate to the newly created tab.  Then, simply click Go next to the All view and voila.....wait a minute what is this error message?
    Here is another case where I received more detailed error messages with Summer ‘15.  Spring ‘15 simply told me there was an error and to contact my admin.  The DataServiceProtocolVersion of v1 does not look promising, time for some more research.

    We Have a Problem

    First stop, was to check out the spec.  DataServiceVersion is defined in the Service Metadata Document and indicates the version of OData used.  So now to inspect the metadata for our Dynamics AX source.  This can be done by adding /$metadata to the end of our base URL.
    Hmm…DataServiceVersion = “1.0”, which does not look good.  Let’s try a few of the OData URI Conventions that should be supported like $select to specify columns to return. This can be done by adding ?$select=<column> name to the end of the ListOfUSState URL - https://<host>.<domain>:8101/DynamicsAx/Services/ODataQueryService/ListofUSStates?$select=LogisticsAddressState_1_Name.  Unfortunately, we run into the same error as before.


    At this point, I am reasonably certain Dynamics AX 2012 is OData version 1.0 and Lightning Connect requires 2.0.  Just to leave no stone uncovered, time to call in the big guns and verify with Salesforce Product Management.  After taking a look at my setup, they reached the same conclusion that Dynamics AX 2012’s OData 1.0 version was not supported.

    What I did learn from the Product Management team is that Summer ‘15 loosened some of the restrictions on OData 1.0 implementations.  Rather than immediately excluding the service based on the version, Lightning Connect will attempt to use the service. Only if the service does not support the necessary conventions, like $select, will it fail.  This can be useful in cases where DataServiceVersion is not specified, as the OData spec indicates that version 1.0 should be assumed.

    While I did not end up with Dynamics data available in Salesforce via Lightning Connect, I did learn a lot along the way.  Once you have an OData source available, setup in Salesforce is fast and easy.  I am looking forward to the next attempt to use Lightning Connect.  Besides, there are a lot of other ways to get Dynamics data into Salesforce.

    Monday, June 8, 2015

    Dynamics AX Setup for OData Integration with Salesforce

    Microsoft Dynamics AX
    In an attempt to access Microsoft Dynamics AX 2012 data in Salesforce through Lightning Connect, much of my effort went towards getting the OData service in AX setup correctly.  Here is an overview of my steps to get the environment up and running.

    Environment Setup

    Obtaining a Dynamic AX demo environment was easy.  With Microsoft Lifecycle Services, it was just a few steps to be up and running.
    1. Log into Life Cycle Services
    2. Create a project
    3. Connect to an Azure subscription (I used a 30 day free trial)
    4. Deploy an Dynamics AX demo environment on Azure
    5. Log in via Remote Desktop and open the Dynamic AX Client
    The AX demo environment is setup with a demo organization, including out of the box setting, data, etc.and is ready to go.

    DYNAMICS AX and ODATA Service

    With Dyanmics AX, OData service is available out of the box with access to the queries setup in AX.  It is accessible via http://<host>.<domain>:8101/.   This provides a list of the available queries that can be called, by adding the name to the end of the URL.  For example, get a list of all US states via https://<host>.<domain>:8101/DynamicsAX/Services/ODataQueryService/ListofUSStates.

    Dynamics AX OData Query Service Tables

    You are required to authenticate to the service first to ensure you are authorized to access the specified resources.  Now the astute reader may notice the issue we will have with Salesforce.  As we are authenticating to the AX OData Service, we must specify a secure URL when setting up our External Data Source within Salesforce

    ENABLING HTTPS for Dynamics AX Odata

    As you will see the actual changes needed to enable HTTPS are not that extensive.  However, I could not find a clear set of instructions, so good old fashion research and trial and error lead to to the following setup tasks.

    First some background, the AX OData Services is a Windows Communication Foundation (WCF) app that runs as a service - the Ax32Serv.exe.  While some WCF apps run inside of  Internet Information Services (IIS), this one is stand alone.  As such, the setup is not done inside of IIS.

    SSL Certificate

    A SSL certificate is needed in order to communicate on HTTPS.  If you have a certificate that matches the final domain you will deploy the OData Service to, use it now.  If not, you can create a self-signed certificate.  There instructions are for IIS7 and a quick web search will bring up other options.  When attempting to configure Lightning Connect in Salesforce, a self-signed certificate will not be trusted and the connection will not work, but it gets us through the first step.

    Port Binding

    Once you have your SSL cert, we need to configure port 8101 to use it.  Full details on how to do this can be found in this article - Configure a Port with an SSL Certificate.  The summary is:

    • Check the port configuration from command line 
      • netsh http show sslcert ipport=
      • No results indicates nothing is bound 
    • Get the SSL certificate's thumbprint from MMC
    • Bind the SSL cert to the port from command line
      • netsh http add sslcert ipport= certhash=<tumbprint> appid={00112233-4455-6677-8899-AABBCCDDEEFF}
    • Use the check command again to verify the bound cert

    Setup URL Reservation

    A reservation is needed to allow the WCF service to listen on the assigned portion of the URL.  Full details can be found in this article - Configuring HTTP and HTTPS.  The summary is:
    • Check the existing reservation.  There is likely a HTTP reservation already in place
      • netsh http show urlacl url=https://+:8101/DynamicsAx/Services/
      • Be sure to include the trailing /
      • No results means there is not a reservations.  
    • Delete any existing reservation
      • netsh http delete urlacl url=https://+:8101/DynamicsAx/Services/
    • Add a new reservation with HTTPS
      • netsh http add urlacl url=https://+:8101/DynamicsAx/Services/ user=CONTOSO\admin
        • Be sure to change the user to the user that runs the service
    • Use the check command to verify the reservation

    Ax32Serv.exe Config File Changes

    Now it is time to update the config file for the AX Service, which handles the WCF OData service.  The Ax32Serv.exe.config file can be found in C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin (change to your install drive).  You may want to create a backup before you change.  The Service Endpoint, Service Behavior and Binding Method all need to be changed.

    Service Endpoint
    • Find the <services> section and look for the service name="Microsoft.Dynamics.AX.Framework.Services.ODataQueryService.ODataQueryService"
    • Update the address of the endpoint to be https instead of http
    Service Behavior
    • The service definition references a behaviorConfiguration="ODataQueryServiceBehavior"
    • Locate this behavior name in the <behaviors> section
    • Change httpGetEnabled to httpsGetEnabled
    • Change httpGetURL to httpsGetURL and modify the address to be https instead of http
    Binding Method
    • The service definition references a binding="webHttpBinding" and binding configuration="ODataQueryServiceBinding"
    • Location this binding in the <bindings> section
    • Change the mode in <security> from "TransportCredenialOnly" to "Transport"
    • Change the clientCredentialType from "Windows" to "Basic"
    • I removed the proxyCredentialType="Windows" attribute as well
    My updated config file:

    Updated Config File with OData on HTTPS

    Save your updated config file and restart the Microsoft Dynamics AX Object Server 6.3 service from Services in MMC.

    Firewall and Azure Endpoint Management 

    Although I did not run into an restrictions with Windows Firewall in my Dynamics AX demo environment, keep in mind that access to port 8101 will be needed for this solution to work.  As Dynamics AX is deployed to a virtual machine within Azure, you need to setup an endpoint for the VM to communicate with the rest of the world.  You can review all the details of Setting Up Endpoints for a Virtual Machine.  Essentially, you need to go to Virtual Machines in Azure Management Portal, click Endpoints, Add a new endpoint, and link a public port of 8101 to the private port of 8101 with the TCP protocol.

    Verifying Locally

    You can now browse locally to the OData service on HTTPS - https://localhost:8101/DynamicsAx/Services/ODataQueryService/  You will likely receive a certificate error if using a self-signed cert and be prompted for a login.  You will see a list of "tables" that you can then query for results.  

    You can also verify by pulling the data into Excel on your own machine.  You will need to use the URL from Azure - 
    https://<instance> Again, you will need to click through the certificate error and supply credentials.  You can also add the self-signed certificate to your local trusted certificate authority certificate store.

    Open a new Excel workbook, go to Data -> From Other Sources -> From OData Data Feed.  Supply the URL, credentials and click next.  Pick your table, I used ListofUSStates to keep it simple.  Finish to save the data source, select a location in the sheet and your Dynamics AX data is now in Excel.  Here is an example list of states from Dynamics pulled in Excel.

    Microsoft Dynamic AX Data in Excel through OData Query

    Lightning Connect Preparation

    In order to setup the OData Connection in Salesforce, we need to have a trusted certificate.  As we supply credentials to connect to Dynamics, the URL must be HTTPS, with a valid cert.  Since we do not own * we cannot purchase a certificate for it.  Instead, we must setup a custom domain with Azure Cloud Services and secure an application in Azure Cloud Services. There are two knowledge base entries with the details of each of these steps - Associating a custom domain and securing communication with Microsoft Azure.  Use a custom domain that you own and have a valid SSL certificate for.

    Now with this final, external URL and valid SSL certificate, we are ready to try and connect from Salesforce, which I will discuss in another post.  To add a little foreshadowing, things did not go as planned.