PureConnect

 View Only
Discussion Thread View
  • 1.  Database Query Recommendation

    Posted 04-30-2020 15:16
    ​Hello all.

    I'm hoping somebody can recommend a more efficient way to write data to a variable from a db with many columns of data.  The database has columns of MenuOption, English, German, and many other languages.  The rows are Option_1 (for MenuOption) with an integer in each language column.

    I have the db query configured so I have unique variables bound to each column.  So I pull the appropriate record (where MenuOption=x), and I now have several (15+) integer variables, each representing one of the language columns (ie: iEnglish=1, iGerman=4, etc). 

    I now need to act on one of these variables based on the language for the specific interaction, which is represented in another variable (sLanguage=English). 

    I am doing it now with a selection step where the string variable is used as the criteria of the selection, and I write the specific language integer to a new variable (iFinal), which then proceeds through the rest of the handler.

    Is there a tool that can write the value to iFinal based on assembling the variable name from text and another variable name (ie: "i" & sLanguage = iEnglish).

    Or is there another option?

    Any recommendations are appreciated.

    PS: Can the Attendant DB Query tool use an attribute to define the desired column to return?

    Thanks.
    #Handlers

    ------------------------------
    Tom Mullen
    Boehringer Ingelheim GmbH
    ------------------------------


  • 2.  RE: Database Query Recommendation

    Posted 05-01-2020 12:13
    Edited by Paul Simpson 05-01-2020 12:13
    Hi Tom,

    My first question is, where does the variable sLanguage come from? String comparisons are relatively inefficient, so if you could arrange to prepend the string with the integer, then you can simply strip the first character off and convert it directly to an integer, which is much faster. This may remove the need for the Select all togetehr, but even if you still require it, you can then use integer comparisons which are more efficient.

    Unfortuantely, Handlers doesn't support the type of self-modification you are asking about (i.e. referencing a variable or subroutine indirectly by passing its name in another variable)

    AFAIK, neither the Attendant, nor the Handlers DB tool can dynamically select the column based on an attribute or a variable, the best way to do this would be from a Handler calling a Stored Procedure.which would add the dynamism you are asking about.

    I hope my ramblings make some sense and I'm happy to discuss options with you. If you could be a little more specific about what you are doing with the values overall (the 10,000 foor view!) it may help ;-)

    ------------------------------
    Paul Simpson
    Senior Technical Instructor
    ------------------------------



  • 3.  RE: Database Query Recommendation

    Posted 05-01-2020 14:45
    ​Thanks Paul.

    This is to support our global service desk.  Our GSD is supported by 2 vendors covering about 15 languages with over 40 different possible destinations depending on language & menu option selected.  We are adding load balancing to the process for the 2 vendors, and the load balancing percentage is based on the language.

    I have it working now using selection steps in Attendant.  It is quite complex and inefficient in it's current configuration.

    As I am more comfortable now with creating subroutines (thanks to your class :-) ), and using databases, I am rebuilding it to try and be more efficient.

    When the call comes in, we run a subroutine that does a db query based on the Country Code of the ANI. This handler creates attributes for the caller's Language, & Country.

    It then goes back to Attendant, and offers the caller with their menu options.  This creates the MenuOption attribute.

    We then run another subroutine that performs another db query based on the menu option to get the load balance percentage for the caller's language.  This is where I'm trying to improve the logic.  Your recommendation of using a stored procedure may be the answer.  This is something I'll need to experiment with.  Would I use the DB Query tool for executing a stored procedure, or is that a different tool?

    FYI, once we get the load balance percentage, the subroutine runs a process to determine which vendor the call should be routed to, and then pulls the appropriate destination from another table.

    Thanks!
    Tom

    ------------------------------
    Tom Mullen
    Boehringer Ingelheim GmbH
    ------------------------------



  • 4.  RE: Database Query Recommendation

    Posted 05-01-2020 17:30
    Tom,

    I am not sure if this will accomplish what you are wanting to do...but i am sure @Paul Simpson will know.

    Have you explored using SOAP Request? We use this to call a web service which can grab all the information we need at once. So instead of doing mutliple DB query from handlers​, we do 1 call to a web service and it does all the dB queries and then we can then decide what is returned and assign it as an attribute to the call.

    I don't recall SOAP tools being something covered in Handler class, but for us has been a very powerful tool.

    ------------------------------
    Scott Williams
    Missouri Higher Education Loan Authority
    ------------------------------



  • 5.  RE: Database Query Recommendation

    Posted 05-03-2020 22:49
    Hi Tom,

    Have you thought about using an IP Table for the data in the initial query? That would be much easier and faster than any DB query.

    If you want to use a stored procedure, you would use the DB Stored Procedure tool step. The query vs stored procedure wouldn't necessarily be more or less efficient. That would depend on the data and how you built the query. It may be easier via a stored procedure.

    Scott mentioned using SOAP tools. REST APIs are more common nowadays, so if you build a new service, it can be a RESTful web service and you can use the REST tools. Your service would still have to communicate with a DB and now you're introducing another step.

    You mentioned going back to Attendant from a subroutine. Why not just build the rest of your logic, including the menu that follows, in the same subroutine?


    Thanks,

    ------------------------------
    Daniel McLeod
    Qsect LLC
    ------------------------------



  • 6.  RE: Database Query Recommendation

    Posted 05-04-2020 09:48
    Whilst I agree that IP Tables would be faster than a database lookup, I'm not sure the bottleneck is with the query itself.

    My two areas of concern are the Select statement with multiple string comparisons, which is used to convert from a language name to a number, and the variable column selection. Both of these would remain an issue.

    Where an IP Table may very well help is with the removal of the Select statement. Assuming my previous suggestion (prefixing the string with an integer which you can strip off and convert) isn't workable, then you could use an IP table to lookup the language and return an integer. Given the number of languages, this may very well be the most efficient method and also allows for future languahe additions without modifying the code - always a good thing!

    I agree totally that using REST or SOAP would add an additional layer to the process, although this may not be all bad. After all, abstracting it in this way would mean that changing the database back-end at a later date would be much easier. It also would allow the entire process to be handled within Attendant, since neither IP Tables not Stored Procedures are currently supported there. Unfortunately, unless I have missed something, Attendant cannot currently make direct use of REST, so for this solution, if it's to be handled entirely in Attendant, it'd have to be SOAP.

    My reason for suggesting a Stored Procedure is to allow for the variable column return. PureConnect is not a data-processing application, so it's support for complex data analysis is somewhat limited. Using an SP, you could pass the row and column required and have it return the value, without tedious post-processing in a Handler. I agree that the actual call may not be more efficient, but I believe the overall process would be.

    Finally, I have to respond to your question. I believe that for the most efficient workflow, and the most supportable end result, as much as possible should be completed within Attendant and subroutines only called to perform the minimal amount of work that cannot be achieved any other way, immediately returning control to Attendant for the heavy lifting. Consider the "Play a Menu" Operation. It handles playing the prompt in the chosen language, repeating the playback, default options, possibility of Speech Recognition integration and, most importantly, it is fully tested and it works. To replicate that one operation in Handlers involves a lot of code, with subsequent testing and then the possibility of still having errors. Furthermore if it is enhanced in a furture release, then the enhancements would not be available to the custom code. This is just one example, consider "Caller Data Entry" or pretty much any of the operations.

    HTH


  • 7.  RE: Database Query Recommendation

    Posted 05-04-2020 10:41
    Hi Paul,

    Without more details, it's difficult to provide a recommendation, so I was just trying to suggest some alternatives.

    If they built and maintain the data in the db and it's not updated from an outside source, then they could probably restructure it and move it into an IP table that would work. With that being said, they could also potentially restructure their data in the DB itself to make a query more efficient. With the current data and structure, a stored proc would be the easiest way to offload the logic to the SQL server.

    I think your statement about PureConnect not being a data processing application is spot on. Moving logic outside of PureConnect when possible is always ideal. Also, moving to a REST API is the method I generally recommend. That positions the customer to be able to transition to PureCloud/Genesys Cloud in the future too. Unfortunately, not all orgs have access to a backend dev to build a new service and maintain it.

    In regards to sending calls back to Attendant, I guess it depends on the use case. I don't think Attendant is always the most efficient and supportable. That depends on the logic and skill level of the individual. I've seen a lot of handlers that could have used Attendant to accomplish the same logic in a much cleaner and readable way. A lot of this was from pre 4.0 when Attendant lacked a lot of the features it has today. I've also seen the opposite, where too much was being done in Attendant that would have been simpler in handlers. The reason I asked, was because bouncing between Attendant and handlers is not always the best option. If you're going to call multiple subs, it's time to consider moving the logic into a handler. If you build your own sub routines playing prompts, menu options, playback, etc. then it's reusable and most handler developers end up doing this at some point or have shared subs within their company as to not have to rebuild this for different customers. You could even go as far as copying some of the logic from the Attendant handlers. It's only difficult once. Maybe the usage has grown, but I'd say at least 90% of the customers I've worked with never used speech rec. It wouldn't be much different to enable it via handlers though. It really just depends on what you are trying to accomplish and the complexity. It's not a one size fits all approach.

    Thanks,

    ------------------------------
    Daniel McLeod
    Qsect LLC
    ------------------------------



  • 8.  RE: Database Query Recommendation

    Posted 05-04-2020 14:31
    Daniel,

    I can certainly agree that it's not a "one size fits all" situation. We will have to agree to differ on the Attendant / Handlers hand-off. I'm always an advocate for using as much "Out the Box" functionality as possible, but every situation is different.


  • 9.  RE: Database Query Recommendation

    Posted 05-04-2020 13:24
    Thanks to everybody for the thoughts.  The reason I'm using a database instead of an IP table is because it is accessed by 3 different IC systems (US / EU / Asia).  Using the database enables us to make a change at one location for the majority of the ​configuration.  It also more efficiently load balances the calls globally instead of regionally. 

    I send it back to attendant because then any of the Genesys SMEs in our company can make programming adjustments that may be necessary.  They just need to make the change 3 times.  Only a very small number of people have the expertise to manipulate the subroutines.

    I have zero experience with either SOAP or REST, so I have some research to do.

    ------------------------------
    Tom Mullen
    Boehringer Ingelheim GmbH
    ------------------------------



Need Help finding something?

Check out the Genesys Knowledge Network - your all-in-one access point for Genesys resources