Announcing LDSql

[RossEvans]

I have now done this. The Export (MLS) article has updated details regarding the stake export files. It still has the same warnings that it is unofficial and simply the result of analysis. But the stake files are not that different from the ward files, so I'm pretty confident that the stake file documentation is as good as the ward file documentation.

Thank you for doing that. I have a couple of followup questions about the content of the stake files.

1) With regard to the "Indiv ID" field of Membership.csv, Organization.csv and WardOrganization.csv, is the value of "Indiv ID" assigned by the stake MLS system, or is it somehow transmitted from the corresponding value of "Indiv ID" used in the ward export files?

2) Similarly, is the value of "HofH ID" in Membership.csv assigned by the stake MLS software, or does it use the value from ward MLS?

This is quite significant, because in the ward exports (and thus in the schema I developed for LDSQLite), the 'Indiv ID" field and the "HofH ID" fields are the natural primary and foreign keys for several tables. If those values are originated locally in stake MLS, they also would be unique within the scope of a stake. But if they are derived from ward MLS systems, they would only be unique when combined with Unit Number.

3) Do I understand correctly that the Unit Number field is integer digits, with no leading zeros? That is what the Wiki implies, so I assume it is correct. But it doesn't hurt to reconfirm. And I have seen some other applications in which a leading zero is applied.

[russellhltn]

As a footnote, even the Membership.csv file in the stake export still has things I don't fully understand. This thread in another forum (the support group for Ward Tools, another third-party app dependent on the undocumented MLS files) demonstrates that some stake users have discovered anomalies in the content of the Unit Number field. Read the comments near the end of that thread.

Out of unit members. Hmmmm, that could give rise to some special situations.

- A "out of unit member" record is created in "Unit A", but the actual records are in "Unit B" - what should the stake MLS export show? Two records for the same person - one in each ward? Or one record - but for what unit (Unit A, Unit B, or the stake)?

- What if the "Out of unit member" is from another stake?

- What if information is incomplete in the "Out of unit" member record?

- What if the member subsequently moves out of "Unit B", but still has a "Out of Unit" record in "Unit A"? What is the results if the export is done at any point in this process?

[aebrown]

Out of unit members. Hmmmm, that could give rise to some special situations.

Interesting questions, indeed, but unfortunately they can't be answered with the test data, because there is no way to get an out of unit member into the stake membership data. You can only create an out of unit member at the ward level, which then presumably gets transmitted to the stake. But the transmission mechanism is disabled for test data.

[aebrown]

1) With regard to the "Indiv ID" field of Membership.csv, Organization.csv and WardOrganization.csv, is the value of "Indiv ID" assigned by the stake MLS system, or is it somehow transmitted from the corresponding value of "Indiv ID" used in the ward export files?

2) Similarly, is the value of "HofH ID" in Membership.csv assigned by the stake MLS software, or does it use the value from ward MLS?

I'm 99.99% sure that the answer to both of these is that the IDs are assigned by the Stake MLS software, independent of the ID values in Ward MLS.

In the test data, the IDs are the same in both Ward and Stake MLS, but that can't be true in general. If the value were generated by Ward MLS, then transmitted to the stake, there would most certainly be collisions, thus requiring the use of the unit to create a unique key.

But I don't see any collisions in my stake's data, and the IDs for about 3000 members range from about 212000 to 219000. To be 100% sure I'd have to go to one of the wards and do an export, but the probability of 3000 IDs having no collisions in a range of 7000 when they were generated independently in 7 different wards has to be so close to 0 that I feel pretty safe in concluding that they are generated in Stake MLS.

So one could not link ward and stake export files using these IDs, but you should be safe within the realm of stake export files using only the Indiv ID and HoH ID as unique keys, just as you can do in the realm of a collection of ward export files.

3) Do I understand correctly that the Unit Number field is integer digits, with no leading zeros? That is what the Wiki implies, so I assume it is correct. But it doesn't hurt to reconfirm. And I have seen some other applications in which a leading zero is applied.

Yes, that is correct. The test unit for wards is 108, and it appears in the export files simply as "108", not "0000108".

[russellhltn]

unfortunately they can't be answered with the test data

Right. Which leaves this to those of us with access to stake data to figure it out. It helps if you have a singles or student ward in the boundaries.

I'll go out on a limb and guess that the stake unit number shows up in the stake membership CSV when a member is a part of more than one unit. I should be able to test that theory.

[RossEvans]

Right. Which leaves this to those of us with access to stake data to figure it out. It helps if you have a singles or student ward in the boundaries.

I'll go out on a limb and guess that the stake unit number shows up in the stake membership CSV when a member is a part of more than one unit. I should be able to test that theory.

Some commenters in the Ward Tools thread, stake MLS users themselves, seemed to think the situation had something to do with the timing of move-ins and/or move-outs -- perhaps when stake MLS gets updated before the update is finally applied in the ward MLS databases.

[RossEvans]

I'm 99.99% sure that the answer to both of these is that the IDs are assigned by the Stake MLS software, independent of the ID values in Ward MLS. "

Thanks. That analysis seems solid enough that this derived fact should be written into the verbiage of the Wiki.

But I don't see any collisions in my stake's data, and the IDs for about 3000 members range from about 212000 to 219000.

That's interesting. In ward MLS, the "Indiv Id" and "HofH ID" values apparently just start incrementing at a very small integer, initialized at the time MLS was first installed. (The lowest value in my ward today for each field is 11.) So unless your stake started using MLS many years before MLS was invented and had a couple of hundred thousand move-outs, which seems impossible, stake MLS initializes its incrementation of IDs at a very large number.

[aebrown]

That's interesting. In ward MLS, the "Indiv Id" and "HofH ID" values apparently just start incrementing at a very small integer, initialized at the time MLS was first installed. (The lowest value in my ward today for each field is 11.) So unless your stake started using MLS many years before MLS was invented and had a couple of hundred thousand move-outs, which seems impossible, stake MLS initializes its incrementation of IDs at a very large number.

I agree that the ID values just start incrementing at a very small integer (I'm pretty sure that integer is "1"). But I don't think Stake MLS is any different from Ward MLS in this regard.

I have another theory about how a stake would have such high ID values, related to the way Stake MLS processes data. The first part of the theory I am pretty confident about:

• Whenever a stake does a Request Unit Refresh Data, the membership data is imported into the stake from the wards, and all Indiv and HoH IDs are recalculated, but the starting value for those IDs continues to auto-increment from where it left off.

Although we've done a few unit refreshes, that postulate alone does not seem to explain why our 3000 members would be distributed across 7000 ID values -- we haven't had 4000 move-ins/move-outs since our last refresh (our stake is relatively stable).

Another interesting point is that there is one gap of 2300 IDs somewhere in the series of IDs. Perhaps a ward requesting a refresh can also cause that ward's members to get a new set of Indiv IDs. That part of the theory would be harder to test.

But as fun as this speculation may be, it's only speculation and probably doesn't make much difference in the grand scheme of things.

[RossEvans]

But as fun as this speculation may be, it's only speculation and probably doesn't make much difference in the grand scheme of things.

What is significant to users of the export data is that if the ID values do reset upon a unit refresh, that would affect any applications that depend on "Indiv ID" or "HofH ID" as persistent values, for such purposes as linking to external tables of user notes or photos.

So if we are confident of that fact, it should probably be in the Wiki.

(Of course, all these things would be so much better documented by the people who really know, the MLS development team.)

[aebrown]

What is significant to users of the export data is that if the ID values do reset upon a unit refresh, that would affect any applications that depend on "Indiv ID" or "HofH ID" as persistent values, for such purposes as linking to external tables of user notes or photos.

So if we are confident of that fact, it should probably be in the Wiki.

(Of course, all these things would be so much better documented by the people who really know, the MLS development team.)

That's a good point. But upon further reflection, I'm not sufficiently confident of my theory to document it. Sometime in the next month or two, I think our stake will request another refresh, so I'll be able to test the theory then.