Create an e-mail alert when some criteria has been met after Processing Analysis Service cube

Source: Internet
Author: User
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_addmediaserver @ 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_add1_srvlogin @ 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]. & [2, 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:

Employeename Salestargetamount Salesamount
Garret R. Vargas 280000 245520.3631
Jae B. Pak 883000 778625.4236
Jillian Carson 714000 611129.0544
Jos é edvaldo. Saraiva 569000 502176.7795
Linda C. Mitchell 894000 851503.5759
Lynn N. tsoflias 399000 335166.417
Michael G. Blythe 849000 728590.3421
Pamela O. ansman-Wolfe 343000 287360.7647
Rachel B. Valdez 366000 338292.5434
Ranjit R. varkey chudustml 707000 595612.6343
Shu K. Ito 614000 488129.3625
Stephen Y. Jiang 84000 72229.6073
Syed E. Abbas 7000 5313.012
Tete A. Mensa-Annan 454000 387635.9604
Tsvi Michael. REITER 538000 483937.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 = 'danw@Adventure-Works.comThis 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 ).

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.