In my view, the complexity of a model can be summarized by the number of different paths thru the model. The higher the number the more complex. To count the paths with a macro the legal paths need to be identified by foreign key constraints. If your model represents its relationships the attached macro can not only count the paths but it color codes the tables by changing their background color to highlight the 'importance' of the table in the model. This is also very handy when getting familiar with a new model.
Each table has two path counts associated with it; one towards root table(s), another towards leaf table(s). The counts are stored in attachments to the table so you can display them on your diagram if you want.
The color coding is done by creating a relative scale such that tables having scores in the lowest 25% are white, the next 25% light, 25% medium and 25% get a dark background. The darkest tables represent the tables that have the most paths. The table with the MAX leaf or root score gets a distinctive dark color so it can be spotted among others in the top 25%. Dark red represents root tables (those with the most child paths). Dark green (leaf tables) have the most parent paths. White tables have few or no precedents or dependents.
When scoring a table, a loop is arbitrarily counted as 3 tables. My logic being if one follows the recursion the counter would overflow so stopping somewhere is required. Since loops are usually not really all that complex I picked 3 but if you want to make them more expensive you can just change the literal to something else.
Since there is no intrinsic way to tell once a macro starts if/when it's done, I pop up a message box when you start the macro telling you to wait for the "Processing Complete" message box. Large models sometimes take a while to analyze and color. If you run from the macro editor, debug messages appear in the immediate window to assure you something really is happening:)
It wouldn't matter how many months I used this without making an update I'd notice something right after posting. In this case a way to make it dramatically faster.... That's the update, It not only runs faster but tells you how long it took when it is done.
After posting the last update, support sent me a workaround to a bug I complained about that prevented changing the size of shapes added to a model. So now this 2nd update inserts the appropriate work around code so that the 'legend shape' has a useful size.
It turns out that the workaround doesn't actually work but the change doesn't hurt anything so there is no point retracting or backing it out. Since writing the tool I've switched jobs and use PowerDesigner so I won't be updating the macro anytime soon if ever. Feel free to post updates yourself if you make any!
Powered by IDERA