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.
19 thoughts on “PowerPivot wish list: graphical relationship view”
Ken,
Watch:
http://www.msteched.com/2010/Europe/BIN205
Hi David,
That looks really interesting except... my copy of Visio 2010 Professional doesn't seem to have that template, and I can't figure out how to get it!
My "Office.com Templates" just tells me it is "out of date" for all categories, and the steps to do it on the Office help site (http://office.microsoft.com/en-us/visio-help/create-a-pivotdiagram-HA010014196.aspx) pertain to Office 2007.
I'd love to try that out... although I will still say that I think it should be in PowerPivot natively as many small businesses won't have (or even be aware of) Visio.
It would sure be nice if I could just click a button and see this in a graphical view like in Access.
But that is working backwards. The table design comes before the physical joining. Unless the tables are very obvious, why wouldn't you always draw the relationships.
What version of Visio are you using? The database modeller I use in Visio is better than that.
Sorry, so you're saying that you'd use the graphical view to actually build the relationships?
The issue I'm facing here is that I set up my relationships how I thought they should be, but my source database has the same info in multiple tables. (eg every table starting with POS has a POSPartition in it.) I'm finding that I have to carefully pick how to set up the yellow brick road to get from table to table correctly though, as that may be the only commanilty.
I'm using Visio 2010 Professional... you?
I am saying that I draw out the relationships first, so that I can check that it is valid and sensible. Then I do the joins in whatever tool.
I am using Visio 2010 Professional too. It's in the lappie, I will check what the actual template is and post back, maybe with a piccie.
Fair enough... the situation that I ran into is that I started doubting what I'd set up. So what I was looking for was a way to verify what I had, so I could review it to see if changes were necessary. I ended up marking down all my relationships by hand, making the visio chart and drawing them all in before I could even begin to audit.
Very curious to see what template you use... Sounds like I wasted a bit of time here.
I still maintain that a rudimenatry version of this tool should be built into PowerPivot though, like it is with Access. I'd be surprised to find Visio is a standard install for most Excel pros...
Ken,
The template that I am using is if File>New>Software and Database>Database Model Diagram. Uisng this template, I create models like this http://tinypic.com/view.php?pic=332ue8h&s=7
I think I disagree about building it into Visio, a tool should target itself appropriately. PowerPivot is not a datamodelling tool (nor is Access), and it is better IMO to use a tool better suited for the job. One of MS' big failings in my view is that they try to make tools do everything, and so many things never get properly implemented (just look at Excel, or SQL Server come to that).
As for Visio, I agree, but I think that Excel pros, or more accurately developers, should give it good consideration. It is a fantastic tool. Admittedly, the object model is hard, but it is such a good tool. I used it before MS bought it, and we used to do basic data modelling (when the templates were not as good) and then upload the basics into an enterprise tool like Erwin, we did layout diagrams where you could use the shape data to store attributes, job cycles where we built an Excel spreadsheet to identify arcs from job start and end times and upload that into Visio to create a 24 hour job cycle as a doughnut/onion type image. Fantastic tool.
This is bizarre... I don't have a Software and Database category. I've checked and I have full install of Visio Professional (including all Metric and US units.) Offic.com templates is no help as it just tells me that the categories it provides are "out of date" but gives no way to update them.
This is irritating!
What stencil are you using Ken?
You lost me... aren't Stencils the things that hold the shapes once you've already kicked off a template? I can't even find the template to start with...
I was asking about the stencil that you used to draw the diagram in this blog.
Ken,
What have you got in HKCU>Software>Microsoft>Office>14.0>Visio>Application>LicenseCache?
Hi Bob,
Don't have the file with me, but I think it was the basic flowchart stencil.
Interesting... that key shows STD, which is making me think that Visio believes it is Standard. It's definitely Pro though. I'm going to delete that key and run a repair to see if it fixes it up...
Nope... still comes back as STD, and still no Database catagory...
Aha!
I think I must have used an Standard key to activate the product or something. I downloaded a new key for Visio Pro and it seems to be working. I see the template now, and just have to figure out how to use it!
That's yet another dram you owe me 🙂
You can collect it at the summit. 😉
Ken,
The functionality you mention came up in one of my first PowerPivot posts in Microsoft Connect. With any luck, we might see something like this in the next version.
Interestingly, some of the early betas did have include a table relationship diagram. Apparently it was removed because the target audience they approached were scared off by the database look and feel. Internally, many developers wanted to retain the functionality.
While I have not played with the v2 CTP yet, I understand that this ability is now in. Yay!