Turn FOS Dashboard Proof of Concept into MVP

[Br-Johnson]

• [x] #30
• [x] #83
• [x] #57
• [x] #55
• [ ] #78
• [ ] #91

---

Required:

• [ ] Fine tune timing of data pipelines and optimize query performance
• [ ] Ensure refresh schedules are set successive to initial data extraction
• [x] Create separate 'About/How to' report for FOS and add disclaimers
• [x] Add more info to chart subtitles
• [x] Implement ordering to make bycatch last in list (currently named as 'z_bycatch')
• [ ] Link maps. Link to mapping application, if using the same data.
• [x] Ensure all charts are colourblind friendly
• [ ] Ensure all 'year' fields are reporting on 'calendar year' and not 'fishing year'
• [x] Add PFMA to commercial catch filters
• [x] Investigate mapping options more...
• [x] Determine how to show how data has been filtered for a data export (metadata)
• [ ] Implement screen reader aids to power BI components

Desirable:

• [ ] How to incorporate PacHarv data?

[Br-Johnson]

@AnnaD-M I think we should delay starting this next phase by two weeks so that you help contribute to some of the broader regional coordination, especially regarding establishing the Pacific Salmon Data Community of Practice.

[AnnaD-M]

Lakehouse shortcuts @alburycatalina Background:

• Joey set up permissions to enable members of the Pacific Science Sandbox to use a shortcut to connect directly to select lakehouses within the Pacific Salmon Data -Landing workspace.
• This enables Pacific Science Sandbox members to access lakehouse contents without having to copy the data (using a dataflow or otherwise).
• The identity of the calling item's owner (e.g., Joey Seto with full permissions to the Pacific Salmon Data -Landing workspace) is used to authorize access rather than the user's identity (e.g., DSU member that does not have access to the Pacific Salmon Data -Landing workspace).
• A shortcut simplifies access to shared data, enabling users to work with external data as if it were part of their own lakehouse without duplicating it.
• For the Fishery Operations Data Explorer project, the BRONZE_FOS_PROD lakehouse in the Pacific Salmon Data - Landing workspace contains the data copied over from a direct connection to the FOS oracle database.

Impacts: we would just have to set the refresh schedules on the artifacts downstream of the shortcut. Sherry/Peter would be responsible for setting the refresh schedule for the initial data extraction process to the lakehouses within the Pacific Salmon Data -Landing workspace.

Steps:

1. Create a new Lakehouse, e.g., 'shortcut_BRONZE_FOS_PROD'
2. For ‘Get data in your lakehouse’, use ‘New shortcut’
3. Select ‘Internal sources: Microsoft OneLake’
4. Select ‘BRONZE_FOS_PROD’ in the ‘Pacific Salmon Data – Landing’ workspace.
5. Select the tables you need for your workflow, e.g., fishery, catch, opening_gear.
6. Replicate Jason's queries using notebooks
7. Create a new Lakehouse, 'silver_FOS_prod', and direct the tabular outputs of notebooks here.

To follow:

• Delete Joey Seto's demo lakehouse (demo_LH) used to demonstrate this process (check with Sherry first).
• Will need to replicate and re-reference notebooks, semantic models and associated reports.

[AnnaD-M]

Tested the audience configuration for 'entire organization' and confirmed that you can limit the content, and the app can still be seen by any DFO staff member (e.g., only had the 'about' report visible and someone in the Ecosystem Management Branch could view the app).

[AnnaD-M]

Figured out process to display last data update/modification timestamp: For each table in a lakehouse, there is an associated _delta_log directory. Each delta log file corresponds to a change in the table (see screenshot). The ‘nb_bronze_fos_prod_table_last_modified’ notebook reads the delta logs for each table in the BRONZE_FOS_PROD lakehouse and determines the maximum modification time across all tables. It then writes the max modification time to the ‘bronze_fos_prod_max_modification_time’ table in the SILVER_FOS_PROD lakehouse. So, in a roundabout way it captures the timestamp for which the initial data extraction process completed. The 'max modification time' can then be displayed on relevant Power BI reports to that end users know how current the data is. Note that the modification timestamp differs from the refresh timestamp.

[AnnaD-M]

Updates for Nov 18th review with Erika and Jason: • Workaround to dynamically summarise how the data is being filtered. Cannot dynamically update lakehouse tables, and cannot display dynamically generated ‘measures’ in a table visual. Smart narratives and text boxes do not allow you to select, copy and paste text (only the option to ‘share’ the visual). Workaround is to create a DAX table to house the dynamic (filter selection). • Updates/additions to disclaimers, including definition of ‘Protected B’ data. • Workaround to mimic pop up for acknowledgment of disclaimers (using selections and bookmarks). In theory, any DFO computer that has visited the site would have been exposed to the disclaimer and had to click the 'I acknowledge', so this would be a way to see a list of users that have 'acknowledged' the disclaimers. • Added PFMA map. Note that there are some PFMAs that when selected do not return any results, so that list will truncate in the future. • Rearranged selectors/filters to reflect order of importance. • Reordered species column in order of importance based on DFO web content, with bycatch last. • Added tabs for additional information. • Added definition of ‘change over time’. Future updates: • Update data flow diagram to remove lakehouse names and include SQL query replication. Provides end users with a general understanding of how the platform works. • Updates to openings query results (pending completion of Sherry’s review process).

[AnnaD-M]

Updates based on feedback 18 nov 2024

'About' page:

• In Usage Agreement, removed 'all' from 'please read all disclaimers carefully'.
• In 'About', reworded FOS description and updated 'end users' to 'internal staff'.
• Made footnote asterisk red.
• Updated data usage verbiage to remove 'rule of 3'.
• Updated image of salmon to marine life stage.
• Made it a positive statement to revert back to the default viewing state using the 'quick guide' button.
• Moved subheading up in Licence Area Descriptions.
• Removed duplicate link to fishing area maps from Additional Resources.

'Catch Insights' and 'Catch Summary' pages:

• Removed 'dynamic metadata' table. When exporting data from a table, the applied filters are listed at the bottom of the table. This only works when exporting from the app interface, not Power BI Desktop.
• Removed 'bycatch' and limited to show steelhead as the only non-Pacific salmon option.
• Showcased option to use Synoptic Panel map for increased interactivity. Created custom map for PFMAs and used Synoptic Designer (Synoptic Designer for Power BI) to create areas and export to SVG doc. This allows a user to click on a feature on the map to filter all the visuals, but this does not update the filter settings. If you use the slicer/filter, you can then only click on unfiltered features on the map.

Unfulfilled requests:

• The app refresh date (left side of top banner) is a built-in feature that cannot be removed or customized in the configuration settings.

ArcGIS for Power BI plugin:

• ArcGIS for Power BI plug in does not currently allow for filtering of other visuals upon clicking on a feature.
• There appears to be an issue with establishing a join to a feature layer hosted in the DFO enterprise account (likely due to security settings). Joins work fine with feature layers hosted on ArcGIS Online.
• Must be logged in to be able to add multiple layers to the one map visual.