Over the last couple of days I've been trying to build some stuff with PowerPivot and I've come up with some other things that I would find pretty helpful. Here's one of them.
One of the big challenges I found was trying to figure out why my PivotTable was returning the results it was, instead of what I was expecting. Part way through my troubleshooting process I began to doubt the way I'd set up the relationships between my tables, so I decided to take a look at them. I found myself staring at this table:
The problem is that it's really difficult to visualize data in this format, and I found myself longing for a good old Relationship diagram like we can build in Access. I did pretty much the only thing I could and reached to Visio to draw this one up:
The one side of the relationship is shown with the +, while the many is shown by the circle and lines.
This is the final version, but the mapping of the prior showed me that I'm made some errors in the relationship flow. Even this version actually turned up a couple of thingsâ€¦ tblCOA is the centre of a couple of many to many relationships.
The challenge with this is that it took me quite a while to draw up as I had to document each link, draw the table, then move everything around so that it wasn't a crazy mess. It would sure be nice if I could just click a button and see this in a graphical view like in Access.