Integrate Teramind Data for displaying into Power BI

[jesusitd]

The data point / report to start with is ADP historical login data for all users.

Also this endpoint: https://intakedesk.teramind.co/#/schedule?tab=scheduling

The other data point TOP PRIORITY is /tt/r/time-records/grid, which correlated to https://intakedesk.teramind.co/#/tt/r/timecard and https://intakedesk.teramind.co/#/tt/r/time-records , but we prefer time-records because the API data seems more complete. Tasks:

• [x] Pull Time Records data into Azure Blob
• [x] Store metadata about Time Records (JG, Table Storage?)
• [x] Store the Time Records in SQL (1M rows/year without scaling, as per estimation)
• [x] Process the records to get aggregate data for Power BI in SQL (day-agent)

Data points to Ingest:

• [x] Web Apps
• [x] Time Records
• [x] Web Apps historical (MOVE MINERS TO FUNCTION APP)
• [x] Time Records historical (MOVE MINERS TO FUNCTION APP)

Data points to Mine:

• [x] ADP Login Time from Webapps (Table + SQL)
• [x] Time calculations at Agent/Day level from Time Records (Table + SQL)

Infrastructure:

• [x] Storage account
• [x] Azure SQL basic (Not needed V1)

The goal is to provide accounting with a Teramind report that can be used for weekly worked-time analysis.

Week-daily (date dim) Agent (agent dim) Facts:

• Time worked - EG no like i.e. 24h worked - skewed
• Idle time - skewed
• Productive time - skewed
• Activity % - skewed, keep around but don't use as a KPI. Do NOT MINE productivity endpoints for V1.
• Breaks # (FOR V2)
• Time breaks (FOR V2)
• Time stacked bar chart (FOR V2)
• ADP Login time (FOR VIVIAN, CRITICAL DAILY)
• First time (>5%) (FOR VIVIAN, CRITICAL DAILY)
• Last time (>5%) (FOR VIVIAN, CRITICAL DAILY)
• NEED TO COME UP WITH CALCULATIONS from MINED data: time worked, idle time, productive time, activity %

CRM Data to link:

• First Call
• ADP- 1st call latency
• No. calls, conversions, etc...

DOESN'T need to be a new report. May be Remote Agents report.

DON'T use for V1 the endpoint https://{teramind-instance}/tm-api/v1/stats/productivity

EG: Think of this report, where you can select a dat range and use a matrix for each agent to drop-down on the daily data:

Create a new tab to show information about the Agent Activity from TV 2.0 Small in a CALENDAR FASHION, like:

[jesusitd]

Pull Time Records data into Azure Blob

Proposed storage format: {yyyy-mm-dd EST}/{agent_id}/{time-record-file}_{page_num}.json

This means that a list of agents (agent id) must be pulled before running the API calls.

• The files must be stored as is.
• Ingestion should overwrite today's data.
• A "Yesterday" job should be run to close every day.
• Consider Queues to take care of immediate postprocessing, so we can separate matters.

UPDATE deployed resource group by solution out of Logic Apps

UPDATE pulling TT Time Records into Azure Blob Storage

[jesusitd]

Store metadata about Time Records (JG, Table Storage?)

An initial metadata store can be built with Table Storage (perhaps).

This would actually take a queue to be fed by the previous process. Need to define the processing mechanism, and schedules. Metadata must be broken down by TIME RECORD ID.

[jesusitd]

Store the Time Records in SQL (1M rows/year without scaling, as per estimation)

A SQL database will be enabled to host the individual-record granularity.

Need to define is this would be triggered by the Table Storage metadata or by a separate routing queue from the ingestion process.

This data could be processed in Power BI to generate something like this:

[jesusitd]

Process the records to get aggregate data for Power BI in SQL (day-agent)

A data mining process must be in place to aggregate the data into workable chunks, as per @fedegarza specs:

• Look for the first activity with % > 5% => Day Start
• Look for the last activity with % < 5% => Day End
• All time windows with < 5% activity will be considered breaks
• Must count breaks
• Must count total time
• Must count breaks time
• Must count worked time
• Must calculate active %

This will be pushed into a SQL database for reporting.

[jesusitd]

Store metadata about REPORT WEB APPS (Table Storage)

Logic App: teramind-report-web-apps-blob-to-table

Azure Table: ReportWebApps

Store metadata about TT TIME RECORDS (Table Storage)

PENDING

[jesusitd]

DONE!

Visit Resource group: https://portal.azure.com/#@intakedesk.com/resource/subscriptions/8af0aa96-375e-4485-8946-1bd60775a318/resourceGroups/integrations-teramind/overview