July 16, 2010
Hi Pieter,
No apology necessary, I am just trying to illustrate that we're taking two steps forward and one step back (sometimes two) with each exchange and there's obviously more information that I'm not aware of that's driving your decision making. Without knowing the full picture I can't help you.
It's great that you provided a simple example of what you're trying to do, but by making it too simple you've left out important information which results in me providing you with answers that don't meet your needs. This is wasting time, both yours and mine.
I recommend you create a new example file that addresses EVERY scenario you're likely to come across and addresses all of your needs so that we can help you correctly the first time.
And take the Power Pivot course! This really is the best way, because it still seems to me that you don't really understand the purpose of the dim tables vs the fact tables.
In regards to having a primary key or not, it doesn't really matter as long as the dim table has a unique identifier for each town and area, this can be the name or an ID. If the AreaID and AreaName columns are both unique then the ID column is resulting in you storing data in Excel that you don't really need. Likewise for the TownID and TownName.
Mynda
August 3, 2020
Hi Mynda,
Many thanks and I've taken everything in. To be honest, although it seems that we were going round in circles (and we probably were) I was learning lots and had to make some irritating mistakes along the way. I'm going to take your advice and take a few days to develop my thoughts and consider every scenario I'm likely to come across before coming back again. Part of the problem was likely due to my trying to use Excel/PP as a full relational database rather than doing the number crunching that it's really good at!
I fully agree with what you say about using the unique Town or Area as the pimary key, though the "short code" I was using seemed "tidier".
I'll be back ... if that'sOK.
Pieter
1 Guest(s)