Alerting without SQL Server Notification Services

Last week I worked with a customer who wanted to give users the ability to create and subscribe to alerts for changing KPI Values. This is a pretty standard scenario, and one we covered with BSM 2005. However, with the “loss” of Notification Services in SQL 2008, PerformancePoint server does not include alerting functionality out of the box (which stinks, but that’s another rant).

So, the customer was interested in creating a simple “SQLNS-less” alerting system that could send users email when KPI X’s value went up or down by a certain % or value. I put together a POC for them which uses SQL Integration Services (SSIS) to do the work. Note that this isn’t necessarily a best practice since as far as I know Microsoft doesn’t yet have any real guidance around how to replace SQLNS.

There were actually a bunch of different ways we could have approached this:

· Biztalk – rejected because it takes a while to grow BTS expertise and we’d be hitting a small-ish problem with an awfully big hammer

· Managed code and/or services which took care of alerting from soup-to-nuts – rejected because we didn’t want to recreate the wheel

· Managed code and/or services which managed part of the alerting process (detecting a change and dropping an event in the Windows Event log or raising a WMI event) and MOM or other operational management software to do the actual heavy lifting. We’d rely on MOM to see the newly added event and send whatever notice to whatever endpoint we specified. We rejected this because we couldn’t count on everyone having MOM running.

That pretty much left us with SSIS, which could do much of the work for us.

Here’s what this simple POC does:

· Collects “alert subscriptions” in a SQL table. Each subscription includes the email address to which we’ll send an alert, the KPI name in adventure works we’re monitoring, the value of that KPI when the subscription was created, and the “change factor” (up or down X%) which will cause the alert to fire

· Uses an SSIS Ipackage which reads each alert subscription and:

o Grabs the current value for the KPI we’re dealing with in the subscription currently being processed

o Compares the current value of the KPI to the “historical” one stored in the subscription table and sees if we’ve hit our change factor or not

o Sends an email to the user if we have

The POC is rough and makes no attempt at intelligence (grin). For example, we’ll happily send the same alert to a user over and over again when the package is run after we’ve hit the “change factor” for the subscription in question. Extra logic needs to be added to improve real-world usability of this thing.

 

Here are the components:

· A table which holds subscriptions:

USE [Catcher]

GO

/****** Object: Table [dbo].[kpiDemo] Script Date: 10/22/2007 09:24:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[kpiDemo](

                [emailAddress] [nvarchar](50) NOT NULL,

                [kpiName] [nvarchar](50) NOT NULL,

                [kpiCurrentValue] [float] NOT NULL,

                [kpiMovement] [float] NOT NULL,

 CONSTRAINT [PK_kpiDemo] PRIMARY KEY CLUSTERED

(

                [emailAddress] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT KPIDemo VALUES (‘someEmailAddress@domain.com’, ‘Channel Revenue’, 80000000, -0.1) -- Alert someEmailAddress when the Channel Revenue KPI value in AdventureWorks (with a current value of 80M) declines by 10%

· The package itself (attached below)

I had a bear of a time successfully reading KPIStatus, KPIValue, and KPIGoal values in the package. In essence, when I constructed an MDX query and fired it from an OLEDB Data Source or Data Reader, I’d get the following error message:

The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR

I’d find that only two of the three columns I asked for came back, as well. The third (usually KPIStatus) would disappear into the ether. This made me sad and evidently is a known issue. I found a thread about it here but discovered the workaround did not help me:

https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=215075&SiteID=17

I ended up going with a Script Component acting as a source, and used code stolen from this great blog entry to get my values back:

https://blogs.msdn.com/olivier_pieri/archive/2004/12/06/275811.aspx

All standard disclaimers apply – use this at your own risk, it is unsupported and could burn down your house if left running unsupervised.

 

Package.dtsx

Comments

• Anonymous
  November 16, 2007
  OK, you showed SSIS = SQL Server Integration Services but you dont explain POC ...any clues please?
• Anonymous
  November 20, 2007
  POC = proof of concept
• Anonymous
  February 15, 2008
  Great blog entry.I suppose that when you deploy the SSIS package to SQL Server you'd stablished the schedule for it?Have thought of creating another table to store the alerts that have been sent, that way you could control the number of alerts sent by checking if the user has been already alerted.
• Anonymous
  June 04, 2009
  Russell, Thanks for this nice article. I have couple of questions.

1. We have KPIs in  SQL Server Analysis Servcies 2005 and we wnat to send Alerts using SQL Server Notification Servcies 2005. Can we do this, if yes, please share the approcah.
2. In the above scenario, how are you storing KPI value in database or you are reading it using SSIS ?

• Anonymous
  May 26, 2010
  Hi, I would like to introduce an interesting PerformancePoint add-on. Bidoma Alert is a productivity add-on for Microsoft PerformancePoint Server 2007 and PerformancePoint Services for Microsoft Office SharePoint 2010 providing extensive e-mail alerting capabilities. Read more about Bidoma Alert for PerformancePoint and see first screenshots at www.bidoma.com/.../BidomaAlert.aspx For questions feel free to contact me! Best Regards, Don Nakanishi