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

Using Page and CAML Query to Extract SharePoint List in SSIS

 

There are several ways to read SharePoint List as data source in SSIS.  The easiest way is using the SharePoint List Adapter.  You could download it from the Codeplex site.  However, you may want to have more control during extracting the list to avoid failure, to manipulate data before sent to the output rows, or you are experiencing timeout issue with lots of item in the list. 

Here is the solution I used:

Add For Loop Container in Task Component with a Data Flow

• Since the SharePoint list could be large, use the for loop container to loop through the paging.

• Set InitExpression to the Page number @PageCount = 1
• Set the EvalExpression to a Variable: @LoopNext  which will be determine in the data flow.
• Set the AssignExpression to be @PageCount = @PageCount + 1

 

Create a Script Component  with Source Type to Read SharePoint List

• In the data flow, add a Script Component with Source type.

• Pass in @LoopNext  and @SharePointPageInfo variables as Read Write Variables.
• Pass in @SharePointSite, @SharepointListname, @SharePoinbtView, @SharePointRowLimit variables as Read Variables.
• Create two Outputs. One for the default SharePoint list data set, the other will hold the error data.
• Click Edit Script… button to add .net code
• Right click the project to add Server Reference that point to the SharePoint Site

• In CreateNewOutputRows(), instantiate the SharePoint Web Service List object
• Create several XMLNode objects as the parameters to call System.Xml.XmlNode GetListItems()

//Retrieve the list of available fields from the GetList call to Sharepoint.

Lists SPS = new Lists();

SPS.UseDefaultCredentials = true;

SPS.Url = strSharepointURL;

//Create XML Node

XmlDocument xmlDoc = new System.Xml.XmlDocument();

XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query","");

XmlNode ndListItems = null;

ndListItems = SPS.GetListItems(strListName, strViewName, ndQuery, ndViewFields, strRowLimit, ndQueryOptions, null);

• Once the result gets back, iterate through the XML nodes to populate the default output data row.
  
• If there is an exception thrown
  
  
  
  • Set the @LoopNext to false which will stop the For Loop container
    
  • Add a row that contains the exception message to error output.

catch (System.Web.Services.Protocols.SoapException ex)

     {

         string strError = "Message:" + ex.Message + "\nDetail:" + ex.Detail.InnerText + "\nStackTrace:" + ex.StackTrace;

         ErrorBuffer.AddRow();

         ErrorBuffer.ErrorData = strError.ToString().Substring(0, strError.ToString().Length > 4000 ? 4000 : strError.ToString().Length);

         bLoopNext = false;

     }

• In the data flow, sent the SharePoint list items to the destination table and the error data to error table. This way the SSIS package will not fail to execute with any SharePoint Site issue. Other process could utilize the data in the error table to recover it softly.
  
• In the Add Metadata component, add the @PageCount as an output column. It could be used for recovery or debug purpose.
  

 

Narrow Down the Needed Fields

Don’t select all the fields from the list. Sometime the list may contain binary data, meta data or image that may take long time to load. To narrow down the fields, just specify the fields in the ViewFields XML Node as:

XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

//Get List of Field needed

ndViewFields.InnerXml = "<FieldRef Name='ID' />";

ndViewFields.InnerXml += "<FieldRef Name='Created' />";

ndViewFields.InnerXml += "<FieldRef Name='Author' />";

ndViewFields.InnerXml += "<FieldRef Name='Editor' />";

ndViewFields.InnerXml += "<FieldRef Name='Modified' />";

Add CAML Query to Extract Only the Delta

To pull only the delta records in, just add filters in the query. The SharePoint list has a system field called “Modified” which will be time stamped when the record get changed. You could utilize the CAML query to get the items that have Modified Date greater than a Date value or within an arrange of date. If you don’t know the syntax of the CAML Query, you could use some SharePoint Utility such as U2U CAML Builder.

Here is a sample CAML Query that will filter the result by range of date:

<Where><And>

<Geq><FieldRef Name="Modified" IncludeTimeValue="TRUE" /><Value Type="DateTime">2012-01-01 00:00:00</Value></Geq>

<Leq><FieldRef Name="Modified" IncludeTimeValue="TRUE" /><Value Type="DateTime">2012-01-31 23:59:00</Value></Leq>

</And>

</Where>

Page though Large Number of List Items in the Script

Depend on the size of the item, you may set the row limit to 1000 while calling the GetListItems().  The function supports service-side paging. When the XML result returned, it includes a ListItemCollectionPositionNext attribute that contains the information to support the paging.  You need to save it for the next call.  Make sure you do not modify the string. 

In the example below, I set the ListItemCollectionPositionNext attribute to a local variable first and the bLoopNext to true. I then save the string to a SSIS Read/Write variable to be use for next loop.

//Get page related meta data

XDocument meta = XDocument.Parse(ndListItems.InnerXml);

if (meta.Root.Attribute("ListItemCollectionPositionNext") != null)

    {

       strPage = meta.Root.Attribute("ListItemCollectionPositionNext").Value;

       bLoopNext = true;

    }

else

    {

       strPage = string.Empty;

       bLoopNext = false;

    }

When next loop starts, I set the saved attribute string to the QueryOptions parameter to be used to fetch the next set data back.

//Set page Query option

if (strPage.Length > 0)

    {

       ndQueryOptions.InnerXml = @"<Paging ListItemCollectionPositionNext='' />";

       ndQueryOptions.ChildNodes[0].Attributes["ListItemCollectionPositionNext"].InnerText = strPage;

    }

else

    {

        ndQueryOptions.InnerXml = "";

    }

 Result

After executing the SSIS package, you can see the SharePoint List been extracted page by page as below:

 

The PageCount column stores the SharePoint List page number.

Reference:

http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx

http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

Publish a BISM Tabular Model Database Connection on SharePoint 2010

Once you built a BISM Tabular Model project and deployed to SSAS,  you may want to publish it on the SharePoint 2010. A BISM connection allows business end users to access the underline model on SharePoint with addtional security control. It could also utilize the SharePoint quick launch commands to open the Excel Workbook or Power View Report.  For more information about how to create a BISM Tubular Model project, see my previous blog entry Create a SSAS BISM Tabular Model Project.

 

Add the BI Semantic Model Connection Content Type to the SharePoint Library

• Go to the SharePoint Library site that will host the connection file, click the Library in the Library Tools.
• Click the Library Settings
• In the General Settings section, select the Advanced settings

• In the Content Types, Select the Allow management of content types to be Yes, then click OK
• In the Content Types section, Click on the Add from existing site content types
• Add the BI semantic Model Connection

• After click on the OK button, you can see the BI Semantic Model Connection added in the Content type table

 

Set Up Permissions

• Grant the Tubular Model Analysis Service administrative permissions to the SharePoint Service Account.
• Grant the user who is going to use the connection with the Read permission on the Tabular Model database
  
  • Add a Role with Read permission
  • Adding the user to the Role

 

Create the Connection File on SharePoint

• Go to the library page, click on the Documents in the Library Tools.
• Click on the down arrow on the New Document and select the BI semantic Model Connection.

• On the New BI Semantic Model Connection page, specify the server name and database name and then Click OK.

• On the library page, you should be able to see the new connection file.

Use the BISM Connection in SharePoint

Now you may use Excel or Power View to consume the BISM connection file as the data source. 

• If you create the file in PowerPivot Gallery, you could click on either the Open New Excel Workbook or Create Power View Report link on the right upper corner to open the application.

• If you create the file in the Shared Documents page, you could click on the Down Arrow on the File Name to launch either the Excel or the Power View Report.

Additional Reference

http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx
http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx

Create Reports Using PowerPivot Data Source

You can create new SSRS reports, Excel reports, or PPS Dashboard reports based on PowerPivot workbooks that are published in a SharePoint PowerPivot Gallery. PowerPivot data access on a server is supported through SharePoint 2010 with SQL Server 2008 R2.

SSRS Reports

You can use either Report Designer in BIDS or Report Builder to access the PowerPivot data.  The Reporting server could be in native mode or in SharePoint integrated mode.  Not like the Excel reports which include embedded data, SSRS reports always access PowerPivot data as an external data source.

In order to support PowerPivot connections from Report Designer, you must have the SQL Server 2008 R2 OLE DB Provider for Analysis Services installed.  Windows authentication is also required to integrate between the SSRS server and the PowerPivot server.  More information could be found at http://msdn.microsoft.com/en-us/library/ee210631.aspx.

Steps for SSRS Reports:

• Create a new Report Server Project in Visual Studio 2008 or BIDS
• Add new data source in Share Data Source

• Using the Microsoft SQL Server Analysis Services Type with Connection String pointed to the SharePoint location for the PowerPivot spreadsheet, for example: Data Source="http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx"

• Click on the Credentials tab to set the Authentication to be Windows and click on OK button.

• Add a new report and select the Share data source just created, then click on Next.

• Click on the Query Builder button

• The Query Designer will be opened with a Sandbox cube

• You now should be able to work on your MDX query to get your result as needed.

 

Excel Reports

You could also create new Excel reports to access the PowerPivot workbooks that you have permissions with.

Steps for Excel Reports:

• Open Excel, go to Data tab and select From Data Connection Wizard on Other Sources menu

• On the Data Connection Wizard window, select the Other/Advanced option and click on Next button

• Select the Microsoft OLE DB provider for Analysis Services 10.0 and click on Next

• Enter the data source in the Data Source textbox as : http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx and click OK

• The Select Database and Table window will then be opened with Sandbox Cube been selected to be used. Click on the Next to save the data connection file and finish the wizard. Here’s what the connection string may look like:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=PowerPivotDemo Sandbox 6042a0d5-355c-47ed-b24c-d458bfecd529;Data Source=http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Data Source Version=5/9/2011 5:27:11 PM

   

• You now should be able to use the Data from the PowerPivot data source to create your excel report

 

Performance Point Server Reports

Performance Point Server on SharePoint 2010 also supports PowerPivot data source.

Steps for PPS Reports:

• Open PPS Designer and connect to the SharePoint site
• Create a new Analysis Service data source

• Using the following connation string: Provider = molap; datasource = http://SharePointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx
• In the Cube drop down box, select the sandbox cube

Since Excel Pivot Tables that go against PowerPivot data do not support the write back, offline cubes, and drill through,  there are some known issues when using the PowerPivot data source in PPS reports such as:

• Show Details error

• Error running data source query. DrillThrough is not supported because the server is running in VertiPaq mode.

 

 

Additional References:

PowerPivot Connection Type (SSRS)

Plan for PowerPivot Integration with Reporting Services

PowerPivot Features

ErrorCode Enumeration

Enhance PowerPivot Cube using Visual Studio 2008 R2

PowerPivot is a self service BI tool that you may use it to prototype your work quickly. The SQL Server Analysis service that PowerPivot cube used is a VertiPaq storage mode which is different from the traditional  MOLAP, ROLAP or HOLAP cube. It is a read only temporary cube that PowerPivot Service creates and manages. You may persist or enhance it using Visual Studio 2008 (SQL Server 2008 R2). 

Here are the steps to enhance it using Visual Studio:

• Backup and Restore the PowerPivot Sandbox database with a different name on the VertiPaq Mode SSAS.
• Open the Analysis Service Database option to point to the restored database from Visual Studio to create a new project.

  

• Once the project is created, you could see all the dimensions and a cube called Sandbox in the project.

  

• The Data Sources contains a connection string that connects to the original data source used by the PowerPivot workbook.
• The Data Source Views contains all the tables from the PowerPivot workbook as Named Queries.
• Since it is a read only cube, if there is any modification or trying to process the cube, you will get the following error

• To overcome the read only issue, you need to detach and then attach it by unselecting the Read-only checkbox to change the ReadWrite mode to be ReadWrite.

 

• Now you should be able to add a new MDX or DAX measure and then deploy/process it.

• You could view your result in both the Visual Studio or SSMS.

• If you try to restore the PowerPivot Cube on a traditional SSAS Server, you will encounter the following error: Backup and restore errors: Restore failed. The backup file was created on a server with DeploymentMode=1 and the current server is running DeploymentMode=0. (Microsoft.AnalysisServices)