ClickAnalyze Insight Agent for all Nightly Reports

You have just configured new ClickSchedule report using the Service Optimization Administration console. Now you need to configure an agent to run the report once a night.

I strongly suggest to use the ClickAnalyze Insight agent for any nightly report, with the cost of the new time table needed for the agent. You can create such table using the following SQL:

create table W6RP_MY_REPORT_TIME(PK_Date date not null,
Date_Name varchar2(50) null,
constraint PK_MY_REPORT_TIME primary key (PK_Date)
using index tablespace USERS) tablespace USERS;

Populate the table with single record for two days ago:

TimeCmd "Provider=OraOLEDB.Oracle;User Id=W6ADMIN;
Password=****;Data Source=CAR_ALIAS"
W6RP_MY_REPORT_TIME en-US -2 -2

Create foreign key relation between the tables:

alter table W6RP_MY_REPORT
add constraint FK_MY_REPORT1 foreign key (Time_Start)
references W6RP_MY_REPORT_TIME;

Now just set a new ClickAnalyze Insight agent setting to process the time table and the report:

Owner:        [Application]
Category:     Agent
Sub Category: Insight
Name:         My Report
Body:
<InsightAgent>
  <Connections>
    <Connection>
      <TargetConnection
        Key="-1">(local)</TargetConnection>
      <TimeTable>W6RP_MY_REPORT_TIME</TimeTable>
      <Culture>en-US</Culture>
      <RelativeTo>-1</RelativeTo>
    </Connection>
  </Connections>
  <Reports>
    <Report>
      <Name>My Report</Name>
    </Report>
  </Reports>
  <Notifications>
    <Notification Active="true">
      <Type>Email</Type>
      <From>click@acme.com</From>
      <To>
        <Recipient>dispatcher@acme.com</Recipient>
      </To>
      <Subject>My Report Execution Result</Subject>
      <Importance>2</Importance>
    </Notification>
  </Notifications>
</InsightAgent>

What you will get is the better error handling of the agent, which rollback partial processing. You can add multiple reports to run in sequence. Just remember, one error will rollback all the reports.

After error, you can try and fix the problem and rerun the agent. If the agent run successfully, then rerun is not going to do any processing, since the time table is already with the processing date.

Mobile iPhone Client and Multitasking

As we all know, Apple previewed today some features of the next major upgrade for the iPhone OS. Especially important for me is the multitasking implementation (we all know it was just matter of developing the correct UI and waiting for 1GHz CPU, which probably be in the next generation iPhone hardware).

While still offering the push notifications option, it is much easier to implement a thread that polls the remote server. I will have to check carefully, what is allowed under the updated platform.

I feel that I started the development at the right time, when the OS is maturing. I will probably aim to the end of 2010, so I can benefit from the iPhone OS 4 improvements.

Short iPhone Client Update

I have a WebService working on the server side. I have a WebServiceClient working asynchronously on the iPhone. Still need to finish the SQLite code for the client side database. I have initial UI components. Also, adding some barcode reading code, just in case ;-)

Sample Data

Any demo is better when showing "full and real" data. Means that, all engineers are scheduled in the past, the demo system is fully functional, and all clients are showing the most complete picture of the demoed company.

However, when the demo data needs to include ClickAnalyze data, including single district is not enough. You need at least 2 regions, each with 5 districts, and history for at least 3 years. For a recent demo, I made just that.

Now I need to find some time to: (A) Make the code more generic, so it can fit other demos. (B) Add the option to see trends over time, probably I need to create new engineers over time, to show growth. and (C) Story. It would be best if the demo will include points in time that can be shown with correlation to the story.

After seeing only few demos during my years in ClickSoftware, and after this recent demo, which I helped to prepare small part of it, I can definitely say that ClickAnalyze is lagging behind in the demo-sphere.

SQL Server CE ? not with iPhone

The first hurdle with creating the iPhone client is to overcome the SQL Server CE synchronization of the current ClickMobile client. In this post, I describe in short the decision I took in the replacement of this technology.

The current ClickMobile solution is storing all the data and the communication into local SQL Server CE database. Then the client taking advantage of the built-in technology of SQL Server CE to synchronize the local instance with the centralized instance on the remote server.

SQL Server CE includes a special "Remote Data Access" object, that communicate with the central SQL Server over HTTP. This object exposes special PUSH and PULL methods for table synchronization. On the server, ClickMobile is an agent, which runs in the background and poll the SQL Server database. This agent is responsible for processing the "requests" records.

I want to have the iPhone client communicating using the same "channel". So I started by creating a WebService with PUSH and PULL methods. This WebService allows the client to perform the table synchronization without running SQL Server CE.

It is now the time to create the client side component. I think it should be a SQLite to SOAP and SOAP to SQLite gateway. The SOAP messages are proprietary and are not following any standard (except of being SOAP messages). Following this client side development, I may also develop compression to save on bandwidth.

Well, off to work now...

ClickMobile for the iPhone

I have decided to develop an engineer client application for the iPhone (similar to ClickMobile). I guess I will have this blog updated with my progress from time to time.

Here is a short status of what I did already:

1. It took me few days to have a running environment of Service Optimization on VirtualBox (including both server side of ClickMobile, as well as PC client of ClickMobile).

2. It took me another day to bridge the host OS and the guest OS securely, so the iPhone Simulator can talk to the WebService on the virtual machine.

3. Another day I spent on creating small iPhone app, that can send SOAP message to WebService in the ClickMobileSync directory, and parse the SOAP response.

Now, since there is no SQL Server Mobile for iPhone, I will need to go with custom solution (hope this wont be too long).

If you have any comments, requests, or priorities for such application, please leave a blog comment or call me. I would be more than happy to hear from you ;-)

Geographic Domain in Client Add-in

When writing add-in to the ClickSchedule client, I was trying to get the full navigation tree from ClickSchedule client. I have made this short code:

' Option 1 - using the client's loaded domain
Public Function DoAddIn( _
    ByRef objCallingApp As Object, _
    ByRef objCallingDoc As Object, _
    ByRef lDocType As Integer, _
    ByRef varSelectedEngineers As Object, _
    ByRef varSelectedTasks As Object, _
    ByRef varSelectedAssignments As Object) _
    As Boolean

    ' Get the client domain navigation tree
    Dim navObj As Object = _
        CType(CType(objCallingApp, _
        W6BFClient.W6BFClient).Domain, _
        W6BFClient.Domain).NavigationTree
    If Not IsArray(navObj) Then Return False
    Dim navArr As System.Array = navObj

    ' Request full navigation tree
    Dim request As XmlDocument = New XmlDocument
    request.LoadXml( _
        "<SXPServerGetTree Revision=""7.5.0"">" & _
        "<NavigationTree/></SXPServerGetTree>")
    Dim ids As XmlNode = _
        request.DocumentElement.FirstChild

    ' Set the collection ids
    Dim first As System.Array = navArr(0)
    For index As Integer = _
        first.GetUpperBound(0) To 2 Step -3

        ids.AppendChild(request.CreateElement( _
            "CollectionID")).InnerText = _
            first(index).ToString()
    Next

    ' Create connection object (this is add-in, _
    ' connection already open by the client)
    Dim sxp As W6Logon.Connection = _
        New W6Logon.Connection
    Dim response As XmlDocument = sxp.Send(request)
End Function

The problem with this option is the requirement for the user to have one or more districts loaded in the client, prior to opening this add-in. So, here is the second option:

' Option 2 - not using the client's loaded domain
Public Function DoAddIn( _
    ByRef objCallingApp As Object, _
    ByRef objCallingDoc As Object, _
    ByRef lDocType As Integer, _
    ByRef varSelectedEngineers As Object, _
    ByRef varSelectedTasks As Object, _
    ByRef varSelectedAssignments As Object) _
    As Boolean

    ' Get the client app manager
    Dim clientAppManager As _
        W6BFClient.IW6BFClientAppManager = _
        CType(objCallingApp, _
        W6BFClient.IW6BFClientAppManager)

    ' Request admin setting
    Dim requestAdmin As XmlDocument = New XmlDocument
    requestAdmin.LoadXml( _
        "<SXPServerGetObjects Revision=""7.5.0"">" & _
        "<ObjectType>UserSetting</ObjectType>" & _
        "<Indexes><Distinct>0</Distinct>" & _
        "<Index><LowBound><Property>" & _
        "<Name>Category</Name>" & _
        "<Value>Power Scheduler Client</Value>" & _
        "</Property><Property>" & _
        "<Name>SubCategory</Name>" & _
        "<Value>Administrative Settings</Value>" & _
        "</Property></LowBound><HighBound>" & _
        "<Property><Name>Category</Name>" & _
        "<Value>Power Scheduler Client</Value>" & _
        "</Property><Property>" & _
        "<Name>SubCategory</Name>" & _
        "<Value>Administrative Settings</Value>" & _
        "</Property></HighBound></Index></Indexes>" & _
        "<RequestedProperties>" & _
        "<Item>Key</Item><Item>Owner</Item>" & _
        "</RequestedProperties>" & _
        "</SXPServerGetObjects>")
    Dim responseAdmin As XmlDocument = SendSxp( _
        "SXPServerGetObjects Administrative" & _
        " Settings", requestAdmin)

    ' Get the template key
    Dim template As String = _
        clientAppManager.AdminTemplateName.ToLower()
    Dim key As Integer = -1
    For Each child As XmlNode In _
    responseAdmin.DocumentElement.FirstChild.ChildNodes

        Dim owner As String = child.SelectSingleNode( _
            "Owner").InnerText.ToLower()
        If template.Equals(owner) Then
            key = CInt(child.SelectSingleNode( _
                "Key").InnerText)
            Exit For
        End If
    Next
    If (key = -1) Then Return False

    ' Request template setting
    Dim requestTemplate As XmlDocument = _
        New XmlDocument
    requestTemplate.LoadXml( _
        "<SXPServerGetObjects Revision=""7.5.0"">" & _
        "<ObjectType>UserSetting</ObjectType>" & _
        "<KeySet><Key>" & key.ToString() & _
        "</Key></KeySet><RequestedProperties>" & _
        "<Item>Key</Item><Item>Body</Item>" & _
        "</RequestedProperties></SXPServerGetObjects>")
    Dim responseTemplate As XmlDocument = SendSxp( _
        "SXPServerGetObjects AdminTemplateName", _
        requestTemplate)

    ' Get the task property ids for the navigation tree
    Dim body As XmlDocument = New XmlDocument
    body.LoadXml( _
        responseTemplate.DocumentElement.FirstChild. _
        FirstChild.SelectSingleNode( _
        "Body").InnerText)
    Dim navigationProperties As XmlNode = _
        body.DocumentElement.SelectSingleNode( _
        "SOFTWARE/IET/W-6BreakFix/Client/" & _
        "Administration/Navigation")

    ' Request task scheme
    Dim requestTaskScheme As XmlDocument = _
        New XmlDocument
    requestTaskScheme.LoadXml( _
        "<SXPServerGetCollectionsScheme " & _
        "Revision=""7.5.0"">" & _
        "<Collections><Collection><ID>2</ID>" & _
        "</Collection></Collections>" & _
        "</SXPServerGetCollectionsScheme>")
    Dim responseTaskScheme As XmlDocument = SendSxp( _
        "SXPServerGetCollectionsScheme", _
        requestTaskScheme)

    ' Request full navigation tree
    Dim request As XmlDocument = New XmlDocument
    request.LoadXml( _
        "<SXPServerGetTree Revision=""7.5.0"">" & _
        "<NavigationTree/></SXPServerGetTree>")
    Dim ids As XmlNode = _
        request.DocumentElement.FirstChild

    ' Set the collection ids
    For index As Integer = _
        1 To navigationProperties.ChildNodes.Count

        Dim propertyID As String = _
            navigationProperties.SelectSingleNode( _
            "Level" & index.ToString() & _
            "/FieldNumber").InnerText.Replace("""", "")
        Dim collectionID As String = _
            responseTaskScheme.DocumentElement. _
            SelectSingleNode( _
            "Collections/Collection/Attributes" & _
            "/Attribute[ID='" & _
            propertyID & _
            "']/KeyTypeInfo/ObjectType").InnerText
        ids.AppendChild(request.CreateElement( _
            "CollectionID")).InnerText = collectionID
    Next

    ' Create connection object (this is add-in, _
    ' connection already open by the client)
    Dim sxp As W6Logon.Connection = _
        New W6Logon.Connection
    Dim response As XmlDocument = sxp.Send(request)
End Function

The second option is longer, but works without loaded districts.