February 2012 – The Data Queen

Creating an SSIS Package Template with Predefined Package Configurations

Best practices suggest creating a package template with the Package Configurations already predefined for your project, and then copying and the template package as a starting point for each additional package in your solution. This way you won’t have to add the configurations to each package, but only do it once for your solution and copy the configurations by using a copy of the template for each new package in your solution. Here is one way to to this.

1. ADD AN ENVIRONMENT VARIABLE TO THE SERVER TO POINT TO THE CONFIGURATION DATABASE

On the server that will be running the SSIS packages, add an Environment Variable called “SSIS_CONFIG_DB” to hold the value of connection string for the Configuration database that will store all the other connection strings.

All Programs – Computer – right click on Computer and select Properties

Select Advanced System settings

On the Advanced tab, click on the Environment Variables button

Add a new System Variable by clicking the New button and filling the Variable name SSIS_CONFIG_DB and the variable value in the format of:

Provider=SQLNCLI10.1;Data Source=servername;Integrated Security=SSPI;Initial Catalog=databasename

2. (CLOSE AND) OPEN BIDS

Open BIDS solution AFTER the variable is created, so that your variable will show up in the dropdown.

3. REMOVE ANY SHARED DATA SOURCES IN YOUR PROJECT SOLUTION

Open or create a new solution. If you are using Shared Data Sources, delete them from the Data Sources folder in the Solution Explorer now. Your packages will retain their individual connection managers. Shared Data Sources and Package Configurations don’t mix very well, and here’s a good write up on why.

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

4. CREATE A NEW PACKAGE

Add a new package to your solution called Package_Template.

5. ADD A CONNECTION TO YOUR PACKAGE FOR THE CONFIGURATION DATABASE

Add a Connection to your package which connects to the database you have chosen to hold all your configuration strings in Step 1. To do this, right click in the Connection Managers pane in your Package_Template package and set up the appropriate connection.

6. ADD THE ENVIRONMENT VARIABLE CONFIGURATION TO YOUR PACKAGE

This Configuration needs to be first in the list of Configurations defined in your package, since the other Configurations will be referencing it.

Right click on Control Flow, select Package Configuration. Check the Enable package configurations checkbox. Then click the Add button, select Configuration Type “Environment Variable” and select your SSIS_CONFIG environment variable from the dropdown.

Click Next. Select the Target Property by navigating to the Connection Manager for the Configuration Database which you set up in step 5, and selecting the ConnectionString property.

Click Next and give your Configuration a name “ConfigConn”

7. ADD A SQL SERVER CONFIGURATION TO YOUR PACKAGE

Add another configuration of type “SQL Server” by clicking the Add button. Select SQL Server from the Configuration Type drop down. Select the connection to your DW (the one you are pointing to in your environment variable) and if this is the first SQL configuration you are adding to this database, click New to create the table in the DW where the connection strings will be stored. Or, if this is not the first time, then select the name of your configuration table from the drop down [dbo].[SSIS Configurations]”

If this is the first time you are creating an entry for this connection, create a Configuration filter by typing in an appropriate name for your connection string, which will identify that connection in the table. Otherwise select the name of your existing entry from the dropdown.

If this is the first time you are creating a Configuration to this database, you will need to navigate to the appropriate Connection Manager and select the Connection String property.

If this is not the first time you have used this particular Configuration filter a message box will ask you if you want to reuse the existing configuration or overwrite. Select Reuse Existing, so the information in the database about this connection will not be overwritten.

Give your connection Configuration a name. Example “NAVConn”

You can go ahead and add the rest of your package connections as SQL Server configurations. Take a look at the table “SSIS Configurations” in the Configuration database you defined, to see the connection strings being entered. When you update the connection string in this table, your packages will point to the updated database. Try it by updating the value and reopening your package.

If you want to add logging to your package template at this point you can look at this post – Configure SSIS Logs

Thanks to these two sites for giving me some tips on this subject:

http://www.rafael-salas.com/2007/01/ssis-package-configurations-using-sql.html

http://www.mssqltips.com/tip.asp?tip=1405

Compare Week Last Year for a 53 Week Year Using MDX

Many companies use 4-4-5 calendar which ends up with an extra week approximately every 5 years. This makes comparing periods challenging when you encounter the 53 week year. This is especially challenging when reports are driven from an SSAS cube data source, and you want to build in the logic to automatically compare the correct This Year period with the corresponding Last Year period as per the companies business rules. There is a way to do this. It isn’t pretty, but it works. The reporting will automatically choose the correct matching period in the context of a 52 or 53 week year.

There are some possibilities. A company may want to compare the year after a 53 week year by shifting each week of the 53 week year by 1 week. For example, if 2012 is the 53 week year then we want to compare:

Another possibility is that the company may want to compare the 53 week year by shifting the previous year weeks, or in some other way identify whether a week belongs to a 52 or a 53 week year for purposes of comparing periods. This requires a bit more under the hood, but also doable.

COMPARE THE YEAR AFTER A 53 WEEK YEAR WITH THE CORRESPONDING SHIFTED WEEKS FOR LAST YEAR

This is relatively easy. Basically you make all your Last Year (LY) calculations and LY Period To Date calculations by counting back 52 weeks from the current week. The week in your hierarchy must always be selected in your query. I use Fiscal Week ID as the level to which I map all previous period calculations, so the Fiscal Week ID must be selected in all queries using these calculations.

This calculation will return the [Sls Dollars LY TW] (last year this week)

(PARALLELPERIOD
( [TIME].[Week Period To Date].[Fiscal Week ID], 52, [TIME].[Week Period To Date].CURRENTMEMBER )
,[Measures].[Sales Dollars])

Notice how 2013 Wk 1 shows 2012 Wk 2 for the Sls Dollars LY TW. And 2012 Wk 2 shows 2011 Wk 2 as desired.

For Periods To Date you can do this the same way. You would like the LY QTD for 2013 Wk1 to show the QTD up to 2012 Wk 2.

This query returns [Sls Dollars LY QTD].

Aggregate
(
PeriodsToDate
(
[TIME].[Week Period To Date].[Fiscal Quarter],

             PARALLELPERIOD
            ( [TIME].[Week Period To Date].[Fiscal Week ID],52,
                [TIME].[Week Period To Date].CURRENTMEMBER
            )
),
[Measures].[Sales Dollars]
)

Here are the results. Again, you must have the Fiscal Week Id from the hierarchy selected in your query for this to work.

Notice how 2013 Wk 1 shows 2012 Wk 2 QTD for the Sls Dollars LY QTD. And 2012 Wk 2 shows 2011 Wk 2 QTD as desired.

IDENTIFY WHETHER A WEEK FALLS IN A 52 OR A 53 WEEK YEAR FOR USE IN CALCULATIONS

Here is an example of how to identify whether a week falls in a 52 or 53 week year, in order to be able to use in your calculations. It isn’t pretty, but it does the job.

1. Add a column to your DIM_Date table to identify whether a year has 52 or 53 weeks.

2. Add a named calculation to the DIM_Date table in your cube Data Source View.

The calculation is FiscalWeeksCount*10000+FiscalYearID. This will give you an integer which begins with either 52 or 53 and ends with the Year. For example 522011 or 532012.

3. Add this calculated field to your Time dimension. Add it as a level of your Time Hierarchy, directly after Fiscal Year. You may prefer to create a new hierarchy specifically for reporting needs so as not to confuse users.

The reason the Year is appended to the Fiscal Weeks Count, rather than having the Fiscal Year roll directly up to Fiscal Weeks Count is that that would disturb the order of the children. 53 would come after 52 in the hierarchy, and therefor 2012 would come after 2013. The MDX calculations use relative position in the hierarchy to find a previous period, so the weeks must remain in the correct order in the hierarchy in order for this to work.

4. Save the change and rebuild the cube. Now you can reference this level in the hierarchy in order to identify how many weeks in the fiscal year. You can use it in a case statement, like this:

CASE
WHEN [TIME].[Fiscal Weeks Count Year].CURRENTMEMBER.MEMBERVALUE > 530000 THEN
(PARALLELPERIOD ( [TIME].[Week Period To Date].[Fiscal Week ID], 53, [TIME].[Week Period To Date].CURRENTMEMBER ), [Measures].[Sales Dollars] )
ELSE
(PARALLELPERIOD ( [TIME].[Week Period To Date].[Fiscal Week ID], 52, [TIME].[Week Period To Date].CURRENTMEMBER ), [Measures].[Sales Dollars])
END

This determines if the current selected week falls in a 52 or a 53 week year, and calculated the corresponding previous period accordingly. You could use ANCESTOR if you want to find out if the previous year was a 52 or a 53 week year. You now know where your selected week is relative to a 53 week year and can use this information as needed.

Filter a Parameter with Long List of Values Using Type Ahead

I have a customer who has huge numbers of SKUs. Their reporting is using an SSAS cube as the data source. When filtering an SSRS report the users have to scroll through many many records to get to the SKU they want. There is a type-ahead feature built into SSRS but you have to type superfast and hit it exactly in order to make it work. I came across this great solution, and would like to blog the step-by-step with a cube as datasource.

The solution is basically to add an additional text parameter which will prefilter the available values in the large parameter list. This way the user doesn’t have to scroll through the long list to find the item they want, but can produce a shorter picklist, or even reduce the list down to one value if they type the exact SKU in. They still have to check the box on the item they want, even if they’ve typed it all in, but it is still a good option when dealing with huge lists.

1. Alter your existing Available Values query to include the new pre-filter parameter.

When you created the original parameter in the report BIDS automatically created the dataset for the Available Values in the parameter.

You need to alter this query to use an additional pre-filter text based parameter.

1. Show hidden datasets. Right click on your data source and select Show Hidden Datasets

2. Find out which dataset is being used by your parameter in the Available Values. Right click on the parameter and select Parameter Properties. Select the Available Values tab and find the name of the query being used.

3. Open up the dataset query. Right click on the dataset you just identified, and select Query.

4. Add a parameter to the query.Click on the parameter button in Query Designer.

Type in the name of the new Parameter you would like to add for the type-ahead prefilter, and put in a Default value. Don’t worry, you can change the default value in your Parameter Properties later if you wish.

Select OK.

5. Add the highlighted additional line of MDX to your query. Place the line right before the very last FROM [nameofcube], and add a close bracket at the end of the query. This MDX will further filter your available values based on what is typed into the prefilter you are creating.

WITH MEMBER [Measures].[ParameterCaption] AS [Product].[Style Code].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue]
AS [Product].[Style Code].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Product].[Style Code].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Style Code].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@ProductDivisionCode, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT (IIF(LEN(@ProductStyleCode_filter) =0, ([[Product].[Style Code].ALLMEMBERS), (FILTER([Product].[Style Code].ALLMEMBERS, INSTR ([Product].[Style Code].CURRENTMEMBER.MEMBER_CAPTION , @ProductStyleCode_filter) > 0)))) ON COLUMNS
FROM [Sales and Inventory]) )

Click OK to save your Query.

BIDS has created your new paramater for you. Now you can edit the default value of your parameter if you like, and then test your report. All should be working as expected.

Thanks to Thomas Puch and Ella Maschiach for their blogs on the subject.