Automate SMD Power BI Sign Data Refreshes

[Charlie-Henry]

Susanne's signs and markings dashboards have a few datasets that are stored locally and accessed by the Power BI gateway on her laptop. This requires her laptop to be on for Power BI to access and refresh the dashboard. We'd like to see if it's possible to move this data to the cloud or move to a different gateway that is online all the time.

Reference: [Power BI] Monthly 311 Reporting in SMD dashboard using ODP #12379

[susannegov]

Signs_Markings_Work_Orders Data Sources

Most of the GIS data sources for this report live as an excel file since Power BI connecting to Oracle GISMAINT SDE only pulls from the last Thursday of the data when I last attempted it (I think it had something to do with versioning). This makes it difficult if Markings GIS QA is not up to date, or if we need to report work done for that week.

signs markings materials/specs come from AGOL which is also converted to a excel file through FME Workbench. I've requested to have it on Socrata so Power BI wouldn't be pull from excel anymore. See github issue https://github.com/cityofaustin/atd-data-tech/issues/11312

I've been using FME Workbench as an ETL tool to convert the feature layer to excel since it provided the most up to date info.

Would like to have the GIS data on Open Data Portal that Power BI can pull from instead of using an ETL process to convert them to excel files for Power BI.

CSR reporting is from an email of CSR % completed on time from 311. Someone at SMD copy/paste numbers on the email to the spreadsheet at the end of every month.

File path to excel file is on G drive: G:\ATD\Signs_and_Markings\MARKINGS\Markings_Analysis\Dashboard Data Excel file (spec/materials): Signs_Markings_work_orders Excel file (gis markings assets): Markings_Dates Excel file (CSR reporting): CSR_reporting_all

Signs Markings Decision Tree Data Sources

These are GIS layers of markings assets with equity zones information provided using spatial analysis. It lives in a file geodatabase, but it could be added to GISMAINT as an alternative option. They are also in an excel file for Power BI to pull. File path: G:\ATD\Signs_and_Markings\MISC_PROJECTS\Maintenance_Plan_Signs_and_Markings\Decision_Tree

Automatic Refreshing

On-premise data gateway is what Power BI uses to provide automatic refresh. However the device where the gateway is stored in has to always be online. https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

Lance B. has access to an enterprise gateway set on a machine that is an option to do automatic refreshing. I sent an email to him 3/07 and he has given me access to the enterprise gateway. It's called ATMS PBI Gateway.

Would like to fix some of the ETL issues with GIS feature layers before tackling PBI automatic refresh. Since a lot of retrieving the information in GIS is pretty manual already.

[Charlie-Henry]

The current geo data pusher pushes data from GISMAINT to GIS DM every week for the we'd like it to be daily. Will ask @andrewshensky if it's possible to update the GIS DM more frequently like once per day.

For: Feature layers: markings_short_line, markings_specialty_line, markings_specialty_points Tables: markings_short_line_dates, markings_specialty_line_dates, markings_specialty_points_dates

[susannegov]

This is an example of an attachment 311 sends to people: Monthly Summary Report - ATD -7-1-21.xlsx

[andrewshensky]

I am showing that those feature layers are currently being updated from Maint to DM on a daily basis. It doesn't look like the tables exist in DataMart, only in Maint,

[susannegov]

Should I submit a DTS ticket to add the markings_dates table on GISDM? What steps do I need to get the ball rolling on getting the dates table on GISDM?

[andrewshensky]

If the tabular data is needed to be added to DataMart, we will need to add sufficient metadata for each table in Maint to meet CTM's specifications. Then I can submit a ticket to have them add that data to GISDM and Geodatapusher. At that point we can set up the job in Geodatapusher to update daily for the tables as well. Since most the work falls on the CTM side, I can't give an accurate timeline for all this to happen.

[susannegov]

@andrewshensky Do you want me to fill out the metadata for the related tables then? I remember doing it a couple years ago for the markings layers using excel sheet Jaime provided. I think it was changed into some sort of form add-on option on ArcGIS Pro now?

[andrewshensky]

@susannegov Yeah if you can that would be great. Let me know if you run into any issues or if anything is needed from me to help get the metadata added

[susannegov]

I know it has been a while since the sync. Getting the markings dates table turned out to be harder than anticipated (some bugs and waiting from CTM). I figured we should try to get the other datasets PBI is in meanwhile.

Datasets needed

• [x] Get markings dates tables from GISMAINT -> GISDM -> Socrata Open Data Portal
• [x] Get Monthly summary report email -> Socrata Open Data Portal
• [x] Get markings and signs specs/materials object tables from Signs and Markings Operations knack app on Socrata Open Data Portal
  • [ ] work_orders_signs_asset_spec_actual - Sign Specifications
  • [ ] work_orders_markings_specification_actual - Markings Specifications
  • [ ] invoice_items
  • [ ] inventory_transactions

[susannegov]

SMD Performance Measures dashboard is in automatic refresh as of yesterday. Need to add:

• [x] sign data
• [ ] long line data
• [x] 311 sr data from ODP

[amenity]

Update

• Remaining sign data will be delivered and added to MAINT next week. @susannegov - please create work session to review MAINT --> ODP publication process.
• Long line will be a bigger lift, covered by #13387
• 311 CSRs are covered by other issues.

[susannegov]

Andrew, David, and I did the GISMAINT to ODP for the signs_assets feature layer on 9/25 Location of FME file in: ...

1. Copied the old Socrata writer used in markings dates ODP
2. Used Attribute Manager to rename fields, included "Resolved domains" in the sign data reader setting to labeled domain values instead of short integer code values
3. EsriReprojector used to transform from NAD 1983 to WGS 1984?
4. Link to roadway assets on the EDP (private only to transportation data): https://datahub.austintexas.gov/dataset/Roadway-Sign-Assets/ggnk-3ykn

To do

• [x] Write-up Roadway Signs metadata
• [x] Added Location column from Socrata
• [x] Check tomorrow if location column still exists.
• [x] Check if when new points are created, if Location is created
• [x] Republish to public
• [x] Append batch 5 data
• [x] Update description metadata as-needed after new batch added

[susannegov]

• [x] Replace 311 SR link with ODP link https://datahub.austintexas.gov/Transportation-and-Mobility/Transportation-and-Public-Works-311-Service-Reques/38mr-dwji
• [ ] long line dataset in PBI

[susannegov]

The rest is done except long line, but long line is being tracked in https://github.com/cityofaustin/atd-data-tech/issues/13387