One of the most powerful features of BI xPress, in my opinion, is the Package Builder Wizard. The Package Builder Wizard allows an SSIS developer to create a template from a package or a from a few components of a package. The developer can then use those templates to create new packages or alter existing packages. In this short post, I’m going to walk through creating a template that contains a package configuration and then show you how to apply that template to all the packages in my SSIS project.

Recently I was consulting for a client that required me to add multiple package configurations to dozens of existing packages. Using the follow method, I completed that work in less than an hour. Needless to say, the client was very impressed and has since bought many copies of BI xPress.

If you’d like to follow along with me, visit http://www.PragmaticWorks.com and download the free trial of BI xPress.

I’ve got an SSIS projects that has a few packages in.

My requirements are that I need to put package configurations on each of these packages so that way I can manage the package’s connections outside of BIDS.

The first thing I need to do is create package configurations for the properties I’d like to configure from outside the package. In my case, I’m creating two package configurations: One for my connection to my destination database and another for the connection to my auditing database.

To create a package configuration, right-click in a blank area of the Control Flow and select Package Configurations.

With the Package Configuration Organizer, make sure to check the check box to Enable Package Configurations. Then click the Add button. Next select the type of Configuration. In this example, I’m creating an XML configuration file (.dtsConfig). Specify the file path and name of the dtsConfig file. Then click Next.

On the next screen, select the property that you want to configure. I’m going to select the ConnectionString property for the CashForClunkersDW Connection Manager. Click Next.

On the final screen, give the Configuration a name and click Finish. I then created an additional configuration file for my other connection. Here you can see the two configurations I created.

Now that I’ve created the package configurations I want to apply to all my packages. I need to create a template. To create a new template, right-click in an empty area of the Control Flow and select Package Builder Wizard (BI xPress).

When the wizard opens, select the option on the top left to create a New Template (From Selection). Specify the Template Name and select the folder to store the template. Click Next.

On the next screen, select the package objects to include in the template. Scroll all the way to the bottom of the Configurable Objects window and select the Package Configurations. Then click the Next button.

Then click Start on the next screen to create the template. Now that the template is created, now we can apply our template to the rest of our packages. In the Solution Explorer, select the packages you want to apply the new template to by holding Ctrl and clicking. Then right-click one of the selected packages and select Package Builder Wizard (BI xPress).

Make sure the option to Modify Packages (From Template) is selected. Click Next.

Then I’m going to select the template I just created.

On the next screen you should see the packages you selected in the Solution Explorer. You can select other packages or deselect. Click Next again. On the next screen, click Next again.

If a warning message appears letting you know that the package layouts will be modified, click Yes to continue. Then click Start to apply the template to the packages.

After applying the template to the other packages, make sure you open each package and check the configurations just to make sure everything went as planned before you start testing. And that should be it! You just added package configurations to all of your packages in a matter of minutes using BI xPress.

SSAS

Defining Member Properties for an Attribute in SSAS 2008

August 11, 2011 Dustin Ryan 2 Comments

I was working with a client the other day designing a couple cubes. These cubes used two pretty large dimensions, each containing many attributes. In order to increase the performance of these large dimensions, I worked with the client to identify attributes that the end users would not necessarily need to slice and dice with, but would still be useful to view the attributes. Once those attributes were identified, I displayed them as what was known as members properties. So lets walk through how we can accomplish this.

Here’s the Customer dimension in the Adventure Works 2008 R2. As you can see, there’s a whole lot of attributes, many of which probably would provide little value by allowing the end users to slice with.

Attributes with high cardinality are prime candidates to become a member property. A good example would be AddressLine1.

The first thing we need to do is set the AttributeHierarchyEnabled property to False for the AddressLine1 attribute.

This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

To allow the users to view this attribute as a member property, we need to create an attribute relationship between AddressLine1 and the attribute we would like to view AddressLine1 from. By default, all attributes are related to the key column in the dimension. But I would like our end users to be able to view AddressLine1 when the hover over the Name field of our customer.

To accomplish this, go to the Attribute Relationships tab in the dimension designer. In my Customer dimension, I’ve got a calculated column called FullName but you could use FirstName for this example. Right-click the FirstName and click New Attribute Relationship. Then in the Create Attribute Relationship window, find Address Line1 in the Related Attribute drop down list. Click OK.

After I processed the cube and connected to the AS database in Excel, I can now view the member properties for the Name attribute. Right-click the Name of a customer in your pivot table, click Show Properties in Tooltips, and click Show All Properties or select the individual attribute you’d like to view as a tooltip.

Now when I hover over the name of a customer, I see the properties of that customer. In the Adventure Works cube, you can see how many other attributes have been created as member properties of the Name attribute.

Alternatively, you can choose to display the member properties as a column in the pivot table by right-clicking the name of a customer, clicking show properties in report, and then select the field to display in the report.

This is a great way to increase performance of large, unwieldy dimensions and still give the end user their wildest dreams Winking smile .

Pragmatic Works, SSIS

Audit and Watch SSIS Packages Execute on the Server

August 8, 2011 Dustin Ryan 1 Comment

Among the many cool features of BI xPress is the extensive and robust Auditing Framework available right out of the box. With the BI xPress Auditing Framework, a user can track errors, warnings, row counts, variable, connections, source queries, and many other package properties. Whether you’re applying this incredible Auditing Framework to a single package, a dozen packages, or 100 packages, stepping through the wizard only takes a few moments. In this post, I’m going to walk you through applying the Auditing Framework to an SSIS package.

We’ve got a simple package that loads a dimension in a small data warehouse based on the Cash For Clunkers data.

Now lets add the BI xPress Auditing Framework. To apply the Auditing Framework, I can simple right-click the package in the Solution Explorer and select Add/Remove Auditing Framework (BI xPress).

This will launch the Auditing Framework Wizard. After clicking Next on the opening screen of the wizard, select the option to apply the Auditing Framework. Likewise, you can remove the Auditing Framework by selecting the other option. Click Next.

Select which packages you’d like to apply the Auditing Framework to. You can select packages in a File System, on a SQL Server, or in an SSIS project to name a few locations.

Click Next. Now you’ll have to create the Auditing database or select the Auditing database if it already exists. If you need to create the database, specify the server you want to create the database on and click the Create New Database button. Make sure you click the Create new connection button to create the Connection Manager in the SSIS Package that points to the Auditing database. You can also check the check box to create an XML Package Configuration file.

Then click the Logging Options tab. On this tab, you can specify DataFlow Logging, Variable, Connection, and other logging properties. Using these options, we can specify to log dataflow source and destinations row counts, real time data flow monitoring, connections, variables, and other properties. All of these options are selected by default so we’ll leave these selected.

On the Advanced tab, you can specify if there are any other customer user variables that you would like to log. And on the Alert Filtering tab, you can specify which warnings you do not want to log, for example any truncation warnings using the Warning Editor.

Click Next. Then click Start to apply the Auditing Framework to the package. When the process is complete, click Finish. Now that we’ve successfully added the Auditing Framework to the package, lets take a look at it.

The first thing you’ll probably notice is the new Connection Manager connecting to our Auditing database where all our auditing data is kept. Then if you open any data flow tasks, you’ll notice that they appear “jumbled up” after adding the Row Count Transforms. Simply reformat the data flow. Notice the Row Count counting rows out of the source and the second Row Count transform counting rows into the destination. The row count info is stored in variable.

This information is then written to our database using tasks located in our SSIS package’s Event Handlers, such as OnError, OnWarning, OnPreExecute, OnPostExecute, and possibly in the OnVariableValueChanged Event Handler if you specified to track variable change history.

Now that we’ve applied the Auditing Framework, I’ve deployed the package to my server and executed it a few times in order to create a history of executions. One of the really cool things about BI xPress is that after applying the Auditing Frameworks we can watch our SSIS packages execute on the server as if we were watching them in BIDS! To open the BI xPress Monitoring Console, click the BI xPress menu in BIDS and select SSIS Monitoring Console.

In the SSIS Monitoring Console, one of many reports you can use to troubleshoot your SSIS package executions is the Execution Control Flow Diagram. We can watch the Control Flow and Data Flow while the package executes on the server. Any Errors and Warnings that occur will be instantly visible and we can also see the values of logged package variables. To see a full list of all the reports available right out of the box, click this link.

The BI xPress Auditing Framework is unlike any other out of the box framework available. It’s complete and thorough and gives us, as SSIS developers, more than Microsoft ever dreamed in regards to SSIS troubleshooting. To download a free trial of BI xPress, visit PragmaticWorks.com.

MDX, SSAS

Daily Average Orders Calculation at Any Level of Date Hierarchy

August 5, 2011 Dustin Ryan 1 Comment

Today I was helping a fellow BIDN.com user come up with a calculation to figure out the average numbers of orders on day at any level of a date hierarchy. So I figured I’d post the calculation because 1) maybe it will be helpful to you and 2) I’m going to forget this calculation so I’ll at least be able to use this post as a reference.

WITH 
    //Gets a Summation of the Orders in the Fiscal period
    MEMBER [Measures].[Sum] AS 
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])
    //Counts the days in the Fiscal period
    MEMBER [Measures].[Count] AS 
    
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
    //Divides the sum of orders by the count of days to calculate the average daily 
    //orders in that fiscal period
    MEMBER [Measures].[AvgDailyIntOrdCount] AS  
    
        SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])/
        COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])})
        
SELECT     {[Measures].[Sum],[Measures].[Count],[Measures].[AvgDailyIntOrdCount]} on 0,    
{[Date].[Fiscal].Members} ON ROWS 
FROM
      [Adventure Works]

In the calculation, I use the SUM function to get an aggregations of the Internet Order Count for any level of the Fiscal Date hierarchy. I also used the Count function with the Descendants function to get a count of days beneath a member of any level of the Fiscal Date hierarchy.

It seems to work pretty good, but if you have an alternative way to calculate this, please post it in the comments section!

Errors, SSRS

SSRS Expression Iif Statement Divide by Zero Error

August 1, 2011 Dustin Ryan 3 Comments

If you’ve ever tried to use an IIF statement expression to fix an error received by dividing by zero, you probably still received the divide by zero error message. Very frustrating.

An expression like this returns an error when Sum(Fields!Beta.value) = 0:

=sum(Fields!Alpha.Value)/sum(Fields!beta.Value)

So you, being the critical thinker that you are, try the following:

=iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/sum(Fields!Beta.Value))

Alas, this will not work. Even though SSRS may evaluate your expression as true, it still will attempt to resolve the false part of your expression, which gives you the divide by zero error.

To get around this infuriating issue Microsoft should have dealt with in the first place, try this:

=iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/iif(sum(Fields!Beta.Value)=0,1,sum(Fields!Beta.Value))

This solution should fix any issues you have dividing by zero.

SSIS

Setting Up a Macro in the BIDS Toolbar to Execute an SSIS Package

July 29, 2011 Dustin Ryan

Here’s an extremely helpful little tip that my friend, Ken Hendrix, showed me the other day. Currently, to execute a single SSIS package in BIDS, you have to open the Solution Explorer, right-click the package, and click Execute. If you’re debugging a lot or do not have your Solution Explorer open, this can sometimes be a little inconvenient. So Ken came up with a Macro that you can add to your tool bar that will allow you to execute your open package with a single click.

1. The first step is to create the Macro. With BIDS open, click View, select Other Windows, and Select Macro Explorer.
2. In the Macro Explorer, expand MyMacros and right-click on Module1. Select New Macro.

3. Change the Macro Sub Name to ExecutePackage and add the Sub below:

Sub ExecutePackage()
    DTE.ExecuteCommand("ProjectandSolutionContextMenus.Item.ExecutePackage")
End Sub

Your macro should look like this:

4. Now we need to add the Macro to the toolbar in BIDS. At the end of the Standard toolbar, click the little drop down arrow, select Add or Remove Button. Then select Customize.

5. On the Commands tab of the Customize window, select Macros in the Categories pane. Find the Macro you created. It should read MyMacros.Module1.ExecutePackage if you gave it the same name as I did. Next, drag the Macro into the toolbar where you would like it to be.

6. With the Customize window still open, right-click the Macro in the toolbar, click Change Button Image, and select the icon you would like to display for your Macro. I selected the little running man. Lastly, right-click the Macro in the toolbar and click Default Style to remove the text next to the icon in the toolbar.

And you’re finished! To execute an SSIS package with the new Macro, either highlight the package in the Solution Explorer or focus on the tab with the open package. Then click the icon to execute your Macro.

I hope someone else finds this as helpful as I did. Thanks, Ken!

SSRS

SSRS 2008 R2 LookUp and LookUpSet Functions

July 27, 2011 Dustin Ryan 6 Comments

With the release of R2 came a couple of pretty nifty new expressions in SSRS. The LookUp function allows us to perform a look up against a separate data set in our report using a field in our source data set where there is a 1 to 1 relationship. Likewise, the LookUpSet function allows you to do a look up values in a separate data set where there is a 1 to many relationship.

The LookUp and LookUpSet functions are useful when you cannot write a query to join our two data sets together. So maybe we have a shared data source in our report that returns some data that we need to join to an embedded data set in our report. We could use the LookUp and/or LookUpSet functions to bring those two data sets together within our report.

Let’s walk through a couple examples so we can see how to use these new functions. First, lets check out the LookUp function. I’ve got a shared data set in my Report Project that returns the total Reseller sales but in my report I have an embedded data set that has all my Resellers names. I’ll use the LookUp function to bring those two data sets together.

Our expression to look up the TotalSales from our shared data set looks like this:

=LookUp(Fields!ResellerKey.Value,Fields!ResellerKey.Value,Fields!TotalSales.Value,"ResellerSales")

Using the LookUp function, I’m able to look up the Total Sales Amount from the destination data set and bring it together with our source data set.

Now lets take a look at the LookUpSet function. At first glance, it appears to work exactly the same as the LookUp function.

=LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders")

But if we run our report with this expression, we’re going to get an error.

This is because the LookUpSet function brings back a collection of results. We cannot simply display the results in a textbox in our tablix. We need to concatenate the results into a string. To do this, we are going to use the Join function with our LookUpSet function.

=Join(LookUpSet(Fields!CustomerKey.Value,Fields!CustomerKey.Value,Fields!OrderNumber.Value, "CustomerOrders"),"," + vbcrlf)

The Join function takes a collection of objects and delimits them with our specified delimiter. In this case, our delimiter is a comma with the VB carriage return line feed (vbcrlf). Using the Join and LookUpSet functions, we are able to look up all the order numbers from the destination data set and bring those into the same tablix as our source data set.

Pretty cool, huh?

MDX, SSAS, SSRS

Use Date Picker Control with MDX Based Reports

July 26, 2011 Dustin Ryan

If you’ve done a couple cube based reports, you’ve run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can’t use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports.

1) In the Report Data pane of BIDS, open the parameter properties for the parameter(s) you are using to specify a date or begin and end dates and change the data type to datetime. Change available values to None.

2) Go ahead and delete any hidden datasets that populate the drop down lists for your date parameters. You won’t need those with the date picker control.

3) In your data set that populates your report, open the properties, and in the parameters view, you want to use an expression on the parameter value field. So instead of the parameter value containing something like "=Parameters!DateDate.Value", you want to change it to something like:

="[Date].[Date].&[" + Format((Parameters!DateDate.Value), "MM-dd-yyyy") + "]"

Keep in mind that you’ll need to perform step 3 on any data sets that use your date parameters.

The key is to format the expression in such a way that it looks exactly like the members of your date dimension. Once you do that, click preview and you have the beautiful and end-user friendly date pickers in your cube based reports!

You can also go back to your parameters to specify default values using today and/or dateadd (ex. "=dateadd("M",-12,today)").

One of the downsides to using the date picker control with reports using your cube is a data source is that if the end user selects a date in the date picker that is not in your cube, your report is going to blow up, so just be aware of that.

If you have any questions, feel free to leave a comment!

Uncategorized

Which piece of the MS BI stack are you most interested in learning?

July 26, 2011 Dustin Ryan

SSIS

SSIS Package Development Troubleshooting Tips

July 25, 2011 Dustin Ryan

When developing an SSIS package, sometimes it can be frustrating trying to troubleshoot various issues and faults with the package. And if you’re not a seasoned SSIS developer, you may not be sure about the best way to solve your problem. Thankfully, Microsoft has seen fit to bless us with some useful tools for troubleshooting.

Control Flow Troubleshooting

When working in the Control Flow, there are several useful ways to troubleshoot during the development of your SSIS Package:

1) Use breakpoints! Breakpoints can be applied to an individual task or at the package level. A break point will pause the execution of the package at a certain point. You can specify a package to pause at pre-execution, post-execution, on warning, or on error event of a single task, container or package, just to name a few. To enable a breakpoint, right click the task you want to enable your breakpoint on and select Edit Breakpoints. You’ll then be able to specify on which event you would like to pause the package. After adding a breakpoint to a task, you will see a red dot on the task. During execution of the task, you will see the red dot change. To continue execution of a package after it has paused at a breakpoint, simply click the green “Play” button.

2) Check the progress tab! The Progress tab displays valuable information about the execution of your SSIS package. In the Progress tab, SSIS will list all tasks in order of execution and display start and finish times at the task and package level. It even contains message about any errors or warnings that may have occured during execution. If your package failed, this is where the error will be displayed!

3) Use the Debug Windows! The various Debug windows provide you with valuable information during the execution of a package that has breakpoints. Error messages and warning are displayed in the Debug Output window, as well as value of variables during execution. To view the various Debug windows, add a breakpoint at the Pre-Execute event of the package and execute the package. Then in the main menu at the top, click Debug, select Windows, and you will be able to select the various debugging windows. To view the values of variables during run time, open the Watch window and type in the name of the variable you’d like to watch. The watch window will then display the data type and value of any variable you’d like to see.

Data Flow Troubleshooting

Troubleshooting the execution of a Data Flow Task is a little different than troubleshooting the Control Flow because you cannot add a breakpoint to a Data Flow Transform. But there are still tools available to us as developers.

1) Use Data Viewers! Data viewers allow us to watch the data as it passes between transforms. A data viewer can display data in a grid, histogram, scatter plot, or column chart format. To add a data viewer to a Data Flow, simply right click a precedence constraint and select Data Viewers. You can then Add and Delete a data viewer.

2) Use a Conditional Split as a destination! This is kind of a little trick that SSIS developers use that allow you to execute a data flow without writing to a destination. Simply use a Conditional Splitl as the last transform in place of a destination in the data flow and place a data viewer on the precedence constraint directly before the Conditional Split. You can then copy and paste the data from the data viewer into Excel or Note Pad for further review. Or if your place of employment has blessed you by purchasing Task Factory, simply use the Terminator Destination, which requires no configuration and acts as a dead end for your Data Flow during development.

3) Use Row Count transforms! Placing a Row Count directly after the source and directly before the destination in a data flow will allow you to closely monitor the number of rows passing through your data flow.

4) And lastly, use the Progress Tab and Debugging Window just like when you are troubleshoot your Control Flow.

Even while using each and every last one of these tips I’ve covered, every once in a while you’re going to come across on error that offers no real explanation or solution. Which brings me to my final and most important tip to remember when trouble shooting your SSIS package: GOOGLE!

Data and Analytics with Dustin Ryan

Quickly Apply SSIS Package Configurations to Multiple Packages with BI xPress

Like this:

Defining Member Properties for an Attribute in SSAS 2008

Like this:

Audit and Watch SSIS Packages Execute on the Server

Like this:

Daily Average Orders Calculation at Any Level of Date Hierarchy

Like this:

SSRS Expression Iif Statement Divide by Zero Error

Like this:

Setting Up a Macro in the BIDS Toolbar to Execute an SSIS Package

Like this:

SSRS 2008 R2 LookUp and LookUpSet Functions

Like this:

Use Date Picker Control with MDX Based Reports

Like this:

Which piece of the MS BI stack are you most interested in learning?

Like this:

SSIS Package Development Troubleshooting Tips

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Dustin Ryan is a Data Specialist interested in Azure, SQL Server, and Power BI.