Slowly Changing Dimension Type 1 Changes using SSIS SCD Component

The Slowly Changing Dimension Component included with SSIS is one of the methods you can use to manage slowly changing dimensions, and its pretty easy to use.  The problem with the SCD component is that it performs pretty badly, especially as the number of rows in your table grows.  I would say that any Dimension with over 50,000 records in it would be too big for this component.  For small dimensions it will work just fine.

Once you have configured your source component and placed the SCD component in you design pane and hooked the two components up, double click on the SCD component to open the editor.

The first step is to connect to your destination table.  Then you need to select the column or columns in the destination table that match the key or keys from your source table.  In this instance we are choosing the UserAK column which is the Primary Key in our source Table and the Alternate Key in the destination table.  Once the Key columns are selected click NEXT.

On this screen you tell the wizard which of you non key columns are you going to update.  You do this by selecting one of the options from the dropdown menu under the Change Type column next to each Dimension Column.  There are three options:

1. Fixed Attributes, which means that the data in these columns won’t ever change, even if a change comes through from the source. 
2. Changing Attributes which corresponds to a Type 1 change.
3. Historical Attributes which corresponds to a Type 2 change.

Since that we are only worried about Type 1 changes we are going to select the Changing Attribute option.  Once all the columns are configured as you would like them click next.

Here we will configure how to handle the Fixed and Changing attributes. The first options determines how we want to handle updates that come for fixed attributes, either fail the transform or not. The next options allows you to update columns in historical records as well as the current record that are changing attributes. Once done here click next.

On the next screen you configure how to handle inferred members, for our purposes we will just leave this option disabled. Click Next and Finish.  The wizard will now add both an OLEDB Destination component and OLEDB Command component to handle the inserts and the updates and configure them for you.

Please don’t let the relative ease of setting up the SCD component drive your decision to use it.  If you expect your dimension to get fairly large there are other design patterns that you can use that will produce much better results.

Why I blog

Knowing how to do something in the various parts of the SQL Server stack can be a challenge when first starting out.  Knowing when to do something is a step up from that challenge, and requires experience on top of knowledge.   Thankfully the SQL Server community has a wealth of information and tons of people willing to share from their experiences.  This is one of the core components to the corporate culture at Pragmatic Works which is where I work.  Most of the consultants at Pragmatic Works are very involved in the community, from running and presenting at user groups to presenting at conferences like PASS and many SQL Saturdays across the country. Blogging is also a great way to get your opinions out and share your experience. I’ve been trying to ramp up on my blogging over the last few months.  There are bunch of reasons that I enjoy blogging and sharing my experience:

1. When I blog about challenges I’ve overcome at work it really reinforces the lessons learned from that day/days
2. I know that I will come across that challenge again, and I just can’t remember every solution to every problem I face, but by blogging about them I have a reference written by someone who explains things in a way that I can understand, or at least I hope I understand myself.
3. Giving back.  While I don’t post on forums with tons of questions I definitely scour them for answers to my own questions.  This blog is my way of giving back, and adding my two sense.
4. It has forced me to learn more and dive deeper into understanding the SQL Server Stack.  As a consultant our clients look to us to be experts, not only in building solutions, but in educating them in how to do this when we leave.  Writing this blog helps me organize my thoughts and look at problems not just from a “how do I solve this” viewpoint, but also from a “how do I show others how to solve this”

Going forward I’m going to try and not only explain how to build a package or write a report, but why I built it that way and why I didn’t build it another way.  I’m also hoping that from all of you out there who get a chance to read my blog, you will challenge me to explain more, force me to learn more and help you to learn more as well. 

As I sit here writing this it sounds like quite a lofty goal, especially since my blogging has not been extremely consistent over the last year.  I look at it this way, if I want to really dedicate myself to learning as much as I can about SQL Server I have to put pressure on myself to keep me motivated, and I can’t think of any way better to put pressure on myself than to publicly state a goal, and allow people to track my progress.  Thanks for reading and please come back soon and leave comments.

Thanks

Develop Packages Faster using BIxPress Package Templates: Creating a Template

The current project I am involved with has required me to develop packages as quickly as possible while also adhering design standards. The packages had a number of things in common with each other, including data sources, variables and components used to load the packages.  This is the perfect scenario for creating a template to speed up development of SSIS packages.  To create a template you can right click on the SSIS packages folder and select the BIxPress Package Builder Wizard

 

From the wizard specify the New Template from Package option. Give the template a meaningful name for example DimensionLoadTemplate.  Note that the templates are saved as .flow file.  Select the folder you want the template to be saved in.  A great tip I’ve picked up from the other consultants here at Pragmatic Works is to select a shared folder on the network to store any user created templates so that way any developer can have access to the templates.

On the next screen select the package you want to create the template from and then click next.

The next screen allows you to select which options for every  object in the package you want to set up as configurable by default. The list of configurable objects is on the left

Once you select an object from the object list the configurable properties pane is populated with all of the properties for that object.  Place a check mark next to the properties you want to be able to configure when using the template to create a new package. For the OLEDB Source Component I am choosing to make the Name of the component and the SQL Command default configurable.

Repeat this for every object you want to configure.  Once you are done with this selection process click next and then start.  Just like that you have now created a Template that you can use over and over again to speed up your SSIS development time significantly.  Try BIxPress yourself and tell me what you think.

Document your SSIS Packages with BI Documenter Part5: Scheduling your Documentation

The last entry in this introductory series to BI Documenter will cover how you can automate the documentation process.  The automation works by taking the existing solutions that have been created and in conjunction with Windows Scheduled Tasks sets up the documentation to be run on a schedule that you define. So lets get to it.

First select the solution you want to schedule.

Next select the Schedule snapshot link from the tools menu on the right hand side of the screen.

This will launch a new window where we will build the command line to schedule the snapshot. There are several options as well as an explanation on how to add the command line to the  Window Scheduled tasks.

1. Here you can again select the solution you would like to automate.
2. Set the server name, username (if needed), password(if needed)
3. The name of the metabase for the solution (the default is BIDocumenter)
4. Select the output file type (CHM, or HTML)
5. Set the Output file path
6. Here you have the option to append a date time stamp to the end of the snapshot.
7. You can choose to enable logging which will create a log for the creation of the snapshot and output to a file.
8. For the executable you need to supply the file location for the BI Documenter executable file. The default location is supplied for you, but if you changed any of the defaults when installing the program you may have to navigate to that folder on your own.
9. The Arguments section is the fully constructed command line that you will then use to schedule your job in the Windows Scheduled Tasks. You can also test the command line here which will run a complete snapshot of you solution from the command line.
10. This section walks you through using the Windows Scheduled Tasks to schedule your BI Documenter Snapshots.

When I ran through this wizard myself I was able to leave the defaults for just about everything.  The only thing I changed was the timestamp for my output file.

Now lets walk through adding this to Windows Scheduled Tasks.

I’m using Windows7 so to find the Task Scheduler I had to go to the control panel and under administrative tools I found the Task Scheduler. Once there select create Basic Task.

This will open a new window where you will give the task a name and a description(optional)

After clicking next you are presented with a list of options for when to start the task.

Click next and set the frequency and day of the week(if you choose weekly) to run the task.

Next specify what the task is to do. Select Start a Program

On the next screen is where the BI Documenter wizard we ran through earlier will be a huge help.

In box 1 enter the location of the executable for BI Documenter, and if you remember the wizard helped us find that.

In box 2 we add the arguments, which the wizard build for us after we selected all of the options.

Click next and Finish.  Now navigate to the task inside the Task Scheduler Library, right click and select run. Below you can see the newly created document.

This might seem a little bit too daunting if you are new to using the Task Scheduler, but trust me if I can do this then just about anyone can.

I hope you found this series useful and you’ve had a chance to download and test drive BI Documenter.  Please feel free to leave any comments and let me know what you think of BI Documenter.

Document your SSIS Packages with BI Documenter Part4: Object Lineage and Impact Analysis

Sorry for the long layoff since my last post in this series but the holiday season and client commitments demanded a lot of me.  I am back now and ready to continue to share with you all.

In some of the more complex SSIS packages it can be a challenge to identify what objects in your environment will either impact your SSIS package of be impacted by your SSIS package.  This is doubly true if you didn’t design the package your self.  BI Documenter has a feature called Impact Analysis/Object Lineage that helps you get a better sense of these objects. 

Lets continue with the package we’ve been using throughout these tutorials.  Select the solution from the list.

Then click on the link to launch the Impact Analysis/Object Lineage wizard.

Choose the Packages Tab and then right click on the package you want to investigate. You’ll have two options at this point.  You can render it as a diagram or render it as a list.

If you select the first option the diagram will look something like this.

The diagram shows that the package accessing two different tables.  On top of that is clearly shows you which tables are providing the data for the package and which table is consuming the data.

The list gives a slightly different view of the tables but still just as useful.

It lists them by objects used and objects using the package. Once you have select the version that suits you, you can choose to save the image or print it.  For my money I would choose to save it as part of the solution and have it available in the complete document.  It can also be saved as a separate file if you choose.

I suggest you test out BI Documenter for you self and test this and any of the other features I’ve talked about in my last few blogs.