SQL BI – Page 4 – 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

SSIS 2012 – Package Configurations Menu Option Missing

I’m going to blog this in case anyone else has run into the same issue. I recently tried to create SSIS Package Configurations using SQL Server Data Tools (SSDT) in a SQL Server 2012 environment in Cloudshare.

As always, I right clicked on the Control Flow designer for my package expecting to see the Package Configurations option on the menu. The option was not there.

So I searched msdn I found the instructions for 2012 tell me to select Package Configurations from the SSIS menu. http://msdn.microsoft.com/en-us/library/ms141132(v=sql.110).aspx I did that, but it’s not there either.

I haven’t been able to find a solution to getting this option back into my menu, but I did find a workaround. In my Package Properties window there is an option for Configurations.

When I click on the ellipsis I get the Package Configurations Organizer window I am looking for.

Just click on the Enable package configurations checkbox and away you go as normal.

I will be posting a blog shortly on some best practices for defining and reusing Package Configurations across your solution using a Package Template.

As an aside, I did also come across this interesting blog post on Parameters in Denali. As he states, they don’t replace Package Configurations, but it is another way to go.

http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/11/parameters-in-ssis-in-denali.aspx

How to Create an Inventory Aging Report from a Cube Data source

This is the fourth in a series of blogs about how to use math to filter out values from an SSAS dataset, and then create aggregations in a report. You want to filter out some values from a report column and then sum them, but you can’t use an IIF statement because it can’t be aggregated. Related blogs : Filter Most-Recent-Day, Week-To-Date and Full Week, Filter from Percentage where Last Year = Zero, and Aggregate Last Child

In this situation the client would like an Inventory Aging report. The report needs to include columns for inventory received 0-3 months, 4-6 months, 7-9 months and 10-12 months ago. The user can choose a Fiscal Week for the On Hand Inventory, and the report must calculate the aging based on the Last Receipt Date, which is built into the Inventory On Hand table. This seems simple enough until you get into the details.

The end results should look like this:

1. CUBE MUST HAVE TWO DIFFERENT TIME DIMENSIONS

The user must be able to select the week of the On Hand Inventory, and the report must render the aging based on Receipt Date. So two different date fields are required in your Inventory FACT table – Inventory Date and Receipt Date, and two different time dimensions should be created from these.

2. CUBE DIMENSION NEEDS TWO WAYS TO ACCESS FISCAL MONTH ID

The next thing you need is a dataset with the right fields in it to allow you calculate the aging. You’ll need the Fiscal Month ID of the selected Inventory Date as well as the Fiscal Month ID of the Receipt Dates, so you can calculate how many months apart they are for the aging. Unfortunately you can’t include two fields in your dataset with the same name, which is what would happen if I just pulled in each of the Fiscal_Month_ID fields from the two Time dimensions.

If I try to build a calculated field in order to rename one of the fields I still need something from the Time dimension that will populate the CURRENTMEMBER aspect. So if I built a calculated field like this [TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME, it will return [TIME OH Last Receipt Date].[Fiscal Month].[All] unless I pull in the Fiscal Month ID field to give the CURRENTMEMBER it’s context. This will mean two fields with the same name again, and the query will not accept it.

I worked around this by including Fiscal_Month_ID AND Fiscal_Month in my Time dimensions, with Fiscal_Month using the Fiscal_Month_ID as it’s key. This gives me two ways to access the Fiscal Month ID, one from each Time dimension.

3. REPORT DATASET NEED CORRECT FIELDS TO YIELD DESIRED RESULTS

In the Dataset Query I included Fiscal_Month_ID from the OH Inventory Time dimension and Fiscal_Month from the Last Receipt Date Time dimension. Then I added a calculated field called ReceiptMonth to my query which yields the tuple showing the underlying Fiscal Month ID.

[TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME

I will reference this field to find the number of months difference between the user Selected Month and the Last Receipt Date.

4. ADD CACULATED FIELDS

Right click on your Dataset and select Dataset Properties. Go to the Fields selection and add the following calculated fields:

1. ReceiptMonthID – This pulls the Month ID out of the tuple. My month IDs are in the form of 201001 for example for January 2010.

=Mid(Fields!RecepitMonth.Value,InStr(Fields!RecepitMonth.Value,"].&[")+4,6)

2. MonthDiff – Because the Month IDs are in the form of 201001, they need to be turned into dates to calculate the number of months between them. The +1 will prevent division by zero in the next calculations.

=DateDiff("M",
CDate(RIGHT(Fields!ReceiptMonthID.Value,2)+"-01-"+LEFT(Fields!ReceiptMonthID.Value,4)),
CDate(RIGHT(Fields!Fiscal_Month_ID.Value,2)+"-01-"+LEFT(Fields!Fiscal_Month_ID.Value,4)))+1

3. ThreeMonths – This actually included 4 months, the current month plus the previous 3 months. It returns a 1 if the MonthDiff between 1 and 4, otherwise it returns a 0.

=CEILING(FLOOR(4/Fields!MonthDiff.Value)/(4/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

I take the Floor of 4/MonthDiff to get a positive value if the MonthDiff <= 4. Then I divide it by (4/MonthDiff) to get a value of between 0 and 1. And I take the Ceiling of this to end up with either a 1 or a zero.

4. SixMonths – This includes the current month plus the previous 6 months. It returns a 1 if the MonthDiff between 1 and 6, otherwise it returns a 0.

=CEILING(FLOOR(7/Fields!MonthDiff.Value)/(7/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

5. NineMonths – This includes the current month plus the previous 9 months. It returns a 1 if the MonthDiff between 1 and 9, otherwise it returns a 0.

=CEILING(FLOOR(10/Fields!MonthDiff.Value)/(10/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

6. TwelveMonths – This includes the current month plus the previous 12 months. It returns a 1 if the MonthDiff between 1 and 12, otherwise it returns a 0.

=CEILING(FLOOR(13/Fields!MonthDiff.Value)/(13/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value

5. ADD A DATASET FILTER TO REMOVE FUTURE MONTHS

Right click on the Dataset and go to the Filters tab. Add this filter. Be sure to select the data type of Integer or you will get an error.

6. DO A BIT OF MATH IN THE REPORT TO USE THE CALCULATED FIELDS

0 –3 MONTHS =Sum(Fields!ThreeMonths.Value)

4 – 6 MONTHS =SUM(Fields!SixMonths.Value)-SUM(Fields!ThreeMonths.Value)

7 – 9 MONTHS =SUM(Fields!NineMonths.Value)-SUM(Fields!SixMonths.Value)

10 – 12 MONTHS =SUM(Fields!TwevleMonths.Value)-SUM(Fields!NineMonths.Value)

OLDER THAN 12 MONTHS =SUM(Fields!Inv_Dollars.Value)-SUM(Fields!TwevleMonths.Value)

This is just junk data, but you can get the idea of how the report slots the inventory into the right aging based on the MonthDiff.

Now hide the details to show the correct aggregates for your report.

This is not as complex as some situations, but it’s nice to be able to filter out values from your query and aggregate the results without having to use custom code for to do the aggregation for every value that has an Iif statement. I find this much cleaner, once you get the math right.

How to build a Weekly Report with Most-Recent-Day and Week-To-Date values Filtered correctly for Last Year

Not to beat a dead horse, but this is another SSRS aggregation situation against a cube data source that can be solved by math. This is the third in a series, and I suspect there will be a few more, since each situation is different.

You can get the concept from my previous posts Filter Zeros from a Percent Increase and Aggregating Last Child values. This one is even more complicated, but I’ll give the quick and dirty version.

The client wants a report that allows the user to choose a week, and have report columns for the most recent day in that week (so it will be Saturday for all past weeks, and today for the current week), for the week-to-date, and for the full week. This get tricky because some of the values in these sets of columns include most recent day Last Year, and week-to-date Last Year. I can’t filter the data set because some of the columns need the full week values for Budget and for Last Year, even if we’re only part way through the week. Below is a report with the weekdays toggled open so you can see exactly how the values are summing up for the groups. Thursday is the most recent day in the week, so it is the only one included in the Thursday totals. Sunday to Thursday are included in the Week-To-Date totals.

To accomplish this I use math to zero out any unwanted values. The math itself takes some thinking, and every situation is different. I put the values in a spreadsheet and play with them until I get the right combination. Here is how I solved this one.

CREATE TWO FACTORS – Week-To-Date and Most Recent Day

The end goal is to create two multipliers, one for Most Recent Day, and one for Week To Date. You will be able to use these multipliers on any measure in your data set to filter out unwanted values.

The user can select a single week for the report. The report dataset must contain the integer value of the Week Number out of the parameter select by the user. **Note: this solution only works if the weeks in your cube are numbered uniquely and consecutively over time. So the first week in your Date table should be week one (or whatever starting point you choose), and each week number thereafter should be greater than the last.

1. CREATE NEW FIELDS IN THE REPORT DATASET PROPERTIES

Double click on your dataset and go to the Fields tab in the Dataset Properties. Click on the Add button and select Calculated Field.

2. FIRST WE FIND THE MAXIMUM DAY FOR THE SELECTED WEEK

Add the following Calculated Fields. The end goal is to create two multipliers, one for Most Recent Day, and one for Week To Date. You will be able to use these multipliers on any measure in your data set to filter out unwanted values. See Excel screenshot below for examples broken down for current week and previous week.

1. NowDay – This gives me the day number of today from 1 to 7

=DatePart("w",Now(),FirstDayOfWeek.Sunday)

2. CurrentWk – There is a hidden parameter in the report defaulting to whatever the week number is of today. Example today could fall in week # 319 according to my DIM_Time table. **

=Replace(Mid(Parameters!CurrentWeekID.Value,InStr(Parameters!CurrentWeekID.Value,"].&[")+4,10),"]","")

3. Floor – The floor returns either a 1 or a 0. It returns a 1 if the week selected is the current week, otherwise it returns 0.

=FLOOR ( Fields!Fiscal_Week_ID.Value/Fields!CurrentWk.Value )

4. Ceiling – The ceiling returns either a 7 or a 0. It returns a 0 if the week selected is the current week, otherwise it returns a 7. I use a +1 to prevent a divide by zero.

=(CEILING( (Fields!CurrentWk.Value-Fields!Fiscal_Week_ID.Value) / (ABS(Fields!CurrentWk.Value-Fields!Fiscal_Week_ID.Value)+1) ))*7

5. MaxDayTW –This is the maximum day number (1 to 7) for the week selected. If it is the current week it will be whatever number day of the week today is. If is a past week it will return a 7. So if today is Wednesday, and my week starts on Sunday, then MaxDayTW will be 4. The way it works is if the current week is selected then the Ceiling will be zero, and we will keep the NowDay value. If it is a past week, then the NowDay value will be zero’d out and the Ceiling will be 7, making it the MaxDayTW = 7, the last day of the week.

=(Fields!NowDay.Value*Fields!Floor.Value) + Fields!Ceiling.Value

6. MaxDayTW_Name – This gives the weekday name for the MaxDayTW that we calculated – Monday, Tuesday, etc. to be used in the column header of the report.

=WeekdayName(Fields!MaxDayTW.Value)

3. THEN WE CREATE THE MULTIPLIERS

Add the following calculated fields:

7. WeekToDate_multiplier – This will return a 1 or a 0. It will be a 1 for all days which are less than or equal to the MaxDayTW, and zero out any unwanted days in the week-to-date.

=CEILING ( FLOOR ( Fields!MaxDayTW.Value / Fields!Day_Of_Week_ID.Value ) / ( Fields!MaxDayTW.Value / Fields!Day_Of_Week_ID.Value ) )

8. MostRecentDay_multiplier – This will return a 1 or a 0. It will be 1 for the most recent day of the week and a 0 for any other days of the week.

=FLOOR (Fields!Day_Of_Week_ID.Value /Fields!MaxDayTW.Value)*Fields!WeekToDate_multiplier.Value

4. USE THE MULTIPLIERS IN YOUR REPORT

You can either create additional calculated fields in your dataset, or you can just apply the multipliers directly in your report. I prefer to use them in the report so I don’t have to track back to multiple places to find out exactly how a calculation is working. Be sure to use the multiplier BEFORE aggregating, since you want to multiply the individual rows, not the aggregate.

Examples:

1. To get Most Recent Day Sales $ use this calculation:

=Sum(Fields!Sales_Dollars.Value*Fields!MostRecentDay_multiplier.Value

2. To get Week To Date Sales $ for Last Year use this calculation:

=Sum(Fields!Sls_Dollars_LY.Value*Fields!WeekToDate_multiplier.Value)

Here are the results. The weekdays are toggled open so you can see exactly how the values are summing up for the groups. Thursday is the most recent day in the week, so it is the only one included in the Thursday totals. Sunday to Thursday are included in the Week-To-Date totals.

This is not a simple solution, but once it is in place it is very simple to use. It gets around all the complexities of having to use Custom Code to sum up each different measure for each different group in your report. It also allows you to have repeating headers on each page rather than having to hold your table within a List to make the Custom Code work properly in all renderings. Get the two multipliers right, and you can aggregate as many measures as you like without any hassle at all.

** It’s worth repeating that this solution only works if the weeks in your cube are numbered uniquely and consecutively over time. So the first week in your Date table should be week one (or whatever starting point you choose), and each week number thereafter should be greater than the last.

How to Filter out Last Year Value from a Percent Increase aggregation when the Last Year value is less than or equal to Zero

This is a sister solution to my last post which describes how to use math to ignore unwanted values in a cube query for an SSRS report, but still be able to aggregate the resulting values. IIF statements can’t be aggregated, and aggregations can’t be nested, but math can always be aggregated in SSRS.

A common requirement is to calculate a Percent Increase over Last Year (LY). The Percent Increase calculation is:

=(This Year – Last Year) / Last Year

But what if you want to ignore all values in a row where LY <= 0? You can hide the detail rows using an IIF statement in your report, but to sum and calculate the percentage at a group level all values will be summed. If this Year (TY) is more than zero it will be included in the % calculation, even though LY was less than or equal to zero. And if LY is less than zero it will also be included in the sum. This will skew the results and not give the desired results. For example:

The basic premise is that you want to zero out LY and TY in your calculation if LY <= 0. Here’s how.

1. ADD A CALCULATED FIELD TO YOUR DATASET

Add a calculated field to your Dataset Query called “LY_Inc_Multiple”. Right click on the report Dataset, select Dataset Properties, Select Fields, click on the Add button and select Calculated Field.

2. WRITE YOUR CALCULATION

We want to create a calculation that will produce either a 1 if LY is positive or a 0 if LY is zero or negative. We can then use this to multiply the values in the aggregate percent calculation for the Group to exclude any unwanted values from the result. Here I will divide LY by the absolute value of itself, plus one. The ‘plus one’ is to prevent division by zero if LY is zero. Take the Ceiling of this calculation will give you a result of either 1 or 0.

Click on the function button in the Field Source of your new LY_Inc_Multiple calculation. Enter the following:

=(Ceiling(Fields!LY.Value/(ABS(Fields!LY.Value)+1)))

Here is how the calculation works, broken down step by step

3. USE YOUR CALCULATION IN THE REPORT

You can then go ahead and multiply your Increase Percent variables to zero out any unwanted values. Be sure to use the LY_Inc_Multiple in your statement BEFORE summing.

For clarity, your calculation is this:

=(LY_Inc_Multiple*(TY-LY))/(LY_Inc_Multiple*LY)

In your report at the group level it manifests as this :

=Iif(Sum(Fields!LY_Inc_Multiple.Value*Fields!LY.Value)=0,"",((SUM(Fields!LY_Inc_Multiple.Value*(Fields!TY.Value-Fields!LY.Value))/Sum(Fields!LY_Inc_Multiple.Value*Fields!SunLY.Value))))

The IIF statement will hide any division by zero.

That’s basically it. Each situation you come across will be slightly different, and you’ll need to think about the math you need for your particular situation. It’s brain teaser for sure, but once you get the hang of it it can make report building much simpler. You can avoid custom code solutions which have their own headaches, and you can control your summing and grouping without any funkiness.