In Pigment, you will need to load different types of data in either high or small volumes. The purpose of this article is to answer the question of which Block to load the data into: Dimension List, Transactions List or Metric?
Note
This article discusses Pigment concepts covered in the Fundamentals training, and although there is a short summary below, we advise you go through the Fundamentals trainings before reading this article.
1. Summary of the concepts
Data
We are talking about data, but what type of data in particular?
Here, I’ll differentiate between Data & 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.
Meta Data? Well, you can think of that as:
-
General Ledger accounts
-
Customer names
-
Products
-
Employee names
Dimension List
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.
Transactions List
Transactions Lists are purposefully made to hold data, specifically transactional data. There are two major differences between a Dimension List and a Transactions List:
- Transactions 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 & update it (you need to delete it first, then reload).
- Transactions Lists cannot be used as the dimension for a Metric. You’ll only be able to see the Transactions List in the list itself.
Metric
Metrics are Pigment Blocks that hold most of the logic & 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 & reports.
Examples:
- Revenue by Country x Product x Month
- Budget by Account x Department x Month
Meta Data
Meta data will overwhelmingly be used as Dimensions, hence, it should go into Dimension Lists which you can then use to create your Metric.