I was complaining to my co-worker the other day that my output window in Visual Studio 2010 was not showing during a build.  I usually hit Ctrl+Shift+B, and while I appreciate the minimalist UI, I like to see what it’s doing.  All I would see was the status bar text saying, “build started…” followed by, “build succeeded”, or in same cases, “build failed”.

When I was watching my co-worker, the output window popped up showing a log of the progress.  I think this is the default but I don’t recall changing my configuration.  I do normally take the output window and unpin it so it stays out of the way and off my screen while I’m coding.  Perhaps the act of doing this flips the option.  I don’t know.  What I’m after is when compiling, slide the window up from the bottom and show a log of what is compiling.

I poked around and found the option to turn this on or off and here it is for future reference.

Tools –> Options –> Projects and Solutions –> General –> Show Output window when build starts

I think it's safe to say that most (if not all) data warehousing experts recommend keeping track of ETL execution results in some way. I started working with custom audit tables back in the DTS days, starting with some code I stole from Professional SQL Server 2000 DTS by Chaffin, Knight and Robinson. Over time, I've morphed the audit database into a critical part of the Cookie Cutter SSIS methodology.

Our standard audit database contains the following schemas:

• audit
• metadata
• etl
• util
• and the ever-popular dbo, of course

Our standard audit database contains the following tables:

…and the following stored procedures:

I’ve attached a script to create the audit database and all of the above schemas, tables and procedures.  The script assumes an int type for the job_key and the package_exec_key, and you’ll need to amend the code for the database create statements to point to the correct path in your environment:

In the next post in this series, I’ll discuss the other standard databases and some utility functions.

This is an update to running cmd files from Visual Studio 2005 and Visual Studio 2008 post I did a couple year ago.  You can use this technique to run windows command files with the .bat and .cmd extensions.

Just as previously, we need to create at least one, and optionally two, external tools.

One that terminates the window after executing.

Here’s the values for you to copy+paste and screenshot.

One that leaves the command window open after executing.  Useful if you didn’t put a ‘pause’ in your command file or if you want to leave the cmd window open for additional commands.

Now we should see our new external tools available on the Tools menu.

However, we’d like to right-click on the file and run the cmd file via Solution Explorer like so:

Customizing this context menu in Visual Studio 2010 is abit different from context menu customization in VS2005/2008.

Click Tools –> Customize… to launch the Customize Dialog.

From that dialog we want to select the ‘Context menu’ radio option and the ‘Project and Solution Context Menus | Item’ from the drop down.  I’m going to put the two commands right under ‘Open With…’.  You can certainly do whatever you wish.  Start this process by clicking ‘Add Command’ button on the same dialog.

So here’s a screenshot with that above paragraph in a picture instead of words:

When we click ‘Add Command’, the Add Command Dialog will open.  Select ‘Tools’ from the Catgories list box on the left and find your external command from the command list box on the right.  Your command will be named something like ‘External Command {Number}’ where {Number} is the number in your list from your External Tools dialog.

Clear as mud?  I’m going to use this to make sure I add External Command 3 and External Command 4 to the context menu.

Now the ‘Project and Solution Context Menus | Item’ context menu should look like this in the Customize Dialog.

Close that dialog and check out your new menu items by right clicking on a file in Solution explorer.  You should see your new external commands.

For extra credit, you can go back into the Cusomize dialog and move your commands around into spot your like and alos create a Group around them.

The finished product looks like this.

Went through some interesting configurations today and wanted to document it.

Our client has a server, Windows Server 2003 SP2, in which we already have many ASP.NET 2.0 applications running, including some serving up Silverlight 3.0.  We needed to migrate one of our applications to ASP.NET 4.0 and Silverlight 4.  It was a little more involved than I would’ve thought.

Here’s the steps:

Install the .NET 4.0 Framework on the server.

This did not require a server reboot.  I have yet to have a reboot required when installing a .NET Framework version.  Perhaps I’ve been lucky so far.  This is important because you don’t want to disrupt folks using the resources on the box.

Create a separate App Pool for the web applications that are going to run under .NET 4.0.

From what I understand, you can’t have different web applications running under the same application pool.  Since this box has production and beta applications on it already, we need to create an app pool.  Nothing fancy.

Create the new virtual directory for the new application and assign it your new app pool

Nothing fancy here.  Use the properties dialog to create the application and assign the app pool.

Install the scriptmaps for ASP.NET 4.0 to the new web application.

If you don’t do this step and you try to run the application under 2.0 you will get error:

Unrecognized attribute 'targetFramework'

If you try and set the ASP.NET version using the properties panel, you will get a warning that you are going to cause a restart of the entire W3SVC service therebye killing all the work anyone may be in the middle of using existing applications.

Since we don’t want to disrupt currently running applications, we need to do this through the command line.

Open a cmd prompt and navigate to the v4 Framework directory: %WINDIR%\Microsoft.NET\Framework\v4.0.30319.  Once there, we can use the administration utility to change the application.

   1: aspnet_regiis.exe -s W3SVC/1/ROOT/SampleApp1

Here’s an example of the output when doing this against a test application.

Enable ASP.NET v4.0.30319 web service extension

If you try to hit the web application prior to this step you will receive ‘The page cannot be found’.

Enable the new version ASP.NET by using the Web Service Extensions node in IIS 6 and switching ASP.NET v4.0.30319 from Prohibited to Allowed.

Finished

From here you should be done and can now use your application right alongside older ones on the same web server.

One of the things that may be different for us here at Perkins Consulting compared to your own work environment is that we're developing data marts for a number of different clients. Consequently, we benefit from standardization - names, objects, code, etc. Please keep that in mind as you read this series - some of the naming conventions we use, for instance (and the reasons we use them), may not fit the your company's model. Feel free to modify!

That's the whole point of cookie cutter SSIS - develop once, copy many.

In this second post of the series, I want to describe the standard databases we deploy for our clients and how they fit into the cookie cutter methodology.

For each new data mart project, we deploy a minimum of the following databases:

(A note on naming: Because we like to keep all the associated databases listed together, we use naming conventions to group them. The SQL Server database names  here are the current "generic" names that are easily transportable from one installation to the next. In the past, we've also used a prefix to denote the individual client - whatever works to keep them together in SSMS. I should also mention that I always use lower case and separate key words with underscores. This means that I won't have to think too hard when I'm working on a client site with a case-sensitive server on the same day that I'm working on another site that's not case-sensitive.)

The Data Mart database is, I hope, self-explanatory. This is the final resting place of the tables - facts, dimensions, aggregates, bridge tables - that comprise the data mart. The tables in this database will differ from client to client, of course, but will always include at least one date dimension and a table that contains reporting control parameters.

The Audit database contains a set of standard tables and other objects (user-defined functions, stored procedures, etc.) that support the auditing subsystem we use to track and support ETL activity. I'll describe the audit database in more detail in a later post.

The Staging database is volatile. By that I mean that the data in the database is not expected to persist, even though the table structures will. It is a repository for interim data, and yes, sometimes you need that interim storage. Even though SSIS is very good at managing an ETL data stream from source to final target, sometimes the interim step grants you better performance or more flexibility in processing.

The Source database is the landing spot for extracted data. The data in these tables is not expected to persist either. Each table in the Source database matches a data source object somewhere in the client's system, and is truncated and repopulated with each ETL run. With our clients, we try to use the concept of "Get In, Get Out and Get On With It" with respect to their source systems. The scheduled extract of the data is usually constrained by a number of things, including:

• Status changes during the day that would affect reporting in an unexpected way
• Source system batch processing that must complete in order to present the data correctly
• Other system activity that consumes resources or locks source tables

We grab the data out of the source systems and park in in the Source database, after which we can work with it without worrying about the impact on the client's operations.

The Error database is the location for storing redirected data that would otherwise cause an insert process to fail. The tables in this database may be truncated and repopulated with each run (for data that will continue to fail until it's repaired at the source, for instance, and will continue to show up as an error until it's fixed). The data may also be allowed to persist in the Error database and be repaired and reloaded from the error tables (this is necessary for source data that cannot be recaptured in the same state from one ETL run to the next - inventory snapshot data, as an example).

Nearly every client installation requires the maintenance of some supplementary data that is not contained anywhere in the ERP system. (Can anyone say "Excel spreadsheets"?) The Lookup database is where we store persistent supplementary data. The data may be updated in its own SSIS package, but it's essentially reference data that's manually maintained by the client, in order to provide additional reporting richness to data mart tables.

The Archive database may or may not be used at any given client installation, depending on need. For instance, at one client site, we pass data back and forth from the data mart to a third-party service using the third party's defined fixed-width flat files. Troubleshooting issues with the files is much easier when we load the contents into database tables. The Archive database is used for non-data mart data only.

In the next post in this series, I'll go into more detail on the Audit database and its structures.

I've been working with SSIS since just before the RTM of SQL Server 2005. Over time, I've been able to steal tips and tricks from a number of sources (sessions at PASS Summits, notably those presented by Rushabh Mehta and Erik Veerman; Ralph Kimball, Joy Mundy and Warren Thornethwaite of the Kimball Group; SQLIS; Brian Knight and others that I apologize ahead of time for not mentioning). From all these sources, distilled by the practical work we've done with our own clients, I've evolved a standard set of patterns and practices that allow quick, efficient development for moderately sized data marts.

I call this "Cookie Cutter SSIS."

The most extreme example of the use of these concepts is what I refer to as "Data Mart in a Day." Back in 2007, we had a prospective client who was interested in the Cognos (now IBM Cognos) business intelligence toolset, but wasn't certain how the tools would work for their organization. As a result, Perkins Consulting engaged to do a limited proof-of-concept project for this client, that started with the creation of a modest data mart (four dimensions, three base facts and an aggregate fact all with a relatively small number of rows) against which to deploy the Cognos reporting tools. Because the focus of the project was on the reports, we needed to get the data mart built and populated (along with ongoing maintenance) as quickly as possible.

Okay, okay. So the complete data mart was not literally finished in a day (we did some prep work prior to the onsite development day, added one of the facts and the aggregate after the first reporting pass and completed the data validation phase afterwards.) Nevertheless, this was PDQ - and the POC data mart remained in production with minimal downtime at this client site for at least six months.

I'd like to share some of these shortcuts that I continue to use to quickly deploy data mart objects. I'll say up front that the scope of the series is strictly mechanics - how to use the "cookie cutter" method of standard objects and templates to speed data mart ETL.

We're assuming that the data discovery and design phases have already occurred. We've got our data model; we know our data source options. Now we're ready to create our databases, build our tables and use SSIS to populate our data mart.

This methodology is predicated on several things:

• A standard set of supporting databases, in addition to the data mart database itself
• Uniform handling of data mart object metadata using SQL Server extended properties
• Use of configuration files to enable data-driven dynamic Connection Managers
• SSIS package templates, pre-configured with standard variables, containers and objects
• Utility tables, functions and stored procedures
• Custom SSIS components from community resources that extend SSIS functionality
• A standard date dimension design and data source (Excel spreadsheet) that can be customized for specific client needs

In the next post, we'll talk about the standard databases that are the first step in the process.

This is just a quick post to remind myself as much as anyone else who’s interested…

I am a firm believer in “Develop Once…Copy Many” since as far as I’m concerned, the fun in development is in the problem-solving, not necessarily in multiple implementations of a solution (no matter how cool it happens to be). 

Over time, I’ve evolved a templated “cookie-cutter” approach to ETL that allows me to copy and reuse SSIS containers for data mart object processing.  Normally, this works quite well, although BIDS has some interesting ideas about how to arrange nested containers and objects, which necessitates some fancy footwork at times. 

One of the instances (other than display) in which this fails is in copying a Data Flow task with an OLE DB Source adapter that includes a parameter in a SQL command.  When copying the the data flow task, the placeholder for the parameter is persisted, but the variable mapping for the parameter is not:

So, if you’re seeking the path of least resistance in your SSIS development and choose to copy and re-use Data Flow tasks that include parameters mapped in the SQL statement of an OLE DB Source adapter, don’t forget to remap the variable.

In the first post in this series, http://blogs.perkinsconsulting.com/post/Managing-IBM-Cognos-Transformer-Using-MDL-Part-1.aspx, I described what I consider the optimum configuration for working with IBM Cognos Transformer using the MDL language.  In this post, I’ll talk about one of my favorite uses for MDL – creating a library of reusable model component “building blocks”.

Note:  For the purposes of this post, I’m using IBM Cognos series 7 with delimited text file data sources.   I’m also using the recommended model design protocol - “structural” queries to define the dimensions and “transactional” queries to define the measures and numerical accumulations. 

In our standard data mart development, we use the Kimball model of a constellation of dimensions surrounding a fact – the “star schema”.  Since we always try to deploy conformed dimensions, any given dimension may relate to multiple facts, yet the structure of the dimension and its internal hierarchies remain constant across the reporting environment.  This being the case, it’s really annoying to have to define the same dimensions in every Transformer model you create.  Furthermore, if your company has more than one Transformer designer, it might be possible (even with the best data mart design) for individual designers to configure their Transformer model differently, albeit inadvertently, and consequently introduce inconsistencies across reports.

MDL to the rescue!

Using MDL, you can develop a centrally maintained library of model object definitions which can then be agglomerated into a new model (also using MDL).  This is particularly effective with dimensions.  Measures, since they are dependent on the “transactional” or fact data sources are likely to vary more than dimensions.  This doesn’t mean you can’t make a measure building block – just that it might not be as universal as a dimension.

In order for this methodology to be effective, the Transformer object (we’ll refer to this object as a “dimension” from now on) should have a discrete data source, preferably that describes the single dimension only.  Each building block will contain the dimension and its data source – but nothing else.

As I mentioned in the first post in the series, it’s possible to create your MDL model file completely from scratch by typing commands into a text editor, but I have better things to do with my time.  Instead, I prefer to take an existing model with which I’m satisfied and break it apart into component blocks.

Make sure VerbOutput is set to 1 and ObjectIDOutput is set to 0 in your configuration file, as outlined in the first series post.

1. Open the source model with the Transformer UI.
2. Save the model as another name (say, for the dimension MDL file you’re creating), being sure to use the .mdl file type.
3. Remove all the dimensions except the one you’re creating.
4. Remove all the data sources except the ones that support the dimension you’re creating
5. Since you’re creating a generic template for the dimension, you don’t want to store any existing category values, so perform a Clean House on the model to remove all category values.
6. Save the model.
7. Open the model using a text editor (not Transformer).
8. Remove the “New Model” node at the top of the file. (Note: Be sure and do this each time you save your template from Transformer, since the New Model node will start a new model definition each time it’s encountered).
9. Save the file again. You now have a dimension template.

Even though you don’t want the “NewModel” command in all your dimension templates, in order to put together a new model using MDL, you have to have at least one “NewModel” command in the MDL code.  So, we’re going to make a “New Model” template that’s nothing but a NewModel command node.

1. Create a new model in Transformer (don’t put anything in it).
2. Save it as “NewModelTemplate.mdl” or some similar exciting name.

This is your template for new models.

Okay, now for the fun part.  We’re going to put our building blocks together to create a new model.

In addition to the item_dimension and NewModelTemplate, I’ve also got a date_dimension template.  To pull all these together, I’m going to write a small MDL code file.

1. Open a text editor such as Notepad.
2. The first line of the code file should reference the new model template, using the MDL command “OpenMDL”.
3. On each succeeding line, specify another MDL template to open using OpenMDL. Transformer treats each successive file cumulatively, so it will add any code it finds in each subsequent file to the code it’s already read.
4. Save the file with the .mdl file extension.
5. Open the file with the Transformer UI.

Transformer combines all the code in the template files to create a new model using all the building blocks you specified.  Pretty nifty, eh?

For more information on the MDL language itself, check out the documentation that ships with Transformer.  The Transformer MDL Reference guide is pretty well hidden in versions prior to series 8:

In series 8, it’s one of the selections on the Transformer Documentation page and consequently a little easier to find, although it’s now called the Transformer Developer Guide.

I started working with IBM Cognos Transformer (the tool that builds multi-dimensional cubes) back in the day – the version 6.0 day, as a matter of fact, before the advent of SQL Server Analysis Services.  I believe that at that time, the binary model files had a file extension of .pyg, and the only way I knew to manipulate the model was inside the Transformer UI.

Ah, the innocent days of (relative) youth.

Well, innocent and really annoying, too.  I’ve always had issues with programs that were only accessible one way.  I like knowing about back doors and troubleshooting methods to invoke when the compiled UI lets you down or corrupts your files. 

Then Cognos released an update to Transformer that changed the file extension from .pyg to .pyh and I discovered the .mdl file format.

When you save your model in the .mdl format, it’s stored in MDL, the proprietary text-based modeling language that is the “universal translator” between versions of Transformer. 

In those early days, other than just using the .mdl file format as a conversion function, I also used it to compress model files.  The binary .py? files never release any space, since they store information about changes to the model.  Consequently, they continue to grow forever.  The only way to wrangle them back into a manageable size is to save them as .mdl files.  When you save the .mdl file back to .py? format, all that unseen space is removed and the binary file is restored to a reasonable size.

After a while, I wondered…why bother to go back to a .py? file anyway?  The only thing you really gain using the .py? file is a slight advantage in load time.  You can do anything with the .mdl formatted model that you can with a .py? file.  And I discovered that you could do even more. 

In addition to just being able to open and edit the model file in a text editor (a huge bonus for me, see reference to back doors above), you can actually use the MDL language to alter, manipulate and create all or part of a Transformer model.  Granted, creating an entire model from scratch using MDL would not be my idea of a good time, but luckily this is not an all-or-nothing proposition. 

My two favorite uses for MDL are using it to create model “building blocks” (reusable model components that can be standardized and used across multiple models), and using MDL to automate model maintenance and cube builds. 

In this series of blog posts, I’ll cover both these scenarios, but first, we need to do a little housekeeping. 

There are two types of MDL – Structured MDL and Verb MDL.  Structured MDL was the original version, but it’s more restrictive – you have to pay attention to the order of the commands in the file, for instance.  Verb MDL is easier to work with.  However, the default setting for the MDL version for Transformer out-of-the-box is Structured MDL, so we need to change a couple of settings in trnsfrmr.ini (for pre-series 8 Transformer) or cogtr.xml (for series 8).

The VerbOutput setting toggles between Structured MDL and Verb MDL.  VerbOutput = 0 sets Transformer to Structured MDL mode.  VerbOutput = 1 sets Transformer to Verb MDL mode.  The second setting (ObjectIDOutput) affects the way object values are presented in the MDL text file.  ObjectIDOutput = 1 presents the object identifiers as numeric values, which are not easy to evaluate in a human-readable way.  ObjectIDOutput = 0 presents the object identifiers using their text value, which is much easier on the eye (and brain).

• Series 7
  • Open …cer[2,3 or 4]/bin/trnsfrmr.ini
  • In the [PowerPlay Transformer] section, set the VerbOutput option to 1
  • In the same section, set the ObjectIDOutput option to 0

• Series 6.*
  • Open …cer1/bin/trnsfrmr.ini
  • At the end of the [PowerPlay Transformer] section, add a line and type in VerbOutput=1
  • Add another line and type in ObjectIDOutput=0
• Series 8
  • Open …cognos/c8/configuration/cogtr.xml
  • Add the following node under the <Section Name="Transformer"> node:: <Preference Name="VerbOutput" Value="1"/>
  • Add the following node under the <Section Name="Transformer"> node: <Preference Name="ObjectIDOutput" Value="0" />

The following examples show the same Transformer MDL model category definitions.  First, as Structured MDL (ObjectIDOutput is the default of 1):

Next, Verb MDL with ObjectIDOutput = 1:

Next, Verb MDL with ObjectIDOutput = 0:

As you can see, setting the ObjectIDOutput to 0 presents the .mdl file in a much friendlier way.  After you set your VerbOutput and ObjectIDOutput configuration options, open one of your models from the Transformer UI, then use the Save As option to save the model as an .mdl file with the new configuration options applied.  You can then open the .mdl file in your favorite text editor for your viewing pleasure.

I’m posting this in case someone else comes across this strange bug.

I recently had a Silverlight project start displaying a vertical scrollbar in the window in IE8.

It turns out I had ‘formatted’ the page that hosts the Silverlight object control and that since the div element was on a separate line from the iframe element, it was causing the scrollbar to appear.

I tried playing around with the CSS since I don’t think it should matter how I have the angle brackets in the page but I finally gave up and just put it back the way it was.  This still feels dirty to me.

So if you are having similar issues.  Here’s at least one solution.