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.

• 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.

 

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

• 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.

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

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

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

• The Data Alert Designer window will open

• 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

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

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

• 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.

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

• 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.

• 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.

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.

Here is a sample data alert email.

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.

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

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.

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

Use DAX Search Function to Retrieve Subset Data in SSRS Report

Most of you would be familiar with substring search in T-SQL. If you have a SSRS report using DAX, do you ever winder how to do the search using the pass in parameter?

Here is the sample DAX query used to retrieve the Order Count and Sales Amount for Customers from Internet Sales:

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales'    
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Here is the sample result:

 

 

If you only want to return certain customer, you will need to add a filter by pass in the Customer name as the report parameter.  Here is the sample DAX query:

 

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  DimCustomer[CustomerName] = @CustomerName
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Then you could get the result that has exact match to the customer name passed in.

 

If you need to return all the customers that has name like “Peter”, you could use DAX Search function to retrieve the list.  Here is the example:

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

The above query equals to the following T-SQL:

SELECT 
[CustomerName] ,[Gender], [Order Count],[Sum of Sales Amount]
FROM [dbo].[FactInternetSales] F 
INNER JOIN [dbo].[DimCustomer] C
On C.CustomerKey = F.CustomerKey
WHERE C.[CustomerName] like @CustonerName

You can see the result as below.  All the customers with name containing “Peter” got returned.

 

 

Sometime you may also want to handle the situation when user passed in Space or Blank parameter.  Here is a technique you could use.  When Blank customer name entered, the report will return all the customer without any filter.

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IF(@CustomerName = blank(), 1=1, IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0 )
                    )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

Here is the result:

 

 

Reference

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

Setup SSRS email Delivery Option Using Gmail Account

 

In order to use the SSRS email delivery subscription or Data alerts feature, you need to configure the email option. If you don’t have access to the company exchange server, you could configure the system to use a Gmail account instead.  Here are the steps:

Configure a virtual SMTP Server

• Install SMTP Server window option on the local box or a server.
• Open IIS 6.0 manager to configure the SMTP option:
• Click on the Access tab and select Anonymous access option in the Authentication option box

• Click on the Outbound Security option button from the Delivery tab, select the Basic authentication with your Gmail account and enable the TLS Encryption.

• Click on the Outbound connections option button, set the TCP Port number to 587

• Click on the Advance option button, set the Smart Host to use smtp.gmail.com

Now you need to configure the SSRS to use the email delivery method. Depend on the SSRS mode, you will do the following:

SSRS Integrated Mode

Open SharePoint 2013 Central Administration and select the Manage Service applications option

• Click on the SQL Server Reporting Services Service to open the configuration page
• Click on the E-mail Setting link

 

• Enable the Use SMTP server option and set the Outbound SMTP server name or IP address; Set the From address to your Gmail address same as you used in the setup IIS step.

• Click on OK button. You are good to go now.

SSRS Native Mode

Open Report Service Configuration manager.

• Click on the e-mail setting tab
• Type your Gmail address in the Sender Address on the SMTP Settings window
• Set the SMTP Server to the server name as previous step

• Click on Apply button. You are good to go now.

Send a SMS text message from email

If you want to send SMS text for the Data Alert. You could type in the 10 digit number with the mobile provider extension as the example below to send the SMNS text.

 

Reference

http://www.vsysad.com/2012/04/setup-and-configure-smtp-server-on-windows-server-2008-r2/

http://www.nytimes.com/2011/11/03/technology/personaltech/how-to-send-text-messages-by-e-mail.html?_r=0

Trigger SQL Agent Job by Files

 

Wondering how to trigger a SQL agent job when the file is ready to be processed?  The simple and easy way to trigger a SQL Job after a file dropped into a window directory is attaching a SQL alert to the SQL job.

Here are the steps:

1. Open the job property window for the existed SQL agent Job.

2. Click on the Alert Node

3. Click on Add button to open new Alert window

4. Select WMI Alert Event type and set the property for Namespace and query

5. Click on OK to attach it.

Once file dropped in the folder, the SQL job will be triggered. The example I used will insert a row to [dbo].[FileProcessTime] table.

you may go to the Job property->History Page for the Alert  or Query [msdb].[dbo].[sysalerts] table to check the the Number of occurrences and Last execution Time.

Sometime you may consider to delay the response for large or frequent files, you could go into the Option page to set it.

Reference:

http://msdn.microsoft.com/en-us/library/ms190084.aspx

http://msdn.microsoft.com/en-us/library/ms186510.aspx

SQL Saturday #174 Recap

 

What a great success SQL Saturday was in Charlotte last weekend.  I volunteered to be a proctor in the morning, so I could attend and also help out.  My presentation on “Explore to Integration Services Catalog” went well with full house of attendees.  Thanks to all the people who attend my session and everyone in the Charlotte BI group for organizing a great event.

I was asked to post my PowerPoint presentation. 

Here is the link:

Explore the Integration Services Catalog

Use Sybase SQL Anywhere as data source for PowerPivot

To use Sybase SQL Anywhere as the Power Pivot data source, you need to use ODBC. It is not intuitive to set it up.  After encountered several errors, I am able to load the data into PowerPivot to analyze it now.

Here is the list of steps:

• Install SQL Anywhere Client. You may download it from Sybase we: http://www.sybase.com/detail?id=1087327. If you have 32 bits Office product, you need to install 32 bit client.
• Open the Sybase Central App and create a new connection to the Sybase Host and the database you want to load the data from.
• After you test the connection, click on the Save ODBC Data Source menu on the Tools button.

• When the Save as ODBC Data Source window open, type in a name for the ODBC and click on the Save button.

• You could now open the ODBC data source admin to verify it been created.  Please note that if you install 32 bit SQL Anywhere on a 64 bit windows machine, then you will need to use the 32 bit ODBC to view/configure it. You could find it in C:\Windows\System32\odbcad32.exe.

• In Excel, open the PowerPivot, click on the Get External Data from Other Sources on the Home page.
• Select Others(OLEDB/ODBC) from the list to specify the Connection String

• You could type in the following connection string in the Table Import Wizard window:
  Provider=SAOLEDB.12;Data Source=DataSourceName; Persist Security Info=False
   

• Or Click on the Build button to open the Data Link Properties window to select the ODBC just created.

Now, you should be able to load the data from SQL Anywhere data source into PowerPivot workbook.