Meeting Prep for 10/16/2023

Previous Next Steps

1. Admin access. WE DON'T NEED THIS.
2. Linked databases. He didn't really comment on this in his reply and as such no progress. Follow up with him on those.
3. Excel spreadsheets w/ calculations etc. We got those.
   1. We started mapping out what databases were required.
   2. Touched on how the queries fit together.

PowerBI Progress

Visualization

• Melissa has a bunch of charts to show in PowerBI.
• They connect to PSQL (Phytos) and Access (Fish).

Queries

• Melissa's done some exploration into building complex queries in Power BI.
• She's successfully migrated at least one (small) query.
• She's also shown how to chain queries together to ultimately visualize the data via chart.
  • Will we even need chained queries, or can we use some other functionality?
  • DAX queries - we haven't explored yet.

R Code

• We've created some R Code to create visualization (Fish DB) through Power BI.

Data Migration

• Reminder: we chose PostgreSQL as the new standard database.
  • This is the same technology behind the Flows DB.
  • Excellent large data support.
  • Support for time series data.
  • Enterprise-level open-source software

Excel - Phytos

• We already touched on Phytos.
• We created a Python script to migrate the data from Excel to PSQL.

Access DB - Fish

• We've started a test migration of a "simple" table from the Fish DB.
• Out-of-the-box migration tool "MS SQL Server Migration Assistant for Access" used to migrate from Access to SQL Server.
• Performed CSV export of single table "TransectSediment&_Depth_Data"
• CSV import into PSQL did not work. Bad parsing (commas in strings)
• Other delimited values "|" made it further, but ultimately errored also.
• Going to explore a more robust custom approach for migrating data (Python scripting, similar to Phytos).
• For proof of concept, this could work well.
• We will need to tweak our strategy anyway once we get an understanding of which tables will migrate and how they might need to change before migrating.
• Existing table definitions probably won't be used as-is due to lack of good relational design practices.

Next Steps and Questions

• How do we start interacting with the other DB experts?
• Foundation for additional POTW (publicly owned treatment works) data? Related to Sudhir's conversation with Reese MSD.
• We want to make sure that we future-proof our design as much as possible.