geekstentialism

New Feature and Refactoring Freedom: Determining Code Coverage in Sails.js

2014-07-04T10:53:00.003-07:00

Happy Independence Day! To celebrate the USA's birthday, I offer a quick post on exercising your freedom as a developer to implement new features or refactor code without flying blind or worrying what you may have missed or broken. I'm of course talking about some code coverage in Sails.js.

In my prior post on Web API development in Sails.js, we utilized Grunt and Mocha as the primary tools for our unit tests. However, what we did not review was how to target what you missed in your unit tests. After all, what good are running unit tests if you don't know where your gaps lie?

Istanbul (not Constantinople) to the rescue! Istanbul is a fantastic JavaScript library which produces all manners of code coverage report formats. For the purpose of this exercise, we will simply have it generate a /coverage folder in our root with nicely formatted HTML showing us where our code coverage gaps are.

Referring back to our sample Sails.js project, the AgileMovieDB API, we'll need to first go into the command line and type in these commands to install Istanbul and the grunt-mocha-istanbul Grunt task library.

sudo npm install -g istanbul
sudo npm install grunt-mocha-istanbul --save-dev

The second bit will save a dev dependency of grunt-mocha-istanbul to your package.json. Fantastic.

Now, we need to do a little bit of Gruntfile.js hacking.

Gruntfile.js: mocha_istanbul task setup in initConfig

mocha_istanbul: {
            coverage: {
                src: 'tests', // the folder, not the files,
                options: {
                    mask: '**/*.spec.js'
                }
            },
            coveralls: {
                src: 'tests', // the folder, not the files
                options: {
                    coverage:true,
                    check: {
                        lines: 75,
                        statements: 75
                    },
                    root: './api', // define where the cover task should consider the root of libraries that are covered by tests
                    reportFormats: ['html']
                }
            }
        },

Gruntfile.js: Register a Coverage Task

// Code Coverage with Mocha and Istanbul (not Constantinople)
    grunt.registerTask('coverage', ['mocha_istanbul:coverage']);

Congratulations, folks, we've got code coverage in our Sails.js application. How do we run it? Fire up the command line and type in this command: grunt coverage.

You'll notice a new /coverage folder in the root of the /Server folder. Expand it, then expand the lcov-report folder. Open up index.html in your favorite browser, and you should see the following output:

The links for each of the folders are hot, so let's examine one of our managers/ that's giving us some code coverage issues.

As you can see, Istanbul gives us some fantastic qualitative and quantitative measures. What's highlighted in red is what was not tested, and the number to the right of the line number indicates the # of times that line of code was hit. Brilliant.

Using Istanbul code coverage with Mocha unit tests will enable us the freedom to implement features and refactor our Sails.js code without the worry of "flying blind" as our software evolves. I hope this post proves useful in your continuing quest for quality MEAN stack development using Sails.js for the Web API!

How to Unit Test a Sails.js Model Without Lifting Sails

2014-06-30T17:55:00.002-07:00

The Problem

As a quick follow-up to the previous post about creating a traditional MVC-style Web API using Sails.js, I did get some questions around how to test models in Sails, especially those that have instance methods and override their Waterline lifecycle callbacks, such as afterCreate and afterUpdate. This post is for you guys!

The Approach

First, we need to ensure that Waterline is installed as a dev dependency. This is accomplished by going to the command line of your Sails application's root and typing in sudo npm install waterline --save-dev (or just npm install waterline --save-dev for Windows folks).

Note that this approach is confirmed to work in Sails 0.9.16, and the Waterline approach used here may not jive with 0.10.x, as some readers have reported.

Using the same exact unit testing framework (Mocha) as the last article, let's assume we fortified our Movie model with an "unbiasedCriticism" instance method and some lifecycle callbacks using Socket.io, defined as follows:

/api/constants/SocketIOConstants.js

/**
 * Events for Socket.io pub/sub
 * @type {string}
 */
exports.EVENT_MOVIE_CREATED = 'movie:created';
exports.EVENT_MOVIE_UPDATED = 'movie:updated';

/api/models/Movie.js

var SocketIOConstants = require('../constants/SocketIOConstants');

/**
 * The Movie model
 */
var Movie = {
    tableName: 'Movie',
    attributes: {
        movieId: {
            type: 'INTEGER',
            required: true,
            unique: true
        },
        name: {
            type: 'STRING',
            required: true
        },
        releaseDate: {
            type: 'DATE',
            required: true
        },
        rating: {
            type: 'STRING',
            required: true
        },
        grossEarnings: 'FLOAT',

        // Instance methods
        unbiasedCriticism: function() {
            if(this.name.toLowerCase().indexOf('godfather') > -1) {
                return 'PHENOMENAL!';
            }
            else {
                return 'It stinks!';
            }
        }
    },

    // Lifecycle callbacks
    afterCreate: function(newRecord, next) {
        sails.io.sockets.emit(SocketIOConstants.EVENT_MOVIE_CREATED, newRecord);
        next();
    },

    afterUpdate: function(updatedRecord, next) {
        sails.io.sockets.emit(SocketIOConstants.EVENT_MOVIE_UPDATED, updatedRecord);
        next();
    }
};

module.exports = Movie;

Again, as a review from the last post, a unit test is one that can stand alone and tests specific functionality only on the module/component you are referencing. Any external dependencies should be mocked in some form. Now, for our unit test!

/tests/models/Movie.spec.js

var MovieModel = require('../../api/models/Movie'),
    assert = require('assert'),
    _ = require('underscore'),
    Waterline = require('waterline'),
    SocketIOConstants = require('../../api/constants/SocketIOConstants'),
    diskAdapter = require('sails-disk'); // FYI, sails-memory doesn't seem to play well in a mock situation

var Movie, movie, movieCollection, sails;
var socketEvents = [];


/**
 * Unit tests for the Movie Model.  Intended only to test attribute methods, not Sails or Waterline functionality.
 */
describe('The Movie model', function () {

    /**
     * Create a "sham" model from sails-disk
     */
    before(function(done) {
        // Create a sails stub for socket.io
        sails = {
            io: {
                sockets: {
                    emit: function(event, data) {
                        socketEvents.push(event);
                        return;
                    }
                }
            }
        }
        global.sails = sails;

        // Create a model using sails-disk
        MovieModel.adapter = 'disk';
        Movie = Waterline.Collection.extend(MovieModel);
        new Movie({ adapters: { disk: diskAdapter }}, function(err, collection) {
            if (err) {
                done(err);
            }
            else {
                movieCollection = collection;
                collection.create({ movieId: '1', name: 'The Godfather', releaseDate: new Date(), rating: 'R'})
                    .done(function(err, mockMovie) {
                        if (err) {
                            done(err);
                        }
                        else {
                            movie = mockMovie;
                            done();
                        }
                    });
            }
        });
    });

    describe('attribute methods', function () {
        it('should produce unbiased criticism of the Godfather', function () {
            var criticism = movie.unbiasedCriticism();
            assert(criticism === 'PHENOMENAL!');
        });

        it('should have emitted a movie created event on the socket', function() {
            assert(_.contains(socketEvents, SocketIOConstants.EVENT_MOVIE_CREATED));
        });
    });

    /**
     * Delete everything from sails-disk.  A clean HD is a happy HD.
     */
    after(function(done) {
        movieCollection.destroy().done(function(err) {
            if (err) {
                done(err);
            }
            else {
                done();
            }
        });
    });
});

What The Heck Did We Do?

Our Movie model has two lifecycle callbacks (afterCreate and afterUpdate) and one instance method (unbiasedCriticism). In order to ensure that the model's methods behave as we expect, we need to spin up a "sham" Waterline. In this case, we are spinning up Waterline using sails-disk. We then "create" a sample object on which we can make assertions. Finally, we need to create a stub sails literal that we can throw in global that has io.sockets.emit defined. This all happens in the asynchronous "before all" hook of Movie.spec.js.

Once that's all in place, assertions should be elementary. The unbiasedCriticism method indeed shows that the function brings back 'PHENOMENAL!' and that our stubbed sails.io.sockets fired off the Movie Created event we established in SocketIOConstants.js.

The "after all" hook simply nukes the object we created on disk so we don't waste any of our disk space. Easy peasy lemon squeezy.

Other Alternatives

Fernando De Vega created a library called Wolfpack that does the stubbing work for you, like we did above with Waterline. I haven't messed around with it personally, but it certainly looks promising! I simply wanted something that I could fully control and understand, hence the approach above.

I hope this helps your unit testing efforts as you continue to Sail away!

Growing Up and Growing Large: Modern JavaScript Web Development Using Sails.js and AngularJS (Part 2 of 3)

2014-06-20T05:57:00.000-07:00

Sails.js: A Server-side Solution

One of the early criticisms (and also strengths) of JavaScript, and open source architectures in general, was the conundrum of "too many options". After all, it was scientifically proven that people (yes, even software developers) prefer solutions over individual components. Part of growing up is learning from past mistakes, and the JavaScript community has responded in kind, particularly the folks at Giant Squid with Sails.js.

Sails.js attempts to reel in the noise in choosing components for a server-side JavaScript framework. It is a proper MVC framework, inspired by Ruby on Rails, that gives a developer everything you could possibly need: an MVC pattern, ORM, multiple adapters for data stores, Socket.io support, intelligent routing, and much more, all in a carefully curated solution so that a developer can focus on making great software products versus worrying about getting components to play nicely with each other.

Great, So What Are We Building?

For this exercise, we're going to build a simple movie database called the AgileMovieDB. Let's assume a directory structure of /AgileMovieDB/Server which will house the Sails.js source code and /AgileMovieDB/Web which will house the AngularJS source code.

This will be a simple application where a user can type in the name (or parts of the name) of a movie or actor on the Angular UI, and our Sails.js Server API spits back search results.

For the "get on with it" crowd, direct your Git to the GitHub Repo :)

Prerequisites

There's a few pieces of software you ought to install first before we begin this exercise:

Node.js: Server-side JavaScript! For *nix users, make sure to also install npm, Node.js' equivalent of NuGet or Maven.
MongoDB: A NoSQL document store which will act as our persistence layer
WebStorm: Quite simply, the best IDE for JavaScript development, in my humble opinon
Mongo Plugin for WebStorm: Allows you to browse MongoDB servers right in WebStorm
(Windows Only) MongoVue: A GUI for Windows to browse through Mongo databases

Time to Set Sail

So, let's get started. While Sails.js could stand alone as a "pure" server-side MVC application, complete with support for template engines such as Jade, we are simply using it as a Server API that backs an AngularJS front-end. So, in other words, this application will serve as the RESTful services that act as the gatekeeper to your domain objects, as well as house the domain objects themselves.

Getting a skeleton Sails application called Server is as easy as this:

In a terminal or cmd window in the aforementioned AgileMovieDB folder, type sudo npm -g install sails (or simply npm -g install sails for Windows)
Type in sails new Server
Type in cd Server
Type in sails lift
Open a web browser and go to http://localhost:1337
Victory dance

Congratulations, you have your very first Sails.js application!

Understanding the Overall Sails.js Architecture

When you open the /Server directory in WebStorm, you're going to see the basic Sails.js application structure. It helps to understand what exactly is under the hood before we start hacking away.

/api: 99% of your coding will be done here. This folder contains separations of the MVC pattern, shared services, adapters, and models.
/assets: If we were building a classic MVC web application, this is where your UI/UX artifacts would live -- CSS/LESS files, image files, and the like
/config: Under-the-hood configurable items for Sails.js, such as what database to connect to, CORS policies, authentication policies, HTTP error handlers, etc
/node_modules: Being a Node.js application with library dependencies managed by npm, this is where said library dependencies will live.
/views: Again, if we were building a classic MVC web application, this is where your HTML templates would live. Out of the box, Sails.js supports Jade (as denoted by the *.ejs files)

You'll also notice a few files created by Sails on the root.

app.js: Instead of "sails lift", you may optionally run this using Node.js. When developing in WebStorm, this would be your entry point for debugging.
gruntfile.js: The main file for your Grunt task runner. In JavaScript development, these task runners are your best friends, helping immensely with build automation, running unit tests, and the like

Got It. So, Let's Talk About this API Folder

Naturally, there's a separation of concerns when developing a Sails application. It should come as no surprise that this follows the tried and true Model-View-Controller (MVC) pattern. Let's take a look at each subfolder in /api and understand what goes where.

/adapters: If you must create a custom adapter that Sails' built-in ORM, Waterline, needs to use, your custom adapter goes in here and gets registered in global. 9 times out of 10, you won't need to, but there is documentation for any edge cases.
/controllers: Self-explanatory -- your controllers go here. Up-front, they are code-generated using the sails command, which we will see shortly.
/models: Self-explanatory -- your domain models go here, annotated in a fashion understood by Waterline, which we will see shortly. Any Model object gets thrown into global.
/policies: This folder houses any custom access control code. In short, they enforce who can and can't access controllers. We won't get into this level of complexity for this series, but the documentation has an excellent overview.
/services: This folder houses any application-wide "services" in Sails parlance. These services could be, say, Repositories that interact with Waterline and MongoDB, or services in charge of pulling down and transforming JSON's from external API's. Any file created here gets thrown into global.

10-4, Ghost Rider. Let's Sort Out this Domain.

Now that we have a lay of the land in Sails.js, we can begin development. Let's attack this with a bottom-up approach, being that we have a well-defined (and simple) UI interaction and a well-understood domain. We're going to track Movies and people involved with Movies.

Being that we're using a NoSQL data store, we could just model one Movie table and domain object, but let's make it a little relational to ease us normalization junkies into this brave, new, schema-less world. So, that would involve a Movie entity, a Person entity, and a many-to-many relationship between both via an intermediary entity called MoviePerson. (a Person can be multiple characters in many movies and also could be multiple roles in the crew, i.e. Executive Producer and Director...and movies contain many people playing multiple roles in the cast or crew).

So, with that in mind, we have three models to build in /api/models: Movie, Person, and MoviePerson.

Let's annotate these objects in the format understood by Waterline.

Movie.js

/**
 * The Movie model
 */
var Movie = {
    tableName: 'Movie',
    attributes: {
        movieId: {
            type: 'INTEGER',
            required: true,
            unique: true
        },
        name: {
            type: 'STRING',
            required: true
        },
        releaseDate: {
            type: 'DATE',
            required: true
        },
        rating: {
            type: 'STRING',
            required: true
        },
        grossEarnings: 'FLOAT'
    }
};

module.exports = Movie;

Person.js

/**
 * The Person model
 */
var Person = {
    tableName: 'Person',
    attributes: {
        personId: {
            type: 'INTEGER',
            required: true,
            unique: true
        },
        firstName: {
            type: 'STRING',
            required: true
        },
        lastName: {
            type: 'STRING',
            required: true
        },
        dateOfBirth: {
            type: 'DATE',
            required: true
        },
        nationality: 'STRING',
        grossEarnings: 'FLOAT'
    }
};

module.exports = Person;

MoviePerson.js

/**
 * The MoviePerson model
 */
var MoviePerson = {
    tableName: 'MoviePerson',
    attributes: {
        movieId: {
            type: 'INTEGER',
            required: true
        },
        personId: {
            type: 'INTEGER',
            required: true
        },
        role: {
            type: 'STRING',
            required: true
        },
        type: {
            type: 'STRING',
            required: true
        }
    }
};

module.exports = MoviePerson;

Simple enough. But, wait, we forgot something didn't we? We need to make sure our persistence layer is defined as MongoDB and pointed properly to our installed local MongoDB instance. For that, let's first obtain the sails-mongo adapter.

Fire up a command line (or Alt + F12 from WebStorm) and make sure you're on the /Server folder
Type in sudo npm install sails-mongo --save (or simply npm install sails-mongo --save)

Now, let's shuffle over to /config/adapters.js. It should look like this.

/config/adapters.js

/**
 * Global adapter config
 * 
 * The `adapters` configuration object lets you create different global "saved settings"
 * that you can mix and match in your models.  The `default` option indicates which 
 * "saved setting" should be used if a model doesn't have an adapter specified.
 *
 * Keep in mind that options you define directly in your model definitions
 * will override these settings.
 *
 * For more information on adapter configuration, check out:
 * http://sailsjs.org/#documentation
 */

module.exports.adapters = {

  // If you leave the adapter config unspecified 
  // in a model definition, 'default' will be used.
  'default': 'mongo',

    // MongoDB
    mongo : {
        module: 'sails-mongo',
        host: '127.0.0.1',
        port: 27017,
        database: 'AMDB',
        user: '',
        password: ''
    },

  // Persistent adapter for DEVELOPMENT ONLY
  // (data is preserved when the server shuts down)
  disk: {
    module: 'sails-disk'
  },

  // MySQL is the world's most popular relational database.
  // Learn more: http://en.wikipedia.org/wiki/MySQL
  myLocalMySQLDatabase: {

    module: 'sails-mysql',
    host: 'YOUR_MYSQL_SERVER_HOSTNAME_OR_IP_ADDRESS',
    user: 'YOUR_MYSQL_USER',
    // Psst.. You can put your password in config/local.js instead
    // so you don't inadvertently push it up if you're using version control
    password: 'YOUR_MYSQL_PASSWORD', 
    database: 'YOUR_MYSQL_DB'
  }
};

So, now, our house is order from a domain and persistence perspective. We have Waterline domain objects that will translate to separate Mongo collections, and our application is now configured to use our local MongoDB instance.

Surfacing Data to the Controller

In classical MVC frameworks like ASP .Net MVC and Spring MVC, it's often an acceptable pattern to have some sort of intermediary between Model/ORM interaction and the Controllers themselves. In short, the separation of concerns would be Model/ORM/DB --> Business Logic --> Controllers...that is, controllers ought to only be concerned about transport of data, usually in the form of data transfer objects, or DTO's, and there will be a layer between it and the Model/ORM that handles any sort of business logic manipulation. We're going to follow this pattern for this thought exercise.

Sails.js, out of the box, offers this functionality in the way of the /api/services folder. Any exports code created here can be accessible globally. However, for the sake of argument, let's say we want to keep our Sails.js app as skinny as possible and not clutter up global and only "use what we need" in a Controller. Following this pattern, let's establish another folder in /api called /managers. Again, a Manager's concerns would be any sort of Model and Business Logic interaction, and it would return resulting DTO's back to the Controller.

We'll create the SearchManager as a standard prototypical JavaScript object with two methods: searchByMovie and searchByPersonText. Again, our application will have the ability to search for parts of a word or words in movie titles as well as people involved with movies.

We'll need a few DTO's, per our pattern, to reflect this -- one for each of the Model objects (Movie and Person), a specialized one called CastCrewDTO which is meant to represent a Person belonging to a movie playing any number of roles, and a SearchResultDTO to consolidate all of this for the controller to return to consuming applications. We're going to create a /api/dto folder and throw these JavaScript objects in there.

/api/managers/SearchManager.js

var _ = require('underscore');
var Promise = require('promise');
var MovieDTO = require('../dto/MovieDTO');
var PersonDTO = require('../dto/PersonDTO');
var CastCrewDTO = require('../dto/CastCrewDTO');

/**
 * The Manager layer acts as an intermediary between controllers and the domain
 * For this pattern, the Manager layer simply spits back DTO's for the Controller
 */
function SearchManager() {

};

/**
 * Searches movies by a particular text
 * @param movieText
 * @returns {Promise}
 */
SearchManager.prototype.searchByMovie = function (movieText) {
    var movieDtos = [];
    var self = this;
    return new Promise(
        function (fulfill, reject) {
            Movie.find({ or: [ { like: { name: '%' + movieText + '%' }} ] })
                .done(function (err, movies) {
                    var movieIds = [];
                    if (err || _.isUndefined(movies)) {
                        reject(err || movies);
                    }
                    else {
                        _.each(movies, function (movie) {
                            var movieDto = new MovieDTO();
                            // Add the DTO
                            movieDto.fromEntity(movie);
                            movieDtos.push(movieDto);
                            movieIds.push(movie.movieId);
                        });

                        // Get cast and crew
                        MoviePerson.find({ movieId: movieIds })
                            .done(function(err, moviePersons) {
                                var personIds = [];
                                _.each(moviePersons, function(moviePerson) {
                                    personIds.push(moviePerson.personId);
                                    var castCrewDto = new CastCrewDTO();
                                    castCrewDto.personId = moviePerson.personId;
                                    castCrewDto.role = moviePerson.role;
                                    var movieDto = _.find(movieDtos, function(movieDto) { return movieDto.movieId === moviePerson.movieId });
                                    if (moviePerson.type === 'Cast') {
                                        movieDto.cast.push(castCrewDto);
                                    }
                                    else if (moviePerson.type === 'Crew') {
                                        movieDto.crew.push(castCrewDto);
                                    }
                                });

                                Person.find({ personId: personIds })
                                    .done(function(err, persons) {
                                        _.each(persons, function(person) {
                                            _.each(movieDtos, function(movieDto) {
                                                // TODO: Do a filter instead of a find.  A Person can play multiple roles
                                                var matchedCast = _.find(movieDto.cast, function(castDto) { return castDto.personId === person.personId });
                                                var matchedCrew = _.find(movieDto.crew, function(crewDto) { return crewDto.personId === person.personId });
                                                if (!_.isUndefined(matchedCast)){
                                                    matchedCast.firstName = person.firstName;
                                                    matchedCast.lastName = person.lastName;
                                                    matchedCast.displayName = person.lastName + ', ' + person.firstName;
                                                }
                                                if (!_.isUndefined(matchedCrew)){
                                                    matchedCrew.firstName = person.firstName;
                                                    matchedCrew.lastName = person.lastName;
                                                    matchedCrew.displayName = person.lastName + ', ' + person.firstName;
                                                }
                                            });
                                        });
                                        fulfill(movieDtos);
                                    });
                            }
                        );
                    }
                }
            );
        }
    );
};

/**
 * Searches Persons by a particular text
 * @param personText
 * @returns {Promise}
 */
SearchManager.prototype.searchByPersonText = function (personText) {
    var personDtos = [];
    return new Promise(
        function (fulfill, reject) {
            Person.find({
                or: [
                    { like: { firstName: '%' + personText + '%'} },
                    { like: { lastName: '%' + personText + '%'} }
                ]
            })
                .done(function (err, persons) {
                    if (err || _.isUndefined(persons)) {
                        reject(err || persons);
                    }
                    else {
                        _.each(persons, function (person) {
                            var personDto = new PersonDTO();
                            personDto.fromEntity(person);
                            personDtos.push(personDto);
                        });
                        fulfill(personDtos);
                    }
                });
        }
    );
};

module.exports = SearchManager;

/api/dto/CastCrewDTO.js

function CastCrewDTO() {
    this.firstName = '';
    this.lastName = '';
    this.displayName = '';
    this.role = '';
    this.personId = '';
};

module.exports = CastCrewDTO;

/api/dto/MovieDTO.js

function MovieDTO() {
    this.id = '';
    this.movieId = '';
    this.name = '';
    this.releaseDate = new Date();
    this.rating = '';
    this.grossEarnings = 0.00;
    this.cast = [];
    this.crew = [];
}

MovieDTO.prototype.fromEntity = function(movie) {
    this.id = movie.id
    this.movieId = movie.movieId;
    this.name = movie.name;
    this.releaseDate = movie.releaseDate;
    this.rating = movie.rating;
    this.grossEarnings = movie.grossEarnings;
};


module.exports = MovieDTO;

/api/dto/PersonDTO.js

function PersonDTO() {
    this.id = '';
    this.firstName = '';
    this.lastName = '';
    this.displayName = '';
    this.dateOfBirth = new Date();
    this.nationality = '';
    this.grossEarnings = 0.00;
}

PersonDTO.prototype.fromEntity = function(person) {
    this.id = person.id;
    this.firstName = person.firstName;
    this.lastName = person.lastName;
    this.displayName = person.lastName + ', ' + person.firstName;
    this.dateOfBirth = person.dateOfBirth;
    this.nationality = person.nationality;
    this.grossEarnings = person.grossEarnings;
};


module.exports = PersonDTO;

/api/dto/SearchResultDTO.js

/**
 * A DTO to encapsulate search results for movies or persons
 * @param User
 * @constructor
 */
function SearchResultDTO() {
    this.searchText = '';
    this.movieDtos = [];
    this.personDtos = [];
}

SearchResultDTO.prototype.fromMovieDtos = function(movieDtos) {
    this.movieDtos = movieDtos;
};

SearchResultDTO.prototype.fromPersonDtos = function(personDtos) {
    this.personDtos = personDtos;
};


module.exports = SearchResultDTO;

You'll notice the SearchManager directly invokes the Models we created above. WebStorm might bark at you for this, but if you recall, anything created in /api/models gets added to global, so when this runs, this code will resolve just fine!

The DTO's themselves are nothing special -- plain JS objects. You'll notice the PersonDTO and MovieDTO have fromEntity methods that convert the models to the DTO's. Since CastCrewDTO and SearchResultDTO are specialized logical objects, no such "fromEntity" methods exist for these and we'll set the properties for each as we go.

Controller Methods for Consuming Applications

As was mentioned earlier, controllers can be automatically code-generated. Let's say we want to create a SearchController, whose methods sort of match what we made above in the SearchManager, with one method called byActorOrMovie. It is simple as going to a terminal or command line, going to the root of your Sails project (same level as node_modules) and typing in sails generate controller search byActorOrMovie. Hit enter and you get the skeleton below.

/api/controllers/SearchController.js

/**
 * SearchController
 *
 * @module      :: Controller
 * @description :: A set of functions called `actions`.
 *
 *                 Actions contain code telling Sails how to respond to a certain type of request.
 *                 (i.e. do stuff, then send some JSON, show an HTML page, or redirect to another URL)
 *
 *                 You can configure the blueprint URLs which trigger these actions (`config/controllers.js`)
 *                 and/or override them with custom routes (`config/routes.js`)
 *
 *                 NOTE: The code you write here supports both HTTP and Socket.io automatically.
 *
 * @docs        :: http://sailsjs.org/#!documentation/controllers
 */

module.exports = {
    
  
  /**
   * Action blueprints:
   *    `/search/byActorOrMovie`
   */
   byActorOrMovie: function (req, res) {
    
    // Send a JSON response
    return res.json({
      hello: 'world'
    });
  },




  /**
   * Overrides for the settings in `config/controllers.js`
   * (specific to SearchController)
   */
  _config: {}

  
};

For those of you who have worked on Ruby on Rails, this functionality should seem familiar! Codegenerating controllers is nice and all, but we obviously need to wire it up to use the SearchManager and the SearchResultDTO we created, so how do we do that? Well, by the magic of require, of course!

/api/controllers/SearchController.js

var SearchManager = require('../managers/SearchManager');
var SearchResultDTO = require('../dto/SearchResultDTO');

/**
 * SearchController
 *
 * @module      :: Controller
 * @description :: A set of functions called `actions`.
 *
 *                 Actions contain code telling Sails how to respond to a certain type of request.
 *                 (i.e. do stuff, then send some JSON, show an HTML page, or redirect to another URL)
 *
 *                 You can configure the blueprint URLs which trigger these actions (`config/controllers.js`)
 *                 and/or override them with custom routes (`config/routes.js`)
 *
 *                 NOTE: The code you write here supports both HTTP and Socket.io automatically.
 *
 * @docs        :: http://sailsjs.org/#!documentation/controllers
 */

module.exports = {
    
  
  /**
   * Action blueprints:
   *    `/search/byActorOrMovie/{someSearchText}`
   */
   byActorOrMovie: function (req, res) {
      var searchManager = new SearchManager();
      var searchResultDto = new SearchResultDTO();

      // Search by movies, then by persons
      searchManager.searchByMovie(req.params.id).then(
          function(movieDtos) {
              searchResultDto.fromMovieDtos(movieDtos);
              searchManager.searchByPersonText(req.params.id).then(
                  function(personDtos) {
                      searchResultDto.fromPersonDtos(personDtos);
                      searchResultDto.searchText = req.params.id;
                      return res.json(searchResultDto);
                  },
                  function(error){
                      res.serverError(error);
                  }
              )
          },
          function(error) {
              res.serverError(error);
          }
      )
  },

  /**
   * Overrides for the settings in `config/controllers.js`
   * (specific to SearchController)
   */
  _config: {}

  
};

Unit Test Setup

As we wrote the DTO's, SearchManager, and SearchController files above, one thought that must've come to mind is how to unit test all of this code? For this exercise, we choose the following supporting libraries to help us (which are installed using npm install)

Mocha: An easy to use, flexible unit testing framework for Node.js
Assert: An assertion library ported from CommonJS
Lo-Dash: Many useful helper functions for JavaScript, specifically checking for defined/undefined/null values and collection helpers
Proxyquire: An excellent tool to inject stubs in place of anything injected using require
grunt-mocha-test: A grunt task for running Mocha tests

These libraries used together provide us with all the unit testing capability we need. Before we write up some unit tests for the SearchController and SearchManager, we first need to modify the gruntfile.js on the root, as Grunt will be primarily charged with running the Mocha Tests.

/gruntfile.js

    grunt.loadTasks(depsPath + '/grunt-contrib-less/tasks');
    grunt.loadTasks(depsPath + '/grunt-contrib-coffee/tasks');
    grunt.loadNpmTasks('grunt-mocha-test');

And also, we need to modify the grunt.initConfig to actually have a task to use the grunt-mocha-test task runner.

/gruntfile.js

        mochaTest: {
            test: {
                options: {
                    reporter: 'spec'
                },
                src: ['tests/**/*.spec.js']
            }
        },

You'll see it's configured to run any *.spec.js in the /tests folder. We'll save this gruntfile.js and establish said /tests folder, making a subdirectory structure that closely matches the /api folder to correspond with our components.

Typically, in JavaScript, you would create a test for a .js file with an equivalent .spec.js file, so we will do the same here -- /tests/controllers/SearchController.spec.js for the SearchController and /tests/managers/SearchManager.spec.js for the SearchManager.

Unit Testing Approach

I don't see enough posts about unit testing, which is a key facet of Agile development, so let's quickly review what we intend to accomplish here.

A "true" unit test is one that can stand alone. That is, I should be able to run this test without lifting Sails, having a web server up, having MongoDB running, etc. (hence a UNIT test). To accomplish this in classical languages such as Java and C#, we'd develop to interfaces, utilize dependency injection libraries, and create mocks or stubs of any dependent components, such as the ORM, HTTP calls, database calls, etc.

JavaScript, being dynamic in nature, allows us to be even more flexible. We can mock these dependencies right in-line. Better yet, as opposed to Mocking libraries which may produce mocks for methods, properties, and fields we don't even use, by writing these stubs ourselves, we actually narrow down what methods, properties, and fields we need to use from dependent modules.

You're going to see the above approach with our unit tests in this particular project.

Unit Testing the Manager

The SearchManager's primary responsibilities are interacting with the Waterline ORM based on some input from the Controller (in this case, a string for which to search in a Movie name or a Person's first or last name), get the object(s) from the ORM, and send back DTO(s) to the controller.

The curveball here is that Sails.js expects the Models to live in global, so our mocked Models will also live there. We also mocked up some data from the ORM using straight .json files, instead of literally coding them in.

/tests/managers/SearchManager.spec.js

// Injected objects
var _ = require('lodash'),
    assert = require('assert'),
    SearchManager = require('../../api/managers/SearchManager.js'),
    MovieData = require('../mockData/SearchManager/Movie.json'),
    MoviePersonData = require('../mockData/SearchManager/MoviePerson.json'),
    PersonData = require('../mockData/SearchManager/Person.json');

// Mock domain objects
var mockMovie,
    mockMoviePerson,
    mockPerson,
    searchManager;

describe('SearchManager', function(){

    /**
     * Setup mock models
     */
    before(function(){
        // Mocked Movie model
        mockMovie =  {
            find: function(args) {
                {
                    var done = function(cb) {
                        return cb(null, MovieData);
                    };

                    return {
                        done: done
                    };
                }
            }
        };

        // Mocked MoviePersonModel
        mockMoviePerson =  {
            find: function(args) {
                {
                    var done = function(cb) {
                        return cb(null, MoviePersonData);
                    };

                    return {
                        done: done
                    };
                }
            }
        };

        // Mocked Person Data
        mockPerson =  {
            find: function(args) {
                {
                    var done = function(cb) {
                        return cb(null, PersonData);
                    };

                    return {
                        done: done
                    };
                }
            }
        };

        // Set expected globals
        global.Movie = mockMovie;
        global.MoviePerson = mockMoviePerson;
        global.Person = mockPerson;

        // Setup the SearchManager
        searchManager = new SearchManager();

    });

    describe('searchByMovie', function() {
        it('Should get movies with cast and crew', function(done){
            searchManager.searchByMovie('Godfather')
                .then(
                    function(movieDtos) {

                        // Assert that an array of movieDto's was created
                        assert(movieDtos.length > 0);
                        assert(movieDtos[0].name === MovieData[0].name, 'Invalid name');
                        assert(movieDtos[0].movieId === MovieData[0].movieId, 'Invalid movieId');
                        assert(movieDtos[0].releaseDate === MovieData[0].releaseDate, 'Invalid releaseDate');
                        assert(movieDtos[0].rating === MovieData[0].rating, 'Invalid rating');
                        assert(movieDtos[0].grossEarnings === MovieData[0].grossEarnings, 'Invalid grossEarnings');
                        assert(movieDtos[0].cast.length === 2, 'Wrong number of cast members');
                        done();
                    },
                    function(error) {
                        done('General error');
                    }
                )
                .catch(done); // Promise has its own error handler so we need to pass the done function here

        });
    });
});

/tests/mockData/SearchManager/Movie.json

[
    {
        "movieId": 1,
        "name": "The Godfather",
        "releaseDate": "1975-04-21T18:25:43-05:00",
        "rating": "R",
        "grossEarnings": 1200000000.00
    }
]

/tests/mockData/SearchManager/MoviePerson.json

[
    {
        "movieId": 1,
        "personId": 1,
        "role": "Michael Corleone",
        "type": "Cast"
    },
    {
        "movieId": 1,
        "personId": 2,
        "role": "Vito Corleone",
        "type": "Cast"
    }
]

/tests/mockData/SearchManager/Person.json

[
    {
        "personId": 1,
        "firstName": "Al",
        "lastName": "Pacino",
        "dateOfBirth": "1940-04-25T18:25:43-05:00",
        "nationality": "Italian",
        "grossEarnings": 250000000.00
    },
    {
        "personId": 2,
        "firstName": "Marlon",
        "lastName": "Brando",
        "dateOfBirth": "1924-04-03T18:25:43-05:00",
        "nationality": "American",
        "grossEarnings": 280000000.00
    }
]

Unit Testing the Controller

The SearchController's primary responsibilities are taking DTO's from their respective Manager(s) and shuttling them back to any consuming application. Furthermore, if any errors were thrown downstream, the Controller should also throw an appropriate server error. In short, the Controller acts as the "plumbing" in between the domain and any consuming applications.

/tests/controllers/SearchController.spec.js

// Dependent objects
var _ = require('underscore'),
    assert = require('assert'),
    MovieDTO = require('../../api/dto/MovieDTO'),
    PersonDTO = require('../../api/dto/PersonDTO'),
    CastCrewDTO = require('../../api/dto/CastCrewDTO'),
    proxyquire = require('proxyquire'),
    Promise = require('promise');

// Mock objects
var req, res,
    SearchController;

describe('SearchController', function(){

    /**
     * Shared objects
     */
    before(function() {
        // Mock req
        req = {
            params: {
                id: 'wedding'
            }
        };

        // Mock res
        // Create fields to hold the res responses and server errors
        res = {
            rawObject: null,
            jsonObject: null,
            errorObject: null,

            json: function(object) {
                this.rawObject = object;
                this.jsonObject = JSON.stringify(object);
                return object;
            },
            serverError: function(error) {
                this.errorObject = error;
                return error;
            }
        };
    });

    describe('on failure of getting data from SearchManager', function() {
        /**
         * Setup Mock Managers for failed calls
         */
        before(function(){

            // Mock SearchManager
            function SearchManager() {
                var searchByMovie = function(movieText) {
                    var then = function(cb, error) {
                        return error('Oh noes!')
                    };

                    return {
                        then: then
                    }
                };

                var searchByPersonText = function(personText) {
                    var then = function(cb, error) {
                        return error('Oh noes!');
                    };

                    return {
                        then: then
                    }
                };

                return {
                    searchByMovie: searchByMovie,
                    searchByPersonText: searchByPersonText
                };
            };

            // Setup the Controller
            SearchController = proxyquire('../../api/controllers/SearchController', { '../managers/SearchManager': SearchManager });
            res.rawObject = null;
            res.jsonObject = null;
            res.errorObject = null;
        });

        it('byActorOrMovie should report a server error', function() {
            SearchController.byActorOrMovie(req, res);
            assert(_.isNull(res.jsonObject));
            assert(!_.isNull(res.errorObject));
            assert(res.errorObject === 'Oh noes!');

        });

    });

    describe('on successfully getting data from SearchManager', function() {
        /**
         * Setup Mock Managers for successful calls
         */
        before(function(){

            // Mock SearchManager
            function SearchManager() {
                var searchByMovie = function(movieText) {
                    var then = function(cb) {
                        var movieDtos = [];
                        var movieDto = new MovieDTO();
                        movieDto.id = 1;
                        movieDto.name = 'Wedding Crashers';
                        movieDto.rating = 'R';
                        movieDto.grossEarnings = 1337000.00

                        var castCrewDto = new CastCrewDTO();
                        castCrewDto.firstName = 'Owen';
                        castCrewDto.lastName = 'Wilson';
                        castCrewDto.displayName = 'Wilson, Owen';
                        castCrewDto.role = 'John Beckwith';
                        castCrewDto.personId = 1;

                        movieDto.cast.push(castCrewDto);
                        movieDtos.push(movieDto);
                        return cb(movieDtos)
                    };

                    return {
                        then: then
                    }
                };

                var searchByPersonText = function(personText) {
                    var then = function(cb) {
                        var personDtos = [];
                        var personDto = new PersonDTO();
                        personDto.id = 1;
                        personDto.firstName = 'Owen';
                        personDto.lastName = 'Wilson';
                        personDto.displayName = 'Wilson, Owen';
                        personDto.dateOfBirth = new Date('1968-11-18');
                        personDto.nationality = 'American';
                        personDto.grossEarnings = 133700.00
                        personDtos.push(personDto);
                        return cb(personDtos);
                    };

                    return {
                        then: then
                    }
                };

                return {
                    searchByMovie: searchByMovie,
                    searchByPersonText: searchByPersonText
                };
            };

            // Setup the Controller
            SearchController = proxyquire('../../api/controllers/SearchController', { '../managers/SearchManager': SearchManager });
            res.rawObject = null;
            res.jsonObject = null;
            res.errorObject = null;
        });

        it('byActorOrMovie should return results', function(){
            SearchController.byActorOrMovie(req, res);
            assert(!_.isNull(res.rawObject));
            assert(!_.isNull(res.jsonObject));
            assert(_.isNull(res.errorObject));
            assert(res.rawObject.searchText === 'wedding');
            assert(res.rawObject.movieDtos.length === 1);
            assert(res.rawObject.personDtos.length === 1);
            assert(res.rawObject.personDtos[0].firstName === 'Owen', 'PersonDTO.firstName');
            assert(res.rawObject.personDtos[0].lastName === 'Wilson', 'PersonDTO.lastName');
            assert(res.rawObject.personDtos[0].displayName === 'Wilson, Owen', 'PersonDTO.displayName');
            assert(res.rawObject.personDtos[0].nationality === 'American', 'PersonDTO.nationality');
            assert(res.rawObject.personDtos[0].grossEarnings === 133700.00, 'PersonDTO.grossEarnings');
            assert(res.rawObject.movieDtos[0].name === 'Wedding Crashers', 'MovieDTO.name');
            assert(res.rawObject.movieDtos[0].rating === 'R', 'MovieDTO.rating')
            assert(res.rawObject.movieDtos[0].grossEarnings === 1337000.00, 'MovieDTO.grossEarnings');
            assert(res.rawObject.movieDtos[0].cast[0].lastName === 'Wilson', 'MovieDTO.cast.lastName');
            assert(res.rawObject.movieDtos[0].cast[0].firstName === 'Owen', 'MovieDTO.cast.firstName');
            assert(res.rawObject.movieDtos[0].cast[0].role === 'John Beckwith', 'MovieDTO.cast.role');
            assert(res.rawObject.movieDtos[0].cast[0].displayName === 'Wilson, Owen', 'MovieDTO.cast.lastName');
        });
    });
});

The curveball with this unit test is how we inject the dependency to SearchManager. We use a library called proxyquire to inject our mock SearchManager to the Controller. Think of it as a Mockito or Moq equivalent for JavaScript frameworks that use require.

Unit Testing the Models

In this particular example, being simplistic in nature, we didn't code in any behavior in the Models. Sails.js allows you to write in methods as part of the attributes of a Model, so if you had said methods implemented, you'd simply require whatever Model it was you're testing and run against those methods.

Running the Unit Tests

Now that we've got our unit tests in place, fire up a terminal or command line, and type in "grunt mochaTest". You should see the following output

eric@eric-CM6870:~/Projects/AgileMovieDB/agilemoviedb/Server$ grunt mochaTest
Running "mochaTest:test" (mochaTest) task


  SearchController
    on failure of getting data from SearchManager
      ✓ byActorOrMovie should report a server error 
    on successfully getting data from SearchManager
      ✓ byActorOrMovie should return results 

  SearchManager
    searchByMovie
      ✓ Should get movies with cast and crew 


  3 passing (12ms)


Done, without errors.

Seed Some Data!

What good is this Movie database without some sample data? I've included a simple Node.js script in /seeds called seed.js. Simply run this to populate your local MongoDB instance with sample movie data from the .json's in that folder. Note, this seed will not currently work for Sails.js v0.10.x.

Check Out the API!

So, now we can say with pretty good confidence that we've got a working API. Fire up a terminal, go to the /Server folder, and type in "sails lift". Alternatively, in WebStorm, find the app.js on the root of /Server and run it.

Let's test our primary endpoint, /search/byActorOrMovie.

/search/byActorOrMovie/b

{
searchText: "b",
movieDtos: [ ],
personDtos: [
{
id: "53a429a41d7e9b4417a67e11",
firstName: "Marlon",
lastName: "Brando",
displayName: "Brando, Marlon",
dateOfBirth: "1924-04-03T18:25:43-05:00",
nationality: "American",
grossEarnings: 280000000
},
{
id: "53a429a41d7e9b4417a67e16",
firstName: "Steven",
lastName: "Spielberg",
displayName: "Spielberg, Steven",
dateOfBirth: "1946-12-18T18:25:43-05:00",
nationality: "American",
grossEarnings: 400000000
}
]
}

/search/byActorOrMovie/ford

{
searchText: "ford",
movieDtos: [ ],
personDtos: [
{
id: "53a429a41d7e9b4417a67e14",
firstName: "Harrison",
lastName: "Ford",
displayName: "Ford, Harrison",
dateOfBirth: "1942-07-14T18:25:43-05:00",
nationality: "American",
grossEarnings: 250000000
},
{
id: "53a429a41d7e9b4417a67e15",
firstName: "Francis",
lastName: "Ford Coppola",
displayName: "Ford Coppola, Francis",
dateOfBirth: "1939-04-07T18:25:43-05:00",
nationality: "American",
grossEarnings: 300000000
}
]
}

/search/byActorOrMovie/star

{
searchText: "star",
movieDtos: [
{
id: "53a429a41d7e9b4417a67e0c",
movieId: 2,
name: "Star Wars",
releaseDate: "1977-01-21T18:25:43-05:00",
rating: "PG-13",
grossEarnings: 1400000000,
cast: [
{
firstName: "Mark",
lastName: "Hamill",
displayName: "Hamill, Mark",
role: "Luke Skywalker",
personId: 4
},
{
firstName: "Harrison",
lastName: "Ford",
displayName: "Ford, Harrison",
role: "Han Solo",
personId: 5
}
],
crew: [ ]
}
],
personDtos: [ ]
}

Pretty neat!

Wrap-up

For the past few years, many have wondered if Node.js as a server-side web API is "enterprise-ready" from a development perspective. While this project is very simplistic, we went over:

Separation of Concerns
Dependency Injection
Configurability
Testability

What we did not cover in this project that Sails.js supports, which I'm sure enterprises are concerned about, to name a few:

CORS Configuration
Authentication using Passport
Access Control Policies
Session States
Socket.io (i.e. real-time push notifications, or pub/sub) Support

In short, is Sails.js "enterprise-ready"? I'd say a resounding heck yes. For smaller companies equipped with skilled JavaScript developers looking to launch scalable, durable products with quick time to market, Sails.js is certainly a compelling option worth an evaluation.

Stay tuned for Part 3 of this 3-part series where we wire up an AngularJS SPA to this Sails.js Web API!

Seeding a Sails.js Application's MongoDB Store Standalone Using Node.js and Waterline

2014-06-15T12:58:00.000-07:00

As I was constructing the Sails.js application for the forthcoming Part 2 of my 3-part MEAN-stack series, I realized that I needed an easy way to seed the MongoDB instance on which my Sails app would run. In this particular example, I wanted to seed three collections (Movie, Person, and MoviePerson) in MongoDB with data defined in .json files that matched the Models for these collections I'd created in Sails.

So, how do we go about doing this in a standalone fashion using Node.js and Sails' excellent Waterline ORM?

[NOTE: The approach below is confirmed to work in Sails v0.9.16, but is not functional for the forthcoming v0.10. I will update with a post on that as soon as I've got a viable solution]

Reusing My Sails Configuration

One thing I wanted to do was to reuse the Models I had already created in Sails, as well as leverage the /config/adapters I'd already set. Furthermore, I didn't want to have to rely on the full Sails stack to accomplish this: I wanted a simple Node.js script, using Waterline standalone, to perform this task. Finally, I wanted this done in a "promise-y" way -- sequentially executed seeds.

First things first. Let's assume we have three JSON's: Movie.json, MoviePerson.json, and Person.json, all corresponding to collections/Sails models of the same name. Let's say they live on the root of your Sails app, on a /seeds directory. Let's create a seed.js file, intended to be a standalone .js file we can run using Node.js to seed our Mongo instance using Waterline.

We need to bring in Waterline, /config/adapters, the sails-mongo adapter, the Promise library, and lodash for some helper functions. Note that these libraries were pulled down to my Sails project using npm (i.e. sudo npm install lodash waterline sails-mongo promise --save )

var _ = require('lodash'),
    Waterline = require('waterline'),
    adaptersConfig = require('../config/adapters'),
    mongoAdapter = require('sails-mongo'),
    Promise = require('promise');

Now that we've got the prerequisite libraries, let's configure the Mongo adapter to reuse /config/adapters.js from my Sails project. This requires us to define all the properties needed by the sails-mongo library.

// Match our config
var mongoUrl = 'mongodb://' +
    adaptersConfig.adapters.mongo.user + ':' + adaptersConfig.adapters.mongo.password + '@' +
    adaptersConfig.adapters.mongo.host + ':' + adaptersConfig.adapters.mongo.port + '/' +
    adaptersConfig.adapters.mongo.database;

mongoAdapter.database = adaptersConfig.adapters.mongo.database;
mongoAdapter.host = adaptersConfig.adapters.mongo.host;
mongoAdapter.port = adaptersConfig.adapters.mongo.port;
mongoAdapter.user = adaptersConfig.adapters.mongo.user;
mongoAdapter.password = adaptersConfig.adapters.mongo.password;
mongoAdapter.config = { url: mongoUrl };

The mongoAdapter.config bit was an interesting find. Originally, I did not define it, and sails-mongo completely vomited. In tracing down the exception, it appears that sails-mongo (as of v0.9.8) requires that this be defined.

Reusing My Sails Models and Literally Defined JSON's

Now that we have our /config/adapters.js doing the work for us, what of the Models in Sails? Let's first define how we're going to seed the Movie collection. We will encapsulate this in a function, since the ultimate goal is to make this process "promise-y".

/**
 * Seeds movies in a promise-y way
 */
var seedMovie = function() {
    var MovieModel = require('../api/models/Movie'),
        MovieData = require('./Movie.json');

    return new Promise(
        function(fulfill, reject) {
            // Seed Movie Data from our JSON
            MovieModel.adapter = 'mongo';
            var Movie = Waterline.Collection.extend(MovieModel);

            // Invoke Waterline
            new Movie({ adapters: { mongo: mongoAdapter }}, function(err, collection) {
                if (err){
                    console.log('There was a problem initializing the Movie collection.');
                }
                else {
                    // First nuke all existing movies
                    collection.destroy().done(function(err, deletedMovies) {
                        console.log('Successfully deleted Movies!');
                        collection.createEach(MovieData, function(err, models) {
                            if (err || _.isUndefined(models)) {
                                console.log('Could not create Movie collection!');
                                fulfill();
                            }
                            else {
                                console.log('Successfully inserted Movies');
                                collection.find().done(function(err, existingMovies) {
                                    fulfill(existingMovies);
                                });
                            }
                        });
                    });
                }
            });
        }
    );
};

As you can see, by using the magic of Require, we are able to reuse our Movie model and the literal JSON of Movie documents we want in the Mongo collection. The only curveball is that we have to literally define the adapter for the Movie model before using Waterline to extend and use it.

From that point forward, it's out of the box Waterline. As you can see, we first destroy the collection to remove any documents in the collection, then invoke a createEach using our Movie.json, which is a literally defined array of Movie objects. The entire operation is encapsulated in a Promise that is the return type of this function.

Fulfill Our Promises!

Last and certainly not the least, how do we do this sequentially using the magic of Promises? Assuming we'd already created three functions in seed.js: seedMovie, seedPerson, and seedMoviePerson, all returning promises, we'd just chain them together like so:

// Node.js: fulfilling more promises than all politicians combined in history
seedMovie()
    .then(function(movies) {
        seedPerson().then(function(persons) {
            seedMoviePerson().then(function(moviePersons){
                console.log('Seeding process complete!');
                process.exit();
            });
        });
    });

Wrap-up

Once we are at this point, it's elementary -- simply run node seed.js and watch the magic happen. Check out the solution on GitHub if the step-by-step above didn't quite work out for you.

Hopefully, this post is useful for you in your Sails.js endeavors!

Growing Up and Growing Large: Modern JavaScript Web Development Using Sails.js and AngularJS (Part 1 of 3)

2014-06-12T05:30:00.000-07:00

The JavaScript Revolution

I will be the first to admit: had you told me that JavaScript would become the de-facto standard for web development a few years ago, I would've laughed in your face and requested that you take some breathalyzer measurements. How could a language reserved for some ugly client-side DOM manipulation and the general clutter of the view-side of MVC architectures become anything close to enterprise-grade? Heck, JavaScript was even an afterthought, built in 10 days by Brendan Eich to give browsers some whizbang on the client-side.

Well, the numbers don't lie: JavaScript development has grown up and grown large...but this ain't your mid-to-late 2000's JavaScript, folks. We are talking highly scalable, testable, and standards-compliant multi-platform development. We are talking a language that is very well suited for using NoSQL data stores. We are talking a language that empowers developers to react and implement quickly. Modern JavaScript is to past JavaScript as a jetliner is to the Wright Brothers plane. It is time to take it seriously and for practitioners of classical languages to bone up on this quirky but highly effective interpreted language and its frameworks.

Lean and MEAN

PHP had the LAMP stack (Linux/Apache/MySQL/PHP). JavaScript has the MEAN stack (MongoDB/Express/AngularJS/Node.js). Like with the LAMP stack, each letter of the acronym represents important components.

"M": MongoDB

MongoDB is the data store of choice for JavaScript-based frameworks. It is, indeed, a NoSQL database: a schemaless document store that, out of the gate, supports REST and JSON, as well as scale-out capabilities (sharding) that you can expect from a NoSQL platform.

"E": Express

Express is a Node.js-based framework (Node.js is, incidentally enough, also part of the overall stack) that represents server-side JavaScript. Yes, server-side like ASP .Net MVC and Spring MVC. This is the Server API portion of a MEAN stack application, the keeper of domain objects via RESTful endpoints.

Sails.js is an excellent MVC framework built on top of Express, and is the framework we will use for this thought exercise. For .Net folks, think of Express as IIS and Sails.js as ASP .Net MVC. For Java folks, think of Express as JBoss and Sails.js as Spring MVC. You could technically build a "traditional" server-side web application on Express without the use of Sails, if you so desired.

"A": AngularJS

AngularJS is a client-side single page application (SPA) framework heavily sponsored by Google that aims to "extend" HTML to represent the dynamic nature of web applications and provide a slick, seamless user experience.

"N": Node.js

While technically a subset of the "E" part of MEAN, Node.js does so much more than just the server-side operations. Node.js helps manage JavaScript library dependencies for the Server API as well as AngularJS (using NPM...think Maven for Java or NuGet for .Net). It can also help streamline the "build" workflow of a MEAN stack application by running unit tests (in conjunction with a task runner, like Grunt or Gulp). Node.js is essentially the glue that binds all the elements together.

A Typical MEAN Stack Web Application

A "typical" use case of a web application

As the diagram above indicates, MEAN stack applications give your typically nice, segregated separation of concerns.

Sails.js on the server-side exposes RESTful endpoints for external applications to interact with the domain of the application. The domain is persisted on MongoDB and you'll have "POJSO's" (Plain-old JavaScript Objects) for Sails.js to interact with the persistence layer. In case you're wondering, yes, you could easily swap out MongoDB with MySQL or PostgreSQL if you're still unsure about NoSQL platforms...but I'd recommend against torturing yourself in that fashion. ;)

AngularJS on the client-side focuses on a rich user experience, shuttling any of its data needs to the Sails.js Server API. Likewise, if you were to build, say, a native Android or iOS application, they would interact directly with the Sails.js Server API to get at your application's domain object model. True multi-platform development!

While the representation above isn't anything new in software, doing a full MEAN stack offers many benefits:

JavaScript end-to-end. No context-switching for developers when they cross the boundary from client to server-side. (Read: faster development, real collaboration between client and server)
Built to scale out. AngularJS is a SPA loaded on the client. Sails.js runs on Express and Node.js which was designed to scale out. Likewise for MongoDB.
JavaScript has grown up. This ain't your daddy's JavaScript. Want complex collection or object manipulation? npm install underscore (or lodash). Need an ORM, Socket.io support, native RESTful/JSON support? Comes out of the box with Sails.js and AngularJS. Dependency Injection? Built into AngularJS, and use require on Sails.js. Unit testing and mocking: do you prefer jasmine or mocha?

Okay, So Let's Build Something!

In Part 2 of this 3 part series, we're going to get started with a bottom-up approach. Using Sails.js and MongoDB, we will create a domain model and persistence layer for a movie application called the AgileMovieDB.

Orlando Code Camp 2013: SQL 2012 BI

2013-03-17T08:15:00.001-07:00

Many thanks to everyone who attended my Orlando Code Camp 2013 session on SQL Server 2012 BI. There is great potential for the Tabular Modeling of SSAS, and I hope you're excited about using it for your BI needs!

Here are a few links that I promised that will help you get started quickly in using the entire SQL Server 2012/SharePoint Server 2010/PowerPivot and PowerView stack:

For those with Subversion who want to get at the artifacts from the session yesterday, perform a checkout on https://edg.sourcerepo.com/edg/OrlandoCodeCamp2013 to get the Visual Studio solution and the backup of the NFLDW database we used as our source for SSAS Tabular.

Finally, feel free to contact me on Twitter (@grales) or e-mail (eric.v.nograles@gmail.com) if you have any questions/issues or wanted to bounce some ideas around SSAS tabular and its applications in corporate BI.

Thanks again to the Orlando .Net User Group for the opportunity to speak at this fun event! Hopefully, I'll be seeing you all again next year!

The AgileThought family thanks everyone for attending our sessions at Orlando Code Camp 2013!

Packaging Existing SQLite Databases With Your Google Android Application

2013-01-29T07:20:00.002-08:00

Many examples of Android applications on the Internet assume that the apps you develop will, by default, create a new, blank MySQL database at its first run-time. However, there aren't many examples of a situation where one would create a separate MySQL database which would then be used by the Android application. A potential solution, borrowed from databases on other platforms that use Continuous Integration, would be to embed seeding SQL statements in the application to run on creates or upgrades, but this may be prohibitive for some developers in terms of practicality and time, in addition to the fact that Android does some backend wizardry with SQLite databases to have them work properly with its SQLiteOpener class. So, how would one embed an existing SQLite database to the application's assets folder and use it at runtime? Stay tuned for the solution after the jump!

A fellow blogger, Juan-Manuel Fluxa, was able to distill a simple solution to this conundrum. It involves a few manual steps on your existing MySQL database. To perform these steps, either have the SQLite command line handy or download a GUI-based tool such as SQLite Database Browser. Mimicking the original blog post, we are using SQLite Database Browser on our existing database, which on the filesystem exists as SampleDatabase.db.

Subversion Checkout Source for the Example Below: https://edg.sourcerepo.com/edg/Geekstentialism/Geekstentialism.AndroidWithDB/

Step 1: SQLite Manual Steps

Execute this SQL Script: CREATE TABLE "android_metadata"("locale" TEXT DEFAULT 'en-US')
On ALL of the tables in your database, add a column called _id which will be an INTEGER PRIMARY KEY column.
Compact and save your database

Step 1 Recap: What the Heck Did We Just Do?
The first SQL statement creates an embedded table expected by Android. The android_metadata table is used by Android's SQLite helper objects to facilitate connectivity to an app's database.

The second task has you create the expected primary key field by Android's SQLite helper objects. Now, you obviously must be asking the question, "what if I already have a Primary Key field defined"? Unfortunately, due to the behavior of the Android SQLite helper objects, whatever your existing primary key may be will need to take a backseat to the "_id" field. To ensure that your data can still be retrieved quickly using your database's originally intended keys, I would highly recommend creating indices on those columns.

Step 2: Android Magic

Add the database you created above to your application's assets folder.
Within your application's data namespace (or however you've arranged your tiered architecture), create an object representation of your database and have it implement SQLiteOpenHelper. For brevity's sake, click here to see the source of the example project above. This code example was taken from Juan's original blog post above.

Step 2 Recap: What the Heck Did We Just Do?
Adding your MySQL database to the assets folder ensures that it becomes part of your application on deployment.

The SampleDatabase.java class above implements SQLiteOpenHelper to modify the default behavior of this class. As you can see in the code, it first checks for the existence of the database on the Android device's filesystem. If the database does not exist, it calls the getReadableDatabase function of the SQLiteOpenHelper to create a default, blank database for your application.

After the blank database is created, the copyDatabase method streams in the database you dropped in the assets folder of your application, overwriting the blank skeleton.

To use the SampleDatabase.java class above in your UI (or, preferably, your Data Provider class), after instantiating it, call the createDatabase method to have it perform the magic above. Voila, your MySQL database is now deployed as part of your Android application!

The example above does not follow the generally recommended pattern of an Android Data Provider -- we'll get to that in a later blog post. If you're familiar with the pattern, the implementation above should be very simple to integrate with your Data Provider solution.

Gotchas

The path to your application's database on the Android device's filesystem will always be /data/data/com.yourandroidapp/databases, where com.yourandroidapp is obviously the package name you have chosen for your app.
Remember that Google Play has a 50 MB file size limitation for your .apk. Because you are dropping your MySQL database to the assets folder, it becomes part of the .apk.
Indices are your friends, but remember they also take up disk space. If your application has a fairly large database that retrieves its records using your originally intended primary keys (versus the embedded "_id" fields we created above), create indices on those columns, but be cognizant of the disk space growth.

Using Unity for Dependency Injection With WCF Services

2012-09-15T20:15:00.001-07:00

The Dependency Injection (DI) pattern of software development offers many benefits in the area of separation of concerns. The loose-coupling nature of this pattern allows for truly atomic unit tests and (theoretically) more effective development.

While the DI pattern is well documented in web UI technologies that espouse separation of concerns (such as MVC), the use of this pattern in the less glamorous area of application integration using web services is a little leaner on the volume of documentation. Being that application integration apps such as WCF Services have a tendency to perform some elaborate transportation and transformation logic, having the benefits of the DI pattern greatly improves the effectiveness of the development of these applications.

So, in terms of WCF Services, how exactly do we achieve the DI pattern? Thanks to the lightweight Unity library, we can offer the following DI benefits for a WCF Service:

File-less activation of services (no more pesky .svc files to maintain)
Loosely coupled development
Rapid and agile development thanks to unit testing

Coding commences after the jump!

For this example, we will create a WCF Service project (hosted on IIS) which will contain the these two services, with their corresponding operations:

DateOperations.svc

DayOfYear [DateTime Input, Integer Output]: Returns the day of the year for a given date
CalendarQuarter [DateTime Input, String Output]: Returns a fiscal quarter string (ex. "3Q12") based on calendar quarters

NumericOperations.svc

CalculateSalesTax [Decimal and String Input, Decimal Output]: Returns the sales tax for a total value for a given state.

Again, we will want to achieve file-less services and loosely coupled development with this WCF service. So, let's code away!

[As an aside, the completed solution can be found in its entirety here for download! If you have a Subversion client, grab a Checkout from https://edg.sourcerepo.com/edg/Geekstentialism/Geekstentialism.SampleWCF/trunk/ ]

Defining the Application Architecture

Firing up Visual Studio 2010, we create a solution with the following projects:

Geekstentialism.SampleWCF.Web: Our WCF Service Application web project which will be hosted on IIS. Go ahead and remove the default .svc and interface files the template creates, we will have no use for them in this example.
Geekstentialism.SampleWCF.Services: Class library which contains the concrete implementations of our services
Geekstentialism.SampleWCF.Common: Class library which contains the interfaces implemented by our services
Geekstentialism.SampleWCF.Components: Class library which contains operations specific to components. For example, a NumericOperationsComponent which handles the calculations for the NumericOperations service.
Geekstentialism.SampleWCF.Tests: The Unit Test project

There are a couple of references we need to add to our solution. We'll need to add some Library Package References from NuGet to Geekstentialism.SampleWCF.Web, specifically for Unity and Unity Interception. Also, we'll need to add a Library Package Reference in the Unit Test project to Moq, which is a nice, lightweight library which will enable us to mock up the dependent interfaces for each of our layers above.

Finally, following separation of concerns, the projects themselves need to reference each other in the following pattern:

Geekstentialism.SampleWCF.Web: References Geekstentialism.SampleWCF.Common and Geekstentialism.SampleWCF.Services
Geekstentialism.SampleWCF.Services: References Geekstentialism.SampleWCF.Common and Geekstentialism.SampleWCF.Components
Geekstentialism.SampleWCF.Components: References Geekstentialism.SampleWCF.Common
Geekstentialism.SampleWCF.Tests: References all projects

So, now we've got our solution all setup with all the proper references to external libraries (Unity, Unity.Interception, and Moq) and each other. We can begin the fun part...coding!

The Foundation: Interfaces

I personally like starting from the ground up, so let's attack the Geekstentialism.SampleWCF.Common project first. First, we create an Interfaces folder. We're going to need to create the following interfaces in this folder (note, links follow you to the each file in source control):

IDateOperationsComponent: The interface which is the basis of DateOperationsComponent, the heavy lifting arm of the DateOperationsService.
INumericOperationsComponent: The interface which is the basis for NumericOperationsComponent, the heavy lifting arm of the NumericOperationsService.
IDateOperationsService: The contract which the DateOperationsService will follow.
INumericOperationsService: The contract which the NumericOperationsService will follow.

For the sake of brevity, both the Component and Services interfaces above will define the corresponding operations we defined above: DayOfYear and CalendarQuarter for the Date Operations and CalculateSalesTax for the Numeric Operations.

Also, the service interfaces (aka the ones our actual WCF services will implement) will define the ServiceContract and OperationContract text decorations in preparation for WCF Service use.

The Heavy Lifting: Components

Following a familiar n-tiered architecture (and good separation of concerns), the actual implementation of the operations above will not live on the Service code itself. We have a Component layer which will facilitate this logic. Some circles call this the Business Object layer, but it serves the same purpose: do the heavy lifting behind the scenes! These objects will be created in the Geekstentialism.SampleWCF.Components class library project.

We will implement the following components:

DateOperationsComponent: Implements the IDateOperationsComponent above. The actual implementation for DayOfYear and CalendarQuarter will be implemented in this object.
NumericOperationsComponent: Implements the INumericOperationsComponent above. Again, we will place the concrete implementation for CalculateSalesTax here.

So, now that we have concrete implementations of the intended outputs for the service operations themselves, let's actually build the "public facing" part of our project -- the Services themselves!

The Integrators: Services

This is the layer where we get into Dependency Injection, the Geekstentialism.SampleWCF.Services project. If you think about our structure above, we will create a DateOperationsService that is dependent on the DateOperationsComponent. However, since one of the key tenets to DI is to program against interfaces, we are actually going to create a constructor for this class with one parameter, the IDateOperationsComponent interface. We will then create a NumericOperationsService with the same structure, except its constructor's one parameter is the INumericOperationsComponent interface.

Both services will obviously also implement their respective interfaces (IDateOperationsService and INumericOperationsService, respectively). So, how do we hook up the corresponding components' methods to these interfaces' methods? Quite simply, we define a private object that will hold the interfaces mentioned above and call the methods from those interfaces directly. For example, for DateOperationsService, we will create a private object called dateOperationsComponent, which is an IDateOperationsComponent type. In the constructor which takes this interface, we assign this private object's value to the interface coming in on the constructor. We will follow the same pattern for the NumericOperationsService.

For those who are new to Dependency Injection, take heed that we will put everything together when we get to the topmost layer, the web project. However, if you care for some light reading, here is quite possibly the easiest explanation for the DI concept and why we apply this pattern in software development.

The Sanity Checks: Unit Tests

Now, we get to perhaps the second most important thing to get right apart from the Web Project -- the test project. While I can imagine some groans coming from my fellow developers, allow me to offer my experiences with unit tests on a properly decoupled application such as the one we have just built. When properly constructed, your unit tests will save you an unbelievable amount of time, especially when you make changes to your software. Get into the habit of creating proper unit tests and configuring your projects to run them first. It can be an odious task, but it is worthwhile.

Now that I'm off my soap box, let's write up some unit tests for our decoupled components. We'll create the following folders: Services and Components.

In the Services folder, we will create the DateOperationsServiceTest and NumericOperationsServiceTest unit test classes.

In the Components folder, we will create the DateOperationsComponentTest and NumericOperationsComponentTest unit test classes.

Remember the reference to Moq we had created earlier in this Geekstentialism.SampleWCF.Tests project? We're going to put them to use, specifically in the Services tests. This is the beauty of DI and programming to interfaces -- when we create our unit tests for our service operations, we do not actually instantiate concrete DateOperationsComponent and NumericOperationsComponent classes. We instead mock up these components (using Moq) since these unit tests only care for the logic happening on the Service layer. So, we appropriately mock up our Components classes in the Services tests and write a few assertions. Since these services are so simple, we simply assert that the methods on the Component Layer were called once. Loosely coupled software ftw!

Finally, since the Components themselves aren't dependent on anything, writing Unit tests for these are fairly straightforward, testing the functionality requested above.

The Front-end: Web Project

So we have a very nice structure above, separating the concerns of the service and business logic. What you're most likely asking right now is, how the heck do these services get exposed? They are, after all, just simple class libraries, and we didn't create concrete .svc files on the web project (nor do we want to!). This is where Unity works its magic. The key to dependency injection is to let the DI container know what the default constructor for an interface is, and all this magic happens in the web.config in the Geekstentialism.SampleWCF.Web project.

Before we muck around the web.config, let's drop some prerequisites to get the web project. Let's create a new folder called Unity. We're going to create four supporting objects: UnityServiceHost, UnityServiceHostFactory, UnityServiceBehavior, and UnityInstanceProvider. Thanks to an example from our friends at Avington Solutions, we have this code readily available in the full example which you are free to use. In short, these classes will do the magic of using the Unity containers and activating the services we configure in web.config.

With these supporting objects in place, let's do some web.config hacking! The first thing we need to do is to define a config section for Unity, where we'll define the default constructs for our interfaces.

Next, let's modify our system.ServiceModel node. We want to use the aforementioned supporting Unity classes for service activation, so we won't have to maintain those pesky .svc files. This will be defined in the serviceHostingEnvironment node. We're going to create a serviceActivations node and then configure two services : ./DateOperations.svc, which will be activated using Geekstentialism.SampleWCF.Services.DateOperationsService, and ./NumericOperations.svc which will be activated using Geekstentialism.SampleWCF.Services.NumericOperationsService. You'll notice that we are also using our newly created UnityServiceHostFactory as the factory for both services.

Finally, we'll define the unity config section with the default concrete objects for IDateOperationsComponent and INumericOperationsComponent respectively. This is our web.config in its finished state.

Voila! With this architecture in place, we now have a file-less WCF Service Application with two services and proper Dependency Injection. We've also got some nifty unit classes in place that will quickly identify any logical issues for us should we change any layer of our WCF application.

When we invoke our operations using their WSDL's and soapUI as our primary testing tool, we can see they work beautifully.

The Wrap-up

Using Dependency Injection with WCF Services offers us the same benefits as it does for more glamorous architectures such as MVC. Using the DI techniques above, you can craft even more complex WCF Services -- perhaps end-to-end transport services -- and ensure that your software will be agile, well-tested, and elegant!

Business Intelligence Shootout: Microsoft SQL Server 2008 BI vs. Pentaho BI Enterprise Edition (Part 2: SQL Server Analysis Services)

2012-06-17T08:49:00.004-07:00

The BI Platform That They Already Own
So, now that we've seen some of the basic capabilities of Pentaho Analysis Services (aka Mondrian), let's check the other side of the ring where SQL Server Analysis Services (SSAS) sits. The entire SQL Server BI stack is a very interesting case. Starting with SQL Server 2005, Microsoft began packaging their entire BI suite with a Standard Edition license, and I mean the whole shebang. SSIS, SSAS, SSRS, SSMS, and BIDS...the whole gang to satisfy all data analytic needs. Apparently, this was not emphasized enough in the literature for Microsoft SQL Server, because more often than not, this can be news to IT folks in enterprises. Usually, it's good news, as the company may have already made a sizeable investment in SQL Server, and the icing on the cake is a world-class BI platform.

Now, for those companies who haven't already made an investment in SQL Server, the barrier to entry for SQL Server BI may be the price of a license. After all, the bulk of the license fee pays for the RDBMS, one would argue. However, this does not diminish the value of the SQL Server BI stack. This platform has come a long way since Analysis Services was first revealed in SQL Server 2000, and we will take a deep dive into the capabilities of the latest features within SQL Server 2008 R2 Business Intelligence.

SQL Server Analysis Services (SSAS)

Setup

Windows 7 Home Premium (64-bit)
8 GB RAM
Microsoft SQL Server 2008 R2 (SSAS, SSIS, SSRS, SSMS, BIDS)
Source Control Link: Click Here

Design Tools

SQL Server Management Studio (aka SSMS, desktop application)
Business Intelligence Development Studio (aka BIDS, desktop IDE)

Overview

This setup of SQL Server 2008 BI collocates both the relational database system with SSAS, as with our Mondrian deep dive. Now, you may be saying to yourself, the resources available for SQL Server 2008 BI is considerably more than what we offered on a meager VM for PAS. However, this shoot-out isn't designed to empirically prove which platform is faster -- the intent here is to see which product ,in terms of end-to-end development and consumption of BI, has the edge over the other. So, we're talking ease of development, usability, and product cohesion versus "how many milliseconds did it take to bring back those 200,000 tuples".

With that in mind, setup of a collocated SQL Server BI platform is also a cinch. Just simply follow the steps on the SQL Server install (we chose default options, apart from our instance names, which we specified as SQLDEV01), let the install run, grab a cup of tea, then come back to see SQL Server 2008 R2 running on your machine. (Of course, when deploying to a production environment, the install gets a little more involved, with ActiveDirectory identities, RDBMS collation settings, default file locations, etc.)

SQL Server Business Intelligence on one server

As far as a readily deployed UI for ad-hoc analytics (such as Pentaho User Console), SQL Server does not have something out of the box. It would be neat if Microsoft, in a future release, would have a Pentaho User Console equivalent -- perhaps a Sharepoint site with PowerView and PowerPivot.

Like PAS, SSAS runs as a service, persisting and caching calculations in memory to enable fast analysis. Each SSAS database (equivalent to a PAS schema) has a plethora of options (as we will see later) to enable efficient partitioning, scaling, and caching. In terms of connectivity to SSAS databases, you've got the usual suspects: default TCP ports on which SSAS listens (2383 by default) and XMLA or SOAP calls via IIS.

As with the PAS review, I have produced all the artifacts that we cover here on my source control system. If you have a Subversion client, point to https://edg.sourcerepo.com/edg/SQLServerAdventureWorks

Now that we've got the technical rundown covered, let's dive right into some development!

Connection and Schema Creation
In PAS, we mentioned that a power user can develop their own cubes directly on Pentaho User Console or a developer can deploy a proper schema for consumption by many stakeholders. The same could be said about the SQL Server BI stack. However, it's not quite as cohesive as Pentaho. For a power-user who wants to create cubes for quick and dirty analysis, Microsoft offers PowerPivot for Excel. While technically not a part of the SQL Server BI stack (it's obviously an Excel product), it offers the same functionality as the Pentaho User Console in allowing users to define quick one-off schemas for data analysis.

We're more concerned about wrapping BI in a development lifecycle in this comparo, so with that in mind, we go straight to the heart of SQL Server BI development, Business Intelligence Development Studio (BIDS). BIDS is simply the Visual Studio 2008 IDE with templates designed specifically for SQL Server BI. You've got projects for SSAS, SSIS, and SSRS all in a neat, tidy package.

Ready-made, world-class IDE for Business Intelligence development

For those who develop Microsoft products, this is tremendous, as you are working within a familiar IDE. For developers from other platforms, I can assure you that you will start to love Visual Studio when working with SQL Server BI (take it from a guy who also loves Eclipse).

The first thing we must do is to create a new SSAS project. So, upon launching BIDS, we select Analysis Services Project and for a Name, let's call it the obvious (AdventureWorks). This creates a proper project for you to begin SSAS development. On the Solution Explorer, you will see subfolders for various artifacts that SSAS expects.

A blank SSAS 2008 Project

The subfolders are actually arranged vertically by the logical steps you would take to create SSAS cubes. The first thing we must establish is a connection to our RDBMS data mart. This will be accomplished by right clicking on Data Sources, then clicking New Data Source. The Data Source Wizard comes up and guides you through the connection setup process.

On the "Select how you define the connection" step, we would click on the "New..." button which will launch a Connection Manager window which should be familiar to Microsoft application developers. The server name for this case is localhost\SQLDEV01 using Windows Authentication. We then select the AdventureWorksDW2008 database which already resides on the instance. Click Test Connection to ensure connectivity, then click OK.

Hit Next and then we come to the step which asks for impersonation information for this SSAS database. This is very important, as this is the user context on which the SSAS database will attempt connectivity to the relational data mart. For the purposes of this exercise (and for simplicity's sake), since the service is running as Local System, I simply select "Use the service account" then click Next. In a Production setting, you would most likely use some kind of domain service account for the user context.

The last step is to define the name for this Data Source, so let's call it Adventure Works Data Warehouse, then click Finish. Voila, the foundation for our cube and dimension development has been laid down.

Ready to rock!

Cube Creation
So, now, let's define our analytic structure. In SSAS, this is first accomplished by creating Data Source View against a Data Source. This Data Source View essentially is the star schema by which cubes would adhere. You can define your entire star schema on one Data Source View or segment it at your discretion. The Data Source View also acts as an abstraction/alias layer, where you may rename the names of the relational tables and/or table columns to terms that are relevant to your business stakeholders. This is the concept of a "FriendlyName" within SSAS.

For simplicity's sake, let's define the entire star schema of Adventure Works in one Data Source View. We right click on Data Source Views then click on New Data Source View to begin. Another friendly wizard pops up to guide us on our way.

It first asks for the Data Source for the view -- we will select the Adventure Works Data Warehouse source that we had created in the prior step.

In the second step, the wizard asks for the tables/views to be included in the view. To answer a possible question, yes, it is entirely possible to create an SSAS cube against an existing relational data source that has Views built on top of it to simulate a star schema. However, this is generally not recommended, as a core tenet of a DW/BI solution is to minimize the hits to your source relational database. For more reading on the subject, I would recommend the usual suspects: The Data Warehousing Toolkit and the Microsoft Data Warehousing Toolkit.

For the purposes of this exercise, we will select the Dimension and Fact tables as they appear on the relational source and bring them over. Click Next.

Finally, we assign a name to the Data Source View. To be descriptive, let's call this Adventure Works Full Dimensional Model then click Finish.

Star schema relationships defined in the view? Why thank you!

The neat part about a Data Source View in SSAS is that it automatically determines the relationships between your Dimension and Fact tables based on the relationships established on the relational database. So, as an example, on the AdventureWorksDW2008 database, there is a foreign key constraint on the FactInternetSales table on the CurrencyKey column that points its parent table to the DimCurrency table's CurrencyKey column. You see the same relationship established on the DSV. In short, SSAS tries not to have you repeat your work that you did on the data mart.

You can go crazy with the FriendlyNames here on the DSV. In fact, most businesses would generally request friendlier names for the Dimension and Fact Tables (for example, "Reseller Sales" instead of "FactResellerSales", "Employee" instead of "DimEmployee", etc.), but for the purposes of this exercise, we're going to leave it as-is. Now, let's create some cubes against this Data Source View. This is accomplished by right clicking on Cubes, then clicking on New Cube. The Cube Wizard pops up and on the second step for the Select Creation Method, we select the Use existing tables option then click Next.

On the next step, the Select Measure Group Tables, we will select a Fact table...for this example, we'll start with FactResellerSales as we did with PAS. Click Next.

In the next step, the wizard breaks down the fact table by the measure groups detected (basically anything numeric). You can de-select any fields that may actually be degenerate dimension fields -- in this fact table's case, Revision Number would be such a field, so we deselect that field for the measure groups. Click Next.

In the following step, the wizard tries to detect the dimensions that must be created based on the star schema of the sourcs DSV. We will leave this as-is, as it detects all the dimensions and subdimensions properly, thanks to a well-constructed data mart. Click Next.

Finally, we are to give the cube a name. Let's call this one Reseller Sales. Click Finish.

SSAS creates the cube along with skeleton dimensions (just the dimension keys as attributes). We'll get to the dimensional attributes later. For now, let's ensure our cube is setup properly. First, let's make sure that the appropriate measure groups round off to the nearest hundredth decimal place. This is accomplished by expanding the Fact Reseller Sales node on the Measures pane (upper left hand corner in the Cube Structure window), selecting a measure, then setting its FormatString property to #,##0.00;-#,##0.00. We'll do this for all the measure groups in Fact Reseller Sales except for Fact Reseller Sales Count.

Just being nitpicky here, but I'd like a future release to have the ability to select many measure groups and being able to set common properties to the same value. It's certainly not a dealbreaker, but it can get fairly annoying when you have a fact table of many measures.

For now, let's Build and Deploy the cube and dimensions as they stand so that BIDS' metadata gets refreshed and we can work with our model in a more dynamic fashion. First, let's make sure we are deploying to the local SSAS instance by right clicking on the AdventureWorks solution then clicking Properties. In the Deployment subwindow, the Target Server should be localhost\SQLDEV01 and the Database should be AdventureWorks.

Click on Build --> Build AdventureWorks. We shouldn't receive any errors. Finally, click on Build --> Deploy AdventureWorks. After a quick deployment, we're all set for further development.

Click on Browser to refresh the metadata within the IDE for dynamic development.

Creating Calculations
As with PAS, creating calculations in SSAS is a straightforward process. Click on the Calculations subwindow. Then right click in the Script Organizer pane and select "New Calculated Member". The Name of the Calculated Member will be [Net Profit Or Loss].

As we defined in the PAS exercise, this calculated member simply subtracts Total Product Cost from Extended Amount. Since we deployed and refreshed the metadata of our cube, BIDS offers a nice drag and drop feature from the measure groups for this calculated member. We simply expand the Measures node and first drag over Extended Amount to the Expression text box (which results in [Measures].[Extended Amount] to be written). Type in a dash for the minus sign then drag over Total Product Cost.

In the Format String, use the same "#,##0.00;-#,##0.00" formatting (do not forget to include the double quotes in this case) we used on the measure groups. For Non-empty behavior (in other words, what measure group's non-empty behavior it would mimic), select Extended Amount and Total Product Cost. Click File --> Save All, and the calculated member is set!

Dimension Creation
Now that we have the basic setup of the cube complete, we can move onto hashing out the skeleton dimensions that were created for us. To say the creation and editing of dimensions in BIDS is easier compared to the Pentaho Schema Workbench would be a severe understatement.

For starters, the Dimensions are already created for us in SSAS upon creation of the cube. As mentioned previously, these skeleton dimensions simply have their primary keys as the attributes, but BIDS allows the ability to intuitively add attributes from the source to the dimension.

We'll use the Dim Sales Territory dimension as an example. Let's double click on Dim Sales Territory under Dimensions. There are four subwindows on this view -- Dimension Structure, Attribute Relationships, Translations, and Browser. Dimension Structure is the default view and is the place where we will define what attributes are to be included in the Dim Sales Territory dimension.

In this Dimension Structure view are three self-explanatory areas: Attributes, Hierarchies, and Data Source View. To add an attribute to the dimension from the source, one must simply select what attributes to be added from the Data Source View and drag/drop these fields to the Attributes area. For Dim Sales Territory, we will move the rest of the fields from the Data Source View over to the Attributes area. Repeat this process for all the dimensions that have been created.

Creating Hierarchies
Once attributes have been defined, to define hierarchies, it will be the same drag/drop principle, except this time, you will be dragging individual Attributes to the Hierarchies area, in order from parent to child. For this particular hierarchy, as the Sales Territory Group is the parent of Sales Territory Country and Sales Territory Country is the parent of Sales Territory Region. We define this hierarchy as such.

You can rename the Hierarchy once it has been established by right clicking on it and going to Properties. We'll call this hierarchy Sales Territories.

One last step in defining this hierarchy is to define the Attribute Relationship for the hierarchy. This enables SSAS to effectively and efficiently calculate this hierarchy during processing, and thus, it will make aggregations against this hierarchy be considerably faster.

To establish the desired Attribute Relationship, we click on the Attribute Relationships pane. At first, we see that there is only attribute relationships defined between the Sales Territory Key and the dimensional attributes we dragged over earlier.

Attribute Relationships are read to be many to one from left to right. So, for the example above, the Attribute Relationship should be read as "Many Sales Territory Keys are associated to one Sales Territory Group", etc. We want to further hash this Attribute Relationship out to include the hierarchy we created.

Conceptually speaking, we want to design the relationship such that you can read it as: "Many Sales Territory Regions are associated to one Sales Territory Country, and many Sales Territory Countries are associated to one Sales Territory Group".

To implement this, we first start at the lowest level, Sales Territory Region. Right click on this node then click on New Attribute Relationship. On the Related Attribute dropdown, select Sales Territory Country. Leave the Relationship type as-is (Flexible). Click OK. Repeat the same step with the Sales Territory Country node, except the Related Attribute should now be Sales Territory Group.

Save the dimension, build the solution, and deploy it. Now, if we go back to the Reseller Sales cube and go to Browser, you will see the Sales Territories Hierarchy defined in the Dim Sales Territory dimension.

A very nice hierarchy!

Applying Security
SSAS has an incredibly robust security system. The database is secured by a Roles-based system, where one can define what users can access what cubes. We can even get to the granular level and define what dimensions or what tuples specific users can see (though, Microsoft generally recommends making your security definitions broad).

Since this topic covers all manner of sins, I will refer you to the TechNet lesson which covers all the basics. Suffice to say, this security model should fulfill the vast majority of restricted access needs of an organization.

Consumption of Data
So, now we have a nice, fully hashed out Dimensional Model with a Reseller Sales cube properly defined. We do one last build and deploy, and our SSAS database is now ready for some slicing and dicing!

The primary user interface for SSAS really is Microsoft Excel (2010 version in this example). It is the platform of choice for most data analysts in enterprises, and quite frankly, it is one of the best ad-hoc data analytic tools, period. This is the one area where SQL Server 2008 BI kind of falls short comparatively to Pentaho. As previously mentioned, Pentaho's main paradigm is everything through the Pentaho User Console first. SQL Server 2008 BI requires a little work to get a common interface up and running, specifically with tools like Sharepoint Server and PowerPivot for Sharepoint.

With regards to Excel 2010, establishing a connection to our SSAS cube is straightforward. First, go to the Data ribbon and select "From Other Sources". In the submenu, select Analysis Services. The server name for this example is localhost\SQLDEV01 and we will use Windows Authentication.

Upon hitting Next, we see our deployed AdventureWorks database and the Reseller Sales cube available. Select the Reseller Sales cube then hit Finish.

Excel asks what kind of report we want, and we'll use a plain ol' PivotTable Report for this example. We'll drop the table on $A$1 and hit OK. What appears next is very familiar to Excel users, the Pivot Table Field List. It is broken down by the measures table first followed by the associated dimensions.

From here, slicing and dicing is as simple as dragging and dropping measures and dimensional attributes to the appropriate axes or to the values section. First, let's see Net Profit or Loss by Department Name (from the Dim Employee dimension) and broken down by Fiscal Years across the columns. We'll select Net Profit or Loss from the measures and then drag Department Name to the Row Labels and Fiscal Year to the Column Labels.

Easy peasy. The Excel 2010 PivotTable is the most powerful and primary way most "power data analysts" will access the cubes. It allows the ad-hoc functionality to answer most data analytic questions in whatever format the data analyst desires. Because it is native to Excel 2010, all functionality of Excel 2010 is also at the disposal of the data analyst, which some may argue is the best AND worst part about opening up Excel 2010 as the primary front-end for SSAS.

Since this overview only covers SSAS, we will not go into detail about SSRS, but one should understand that SSRS is the primary front-end for parameterized static reports against the SSAS cubes. That is, the report format itself will not change, but users would need the functionality to see different cuts of the data on the report format. One could argue the same functionality could be replicated in Excel -- however, SSRS offers niceties such as application level security and scheduled distribution of reports. In most SQL Server BI implementation I'd been involved with, a combination of Excel and SSRS usually does the trick for a majority of users.

Consumption of Data Summary
While PAS comes tightly coupled with Pentaho User Console, SSAS and SQL Server BI does not have a "one stop shop" for users to immediately access BI artifacts. The primary bare-bones method for accessing cube data is via Excel 2010, and SSRS reports could be built on top of the SSAS cubes to allow users the ability to have parameterized static reports.

This setup isn't necessarily a bad thing, as not all BI implementations are created equal. However, a common front-end like Pentaho User Console would be most helpful to make BI implementations on SQL Server a little more agile with regards to rapid prototyping. On the flip side of the coin, most of the audience for a BI tool would be ecstatic with the capabilities granted on their "home turf" platform (Excel), so such a user interface would be more of a nice-to-have.

In terms of consuming the data itself, it is incredibly intuitive within Excel 2010. Instead obtaining knowledge of, say, ANSI SQL querying, data analysts need only understand how the dimensional model is structured and how a PivotTable Report works, and many analytic questions can be answered. In short, if you're walking into a Microsoft shop that needs BI, there are very few reasons why you SHOULDN'T use SQL Server BI.

SQL Server Analysis Services: The Good, The Bad, and The Weird

The Good

Development process is a cinch comparatively to PAS. BIDS is uses the world-class Visual Studio environment, and the dimensional modeling components for SSAS are highly intuitive.
Tight integration with Excel 2010. Considering Excel is the dominant data analytic tool in the market, this is a boon for developers. Data can be easily brought in and any number of creative solutions off of Excel can be formulated for business use.
SSAS scales very nicely. It is very fast out of the gate, but if your implementation calls for massive data marts and cubes, SSAS offers the ability to scale out across servers.
The security model is vastly superior to PAS

The Bad

Licensing. Want SSAS by itself? You'll need to buy a full SQL Server license. Smaller enterprises may be swayed away due to the cost of entry, and will essentially pay for components they may not use (such as the database engine, SSIS, SSRS, etc)
No common user interface for SQL Server BI out of the gate. I think if Microsoft came up with a Pentaho User Console concept for SQL Server BI, it will add tremendous value.

The Weird

Why can't we multi-select measures on a cube in BIDS to edit common properties?

SQL Server Analysis Services: The Summary

I honestly think, apart from minor quabbles, that Microsoft has hit their BI offering out of the park. While platforms such as TM-1 or EssBase may have the leg up in terms of flexibility under the hood of the analytic engine, SSAS has succeeded in wrapping the complicated concept of BI development and consumption into an easy-to-use package. While arguments can be made for the merits or shortcomings of requiring a full SQL Server license for the BI stack, I actually think that the tight coupling is needed.

A BI solution needs to be coherent not only on the end-product but also for development. In my opinion, tightly coupling the components with the SQL Server database engine ensures the best functionality of SSAS (and the other BI components) out of the gate. Gone are the days when developers need to troubleshoot why the analytic engine doesn't play nicely with the database engine, or why the reporting front-end has some quirks obtaining data from the analytic engine. SQL Server BI's strongest trait is its coherence.

If you have a Microsoft shop with an investment already made in SQL Server, choosing SSAS as the primary analytic engine (as well as the rest of the SQL Server BI stack for other functionality) should be a no-brainer. If your organization has not made an investment in SQL Server, but is otherwise a heavy Microsoft shop, SSAS is still definitely worth a look. While the cost is a factor, comparable offerings from vendors such as Oracle or SAP would probably run you the same amount and would not have the luxury of tight integration with other Microsoft offerings. Make no mistake, SSAS is a world-class analytic platform that I would not hesitate recommending.

Business Intelligence Shootout: Microsoft SQL Server 2008 BI vs. Pentaho BI Enterprise Edition (Part 1: Pentaho Analysis Services)

2012-02-20T18:16:00.000-08:00

A Tale of Two Analytic Engines

In this first head-to-head comparison, we pit Microsoft SQL Server Analysis Services (SSAS) 2008 against Pentaho BI's Analysis Services. As mentioned in the introductory post, in-memory analytic engines aren't exactly new hat. In fact, the inspiration for these engines came from very simple spreadsheet applications, hence the origins of Essbase's name -- "Extended Spread Sheet Database." Despite their age, the benefits of analytic engines remain the same: data retrieval and ad-hoc analysis at lightning speeds. Because most of the data is persisted in RAM, the speed at which you retrieve the data is only limited by your network speed and your user interface's rendering. With that in mind, we have two analytic engines here that drew inspiration from similar roots, but go about their implementations differently.

Microsoft SQL Server Analysis Services has actually been around since SQL Server 7, thanks to Big Redmond's acquisition of Panorama Software. It has only been a recent development, starting with SQL Server 2005, that Microsoft has made a serious push into the BI space, literally offering its entire BI stack for free with a SQL Server 2005 (and then later, 2008) Standard Edition license. Microsoft innovated the now ubiquitous Multi-Dimension Expression (MDX) query, and has made strides in usability, deeply integrating the SQL Server BI platform to all of its core enterprise offerings, Microsoft Office and Microsoft Sharepoint, as well as its well renowned integrated development environment, Visual Studio.

Pentaho Analysis Services, aka Mondrian in the open source world, is a relative newcomer to the BI marketplace. Started by industry veterans from the defunct Arbor Software (where Essbase was incubated and released) at the turn of the 21st century, this Java-based analytic platform began its roots as an open source platform, along with the other components of Pentaho BI. The goal of its founders was to create a powerful, flexible, cohesive, scalable, and cost-effective platform, meeting or exceeding the capabilities of its commercial conglomerate counterparts. Who better to architect and develop such a solution but some of the very pioneers of the BI movement? PAS is the core of the Pentaho BI stack, offering seemingly the same capabilities as other analytic engines in the market.

With the history of both analytic engines in mind, let's take a deep dive at both, using AdventureWorks as the star schema base. For the impatient, I have published all the artifacts produced in this blog post on my source control system, which grants everyone read access. If you have a Subversion client, point to https://edg.sourcerepo.com/edg/PentahoAdventureWorks

The Requirements
We will test both analytic engines' and corresponding design tools' capabilities in addressing the following requirements for the AdventureWorks data warehouse:

The analytic engine shall be able to form the ResellerSales cube and shared dimensions within a common domain
The analytic engine shall be able to define a simple calculated member against the cube created against ResellerSales called "Net Profit or Loss". Calculation: (Extended Amount - Total Product Cost)
The analytic engine shall be able to define a simply hierarchy in DimSalesTerritory that illustrates the following: Region --> Country --> Group
The analytic engine shall implement the following read security rules on FactResellerSales: only Sales Managers and Executives can read FactInternetSales and FactReseller sales data

With the stage set, let's begin our comparison!

Pentaho Analysis Services (PAS)

Setup

Ubuntu 11.10 (aka Oneiric Ocelot) Server
2 GB RAM
20 GB HDD
Apache TomCat Server
MySQL Server (collocated with PAS)
Source Control Link: Click Here

Design Tools

Pentaho User Console (web-based)
Pentaho Schema Workbench (desktop application)

Overview
The standard setup of Pentaho Business Analytics is collocating all components on the same server, which is also the default for SQL Server 2008 BI. We won't get into how to optimally arrange the platforms in a distributed format for the purposes of this head-to-head, but know that Pentaho does have the ability to scale out in a distributed format, if needed.

The setup for Pentaho Business Analytics on Ubuntu is a cinch. Just have a standard Ubuntu Server ready with no components (save for SSH) installed, and then run the binary, follow the instructions, change the defaults if you wish, and voila. PBA can be up and running on a Linux Server in about 10 minutes. The primary front-end for Pentaho Business Analytics is a component called Pentaho User Console, a web-based application.

The Pentaho User Console Login and Main Page, respectively

In terms of PAS, like the SSAS engine, it is hosted in-memory. Pentaho describes the PAS as an "ROLAP engine", which may confuse people accustomed to SSAS. In PAS world, "ROLAP" stands for "relational OLAP", whereas in SSAS, it stands for a type of expensive in-memory storage for its cubes called "real-time OLAP". It's important to note the distinction, as they have completely different uses.

Connection and Schema Creation
A user or developer can go about deploying cubes on their Pentaho one of two ways: define a new Data Source on the Pentaho User Console (more for the power-user) or create a proper schema using the desktop application Pentaho Schema Workbench. As a developer, there are many limitations of the first option -- you may only create one cube at a time and cannot fully define the whole domain of your data warehouse, no advanced security settings, inability to create complex calculated members, etc. So, for the purposes of this shootout, we will cover the proper "developer" way of creating and deploying cubes -- using Pentaho Schema Workbench. I have installed it on my local Windows 7 machine and will be developing on Windows whilst deploying to the Linux server.

When you first launch Pentaho Schema Workbench, the first thing you must define is a connection to your data warehouse, which is accomplished by clicking Options --> Connection...

Note that Pentaho supports most all database platforms -- however, keep in mind that, if you want a certain database connection to work in development time, you must download its respective JDBC driver and drop it into the \schema-workbench\drivers subfolder on your development machine. Likewise, on the server, be sure to drop the .JAR file for that JDBC driver into the /tomcat/lib subfolder to ensure functionality. We are using out of the box connectivity to MySQL, so we don't have any exotic setup for this review.

Once a connection is established, you may begin creating your cube and dimension structure by clicking on File --> New --> Schema.

Nothing too interesting yet, but we have our foundation laid down. A MySQL connection to AdventureWorksDW2008 defined in an as-of-yet unsaved Pentaho schema file. To those familiar with the cohesiveness of the Visual Studio IDE, the first impression of the Pentaho Schema Workbench is that it looks a little...bare bones. The analogy I can provide is that Visual Studio is like making a painting, but having a bunch of tracing templates ready to use, whereas the Schema Workbench seems like literally a blank canvas. We can click on the topmost "Schema" node and define some attributes, so let's call our schema "AdventureWorksDW".

Cube Creation
Now let's go and define our analytic structure, starting with the cubes. We'll begin with the cube that will be created against FactResellerSales.

Right click at the topmost "Schema" node then click on "Add Cube"
In the "name" field, type in ResellerSales
Right click on the cube level (now named ResellerSales) then click "Add Table"
In the new "Table for ResellerSales Cube" child, expand the "name" dropdown and select FactResellerSales.
Right click on the cube level then click "Add Measure"
In this new "Measure for ResellerSales Cube" child member, type in "Unit Price" for the name
Expand the "aggregator" dropdown and select "sum"
Expand the "column" dropdown, then select "Fact Reseller Sales -> Unit Price - DECIMAL(10,0)"
In the formatString property, type in #,###
Expand the dataType dropdown and select Numeric

Repeat steps 4-9 above for all the measures you want to pull down from FactResellerSales. For all the other cubes you want included in this schema, obviously repeat the entire process.

Creating Calculations
Creating calculations on the cube is a relatively straightforward process in Schema Workbench.

Right click on the ResellerSales cube, then click "Add Calculated Member"
In the newly created Calculated Member node, change the name to "Net Profit or Loss"
The dimension should be "Measures"
The formula should be [Measures].[Extended Amount] - [Measures].[Total Product Cost]
The formatString should be #,##0.00

The calculated member should look similar to the example below.

Dimension Creation
Dimensions can be created on two levels in Pentaho. The first level is local to the cube -- that is, dimensions that are only accessible by that cube only. While this can be useful for dimensions that are only in use by one cube, in the AdventureWorks example, most all of the dimensions are shared across fact tables. This brings us to the second level where we can define dimensions -- schema-wide. We will create all dimensions at this level for this exercise, so to attain maximum flexibility.

First, right click at the "Schema" node then click on "Add Dimension". You should get the "New Dimension 0" as a child of Schema now.

Just to show an example of how the dimension creation process works, let's walk through the creation of adding the "DimEmployee" (we'll call it Employee in our schema) table.

In the newly created dimension above, change the name to "DimEmployee" (we'll call it the appropriate "Employee" dimension name when we use this dimension in cubes later).
In the description, we'll specify some information.
In foreign key, expand the dropdown and select "FactResellerSales --> EmployeeKey - INTEGER(10)".
The type should be "StandardDimension"
In the Caption, type in AdventureWorks Employee"
Expand this new dimension. A default "New Hierarchy 0" should've been created.
This is the topmost hierarchy which will be the parent for "All Employees". Blank out the "name" field for New Hierarchy 0 then press enter.
In the allMemberName, type in "All Employees"
For the primaryKey field, select EmployeeKey
Right click on this new hierarchy (should now be labelled "default") then click "Add Table"
In the new "Table for Hierarchy" child, select DimEmployee in the name dropdown.
Left click back to the "default" node under Employee. Expand the primaryKey field then select DimEmployee -> EmployeeKey - INTEGER(10).
Right click back on the "default" property. Now click "Add Level"
In the name field for this new "Level for Hierarchy" child, type in "First Name"
In the "table" dropdown, select DimEmployee.
In the "column" dropdown, select "DimEmployee --> FirstName --> VARCHAR(50)"
In the "type" dropdown, select String.
For levelType, select Regular
for hideMemberIf, select Never

Repeat steps 12-19 for the rest of the dimensional attributes for DimEmployee. (The caveat being, make sure to select the proper "type" for the data you're pulling up. Ex. varchar = string, datetime = date, etc). Also, repeat the process above for each dimension linked up to ResellerSales.

The schema should now look something similar to below.

Now, how do we get the cube to use the dimension? In Pentaho Analysis Services, there is a concept of Dimension Usage in the cube. This allows for shared dimensions. To create a dimension usage on the ResellerSales cube, perform the following steps:

Right click on the "ResellerSales" node then click on "Add Dimension Usage"
For the name of the Dimension Usage, type in "Employee"
For the foreignKey of the dimension usage, select EmployeeKey
For the source of the dimension usage, select DimEmployee (the shared dimension we created previously)

The steps above create some configuration for our cube to use the "DimEmployee" shared dimension, and call it the "Employee" dimension within the context of the ResellerSales cube.

Creating Hierarchies
When we get to the creation of the Sales Territory dimension, one will notice that it has a hierarchical structure. Specifically, our data is organized such that SalesTerritoryGroup is the parent of SalesTerritoryCountry. In turn, SalesTerritoryCountry is the parent of SalesTerritoryRegion. In this example, I've simplified the naming conventions to be Group, Region, and Country.

To define this hierarchical structure, assuming these fields are already created under the same default hierarchy as explained above, creating this tiered relationship is as follows in Schema Workbench:

Click on the "Country" level under the DimSalesTerritory shared dimension.
Click on the parentColumn attribute, then select SalesTerritoryGroup
Click on the "Region" level
Click on the parentColumn attribute, then select SalesTerritoryCountry

Unfortunately, defining the hierarchy in the Sales Territory dimension above gives us some drawbacks in the dimension. First, the way data will come up will only be if you first drop the Group attribute to an axis first, then the Country, then the Region. If you drop the Country attribute into an axis directly, your report will yield no data.

This is a little curious, as in the SSAS world, this does not happen. In fact, defining the hierachy in SSAS such as the above makes the parent-child relationships easier to read and has no effect on dropping any of the child attributes to an axis to slice and dice.

Again, my new-ness to Pentaho may have something to do with this, and I will explore a resolution of this hierarchy model as I understood it from SSAS. Until then, I will have to take points away from Pentaho for an unclear definition of how the hierarchies should be defined and consumed. For my model, I have just removed the hierarchy in the Sales Territory dimension altogether so that the slice and dice capability is at least usable for this dimension.

Applying Security
Security is also implemented with Pentaho Analysis Services. This obviously will be defined at the Schema level, as security spans across all cubes and dimensions. To setup our security such that only Resellers and Executives can actually see the ResellerSales cube, the following steps should be taken:

Right click on the "Schema" node then click "Add Role"
In the name of the new role, type in "Resellers"
Right click on the Resellers role then click "Add Schema Grant"
On the access attribute, select "none"
Right click on the Schema Grant node, then click "Add Cube Grant"
On the access attribute of the Cube Grant, select "all"
On the cube attribute of the Cube Grant, select ResellerSales

One note about the security model -- it is not easy to implement out of the box. Per the Mondrian documentation, you can either embed the Role in the connection string or programmatically set it. All I could think of is how ugly this is. The security implementation of an analytic engine should be a basic implemented feature, but Mondrian appears to have a separate security model from the Pentaho User Console. This definitely may be a dealbreaker for many folks.

Publishing the PAS Schema
So, assuming you've completed the steps above for the rest of the dimensions within AdventureWorksDW2008 and linked it them up to the ResellerSales cube, we essentially have a ready to publish schema.

Publishing from Schema Workbench is relatively straightforward. Simply click File --> Publish. A popup window appears which asks you to specify the URL to your Pentaho User Console, the Publish Password, and a Username/Password combination with sufficient rights to publish.

Publishing options in Pentaho Schema Workbench

Click OK, and Schema Workbench then makes a connection to your Pentaho repository. Finally, select a destination for this new schema, specify the named connection on your Pentaho server which the schema will use to aggregate its data, and optionally, publish this schema as part of PAS' XMLA service. Click on Publish, and then your schema will now be accessible as part of your Pentaho install.

The final step in publishing the Pentaho Schema

Schema Creation/Publishing Summary
Admittedly, coming from the land of SQL Server 2008 BI, the development process feels incredibly cumbersome with Pentaho. The Schema Workbench should try to adopt something as smooth as an SSAS project on BIDS, whereby the developer simply adds all the dimension and fact tables visually on a view, drag and drop PK/FK relationships among the dimension and fact tables, then creates cubes based on this view, and then finally the tool should be smart enough to create the skeleton for all the dimensions with the fact measures linked up. The definition of the dimensional attributes, properties, and hierarchies should come after this quick vetting out process. It's a small thing, but the fact that Schema Workbench can't create a skeleton structure for me given a relational star schema is a bit annoying.

Anyway, creating your analytic schema will take a bit of time in Pentaho, even if it's a relatively simple star schema with nothing crazy related to hierarchies or calculated members. Calculated members are straightforward. However, the hierarchies, while straightforward to construct, don't seem to behave in the way that I would expect them to behave. Furthermore, the security model really is, for lack of a better term, primitive. I don't understand how PAS got through three version releases without implementing security integration with the rest of the Pentaho suite, it's just mind-boggling.

In terms of publishing the schema, the process is relatively straightforward from Schema Workbench. There doesn't appear to be an automated way of deploying cubes (as part of a Continuous Integration Server, for example), but the structure is relatively simple. The schema file itself is an XML file that will get dropped somewhere in the repository and making it available for consumption is a matter of updating the /pentaho-solutions/system/olap/datasources.xml file.

To sum up, the creation and publishing of the schemas in Pentaho, while functional, seems a bit rough around the edges. This might be slightly off-putting for anyone who came from the SQL Server BI platform first.

Consumption of Data
So, now we get to the fun part -- using the data schema we just published. We've got a nice Reseller Sales cube using a bunch of Shared Dimensions defined above and published to our Pentaho server. Let's do a little slicing and dicing!

The primary user interface for the Pentaho BI stack is the User Console. On a default install, it sits on port 8080 of your Apache Tomcat server. So, let's pop into the User Console and see where we can get at this data.

The Pentaho User Console looks very slick

I must admit, this is one of the components of Pentaho that I've absolutely grown to love. In terms of user interface presentation and coherence, I believe Pentaho really has beaten Microsoft here. Whereas a SQL Server 2008 BI solution is disjointed from the start (until you develop some Sharepoint site to sit on top with a little PowerPivot and some SSRS reports), Pentaho feels...unified right out of the gate. Everything through the web-based User Console first.

We'll go into a deep dive of the Pentaho User Console in a later article which we review the overall "out of the box" user presentation of each platform, but from a preliminary perspective, I really prefer the Pentaho web-based UI.

The way to do some ad-hoc slice and dice analysis on a published cube on the User Console is simple. Click on New Analysis, select the cube, and you're off!

Voila! A blank analysis report

The report obviously starts off blank, but if you're familiar with any analytic tool, the next part should be straightforward. Simply drop what measures you want to aggregate onto the report canvas. Afterwards, drop in whatever dimensional attribute you want along the row and column axes. For this example, let's measure Net Profit or Loss. Then, let's see the data by Department Name (off the Employee Dimension) across the rows and then by Fiscal Year across the columns.

Hmmm..repeating department names.

The data is presented, as we expect, in lightning speed. However, one quirk I see with the Analyzer Report is that it does not aggregate by dimensional attribute...it only seems to aggregate by the dimension itself. In other words, in SSAS, the above slice would've come up with one row only, with Net Profit or Loss aggregated by the Department Name. However, it appears that the Pentaho web-based Analyzer Report does not have this built-in functionality. The "Sales" department name above repeats by the number of employees we have in the Employee dimension. How curious.

Of course, this could be the way I designed the dimension, so I've floated up this question to the Pentaho Forums, hoping to gain insight, if it is indeed a system limitation or simply incorrect design on my part for the dimension. I will update this article as soon as I get a response back. If it is a system limitation, that would be a little disheartening, as this web-based analytic tool is otherwise brilliant.

To demonstrate its flexibility, let's slice out some data that won't produce repeat data. Let's measure Net Profit or Loss by Region (off of the Sales Territory dimension) on the column axis and then dragging the Fiscal Year dimensional attribute over to the row axis. Changing your sliced data is as simple as clicking and dragging the Department Name dimension from the row axis and into a garbage can that appears in the lower right hand corner. Then, drag the Fiscal Year dimensional attribute from the column axis to the row axis. Finally, drag over the Region dimensional attribute from the Sales Territory dimension and drop it on the column axis.

The ol' switcheroo

Now, let's get a little fancy. Let's see this data plotted across a line graph. To do this, there is a Pie-Chart shaped icon on the upper right hand corner of the report (to the right of "View As"). To the right of this pie chart is a downward arrow. Click on the downward arrow then select "Line".

Very slick!

As you might have seen from the other options, there are multiple visualizations of data you can make right off of the interface. Very, very slick. Let's save this report off before we continue. If you haven't already, in your Pentaho repository, create an AdventureWorksDW2008 folder, and then create an Analysis subfolder underneath that. Click File --> Save As. Browse to this subdirectory and let's call this report "Net Profit or Loss by Region". Finally, click Save, and then when you browse to this subdirectory in the repository, you'll see our fancy new report.

Let's examine some export options. Web-based is nice and all, but at times, you will need to save off to a file format that can be read by stakeholders locally. The Analyzer Report offers the ability to export to PDF, Excel, or CSV. Simply click on the More option above the report, select Export Report, then select your desired format. See examples below of the export formats we can achieve.

Without going into too much detail, the PDF reports in particular look very slick, complete with a cover page, any graphs (like the one above) we create, and report parameters in the last page.

On Excel, the data is separated by sheets: tabular data on a "Report" sheet, charts on a "Chart" sheet, and logistic information on a "Report_Information" sheet. This is also a very nice layout.

CSV is obviously very bare bones, intended to be a raw source of data for any external apps that need to consume your intelligence data.

On a final note, for those who wish to access the PAS data using Excel natively, PAS actually publishes out an XMLA service that can be consumed within Excel. There is, however, no out-of-the-box connector to Excel that one could use. A software purveyor, Simba Technologies, has created a third party Excel connector (SimbaO2X) to XMLA sources that integrates right with Excel's PivotTable functionality. However, I did some tests with the latest version, and it does not play well with Excel 2010 -- I'm told a fix will be applied that will allow it to play well with Excel 2010. However, as of this writing, this connector is NOT usable with Excel 2010.

Consumption of Data Summary
Apart from the quirk we saw above with repeating dimensional attributes, I am a very big fan of the Pentaho Analyzer Report module on the Pentaho User Console. It's incredibly easy to navigate, functions are very intuitive, and the slice and dice capability is at the very least on-par with SSAS. The niceties, such as graphing, exporting, and saving off to the Pentaho repository for use in Dashboards is icing on the cake. This is a world-class data analytic tool.

Pentaho Analysis Services: The Good, The Bad, and The Weird

The Good

Pentaho Analyzer is a killer application. Web-based (read: cross-platform), fast, responsive, and with a plethora of options, this module should be the envy of its competitors.
The PAS engine seems to be very responsive, simple aggregations and calculations are on-par with leading BI tools
XMLA service means that PAS is truly cross-platform out of the gate.

The Bad

A cumbersome development process, even with the Schema Workbench development environment. Pentaho should strive to create a development experience as smooth as BIDS.
The operational options of a PAS cube seems limited. Whereas in SSAS, you can specify items like cube data partitioning, method of retrieving data (ROLAP, HOLAP, MOLAP), you're pretty much stuck with Pentaho's base functionality unless you muck up the source.
Uneven documentation on the Pentaho website and Knowledge Base. Perhaps the formal courses offered by Pentaho are the way to go, but the literature available for Mondrian seems lacking, specifically for new users.
No drillthrough capability in Pentaho Analyzer
Almost laughably limited security model

The Weird

Repeating dimensional attributes?
Issues displaying hierarchies on Analyzer?

Pentaho Analysis Services: The Summary

The real question we're trying to determine is if Pentaho Analysis Services is truly an enterprise-level analytic engine, especially from the eyes of someone who worked with SSAS for so long. While the engine does meet most all of the requirements set forth for the comparison (we're excluding the hierarchies issue and repeating dimensional attribute issue here, as I am new to Pentaho), the quality of documentation of the product is very uneven. While I understand that Pentaho does not have the backing of the billions that Microsoft possesses, at the very least, a proper starter's guide should have been written. As it stands, what you can find on the Knowledge Base, I feel, is very lacking, and I had to do a lot of trial and error and Googling examples from forums to get my schema "right enough".

Also, the development tools seem very lacking in comparison to Microsoft Business Intelligence Development Studio. As I mentioned in the start of the overview, whereas BIDS is a user-friendly, almost guided way to develop BI, Pentaho Schema Workbench is a rough, blank slate, leaving you to hang yourself in multiple, creative ways.

Finally, the big dealbreaker for me would be the security model. Simply put, Pentaho's solution for PAS security is unsatisfactory. It makes PAS seem like a completely separate module from the rest of the suite (not using the Pentaho user/role model in the User Console). While having a fast, efficient analytic engine is the primary objective, objective 1b should have been to properly integrate with the overall security model, as not EVERYONE should be able to see all analytic data. I don't know what Pentaho was thinking with this.

That being said, I would probably feel uncomfortable recommending PAS as an enterprise-level analytic engine for a large company. It is close to being very good, but I feel even in its current iteration (v4.1), it is still undercooked. This is a little frustrating, as I honestly want to like this platform. Analyzer is one of the best web-based analytic tools I've encountered, and the concept of the Pentaho User Console on top of it is very slick. However, the lack of a proper guided development process, quirks with creation of simple analytic artifacts such as hierarchies, the primitive security model, and the uneven documentation would be enough to scare most risk-averse enterprises away.

However, this does not mean I wouldn't recommend PAS outright. It still does many things very well, and as such, if a company already had a big investment in Java, Linux, and open source platforms or simply cannot afford the costs associated with running and maintaining SSAS, PAS would definitely deserve a fair shake, knowing that getting PAS up-to-par with industry standard analytic engines may require some custom development (specifically with the security).

Stay tuned for the next article -- SQL Server Analysis Services 2008 and a side-by-side comparison with Pentaho Analysis Services

Business Intelligence Shootout: Microsoft SQL Server 2008 BI vs. Pentaho BI Enterprise Edition (Introduction)

2012-02-03T20:49:00.000-08:00

Business Intelligence: A History in Review
Business Intelligence (BI) has become a hot topic in IT and business in recent years. In the past, BI seemed more like a gimmick, with disconnected pieces of software made by various purveyors that seemingly required a significant upfront investment from both IT and business resources, from both a fiscal and training standpoint.

You might have a tremendous analytic engine from someone the likes of Arbor Software with their Essbase platform, but to effectively syphon the data out into a presentable, maintainable format, you'd also have to invest in some sort of enterprise reporting tool like Arcplan. Furthermore, to grant users the ability to perform ad-hoc slice and dice analysis, you'd need an analytic software package pushed by a company like Hyperion. The overall experience of BI, much like its roots, seemed disjointed, with no common vision, and as a result, was expensive to learn and maintain and did not impact the business world the way it had hoped.

The days of disjointed BI have thankfully past, and we are in an era of computing where these tools have become nearly as slick as their web and desktop enterprise application counterparts. Speed of deployment, ease of use, and cost efficiency are the key elements in modern BI. Users want their data faster, more flexible, and robust enough to truly gain that holy grail of turning business intelligence into business INSIGHT.

The Geekstantialism BI Shootout!
In this eight-part series, we will bring two up-and-coming BI platforms front and center, and objectively review the strengths and weaknesses of each, as well as make some recommendations on their optimal effectiveness.

In one corner stands a slick platform backed by the billions of the Microsoft Corporation -- SQL Server 2008 BI.

In the other corner, a young upstart Java-based open source BI platform architected by some of the brightest minds in BI since its inception -- Pentaho Business Analytics.

The Parameters
In order to gain the best insight of the strengths and weaknesses of both platforms when stacked up against each other, I've put together the following parameters/requirements that both must follow in this thought exercise...

Pentaho System Setup

Ubuntu Linux Server 11.10
Apache Tomcat Server
MySQL database backend

SQL Server 2008 Setup

Windows Server 2008 Enterprise Edition
IIS
SQL Server 2008 backend

Data Source
The base data warehouse for both will be the AdventureWorksDW2008 database offered by Microsoft. Obviously, as Pentaho BI runs off of MySQL out of the box, I will be converting said SQL Server database to MySQL, keeping it structurally the same.

Analytic Engine
Using the design tools for both platforms, cubes will be built against all fact tables in the AdventureWorksDW2008 database. All dimensions will be created. Dimensional hierarchies will be created where applicable. Simple calculated members will be created. Data mining options will be briefly evaluated.

Ad-hoc Analysis
The following ad-hoc slice, dice, and drillthrough will be performed against both platforms using the respective front-ends:

Investigation of Internet Sales by Product, Customer, and Sales Territory
Investigation of Reseller Sales by Order Date, Employee, and Promotion

Reporting
Both platforms will be evaluated in producing the following reports using provided design tools and persisting them to their respective report servers for distribution:

Call Center Report by Date Shift
Category of Items Purchased by Customer and Date.

Integration Engine
Both integration engines will be evaluated by using the design tools provided and will perform the following:

Refresh DimProduct with new data (CSV source)
Refresh DimCustomer with new data (CSV source)
Rebuild the respective cubes and dimensions on the respective platform

The Final Verdict
After going through the components of both BI platforms, I will outline the overall strengths and weaknesses of both, and make some educated recommendations on the optimal implementation of either platform. The goal here isn't necessarily to determine which is truly "better" (as that can be a relative term, depending on the circumstances), but instead to gain a better understanding of what scenarios would call for what tool. After all, that's the whole fun in technology!

My Qualifications
Finally, if you haven't done so already, you must be asking yourself what makes me qualified to make any sort of judgement call on BI platforms? Professionally, business intelligence has been my specialty area for the past 6 years. Having been involved as the lead developer for multiple BI platforms on projects of varying sizes in finance and investments, I have practical experience on BI implementations and the demands of its users.

I do have a depth of knowledge with the Microsoft product stack, as that has also been my professional specialty for about 9 years now (specifically Visual Studio [C#, VB, ASP .Net], SQL Server, and the SQL Server BI stack).

I also have a depth of knowledge in the open source and Java, always experimenting with various flavors of Linux (from RedHat to Mandrake to Debian and now Ubuntu). For fun, I like to develop Google Android apps.

This will be my first deep dive into the Pentaho platform. I hope to gain and document insight into this well-regarded platform and see how it stacks up to the SQL Server BI stack that I know well. Furthermore, I've always had a soft spot for open source initiatives, but found many times that they're a little too rough around the edges for risk-averse enterprises. Pentaho looks a bit more promising.

Pentaho claims to be able to lure people away from "big commercial BI" with its platform, and I want to put that to the test. Is it really a BI platform I can confidently recommend as a viable, and even superior, option when scoping out projects? Time will tell, and I'm excited to find out!

Stay tuned for Part 1: Pentaho Analysis Services!

Sebastien Lorien's Fast CSV Reader: Standing the Test of Time

2012-01-19T06:58:00.000-08:00

A couple of years ago, when I was working on some data integration projects and didn't have the luxury of SSIS or Informatica, I had to write some custom .Net components to handle CSV sources flowing into OLEDB destinations. Thinking about what libraries are available in standard .Net (2.0 at the time) -- String parsers, RegEx handlers, StreamReaders, etc. -- one would think it would relatively be a cinch. However, I wanted to have some of the niceties afforded to ETL engines like SSIS and Informatica. Specifically, fully qualified text fields, handling of escaped characters, custom delimeter characters, and missing field actions.

Instead of trying to reinvent the wheel, I scoured CodeProject for some inspiration as a starting point. Never did I realize that an entire CSV parsing library was written so well, that I ended up using the entire project out of the box for my CSV handling needs. This is the case with Sebastien Lorien's Fast CSV Reader.

Sebastien did a tremendous job in parsing CSV's the way a good integration utility (such as SSIS or Informatica) would. You name it, this library's got it: handling missing required fields, handling malformed CSV rows, field headers, the works. I believe the only thing that it didn't do (at the time I used it) was identifying exactly which field was not in an expected data format. That may have changed over the years, however, so I'll have to bring it into a test project and see what she can do now, about 3 years later.

Oh, and one other thing. The Fast CSV Reader is incredibly memory efficient. I can corroborate the numbers reported on the Code Project site for this code, she runs lean and mean. I seem to recall running a relatively large CSV file (several hundred megabytes) using the reader, and it ran quickly and I didn't have any memory issues. For that, if I ever meet Sebastien in real life, I would definitely give him a Geek High Five.

Anyway, it appears that this project is still actively supported by Sebastien, so if you are writing custom .Net utilities to handle CSV's (or any character delimited file), I'd highly recommend either using Fast CSV Reader or using its code as a starting point for your implementation.

Google Chrome: What's With All those Processes?

2012-01-14T18:37:00.000-08:00

Full disclosure: I love Google Chrome. Ever since its release, I have not used another browser, be it my old beloved Firefox, Opera, and Internet Explorer. However, one thing always kind of piqued my curiosity about my fast, relatively lightweight browsing friend, Chrome: why the heck does it fire off x processes (10 on this particular instance) of Chrome.exe at launch?

Hmm...Google Chrome phone home while I'm not looking, perhaps?

Upon some further digging, you can check out what's going on behind the scenes with Chrome by clicking on Tools --> View background pages

And this little window pops up...

Ah-ha...there is the answer to our question. In this example, I had 10 processes of Chrome.exe fire off...to correspond with my extensions AND the tabs I currently have open in Chrome.

So, in case you were ever nervous about those excess processes that Chrome spawns, not to worry, it looks like they're there only to help enhance your web browsing experience.

At least, that's what Skynet wants us to think.

Excel Page Breaks in SQL Server Reporting Services 2008 by Number of Rows and Preserving Column Headers

2012-01-08T16:14:00.000-08:00

In your adventures or misadventures with SQL Server Reporting Services 2008 (SSRS), you may be asked to produce exceptionally large tabular reports to be exported to Excel either interactively or on a subscription. While one would assume this to be a non-issue with SSRS, we then realize that SSRS 2008 unfortunately still exports to an Excel 2003 format, and thus is at the mercy of the dreaded 65536 rows per sheet limitation.

In other words, if you produce a simple tabular report that outputs, say, 100,000 rows of data, and want to export it to Excel, you will get a pretty nasty message from SSRS resembling an unhandled ASP .Net exception, which informs you that Excel sheets (at least in the 2003 version) have a 65536 row limitation. Drat!

While the solution would be to have SSRS just export to Excel 2007 or 2010 formats and dump the results to one sheet like a proper modern application, Microsoft felt the need to still tightly couple the output to the "lowest common denominator" of Excel 2003, which, to this day, is probably still the most widely used version of Excel (and MS Office).

So, that kind of leaves us developers out on an island initially. Of course, the next best option is to have SSRS create Page Breaks every 65536 rows, but how are we supposed to do that? Furthermore, how do we have the column headers for the tabular report repeat on each page? Through some digging in various forums and MSDN, the solution to this little conundrum is documented below.

Database Source: AdventureWorks2008 Database
Database Table: HumanResources.Employees
Objectives:

Produce an Excel output from SSRS 2008 which produces a new sheet every 100 rows
Ensure that the column headers repeat on the new pages
Freeze the column headers when browsing the report directly on the web browser

So, let's get started by creating a new report which will point to the HumanResources.Employees table of the AdventureWorks2008 database, and throw all of the data elements to the Details section of the report.

Step 1: Standard SQL query to obtain data

Step 2: Move columns to the Details

Step 3: Easy peasy lemon squeezy

Page Breaks Every Specified Number of Rows

So, now we have our standard tabular report. Let's fulfill our first objective, having a new sheet produced every 100 rows. This is accomplished by creating a new parent group to the details and specifying a Group Expression for this new parent group.

Step 1: Meet the Parent

Step 2: Use the following syntax to group your details by number of rows --
=Ceiling(RowNumber(Nothing)/[number of rows]). Example above breaks the groups up by 100 rows.

Hit OK twice and you will notice a new group (called Group1) created as the parent of your details. We need to further modify this group to create our Page Breaks, so go ahead and double click on that newly created parent group to bring up the Group Properties window. We will now instruct this report definition to break at the end of the group, as well as remove unnecessary sorting on the group and the new "Group1" column created on our report design.

Step 3: Enable page breaks

Step 4: Click on the "Sort by" and then click the Delete button. Press OK twice to save Group Properties.

Step 5: Delete the new "Group1" column that's been thrown onto the design. Delete the columns ONLY.

So, now we have a report definition that page breaks every 100 rows. Brilliant! We're done, right? Well, not necessarily. Suppose our client wants to see the column headers on each new page, as well as have the column headers freeze themselves when they browse the report on their browser interactively...

Repeating Column Headers in Excel and Freezing Interactive Column Headers

The first thing we need to do is to go into Advanced Mode for our Row Groups. This is accomplished by clicking the small black down arrow to the far right of the "Column Groups" label.

Step 1: Click on Advanced Mode

Now that we've got Advanced Mode up, click on the first item on the Row Groups labelled "(Static)" and bring up the Properties window if it's not already on your toolbox.

Step 2: Set FixedData to True (freezes column headings in interactive mode), then set KeepsTogether and RepeatOnNewPage to True and KeepWithGroup to After (publishes column heading on each page when output to Excel).

Congratulations, you have now created an SSRS tabular report that breaks every 100 rows and reproduces the column header on each new sheet, as well as freezes the column headers when scrolling down interactively!

In Summary...

Do I wish that there was more apparent solution to this seemingly trivial issue? I absolutely do. I'm not sure why this design limitation was overlooked in the development process of SSRS 2008. It would seem to me that the proper solution would be that the SSRS Excel output algorithm would be loosely coupled from SSRS itself and be dependent upon the version of Excel one installs on the server on which SSRS is running?

Anyway, whatever the reason, at least there is a solution out there, albeit a bit of a roundabout one.

Shrinking Transaction Logs in SQL Server 2005/2008 Using T-SQL

2011-12-29T08:16:00.000-08:00

A tripping point of database development in SQL Server 2005 and 2008 tends to be the management of the transaction logs. When you get into a fairly complicated transactional database design, developers don't often worry too much about the transaction logs -- that is, until, the number of unused pages grows exponentially to the point where the transaction log hits its disk space limitation (if it has one), or worse yet, peg most of the physical disk space available, not to mention give your database an enormous performance hindrance.

These tasks ought to be taken care of by a DBA (routine transaction log backups/truncations, etc), but developers may not have the luxury of DBA assistance in their test environments.

You can, of course, use SQL Server Management Studio, to assist with shrinking the transaction logs to free up disk space, but there may be times when you need to shrink the transaction as part of your T-SQL stored procedure processes. Sample code follows:

CREATE PROCEDURE [dbo].[usp_ShrinkTransactionLog]
as

Checkpoint
DBCC OPENTRAN(SampleDatabase)

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

The stored procedure above calls DBCC OPENTRAN to give you information on any open transactions, and is optional. It's more of a nice-to-have to see what's still ongoing in the database.

The real meat of the stored procedure is grabbing the internal FileId of your database's log and then issuing a DBCC SHRINKFILE against it, essentially performing the same task as the Tasks --> Shrink --> Files on SSMS -- the code above does not specify a target size, so it will try to shrink to your transaction log's configured default size.

Note that, if your database is on a Full Recovery model, this sproc may not free up all the disk space that you'd want (with DBCC SHRINKFILE potentially coming back with a message saying it couldn't free up all the space requested). This is because the Full Recovery model, by design, requires that you have regular backups of your logs. Thus, it will keep all transaction logs in the virtual logs until they are backed up. The act of backing up your transaction log will truncate it and free up even more space. So, we can modify the above stored procedure to take this into account:

CREATE procedure [dbo].[usp_BackupAndShrinkTransactionLog]
as

Checkpoint
DBCC opentran(SampleDatabase)
BACKUP LOG SampleDatabase

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

Note that the stored procedure code above assumes that you or your DBA have configured a default Backup Destination for your database. You can modify the BACKUP LOG statement to dump the transaction log to a specific named Backup Device by adding " TO SampleBackupDevice_Log1" (BACKUP LOG(Sample Database) TO SampleBackupDevice_Log1)

Using either of these stored procedures as part of your T-SQL routines will help in keeping your transaction log sizes manageable. Of course, do consult with your DBA's if you end up moving your test database to a production environment, as I'm sure they will wonder why the log files backups happen more frequently than they designed. :-)

Additional info from MSDN: Shrinking the Transaction Log

Using Synergy Over a Non-Split Tunnel VPN

2011-09-22T06:36:00.000-07:00

As with any good geek, I have multiple computers running at home. Going by the "DRY" (Don't Repeat Yourself) principle of software development, I preferred not to use multiple sets of keyboards and mice to control these machines. Enter Synergy.

Synergy is a clever piece of open source software. It uses the basic client-server paradigm to allow you to share one computer's keyboard and mouse across multiple computers over the network. The idea here is that the "server" computer has the keyboard and mouse physically connected to it, and the "client" machines simply connect to the "server" to get access to its keyboard and mouse. Simple, yet elegant.

I wanted to take this concept a step further. For my work machine, we have a non-split tunnel VPN. In lay terms, this means that, when I initiate a VPN connection to my work network, I lose all local connectivity on my laptop. In other words, my work laptop is no longer considered "local" to the other computers on my LAN. This is a bummer, because now I WOULDN'T be able to share my one set of keyboard/mouse between my personal laptop and my work laptop.

Through some port forwarding trickery, I was able to get Synergy to run on my personal laptop as well as on my work laptop whilst I was on VPN. How did I achieve this?

Establish a port forwarding rule from my router to my local machine for HTTP (which is TCP Port 80). In other words, if a machine from outside my network browses to the WAN address of my router from a web browser, it will redirect that traffic to my local machine.
Configure the Synergy "server" on my personal machine to run on Port 80.
(Optional) If you have IIS running, set your Default Website to run on another port (say, 81) or just stop it outright.
On the client machine (my work laptop while on VPN), the host name is the WAN address of my router. Go to Advanced Options and set the port to 80.
Start the Synergy server on my personal laptop. Start the Synergy client on my work laptop while on VPN. Presto.

So, let me explain my approach above. By default, Synergy runs on TCP port 24800, which is all fine and good for my local network (I can do whatever I please with regards to my router firewall, port forwarding, etc). However, that is not kosher for my work's firewall. In fact, my work's firewall blocks all outgoing traffic to "non-common" ports...we're extra stingy at my work, so the only "common" port defined is HTTP (port 80), since, well, that's kind of the backbone of the Internet, and they don't want to block out all Internet traffic.

TCP Port 80 is the only non-blocked TCP port I could use to connect Synergy from my work network (via non-split tunnel VPN) to my personal network, hence the setup above.

Of course, this little setup only works if it's not vital for you to actually publish web content on Port 80 for your local network...it personally isn't for me (that's what my web hosts are for!). If your work network's firewall rules are less stingy than mine, you can of course apply the same approach to any TCP port that isn't blocked.

Now, my only concern is that they don't outright block my local network's IP. I haven't WireSharked Synergy so I don't know how verbose the language is when publishing out the X and Y coordinates of your mouse, as well as action buttons from the mouse or keyboard (I can't imagine it to be TOO verbose), so hopefully it will not generate an exorbitant amount of traffic to warrant blocking.

Breaking Down Database Query Results in Chunks of 65536 Rows for Excel 2003 Using Office Interop and VB .Net (Memory Efficient Edition)

2011-03-18T15:10:00.000-07:00

So, my prior post had a solution for breaking down database query results from Excel into Worksheets of 65536 rows each. As previously mentioned in the post, it is a memory hog, and probably will blow through your assembly's allocation. I came up with a more memory-friendly solution to this issue. In essence, in this approach, I swap out memory processing for I/O processing.

This solution takes the following approach:

Dump out contents of database query to Excel 2003 XML format using a SqlDataReader, broken by x rows (65536 rows in this case, in the spirit of Excel 2003's limitations)
Open said Excel 2003 XML file using Excel Interop
Do any post-processing formatting and niceties to your spreadsheet
Save the file as a normal Excel Workbook
Delete the source XML file (which does indeed get gigantic)

The result is a process which consumes, at most, 40-50 MB in memory (as opposed to the hundreds of MB in the prior approach) with performance close to the prior approach's in-memory + Excel Interop approach.

Code follows.

Excel XML Header (as referenced in the code below)


<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>enograles</Author>
<LastAuthor>enograles</LastAuthor>
<Created>2011-03-18T16:24:37Z</Created>
<Company></Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11895</WindowHeight>
<WindowWidth>19020</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="sDate">
<NumberFormat ss:Format="Short Date"/>
</Style>
</Styles>

Implementation Code


Public Shared Function ExportToExcelXML(ByVal conn As SqlConnection, ByVal sql As String, _
                                     Optional ByVal path As String = Nothing) As String

    Dim excel As Application

    Try
        Dim destination As System.IO.DirectoryInfo
        Dim workbookFullPathSource As String
        Dim dtResult As New System.Data.DataTable
        Dim textWriter As System.IO.TextWriter
        Dim pageCount As Integer = 1
        Dim rowCount As Integer = 0
        Dim worksheetWritten As Boolean

        ' First grab the result set
        Dim cmdResult As New SqlCommand(sql, conn)
        Dim rdr As SqlDataReader = cmdResult.ExecuteReader

        ' Generate a new Guid for the Workbook name.
        Dim workbook_name As String = System.Guid.NewGuid().ToString()

        ' First validate the destination
        If String.IsNullOrEmpty(path) = False Then
            If System.IO.Directory.Exists(path) Then
                destination = New System.IO.DirectoryInfo(path)
            Else
                ' Attempt to create it
                destination = System.IO.Directory.CreateDirectory(path)
            End If
        Else
            ' Just drop it in the executing assembly's root folder if no path is specified
            destination = New System.IO.DirectoryInfo(System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\")))

        End If

        ' Construct the full path
        workbookFullPathSource = destination.FullName & "\" & workbook_name & ".xml"

        ' Instantiate the writer
        textWriter = New System.IO.StreamWriter(workbookFullPathSource, False)

        ' Write the header
        WriteExcelXMLHeader(textWriter)

        ' Iterate through the results
        If rdr.HasRows Then
            While rdr.Read

                ' Iterate the sheet if we've reached the limit
                If rowCount = 65536 Then
                    worksheetWritten = False
                    pageCount += 1
                    rowCount = 0 ' Reset the counter to 0
                End If

                ' Define a sheet and write the headers
                If worksheetWritten = False Then
                    If pageCount <> 1 Then
                        textWriter.WriteLine("   </Table>")
                        textWriter.WriteLine(" </Worksheet>")
                    End If

                    textWriter.WriteLine(" <Worksheet ss:Name=""Page " & pageCount & """>")
                    textWriter.WriteLine("   <Table ss:ExpandedColumnCount=""" & rdr.FieldCount & """>")
                    textWriter.WriteLine("     <Row ss:AutoFitHeight=""0"">")

                    ' The headers
                    For i As Integer = 0 To rdr.FieldCount - 1
                        textWriter.WriteLine("      <Cell><Data ss:Type=""String"">" & rdr.GetName(i) & "</Data></Cell>")
                    Next

                    textWriter.WriteLine("     </Row>")

                    ' Yes, the header counts as a row
                    worksheetWritten = True
                    rowCount += 1
                End If

                ' Write the actual data
                textWriter.WriteLine("     <Row ss:AutoFitHeight=""0"">")
                For i As Integer = 0 To rdr.FieldCount - 1
                    Dim dataType As String
                    Dim dataContents As String = rdr.Item(i).ToString()

                    If TypeOf (rdr.Item(i)) Is String Then
                        dataType = "String"
                    ElseIf TypeOf (rdr.Item(i)) Is DateTime Then
                        dataType = "String"
                        dataContents = CType(rdr.Item(i), DateTime).ToString("MM/dd/yyyy")
                    ElseIf IsNumeric(rdr.Item(i)) Then
                        dataType = "Number"
                    Else
                        dataType = "String"
                    End If

                    ' The data with the proper type
                    textWriter.WriteLine("      <Cell><Data ss:Type=""" & dataType & """>" & dataContents & "</Data></Cell>")
                Next

                ' Terminate the row
                textWriter.WriteLine("     </Row>")

                ' Iterate row counter
                rowCount += 1
            End While
        Else
            textWriter.WriteLine(" <Worksheet ss:Name=""Page " & pageCount & """>")
            textWriter.WriteLine("   <Table ss:ExpandedColumnCount=""" & rdr.FieldCount & """>")
            textWriter.WriteLine("     <Row ss:AutoFitHeight=""0"">")
            textWriter.WriteLine("      <Cell><Data ss:Type=""String"">No Results Found from Query:" & cmdResult.CommandText & "</Data></Cell>")
            textWriter.WriteLine("     </Row>")
        End If

        ' Close out the writer
        textWriter.WriteLine("   </Table>")
        textWriter.WriteLine(" </Worksheet>")
        textWriter.WriteLine("</Workbook>")
        textWriter.Close()

        ' Process in Excel Interop for formatting and saving to a proper Excel document
        excel = New Application()
        excel.DisplayAlerts = False

        Dim wbSource As Workbook = excel.Workbooks.Open(workbookFullPathSource)

        ' For all sheets, autofit columns, bold first rows, freeze panes on all worksheets
        For i As Integer = 1 To wbSource.Worksheets.Count
            Dim ws As Worksheet = CType(wbSource.Worksheets(i), Worksheet)
            ws.Select()
            CType(ws.Cells(1, 1), Range).EntireRow.Font.Bold = True
            CType(ws.Cells(2, 1), Range).Select()
            excel.ActiveWindow.FreezePanes = True
            ws.Columns.AutoFit()
        Next

        ' Select the first Worksheet
        CType(wbSource.Worksheets(1), Worksheet).Select()

        ' Save as a workbook, exit out of Excel
        wbSource.SaveAs(destination.FullName & "\" & workbook_name & ".xls", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal)
        wbSource.Close()
        excel.Quit()

        ' Delete the XML source
        'System.IO.File.Delete(workbookFullPathSource)

        Return workbook_name & ".xls" 'workbook_full_path

    Catch ex As Exception
        Throw ex
    Finally
        If excel Is Nothing = False Then
            excel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
            excel = Nothing
            GC.Collect()
        End If
    End Try
End Function

''' <summary>
''' This routine will write an Excel XML header before defining worksheets
''' </summary>
''' <param name="textWriter"></param>
''' <remarks></remarks>
Private Shared Sub WriteExcelXMLHeader(ByVal textWriter As System.IO.TextWriter)
    textWriter.WriteLine(My.Resources.resMain.ExcelXMLHeader)
End Sub

Breaking Down Database Query Results in Chunks of 65536 Rows for Excel 2003 Using Office Interop and VB .Net

2011-03-10T09:24:00.000-08:00

March 10, 2011. Still hard to believe the date. Even harder to believe is how enterprises hold onto older versions of MS Office -- in our case, MS Office 2003. Yes, Excel 2007/2010 and the Ribbon UI brings a bit of a learning curve, but having worked extensively with 2003 and 2007/2010, I can honestly say that I am faster and way more productive on the new Ribbon UI. I have drank the Kool-Aid, I love the newer versions of Office.

The new version of Office give us some niceties not afforded to the 2003 version. Some neat niceties (the Excel file being a glorified Zip file, with XML standards), and some practical niceties... in the case of the latter, the elimination of that pesky 65536 row limitation. Alas, with my enterprise still on 2003, it is a limitation we developers need to live with. We have various utilities in .Net that export results out to Excel spreadsheets, because let's face it, as an ad-hoc BI tool, Excel is pretty darn good at analyzing volumes of data.

With that in mind, when you have a requirement for large amounts of data (think in the hundreds of thousands of rows) to be published out to an Excel 2003 Workbook, it poses a little bit of a challenge. Specifically, how do you break up your results into Pages of Worksheets (65536 rows in each page) that the end-users can manipulate to their hearts' contentment? A couple of issues you run into: (a) How to break down your results in chunks of 65536 rows and (b) How to insert them blocks at a time without blowing through the Range.Value2's memory limitation (which, as far as I can tell, is not even published)?

I came up with a routine that facilitates this functionality. Just a fair word of warning, it's a bit of a hack, using standard ADO .Net objects and clever pasting of arrays to the Range.Value2 property of the Excel Interop Model while gratuitously calling the Garbage Collector so we don't blow through our memory allocation (because it uses up a bunch to begin with in persisting the results to memory as an ADO .Net DataTable).

Not my neatest of routines, but it gets the job done. An obvious improvement would be to break down the work in multiple subroutines. Another improvement could be the utilization of memory -- I've only tested this routine for the upper bounds of our data requirements (about 900,000 points of data?), so for larger data requirements, you may need to tune it a bit so it will not blow through the assembly's memory allocation. Of course, if you come up with a clever way to clean the code up a bit, I'd be happy to hear about it!

Again, I reiterate my HACK ALERT statement:

  
   Public Shared Function ExportToExcel(ByVal conn As SqlConnection, ByVal sql As String, _
                                         Optional ByVal path As String = Nothing) As String

        Dim excel As Application


        Try
            Dim destination As System.IO.DirectoryInfo
            Dim workbook_full_path As String
            Dim dtResult As New System.Data.DataTable
            Dim lstSheets As New Generic.List(Of String)

            ' First grab the result set
            Dim cmdResult As New SqlCommand(sql, conn)
            Dim rdr As SqlDataReader = cmdResult.ExecuteReader
            dtResult.Load(rdr)

            ' How many sheets will we need?
            Dim sheet_count As Integer = SheetCount(dtResult.Rows.Count)

            ' Generate a new Guid for the Workbook name.
            Dim workbook_name As String = System.Guid.NewGuid().ToString()

            ' First validate the destination
            If String.IsNullOrEmpty(path) = False Then
                If System.IO.Directory.Exists(path) Then
                    destination = New System.IO.DirectoryInfo(path)
                Else
                    ' Attempt to create it
                    destination = System.IO.Directory.CreateDirectory(path)
                End If
            Else
                ' Just drop it in the executing assembly's root folder if no path is specified
                destination = New System.IO.DirectoryInfo(System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\")))

            End If

            ' Construct the full path
            workbook_full_path = destination.FullName & "\" & workbook_name & ".xls"

            ' Create Excel objects
            excel = New Application
            excel.DisplayAlerts = False
            Dim wb As Workbook = excel.Workbooks.Add()
            Dim results_processed As Integer = 0

            ' Tracking dictionary so we can order the sheets
            Dim dicSheets As New Generic.Dictionary(Of Integer, Worksheet)

            ' Only do the Excel processing if we have results
            ' Create the right number of sheets
            ' And drop data into each sheet
            If sheet_count > 0 Then
                For i As Integer = 1 To sheet_count

                    ' Create a sheet
                    Dim ws As Worksheet

                    ' Put in order
                    If i > 1 Then
                        ws = wb.Worksheets.Add(Type.Missing, dicSheets(i - 1))
                    Else
                        ws = wb.Worksheets.Add
                    End If

                    dicSheets.Add(i, ws)

                    Dim results_outstanding As Integer = dtResult.Rows.Count - results_processed

                    ws.Name = "Page " & i.ToString()
                    lstSheets.Add(ws.Name)

                    ' Start and end positions of the DataTable.  Conditions for if we have
                    ' more than 65536 results, we need to know the breakdown for where we
                    ' should start and end (ordinally) on the table based on what Sheet we're on
                    Dim dt_start_row As Integer
                    Dim dt_end_row As Integer
                    Dim sheet_end_row As Integer

                    ' Data Table Bounds
                    ' Positionally on the DataTable, where are we extracting data?
                    If i > 1 Then
                        dt_start_row = (i - 1) * 65536 ' Minus one because the first sheet is rows 1-65536
                    Else
                        dt_start_row = 1
                    End If

                    If results_outstanding < 65536 Then
                        dt_end_row = dtResult.Rows.Count
                        sheet_end_row = results_outstanding + 1 ' We need it to be inclusive
                    Else
                        dt_end_row = i * 65535
                        sheet_end_row = 65535
                    End If

                    ' Create a two dimensional array
                    ' First dimension = rows
                    ' Second dimension = columns
                    ' Add + 1 to rows because the first row is always the column headers
                    Dim result(sheet_end_row + 1, dtResult.Columns.Count) As Object

                    ' Publish the row header
                    For col As Integer = 0 To dtResult.Columns.Count - 1
                        result(0, col) = dtResult.Columns(col).ColumnName
                    Next

                    Dim sheet_row_count As Integer = 1

                    ' Propagate the data down in the array
                    ' subtract one since the DataTable is zero based
                    For result_data_row As Integer = dt_start_row - 1 To dt_end_row - 1

                        For result_data_col As Integer = 0 To dtResult.Columns.Count - 1

                            Dim value As Object = dtResult.Rows(result_data_row)(result_data_col)

                            ' DateTimes come up weird on Excel
                            If TypeOf (value) Is DateTime Then
                                result(sheet_row_count, result_data_col) = value.ToString() 'dtResult.Rows(result_data_row)(result_data_col).ToString()
                            Else
                                result(sheet_row_count, result_data_col) = value 'dtResult.Rows(result_data_row)(result_data_col)
                            End If
                        Next

                        ' Iterate the sheet's row counter (not the data table's row counter)
                        sheet_row_count += 1
                    Next

                    ' Arbitrary number of rows to drop at a time so Excel's rng.Value2 doesn't exception out.  Needs further refining, obviously
                    If sheet_row_count >= 65535 AndAlso dtResult.Columns.Count > 30 Then

                        ' Drop in Chunks of 700 rows at a time
                        ' We can change this later as we performance tune
                        Dim row_chunks As Integer = 700
                        Dim excel_sheet_max As Integer = 65536

                        For array_row As Integer = 0 To excel_sheet_max - 1 Step (row_chunks - 1)

                            Dim number_of_rows As Integer

                            ' If we are at the very end, specify as such
                            ' as it might not be a full 700 rows
                            If array_row + (row_chunks - 1) > excel_sheet_max Then
                                number_of_rows = excel_sheet_max - array_row
                            Else
                                number_of_rows = row_chunks
                            End If


                            Dim result_segment(number_of_rows, dtResult.Columns.Count) As Object
                            Dim rngBegin As Range = ws.Cells(array_row + 1, 1)
                            Dim rngEnd As Range = ws.Cells(array_row + number_of_rows, dtResult.Columns.Count)
                            Dim rngAll As Range = ws.Range(rngBegin.Address & ":" & rngEnd.Address)

                            ' Copy the 700 rows of elements to the segment
                            Try
                                Array.Copy(result, array_row * (dtResult.Columns.Count + 1), result_segment, 0, number_of_rows * (dtResult.Columns.Count + 1))
                                rngAll.Value2 = result_segment
                            Catch e As Exception

                            End Try

                            Array.Clear(result_segment, result_segment.GetLowerBound(0), result_segment.Length)
                            Array.Clear(result_segment, result_segment.GetLowerBound(1), result_segment.Length)
                            result_segment = Nothing
                        Next

                    Else
                        Dim rngBegin As Range = ws.Cells(1, 1)
                        Dim rngEnd As Range = ws.Cells(sheet_end_row + 1, dtResult.Columns.Count)
                        Dim rngAll As Range = ws.Range(rngBegin.Address & ":" & rngEnd.Address)

                        rngAll.Value2 = result
                    End If



                    ' Formatting -- bold and freeze header columns, autofit columns
                    CType(ws.Cells(1, 1), Range).EntireRow.Font.Bold = True
                    CType(ws.Cells(2, 1), Range).Select()
                    excel.ActiveWindow.FreezePanes = True
                    ws.Columns.AutoFit()

                    ' Blank out the array
                    Array.Clear(result, result.GetLowerBound(0), result.Length)
                    Array.Clear(result, result.GetLowerBound(1), result.Length)
                    result = Nothing
                    GC.Collect()

                    ' Keep track of what we've processed
                    results_processed += sheet_end_row
                Next


                ' Cleanse sheets - remove Worksheets that aren't named "Page x"
                Dim lstRemoveSheets As New Generic.List(Of String)
                For Each objWS As Object In wb.Worksheets
                    Dim ws As Worksheet = CType(objWS, Worksheet)
                    If lstSheets.Contains(ws.Name) = False Then
                        lstRemoveSheets.Add(ws.Name)
                    End If
                Next

                For Each sheet_to_delete As String In lstRemoveSheets
                    CType(wb.Worksheets(sheet_to_delete), Worksheet).Delete()
                Next
            End If

            ' Select the first cell of the sheet

            Dim wsFirst As Worksheet '= dicSheets(1)

            ' Indicate we received no results
            If sheet_count = 0 Then
                wsFirst = wb.Worksheets(1)
                CType(wsFirst.Cells(1, 1), Range).Value2 = "No results found."
            Else
                wsFirst = dicSheets(1)
            End If

            wsFirst.Select()
            CType(wsFirst.Cells(1, 1), Range).Select()

            ' Finally return the workbook name
            wb.SaveAs(workbook_full_path)
            wb.Close()

            ' Dispose the dtResult
            dtResult.Dispose()
            dtResult = Nothing

            Return workbook_name & ".xls" 'workbook_full_path

        Catch ex As Exception
            Throw New Exception("Error encountered when exporting to Excel: " & ex.Message & " " & ex.StackTrace)
            'Return Nothing ' A nothing returned implies unsuccessful creation
        Finally
            If excel Is Nothing = False Then
                excel.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
                excel = Nothing
                GC.Collect()
            End If
        End Try
    End Function

Greekstentialism

2011-02-26T08:54:00.000-08:00

Kalisera from Athens, Greece! Another day, another data integration project with one of our field offices...and thankfully, this one happens to be in one of the cities I've been meaning to visit since I was a child. Growing up a product of the US public school system, you'd often hear stories about ancient Greek civilizations and their culture. This country is indeed the birthplace of democracy and great minds such as Socrates, and of course, being a general math geek that I am, Pythagoras. Heck, ancient Greek reading was even required in high school, as we read Homer's Iliad and Odyssey in our English classes (the latter being one of my favorite books of all time). All things considered, despite having fallen on hard times recently, I believe Greece to still be one of the most fascinating countries in the world. The picture to the left is me seated out front of the Parthenon in the Acropolis, a truly amazing structure that I'd rank up with the Egyptian pyramids as a "must see before time wears it down to nothing."

Anyway, geeky tourist fascination aside, I've learned much about the modern Greek culture since I've been here. Some tips for my fellow Americans who may have never visited Greece before and are planning on coming by in the near future:

The Greeks LOVE cheese. Not just feta either. Cheese is practically on every dish. For those who are lactose-intolerant, bring all appropriate OTC treatments.
Eat lunch at 12-1 pm? Most Greeks will look at you strangely. Lunch here typically begins around 2:30pm/3:00pm.
Eat dinner at 6-7 pm? Again, most Greeks will look at you strangely. Actually, you won't see them looking at you strangely because none of them will be out. Dinner starts around 9:00pm/9:30pm.
Common Greek phrases can be found here complete with phonetic pronunciation.
Want to eat some amazing seafood while driving by some of the biggest villas in Greece? Take a trip a little north of Athens to a place called Kiffisias.
Have ouzo on the rocks when having seafood, then finish off with mastica as a dessert apertif.

Word is I might be headed back around these parts sooner rather than later, so I will be sure to post any more tips as I discover them.

Environmental Geekstentialism

2011-02-13T05:06:00.000-08:00

With petrol prices still climbing and a need for a highly efficient car for my fiance, I decided it was time to finally bid adieu to my Honda S2000. I had quite a terrific time with that car, but its impracticality wore my patience a little thin over the years. I would still recommend it for anyone who wants a reasonably priced convertible sports car without sacrificing the fun -- I dare say you won't find a better 6-speed shifter this side of a Porsche 911. I'll still have my 2003 Subaru WRX project car to keep my speed needs at bay.

That being said, as a result of our vehicular needs, we have joined the ranks of the hybrid owners of the world and got ourselves a 2010 Honda Insight. I must admit, I was a little leery with hybrid technology at first; the first generation Insight was the S2000's environmental cousin (i.e. completely impractical with anything else aside from fuel efficiency) and the prior and current gen Priuses never really did it for me with regards to a driving experience...not to mention its steadily climbing price.

With that in mind, I was able to hunt down a certified 2010 Honda Insight that struck our fancy with its price point. In test driving the car, I found myself to be a bit surprised -- the handling of the car is, dare I say it, actually fun. Its handling characteristics reminded me of my cousin's Honda Fit, with which I'm sure the Insight shared some of its components. The steering is very responsive, definitely much more responsive than the Priuses I've test driven, and it feels pretty nimble on its feet in normal and highway traffic.

Don't get me wrong, speed and acceleration-wise, the Insight is the polar opposite of the S2000, but my fiance was more concerned with reliability and fuel efficiency. In both aspects, I'd say Honda excels. Reliability? It's a Honda. I know it's become the company's unofficial motto, but their cars simply are highly reliable. I still remember my parents' old 1989 Civic. My family piled on about 200,000 miles and 15 years on that car. Engine never blew, only had to replace the usual wear and tear items, and overall was a great little car. Ditto my S2000 -- not a problem with it mechanically in my 3 years of ownership (and, um, enthusiastic driving).

Fuel efficiency...now this is where the Insight really impresses the geek in me. Most of the new hybrids have a similar system, but I believe the Insight's execution of it is a little better on the user-side. The car has a built in ECO Mode which maximizes the car's fuel efficiency to go along with the brilliant ECO Guide user interface on the dash. The ECO Guide is what I'm impressed with the most. The speedometer (mounted at the top of the dash, a la the new Civics) has an ambient light that glows green when you're driving in a fuel efficient manner and dark blue when you're driving like a maniac. To help coach you into better fuel efficiency, there is also a UI on the dash called the Eco Drive Bar which shows you efficiency ranges as you accelerate and decelerate. I think this system, at least for me, works a bit better than Ford's pretty "leaves" UI. The ambient lighting on the dash gives enough of a high level overview of your fuel efficiency while the Eco Drive Bar can show a driver specifically how his inputs affect his fuel efficiency. So, even a lead footed speed addict such as myself can be trained into maximizing his fuel efficiency. Not bad at all.

So, what's the verdict on the fuel efficiency? The sticker says 40 city/43 highway. I drove from the hills of Manayunk to Abington, taking all local hilly roads, and I easily beat the city average and got closer to 45 mpg. I'd imagine that reaching 50+ mpg on the highway is easily attained. The Prius still beats those numbers by about 10-20% with its slightly more advanced hybrid system, but again, I felt the driving experience on the Insight was slightly more fun than that of its Toyota competitor...not to mention I'm kind of biased to Honda products.

While the car enthusiast in me may mourn the loss of one of the best production sports cars ever built, the Eco Geek in me will rejoice in (maybe) once a month fill-ups, lower insurance premiums, and a more positive environment impact. That will certainly help.

...and I think my WRX is about due for a new turbo and intercooler anyway ;-)

Support Rosetta Code

2010-12-29T11:41:00.001-08:00

For all you hobbyist or professional developers out there who have not heard of Rosetta Code, it is a Wikipedia of sorts for programming tasks solved with a plethora of different languages (452 and counting, I believe) -- and they need your support to keep it alive!

Visit their Finances Page to see how you may be able to help (yes, they take PayPal donations). It'd be an absolute shame to see this growing and useful resource go under.

The Best Teaching Programming Language?

2010-12-28T13:43:00.000-08:00

I ran across an article today that attempts to explain the merits of using BASIC as a teaching programming language. While the author makes some valid points, specifically at the simplicity of BASIC, I believe that BASIC is a bit of an archaic language that should probably not be considered as a teaching language. I believe learning this language would espouse some bad habits (GOTO, GOSUB nightmares inherent in BASIC) and would promote design concepts that wouldn't have any application in the real world.

In terms of context, I was trying to think of what would stir someone to pursue programming as a hobby and potential career path. Sure, the very fundamental idea of programming is to send instructions for your computer to execute, but why would someone (assuming in younger generations) pursue this seemingly droll craft?

Thinking back on my younger days as a script kid/amateur hacker, what really appealed to me initially was actually videogames. I was astounded at the "black art" of sending instructions understood by a computer that somehow turned into the gaming experiences I loved as a child. Of course, over time, my interest eventually evolved to my current interest in data driven applications, but what really hooked me first was definitely the flashiness that could be achieved in programming in videogames.

That being said, I tried to think of what language out there would be the best teaching language for someone of my demographic when I was younger -- a language that would be readily accessible, relatively easy to pick up, and a person could steadily expand his/her skills through a learning process. Also, the ability to do some flashy things wouldn't hurt; let's face it, at the end of the day, a programmer would like to see something with a little pizazz versus some boring command line outputs.

Taking these factors into account, if I were younger today, I'd probably want to pick up Java as my first language. The learning curve of object-oriented design may be a turn off for beginners, but you can certainly use Java without having the full knowledge of OOD (though the architecture inherently will drive you to understand it). It is:

A free, readily available platform
All the basic OOD goodies like polymorphism, inheritance, and recursion for advanced learners
Type safety, i.e., an integer is an integer, a String is a String, etc (some schools of thought consider type safety to be a hindrance -- I am still a believer in it)
A huge community from which to absorb knowledge
Multiple platforms for development -- Java 2 SE, J2EE, Android, etc

Some may say that Java may be too formidable a platform on which to start, but I feel that the wealth of resources available for one to learn Java, coupled with the strong community and great tools that aid in learning would make Java the best candidate for a teaching programming language.

So, what about the aforementioned flashiness that will hook certain people? Java's got tools for that too. Specifically, I still remember an intermediate Java class in college that I thought would've helped drive the concepts of OOD home for a lot of students in a high school programming course. We used a great Java application called RoboCode as our course-long project. The final exam? Having all of our classmates' robots go at it in a battle royale on the last day.

Certainly beats solving abstract problems and was more educational if you ask me.

Philly Sports: The Four Horsemen Assembled

2010-12-15T11:21:00.001-08:00

Great day to be a Phillies fan today. In a little over 45 minutes, Cliff Lee comes home to Philly! Not sure to whom I should give credit for the Photoshop below, but whoever you are, well done.

Multiple VSS Projects Using Cruise Control .Net

2010-12-15T10:08:00.000-08:00

I'm a big fan of Cruise Control .Net. As an open source continuous integration server, it performs wonderfully and is quite scalable.

Unfortunately, great CI servers can often times be matched with horrendous source control systems. Specifically, the bane of all SCM's, Visual SourceSafe 6.0. My group at work unfortunately is still encumbered by VSS 6.0 for a little while longer until we finally migrate to a proper SCM in 2011. Subversion or Git obviously would get my first vote....but, I digress...

I ran into a little hiccup with VSS on Cruise Control .Net -- some of my assemblies are located in different project locations. I needed to update multiple projects on different locations in the SCM prior to executing the build.

So, how does one properly update multiple projects in VSS for a Cruise Control .Net build? Digging into the CC .Net documentation further, I found that there is an option for a "multi" sourcecontrol block in your ccnet.config file. Using the multi type sourcecontrol block, one can call for multiple updates to different projects on VSS.

Example:


<sourcecontrol type="multi">
 <sourceControls>
  <vss>
     <executable>C:\Program Files\Microsoft Visual Studio\Common\VSS\win32\ss.exe</executable>
     <project>$/SampleApplication</project>
     <username>VSSUser</username>
     <password>VSSPassword</password>
     <ssdir>\\SERVERNAME\VSS</ssdir>
     <workingDirectory>c:\VSS\SampleApplication</workingDirectory>
     <culture>en-US</culture>
     <cleanCopy>false</cleanCopy>
  </vss>
  <vss>
     <executable>C:\Program Files\Microsoft Visual Studio\Common\VSS\win32\ss.exe</executable>
     <project>$/SampleApplication2</project>
     <username>VSSUser</username>
     <password>VSSPassword</password>
     <ssdir>\\SERVERNAME\VSS</ssdir>
     <workingDirectory>c:\VSS\SampleApplication2</workingDirectory>
     <culture>en-US</culture>
     <cleanCopy>false</cleanCopy>
  </vss>
 </sourceControls>
</sourcecontrol>

In essence, you are instructing CC .Net to make multiple calls to the SCM (VSS in this case) to grab all pertinent source code prior to your build. This example would obviously also work for other SCM's compatible with CC .Net, such as Subversion.

Laptop Keyboard Ergonomics

2010-12-12T11:56:00.000-08:00

The design and feel of laptop keyboards always seemed very similar across the board in my experience. However, there were/are exceptions to this rule. To me, the ultimate laptop keyboard was that of my old IBM Thinkpad T22. The keys were perfectly spaced and the ancillary functions were so easy to figure out, I could turn down my laptop speaker's volume, turn on the keyboard LED, and put the machine on hibernate with my eyes closed. After that laptop, all other keyboards seemed very inadequate to me.

I'm pleased to say that my new laptop, the Lenovo IdeaPad Z565, has a keyboard that will make my yearning for my old T22 keyboard go away. As you can see from the image on the right, Lenovo opted to go with the "chiclet layout" that's become vogue with laptop these days thanks to the MacBooks. I'm definitely a bigger fan of this assembly versus the interconnected keys that would inevitably pop off when my cat decides to use my laptop as a butt heater. Beyond the assembly, you can see that the laptop's size affords it the ability to actually have a relatively full-sized keyboard complete with numeric pad. It took about a day to get used to NOT crunching my hands together to adhere to a small layout. Now that I have gotten accustomed to having a full-sized keyboard on a laptop, I don't think I'll ever be able to go back to the old standard laptop keyboards.