Loading Data: Dimension, Transaction or Metric?

In Pigment, you will need to load different types of data in either high or small volumes. This article provides guidance on which Block to load the data into: Dimension List, Transactions List or Metric?

ℹ️ Note

This article discusses Pigment concepts covered in the Academy, and although there is a short summary below, we advise you to go through the modeler training before reading this article. 

1. Summary of the concepts

Data

Here, we differentiate between Data and Meta Data.

Data are facts that describe the world.
Meta Data are words used to describe the world.

Typical Data examples:

• Accounting general ledger with amounts, dates, descriptions

• Opportunities

• Past revenue

• Roster of employees with start date, salary, etc.

This is Data and these data sets are typically qualified as Transactional Data.

Examples of Meta Data include:

• General Ledger accounts

• Customer names

• Products

• Employee names

Dimension Lists

Dimension Lists are Blocks generally used to represent the business Dimensions of your company: Month, Year, Country, Department, Products, etc. It is possible to use the Dimension List to hold data, with one constraint: you will need a unique ID for each line in your data set.

Transaction Lists  

Transaction Lists are purposefully made to hold data, specifically transactional data. There are two major differences between a Dimension List and a Transaction List:

1. Transaction Lists generate their own unique IDs, hence you do not need to have one in your data set. That means you can load your data straight away, but you won’t be able to reload the same line and update it (you need to delete it first, then reload).

2. Transaction Lists cannot be used as the Dimension for a Metric. You’ll only be able to see the Transaction List in the list itself.

Metric

Metrics are Pigment Blocks that hold most of the logic and data. It is a multi-dimensional cell (Cost by Employee x Month for example). Data can also be loaded directly into Metrics.

2. What goes where?

Now that we have covered the concepts, let’s review specific cases.

Transactional Data

You guessed it: Transaction Lists. If your data set matches several of the points below, it qualifies as a Transaction List:

• multiple fields of data

• frequent reload (monthly, weekly, daily)

• from a source system

An example of this would be a general accounting ledger, extractions from data warehouses, data from your HR system, CRM, etc.

Analytical Data

Analytical data means a single field of data already structured by the Dimensions you’ll need and is ready to use without transformation. This type of data should be loaded directly into a Metric where it will be available to use in your calculations and reports.

Examples:

• Revenue by Country x Product x Month

• Budget by Account x Department x Month

Meta Data

Meta data is overwhelmingly used as Dimensions, hence it should go into Dimension Lists which you can then use to create your Metric.