Aggregate Metrics Data Model

• Tech Stack: PowerApp, MS SQL, Azure Data Factory, CRM Analytics
• Github URL: Project Link

A modularized, scalable, efficient KPI data model to capture aggregated metrics overtime, with native historic and error logging features. Developers can build 1 stored procedure for each KPI that can then group by any number of grouping fields, and 1 master stored procedure captures data for all metrics in the data model.

Benefits of Design Structure

• Modularized - mechanisms to add/remove individual metrics or summary levels and targets without disrupting entire data model. Each metric gets its own stored procedure.
• Metadata Capture - Built to allow developers to store definitions for the metric, date, numerator, denominator, and rate.
• Centralized Automation - One master stored procedure and pipeline to process all metrics.
• Customizable Capture frequency - can choose to process metrics daily (default), weekly, monthly, quarterly, yearly at the metric level.
• Built for Auditing and Historical Tracking -
  • Central stored procedure tracks metadata about each stored procedure that runs (when it runs, how many rows added, updated, deleted)
  • Historical table stores all older data that can be pulled in for audits

Key Features

• PowerApp to store metric metadata to a key dimension table for each metric
• Simple, easy to extend scehma (see ER diagram in Github repo)
• Stored Procedure Template for developers (see Github repo)
• 1 master stored procedure to capture data for all metrics in data model and logic historic data (see Github repo)
• 1 pipeline in Azure Data Factory to automate data capture
• 1 SQL view that join dimension tables to fact tables for easy reports
• 1 KPI Explorer Dashboard to easily slice and dice on the data (see dummy data gif in repo)