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.