City Data Hub Migration - ATD Datasets

[KaroEngstrom]

Quick Overview

The City is adding an Enterprise Data Platform solution, called the City Data Hub, as an internal hub for data sharing. This includes datasets currently on the Open Data Portal, which will need to be migrated to the new environment. DTS is particularly concerned about the migration of existing scripts working between the ODP and our various other solutions like Knack.

Project Info from CTM

• City Data Hub SharePoint

TIMELINE:

Answers so far from CTM (Janette M.) Your team will still be able to use the Socrata Open Data API. Both the Open Data Portal and City Data Hub sites will be on the same system architecture. Data can be federated from the City Data Hub site to the Open Data Portal. Here is the link to Tyler Technologies' developer page: Socrata Developers | Socrata, and the link to their Knowledge Base: Data & Insights Client Center (socrata.com) The City Data Hub will be an open data management platform (not an application development platform) for internal collaboration with data. That is to say there won't be independent development/staging/prod environments for custom programming efforts. The Data Hub will be launched in March 2023 after activities are complete: training, user onboarding, migration, UAT . The vendor will be leading the data automations phase for identification and transference of the various custom ETL connections. This will begin during the migration phase in January 2023. The Open Data team doesn't support the API or custom data automations for departments. But your team will get assistance from Tyler Technologies at that time. To get ahead of it, I suggest that your team creates an inventory of all the different connections that you want re-established. This will help you when you begin to work with the vendor on reconnecting everything.

@johnclary questions:

• will our dataset URLs or resource identifiers change?
• will any of the API behavior/operations change?
• will our API access tokens be affected?
• will our data flow from the EDP to our existing open datasets automatically?
• will we be able to preview / test the new system before our existing datasets are migrated?

@KaroEngstrom is communicating with CTM as the Open Data Liaison working on the metadata cleanup (last quarter of 2022), and will pass on to the dev team any info that may come up in those interactions.

cc: @frankhereford | @amenity

T 10/11/22

• submitted a question about joins to the CTM Team - supported in the City Data Hub, will they also be supported in the ODP as data is federated from the City Data Hub/EDP? ANSWER: tbd

[KaroEngstrom]

M 10/31/22

• Asked CTM for further info on the following last week:

Question - Can you clarify for me if the City Data Hub fully replaces the previous Open Data Portal, or will data go through the City Data Hub and into the ODP instance? I’ve had a lot of concerns raised by our internal users who draw data from the ODP for their PowerBI dashboards. Will they have to reconnect and possibly rebuild those dashboards?

Answer: The City Data Hub will be the new site employees will use to manage their open data instead of the Open Data Portal. However, the ODP is not going away because it is for the community to access the City's data. Employees will be able to decide whether or not to federate data from the internal City Data Hub site to the external-facing ODP.

During the user training/migration period in Jan./Feb., you will be able to onboard to the new platform during the live education courses. These courses will occur the week before a department cohort's migration. More details about that next phase of the project will be emailed to everyone soon from the vendor's PM (Debbie F.).

Question - To create this inventory of connections in preparation of working with the vendor as you suggest, we have the following questions: Will any of the API behavior or operations change? Will our API access tokens be affected? I realize there won’t be a dedicated staging environment, but will we be able to preview or test the new system before our existing datasets are migrated?

Answer: For your other ETL-based questions, I will forward them on to the vendor's project team to review and write-up responses since they will be leading the ETL re-establishment effort.

[KaroEngstrom]

W 11/2/22 - Notes from Tyler Tech's focus group yesterday on Users and Teams

• The meeting was about Roles, which the company envisions being a major organizing principle of user permissions in the new system.

• The session focused specifically on what the Open Data Liaison's role (read: permissions and abilities) is in the City Data Hub.

  

• My issue with adding more rights to the "Roled User" (in this case, the Open Data Liaison) is that TT cannot break down the access to items by department. All role-based rights are domain-wide, meaning access to every department's datasets/assets, and I voiced my concern about this in the meeting. This got support from other depts and some CTM representatives. I believe Open Data Liaison/department oversight level rights and permissions are better managed through asset-specific access (for us, this would mean adding our service account as an owner for ATD assets).

Slide on the EDP to ODP Structure

• Source - any source of data, incl. Knack and not just abaci)
• EDP - Enterprise Data Portal (name of TT solution) = City Data Hub (COA's specific term for this internal sharing platform)
• ODP - Open Data Portal as we know it, except note that private assets would no longer live on the ODP. Anything private would remain on the EDP, until published to internal in EDP and then federated into the ODP as public.

[KaroEngstrom]

F 11/4/22 Update - Mondara Maps and Meeting with Vendor

Mondara Maps

• Context: This issue was raised at dataset review for migration but I'm including it here because it involves tech debt/work the vendor will be doing and we need to stay on top of. Specifically, SMB has identified three maps that CTM had marked as Audit Assets to Review, meaning they are in a deprecated Mondara format and cannot be migrated as is. I asked CTM for assistance on determining how to keep these maps, and here's the response.

[These maps were] published through an automated feature called ESRI Connector which is an admin only setup (uses AGOL as the source). It came up in our "unknown" list (the department metadata field didn't have a dept selected) so I can mark those for your team as not to delete.

[For background], most maps on the site were created using their older technology. And they were published either manually or through the ESRI Connector mentioned above. They did a product-wide upgrade in 2019 so maps created from that point on uses their new map model. It's fairly different where it has a separate table with some additional fields and a map visualization asset to maintain (we're still adjusting and reviewing this map implementation). The issue with migration is that in order to maintain the unique 4x4 ID of the individual map, [vendor] needs to be involved and will rebuild these for us. All maps could technically be rebuilt by staff or our team but then any references to the original 4x4 ID would be lost. Admittedly, I don't know how this transition would affect apps and scripts that reference these and if it will be seamless. That's something [vendor] will need to provide consultation per dept needs.

• Next steps: We will mark those maps as Approved (do not delete) and I'll communicate the above to SMB. Elated to hear vendor will do the rebuild work for us. We will need to keep this in mind for meeting with vendor and for migration itself (awareness of how this will work and communicating changes, if any, to users).

Planned Meeting with Vendor

• Context: our ongoing concern about the automations in place when assets are moved to EDP and then federated back to ODP. As part of ongoing thread with CTM, I sent the below questions. I can tell from the vendor answers that the context of scripts INTO ODP was lost (my bad), but a meeting is nevertheless the absolute best way forward to scope the effort and steps needed for our unique setup.

• Next steps: Our CTM liaison is working to schedule a meeting with the vendor for the week of 11/14/22. I've shared are participant list with her and we can forward invitation as needed when available.

[KaroEngstrom]

Conversation at dev sync 11/10/22

• Miro board
• Popped into the dev sync to share the Miro board and what has been accomplished so far in preparation for next week

Questions:

• How to extend our ETL to push a dataset into production (ODP)?
• Do we need to change code?
• How does authentication work on the internal platform?
• Can private settings serve as a sandbox of sorts for the datasets?

Meeting with Vendor

Scheduled for Friday, November 18, 2022 from 11:00am to 11:30am

[KaroEngstrom]

F 11/18/22 Meeting with Vendor - Notes

Attendance:

• CTM: Janette, Marbenn, Brad
• Vendor: Debbie (PM); Abigail (Technical Consultant)
• DTS: Chia, John, Frank, Charlie, Diana, Karo

Items discussed:

• Datasets that are private or internal will be the biggest change (4x4s and URLs will change)

• For these private ones, will need to use the source dataset - will no longer be on the ODP

• How do people authenticate in PowerBI, and will that change? A: can supply a Socrata login and PowerBi is pretty smart

• EDP will likely be factored in SSO and all authentication should remain the same

• Anything currently external can continue to functions as is - will also be on the EDP

• All 4x4s and URLs will stay the same on the ODP side

Frank - new open dataset - how will it be pushed now?

• Any data that is being updated will originate on the EDP - any endpoints will also need to be updated
• Updating ETL after migration will need to happen
• We'll be making those small changes to internal ETLs

Frank - How does automation transfer over from EDP?

• Federation enabled will do this for you from the EDP
• As soon as updates are made on the EDP, they'll immediately be reflected on the ODP

Diana - what about fast clip data pushes?

• Federation is instantaneous

John - Publishing to EDP and then to ODP

• Will we be publishing to different end point?
• Yes, 4x4s should remain the same but the domain is different
• This would be a significant code change; two domains with the same 4x4 is okay, but two 4x4s would be too much
• vendor says these will remain identical

John - will access tokens for ETL be affected?

• meaning both API token and API key, for clarification
• Abigail will investigate further and get clarification
• methods available on the API will not change

John - all functionality via the API will pretty much say the same?

• vendor: yes

Frank - Joins

• there's documentation on it from TT

Charlie - private datasets will not be on ODP anymore but are ones we would use for testing; would these now be on EDP?

• will remain on the EDP

Frank - staging environment?

• usually isn't set up like that; we set it up and test

John - talking about nearly 50 ETL processes and it's a big ask to test that on the fly; we've been trying to scope this

• concern over just flipping a switch
• the ETL is run as scheduled tasks on servers
• having a testing end point would be really helpful
• vendor: URL is ready and it can be tested there, it's not a secret; maybe could move over one dataset as a test item to see the speed and effectiveness of that, in the short term
• Yes, this would be great, even a dummy dataset would work great for this
• this means DTS would need to have access to the internal site now (Janette says this is fine)

Diana - what about geospatial connectors?

• they'll just get moved over
• Abigail will double-check to ensure those move over

Chia - move question - actual location of the datasets?

• EDP already connected, just empty right now; can already push from EDP to ODP

ACTION ITEM: Karo will share the training portal link and links to trainings and documentation, especially on joins

[dianamartin]

11/18 - Diana Notes

Attendees:

• DTS: Karo, Diana, John, Frank, Chia, Charlie,
• CTM: Janette, Marbenn, Brad,
• Tyler Tech: Abigail, Debbie

Karo showed the City Data Hub Migration Map

Questions:

• Secondary: How is that data drawn to our EDP to PowerBI?

Discussion

• Biggest point in changes are from datasets that are internal and private
• Public datasets can continue to function the exactly the same way. The ODP is the same point as the EDP.
• Private datasets - will need to change in PowerBI, what the source is
• Re-pointing the source dataset, to get the new URL

Authentication to PowerBI

• supply Socrata credentials

John asked: SOAP will be supported? Yes

Frank’s Q1: Socrata API - pointing to ODP or EDP?

• Any data that is being updated will now originate to EDP
• All endpoints will all need to update their ETLS

Frank Q2: How will it be moved to ODP to EDP?

• Do we need to do that data clone process? Will have federation enabled
• Set up as soon as updates to EDP to ODP

4x4s - don’t change

• unique ID: 4 characters-4 characters

Different end point? Yes

Domain is different - URL changes

• significant code change
• managing

EDP federation is real-time, mirrored

2-5 minute updates frequency

SOAP Access tokens, will those be affected? API Tokens or API keys? both

• Tyler Tech needs to find out this response (according to Abigail)

Frank said: he imagines the methods available to the APIs should be the same

John wanted to confirm that API, truncates/modify

• all going to remain the same? Yes

Education cohort - education courses and forward more technical documentation

• Debbie will forward resources links to Frank
• On Demand, Live courses

Charlie Q1: Private datasets, not on ODP. We do have private datasets for testing. Making sure we’re not deleting or duplicating records.

• Debbie says: keep it
• Keep using in EDP environment

Frank Q3: Do you all have/envision a staging environment, to get ahead of everything? or will we have to wait?

• Debbie, no staging environment
• Matter of repointing, getting URL or getting new API key
• Best to do in actual environment
• 40 ETL processes
  • Big switch for us

Waiting to scope the level of effort

• concerned to flip a switch and works
• Debbie asks how we run ETLs
• We have software running to manage those tasks

We have URL already, test connection there

• great candidate for ETL
• Open data hub (longer term)
• URL is not secret

Move one of them over and see how it’ll work

• test one, test dataset
• have a dataset to move over
  • Send chat/email/ URL - they can move it over
  • internal site: CTM to add this team to the environment now

Geospatial datasets - EDP to ODP

• more map friendly
• double check on the connectors for the migration

Chia Q1: moving to?

• Moving to private to EDP → to ODP

Debbie, no assets will be on the ODP

• everything is moved over

Action Items

• Tyler Tech says after Thanksgiving timeline is preferred to send them the dummy dataset
• [x] Huddle as a group to figure out which new dataset to create
• [x] Send dummy dataset to Tyler Tech

[KaroEngstrom]

Vendor Documentation and Training Resources

• Training Portal
• Enterprise Data Portal (EDP/City Data Hub) Knowledge Base home

Joins:

Knowledge Base

• SoQL Join Examples
• Shape and Join Data with the SoQL Query Editor on the Enterprise Data Platform

Training Session (On Demand)

• Shape & Join Data with SoQL Query Editor - OnDemand - requires login; anyone having access issue, lmk!

Data Federation:

• Knowledge Base Articles

[dianamartin]

Post Meeting Notes

• Great to ask about federation is already on
• Training posted on this issue

Which dataset?

• signals?
• dms | digital message signs?

[frankhereford]

👋 @KaroEngstrom - Thanks again for setting up the meeting between us, CTM & TT. I want to check in and see if I can help us take full advantage of that leg-up they offered: testing a dataset's migration beforehand.

We had discussed working with them to get a dataset created, copied or otherwise designated to be the test subject to take through the paces of the upgrade beforehand. I wanted to check in with you to see where that was, and even more, to offer to to help in the effort while we still have some time to do this at our own pace. I was wondering, is there an issue to track that specific testing? Should we create one if not, so we can assign folks with a scoped out task to get that done? I'm very happy to be that person, too! LMK if i can help or if you'd like to discuss this!

Thank you!!

[KaroEngstrom]

M 11/28/22 - Updates and Notes

Migration Day - January 17, 2023

• From Debbie (vendor):

Testing Dataset

• Apps Teams will discuss in App Sync today (11/28/22)
• Then connect with the dev team

[dianamartin]

11/28

Apps Team said that the "Dynamic Messaging Signs (DMS)" data set would be a good candidate data set.

• [x] Karo will create a copy of this dataset
• [x] Rename data set Dynamic Messaging Signs (DMS) - Hub Migration DTS TEST
• [x] Make data set "Private"
• [x] Send to Debbie F. (4x4 and URL) to push to EDP for testing

[KaroEngstrom]

M 11/28/22

• downloaded and re-uploaded dataset as outlined above - Dynamic Messaging Signs (DMS) - Hub Migration DTS TEST
• dataset identifier/4x4: igcg-mzv6
• updated the column types
• unable to publish as "private" so leaving in draft status (the ODP does refer to this as a 'private dataset')
• creating a new issue for the testing

[KaroEngstrom]

Answers to Outstanding Questions from Meeting

• Will access tokens for ETL be affected by the migration? Answer: You should not notice any change.
• Will geospatial connectors move over as they are? Answer: Yes. They should transfer with the migration.

CC: @frankhereford @johnclary

[KaroEngstrom]

F 1/13/23 I'm closing this issue. The ETL process is tracked in #11153