November Report
- Migration
- Visualizations
- Quality Assurance (QA) and Quality Control (QC)
- Next Steps
- API
- We have explored multiple pathways for data migration:
- Migrate the database from Access to MS SQL Server and then to Postgres.
- This is the preferred path, as it allows for intermediate modifications of the original database, ensuring standardization of table and column names. This process also enables the creation of stored procedures or views from the queries as needed, rather than converting all queries into tables, as can happen with other methods.
- Migrate the Access Databases directly from Access to Postgres using DBeaver.
- Migrate the database from Access to MS SQL Server and then to Postgres.
- Verify data consistency by connecting both the original database and the final database to Power BI, creating identical visualizations, and promptly confirming the results.
- We aim to develop an API for future use, facilitating easy access to the data for custom program development.
- In terms of Quality Assurance (QA) and Quality Control (QC) checks:
- It has come to our attention that some columns in the Access database, which should exclusively contain numeric values, also include non-numeric entries like 'N/A' or non-detect. These values will need to be adjusted to either return null or 0, allowing for numeric calculations such as sum or average on these columns.
- We will employ row count as the primary validation method for tables to ensure that data migration does not result in data loss.
Data Visualizations The ability to use a common Microsoft product to quickly and easily interact with the database is vital for ORSANCO. We are aware that not everyone knows SQL or how to write queries to the database that is the main reason we are championing the use of power bi in this process. Power BI is simple to connect to the Database.