Description

The source data is the gsheet from user, we load to big query (BQ) by direct link to gsheet. This is the requirement want to have BQ reflect the change real time.  However, with the direct link, it is not possible for power BI (PBI) because of security. 

Therefore, the table is request to load to normal table before reading by PBI

Detail job

1. Table prj-data-industrial-dash-dev.DM.FACT_oee_wip is created by linked to gsheet directly.
2. Query GSheet-Refresher, which schedule every 30 min will generate table DM.FACT_oee_wip-table
3. View on DPL dataset will read the data from DM.FACT_oee_wip-table
   1. V_FACT_oee_wip is the same as gsheet
   2. V_FACT_oee_losses split from oee_wip and unpivot column to row  
   3. V_FACT_oee_main split from oee_wip to have main data and remove losses data

Access rights

Not required

Source

• OEE Dev
• OEE Prod

Format

• JSON

Destination

Location

• GCP = prj-data-industrial-dash-[env]
• DM Table names 
  • FACT_oee_wip
  • FACT_oee_wip-table

• DPL View names   
  • V_FACT_oee_wip
  • V_FACT_oee_main
  • V_FACT_oee_losses

Format

• columnar format

Sizing

1,405 rows

Assessment

How to validate that the generated output is valid: 

Data same as gsheet

Loading

1.1 Incremental Load

N/a

1.2 Full load

Just trigger the job GSheet-Refresher

1.3. Reloading data

Just trigger the job GSheet-Refresher

1.4 Plan to schedule

Every 30 minutes

1.5 Timing

The average time expected for  loading: 1.5 minutes

Criticality

Low?

Logging

1. Check on the GCP BQ project at "Scheduled queries" the name of the schedule is "GSheet-Refresher"