About Measures and Dimensions
In business intelligence (BI) tools such as Tableau or Excel, data plays one of two major roles in an analysis: dimension or measure. An AtScale virtual model describes the underlying data in the connected data warehouse as dimensions and measures so that BI tools can work with this data more easily.
Measures (Quantifiable Data)
Measures represent the quantifiable data in a model. They are always numeric values that result from an aggregate calculation, such as a COUNT or SUM, on the fact data.
In an analysis, a measure is used to answer business questions such as 'how much?', 'how many?' or 'how long?'. Examples are in the following table.
| Business Question | Source Data Columns | Example Measure and Aggregation Used |
|---|---|---|
| How long on average does a visitor spend on my web site? | session_duration / row_count | Total Session Duration (SUM) / Number of Visits (COUNT) |
| How many active customers do I have? | customer_id | Number of Customers (COUNT DISTINCT) |
| What was the total amount sold? | amount * quantity | Total Sales (SUM) |
One way to determine the measures of a dataset, is to look at the columns in a fact dataset, and ask yourself how that data might be measured. Choosing the right fields in a dataset to serve as measures, and the right aggregations to apply to those fields, depends on how you plan to analyze the data. For example, a product ID number might not make sense to sum or average, but it might make sense to count how many distinct product IDs were sold.
Dimensions (Categorical Data)
Dimensions organize the data and provide context for a model's measures. Dimensions are logical collections of attributes, which are bound to one or more columns in a source dataset. A dimension attribute is data that answers business questions such as 'who?', 'what?', 'where?', or 'when?'. For example:
| Business Question | Source Data Columns | Example Dimension Attribute |
|---|---|---|
| What month had the most sales? | date.month | Month (an attribute of the Order Date dimension) |
| Who buys more - men or women? | customer.gender | Gender (an attribute of the Customer dimension) |
| Where in the country do most of my customers live? | customer.location.state | State (an attribute of the Customer Location dimension) |
In BI client applications, the dimension attributes are used to GROUP and FILTER the measure data. When designing the dimensions of a model, think about how you want to slice, dice, drill into, and filter the data in your analysis, then design your dimensions to support those operations.