Q:I would like to create an e-mail alert when some criteria has been met after processing Analysis Service cube. Any sugestion?
User Rating: / 1
PoorBest 
Written by Vidas Matelis   
Tuesday, 06 January 2009 01:57

Q: I would like to create an e-mail alert when some criteria has been met after processing Analysis Service cube. Any sugestion?

A: Analysis Services does not have "alert" functionality. SSAS cannot send e-mail, so your best bet is to delegate that functionality somewhere else. My suggestion was to use SQL Server.

Here are sample steps:

1. Create linked server from SQL Server to your Analysis Services:

EXEC master.dbo.sp_addlinkedserver @server = N'VIDAS-LENOVO-ADVENTURE'
 , @srvproduct=N'MSOLAP'
 , @provider=N'MSOLAP'
 , @datasrc=N'VIDAS-LENOVO'
 , @catalog=N'Adventure Works DW'
go
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VIDAS-LENOVO-ADVENTURE'
 , @useself=N'False'
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
go

2. Get data from your SSAS database that will trigger e-mai.

Example, following query will get from Adventure Works Employee Actual Sales and Targeted Sales Amounts. Just records where Targeted Sales is more than Actual sale will be returned. You will save results in the table AdventureWorks.dbo.EmplSales:

SELECT [[Employee]].[Employee]].[Employee]].[MEMBER_CAPTION]]] AS EmployeeName
          ,[[Measures]].[Sales Amount Quota]]] AS SalesTargetAmount
          ,[[Measures]].[Reseller Sales Amount]]] AS SalesAmount
  INTO AdventureWorks.dbo.EmplSales
 FROM OPENQUERY([VIDAS-LENOVO-ADVENTURE]
    , 'SELECT {[Measures].[Sales Amount Quota], [Measures].[Reseller Sales Amount]} ON 0
                , NON EMPTY [Employee].[Employee].Children ON 1
         FROM [Adventure Works]
       WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[1])' ) AS M
 WHERE [[Measures]].[Sales Amount Quota]]] > [[Measures]].[Reseller Sales Amount]]]

3. Now you have table AdventureWorks.dbo.EmplSales. If you run query "SELECT * FROM AdventureWorks.dbo.EmplSales", this will be your results:

EmployeeNameSalesTargetAmountSalesAmount
Garrett R. Vargas280000245520.3631
Jae B. Pak883000778625.4236
Jillian Carson714000611129.0544
José Edvaldo. Saraiva569000502176.7795
Linda C. Mitchell894000851503.5759
Lynn N. Tsoflias399000335166.417
Michael G. Blythe849000728590.3421
Pamela O. Ansman-Wolfe343000287360.7647
Rachel B. Valdez366000338292.5434
Ranjit R. Varkey Chudukatil707000595612.6343
Shu K. Ito614000488129.3625
Stephen Y. Jiang8400072229.6073
Syed E. Abbas70005313.012
Tete A. Mensa-Annan454000387635.9604
Tsvi Michael. Reiter538000483937.8403

Now you can use SQL Server stored procedure sp_send_dbmail to send results:

IF (SELECT COUNT(*) * FROM AdventureWorks.dbo.EmplSales) > 0
BEGIN
 EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AdventureWorks Administrator'
, @recipients = ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it '
, @query = 'SELECT * FROM AdventureWorks.dbo.EmplSales'
, @subject = 'Employess that did not reach target'
, @attach_query_result_as_file = 1 ;
END

Of course, before starting to send e-mails, make sure that you setup your mail profiles. There are a lot of articles on the web that tells you how to do that.

When you have this SQL Code, you can add it as a separate SQL step after you finished processing (for example in SQL Server Jobs).

 
Comments (1)
1 Monday, 02 February 2009 02:10
Darren Gosbell
I would make a slight change to this example. I would not use the SELECT ... INTO syntax in production as this can cause blocking on the sysobjects table for the length of the MDX query. I would create the table first and use a normal insert instead.

Tags: alert, mdx
 

Pyramid Analytics