Never Upgrade Database Schema When Disk Drive is Defragmented

My company's Data Warehouse development has finally completed for this release. The architecture team is now at the client site and deploying the Data Warehouse solution. We had estimated we will only take 4 hrs to upgrade the Live Database. We will be performing the first ETL offline. Unfortunately we failed the upgrade last time, not because of incorrect script or any technical error. it is because of the following: Instead of taking 4 hrs to do the database upgrade, we have to bail out after 6 hrs of waiting for the upgrade script to complete. Next time, check the hard drive fragmentation first before doing any database upgrade!

I Need More Memory for Runing ETL Testing

I have been working on a data warehouse project for almost five months now.  We didn’t use any existing ETL process tool on the market, instead we develop our own.

After I studied the source code of Rhino ETL, I believe that it is possible to write our own ETL process. I didn’t use Rhino ETL, instead, my team develop our own framework that suit our need. The key of what I learn about Rhino ETL is that I can yield result from a database connection. This allow me to save memory usage. Instead of loading a list of data, I yield only the data I need to be process. Of course, I still need to load reference data during the transformation process.

Anyways, our application has over 150 tables, and the data warehouse has about 100 tables so far. There will probably be more data warehouse tables need to be added in the future.  We are at the final phrase of the first data warehouse project release. My team has been doing performance and manual testing while I am developing a integration test framework. We have unit test that test the data transformation and other parts of the ETL process, but we don’t have an integration which I believe it is necessary if we want to have a robust product.

Long story short, I finally finish coding the basic integration testing framework. I am using NDBUnit part my my framework.

Here is the basic attributes of ETL Process:
1. It uses ParallelLinq to process the transformation
2. It chunk the process by date range to reduce memory usage
3. It clean up old data from the live system once ETL is completed

Here is the basic attributes of the Integration Test
1. It loads the entire live database to a Dataset before the ETL process
2. It loads the entire live database to a Dataset after the ETL process
3. It loads the entire data warehouse database to a Dataset after ETL process

The data can be either an XML file, or it can be an existing database.

Other than the fact that my Visual Studio Dataset design almost die because of the amount of table that I have, the testing was fine until I use an customer database for testing, and this is what happened:

Good thing I was running Windows 7 64 bit which allow my applications allocate more memory than my other developers’ 32 OS. However, the test in the end took 1 hour to run and fail to perform clean up due to SQL connection was disposed error.

The problem was that I was too optimistic about loading data into memory for comparing data. To resolve this issue, I now have to explicitly load and unload data when doing comparison.

In conclusion, memory and performance issues don’t just exist in your application, but even in your tests.

How to evolve to Sprint Planning in Scrum (Agile Development)

Approx. 2 years ago, my development team began to switch from the traditional Waterfall model to the agile development. In our case, we choice Scrum as our ideal development model.  Switching to agile isn’t an one night process. It took a good year to get everyone comfortable with iterative development.

Since there are already many resources about agile development, I am not going to write in details on what agile development is. Instead, I would like to write about how our development planning process evolves from the waterfall model to the agile sprint planning model.

The following software planning process are listed in chronologic order.

1. Team Lead Prepares the specifications
In this model, developers (incl. myself) were simply given a feature specification document, in return developers provided rough estimate on how long the task may take to complete the projects. Of course, the estimated time had to be somewhat reasonable.  The planning meeting usually involves just the team lead and the assigned developer(s).  In the planning, the general strategy of how resolve the problem is discussed. The project time estimate could range from 2 weeks to a month. The actual length of time to accomplish a task can vary from a couple of day off from the estimate to more than a month.

2. Large tasks are Broken Down
Instead of having a high level specifications overview, developers were given smaller tasks that needs to be accomplished. Often developers may not see the “big picture” of the reason of completing the assignments. The team lead provides his estimated time on how long it should take the developer to accomplish the tasks.

3. Effort Points
The development team learns about Effort Points or Story Points. This was the most challenging part of going into agile development. How do you estimate the effort points. The thing about effort point and time estimate is that given a task and its description, developers should agree upon the same effort point regardless of how fast they code.  An experienced developer may need one day of work to accomplish a task with one effort point. A newbie may need two days of work to accomplish the same task.

Notice my team choice to talk about task, instead of a user story. The team lead estimate the  story point by himself.

4. A Standard Task for 1 Effort Point
It was hard to estimated effort point per task when there are no standard. We needed to come up with a standard of unit. In our product, a typical feature involves creating a new database table definition, add new server side code, add new client code, update the data model, and create a new UI. This set of sub task was used to represent 1 effort point.

5. Small Team instead of Large Team
The development team were broken down into 3 teams. The original team originally has 12 developers. It was broken down such that each team is about 3 to 4 developers focusing on different aspects of the enterprise product suite.

The original team lead now becomes the master team lead of 3 sub team leads.

6. Effort Point and Sprint Planning
In our sprint planning meeting, all developers in the team have to come up with an estimated effort points for a given tasks. Everyone had to agree on the same amount of effort points. To do this, we use planning poker.  Whenever there is a discrepancy of the estimated effort points among the developers, the developers who gave the highest and the lowest effort points would need to explain to the team why they chose the selected card. Doing this has three advantages:

• It allows the developer to express their opinion, or at least force them to speak up.
• It allows exchange of knowledge within the team
• It allows everyone to see other developers’ expectations

After the two developers explain their concerns, everyone get to revolute the efforts and come up with a new estimate. Hopefully everyone will come to the same effort point.

It is actually fun to play planning porker.

Remember, there are no shame disagree other developer estimates. If you truly believe your estimate is correct, you can keep putting a different number than the other does. However, usually majority rules at the end because you are only given a limit amount of time to do the estimate.

7. Two Weeks Sprint + Demo
Instead of doing planning irregularly, the teams had  two week sprints. A sprint’s duration is two weeks. The first day is the sprint planning, and the last day is the sprint demo. That’s right, our developers need to demo what they had done to an audience on what they did during the sprint. The good news for us is that we only demo to the internal people and not customers from outside the company. If the demo crashes, it is not the end of the world. The demo has 3 advantages:

• Ensure developers show us what he/she promise to deliver
• Developers need to test their code well enough so that it works in the demo
• It allows developers to showcase what they have accomplished.

8. Making The Sprint More Efficient
Instead of having all three teams running on the same sprint cycles, it was broken down so that each time do the planning and do the demo on different day of the week. This way the attendees do not have to watch all three demos on the same day.

9. Need Better Tools
Instead of having everything on paper, we start using TFS and its planning tool. The tool keep track on user story, the tasks, the estimated time and the remaining team. This is how the leads keep track if the developers are on track on completing the task.
At this point developers no longer do effort point estimate, instead, we give our estimated time.

10. More Tool
TFS sucks in many usability aspect, as a result we got Urban Turtle to help us.

11. We Need Feedback
With the introduction of Burn Down chart, we now have constantly feedback on how close are we completing our task in time.

12. Dealing with Unknown Factors
I was involved in a project that has many unknowns. In the first 8 to 10 sprints the team under estimated the tasks. Many issues where discovered during the development and were not foreseen in the sprint planning. These new issues either needs to be fix in order to unblock the developers, or they can be push to the next sprint so that they wouldn’t disrupt the current sprint.
After the first  8 to 10 sprints, the  team learned to be pessimistic on their time estimate. It is actually better to over-estimate, so that you are able to complete your committed tasks.

Conclusion

My company’s development methodology continues to evolve. There are many advantages when using agile development, while there are pitfalls we need to be careful of. Here are positive things and pitfalls that I saw in our development model.

Positives:

• Everyone is committed to accomplish the assigned task by the deadline
• Any issues that were discovered during development can properly be addressed in the sprint planning meeting, instead of allowing the developers tackle the problem without meeting with others.
• It is flexible, because every two weeks we have a chance revaluate our task priorities.
• If anyone need to take a sick day, vacation, or leaves the company, it has less impact to the entire projects because the user story is broken down into tasks. A task has to be accomplish at minimum in 2 weeks. If the task takes more two weeks, it needs to be broken down further.

Pitfalls:

• It is temping to under estimate because of peer pressure or their subconscious competitiveness against other developers.
• It is easy to schedule customer’s requested user stories, and forgot developers may want to do their own projects such as writing more unit tests, develop new tools, optimize or improve existing feature quality, preparing training session, which helps improve the development process.

To avoid under estimate the project time, the burn down chart and the retro respective helps the team to recalibrate their thought on the estimate, so that they can do better estimate for the next sprint.

The team can add personal project as a task so that developers get the chance to do their own stuffs.