Excel: How to Use Gannt Chart to Display Event Timeline of a Job

A Gantt chart is a type of bar chart^[1] that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project.

In this article, we will use Gannt chart to display the event timeline of a parent job (i.e., "Creating Application"), which was processed by its child jobs asynchronously.

Gantt Chart in Excel

As shown in [2, 3], there is no direct support of Gantt Chart in Excel.  However, you can use Stacked Bar to achieve the same effect.  In both referenced articles, they use date and duration (in days) as example.  Here we will show you how to use time and duration (in hh:mm:ss) to draw the Gantt chart.

	Start Time	End Time	Duration (in secs)	Duration (in hh:mm:ss)
pool-get-vm-from-iaas	8:49:57	8:52:11	134	0:02:14
otd-add-app-config	8:49:52	8:49:57	5	0:00:05
scale-composite-operation	8:49:57	8:54:03	246	0:04:06
create-image-and-start-instances	8:49:57	8:54:03	246	0:04:06
otd-add-node-to-app-config	8:54:01	8:54:03	2	0:00:02
Creating Application	8:49:51	8:54:03	252	0:04:12

If you select the cell "8:49:57" and check its format by selecting "Format Cell", you would find that Excel automatically assign the "h:mm:ss" format to the cell.

The main issue of drawing Gantt Chart in hh:mm:ss format is that

How would you represent your duration? 

To start with, I have tried the following formats:

• 02:14
• 134 (secs)
• 00:02:14

By trial-and-error, it turns out that Excel only like the third format.  If you chose the first format, Excel would treat it as 02 hours and 14 minutes.

Drawing Steps

Without much ado, I would urge you to watch this video^[2] for the how-to.  Here we only highlight the steps that are unique to this Gantt Chart.

You can insert a bar chart by clicking Insert > Bar > Stacked Bar, and a blank chart will be created as below.

Then you add the data series to the chart by selecting the blank chart and right clicking, followed by choosing Select Data from the context menu as shown above.

In the Select Data Source dialog, click Add button under Legend Entries(Series), and then an Edit Series dialog will appear:

1. Click button under Series name to select the Start Time cell, B1 in this case.
2. Click button under Series values to highlight your time cells (from cell B2 to cell B7 in this example), see below:

Click OK to return to the Select Data Source dialog, then repeat the step to continue adding Duration data series (i.e., Duration (in hh:mm:ss)) to the chart. When the Start Time and Duration data series are inserted into the chart, you will get the following chart:

Next, you need to hide the Start Time legend, select one blue bar and right click, then choose Format Data Series. In the Format Data Series dialog, click Fill from the left pane, and select No fill option, then go on clicking Border Color, and select No line option, the final screenshot should look like:

In the next step, you would like to convert the numerical step on the left side of the chart into a list of tasks. Click on axis labels in the chart, then right click and open Select Data. Under Horizontal (Category) Axis Labels, click on edit. Using your mouse, highlight the names of your tasks.

You're almost finished. You just need to remove the empty white space at the start of your Gantt chart. Click on the first Start Time (i.e., 8:49:51) in your data table. Right click over it, select Format Cells, then General. Write down the number you see. In my case it is 0.367951389. Hit Cancel because you do not want to actually make any change here.

Repeat the step to see the number for the last end time (i.e., 8:54:03) of your time series.

In the Gantt chart, select the time labels below the bars, right click and choose Format Axis.  Change Minimum to Fixed and enter the number you recorded (i.e., 0.367951389). Do the same for Maximum by entering 0.370868056. Change Major unit to Fixed and enter the number 0.0005. You can play with this  delta to see what works best for you.

When you are finished, your Gantt chart should look like this:

Tips

1. Make sure both your "Start Time" and "Duration" cells have the "h:mm:ss" format:
• Format Cell > Custom > h:mm:ss
2. You can reverse the order of Y axis by selecting "Categories in reverse order":
• Click on axis label > Right click and select Format Axis > Axis Options > Categories in reverse order
3. You might want to reorder rows to reveal the order of tasks.  
1. For example, otd-add-app-config is the first task executed in the above diagram and other tasks (except the parent task "Creating Application") depending on it.  So, it might be a good idea to move it to be the first row and you can remove "Creating Application" parent task, etc.

References

1. Gantt Chart
2. How To...Create a Basic Gantt Chart in Excel 2010
3. How to create Gantt chart in Excel?

Beautifying Table and Column Comments for Design Review

Data model design is an iterative process. As soon as the conceptual data model is accepted by the functional team, development of logical data model gets started. Once logical data model is completed, it is then forwarded to functional teams for review. A good data model is created by clearly thinking about the current and future business requirements.

To facilitate the review process, you need to present descriptions of entities and attributes in the data model to the functional team. Some database developers prefer working at source level (i.e., SQL DDL). For example, you can present the following EMP table to the team for review:

-- Employee Data
CREATE TABLE "SCOTT"."EMP"
(
"EMPNO" NUMBER(4,0),        -- employee number
"ENAME" VARCHAR2(10 BYTE),  -- employee name
"JOB"   VARCHAR2(9 BYTE),   -- job description
"MGR"   NUMBER(4,0),        -- manager ID
"HIREDATE" DATE,            -- hiring date
"SAL"    NUMBER(7,2),       -- salary
"COMM"   NUMBER(7,2),       -- commission
"DEPTNO" NUMBER(2,0),       -- department number
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
)

In this article, we will show another way which presents the following table generated semi-automatically from the offline database using JDeveloper and Microsoft Excel:

Using the first approach, the drawbacks include:

• SQL DDL scripts tend to be error-prone
• Comments are only for human reader and not part of the DB definitions

That's why we propose the second approach which can resolve these two issues.

Offline Database

In JDeveloper, database development is available offline in the context of a project, allowing developers to create and manipulate schemas of database objects which can be generated to a database or to SQL scripts. Database objects can also be imported from a database into a project. See my previous post for more details.

You can follow the instructions in [1, 2] to create offline database objects. For the demo, I've created a database diagram and drag an existing EMP table from the SCOTT schema to create a table on it.

Adding Comments

Double-click the EMP table component on the diagram to open the Edit Table dialog,

Select Comment in the navigation panel to enter table's comment as shown above.
Select Columns in the navigation panel and navigate them one by one. In the Comment field, enter column's comment as shown above. Click Save All to save your work.

In the Application Navigator, under Offline Database Sources | EMP_DATABASE | SCOTT, right-click the EMP node, and choose Generate To > SQL script ... to create SQL script file named emp.sql.
Open emp.sql in the editor window. Look for comments of table's and columns' at the bottom of the script as shown below:

COMMENT ON TABLE EMP IS 'Employee Data';

COMMENT ON COLUMN EMP.EMPNO IS 'employee number';

COMMENT ON COLUMN EMP.ENAME IS 'employee name';

COMMENT ON COLUMN EMP.JOB IS 'job description';

COMMENT ON COLUMN EMP.MGR IS 'manager ID';

COMMENT ON COLUMN EMP.HIREDATE IS 'hiring date';

COMMENT ON COLUMN EMP.SAL IS 'salary';

COMMENT ON COLUMN EMP.COMM IS 'commission';

COMMENT ON COLUMN EMP.DEPTNO IS 'department number';

Select the above comments and copy them into a text file (i.e., emp.txt).

Generating Comment Table

Start up Microsoft Excel and import text file as follows:
On the Text Import Wizard, you specify delimiters using space and paired single quotes as shown below:

After clicking on Finish button, you can remove column A,B, and E. It will then present you with the final comment table as shown at the beginning of this article.

Conclusion

Comment tables generated in the second approach have the following advantages:

• The source of comment table is offline database object which can be validated by JDeveloper and can be source controlled.
• They are part of the DB definitions and can be queried as follows:

  • select comments
    from user_tab_comments
    where table_name = 'EMP'
    /

  • select column_name, comments
    from user_col_comments
    where table_name = 'EMP'
    order by column_name
    /

References

1. Designing and Building Database Tables
2. Database Development with JDeveloper
3. Modeling Data with Offline Database in JDeveloper