Miscellaneous Ramblings on Hacking

June 8, 2010

RightNow .NET API Performance = Not So Good

Filed under: CRM, RightNow — Zack Bethem @ 1:59 am

Just a little update to my previous posting on RightNow‘s .NET API performance.

This API has me CRYING for their Apex Data Loader. Apex was FAST!!! It was extremely fast for selecting records. But it was even fast inserting and updating records. That was an awesome tool — dump the records out, massage them in Access using SQL, pump the records back in, go home an eat lunch. Done in 3hrs.

So here are the latest metrics:

RightNow .Net API

Extract: 1.5MM records in 1hr 4min

Update: 1.5MM records 3days+

Some of you may ask what is the update and how involved is it? I believe it to be a fairly simple task, myself. I’m updating each contact record and setting a custom field’s value to an externally assigned primary key. The one excuse I will allow RightNow is that there are 250 or so custom fields for the contact. I would expect some performance degradation, but a data conversion of 1.5mm records taking longer than 3days is just stupid.

I love coding in .Net, but man is this worthless!

RightNow Analytics — The SQL Way

Filed under: CRM, RightNow — Zack Bethem @ 1:47 am

I always lose sight of this when I need it. That and RightNow’s website hides this from anyone’s view (side rant: really, how does one search in Google for RightNow the product?). Gartner says RightNow is #1 for customer support KB search, but I can never find shit using their support KB search. (side rant: seriously how much was Gartner paid?)

Create a New Report via SQL

To do this, we’re going to be using a custom script.

  1. Define a filter that will always return zero rows.
    filter: 1=0
  2. Select Level > Custom Scripts
  3. Select the ‘Finish tab of custom scripts
  4. Paste in the following while modifying the SQL to your liking:$temp=array();
    $row_idx = 0;
    //Run the desired query
    $query=sql_prepare
    (sprintf("
    SELECT label,ac_id, header_code, init_code, process_code, exit_code
    FROM ac_scripts a, labels l
    where a.ac_id = l.label_id
    and tbl=121
    and (header_code is not NULL
    or init_code is not NULL
    or process_code is not NULL
    or exit_code is not NULL)
    "));
    //Each column in the SELECT clause will need to be returned as the appropriate data-type (INT for integer, NTS for string, DTTM for datetime)
    sql_bind_col($query,1,BIND_NTS,80);
    sql_bind_col($query,2,BIND_INT,0);
    sql_bind_col($query,3,BIND_NTS,1000);
    sql_bind_col($query,4,BIND_NTS,1000);
    sql_bind_col($query,5,BIND_NTS,1000);
    sql_bind_col($query,6,BIND_NTS,1000);
    //For each record returned in the above query, return it as one row of output, each field in its respective column
    while ($temp = sql_fetch($query))
    {
    $exit_obj[$row_idx][0]->val = $temp[0];
    $exit_obj[$row_idx][1]->val = $temp[1];
    $exit_obj[$row_idx][2]->val = $temp[2];
    $exit_obj[$row_idx][3]->val = $temp[3];
    $exit_obj[$row_idx][4]->val = $temp[4];
    $exit_obj[$row_idx][5]->val = $temp[5];
    ++$row_idx;
    }
    //Clear your buffer
    sql_free($query);
  5. $exit_obj is the array returned, whose values are subsequently displayed in the report

February 24, 2010

Fun with c# and the RightNow API

Filed under: .Net, RightNow — Zack Bethem @ 12:29 am

I wanted to dump out data from RightNow’s Contact tables. The intention was to load a SQL Server table that would then be used in later ETL for data quality rules.

The code does the magic I want.

  • Execute a RightNow report for a range of contacts.c_id values
  • Store the results in a SQL Server table on my local machine

The only problem is performance. SFDC can dump out 2-3million rows in an hour. My code was far from efficient as it was built for a quick-exercise. The code for 1.5million rows would finish in about 14hours. YUCK! Update: I was able to score 760k records in 1.5hrs … much better.

Here’s my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using obj.api.rightnow.com;
using fault.api.rightnow.com;
using api.rightnow.com;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Becker_DataExtract
{
    class Program
    {
        static void Main(string[] args)
        {

            RNOWObjectFactory ofactory = new RNOWObjectFactory(Properties.Resources.RNOWConnection);
            ofactory.login(Properties.Resources.RNOWLogin, Properties.Resources.RNOWPass);

            //database stuff
            string connString = "server=(local)\\SQLEXPRESS;Initial Catalog=BeckerPOC;User ID=becker;Password=becker";
            string insertSQL = @"insert into RNContacts(c_id,org_id,last_name,first_name,email,email1,email2,street
,city,prov,postal_code,country,ph_asst,ph_fax,ph_home,ph_mobile,ph_office, RNLastUpdated)
values
(@c_id, @org_id, @last_name, @first_name, @email, @email1, @email2, @street
,@city, @prov,@postal_code,@country,@ph_asst,@ph_fax,@ph_home,@ph_mobile,@ph_office, @RNLastUpdated)
";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            object[][] reportResult;

            System.Console.WriteLine("Staring process: " + DateTime.Now.ToShortTimeString());

            for (int i = 1; i <= 25; i++)
            {
                List<RNOWAcFilter> reportFilters = new List<RNOWAcFilter>();
                RNOWAcFilter reportFilter = new RNOWAcFilter();
                reportFilter.Operator = Convert.ToInt32(RNOWUtil.SearchOperator.RANGE);
                reportFilter.Value = i.ToString() + "|" + (i * 1000).ToString();
                reportFilter.FilterId = 1;
                reportFilters.Add(reportFilter);
                reportResult = ofactory.ExecuteReport(Convert.ToInt32(Properties.Resources.RNOWExtractReport), reportFilters.ToArray());

                foreach (Object[] result in reportResult)
                {
                    SqlCommand cmdSQL = new SqlCommand(insertSQL, conn);

                    SqlParameter c_id = new SqlParameter("@c_id", Convert.ToString(result[0]));
                    cmdSQL.Parameters.Add( c_id );

                    SqlParameter org_id = new SqlParameter("@org_id", Convert.ToString(result[1]));
                    cmdSQL.Parameters.Add(org_id);
                    SqlParameter last_name = new SqlParameter("@last_name", Convert.ToString(result[2]));
                    cmdSQL.Parameters.Add(last_name);
                    SqlParameter first_name = new SqlParameter("@first_name", Convert.ToString(result[3]));
                    cmdSQL.Parameters.Add(first_name);
                    SqlParameter email = new SqlParameter("@email", Convert.ToString(result[4]));
                    cmdSQL.Parameters.Add(email);
                    SqlParameter email1 = new SqlParameter("@email1", Convert.ToString(result[5]));
                    cmdSQL.Parameters.Add(email1);
                    SqlParameter email2 = new SqlParameter("@email2", Convert.ToString(result[6]));
                    cmdSQL.Parameters.Add(email2);
                    SqlParameter street = new SqlParameter("@street", Convert.ToString(result[7]));
                    cmdSQL.Parameters.Add(street);
                    SqlParameter city = new SqlParameter("@city", Convert.ToString(result[8]));
                    cmdSQL.Parameters.Add(city);
                    SqlParameter prov = new SqlParameter("@prov", Convert.ToString(result[9]));
                    cmdSQL.Parameters.Add(prov);
                    SqlParameter postal_code = new SqlParameter("@postal_code", Convert.ToString(result[10]));
                    cmdSQL.Parameters.Add(postal_code);
                    SqlParameter country = new SqlParameter("@country", Convert.ToString(result[11]));
                    cmdSQL.Parameters.Add(country);
                    SqlParameter ph_asst = new SqlParameter("@ph_asst", Convert.ToString(result[12]));
                    cmdSQL.Parameters.Add(ph_asst);
                    SqlParameter ph_fax = new SqlParameter("@ph_fax", Convert.ToString(result[13]));
                    cmdSQL.Parameters.Add(ph_fax);
                    SqlParameter ph_home = new SqlParameter("@ph_home", Convert.ToString(result[14]));
                    cmdSQL.Parameters.Add(ph_home);
                    SqlParameter ph_mobile = new SqlParameter("@ph_mobile", Convert.ToString(result[15]));
                    cmdSQL.Parameters.Add(ph_mobile);
                    SqlParameter ph_office = new SqlParameter("@ph_office", Convert.ToString(result[16]));
                    cmdSQL.Parameters.Add(ph_office);
                    SqlParameter RNLastUpdated = new SqlParameter("RNLastUpdated", Convert.ToString(result[17]));
                    cmdSQL.Parameters.Add(RNLastUpdated);

                    //da.InsertCommand = cmdSQL;
                    cmdSQL.ExecuteNonQuery();
                    cmdSQL.Dispose();
                }

                System.Console.WriteLine("Done with loop run: " + i.ToString());
            }

            System.Console.WriteLine("End of process: " + DateTime.Now.ToShortTimeString());
            conn.Close();
            ofactory.logout();
            ofactory.Dispose();

        }

    }

}

Update:

Looking at the comments on this post, it appears that I have RightNow checking my code. Yes. there is a bug in it. The idea is to use a report to dump out data. So the start range needs to be incremented more than it is in the AcFilter.

February 17, 2010

C# interacting with a web form

Filed under: .Net, RightNow — Zack Bethem @ 11:45 pm

Sweet.. I love the web. I needed to build a POC for a RightNow project I’m working on. BAZINGA

Not only was it an awesome domain name. But, the code is exactly what I require. THANKS!

April 23, 2009

Dynamicallly build URLs in RightNow

Filed under: RightNow, Uncategorized — Zack Bethem @ 12:43 am

I always forget this, despite it being one of the most important features.

If you want to build a tab, that can pass a value from a custom field in RightNow as part of the parameter you can do it as follows:

  • $p_ccf_ — use with contact custom fields
  • $p_orgcf_ — use with organization custom fields
  • $p_icf_ — use with incident custom fields
  • $p_acf_ — use with answer custom fields
  • $p_spcf_ — use with sales product custom fields
  • $p_ocf_ — use with opportunity custom fields

Why would the “H” would one do this? Think of it this way. Client ABC has an existing portal that uses friendly URLs (like Ruby on Rails). So if I had an account-id custom field on an organization, I could pass that value and the web page shown in the control would present information pertinent to that account-id (aka. organization). HUGE savings.

February 23, 2009

RightNow Desktop Integration .. it begins

Filed under: RightNow, Uncategorized — Zack Bethem @ 5:39 pm

I’ve started exploring the new capabilities of RightNow desktop integration. It looks to be quite powerful, assuming I can get my hacking skills around it.

Unit testing code is a pain with the integration API. Each change requires a restart of RightNow. That can take a while, even with a fast connection & computer.

One recommendation, however, is to add the following in the project’s Post-build event. This will automatically deploy the new compiled dll and move it to the proper directory for development testing. It should be part of the default set of templates, but it isn’t.

mkdir "%USERPROFILE%\RightNowDev"
mkdir "%USERPROFILE%\RightNowDev\AddIns"
mkdir "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)"
copy /Y "$(TargetDir)$(TargetName).*" "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)\"

Great stuff.

January 28, 2009

RNOWOrganization – how to add a parent?

Filed under: .Net, RightNow — Zack Bethem @ 4:04 am

I’m still working with hacking my way through C#. The latest stumbling block is adding an organization with an associated parent organization. The end result would look like the following within RightNow:
Hierarchy

To date, I have the following code using the RNOW Data Connection API

// set parent org
if (Convert.ToString(row[38]) != "")
{
int z = getXrefId(Convert.ToString(row[38]), tblXrefIds);
if (z != 0)
{
RNOWOrganization parentOrg = new RNOWOrganization(z);
List parentOrgs = new List();
parentOrgs.Add(parentOrg.ID);
myOrg.Parent = parentOrgs;
}
else
{
//log error
}
}

The current question? The code syntax 'myOrg.Parent = parentOrgs;' doesn’t work. Instead, I get the error:
Cannot implicitly convert type ‘System.Collections.Generic.List<int>’ to ‘System.Collections.Generic.List<int?>’

Seems same to me.

Argh!!! Why can it just be a single Integer representing the ID of the organization. A list to set a single parent? What for? Make it simple stupid. The head scratching continues.

Update

Turns out C# provides a way to define a nullable list of collections. This is defined by the ‘?’ notation. So, parentOrgs in the code snippet above had to be defined by:

List<int?> parentOrgs = new List<int?>();
Defining the hierarchy is still not working 100%, but this hurdle is hopped over.

January 7, 2009

RightNow Connect DLL

Filed under: .Net, RightNow, Uncategorized — Zack Bethem @ 2:55 am

I’ve found myself doing some .Net coding these days. A rest from Ruby on Rails (which is friggin’ awesome).

When using the RightNow Connect DLL I always would get the most common error of all..

“Invalid Server Version. Server ‘ ‘. Connect ‘8.5.0.127’.”

Scratching my head. I closed the new solution in Visual Studio, which caused the solution’s folder structure to be created. After reopening it and re-pointing the reference to the RightNow Connect DLL. Things seem to work. No problems with that A-N-N-O-Y-I-N-G “invalid server version” message. Viola! It works. Good enough.

Create a free website or blog at WordPress.com.