Your CRM contains so much vital information for the health or your organisation. It’s your heartbeat, where if the data within your CRM is of good quality and the database is growing in size then your organisation is likely in good shape.
A CRM like Salesforce or SugarCRM will also contain sales opportunities against your CRM contacts. These sales opportunities will be defined by certain attributes i.e. sales person, region, and product, which allow you to report on your pipeline. These pipeline management tools though are designed to only report your pipeline today without any historical values. If you want to see how opportunity records have changed over time then you would need to extract the data into a data warehouse in order to track the slowly changing dimensionality. The revenue $, sales stage and confidence % are also vital measures in your pipeline but the system will lack the ability to do any What If forecasting. You might for example want to do What If models around:
- Changing confidence % against certain large opportunities.
- Delaying close dates of opportunities into next quarter
- Changing sales stages of opportunities to model overall sale stage forecasts in the future
TM1 (IBM Planning Analytics) provides the perfect engine for doing this type of What If modelling but how do you go about integrating TM1 with a CRM like Salesforce or SugarCRM which is cloud based? Most Software as a Service applications have an API (Application Programming Interface) which allows you to authenticate to your account and then issue queries and receive results. To enable TM1 to talk to the CRM via API this can be done via Java. Essentially a TM1 user will execute a Turbo Integrator Process that passes parameters into a Java .jar file. This jar file is compiled code that then handles the authentication to the API, the sending of the API query, the retrieval of the data in JSON format and then loads data into TM1 via the Java TI command codes.
To setup your environment all you need to do is to place the .jar file in your DataDirectory\} javaextensions\user folder and then set the JavaClassPath parameter in your tm1s.cfg file
To call the .jar file you then create a TI process and use the ExecuteJavaN command passing in the parameters of the Java class to call and the logon details.
N.B The password is blanked out in the screenshot below:
The table below shows the Java class “salesforceopportunity” accepting the parameters of the Salesforce logon and then calling another java class “SFEngine”. It will also do the TM1 cube and dimension builds but first the “SFEngine” class will do the Salesforce magic.
You can retrieve data from Salesforce API using SOAP and so this function handles the creation of the Salesforce SOAP query build, execution and clean up. Once this class has finished it passes the clean result set back to the Java class that handles the TM1 load.
Java easily handles the creation of cubes, dimensions and elements via the same commands you are used to in TI including CellPutS and CellPutN.
The result is a two dimensional cube that can be refreshed as often as you like. Now that you have this salesforce dataset in TM1, you can create other cubes to model your Opportunities and do What If scenarios.
Tridant has also written the same capability for SugarCRM and is happy to write other connectors if you would like one for your SaaS system.
Tridant is planning on making this Java code freely available on Github. If you would like a copy then please email email@example.com or contact your Tridant Client Executive.
This is just one of the articles published in our quarterly magazine, ‘The Dashboard’. If you would like to see more then please subscribe to our mailing list here.