SQL Server 2014 Observations

Today’s post is not going to be about explaining something but more along the lines of my experience on SQL Server 2014.

When I heard about all the new features in SQL Server 2014, the In Memory Tables, new cardinality estimator, new changes to how TempDB works, SSIS and Delayed Durability, I was excited to see how I could use these new features for my data warehouses.

I did a full backup, restore to a Virtual Machine (VM), test the upgrade process to SQL Server 2014 to ensure that when I completed the upgrade I would not have any unforeseen issues. (As well as test my backup files and my restore document to ensure that I could restore my system from a backup)

So the first thing that I did was to test the In Memory tables, and I was excited by using the In Memory table with no durability, this would enable my staging tables to work that much faster in getting data from the staging tables into my disk based Fact Tables. I tried a few variations in terms of what Index to use and different types of tables to use. As we are all aware due to this being the first release of In Memory tables, there are some limitations. I have now come to the conclusion for my current workloads that the new In Memory tables offer no real benefit for my staging tables in my data warehouse. I find that at times it is slower than the disk based tables. (I am aware that I could well be not using them correctly, but I did quite a lot of reading and testing to ensure that I was doing what was suggested).

Next I was hoping the combination of the cardinality estimator, changes to the TempDB and Delayed durability would increase the throughput of my queries, which would lead to faster load times from my staging tables into my Fact tables. I have monitored the time my SSIS Packages have taken to run, and unfortunately I cannot see any vast improvement in my times.

What I can say that is working in the new SSDT with Visual Studio 2013 does seem a lot quicker to develop and when debugging in my experience it starts a lot quicker than in the past. Yes it did take some getting used to all the new images for all the control flow items, data flow items etc, but that is a small price to pay. I do prefer the Dark Theme Smile 

So for me in my current environment I would say that all the new changes have not made any real significant impact on my data warehouse. And have not made it any quicker to load data and get it into my Fact table.

NOTE: I welcome anyone who has any suggestions or idea’s to please let me know. I am more than willing to learn as well as potentially not have configured it correctly.

SQL Server Integration Services (SSIS) – Using Send Mail Task for data validation checks

What I wanted to do was to be notified in my data if there were not enough rows of data, or if the financials were too low, indicating that there was a potential issue with the data coming in.

And if it was too low to fire off an email. If it was correct then to not send an email. SO that we would only be notified when there is an issue.

So in order to explain this I will explain it with an example below, which will make it easier to understand.

 

Example:

·         We want to compare the count of SQL Server Analysis (SSAS) Partitions for our Internet Sales Cube.

·         If the count of partitions increases then send an email.

·         If the count of partitions remains the same then do nothing.

 

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

 

You can refer to my previous blog post if you want to see how we populate the SSAS Partition Details

                PUT IN HERE

 

NOTE: We are using SQL Server 2014 and SQL Server Data Tools with Visual Studio 2013

 

 

1.       Create a new SSIS Package.

2.       Next we create the following 2 variables, which will hold our Previous and Current Partition Counts

a.        clip_image002[6]

3.       Next we will load the data into our variables using an Execute SQL Task and a SQL Server TSQL Script.

a.        NOTE: I normally would put the TSQL script into a stored procedure, but for this example I am going to put it in the package so that you can see how it got populated.

b.       From the SSIS Toolbox drag in an Execute SQL Task, then configure it with the following:

                                                               i.      As you can see we gave it a name, changed the result set to Single Row and created our connection

                                                              ii.       clip_image004[6]

c.        Then click on the Ellipses next to SQLStatement and put in the following TSQL code.

Declare @PreviousCountOfSSASPartitions asint

Set @PreviousCountOfSSASPartitions =

       (

         Selectcount(1)

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

         where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’

         andconvert(date,CreatedTimeStamp)=‘2013-07-09’

       )

 

Declare @CurrentCountOfSSASPartitions asINt

Set @CurrentCountOfSSASPartitions =

       (

         Selectcount(1)

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

         where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’

         andconvert(date,CreatedTimeStamp)>=‘2013-07-09’

       )

 

Select   @PreviousCountOfSSASPartitions as PreviousCountOfSSASPartitions

        ,@CurrentCountOfSSASPartitions as CurrentCountOfSSASPartitions

                                                               i.      Then click Ok.

d.       Now in the left hand pane click on Result Set and configure it so that we can populate our variables as shown below:

                                                               i.      clip_image006[6]

e.       Then click Ok.

4.       Next we are going to create a Send Mail Task so that when we get an error to send out the email.

a.        We dragged in a Send Mail Task and configured it to send via our Mail Server.

b.       Along with who to send it to, as well as the details in the email.

c.        NOTE: You can add expressions to add what you would to send dynamically.

                                                               i.      If you are going to put something in the actual email message, it is easiest to compile it in Notepad and then copy and paste it. This way it will keep your content as expected.

5.       Now we are going to use the precedence constraint in order to decide if we must send the email or now.

a.        Drag your precedence constraint from your Populate variables for Count of Partitions to our send mail task

                                                               i.      clip_image008[6]

b.       Then right click on the Success Precedence Constraint and select Edit

c.        We then configured it with the following as shown below

d.       clip_image010[6]

e.       For the Expression we configured it saying if the Previous Partition Count is less than the Current Partition Count then continue and send the email.

@[User::PreviousCountOfSSASPartitions] <  @[User::CurrentCountOfSSASPartitions]

f.         Then click Ok, and Ok again.

g.        You will now see that we have put in an expression

                                                               i.      clip_image012[6]

6.       Now when we run it when the partition counts are different it will send the email as shown below

a.        clip_image014[6]

7.       And then if we run it when the Partition counts are the same, it will NOT go to the send Mail Task

a.        clip_image016[4]

SQL Server Integration Services (SSIS) – Re-run a specific Control Flow Item if it has an error

We had an issue where we only wanted to re-run a particular control flow item when it failed or had an error. We did not want to do this for our Entire SSIS Package but just for a specific Control Flow Item.

 

Example: Inserting Data into a Staging Table and if it fails to then re-try the insert 3 times

 

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

 

NOTE: This example is using SQL Server Data Tools (SSDT) for Visual Studio 2013, and SQL Server 2014

 

1.       Create your new SSIS Package.

2.       Create the following variable:

a.        clip_image002

b.       The reason we have the LoopQuitParent variable is for the following:

                                                               i.      It will be used to exit out of our For Loop Container when it has any other value than zero

                                                              ii.      Within our For Loop Container depending on what happens to our Control Flow items, we will then set the LoopQuitParent to 1 if the Control Flow item has success.

                                                            iii.      If the Control Flow Item fails then we will the LoopQuitParent to 0 (zero) so that the For Loop Container will try again.

3.       Then drop in a For Loop Container into your Control Flow.

a.        We renamed our For Loop Container to:

Retry Staging Insert

b.       We then configured it as shown below:

c.        clip_image004

d.       What we are saying here is when our variable called LoopQuitParent is not equal to zero then exit.

4.       Next we are going to drag in what we want to test to retry within our For Loop Container.

a.        As per our example we are going to first truncate our staging table.

b.       And then Insert data into our Staging table.

c.        Below is a script to create our Staging Table

CREATETABLE [dbo].[Staging_SSAS_PartitionDetails](

       [Name] [varchar](300)NULL,

       [ID] [varchar](300)NULL,

       [CreatedTimeStamp] [varchar](300)NULL,

       [LastSchemaUpdate] [varchar](300)NULL,

       [Description] [varchar](300)NULL,

       [LastProcessed] [varchar](300)NULL,

       [State] [varchar](300)NULL,

       [Type] [varchar](300)NULL,

       [AggregationPrefix] [varchar](300)NULL,

       [StorageMode] [varchar](300)NULL,

       [CurrentStorageMode] [varchar](300)NULL,

       [StringStoresCompatibilityLevel] [varchar](300)NULL,

       [CurrentStringStoresCompatibilityLevel] [varchar](300)NULL,

       [ProcessingMode] [varchar](300)NULL,

       [ProcessingPriority] [varchar](300)NULL,

       [StorageLocation] [varchar](300)NULL,

       [RemoteDataSourceID] [varchar](300)NULL,

       [Slice] [varchar](300)NULL,

       [EstimatedRows] [varchar](300)NULL,

       [AggregationDesignID] [varchar](300)NULL,

       [EstimatedSize] [varchar](300)NULL,

       [Parent_MeasureGroupID] [varchar](300)NULL,

       [Parent_CubeID] [varchar](300)NULL,

       [Parent_DatabaseID] [varchar](300)NULL

)ON [PRIMARY]

d.       NOTE: This table is based on the previous blog post for SSAS Partition Details

SSAS (SQL SERVER ANALYSIS SERVICES) – GETTING ALL PARTITION INFORMATION FROM SSAS DATABASE

e.       Next we put in an Execute SQL Task and put in our Truncate Table Statement for the above table.

                                                               i.      clip_image006

f.         Next we put in our Data Flow Task to insert our data into our Staging Table

                                                               i.      clip_image008

g.        Next we can drag our Success Precedence Constraint between our Truncate Table and our Insert into items.

                                                               i.      clip_image010

h.       Next in order to populate our LoopQuitParent variable for when it succeeds we need to do the following:

                                                               i.      Drag in an Execute SQL Task, and we are going to configure it so that if this Execute SQL Task is run it will set the variable LoopQuitParent equal to 1, which in turn will result in the For Loop Container exiting.

                                                              ii.      We put the following in the General Section of our Execute SQL Task

                                                            iii.      clip_image012

                                                            iv.      NOTE: We renamed it to: SUCCESS – Set Variable LoopQuitParent Equal to 1

                                                              v.      Then we click on the Result Set and configured it so that our variable would be populated

                                                            vi.      clip_image014

i.         Next in order to populate our LoopQuitParent variable for when it fails we need to do the following:

                                                               i.      Drag in an Execute SQL Task, and we are going to configure it so that if this Execute SQL Task is run it will set the variable LoopQuitParent equal to 0, which in turn will result in the For Loop Container continuing.

                                                              ii.      We put the following in the General Section of our Execute SQL Task

                                                            iii.      clip_image016

                                                            iv.      NOTE: We renamed it to: FAILURE – Set Variable LoopQuitParent Equal to 0

                                                              v.      Then we click on the Result Set and configured it so that our variable would be populated

                                                            vi.      clip_image018

j.         Next we need to drag our Success Precedent Constraint to our SUCCESS – Set Variable LoopQuitParent Equal to 1 Execute SQL Task as Shown below:

                                                               i.       clip_image020

k.        Next we need to drag our Failure Precedent Constraint to our FAILURE – Set Variable LoopQuitParent Equal to 0 Execute SQL Task as Shown below:

                                                               i.      clip_image022

5.       The next part is to configure our For Loop Container so that when it does fail how many times we want it to retry.

a.        NOTE: Because we are using the MaximumErrorCount Property for our For Loop Container it works like the following:

                                                               i.      It will always run at least once.

                                                              ii.      For example, if you wanted your package to retry 3 times, you would have to set the MaximumErrorCount = 6

1.       This is because the first time it run’s it is trying to execute. And NO retry has started.

2.       Then it will retry another 3 times each time starting again, making the total times it will need to be configured to 6.

                                                            iii.      So the calculation for the retry is the following:

1.       Total Retries X 2

2.       EG: Total Retries must be 3 times

a.        3 X 2 = 6

b.       We went into the Properties for our For Loop Container and set the MaximumErrorCount = 6

                                                               i.      clip_image024

6.       The final step is if your For Loop Container does error to not propagate to the parent or SSIS Package

a.        NOTE: The reason for doing this is so that your entire SSIS Package will NOT fail during our retries.

b.       Make sure that before you do the steps below you HAVE clicked on the For Loop Container

                                                               i.      As with our example it is called Retry Staging Insert

c.        To change this setting click on the Event Handlers tab at the top of your SSIS package

                                                               i.      clip_image026

d.       Next click on the following below to enable the Event Handler

                                                               i.      clip_image028

                                                              ii.      NOTE: As you can see above our In error event handler is for our For Loop Container called ‘Retry Staging Insert’

e.       Next go to your variables, click on the Grid Options and ensure to select “Show system variables”

                                                               i.      clip_image030

f.         Now scroll down in your Variables until you get to the name called Propagate, with the Scope of On Error

                                                               i.      Change the value to False

                                                              ii.      clip_image032

g.        Now save your SSIS Package.

h.       Now your For Loop Container will not cause your entire SSIS Package to fail.

7.       Now if you run it and it works the first time, it should run through and exit as shown below

a.        clip_image034

8.       Now if we cause the data flow task to fail, when looking at the Execution results we see the following to retry 3 times

a.        clip_image036

b.       When looking at the Truncate Table Staging_SSASPartitionDetails task we see the following

c.        clip_image038

d.       As you can see above it retried a total of 3 times.

e.       And as you will also see due to our configuration not to Propagate to our SSIS Package even though it failed it did complete with success

 

 

SSIS – Dropping Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL

Below are the steps that we have integrated into SSAS using SSIS so that we can then drop our old SSAS Partitions using SSIS and XMLA.

 

Example:

·         We are going to drop our oldest partition from Measure Group called Fact InternetSales 1, which is in our Adventure Works cube.

·         The actual Cube partition name is called:

o    Internet_Sales_2005

 

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

 

 

Getting SSAS Partition Details

You can reference the following section in Analysis Services Quick Wins and go to the following section which will explain how to get down and insert our SSAS Partition details.

                SSAS (SQL SERVER ANALYSIS SERVICES) – GETTING ALL PARTITION INFORMATION FROM SSAS DATABASE

 

Getting Partition Name into Variable in SSIS

Below is how we will then drop our oldest SSAS Partition as per our example above.

 

1.       The first thing that we need to do is to find out our oldest Partition for our Measure Group called:

a.       Fact Internet Sales 1

2.       Once we have this we are then going to put this into a query, which we will then put into a variable in SSIS

3.       This is the query that we are going to use

Selecttop 1 ID as CubePartitionID

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

  Where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’

a.        NOTE: For your particular Partition scheme you might have to change your query to get back the partition ID you expect

b.       As per above we get the following result in SQL Server Management studio (SSMS)

c.        clip_image002[4]

4.       Next in SSIS we will create the following variable as shown below:

a.        clip_image004[4]

5.       Next what we do is to assign our query from step 3 above to our variable.

a.        NOTE: Ideally you would want to put your query into a Stored Procedure.

b.       We drag in an Execute SQL Task, and we rename it to the following:

                                                               i.      clip_image006[4]

c.        Then we configured the General Tab with the following

                                                               i.      clip_image008[4]

d.       Then click on the Result Set on the left hand side and configure it with the following Result Name and Variable Name as shown below:

                                                               i.      clip_image010[4]

e.       Then click Ok.

f.         Now you can run and test to make sure that you can get your variable correctly.

 

Getting XMLA to drop SSAS Partition and put into SSIS

In the following steps we will generate our XMLA and then using this put it into SSIS so that we can then automate this.

 

1.       Go into SSMS and go to your SSAS Cube.

2.       As with our example this was our Adventure works cube.

3.       We then navigated to the following as shown below:

a.         clip_image012[4]

4.       Now right click on the Internet_Sales_2005, Script Partition as, Delete To, New Query Editor Window as shown below.

a.        clip_image014[4]

5.       You will now see the following in SSMS

a.        clip_image016[4]

6.       Next we now need to go into SSIS and create the following variable

a.        clip_image018[4]

7.       Next we need to take our XMLA delete statement and put this into a TSQL Query syntax so that we can then use this to populate our variable (XMLAQuery_DropSSASPartition)

8.       This is the how we did it:

a.        The first thing to do is where you have your script from step 5 above, go into find and replace and do the following:

                                                               i.      clip_image020[4]

b.       NOTE: This is so that when we put this into SSIS and load it as an expression it will not invalidate it due to the double quotation.

c.        Now we put in our TSQL Query syntax as shown below

image

9.       Now go into your SSIS package and next to the variable XMLAQuery_DropSSASPartition click on the Ellipses button

a.        Now we configured our expression with the following shown below:

image

b.       As you can see above we have encapsulated this in our SSIS Expression

c.        What we have also done is to insert our CubePartitionID variable into our expression

                                                               i.      It is highlighted in RED above.

d.       You can click on Evaluate Expression to ensure that everything is correct.

e.       Click Ok to insert our expression

10.    Next what we need to do is to assign our variable we created above into a variable so that this can then be passed to an Analysis Services Execute DDL Task to actually drop the partition, but doing the following below:

a.        First we need to create a variable which will hold our XMLA syntax once it has been populated from step 9 above.

b.       We gave it the following name:

c.        clip_image022[4]

d.       Next what we then need to do is to use an Execute SQL Task to then get our XMLA script populated.

e.       We dragged in an Execute SQL Task and gave it the following name:

                                                               i.      clip_image024[4]

f.         Then we configured our Execute SQL Task with the following to get our data from our variable in step 9 above.

                                                               i.      clip_image026[4]

                                                              ii.      NOTE: AS you can see we set the SQLSourceType to a variable.

1.       And then used our variable name from step 9

g.        Then click on Result Set and we configured it with the following:

                                                               i.      clip_image028[4]

h.       Then click Ok.

11.    Now the final part is to drag in our Analysis Services Execute DDL Task and configure it to connect to our cube, and then use our script from step 10 above.

12.    Now we just need to configure the Analysis Services Execute DDL Task by doing the following.

a.        Drag in the Analysis Services Execute DDL Task, double click to go into the Properties

                                                               i.      Click on DDL

b.       In the right hand side where it says Connection you are going to have to create a connection to your SSAS, and Database

                                                               i.      Click on New Connection

c.        As with our example we created our connection, which you can configure to your SSAS Cube.

d.       Now where it says SourceType, click on the Drop down and select Variable

e.       Then next to Source click on the Drop down and select the Variable that we populated in the previous steps.

f.         As with our example we selected the following:

                                                               i.      User::XMLAScript_DropSSASPartition

g.        It should look like the following:

h.       clip_image030[4]

i.         Then click Ok.

Order of Control Flow Items

The final step is to now order everything correctly in our Control Flow.

 

1.       Below is how we have ordered our SSIS Package

2.       clip_image032[4]

3.       NOTE: You will see that we are truncating our Mart_TD_SSAS_PartitionDetails table.

a.        This is because we want to keep it up to date.

4.       NOTE 2: You will see that even though we started with how to get our PartitionDetails, we still put this at the end, so that once our SSAS Partition has been dropped we have the correct details.

5.       Finally run your SSIS package and it will then drop your last partition