SSAS: Processing Cube Load

When we need to process the cube to fetch new data from the data source, we can either do it manually by right clicking the cube itself or by creating a SQL Server Agent Job.

There are multiple modes of processing the cube and what is updated will depend upon the type of mode chosen. Below code when copied into a SQL Server Agent job step will refresh the data.

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "YourCubeName"
      }
    ]
  }
}

The different options available to loading your cube are as following:

  • Default: It loads data into unprocessed partitions and tables only. Hierarchies, relationships, and calculated columns for only the above affected tables are updated. type is automatic.
  • Full: It is a complete load, which loads data into all the partitions and tables. And all the objects are updated. type is full.
  • Clear: If you have the requirement to clear up your cube to process manually for some particular objects. type is clearvalues.
  • Recalc: It just updates hierarchies, relationships and calculated columns for the complete model.type is calculate.
  • Data: It refreshes the data and clear and the dependents. type is data
  • Add: Append data to this partition and recalculate all dependents. This command is valid only for regular partitions and not for calculation partitions. It applies only to partitions and type is add.
  • Defragment: It defragments the data. As data is added to or removed from a table, the dictionaries of each column can become polluted with values that no longer exist in the actual column values. The defragment option will clean up the values in the dictionaries that are no longer used.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s