I wanted to share a solution that a BusinessObjects customer recently implemented. This solution won the praise of some of the toughest critics in the business and instant gratification, leaving them wanting for more. It is a great example of leveraging the existing investment in a BI system to achieve an innovative solution to a business problem. It also showcases the flexibility and power when using InfoBurst with Business Objects.
The problem was that senior management (VP to CEO) were losing visibility of new sales opportunities that had large revenue potential. Once the opportunity was entered, senior management would not find out about it until it was too late in the sales cycle. Either the opportunity was closed won or closed lost, the latter being of most concern to senior management.
The business requested an IT solution to provide near instant notification when a large value opportunity was created in the Customer Relationship Management (CRM) system which was Salesforce. An early notification would provide enough time for senior management to ensure the right team and support was in place to win the deal. There was only one caveat: the notification needed to support a dynamic recipient list. Given that the customer operates globally and has a complicated organizational hierarchy, this was no small task! And on top of this, the recipient list needed to be configurable!!!
After some back and forth between the business and IT, the BI team was elected to deliver the solution. Since the current BI environment had the technology and know-how, it made perfect sense rather than developing a totally custom solution.
InfoBurst was able to handle the automated scheduling and bursting of the notification by email, supporting a configurable and dynamic recipient list and handle the complex organizational hierarchy.
Below, is an example of what the key executive recipients would receive (modified to respect the privacy of the client):
Below is the process flow for the solution from start to finish:
- At the start of the burst, a SQL query is run to retrieve the Opportunity Ids that is the input parameters for the burst.
- The SQL query also checks against a Burst Log table. This table keeps a record of which opportunities have been sent and the date time it was delivered.
- Once the burst has input the Opportunity ID and generated the HTML burst, it then runs a SQL stored procedure to determine the list of recipient email addresses.
- The Recipient Table is the list of email addresses and criteria for sending the opportunity. The SQL Stored Procedure will look up these values of the Opportunity and cross reference against this table to determine the recipients. Below is an example of the table:
- The SQL Stored Procedure then updates the Burst Log table.
- The HTML burst is then sent to the email addresses returned.
- The Recipient Excel is an excel stored on a network share that the business administrators have access to. They can add/remove recipients. This excel file is loaded into the Data Warehouse via an ETL job. This ensures the BI team is not responsible for the maintenance of the recipients, this is handled by the business.
I hope this provides some inspiration for others when dealing with a similar challenge.
This solution may look simple on the surface when in fact, it is quite intricate.
So please comment below. I welcome a conversation. I would love to hear your feedback. Also, if you need any clarification on this solution or want to know more about the technical pieces don’t be shy.