Insights Data Model
The Insights Data Model is designed to capture and analyze expense-related data within an organization. It provides a structured framework for organizing and understanding expense-related entities, dimensions, and dates. The model allows for effective filtering and analysis of expense data, enabling insights into expense patterns, compliance with policies, project/task expenses, cost center allocations, and more. It facilitates decision-making, policy refinement, and expense control within the organization.
Color Coding:
Green: Dataset with one or more facts (aggregatable fields).
Yellow: Dimension or dataset with categorical fields.
Blue: Date dimension.
Datasets with Facts and categorical data
Expense: The core entity representing an expense amount (fact) that can be aggregated.
Report: A expense report collects items that can be approved and controlled. At report level the Submission/Approval/Control/Settlement times (facts) can be aggregated.
Item: A line item represents an individual entry or expense within a transaction, typically part of a larger set of items associated with the same transaction. It provides detailed information about a specific expenditure, allowing for individual reporting and analysis within the context of the overall transaction. It also holds the rejection reasons by approver and controller. As they are recorded per submission and that would bring to much data to the table, we only recorded the first 5 (this means subsequent) rejection reasons per Approver/Controller. Hence a Number of Rejections (fact) helps keeping track of these.
User Profile & Expense Policy: User profiles representing employees who comply with expense policies. Although this table is mostly used as a dimensional table (i.e. not being used for the facts) it hold two special fields User Info Num 1 and 2 (facts) which can be aggregated.
Datasets with only categorical data: Dimensions
Company & Company Group: Organizational hierarchy where companies are grouped within a company group.
Cost Center: Subdivision of a company, representing a department.
Project/Task: Projects that group tasks, which are subdivisions of a company.
Category/Custom Category: Fixed classifications of products at item level, with the option for custom categories.
Controller: User profile responsible for controlling an item.
Merchant: Merchant data available at item level.
Transaction Type & Card Type: Payment methods and types of cards used, categorized by transaction level.
Location of Spend: Country where the transaction occurred, stored at transaction level.
Report History (see availability): Audit record of submission, approval, control and settlement actions on the expense report.
Blue (Date Dimensions):
Transaction Date: The date the transaction took place.
Posting Date: The date the transaction is logged in the system.
Submission Date: The last time a report was submitted for approval.
Approval Date: The last time an item was approved.
Control Date: The last time an item was controlled.
Settlement Date: The date when an item is marked for settlement after approval and/or control.