How an #Agile Data Warehouse leveraged an #InnovationGame – Iteration 2!

A few weeks ago I authored a post that explained how were leveraging an Innovation game on my current Agile Data Warehouse project. You can find the original post here.

Iteration 2

One of the aspects I love about Agile is the freedom it allows you to be bold as it acknowledges it is impossible to get it perfect off the bat. I find that this encourages people to take a chance and try new things. If it doesn’t work? No problem, we will just adjust and get better as we progress.

My initial blog proposed using a visual Innovation Game with Visual Report Boards to allow for brutal transparency and the management of the data requirements for an Agile Data Warehouse project. Each Visual Report Board had an object at the centre with 6 dimensions around it that illustrated how the object could be reported on. (I affectionately refer to these boards or diagrams as Hexes) Our Objects were the people who the corporation were interested in. It turns out that this method has been successful. It has allowed us to create a visual backlog of data requirements and have the clients prioritize the work and guide the work according to the overall business priorities.

Why am I writing this blog then?

Well it turned out that once we started placing the data requirements or reporting stories on the Visual Report Boards, it became apparent that our objects and dimensions of the diagrams required tweaking.

Initially I had placed a Person object at the centre of the diagram. The dimensions where then aspects of how we need to report on that person. Although this object and dimensions may work for other projects, it did not work for our project. (Actually I think that most Data Warehouse projects would probably make the same change we did, but I’ll let you decide that for yourselves)

Our experience was that most of the reports or data requirements were very clustered on the Visual Report Boards and the diagrams did not allow for the visual communication of what the report was or what data was required. I was starting to worry that this process might not provide the brutal transparency that allowed for the efficient creation of a Data Warehouse in an Agile way.

Just the Facts

After reviewing the requirements, it became apparent I had the wrong objects at the centre! Rather than people at the centre, the objects at the centre should be transactional data. The centre objects needed to be the actual data that was summed, aggregated, filtered, sliced, and diced. Once I made this change, the value of the Visual Report Boards increased exponentially. Now they communicated the content and purpose of the data requirements.

The real indication that I was on the right track is that the Revenue and Expenses Hexes I now had were also the first two Fact tables that were needed in the Data Warehouse! This method of visualization and analysis was aligned 100% with the Data Warehouse design process. Of course the Hexes were Fact table. This made perfect sense. I imagine more Hexes will be needed in the future as we discover the need for more Fact tables.

In addition, we created one more Master Data Hex as some reports and data requirements are not related to transactions.

Summary

I am convinced the use of these Visual Report  Boards and the related use of an Innovation Game enable the creation of a Data Warehouse in an Agile manner. We are executing in Iterations and not increments and the clients are thrilled with the control, visibility, and value they get every 2 weeks. I’ll post another blog once we have created enough of the Fact tables to provide more lessons learned.

How #Oracle helped to make my Data Model #Agile

Many people who know me know that I have a certain fondness for tools that can help to make our jobs easier. I agree that a fool with a tool is still a fool, but I would argue that craftsman with the right tool can be an even better craftsman. In essence, the problem isn’t the tool,it is the person wielding the tool. So let’s not be too quick to paint the use of all tools in a negative light.

Agile Data Modeling

It has always been a struggle to determine how we can manage our Data Models and Databases in an Agile way. In the past, the Data Modeling tools were limited in the functionality they provided that allowed both collaboration and versioning. Recently the Data Modeling tools have become much better at providing that functionality, but most of the Data Modeling tools I have seen have still provided that collaboration and versioning functionality in a proprietary way that was created by the Data Modeling vendor.

This has made the Data Model and Database operate as second class citizens on Agile projects. Usually separate processes need to be created to determine how we will integrate the Data Model and database into Continuous Integration environments. I believe that some of the resistance of Database Modelers and Database Administrators to implement Agile for Data Design activities is due to the fact that the tools at their disposal have placed them at an Agile disadvantage. Due to this the Data Modelers will prefer the illusion of a more stable Data Model so that they are potentially limiting the number of SQL change scripts they need to generate. (and thereby limiting their technical debt) SQL Change Scripts still seem to be the primary way that people roll out changes to their databases. It is not uncommon for projects to have a huge number of these scripts that then themselves need to be maintained. I have seen a wide variety of ways people use to generate these scripts that vary from a Data Modeling Tool generating them to having a DBA write them from scratch.

Having a large backlog of these scripts and having them be generated in such a way is a huge hinderance to being Agile and accepting change. Having to then try to synchronize these scripts in alignment with check-in versions can be beyond frustrating. And I don’t even want to talk about having to maintain all of these scripts and manually checking them in.

Agile Data Warehouse

For a recent project, we were tasked with creating a Data Warehouse and we needed to create the Data Warehouse in an Agile way. We needed to embrace Agility for two reasons:

  1. To deliver the maximum value to the client and deliver the best design that meets actual business needs
  2. To address the reality that we will be required to accept changes throughout the duration of the projects as sub-projects are completed

After reviewing Data Modeling tools that I have used in the past, I was still struggling with how we could develop a process that would allow us to easily adopt Agile practices for our Data Models and databases that would allow the Data Warehouse to be run as Agile as other projects.

Oracle SQL Developer Data Modeler

I did some initial investigation of what Data Modeling tools are now available. My objectives were to find an affordable tool that could be used to do the Data Modeling and that all developers could use to view the Data Models and collaborate on them. Most of the tools seemed to fall into two camps:

  1. Affordable Data Modeling tools that provided good collaboration but limited Data Modeling and integrated versioning functionality
  2. Expensive Data Modeling tools that provided good Data Modeling functionality but limited collaboration and integrated versioning functionality

Then on a list that reviewed the best Data Modeling tools, there was mention of Oracle SQL Developer Data Modeler. It was stated that it was a robust Data Modeling tool that was free. This was certainly something that needed further investigation.

Rarely does a new tool present such functionality that you require time to determine how to use it. 🙂

The functionality that was provided with Oracle SQL Developer Data Modeler absolutely changed our project. We now had the ability to be a first class Agile citizen with the rest of the Agile projects and be able to adapt to change easily. We would be able to roll out changes easily and efficiently as new requirements became apparent.

Oracle SQL Developer Data Modeler provided the following functionality:

  • Full, integrated functionality with Subversion. The Data Models can be checked in and versioned along with the application code.
  • Ability to define a domain library, naming standards, and glossary to ensure a consistent model
  • Ability to apply design rules to a model to ensure Data Modeling standards are being followed
  • Ability to model Logical, Relational, Dimensional, and Physical Models
  • Ability to store DML to populate data along with each table definition. (and determine when they should be executed – at the start/before object creation/after object creation/at the end)
  • Use of Barker notation. (Pet Peeve!)
  • Extensive functionality to generate different types of models and compare different types of models
  • Full versioning functionality to report on the differences between the versions and what changes will be committed on check-in.
  • Robust Data Modeling functionality to create Data Models and create physical databases.

Did I mention free so all the developers could use it to reviews the models and make requests? 

Summary

Although we still need to use Database Re-factoring practices to adapt to changes, the versioning and collaboration features of Oracle SQL Developer Data Modeler did cause us to pause and determine how we could best use its functionality. In fact, it caused us to define a process for how we could efficiently roll out Re-factoring changes in an automated way as Oracle SQL Developer Data Modeler now made the Re-factoring manageable.

If you are looking for an Agile Data Modeling tool, I would highly recommend it!

Did I mention it is free? 🙂

And the support for the product from Oracle is top-notch. Questions I have had have been answered same-day..

Data Modeling mistake – Violating 4th Normal Form

As I read David C Hay’s awesome book on Data Model Patterns, I start to realize the mistakes I have made creating certain Data Models in the past. In particular, one mistake that I made repeatedly became very apparent.

Data Modeling mistake – Violating 4th Normal Form

Looking back in some of the Data Models I have created, I would say they have been pretty good and for the most part modeled the data in the enterprise pretty well. But in a couple of cases, we did create some components of the models that in hindsight leave me somewhat regretful.

The one situation that caused me to reflect was the solution we used to model relationships between three primary entities and addresses. Each of these relationships were a many-to-many relationship so certainly an intermediate table was required. In addition, we also need to define an attribute for the intended purpose of the address. As this was an attribute of the relationship and not the address itself, this was another requirement for a relationship table. So far, so good.

What we eventually decided on was the following structure as an address could be related to an account OR an instruction OR a client.

  • contact_method_id (PK)
  • Address_id (FK)
  • Account_id(FK) – nullable
  • Client_id(FK) – nulllable
  • Instruction_id(FK) – nullable
  • Address fields (no sense listing them all out)

We thought it would be simpler having one table that contained all the relationships to Address. Basically I think we thought that it was simpler from the Data Modeler point of view and made the diagram simpler. But what we underestimated is that it required the developers and business users to understand a more complicated model and know when the different nullable foreign keys would need to be populated. In a sense, we built additional logic into the data model to reduce the number of tables.

4th Normal Form

What we were doing was violating the 4th Normal Form for the reasons of perceived simplicity. In hindsight, I experienced the problems that violating 4th Normal Form can cause.

4th Normal Form is defined as: “Under fourth normal form, a record type should not contain two or more independent multi-valued facts about an entity.”

All my Data Models now are in 4th Normal Form. They may have more entities, but more entities does not mean a more complex model. My standard now is for my Relational models to start in 4th Normal Form and not just 3rd Normal Form.

#Agile in a Unionized Environment?

One question I get asked a lot is whether Agile can work in a unionized environment. Fortunately, I have had the opportunity to work in a unionized environment. The engagement did not start out as an Agile engagement, but as the project wore on there were more and more opportunities to influence in more and more Agile methods. The engagement itself was to help set lead the implementation of a solution into an operational support area. Doesn’t sound very Agile right? But I thought it was a great opportunity to try to extend Agile practices into the realms of Application Management Services, otherwise known as Maintenance and Support.

The People

Without a doubt, the people I met and worked with were some of the most client-focused, motivated people I have ever met. (Hi Catherine!) In fact, everyone on the project team was incredibly focused on value to the customer. At every turn, there was mention by people of the impact that current issues were having on the end clients and co-workers. Were there a few people who were the stereotypical ‘Union-worker’ ? Sure. But in my experience the percentage of those type of people are pretty consistent across both unionized and non-unionized environments.

There were a few issues to used Agile that were perhaps more prevalent in a unionized environment though.

The Challenges

Roles – The primary challenge was that unions typically have a large component of their footprint being the formalization of roles and the responsibilities of each role. In addition, the corporation itself also further segmented these roles into functional departments. These functional departments, like Quality Assurance, were the only departments that was authorized to perform those tasks. An interesting thing happened on projects though. People from these different functional areas were assigned to the project and the project was then given the authority to use them in whatever way they saw fit. The projects were also co-located. So the projects were quite a bit more Agile compared to the rest of the organization. But you always had to be careful so make sure everyone was totally happy with how the team self-organized as one person could submit a union grievance if they did not like the work they were doing and if they thought it was outside their official role. Unfortunately when the project was over and it was transitioned to the Maintenance and Support areas, we were less successful in having the team self-organize and had to adopt more structured roles. It is an interesting note that the client knew that this structure was not as responsive and efficient, but they required the process to be aligned and use their current organizational structure. <sigh>

Compensation – Compensation in a union environment is again a very formalized process and is based upon role and seniority. As we worked on the projects, it really did hamper the ability to recognize people even in informal ways. (like offering to buy someone a lunch) If you wanted to offer someone a perk, you had to offer everyone the same perk whether they were involved or not. This seems like a small thing but it ended up being a constant conundrum for the project.

Documentation – The documentation requirements were quite extensive for two reasons. One, they used documents as the transfer of knowledge between one department to another. Two, they evaluated people’s competencies and their readiness to be promoted to the next level based upon the deliverables they can produce. As you can imagine, this was a problem in trying to move toward more Agile methods. In addition, these documents frequently sat on the shelf for long periods of time after they were produced and became stale. <big sigh>

Hierarchy – Once we transitioned to Maintenance and Support, all departments had managers that assigned work. This was a traditional push system of work. The people were not empowered to pull work at all. Eventually this was a major issue as it really limited the ability to be Agile and focus on Value and Flow.

What did we do?

So you may be saying to yourself, this seems like a failure and Agile can’t work in a unionized environment. I would say that is incorrect.

One aspect that I thought we were very successful was implementing a Kan Ban board and limiting Work in Progress(WIP). The Kan Ban board was also extremely helpful in letting management see exactly what we were working on. Until we implemented the Kan Ban board, it was not uncommon for people to be juggling 20-30 items at once and not informing people of issues. As you can expect, the team also was very energized and focused by the use of the Kan Ban board combined with a Daily stand up to discuss the work they had planned for the day. These practices are still being used today in that area.

As far as the other issues discussed, although they were prevalent in the union environment, I have seen them exist to an equal or greater extent in other large corporations. These are not just union issues. I would propose that they are management issues. I believe they contradict Agile as they place the someone else’s needs above the client.

Can Agile be implemented in a union environment? I would say yes, but there are challenges that need to be addressed. But just like anywhere else, you need the commitment from management that the client’s needs and value will drive the organization’s structure and not the other way around. The challenge with a union environments is you need this agreement from not only the company’s management, but also the union management.

Top 10 practices for estimating Agile projects successfully

I’ve had multiple posts in the past that provided my passionate opinion on why I believe we should estimate Agile projects. (Both from the perspective of the client and the team) But rather than get into that discussion once again, I thought it would be more valuable if I shared the 10 practices that we have used over the past 6 years that have allowed our teams to successfully estimate Agile projects.

Context

I work for a company that bids competitively on projects against other companies. In almost all situations we have to create estimates to provide to the client or else we would not be successful in winning the business. So estimating really is a fact of life for the type of business we are in. Although it might be nice to proceed on a project without an upfront estimate, that really isn’t a luxury we are provided. I believe we are in a similar boat to the majority of people out there.

The Practices

Over the past 20 years I have honed my estimating skills with the experiences on projects. Over the past 6 years, this estimating has been predominantly for Agile projects. Although we have not been perfect in my estimating, I am quite proud of our track record and feel that we have been correct far more than we have been incorrect. Where we have been incorrect, we have augmented the estimating process so we won’t be incorrect in that way again.

The practices are not in order of priority as I had a real problem trying to do that as I think they are all important. So without further ado:

1) Remember to estimate Project Management and Technical Management

We frequently ran across missing these factors on early estimating attempts. This is usually done by everyone as they work on estimating as the work is easy to forget. It is a critical part of the estimate to ensure that these estimates are included so that we don’t scrimp on these tasks and cause larger issues for the project.

2) Don’t plan on your Technical or Application Architects being any more than 20% on construction tasks

This one continues to plague projects I estimate. No matter what the work is, the Architects will always want to work on their fair share of construction tasks. It is a noble desire and will return great benefit if they can work more on construction tasks. But I wouldn’t bet on it. In fact, I would even consider reducing this to zero depending on team size and complexity of the solution. If the Architects can work on more construction tasks, just consider that a bonus.

3) Remember to estimate for meetings and other factors in your schedule estimate

OK, now you have the estimates so lets just plan the hours out on the calendar right? Not quite. Remember to reserve some time every day for meetings, stand ups, and other non-construction activities. In addition, remember to plan for at least some time for vacations, holidays, and sick time. Use your statistics from your company to generate a rough factor for the project. It will just reserve a percentage of your schedule at the start, but then you will have the wiggle room when people start dropping like flies during flu season.

4) Hold a risk workshop and include the weighted estimates in the overall estimate

Mention Risk Workshops and most people yawn before you have completed the sentence. But they have great value. They are a great team building exercise at the start of the project as well. But ultimately, the output from the Risk Workshop should be used to generate an estimate and apply that estimate to the overall project estimate. By calculating the impact and probability of Risks occurring you will generate an estimate of the Risks that will likely occur. My experience is that if you don’t include these estimate, you will frequently address these risks occurring with Change Requests. The project can be much smoother if the estimate are included up front and you don’t surprise the clients. Yes, we won’t know all the Risks at the start, but it is better than assuming there are no risks at all. As Dr. Phil says “How is not estimating any Risks at all working for you?”

5) Estimate at multiple levels and triangulate

Probably the most important practice. We never just create one estimate. We typically estimate bottom-up (object level), deliverable (mid level), and top-down (schedule level). We use metrics for the bottom up and mid level estimates. (how long does a simple CRUD screen take, moderate report, etc..) Then we compare all three estimates and rationalize the differences. It is amazing how this rationalization uncovers estimating oversights.

We also then use Planning Poker sessions as we execute the project to confirm requirements and plan our iterations.

6) Proactively get information required to provide an estimate

Many times there are factors that may cause the estimate to change greatly. (Data Conversions, Legacy interfaces, unknown technology) Ask these questions early on and factor them into your estimates.

7) Say No when you don’t have information to provide an estimate. But be proactive and say what you need to provide an estimate.

If you don’t have the required information to properly estimate, then don’t. 🙂 But don’t just say you can’t estimate. Provide the details of what information you need to be able to provide an estimate. I know on past projects we have guessed many times on estimates where in hindsight, the client would have gladly provided all the information we required.

8) Track estimates and actuals and use the metrics for future estimating. (both within the project and for future projects)

Yep. We need to track estimates. Sorry. These metrics will then be used to determine on average how long certain objects will take and the percentage that certain areas of the project will require. (Like Project Management!)

We also hold retrospectives and review iteration estimates and actuals so that we can revise our estimates as soon as possible within the project and taken them into consideration for future iterations.

9) Factor educated contingency into your estimates.

We have 17 contingency factors (and counting) that are weighted and applied. (at the team’s discretion) If nothing else, it is a least a checkpoint for the team to think about whether certain situations exist that may affect estimating. (new team? data conversion? new technology? complex interfaces?) Don’t just apply a standard 10% blindly to estimates. It has to be based on reality.

Like Risk, these contingency factors should then we applied to the estimate. I would also recommend that you don’t show what part of the estimate is Risk versus Contingency versus base estimate. Clients will then say that the contingencies won’t happen so just subtract that estimate. <sigh> The estimate is holistic.

10) Execute in a manner that respects that estimates are not actuals. They will be incorrect.

And after all the estimates are done, don’t execute in a manner that makes Captain Bligh look like a level fellow. Don’t micro-manage, realize that these were only estimates – some will be higher and some will be lower. Very rarely will any be totally correct. Let the clients trade requirements and stories as priorities change! You could not possibly know all the scope up front. But also don’t just agree to add new scope. A lot of issues about estimating do come down to good old-fashioned scope control. The estimate should be a guide and a placeholder for a discussion, not a roadmap.

Summary

My experience is that is we do all of these practices, our estimates will still be wrong. But much less wrong. More importantly, these practices help us to discover better ways of estimating. Discovering better ways doesn’t only apply to development practices, but all project practices. Ultimately these estimates also help the team members to feel better about their work as well. No one like to miss estimates and managing projects in a manner that respects that the estimates are not actuals will alleviate many of the negative connotations with estimates.

<SoapboxMode>

The correct thing is to fix how the estimates are managed, not to stop doing estimates.

</SoapboxMode>

P.S. I forgot one! Here is a bonus practice 11) Estimate that you will be wrong – remember to estimate for refactoring, defect fixing, and some rework. We are all good, but not that good. 🙂

 

My #1 Agile Failure

Some of you may have read my personal Agile Adoption Story and possibly thought that it was a good story on Agile Adoption and how Agile can sometime succeed on the first try. I know I’ve read many stories about the pain and suffering on the first Agile projects people have been on.

If you missed the Blog Post on my personal Agile Adoption story, you can find it here:

My Agile Adoption Story

So given that my first Agile project was successful? What was my #1 Agile Failure? Simply put, it was the fact that my first Agile Project was successful!

Explanation

Please let me explain. We came out of that first project with a false sense of security. We thought we nailed this ‘Agile’ thing. I mean we didn’t do many of the core Agile practices and still we delivered on time, on budget, and satisfied the client. This false sense of security was at a personal, project, and perhaps even a company level. We thought that we could deliver again with different teams, different problems, and different technologies.

I often say that I would have rather failed slightly on the first project so we knew we still had a way to go. Unfortunately that didn’t happen.

Why did we succeed then?

I believed we succeeded primarily due to the expertise of the development team and the expertise of our client/Product Owner. Having these two factors can address shortcomings in many other areas.

To recap, here are the Agile Practices we did not follow:

  • Test Automation
  • Continuous Integration
  • Kan Ban
  • User Stories
  • Planning Poker
  • Pair Programming
I would not even dream of being on another project without at least 5 and possibly all 6 of these practices. We really had a project that we led in an Agile way but that did not use XP practices, Agile Requirement practices, or any Test Automation! In addition to these 6 practices, I have learned about many other Agile Practices that I also would not be without on a future project. Some of these are:
  • User Story Mapping
  • Paper Prototyping
  • User Design Studio
  • BDD
  • TDD
  • And many others
But it took me a couple of hard projects after this first successful project to teach me that the hard way. I’ll leave it up to a future post to tell the story of a couple of the hard projects. 🙂