SSIS – Configuring SSIS Package to run job after data loading has completed

  • In our example below, we are going to be using a SharePoint 2013 Data Alert job which was created in SQL Server Agent, and then insert this into an existing
    SSIS
    Project.
  • The reason for doing this, is so that every time our SSIS Project runs, as part of the process, it can then fire off a SQL Server Job.
  • Essentially in the past the SQL Server Agent Jobs run
    individually and are not tied into when our data has loaded.
  • By doing it in this manner, it enables the related job to be run after every data load. Which then enables the business to get the relevant data at the relevant times.

     

NOTE: You can modify this for any SQL Server Job.

Find the related SQL Server Agent Job Name

Below details how to find the related SQL Server Agent Job name. As typically when you create a SharePoint Data Alert, or SQL Server Reporting Services (SSRS) Data
Driven Subscription or Email Subscription, it creates the job name with a GUID.

This makes it rather difficult to find the correlating Job Name.

  1. The easiest way to find the SQL Server Agent Job Name, is once you have created your SharePoint Data Alert, or SSRS (Data Driven Subscription or Email Subscription) is to go directly into your SQL Server Agent Job Monitor where the Job was created.
  2. Then open the Job Activity Monitor
  3. Then scroll through the list until you find a job with the following
    properties
    1. The name will be in a GUID format:

    2. The Last Run Outcome is set to Unknown
      1. And the Last Run is set to never

    3. NOTE: If there is more than one job with the above properties, then look to see when you scheduled the start time of your
      job.
      1. If that still does not help you might have to go into the job
        properties and into the schedules to see how often it runs to find the correct job.
  4. If this is in a Test
    environment, you can try and run the job to ensure that you do have the correct job.
  5. Make a Note of the Name of the Job
    1. In our example it was: 68CAE336-3D38-42A6-9526-F32F4D501AA4

 

Modifying the SQL Server Agent Job

Below are the steps to modify the SQL Server Agent job, so that it will not run on a schedule.

NOTE: The reason we are doing this is because we want the job to be run from within our SSIS Project after it has loaded our relevant data.

  1. Go into the properties of your SQL Server Agent Job and click on the Schedules
    Page

  2. Then in the Schedule
    List
    click on the Schedule and select
    Remove.
  3. Once completed there will now not be any schedules for the job.

  4. Then click Ok.

 

Creating and configuring your SSIS Package to run the SQL Server Agent Job for you

Below are the steps to create and configure your SSIS package to run the SQL Server Agent Job as part of the SSIS Project.

  1. Create your new SSIS Package.
  2. Drag in an Execute SQL Task
  3. We renamed our Execute
    SQL
    Task to the following:

    Run SharePoint Data Alerts


  4. Then we went into the properties.
    1. We then put in our Connection to our SQL Server Database, which is the same as the where the SQL Server Agent Job is
    2. Then under the SQL Statement we put in the following:

      EXEC msdb.dbo.sp_start_job N’68CAE336-3D38-42A6-9526-F32F4D501AA4′ ;

      1. NOTE: The thing to note here is that we are using the MSDB
        Stored
        Procedure to start our Job.
        1. And that we are using the Job
          name which we noted in previous steps.
  5. Now that you have completed your Execute SQL Task, it is time to test it.
  6. Right click and select Execute SQL Task.
  7. If successful it should come back looking like the following below:

  8. As well as actually getting the email in your Inbox, as is the case with our SharePoint Data Alert.

For SQL Server 2014 – Use the following link below to use an oData Source to get data from SharePoint List into your table

Below are the steps if you want to use SQL Server 2014 to get data from a SharePoint list into your database.

The URL below explains how to download and install the OData Source for SQL Server

1.       Click on the following link below

a.       http://www.microsoft.com/en-us/download/details.aspx?id=42295

2.       Then once you load the page click on Download

a.       This will then bring up which components you want to download.

b.       Scroll down and select the following:

c.        clip_image001

3.       Then download and install the component.

a.       You can just accept all the defaults in the Wizard.

4.       Then if you now open SSDT, and drag in a new Data Flow Task, then click on the Common Section you should see the following:

a.       clip_image002

5.       NOTE: We would want to use the X86 version for our testing in SSDT, due to SSDT being a x86 version.

6.       NOTE II: You will also have to install the component on your SSIS Server and configure your SSIS package to run in 32bit mode, so that when it is run as part of the job in SSIS it will function correctly.

Configuring your OData Source in SSIS

NOTE: You might have to create your SQL Table, to get the data from your SharePoint List into the table before you complete the steps below. Or you can create the table after step 2 below.

 

1.       Once you have followed the steps above, the one thing to note is how to get the correct URL so that we could create our connection to our Lists

a.       With the example below our URL was:

http://Server/sites/testingdemo

b.       Now in order to get the URL for the lists, we had to put in the following:

                                                               i.      clip_image003

                                                             ii.      NOTE: In your Site, you just need to add the following:

/_vti_bin/Listdata.svc

                                                            iii.      Also ensure the account that you use has access to the SharePoint List.

c.        And then you have your connection completed.

d.       After which you can then select your List from the drop down in your

2.       And then we configured our OData Source with the following:

a.       clip_image004

b.       Under Use collection or resource path, ensure that you select Collection.

c.        Under Collection, click on the drop down, and ensure that you select your SharePoint List that has been created in SharePoint

                                                               i.      In our example it was called: CrimeReportingSubscriptions

d.       You can then click on Preview to see that you are getting the expected data.

e.       Then if you click on Columns you will then see all the SharePoint related columns, as well as the columns from your List

f.         clip_image005

g.       NOTE: Ensure that you do have some data already in your list.

h.       Then click Ok to have your OData Source completed.

3.       Then once completed it will look like the following in your Data Flow

Sharepoint 2013 – refreshing excel workbook with direct connection to sql server analysis services (SSAS) cube

I have not blogged in a while, due to moving countries and starting a new job. So i do hope that this blog will help someone or let them know how easy it is to use SharePoint 2013 to refresh data in an Excel spreadsheet.

What I needed to do, was to use an existing Excel spreadsheet which connected directly to an SSAS cube. We then wanted SharePoint to manage the refreshing of the data. In the past I thought that this was only applicable to Power Pivot Excel workbooks, but after today I realized that you can do this directly to your SSAS cube.

 

Getting the location of where you will store your Data Connections in SharePoint

The first thing that you need to do, is to ensure that you have the location of where you want to store your Connection File in your Excel workbook.

You will also need to have the Data Connections created in your SharePoint site.

 

Example:

·         In our Example we are going to be connecting our Existing Excel Workbook, to a SQL Server Analysis Services (SSAS) cube, using a data connection that is stored within SharePoint.

·         Once we have created our connection, we are then going to use the PowerPivot Refresh within SharePoint to refresh our Excel Workbook from the cube.

 

Assumptions:

·         This is based on SharePoint 2013 Enterprise Edition and SSAS 2012

·         We are going to assume that you have already got your SharePoint site set up.

·         We are also going to assume that you have already created your Excel Workbook, which connects to a cube.

·         We are also going to assume that you have either created a Documents Library, or are going to use an existing Documents Library.

·         And then you have uploaded your Excel Workbook to your documents library.

 

 

NOTE: You will be required to have Owner rights to do the following below within your SharePoint site.

 

1.       Log into your SharePoint site and click on Site Settings

2.       Then click on Data Connections

a.       clip_image001

3.       Once this opens you will need to copy everything before the /Forms/AllItems.aspx

4.       As with our example we copied the following:

http://SharePointBIWebSite/sites/wcsa/Data%20Connections

5.       Now either save the above link or copy the link which will be used in the next steps.

 

Changing our Excel File to use the stored connection within SharePoint

1.       Open your Excel File from your SharePoint location

a.       NOTE: The easiest way is to navigate to where you have uploaded your Excel file and then say Open in Windows Explorer

2.       Then open it in Excel

3.       Then click on Data, and click on Connections

a.       clip_image002

b.      Then click on Properties

4.       Once the Connection Properties Window opens click on Definition

5.       Next what you need to do is where it says Connection Name, change this to something more meaningful and possibly shorter than the default.

a.       We changed ours to the following name:

b.      clip_image003

6.       Now at the bottom where it says Export Connection File click on the button

a.       clip_image004

7.       This will then open the File Save Window

a.       Now at the top where it asks you the location of where you want to save the file click on the Drop down in the Address Bar and put in the URL which we either saved or Copied in Step 4  above and paste it:

                                                               i.      clip_image005

                                                             ii.      Now where it says File Name you can either leave this with the default, but what I recommend is changing it to then match your Connection Name, as we did with our example:

1.       clip_image006

                                                            iii.      Then click Save

b.      This will then open the Web File Properties Window, where it asks for some more information.

                                                               i.      Once again we checked to ensure that our Title Matched our Connection File Name

                                                             ii.      clip_image007

c.       Then click Ok.

8.       Now when you go back to your Connection Properties Window you will now see that your connection File has changed to the location of our ODC which is saved to your SharePoint data connections site.

9.       The next thing that you need to do is to make sure you put a tick in the box, “Always use connection File

a.       NOTE: This is so that whenever and where ever the Excel spreadsheet is used it will always use this connection file

b.      NOTE 2: This is so that when it is uploaded or run from SharePoint it will then use the associated ODC file.

c.       clip_image008

10.   The next thing that you need to do, is to configure the Excel Services Connection.

a.       NOTE: This is required as part of SharePoint so that it can use an Excel Services connection to make the authentication to the SSAS Cube to actually refresh the data.

b.      NOTE 2: You will have to ensure that the person responsible for your SharePoint installation has configured the Secure Store Service (SSS), as well as that the domain account that is linked to the SSS has access to the SSAS cubes.

c.       Click on the Authentication settings.

                                                               i.      Now in the Excel Service Authentication Settings screen configure it with the following below

1.       clip_image009

2.       NOTE: The name of our SSS ID is ExcelDataConnection

                                                             ii.      Then click Ok.

11.   Now when you click OK it is going to go and refresh all your sheets within your current Excel Workbook.

12.   You can now save and close your Excel Workbook.

 

Configuring your Data Refresh in SharePoint for your Excel Workbook

In the steps below we will now configure our Excel Workbook to have a scheduled refresh, so that when people open it up it will have the latest data based on the refresh.

 

1.       Now go to SharePoint where you have got your Excel spreadsheet uploaded.

2.       Click on the Open Menu Ellipses, then the Ellipses again, and finally Manage PowerPivot Data Refresh

a.       clip_image010

3.       This will then open the Manage Data Refresh web page.

a.       You can then configure it with the following:

b.      Under Data Refresh, you must Enable it.

                                                               i.      clip_image011

c.       You can then select  your Schedule Details based on your requirements

                                                               i.      NOTE: If you want to test this now, you must select the Also refresh as soon as possible tick box

                                                             ii.      clip_image013

d.      For the Earliest Start time select when you want the data to be refreshed.

                                                               i.      clip_image014

e.      For the E-mail notifications, this is for people you want to notify if the refresh fails.

                                                               i.      clip_image015

f.        Under Credentials, this is where you MUST specify the same SSS that you configured in your Excel Authentication settings

                                                               i.      As with our example we put in the following:

                                                             ii.      clip_image016

g.       Then finally for the Data Source, it should be configured with your Existing Data source you configured earlier

                                                               i.      clip_image017

h.      Then click Ok.

4.       Now if you selected step 3c above, you can wait a few minutes and see if it worked by going back into Manage PowerPivot Data Refresh and you should see the following:

a.       clip_image018

5.       Now you have completed the data refresh when connecting to an SSAS Cube via SharePoint

Framework for Automating SQL Server Reporting Services (SSRS) data driven subscriptions – Part 1

Description: What the goal was to find a way to automate how an end user could subscribe to a SSRS Data Driven Subscription without any intervention from the Business Intelligence Developer. And also enable the end users to have their own selections. And finally if they did not want to receive the report any longer to then delete their subscription.

In Part 1 we will be creating the SharePoint List, and using SQL Server Integration Services to get the data into our table.

·         What we will be doing below is to first create a SharePoint List

o    NOTE: This is created on SharePoint 2013

§  And for SSIS we will be using SQL Server 2012

·         Then we will use SSIS and a new Data Flow Task to get the data out of our SharePoint List and put this into a SQL server table.

·         After which we will then transform some of the data and then store it in a Dimension table.

o    This dimension table will then be used later in a SSRS Data Driven subscription so that we can email the report out.

 

Example for documentation:

·         We are going to create our List on SharePoint 2013

o    Within our List we are going to have the following fields:

§  Subscription Name

§  Date Range

·         We will specify custom Date Ranges

§  Product Line

·         We will pre-populate the Product Line list

§  Email Address

·         All the above information will be coming from the AdventureWorks2012DW SQL Database

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

Creating SharePoint 2013 List

1.       In your SharePoint 2013 website click on the Site Contents:

a.        clip_image001

2.       Then click on add an app

a.        clip_image002

3.       Then click on Custom List, this is so that we can create our own Custom List

a.        clip_image003

4.       This will bring up the Adding Custom List Window

a.        As with our example we created the following Custom List Name:

                                                               i.      clip_image004

b.       ClickCreate

5.       Now click on your Custom List you created to go into your Custom List

a.        clip_image005

6.       Now at the ribbon on the top click on List and then List Settings on the right hand side

a.        clip_image006

b.       This will then take you to the List Settings

7.       As per our Example explained above we are going to be creating the following columns detailed below by doing the following.

8.       Under Columns click on Create column:

a.        clip_image007

b.       First we created our Subscription Name by clicking on Title and changing it to the following:

                                                               i.      clip_image008

                                                              ii.      Click Ok to create the column

c.        Click on Create Column again to create the Date Range.

                                                               i.      NOTE: We are going to create names for our Date Range which we will convert to actual dates when we insert the data in SSIS in the later steps.

1.       The choices we are going to create are the following due our data having actual data from July 2007 to June 2008

2.       Ranges:

a.        July 2007

b.       July 2007 – December 2007

c.        July 2007 – June 2008

3.       clip_image010

4.       Click Ok to create

d.       Click Create column again for the Product Line.

                                                               i.      Once again it will be a choice of the following:

1.       Accessory

2.       Mountain

3.       Road

4.       Touring

                                                              ii.      clip_image012

                                                            iii.      Click Ok to Create

e.       The final column to be created is the Email Address column as shown below:

                                                               i.      clip_image013

                                                              ii.      Click Ok to Create.

9.       So once completed you will see the following:

a.        clip_image014

10.    Next we will use SSIS to bring the data down into a SQL Table.

 

Downloading and installing the SSIS component to query data from SharePoint Lists

1.       Click on this link below and download the following:

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

2.       clip_image015

3.       Once it has been downloaded, you can run the MSI and complete the steps.

a.        NOTE: You will have to close down and open up your BIDS (Business Intelligence Development Studio) or SSDT (SQL Server Data Tools) in order to see the SharePoint List data.             

 

Creating the SSIS Package to get the data from the SharePoint list into a SQL Server Table.

1.       Create a new SSIS Package and give it an appropriate name.

a.        As with our example we named it the following:

b.       clip_image016

2.       The next thing that you need to do is to create a connection to your SharePoint Server by doing the following:

a.        Right click on the Connection Managers and select New Connection.

b.       Scroll down and select the following:

                                                               i.      clip_image017

c.        Then click Add which will bring up the SharePoint Credential Connection Manager Editor.

d.       As with our example we put in the following:

                                                               i.      clip_image018

                                                              ii.      NOTE: Make sure that the SQL Server Agent Job service account has the permissions to SharePoint.

1.       Alternatively use a Custom Credential which will require access to the SharePoint List.

2.       In our environment we have got a domain account who’s password does not change that we use.

3.       Next drag in a Data Flow Task and as with our example we gave it the following name:

a.        clip_image019

4.       Now go into your Data Flow Task and configure it with the following:

a.        Click in the SSIS Toolbox and under Common you will see your SharePoint List Source

                                                               i.      clip_image020

b.       Drag this into your Data Flow Task and configure it with the following:

                                                               i.      Double click to go into the Properties

                                                              ii.      On the connection Managers Window you have to select your connection Manager:

1.       clip_image021

                                                            iii.      Then click on the Component Properties and put in the following:

1.       clip_image022

a.        This is so that it will look for all the SharePoint Lists

2.       clip_image023

a.        This will be the actual Root of your SharePoint location

b.       NOTE: It must NOT be the actual URL To your list, if you do this it will not find the List.

3.       Then scroll up to the Site List Name and in here you will put your List that you created above.

a.        As shown below is our example:

b.       clip_image024

                                                            iv.      Now click on Column Mappings and if the above settings are correct you should see all the columns as shown below:

                                                              v.      clip_image025

c.        Then click Ok to complete your SharePoint List Source

5.       Next is to create the destination and for this you need to create your destination table first.

6.       Below is the scripted out table that we used:

CREATETABLE[dbo].[Staging_tb_SharePointList](

       [ID][int]NOTNULL,

       [Date Range][nvarchar](255)NOTNULL,

       [Product Line][nvarchar](255)NOTNULL,

       [Email Address][nvarchar](255)NOTNULL,

       [Subscription Name (LinkTitle)][nvarchar](255)NOTNULL,

       [Subscription Name (Title)][nvarchar](255)NOTNULL,

       [Subscription Name (LinkTitleNoMenu)][nvarchar](255)NOTNULL,

       [ContentType][nvarchar](255)NOTNULL,

       [Modified][datetime]NOTNULL,

       [Created][datetime]NOTNULL,

       [CreatedBy][nvarchar](255)NOTNULL,

       [ModifiedBy][nvarchar](255)NOTNULL,

       [Version][nvarchar](255)NOTNULL,

       [Attachments][int]NOTNULL,

       [Edit][nvarchar](255)NOTNULL,

       [Type][nvarchar](255)NOTNULL,

       [ItemChildCount][nvarchar](255)NOTNULL,

       [FolderChildCount][nvarchar](255)NOTNULL,

       [AppCreatedBy][nvarchar](255)NOTNULL,

       [App Modified By][nvarchar](255)NOTNULL,

 CONSTRAINT[PK_Staging_tb_SharePointList]PRIMARYKEYCLUSTERED

(

       [Created]ASC

)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]

)ON[PRIMARY]

 

GO

a.        NOTE: Some of the columns from the source might be set to nvarchar(4000) but I know that we would not have anything this long so we left them all at 255 to save space.

7.       Next we dragged in our OLE DB Destination:

a.        NOTE: You would have to have created a Connection to your Destination Database

b.       clip_image026

c.        As you can see we configured it with the following:

                                                               i.      clip_image027

d.       And mapped the columns as required.

                                                               i.      NOTE: Ensure that all the mappings are correct

8.       Once done it should look like the following:

a.        clip_image028

b.       As noted above the reason for the warning is due to some columns from the source having nvarchar(4000)

9.       Now the final step is to run it and see if it works as expected

a.        But before doing this first add an item to the list:

b.       clip_image029

c.        ClickSave

10.   Now run your data flow task and you should hopefully see 1 row transferred

a.        clip_image030

 

Creating the Stored Procedure to insert the data into the Dimension (storage table) – To be used in Data Driven Subscription

Below is the Stored Procedure (SP) so that we can store the details from the SharePoint List and create some additional column.

The destination table will also be used later in the Reporting Services data driven subscription.

 

1.       Below is the SP which you can use to create your SP.

a.        NOTE: You will have to first create the destination table, which you can complete within the SP.

USE[AdventureWorksDW2012]

GO

/****** Object:  StoredProcedure [dbo].[prc_LoadDIM_Mart_TD_SharePoint_ReportSubscription]    Script Date: 2014-04-23 07:50:25 AM ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

 

 

— ===============================================================================================================

— Disclaimer

— The sample scripts are provided AS IS without warranty of any kind.

— I further disclaims all implied warranties including, without limitation, any implied warranties of

— merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance

— of the sample scripts and documentation remains with you.

— In no event shall I, its authors, or anyone else involved in the creation, production, or delivery of

— the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business

— profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use

— of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the

— possibility of such damages.

— ===============================================================================================================

 

 

 

— ===============================================================================================================

— Author:           Gilbertq

— Create date: 16 Apr 2014

— Description:      Here we are going to Define our Start and End Dates for our selection

—              from our SharePoint List

— NOTE: The format of the Start and End Dates are going to be the DateKey

—       from our Date Table so that this can be passed to our SSAS

—       AdventureWorksDW2012 cube.

— ===============================================================================================================

ALTERPROCEDURE[dbo].[prc_LoadDIM_Mart_TD_SharePoint_ReportSubscription]

 

AS

BEGIN

 

       SETNOCOUNTON;

      

— ===========================================================================

— 1. Getting the DateKey for our Selection: 01 July 2007

— ===========================================================================

Declare@Selection_StartJuly2007asInt

Set@Selection_StartJuly2007= 20070701

 

— ===========================================================================

— 2. Getting the DateKey for our Selection: 31 July 2007

— ===========================================================================

Declare@Selection_EndJuly2007asInt

Set@Selection_EndJuly2007= 20070731

 

— ===========================================================================

–3. Getting the DateKey for our Selection: December 2007

— ===========================================================================

Declare@Selection_December2007asInt

Set@Selection_December2007= 20071231

 

— ===========================================================================

–4. Getting the DateKey for our Selection: June 2008

— ===========================================================================

Declare@Selection_June2008asInt

Set@Selection_June2008= 20080630

 

— ===========================================================================

— Below is the query to either update an existing Subscription or if new

— then insert into the table

— ===========================================================================

 

 Merge[dbo].Mart_TD_SharePoint_ReportSubscriptionsas[Dest]

       Using (

                     SELECT   [ID]

                                  ,[Date Range]

                                  ,[Product Line]

                                  ,Casewhen[Product Line]=‘Accessory’then‘S’

                                        when[Product Line]=‘Mountain’then‘M’

                                           when[Product Line]=‘Road’then‘R’

                                           when[Product Line]=‘Touring’then‘T’

                                           Endas[Product Line For Query]

                                  ,[Email Address]

                                  ,[Subscription Name (Title)]

                                  ,[Modified]

                                  ,[Created]

                                  ,[CreatedBy]

                                  ,[ModifiedBy]

                                  ,Casewhen[Date Range]=‘July 2007’Then@Selection_StartJuly2007

                                         when[Date Range]=‘July 2007 – December 2007’Then@Selection_StartJuly2007

                                         when[Date Range]=‘July 2007 – June 2008’Then@Selection_StartJuly2007

                                         EndasStartDateKey

                                  ,Casewhen[Date Range]=‘July 2007’Then@Selection_EndJuly2007

                                         when[Date Range]=‘July 2007 – December 2007’Then@Selection_December2007

                                         when[Date Range]=‘July 2007 – June 2008’Then@Selection_June2008

                                         EndasEndDateKey

 

                           FROM[AdventureWorksDW2012].[dbo].[Staging_tb_SharePointList]with (nolock)                    

                                           

              )as[Source]

       On[Dest].[ID]=[Source].[ID]

       and[Dest].[Email Address]=[Source].[Email Address]

       and[Dest].[Subscription Name (Title)]=[Source].[Subscription Name (Title)]

       WhenMatched

       ThenUpdateSet

                [Dest].[ID]=Source.[ID]

               ,[Dest].[Date Range]=Source.[Date Range]

               ,[Dest].[Email Address]=Source.[Email Address]

               ,[Dest].[Subscription Name (Title)]=Source.[Subscription Name (Title)]

               ,[Dest].[Modified]=Source.[Modified]

               ,[Dest].[Created]=Source.[Created]

               ,[Dest].[CreatedBy]=Source.[CreatedBy]

               ,[Dest].[ModifiedBy]=Source.[ModifiedBy]

               ,[Dest].StartDateKey=Source.StartDateKey

               ,[Dest].EndDateKey=Source.EndDateKey

               ,[Dest].[Product Line]=Source.[Product Line]

               ,[Dest].[Product Line For Query]=Source.[Product Line For Query]

       WhenNotMatchedbyTargetthenInsert (

                [ID]

               ,[Date Range]

               ,[Email Address]

               ,[Subscription Name (Title)]

               ,[Modified]

               ,[Created]

               ,[CreatedBy]

               ,[ModifiedBy]

               ,StartDateKey

               ,EndDateKey 

               ,[Product Line]

               ,[Product Line For Query]

               

        )Values (

                Source.[ID]

               ,Source.[Date Range]

               ,Source.[Email Address]

               ,Source.[Subscription Name (Title)]

               ,Source.[Modified]

               ,Source.[Created]

               ,Source.[CreatedBy]

               ,Source.[ModifiedBy]

               ,Source.StartDateKey

               ,Source.EndDateKey

               ,Source.[Product Line]

               ,Source.[Product Line For Query]

              )

       WhenNotMatchedBySource

              ThenDelete

       ;

                          

       SETNOCOUNTOFF;

END

b.        

c.        With the above SP the reason for creating the StartDateKey and EndDateKey variables is due to our choice in the SharePoint list:

                                                               i.      clip_image031

                                                              ii.      And the above correlates to our variables in the SP.

                                                            iii.      NOTE: The StartDateKey and EndDateKey will be used later in the SQL Server Reporting Services (SSRS) Data Driven subscription, which in turn will pass the information to the SSRS Report Parameters

d.       We also had to put in a Case statement for the Product Line For Query so that it would match the underlying values in our SSAS dimension

e.       Another thing that we did do is to validate the email address to ensure that there are no bogus email addresses.

2.       So this is what the table will look like with the query for the Data Driven Subscription

3.       clip_image032

 

The next steps will be in the Reporting Services quick wins document where we will explain how we create our report with the require parameters.

After which we then create our Data Driven subscription

And then finally create our Power View Report and pass this link with the filter in our SSRS report.

 

SharePoint 2013 – Creating a data source to an SQL Server Analysis Services (SSAS) OLAP Cube

Below are the details when using SharePoint 2013 when you want to collect to an SQL Server Analysis Services (SSAS) OLAP cube, instead of only being able to use the SSAS Tabular.

 

Example:

·         In our example we are going to connect to our Adventure Works DW 2012 SSAS

 

1.       Go into your SharePoint 2013 website and then click on Files, then New Document and then select Report Data Source.

a.        clip_image002

2.       This will then open the Data Source Properties window where you will configure it with the following explained below.

a.        Where it says Name put in a name so that you know it is your connection file.

b.       clip_image004

c.        Next where it says Data Source Type click on the drop down and select the following:

d.       clip_image006

e.       Now where it says connection string below is the syntax that you have to use with an explanation below:

Data Source=Server\InstanceName;initial catalog=AdventureWorksDW2012;cube=’Adventure Works’

                                                                                       i.      From the syntax above the part highlighted in RED is to specify the SSAS OLAP Name and if required instance name.

1.       As with our example it was called:

a.        Server\InstanceName

                                                                                      ii.      The next section highlighted in GREEN is your SSAS OLAP Cube database name.

1.       As with our example is:

a.        initial catalog=AdventureWorksDW2012

                                                                                    iii.      The final part which you have to specify is the actual cube name, which is highlighted in PURPLE

1.       As with our example it is called:

a.        ;cube=’Adventure Works’

                                                                                    iv.      clip_image008

f.         In the next section which is the Credentials this is what will be used when running the data source.

                                                                                       i.      What we have setup in our environment is we have a static domain account where the password does not change. This is so that we can then use this account and grant it permissions to the required cubes as well as roles.

                                                                                      ii.      NOTE: We also did this because it is a lot simpler to configure the security to our SSAS OLAP cube via the standard roles.

                                                                                    iii.      So with our example we select Stored Credentials and put in our User Name and Password:

1.       clip_image010

2.       NOTE: We selected Use as Windows credentials so that it knows it is a Windows Domain account.

                                                                                    iv.      Then click on Test Connection to make sure the connection to the cube is correct as well as the credentials.

1.       clip_image012

3.       Then click Ok to create the Report Data Source.

4.       NOTE: If you are concerned about security for people using this data source to see specific data you can do one of two things:

a.        ONE: Change the above credentials to Windows Authentication.

b.       TWO: You can also limit who has access to the actual Report Data Source, Power View Report as well as the Folder that stores all the information and set unique permissions.

5.       Now as you can see below we created a quick and easy Power View Report on our SSAS OLAP Instance.

6.       clip_image014