How to combine Ecoinvent Unit (UP) and System (LCI) datasets without loosing processes?

Question

This is a follow on to https://ask.openlca.org/1690/importing-some-unit-processes-missing-in-combined-database as Jonas answered that fully (the "missing" processes had the same UUIDs as some already present) but this clarifies the underlying question and might be more helpful.

Datasets (e.g. as .zolca files) can be combined - e.g. by restoring the larger one and then importing the smaller one into it. If some of the processes in the second dataset have the same UUID (identifier) as processes in the first dataset, then they won't be imported. This works and makes sense.

However, some processes in the Ecoinvent 3.5 unit-processes (UP) dataset have the same UUID as their equivalent process in the Ecoinvent 3.5 system-processes (LCI) dataset (I've only been using cut-off). So, when importing the unit-processes dataset into the system-processes dataset, these unit-processes do not get imported.

So, my real question is, what is my best option to use these two datasets (unit and system) together? This seems to be a common thing to do as it enables more transparency of (unit-)processes that are key to a product system while using system-processes as "outer" providers to keep it all computationally efficient. 

Any help in finding a solution would be really appreciated! Many thanks.

Answer 1

Not sure if I missed something but as far as I can see, equivalent processes in ecoinvent UP/LCI have different UUIDs e.g.:

apple production | apple | Cutoff, U - CN: de20bbdb-364a-30c9-8ee3-14fd22173a80

apple production | apple | Cutoff, S - CN: de4cf6f5-3765-3ae7-a81a-9d6774f777a1

Therefore, you can simply import LCI processes in the UP-based version of ecoinvent.

If for some reason, the UUIDs are the same, you can also assign a new UUID (although this is not really recommended as the chance to cause some problems is probably higher than to actually solve a problem). Maybe you can do this with LCI processes that you want to import as they do not really have links anyway but do not do it with unit processes as the links won't work anymore and definitely create a backup of your database before. And again, this is experimental and not really a recommended practice. Be extremely careful.

Window -> Devloper tools -> SQL

UPDATE TBL_PROCESSES SET REF_ID='NEW UUID' WHERE REF_ID='OLD UUID';

Click on the run button in the top-right corner. Done

Generate UUIDs e.g. via Excel or https://www.uuidgenerator.net/.

Comments

Thanks again - really appreciate you looking at this.
For many of the processes, they are different. This is why I didn't spot this immediately (and possibly why others haven't asked about it either).
If you look at other processes they are the same.  e.g. check out  A03 Fishing and aquaculture where they all seem to be the same, e.g.

fishmeal and fish oil plant construction and maintenance | fishmeal plant | Cutoff, S: cc1ccd88-8818-3855-a8e5-43b7f76f2232


Actually, I wonder whether these are the ones that were added in 3.4 -> 3.5 and so only one UUID was created or something?
If I've done something silly and in fact they are all different, then that would be amazing!

---

In fact, if it is limited to just the processes that were added in 3.4 -> 3.5 then that would be annoying but not so bad - for those ones I could just import a copy of the relevant unit process as needed. It is not knowing the size of the potential issue that causes concern when, e.g. working with colleagues, it's less acceptable to say that "some" (undefined number of) processes will be missing from the combined dataset.

---

I've added something to my answer. Maybe that helps?

---

Thanks - that is potentially helpful and I agree much more sensible to do it with the LCI processes. I will have a play with this but I guess I am still concerned that any future updates would break compatibility.
Please could you ask whether this could be considered for future releases of (Nexus version of) Ecoinvent? i.e. changing the system process UUID's if they are duplicates of unit process UUID's?

---

*much more sensible to do it with the UP!

Maybe keep track of your changes and the original UUIDs. Yes, I will keep track. I remember that we did consider to publish a version where the UUIDs are the same and one where the UUIDs are different ... however, apparently we did not find the time yet to do so.

---

Thanks - that would be great!

I still don't understand why GD would deliberately publish one where some of the UUIDs are the same?

In the "Ecoinvent 3.3 in open LCA"manual (section 2.5) it even states that some UUIDs were changed to ensure that they were all unique.
In the Ecoinvent 3.5 manual (i.e. one you linked to), it states that the six databases can be used together (p3). In the "migrating to ecoinvent 3.5" part of that manual (i.e. the bit about UUIDs being the same), it states that the UUIDs were syncronised between versions of ecoinvent (i.e. so I'd expect each to be unique within a particular version).
Clearly there was also work involved in synchronising the UUIDs of the same process / flow the same between different versions of Ecoinvent. That is excellent, but a completely different issue (as far as I see).
The manual then goes on to say that unit processes in previous versions that were turned into system processes, now have different UUIDs. Again, this makes sense if you're keeping the UUIDs unique within each version of Ecoinvent but is not consistent with having duplicated UUIDs.

Without detracting from the amazing product and excellent work that you've got here, I do feel that a mistake has been made somewhere and express my hope that a future version of Nexus Ecoinvent will have unique UUIDs for all processes.

---

Hi Sam, both "options" have advantages and disadvantages. However, you are right with pointing out that we 1) did not follow a consistent approach in the past and 2) even within our ecoinvent 3.5 version our approach is not fully consistent which is not on purpose. We do plan to publish a corrected version but we do not have a concrete time line (unfortunately) as other work keeps us busy.

---

Hi Jonas, Thanks.
My point was not that the approach wasn't consistent in the past (I think it was), just that it didn't help me understand why this would be deliberate - I honestly can't think of an advantage to duplicating UUIDs within a version of Ecoinvent, but that doesn't really matter.
Anyways, I appreciate your time and thanks for answering - I realise you're all very busy but it is great to know that a version with unique UUIDs might happen, even without a timeline. Keep up the great work!