Identify data mapping platform

[rebekkabry]

Originally I started to build the mock MVP in Excel with a Visio Visualization add-in.

Pros: Common application used across the department Integrates well with PBI and other integrated applications.

Cons: The Visio Add-in has a limit of 100 columns for mapping. Currently the mapping will need at least 400 columns. Additional columns will require subscription, not approved.

Deciding to move forward with the build out in Excel / Power BI. Add-ins will be needed within Power BI to accommodate the interactive and hierarchy features. Hierarchy Slicer

Here a few of the app visualizers that will be considered for the MVP:

• Social Network Graph - most up-to-date looking visual option
• Hierarchy Chart by Akvelon - collapsible options
• Organization Chart - collapsible options

[rebekkabry]

Solution: Power BI Organizational Hierarchy or Chart of Accounts with Parent child functions in DAX Good references: Wordpress Solution

Cons: Does not appear to have alternative visualization options, other than collapsible dropdown. Pros: Utilized a current city wide application, no additional procurement needed.

[rebekkabry]

Currently building out 2 structure options for the framework (excluding any Knack build out requirements)

**Goal**: Compile all assets into a list, list will be assigned IDs.

**Question:** For a mostly dynamic and automated map, would be it better to build a living list contained with individual lists created via queries or static list and mini created listed via queries?

Option1 Build static list with static levels, Build query based list with available "living sources" = 2 lists > 1 masterlist Option2 Build the entire list from individual datasets for each level = 10+ lists > 1 masterlist

"Living" - the data is automatically updated via current operational structures. These are also the most likely to have changes by the various data and stakeholder users.

Knack Consideration

• How did you see Knack Supporting this tool?

• Can we look at a knack buildable table option?

• Could Knack be used to house these tables.

• Will it keep the previous ID relationship when new IDs are added?

  ...you can have relationships btw your records, you have multi or single, etc. 
  ...it will automatically generate IDs for you. 

• [x] Schedule a quick review and question session with Knack expert (Diana, Chrispin and/or Karo)

[rebekkabry]

• getting multi-relationship errors - further investigation in progress

• Example: When attempting to map Manager to multiple divisions, error. The value '123' in 'Sheet3 1'[Parent] must also exist in 'Sheet3 1'[ID]. Please add the missing data and try again.

• 

[rebekkabry]

Visio Model

• allows for multi parent/child relationship
• limit at 100 columns
• Downloading free trial, preparing to out of pocket pay for $5 or $11 monthly sub to complete MVP

Challenges with Other Platforms

Miro Example: Top: Separated Below: Single Line

• Separated columns for each parent/child multiple record (visualization works)

• Single Column

Visio Example:

• Single Line

PBI Example:

• Doesn’t allow single line, will require additional tables for records

[rebekkabry]

• Investigating star/snowflake schema supported by Power BI and other visualization tools.

[rebekkabry]

Star Schema Work Sessions

• Worked with Charlie to investigate with type of schema would best support the current design/future dynamic desires for the ecosystem mapping structure.
• Investigating star concept., needs to consider:
  • What is the most granular level of the mapping assets?
  • Once determined, what columns do we need/have/lookuptables for each item in the hierarchy list?
• [x] Upcoming meeting with Diana, Charlie, Amenity and Rebekka to review schema and discuss Knack as potential application to support project lifecycle.
  • Meeting Notes:
    • Diana agreed that Knack could support most of the functionality desired for the map including many-to-many relationships, handling additional IDs, creating multiple lookup tables to support the array of metadata for each asset group, ability to provide status reports of researches and script status.
    • Recommendation to work with App Experts and start building MVP in production sand box. Test concept and functionality.

Schema to Single Line Draw Out

• Reviewing how the investigating schemas align with the hierarchy levels and single line collapse (desired outcome of project)

PROS about this framework:

• ability to map simple and complex data pathways
• ability to collect and show variations of metadata for nodes across the pathways (changes to divisions, additional stakeholders, etc)
• most of the nodes are built on json enabled and easily "automated" for refresh needs.

[rebekkabry]

Issue:13802 - Currently working with Karo on testing Knack's ability to house the Data Eco-system.

[rebekkabry]

Discussion on Knack DTS Objects:

• Charlie, Diana, Karo, Rebekka

My thought is that we can hopefully allow non-primary instances to reference other instances.

My understanding is that currently primary instances are the only instances that can have related instances:

We want non-primary instances to also be able to have their own child records as well that way we can see where the data flows just by looking at the relationships

• However, in this example.. I believe it’s already doing what you want.

This dataset “Markings Work Orders” origins from a Knack object table

• Script pushed to AGOL ArcGIS Online (AGOL)
• Creates a “feature service” (a spatial dataset)
• This “feature service (this new dataset), is used in a web map application product

This current structure shows that. Because we made the (spatial data set = feature service) and instance, we can choose it as a Source instance thus allowing us to say what application it’s in. Those applications can be a PowerBi dashboard or other custom product. We’d just have to add it to the “Host Application’ list.

In addition to the current relationship structure, we will use to the test environment to review

• A standard where all primary assets have its non-primary and metadata associated; a standard format and form fields that ensure all assets have records associated. • Having all associated data path locations reflected. Example: Specific data/link to - Script pushed to AGOL ArcGIS Online (AGOL) referenced in the Details of Dataset Instances table. • Areas where alerts would be beneficial (Airflow, new datasets, etc.) • Tagging instances with "types". Potentially add columns to reference pathway location/level.

[rebekkabry]

Moving forward with Knack. Closing this ticket. Work will continue to be tracked in Issue 13802.