search

bcgov / foi-flow

Freedom of Information modernization
Apache License 2.0
5 stars 3 forks source link

Research and Discussion: AXIS Database Analysis, ETL Feasibility #181

Closed mpilchar closed 3 years ago

mpilchar commented 3 years ago

Title of ticket: Research and Discussion: AXIS integration

Description

Summarize issue

Dependencies

Dependency tagged - Dev Access managed by IMB - timetable for access to be confirmed this Friday

DOD

m-prodan commented 3 years ago

@abin-aot - IMB advised we can expect access to 10.7 by EOD Wednesday - will report back if I hear anything different!

abin-aot commented 3 years ago

Received DB access on 17/AUG via my _A account. Can see tables now. But waiting for API details from vendor - @lmullane @m-prodan

abin-aot commented 3 years ago

On 30/Aug/2021 , as discussed on Sprint Meeting, splitting this task to two. API Integration analysis will be on other task and will start working on it once we get access to AXIS API

abin-aot commented 3 years ago

AXIS DB analysed : ATIPD on IXIAS\CIRMOTST

Tables are primarily store Request information :

TEST Data created on 10.7 version of AXIS Dev Deployment, refer link below http://dev.axis.gov.bc.ca/ATIPXpress/Request/Correspondence.aspx?**RequestId**=**4217**

MS SQL Master Query to find the Table, Column info. select t.name,c.name from sys.tables t join sys.columns c on c.object_id = t.object_id where t.name like '%req%'and c.name like '%desc%'

select t.name,c.name from sys.tables t join sys.columns c on c.object_id = t.object_id where c.name like '%desc%'

Analysis Summary :

  1. Data inside AXIS DB is stored in a straight forward as per the table listed above and can be fetched easily and transformed, transported to any other datasources/datastores.
  2. Documents uploaded into AXIS NEEDs to be tracked, not sure where it is getting saved.