Thursday, September 26, 2013

Load Data from Stored Procedure in TimeXtender 2012

TimeXtender 2012 is a new data warehouse development tool that I’ve been using to rapidly increase the ETL Development.

There are lots of different way to load data into stage area using TimeXtender 2012.  If you are used to use store procedure to gather the related source data, you could still use it in TimeXtender with minimum modification.

Here is an example:

  • Create a store procedure in the Staging area and deploy it.

image

image

  • In the store procedure, Make sure you upsert the result dataset into a table . You may use INTO Clause (T-SQL) to store the result.
  • Create a Script Action to execute the stored procedure.

image

 image

  • Set the script action as either the Pre-Step or the Post-Step for the Business unit using the Business Unit Advanced property menu.

image

image

  • Create a Customer view in the business unit to read in the data from the result set table.  Make sure you Synchronize the view fields after deploy the view to see the columns.

image

  • After Deploy and Execute the business unit, you could right click the view and select preview to exam the result.

image

image

  • Now you could use the stage data to create/populate the data warehouse table using drag and drop action.

image

Thursday, September 5, 2013

Use SSRS Data Alert

 

SQL 2012 Reporting Services has data alerts capability.  It is a data driven alerting solution. You could use it to inform the users about reporting data that is important to them at a relevant time through email or SMS format.

Here are the steps to create a data alert:

  • Open the report that you want to create the data alert.
  • Click on the New Data Alert on the Actions drop down menu

image

  • The Data Alert Designer window will open

image

  • Edit the Alert Name
  • Create the Alert Rules that are applicable/interested by the users by clicking on the (Add rules…) on the right side pane. 
  • Select and set the data feed columns from the list that you will use to create the rule i.e. Low Water Count.Value

image

  • Set the threshold i.e. is greater than 30

image

image

  • You may change the option from Alert me if any data has:  to Alter me if no data has:

image

  • Set the the time frequency that you want the alert to be sent at Schedule settings session.  Click on Advanced option to set the stop date or option for sending message only the alert result changed.

image

  • Set the message recipients and other information in the Email settings session.

image

  • Click on Save button to create the alert.

 

Manage the Data Alert

Once the data alert been created, you could manage/edit it using Data Alert Manager.

  • To open the data alert manager, right click on the report and click on the Manage Data Alert menu.

image

  • Right click on the alert name, you could select Edit or Delete the alert to modify it.
  • You could also select Run option to execute the data alert.

image

Data Alert Manager also provides alert histories and status for review. When condition met, the system will send out the e-mail message and you will see the Sent Alerts Count increased.

image

Here is a sample data alert email.

image

It provides the actual data alert result and rules/report parameters used for the SSRS report.  In addition, there is a hyperlink in the message for use to review the report detail as needed.

Setup SMS Alert

If you like to send out the data alert to the mobile device with SMS format, you could use the SMS through email option. Different provider uses different SMS gateway.  Make sure you use the correct gateway address.  For example, if you have AT&T mobile number, you could set the PhoneNumber@mms.att.net or phoneNumber@txt.att.net in the recipients address.  When the data alert condition met, it will send a big text message to that phone number.

image

Depended on the provider you used, it is likely to be several of them. Here is a sample:

image

Requirements

To use the SSRS data alert, you must have the followings:

  • SQL 2012 SSRS integrated mode.  It could be integrated with SharePoint 2010 or SharePoint 2013.
  • Enable SQL Server Agent
  • Enable SSRS email delivery option.
  • Need Manage Alert SharePoint permission to setup/manage the data alert.
  • Use stored credentials or no credentials for report data source.

image

If the New Data Alert option is grayed out, the report data source must be configured to use either integrated security credentials or prompt for credentials. To enable the option, you must set the data source to use stored credentials or no credentials.

Reference

http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

http://technet.microsoft.com/en-us/library/gg492252.aspx

http://en.wikipedia.org/wiki/List_of_SMS_gateways