The Djeeni Best Practice

Djeeni is a set of building blocks. You have a lot of freedom in how you build your Djeeni process combining these blocks. Nevertheless, we have designed Djeeni having an optimal combination of MS Excel + Djeeni process steps in mind to achieve maximum efficiency with minimum effort. Please consider using the below tips.

Djeeni looks at your process as a transformation of data from one or more sources to one or more targets. To achieve maximum readability and maintainability we suggest to follow the following sequence of process steps:

1. Define all known source worksheets of the process using WSheet Use
2. Define all known target worksheets of the process using WSheet Use
3. Define all supplementary ('master data' ) worksheets of the process using WSheet Use
4. Optionally use temporary ('parameter') worksheets to store the result of some intermediary calculations using WSheet Use
5. Keep the process steps for creating and filling a specific target worksheet together as a block.
6. Start a new block for a new target.
7. Put a Comment step before every block describing what is created in the block
8. At the end of every block release the source and target worksheets that are not needed further in the process using WSheet Release.
9. Within a block focus on the target structure with the question: how this cell / range gets its value?. Identify ranges that can get a value in one step instead of dealing with every cell separately.
10. Pay attention to templates: most of them are row oriented (similar data below each other) but there are many exception of colum oriented templates (similar data next to each other). You can easily transpose row-oriented ranges to column-oriented ranges in DJeeni instead of filling the target range cells one by one but you have to recognize this option by understanding the template structures of the worksheets.

If calculations look too complex for a direct source-target process then consider building a two-phase process: in the first phase create intermediary/storage targets that contain the results of some calculations; or enrich the input. Then the second phase uses these intermediary targets as source worksheets (next to the original ones) to create the final targets.

Almost every report has fixed parts: labels, headings, (almost) constant values. You can set these values using Djeeni but investing in a one-time effort to create a template instead pays off. The corresponding Djeeni process is shorter and the maintenance cost of the template in MS Excel is lower than updating the Djeeni process.

MS Excel is strong in formatting so Djeeni leaves it to MS Excel. Make your target worksheets beautiful by formatting the templates that you create for them.

When a Djeeni process is working with many source worksheets, it is wise to create a naming convention using fixed length workbook name parts. For example: if your workbook names contain month names then use fixed 3 character length abbreviations instead of variable length month names in the workbook name. This approach comes handy at workbook lists.

MS Excel comes with regional settings and that includes different formatting of dates and numbers. Exchanging workbooks between organisations, systems and users leads to frequent problems with dates not understood by MS Excel as dates but literal text values making impossible to use these values in any process. Most of the time you can not do anything about it as the worksheet is not created by you. But if you can, try the below steps:

• Intermediary worksheets should (and sometimes final worksheets may) contain a date in three separate columns: year, month, date. Consider using dates in this structure. You can easily create real dates from these values (if needed) using the MS Excel function Date().
• Avoid using month names in source and intermediary worksheets. Instead use month numbers (January ⇒ 1, …, December ⇒ 12). Keep month names only for target worksheets.
• If dates are entered manually by a small group of people, educate them to note that (incorrectly entered) dates as text values are left aligned within the cell while proper date values are right aligned. Correct these values during manual entry.

Regional settings apply to numbers, too. Djeeni comes soon with a Cleanse number process step to help fixing issues with different number formatting. Until then you can follow the same instruction as with dates:

• In case numbers are entered manually check if they are right aligned in the cell. Left aligned values are understood by Excel as text and must be corrected during data entry.