|Q:I would like to create an e-mail alert when some criteria has been met after processing Analysis Service cube. Any sugestion?|
|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'
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
3. Now you have table AdventureWorks.dbo.EmplSales. If you run query "SELECT * FROM AdventureWorks.dbo.EmplSales", this will be your results:
Now you can use SQL Server stored procedure sp_send_dbmail to send results:
IF (SELECT COUNT(*) * FROM AdventureWorks.dbo.EmplSales) > 0
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).
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?