NOTE: See KB6200-007-0010 and KB6200-007-0011 for information on different methods for alert services, their advantages, disadvantages and the steps involved.
The solution proposed in KB6200-007-0010 involves the creation of an event table in the warehouse. The creation of new attributes and facts, a new report based off those attributes that must be included in the publication and the inclusion of a Post Structured Query Language (SQL) statement in the Subscription set for the service created.
The solution proposed in KB6200-007-0011 is based on the use of Pre and Post SQL statements that are appended to a subscription set for a service. It requires the particular event (such as a warehouse load) to create a flag in the warehouse after it finishes execution. Then, the services created with Strategy Narrowcast Server needs to have some PRE execution SQL in the subscription set, with the Pre-SQL Service Execution Impact set to 'Required'. The Pre-SQL statement runs against this flag in the warehouse and forces the service to halt execution if the query returns an error.
The third solution proposed in this document is through Narrowcast Server SDK. (The SDK document is 'NCS7SDK.chm' located in '..\Narrowcast Server\Delivery Engine\Document'). It includes Narrowcast Server API functions that allow any external system to tell the Narrowcast Server to trigger a particular service immediately.
SOLUTION:
As a powerful and flexible information delivery platform, Strategy Narrowcast Server provides a comprehensive SDK. It includes COM-based open Application Program Interface (API) and Service Provider Interface (SPI) and all Strategy Narrowcast Server interfaces and functionality are called and built through them. Application developers can utilize such an SDK to customize their own narrowcast system.
Therefore, via API functions such as ExecuteService, application developers can build the application that executes the specified service on demand. Then, such applications can be integrated into the application that detects the alert condition to serve as a powerful event-based alert implementation.
The following Visual Basic (VB) sample code is a function built on ExecuteService function. The references needed for this function in VB are Strategy Delivery Engine API (MCBSAPI.DLL) and Strategy Delivery Engine Public Type Library (MCTypLib.DLL). Besides ExecuteService, the sample code below calls API functions ConnectToRepository, ConnectToSystem, GetObject, DisconnectFromSystem and DisconnectFromRepository. Please refer to SDK documentation for details on such API functions.
Public Function RunService(ByVal sServiceID As String, ByVal sSubsID As String) As String
'Repository connection variables
Dim ConnectionString As String
Dim Prefix As String
Dim DatabaseType As EnumMSTRDatabaseType
Dim ReconnectAttempts As Long
Dim oErrorInfo As IMSTRErrorInfoDefinition
'Execute Function Variables
Dim ServiceInfo As IMSTRServiceDefinition
Dim SubInfo As IMSTRSubscriptionSetDefinition
Dim oAPI As IMSTRDeliveryEngine
Set oAPI = CreateObject("MSTRBSAPI.MSTRBroadcastServer")
If oAPI Is Nothing Then
GoTo ErrorHandler
End If
ConnectionString = "DSN=NC_REPOS;UID=sa;PWD="
Prefix = "demo_"
DatabaseType = MSTRDatabaseTypeSQLServer
ReconnectAttempts = 3
Dim lResults As EnumMSTRReturnValueConstants
lResults = oAPI.ConnectToRepository(ConnectionString, Prefix, DatabaseType, ReconnectAttempts, oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
lResults = oAPI.ConnectToSystem(oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
lResults = oAPI.GetObject(sServiceID, ServiceInfo, MSTRReturnFieldBrowsingInformation, oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
lResults = oAPI.GetObject(sSubsID, SubInfo, MSTRReturnFieldBrowsingInformation, oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
lResults = oAPI.ExecuteService(ServiceInfo, False, SubInfo, False, "1", "1", oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
lResults = oAPI.DisconnectFromSystem(oErrorInfo)
lResults = oAPI.DisconnectFromRepository(oErrorInfo)
If lResults = MSTRError Then GoTo ErrorHandler
Set oAPI = Nothing
ErrorHandler:
On Error Resume Next
RunService = oErrorInfo.Description
Exit Function
End Function
The above function accepts the string input for the service identification (ID) and the subscription set ID, and then executes the service. In order to get the service ID and the subscription set ID for a specific service and corresponding subscription set, users can query Narrowcast Server Repository as follows:
select MR_OBJECT_ID, MR_OBJECT_NAME from MSTROBJNAMES where MR_OBJECT_TYPE = 19
select MR_OBJECT_ID, MR_OBJECT_NAME from MSTROBJNAMES where MR_OBJECT_TYPE = 17
MR_OBJECT_TYPE 19 is for the services (the service ID is the corresponding MR_OBJECT_ID) and MR_OBJECT_TYPE 17 is for the subscription sets.
Rather than manually query the object repository to get the service ID and the subscription set ID, a more powerful method is to utilize the API Function FindObjects to search for objects (service, subscription set, etc.) based on the specified criteria (object type, object name, etc.). If so, the above sample function can be further improved to take the specific service name as the input and then execute the service.
This sample function can serve as the foundation for event-based service application. Developers can integrate it with their alert condition detection applications to make the flexible and powerful event-based alerting implementation. Depending on different operating systems, application environments, database platforms and business requirements for alerting, there are several choices to do so. For example: integrating such function with warehouse loading scripts into a Data Transformation Service (DTS) package for Microsoft SQL Server, calling shell command which executes services based on the above function from Pro*C application for Oracle.
CONCLUSION:
Compared to other event-based alerting solutions, the SDK approach requires more development effort but perhaps is the most flexible and powerful approach. A well-designed event-based alerting implementation based on Narrowcast Server SDK can keep the maintenance on the database to the minimum, eliminate unnecessary network traffic and meet business requirements with the full utilization of the power of Strategy Narrowcast Server.
CUSTOMIZATION WARNING:
This customization is provided as a convenience to Strategy users. Strategy Technical Support makes no guarantees that the code provided will apply to any future or previous builds. In the event of a code change in future builds, Strategy Technical Support makes no guarantee that an updated version of this particular customization will be provided. In the event of a code change in future builds, Strategy may not be able to provide additional code on this matter even though this customization is provided at this time for this specific build. For enhancements to this customization or to incorporate similar functionality into other versions, contact your Account Executive to inquire about Strategy Consulting assistance.
