# Aggregates in Dimensional Modeling

### **Introduction to Aggregate Tables**

Aggregate tables in Astera Data Stack allow users to quickly and easily merge data to compute averages, totals, counts, and minimum and maximum values.

These tables are highly beneficial when used as foundations for standard reports that require minimal changes. For such standardized reporting structures, aggregate tables are fast, dependable, and user-friendly for both developers and end-users. Their ease of setup also makes them valuable for impromptu reporting.

However, adaptability is compromised in favor of this efficiency and simplicity. Aggregate tables are not as practical for analysts who need to examine data from various perspectives. Unlike an OLAP cube, consolidated table data cannot be pivoted, nor can it be drilled down to a more detailed level to view underlying transactions. Nevertheless, aggregate tables can be incredibly useful when applied in the appropriate context.

#### **Creating an Aggregate Table**

For our use case, we will create an aggregate table to aggregate customers’ sales data by month.  For the purposes of this demonstration, please refer to the simple dimensional model shown here:

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FOBeu9He7A1nB4XZYivpA%2F01.png?alt=media\&token=586f476d-2ffc-4d89-9ec1-1cdaf88226ad)

1. As the sales data must be aggregated for our use case, the base fact table will be *Sale*. Right-click the *Sale* table header and select *Add Aggregate Table* from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FJAUSdwOxJkQt9koTMPoH%2F02.png?alt=media\&token=8731bf9a-6f8b-48ce-aaf4-651af6715d47)

2. A new *Sale\_Aggregate* entity will be created in the model. This will act as the aggregate entity, indicated by a blue dash-dotted link pointing to its base fact table, as shown below:

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FMXkjyZS0hfzsR3MmTdC8%2F03.png?alt=media\&token=e62533e3-e572-4921-938d-59aa4c6784ee)

#### **Configuring an Aggregate Table**

1. First, right-click the aggregate table’s header and select *Properties* from the context menu or double-click the table header.
2. In the *Sale\_Aggregate: Aggregate Table Properties* window, provide the name, schema, and description (optional) according to requirements. Once done, click *Next.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FITusQT3QUZwBZtVgvHCy%2F04.png?alt=media\&token=801991af-585c-465b-91d3-f3d964dff3e5)

3. In the *Sale\_Aggregate: Sort Transformation Properties* window, select fields on which the aggregate must be performed, along with the operation. Once done, click *Next.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Ft7W44deGgbapANX8FzaV%2F05.png?alt=media\&token=513ced9c-a42c-4d5a-a44a-7538fc98bb77)

4. In the *Sale\_Aggregate: Aggregate Group By* window, select *Group By* fields to specify fields to analyze data by and select granularity.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FKYT0t5ENwMh9wdEsMkPO%2F06.png?alt=media\&token=243bb392-5510-45af-a489-16099f42f2e9)

**Data Granularity**: You can change the granularity for a date field to determine the intervals for which item values are shown. Granularity in Aggregates only works if you have selected a date dimension related field as a *Groupby* field, which in our use case is the *Invoice\_Date\_key* field. You can set the date granularity to any one of the following values:

* Yearly
* Quarterly
* Monthly
* Weekly
* Daily (this is the default)

5. Once the appropriate granularity has been selected, click *Next.* The *Sale\_Aggregate: Entity Properties shows* the finalized layout of the aggregate table. You can change the length, name, and dB types of the fields according to your needs.
6. Once done, Click *OK* to close the window.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fr1jPdrCH9Spq9t6jFi50%2F07.png?alt=media\&token=3beb5de8-113a-4df6-b3ad-4426049b8a2c)

Your aggregate table has been configured successfully. The newly created blue, dash-dotted links point to the tables that your aggregate table is dependent on.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fdn9KkGlLoFBdPNeXBclB%2F08.png?alt=media\&token=ede9e200-d3f7-42c2-b175-61f2e8b9cae6)

Notice that a new dimension (*MonthDimension*) has also been created. This is because the ‘Monthly’ granularity was selected while configuring the aggregate.

The Month dimension will hold records in monthly granularity and will further allow timely aggregations of these figures when reporting. You will get different dissected dimensions for other granularities except for the ‘Daily’ granularity as the dimension for this granularity is already present in the *DateDimension* entity in the model.

1. Now, forward engineer the aggregate entities (aggregate table and dissected dimension) and the dimensional model (if it has not already been forward engineered).
2. Fill the dissected dimension, the *MonthDimension,* by right-clicking the table header and selecting the *Fill Month Dimension Table* option from the context menu.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FDg4Ynj8Zm00hVXh6bnaA%2F09.png?alt=media\&token=256681c3-860b-47ac-9d14-b00211f6fc90)

{% hint style="info" %}
**Note:** You can also edit or update already configured aggregate tables.
{% endhint %}

#### **Verifying Aggregate Table**s

After completing all aforementioned steps, the model must be deployed. When deploying a dimensional model containing aggregate tables, Astera Data Stack verifies some set of rules specific to aggregates in order for the deployment to be successful.

These rules are:

1. All dissected dimensions such as *MonthDimension*, *WeekDimension*…etc., must be filled.
2. At least one field should be selected for *Groupby*.
3. At least one field should be selected for *Aggregation*.
4. If a DateDimension-related field is selected as *GroupBy* and *Monthly*, *Weekly, Quarterly* or *Yearly* granularity is selected, then the equivalent dissected dimension (such as *YearDimension*) must be present in the model.

#### **Loading/Updating the Aggregate Table**

Aggregate tables are loaded/Updated along with fact tables.

{% hint style="info" %}
**Note:** For more information about configuring the Fact Table Loader object, click [here](https://documentation.astera.com/astera-data-stack-v10/dataflows/data-warehouse/fact-table-loader).
{% endhint %}

To load or update a fact table, the *Fact Table Loader* object can be used in a dataflow.

1. First, drag-and-drop the *Fact Table Loader* object from the Toolbox onto the designer.
2. Double-click the object header or right-click the object header and select properties from the context menu. The *FactSale: Database Connection* window will open.
3. Select the appropriate data model deployment and click *Next.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F1yBlVSt7nFnngJsub0QW%2F10.png?alt=media\&token=bfb1fbe8-8e4c-452d-bc12-b773b181a116)

4. In the *FactSale: Pick Table* window select the fact associated with your aggregate. Once done, click *Next.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FjDpRBT049K3BnO6z1eX7%2F11.png?alt=media\&token=054c7e78-6293-442b-92db-6c74be49e8c8)

5. In the *FactSale: Select Aggregate Table* window, you will see all the aggregates associated with the selected fact.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FXmwrlxE6ZwMxXVMd39qL%2F12.png?alt=media\&token=701fb181-2bb3-41bf-a29c-c839af8dffc2)

6. Check the aggregate tables which you want to load/update and click *OK* to close the window.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F7cZ1owBiYut3X3NWIR1v%2F13.png?alt=media\&token=b8df843f-d925-40e2-8f06-2a9ae54ce69b)

7. Now, map all appropriate fields from the *DataModelQuery* object to your fact loader, in the same manner as loading a fact table.

{% hint style="info" %}
**Note:** For more information about the Data Model Query object, click [here](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/data-model-query).
{% endhint %}

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FF69flvNZqakGlSYoSU97%2F14.png?alt=media\&token=b04e06b2-21fe-44a4-b56b-63833732b5af)

8. Now, run the fact loader dataflow. First, your fact table will be loaded, and then the selected aggregates that were checked earlier in the fact loader. You can see the stack trace of your aggregate in the *Job Progress* window.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FxAkaRQOwAfk5vJHQZjWr%2F15.png?alt=media\&token=b3f03515-39f4-4a23-a54b-0495878ec8d3)

Your aggregate table has been loaded successfully. You can now view the resultant data available in your aggregate table.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FfNEYeiIQQQ4l4dNIlJbz%2F16.png?alt=media\&token=21284317-fd85-4da6-b2b9-abdbba1fe86e)

Also notice that the “invoice Date Key” field shows only monthly level information, as we had selected *Monthly* Granularity while configuring our aggregate.

#### Things to consider when working with Aggregate tables:

* Aggregates only work with Star-Schema dimensional models.
* If any field/relationship/dimension is deleted and was being used in a configured aggregate, the aggregate table will need to be refreshed in order to make the required changes in the aggregate as well. You can refresh the aggregate by right-clicking the aggregate table header and selecting the *Refresh Aggregate Table* option from the context menu.