2015-01-30

Simpro & SSIS Part 3: Loop the entity

This is the final post regarding Simpro and SSIS.

In previous posts, I had outlined how to access Simpro API via OAuth, and how to use SSIS Script component to extract Employee entity information through the Simpro API.

In this post, I want to discuss the actual performance issue I encountered when calling Simpro API.

In my project, what I need to do is to merge Simpro data with dialler’s data. So it is important to find some timestamps to indicate record creation and update datetime. However because of numeric reasons (cannot speak too much about it), I could not find an efficient way to get records I need, i.e. updated records and newly created records.

So what I did is like below



The important part is at the script component , where I wrote a loop to retrieve all quotes from Simpro, and then map the result to my existing Simpro_Quote table (the lookup component).

 public override void CreateNewOutputRows()  
   {     
     string consumerKey = "Your Consumer Key";  
     string consumerSecret = "Your Secret";  
     string url = @"http://URL/api/?format=json";  
     int Limit = 200;  
     int Offset = 0;  
     bool Continue = true;  
     while (Continue)  
     {  
       string JasonBuffer = GetJasonString(consumerKey, consumerSecret, url, Limit, Offset);  
       JObject jObject = JObject.Parse(JasonBuffer);  
       JArray Results = (JArray)jObject["result"];  
       List<int> lst = new List<int>();  
       foreach (JObject Result in Results)  
       {  
         JObject joCustomer = (JObject)Result["Customer"];  
         int QuoteID = 0;  
         bool Succeed = false;  
         Succeed = int.TryParse((string)Result["QuoteID"], out QuoteID);  
         Continue = QuoteID == 3000001 ? false : Continue;  
         foreach (JObject joSection in Result["Sections"])  
         {  
           int SectionID = 0;  
           Succeed = int.TryParse((string)joSection["SectionID"], out SectionID);  
           foreach (JObject joDepartment in joSection["Departments"])  
           {  
             int DepartmentID = 0;  
             int CostCentreID = 0;  
             Succeed = int.TryParse((string)joDepartment["DepartmentID"], out DepartmentID);  
             Succeed = int.TryParse((string)joDepartment["CostCentreID"], out CostCentreID);  
             foreach (JObject joItem in joDepartment["Items"])  
             {  
               int ItemID = 0;  
               Succeed = int.TryParse((string)joItem["ItemID"], out ItemID);  
               ItemBuffer.AddRow();  
               ItemBuffer.ItemID = ItemID;  
               ItemBuffer.DepartmentID = DepartmentID;  
               ItemBuffer.SectionID = SectionID;  
               ItemBuffer.QuoteID = QuoteID;  
               ItemBuffer.CostCentreID = CostCentreID;  
             }  
           }  
         }  
       }  
       Offset = Offset + Limit;  
     }  
   }  
   //base method to search quotes by loop  
   public string GetJasonString(string ConSumerKey, String ConsumerSecret, string Url, int Limit, int OffSet)  
   {  
     string JasonBuffer = "";  
     //build signiture  
     OAuthBase oAuth = new OAuthBase();  
     string nonce = oAuth.GenerateNonce();  
     string timeStamp = oAuth.GenerateTimeStamp();  
     string normalisedUrl;  
     string normalisedRequestParams;  
     string signature = oAuth.GenerateSignature(new Uri(Url), ConSumerKey, ConsumerSecret, null, null, "POST", timeStamp, nonce, out normalisedUrl, out normalisedRequestParams);  
     signature = HttpUtility.UrlEncode(signature);  
     //parameter  
     StringBuilder sb = new StringBuilder("");  
     sb.AppendFormat("OAuth oauth_version=\"1.0\",");  
     sb.AppendFormat("oauth_nonce={0},", nonce);  
     sb.AppendFormat("oauth_timestamp={0},", timeStamp);  
     sb.AppendFormat("oauth_consumer_key={0},", ConSumerKey);  
     sb.AppendFormat("oauth_signature_method=\"HMAC-SHA1\",");  
     sb.AppendFormat("oauth_signature={0}", signature);  
     WebClient Client = new WebClient();  
     Client.Headers.Add("Authorization", sb.ToString());  
     Client.Headers.Add("Content-Type", "application/json");  
     Client.Headers.Add("Accept", "application/json");  
     Client.Proxy = null;  
     JavaScriptSerializer JSS = new JavaScriptSerializer();  
     Dictionary<string, object> Params = new Dictionary<string, object>();  
     Params.Add("CompanyID", 0);  
     Params.Add("Limit", Limit);  
     Params.Add("Offset", OffSet);  
     Params.Add("SortAsc", false);  
     string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "QuoteSearch"},  
         {"params", Params}  
       }  
         );  
     byte[] bytes = Encoding.UTF8.GetBytes(Command);  
     byte[] bytesbuffer = Client.UploadData(Url, "POST", bytes);  
     JasonBuffer = Encoding.Default.GetString(bytesbuffer);  
     return JasonBuffer;  
   }  


It is not a good solution, but it works. If you ask me why I work in this way, probably what I can tell you are

  • Simpro, like other online systems, can afford large amount of short and small requests simultaneously. So split the query into small pieces actually will fasten the data flow task.
  • I tried to use parameters to limit number of records returned from Simpro, but it works when I need to find newly created records only. I could not find parameters to tell me which record was updated within a certain date/time range.
  • Moreover, the Simpro APIs are not 100% correct. From my understanding these APIs are derived from  the interface reporting query. But for some reasons, some of the APIs will fail due to the query error.
So here is the last piece of the Simpro work. To me it is a good experience to work with something not perfect, but finally get problem resolved. Hopefully these posts can help you at some degree,

2015-01-19

Simpro & SSIS Part 2: SSIS for Simple Entity

In this post I am going to discuss how I design the ETL procedure via SSIS.

Usually when I need to start a new project, I would like to have an overall understanding firstly, then do a quick coding demonstration, and finally turn the demonstration into implementations, as demonstration sometimes cannot be converted into a successful implementation.


Now go back to this Simpro topic

  • The client required to merge data from two data sources (Simpro and the dialler) for reporting purpose
  • From business perspective, Employees, Products, Quotes, Jobs, Invoices, and Payments are key entities I need to work on
  • I had built a demonstration in prior post to show how to use OAuth to access Simpro, and how to make a Simpro API call
So I know I can start to turn the demonstration into implementation. Below is a simple implementation for Employee entity  

EMPLOYEE

The Employee information is retrieved through EmployeeSearch method. Due to the business process, I need to add some extra conditions to filter the result set, which is done by calling CostCentreSearch. But basically they follow the same routine.

To prepare the backend, I create an Employee table by using below script

CREATE TABLE Simpro.Employee
(
EmployeeKey INT IDENTITY(1, 1),
EmployeeID VARCHAR(50) NOT NULL,
EmployeeName VARCHAR(255) NOT NULL,
CreatedOn SMALLDATETIME NOT NULL DEFAULT(GETDATE()),
ModifiedOn SMALLDATETIME NULL,
CONSTRAINT PK_SimproEmployee PRIMARY KEY (EmployeeKey)
)
GO


Below is a control flow overview for the Employee Entity



Inside the data flow “DFT Simpro EmployeeSearch”:



And inside the data flow “DFT Simpro CostCentreSearch


 
In the data flow design above, I use the script component as the data source to call the Simpro API. To do that, I firstly add output columns as demonstrated below

 Now we need to make the script work. Open the VSTA interface by click Edit Script, and add the references as mentioned in my prior post (OAuthBase.cs and Newtonsoft.Jason).

 

Within the method CreateNewOutputRows, I input below codes:

 

 public override void CreateNewOutputRows()  
   {  
     string consumerKey = "Your Consumer Key";  
     string consumerSecret = "Your Secret";  
     string url = @"http://URL/api/?format=json";  
     //build Signature  
     OAuthBase oAuth = new OAuthBase();  
     string nonce = oAuth.GenerateNonce();  
     string timeStamp = oAuth.GenerateTimeStamp();  
     string normalisedUrl;  
     string normalisedRequestParams;  
     string signature = oAuth.GenerateSignature(new Uri(url), consumerKey, consumerSecret, null, null, "POST", timeStamp, nonce, out normalisedUrl, out normalisedRequestParams);  
     signature = HttpUtility.UrlEncode(signature);  
     //parameter  
     StringBuilder sb = new StringBuilder("");  
     sb.AppendFormat("OAuth oauth_version=\"1.0\",");  
     sb.AppendFormat("oauth_nonce={0},", nonce);  
     sb.AppendFormat("oauth_timestamp={0},", timeStamp);  
     sb.AppendFormat("oauth_consumer_key={0},", consumerKey);  
     sb.AppendFormat("oauth_signature_method=\"HMAC-SHA1\",");  
     sb.AppendFormat("oauth_signature={0}", signature);  
     WebClient Client = new WebClient();  
     Client.Headers.Add("Authorization", sb.ToString());  
     Client.Headers.Add("Content-Type", "application/json");  
     Client.Headers.Add("Accept", "application/json");  
     Client.Proxy = null;  
     JavaScriptSerializer JSS = new JavaScriptSerializer();  
     Dictionary<string, object> Params = new Dictionary<string, object>();  
     Params.Add("CompanyID", 0);  
     Params.Add("Limit", 65535); //be careful here  
     string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "EmployeeSearch"},  
         {"params", Params}  
       }  
         );  
     byte[] bytes = Encoding.UTF8.GetBytes(Command);  
     byte[] bytesbuffer = Client.UploadData(url, "POST", bytes);  
     string buffer = Encoding.Default.GetString(bytesbuffer);  
     //we do not need to deserialize all fields, just extract fields we need  
     JObject jObject = JObject.Parse(buffer);  
     JArray Results = (JArray)jObject["result"];  
     foreach (JObject Result in Results)  
     {  
       EmployeeSearchBuffer.AddRow();  
       EmployeeSearchBuffer.EmployeeID = (string)Result["EmployeeID"];  
       EmployeeSearchBuffer.EmployeeName = (string)Result["EmployeeName"];  
     }  
   }   
 

 

Codes above will give us a list of Simpro employees, but to ensure we load only what we need to load, and update only what we need to update, we need to apply a quick lookup. The condition I used is 
  • If EmployeeID does not exist => insert
  • If EmployeeID exists BUT name is different => update

Because there are plenty of resources about how to plan/design lookup procedures in SSIS, I am not going to cover this topic here (actually the screenshot is quite clear about the lookup procedure. But please bear in mind, SSIS is NULL sensitive.)
 
For most given Simpro APIs, we can try to follow codes above to pull the data from Simpro.
 
But as you can see from my comments in my codes above, I put the parameter limit as 65535. It is ok because from what I can see the system will not have more than 500 employees. But when we go to Quotes, Jobs, and Invoices, things could be a bit tricky: as far as I can see, I could not find some obvious fields to do simple lookup. And due to the Simpro design, I need to drill down to QuoteItem, JobItem, and InvoiceItem levels.
 
It was Ok at the beginning, but while the system is running, total time cost increased to a risk level. So in my in my next post, I will explain how I dealt with Quote, Job, and Invoice. :P

 

2015-01-11

Simpro & SSIS Part 1: Access Simpro through OAuth


Back to mid of 2014, one of our clients started to use a job management system, Simpro (Simpro.com.au), to run its business. On base of that, what they need is a data procedure to extract, transform, and report Simpro activities with other datasets, for example, contact center activities and dialler performance.

As there was no fast way to integrate a dialler into the system, we decided to download Simpro activities through its APIs. My basic idea is:
  • Extract Simpro data into staging area via SSIS Script task
  • Extract Dialler data into staging area via SSIS package, as it can be done through ADO.Net source
  • Cleansing and load into production

Simpro is a market leading job management system. However you may have the same feeling like me if you look at its API documentation: they are not well prepared. And if you do the Google research, you do not have too many reference materials.

So this is the reason I write this series: to record what I did in the project, start from the planning, to end of the implementation. And I hope this series can help you, if you need to find some Simpro API references.

To access Simpro we need to use OAuth, which you can find quite a lot references online. And you can find the specific guideline for Simpro at http://api.simpro.co/. However they are not provided in .Net implementation. As I am not a coding expert, I used below two components in my design:

OAuthBase.cs
You can find it at https://oauth.googlecode.com/svn/code/csharp/OAuthBase.cs. As name suggested, it is the base to implement OAuth access in .Net code.
Updated on 02/10/2017, please use below link to get OAuthBase:
https://gist.github.com/tsupo/112124

Json.Net
An optional but handy component to parse JSON string. You can find it at http://james.newtonking.com/json.

So too much talk, let’s look at some codes. Below codes are used to do a query against Simpro's Customer entitty. Hope it gives you some ideas before my next post: SSIS script task for Simpro.

(note, it is my understanding that in most Simpro APIs CompanyID is the only parameter required)

 public string GetJasonString(string ConsumerKey, string ConsumerSecret, string Url)  
     {  
       string JasonBuffer = "";  
       //build signiture  
       OAuthBase oAuth = new OAuthBase();  
       string nonce = oAuth.GenerateNonce();  
       string timeStamp = oAuth.GenerateTimeStamp();  
       string normalisedUrl;  
       string normalisedRequestParams;  
       string signature = oAuth.GenerateSignature(new Uri(Url), ConsumerKey, ConsumerSecret, null, null, "POST", timeStamp, nonce, out normalisedUrl, out normalisedRequestParams);  
       signature = HttpUtility.UrlEncode(signature);  
       //parameter  
       StringBuilder sb = new StringBuilder("");  
       sb.AppendFormat("OAuth oauth_version=\"1.0\",");  
       sb.AppendFormat("oauth_nonce={0},", nonce);  
       sb.AppendFormat("oauth_timestamp={0},", timeStamp);  
       sb.AppendFormat("oauth_consumer_key={0},", ConsumerKey);  
       sb.AppendFormat("oauth_signature_method=\"HMAC-SHA1\",");  
       sb.AppendFormat("oauth_signature={0}", signature);  
       WebClient Client = new WebClient();  
       Client.Headers.Add("Authorization", sb.ToString());  
       Client.Headers.Add("Content-Type", "application/json");  
       Client.Headers.Add("Accept", "application/json");  
       Client.Proxy = null;  
       JavaScriptSerializer JSS = new JavaScriptSerializer();        
       Dictionary<string, object> Params = new Dictionary<string, object>();  
       //CustomerSearch parameters  
       Params.Add("CompanyID", 0);  
       Params.Add("Search", "%");  
       Params.Add("Limit", 500);  
       Params.Add("Offset", 2000);  
       string Command = JSS.Serialize(new Dictionary<string, object>  
       {  
         {"id", "1"},  
         {"method", "CustomerSearch"},  
         {"params", Params}  
       });  
       byte[] bytes = Encoding.UTF8.GetBytes(Command);  
       byte[] bytesbuffer = Client.UploadData(Url, "POST", bytes);  
       JasonBuffer = Encoding.Default.GetString(bytesbuffer);  
       return JasonBuffer;  
     }