Archive for the ‘Uncategorized’ Category

h1

No Complaining, it works.

June 24, 2012

In my job as a consultant I get exposed to a wide variety of working environments, some collaborative, some social, some conventional. Often times there are politics that I am unaware of, and quite frankly I try to stay out of, because I’m not there to take anyone’s side, I’m there to help the meet that goal.   This can be quite a stressful existence at times.  On top of all of these work environments I am introduced to, I also have the work environment back at my company office, which admittedly is better than most places I’ve been, but that doesn’t mean that from time to time there are not any complaints. 

When work at a client site is ratcheting up my stress level, either because of approaching deadlines, or on nights like tonight when I’m away from my family and my wife has to take the brunt of the parenting, or when I’m commiserating with co-workers to are facing the same challenges I am, it all seems a bit too much.  I start resenting the fact that I have to travel, and that I’m under pressure at work for a client because they have deadlines to meet.  Sometimes I just let all of these frustrations snowball until I want to call in sick for a month.  In the end regardless of the reason, I’m just not happy from time to time. After a particularly bad string of weeks I spent traveling I just felt out of control, and not the “I’m pissed and I’m not going to take it anymore”, but more of the “something’s got to give” sense. 

It was at this time, where I was looking for a way to improve my situation that I came across a book we had at the office, titled The No Complaining Rule by Jon Gordon.  This isn’t your typical self help book (not that I’m able to judge that since this is the first one I’ve read cover to cover).  It tries to teach you through the use of a fictional story.  The main character of the book, Hope, is facing problems at work that could get her fired, problems at home with her children, and health problems as well.  Add all of this up and if any one had a right to complain it was Hope.  Early in the book its easy to see the authors point is that complaining is a easy habit to get into, to feel justified in doing and can be infectious to others.

It was at this point that the authors words started to make sense to me.  I’ve definitely been in the situation where I didn’t think something was a big deal or reason to get upset, but after talking with someone else, who thought it was a huge, end of the world type deal, it became a much bigger deal to me.  Looking back it seems silly to me that I let somebody’s feelings on a topic make me miserable, but I’m sure it happens more often then we are aware of. Anyway, back to the book.

As the story moves on Hope meets someone who introduces her to the no complaining rule.  As it turns out it isn’t just one rule, but a number of rules, that revolve around complaining.  As you would expect the Hope resists, then applies the rules to her life, and after 150 pages everything is great and wonderful in here life.  While the overall “cheese” factor of the book might be a turn off I did feel like a learned some valuable takeaways from The No Complaining Rule

The most important one of which was that complaining is ok, as long as its constructive complaining.  This is more than just pointing out problems, because that’s pretty easy, and we can create more problems than actually exist once we get in the habit of complaining. This is pointing out a problem and in the next breath offering some solutions to those problems.

One of the other takeaways from the book was that complaining and negativity are more of a choice than we might believe.  This has helped me a lot over the last few months.  I’ve just made it a rule to stay out of negative conversations with people, and to try and cutoff conversations that are just people venting their frustrations and complaints at me as quickly as possible, but not getting drawn in.  Every time somebody complains I just look for a way to spin the conversation into a different direction.  These might seem like obvious coping mechanisms to some of you, but when your bogged down in the negativity it can be hard to see a way out.

This book helped me just take a look at myself and ask why I was unhappy.  I realized that I was letting things get to me too easily, I was listening to people who were unhappy and letting their state of mind rub off on me.  Once I was able to stop my own complaining and limit my interactions with other complainers I saw a dramatic change in my job satisfaction, and my general outlook as a whole.  This isn’t to say there haven’t been or never will be any complaining again, but I now at least have some ways of combating them when they do creep up.

h1

BIxPress MDX Calculation Builder

April 30, 2012

This weekend at SQL Saturday 130 I gave presented a session on Introduction to SSAS, and after my session one of the attendees came up to me and asked how to create what amounted to a rolling 30 60 and 90 days calculation inside of SSAS.  Since this was an 100 level session, the attendee hadn’t ever seen an MDX expression so walking her through the calculation would have been a bit over her head. This is a common problem for most developers just getting into cube building with SSAS.  MDX is a difficult language to pick up.  Thankfully Pragmatic Works has some software that can help with that. 

When I’ve written about BIxPress in the past it has mostly been about the SSIS features it contains, but SSIS isn’t all it does. BIxPress has an MDX calculation builder that plugs in to the Calculation Tab inside of BIDS in you SSAS project, and its not that difficult to use, meaning you don’t have to write any MDX at all. Yes I am serious and don’t call me Shirley (thank you Leslie Nielsen)

As I said before, navigate to the calculations tab in you SSAS Project inside of BIDS. Once there, and if you have BIxPress installed of course, you will see a little green calculator button has been added to the tool bar.

image

When you click on the icon, a list of available calculations and sets are presented in a new window. These include calculations like Month to Date and Percent of Parent, as well as sets returning Top 10 Count or Top 10 Percent.

image

In this example I’m going to do the Profit Margin calculation, so I select it from the list and hit next.  The first thing I’m asked is to provide the measure that represents the total for Sales.

image

Once that is selected I need to provide the Total Cost.

image

(Notice I haven’t written any MDX yet) The next screen allows me to set any conditional formatting, like text color or Red for Profit Margin of <%15 or something to that effect, but I don’t need that for this example.

On the last screen we give the Calculation an Name and associate it with the correct measure group(Internet Sales Profit Margin, and Internet Sale respectively) and then click finish.  Once that is done, build and deploy the cube, and just like that you have a new calculation in your SSAS cube, and you didn’t have to write a single MDX expression.

image

image

Take a look at this and many other feature of BIxPress by Downloading a trial version here.  Also if you have any suggestions for additional calculations, or other features you’d like to see in BIxPress or any of the Pragmatic Works software products, post them here.

h1

Document your SSIS Packages with BI Documenter Part3: Comparing Snapshots

December 1, 2011

In the last two posts we?ve discussed how to use BI Documenter to document your SSIS packages and reviewed the documents that is generated.  Another one of the very useful features of BI Documenter is the Snapshot Comparison. Let us suppose that you have scheduled your snapshots to run on a regular basis, and now you want to compare the different snapshots to view the changes that have been made to your document.  Doing this with BI Documenter is quick and easy.

First thing is to make sure you have 2 snapshots of the same object to compare.  I covered creating snapshots in the first blog of this series check it out here.  For this first comparison I actually didn?t change anything inside the package because I wanted to see what it looked like without any change.  To compare the snapshots launch BI Documenter and choose the appropriate solution.  From inside the solution click on the hyperlink “Compare Snapshots?

image

 

 

The new screen will pop up and you will tell the wizard which snapshots you want to compare and simply click the start button and your comparison will begin.

 

image

 

BI Documenter makes the comparison very easy to read.  As you can see above the color coding on the compared objects makes it easy to quickly identify which objects are New, Modified, have been deleted etc. . . Even though I didn?t change anything inside the package I still received a Modified indicator between the two snapshots on my StartTIme Variable.  Lets take a closer look at that.

image

 

The first thing item indicated as modified is the variable objects, and if we drill down into the variables we see that only one variable has been modified, the strStartTime variable.  We can drill down into the variable and see that the Variable value has been changed.  Lets take a look at why the variable value has been modified.  I didn?t change the expression at all, but the expression uses a system variable that captures the start date of the package, and since that is evaluated when the snapshot was taken the actual value was different for the two snapshots.

Now lets see what happens when I intentionally modify the package.  Lets delete one of the connection managers.

image

Just like that the new snapshot comparison makes it easy to differentiate the objects that have been changed or deleted from the ones that have gone untouched.  Go ahead and test out this feature by downloading the software here.  Tell me what you think of the software.  Be sure to check out my next blog entry when I review the impact analysis/object lineage features of BI Documenter.

h1

Document your SSIS Packages with BI Documenter Part1: Creating the Document.

November 28, 2011

Have you ever inherited an ETL environment and tied to make heads or tails of what was going on?  The previous developer didn’t leave any documentation. You have deadlines to meet and you need to know which packages do what. Does this sound familiar?  The worst is when you do this to yourself, a few years ago you created a package at someone’s request and now they’ve come back to you asking about that package and you can’t remember anything about it.  Proper technical documentation is so important in today’s world.  It can mean a few hours updating some packages for a downstream application when done right, or several days of searching and deciphering or maybe starting at square one in the worst case when documentation is lacking.  I have come across these problems working with clients on occasion and having access to a documentation tool has been a life saver.

BI Documenter is a great tool for documenting your SQL Server environment.  This does more than just document SQL Server Databases and Server Instance, though it does this very well for SQLServer 2000, 2005, 2008 and R2.  It will also document SSIS Packages (SQL Server Integration Services 2005, 2008), document SSRS Reports (SQL Server Reporting Services 2005, 2008), as well as document SSAS Cubes and Databases (SQL Server Analysis Services 2005, 2008).  Since I have been doing a lot of SSIS work lately I thought I’d show you how BI Documenter can help you document existing SSIS Packages.

BI Documenter has a very streamlined and easy to use interface. Launch BI Documenter from the start menu. When it has launched click on add new solution.  A solution here is very similar to a solution inside the BIDS environment.  The solution can contain documentation for any and all parts of the SQL Server stack you want to document.

image

Give the solution a name, and a description if you like and Save.

image

As you can see in the screenshot below I now have two solutions to choose from.  To select the solution you want to work with you can either double click on the solution or make sure the solution you want to select is highlighted and then click next.

image

On the next screen you can add what object types you want to document.   For this we will just choose an SSIS package, though we could choose multiple packages, a SQL Server Database and SSRS report all in one solution.

image

Clicking on Add Integration Services Package will bring up a new window to specify the location of the package you want to add.

image

Here you can specify the storage of the package, there are six choices, three each for SQL Server 2005 and 2008.  For each one you can specify File System, SQL Server or Package Store.  You can then set the authentication user name and password if you need to, and then specify the package path. Since we are using the file system in this example we will specify the containing folder and click next.

image

While there is only one package in this folder at the moment, BI Documenter does allow you to multi select packages here. Also please note that you can add descriptions as well as supply the package password if you have set the appropriate protection level on the package.  Once you have the packages that you wish to document selected click finish and this will bring you back to the solution page.

As you can see now there is now a package underneath the Integration Services Packages folder.

image

If you want to add any additional objects to the solution you could do that here, or if you’re done click Next.    On the next screen we will create the snapshot.  There are three tabs on this screen.  The first is the General tab. Here you can name the snapshot and give it a description.

image

The next tab it the filter tab.  Here you can select which object inside the solution you want to document.

image

As you can see you can choose to document individual components inside the  package, not just the package itself.  The third tab has more advanced configuration options that will be left at the default settings for now.  Lets click next and take the snapshot.  From this snapshot we will be able to create our documentation.

image

Choose the name of you snapshot from the dropdown, and if there are multiple objects in the snapshot you can select them as well.  When your snapshot is selected click next.  On the next screen you will finalize your documentation.  There are two choices for the document type.  It can be either a Microsoft Help File (.CHM) or an HTML file.  Choose the .CHM file here and specify a file location for the documentation.

image

The tabs across the top allow for further customization to our documentation.  The Template tab allows you to customize things like font color and size as well as add a custom logo to the document if you’d like.  The Diagram tab will allow you to create work flow diagrams to the documentation.  The Files tab allows you to add other documents and images to the document.  Of the next four tabs we only care about the Integration Objects.

image

When you’ve selected the options for the document that you want click next and once the document has been created click finish.  Your newly created documentation for the SSIS Package should now be open in front of you. 

To cover the extent of documentation that BI Documenter produces would make this blog post unbearably long, and I’m pretty sure that if I don’t want to write that much in one sitting, you probably won’t want to read that much.  Over the next few days I’ll post additional parts to this series where we can take a look at each section of the documentation that you have just created.  In the mean time please download BI Documenter and give it a try yourself.

h1

Use BIxPress Quick Deploy For Faster SSIS Deployment

October 27, 2011

Over the past few blog entries I’ve discussed multiple ways of deploying SSIS packages, including native and 3rd party methods.  Each one takes you through some sort of wizard to help you select the proper options for the package.  While this is helpful the first time or two you deploy a package to the same server, after a while it tends to be a drag to have to walk through the same steps, and the same configuration over and over. 

BIxPress has a nice feature that eliminates the need to walk through the wizard every time. Its called Quick Deploy.  With Quick Deploy you can single select a package or multi select a group of packages, as long as they are all going to the same location.  Let’s take a look at how it works.

Now let me say right off the bat that you will have to do a little configuring the first time you use Quick Deploy, or if you are using Quick Deploy to deploy to a new environment.  To use Quick Deploy right click on a package and select Quick Deploy SSIS Packages(BIxPress)

image

This will launch a new window.

image

In this new window you can see a dropdown under target location.  Since this is our first time using the Quick Deploy the list is empty.  Lets click on <New Location> and add a location to deploy our packages to.

image

The first thing you have to do with this new window is click the add location button.  I have already done that in this step so it appears grayed out.  Next you need to specify the location type, either File System, SQL Server or SSIS Package Store.  Also give this location a title so you can easily identify it from the drop down from the Quick Deploy window.   Since I’m using the File System as my location type I need to select the path, which I can browse to.  Once all that is done, click okay and it will return you to the original Quick Deploy window.

image

As you can see the drop down that was blank earlier is now populated with the location that we just configured. At this point, if you want to click Start Deploy, you can go right ahead and deploy the package and close the wizard. 

However if you want to use some of the options available from the main wizard for deploying SSIS packages with BIxPress then click the check box next to Use Options From Main Wizard.

image

By clicking on the view settings box next to Use Options From Main Wizard you can see the options from the main wizard.

image

Now if you want to change any of these options you will have to open the main BIxPress SSIS Package Deployment Wizard and change the deployment options there.

image

Click next and leave that window open. Return to the Quick Deploy window and click on the view settings box again and you will see different configurations.

image

With all of that set you can now deploy the package.  The next time you want to deploy the package to that location all you have to do is select that location from the drop down, and click Start Deploy. This is a great way to save some time by not having to run through a wizard time and time again.

h1

Convert your old DTS Packages with DTS xChange

September 28, 2011

In my last post I told you about DTS xChange Profiler and how it can help you plan and budget a DTS to SSIS migration.  Now that the planning is done, and hopefully you now have the approval to go ahead with the project, let’s talk about how DTS xChange can now help you actually convert the DTS packages to new SSIS packages.  When you launch DTS xChange from the start menu you will see a menu with 3 options, Profile, Convert and Monitor.  We are going to choose Convert.

image

On the next screen we will tell DTS xChange where our DTS packages reside, either on a file system or on SQL Server. Once we have navigated to the folder containing our packages we next have to specify where the converted SSIS packages will be stored, again this can be either on a file system or on SQL Server. You will also need to tell DTS xChange what the target SSIS platform is, in my case it is SQL Server 2008.

image 

You must also specify if you are planning to run the package on a 64 bit machine or not, but don’t worry if you don’t know there is an option for that too.  There are also some advance options you can configure on this screen as well.

Once you have set the source and destination for your packages its time to select the packages you want to convert.

image

On this screen you can see w have a list of all the DTS packages in the location we specified.  There are several options we can use to select the DTS Packages we want to convert.  My two favorite buttons on this whole screen are the Select All and the Unselect All, simply because of the convenience factor.  It annoys me to no end when there is a multi-select list and no ability to do either.  There is also a search bar at the top which will allow you to find a specific package.  Once you have selected the packages you want to convert they will appear in the pane on the right.  For convenience you can choose to remove packages from that list by highlighting it and click on the remove button, or you can uncheck the package from the list on the left.  Finally you can select Remove All to start the selection process over.  Once your list is set to your satisfaction you can move on to the next screen.

image

This section allows you to institute some best practices and also configure the migration settings. You can navigate through the different sections by either clicking on the tabs across the top or the list on the left. There are thirteen tabs you can configure in this section, but for brevity I’ll just walk you through the first few.  In the first screen you can set the options for creating configuration file including location of the files and whether they are package or connection configurations.  The next tab configures settings for child packages.  The Auditing Framework tab allows you to add an auditing framework into the newly created packages.  All of these tabs allow you to add additional functionality or best practices into you new packages. Once your done configuring the Best Practices and Migration Settings click next.

image

Verify the list of packages that will be converted and click Start Migration.  As the migration progresses you will see the statistics in the Migration Summary pane be updated. Once the packages have finished migrating you will get a Migration Alerts window to pop up which will allow you to review and warnings or errors that occur during migration.

image

By clicking on the Migration Alerts Hyperlink you can see the details of those warnings.

image

The messages you want to pay particular attention to are the Critical Warning and the Error messages. Once they have been reviewed you can choose to save a log of the messages and then click close. That is pretty much it.  Instead of having to manually select the components to replace the existing DTS package components, rewrite SQL statements etc.. or run through the wizard and in next to no time you can convert your DTS packages to SSIS using DTS xChange.