OLAP Cube Measure Group Processing/Partitions

Process Method

The processing or updating method of a Measure Group is an area that requires careful consideration. The default option is Full process which results in the Measure Group being rebuilt. This is in many ways the safest option, but processing time may mean other options must be chosen.
The following describes the processing methods that are available in Analysis Services for Measure Groups:
· Default Process - Detects the process state of the measure group, and performs processing necessary to deliver a fully processed state.
· Full Process - Processes an Analysis Services Measure Group regardless of state. When Process Full is executed against a Measure Group that has already been processed, Analysis Services drops all data, and then processes it.
· Incremental Process - Adds newly available fact data and process only to the relevant partitions. To use this option you must set the 'incremental filter' field with a statement that results in only new data being selected. Failure to do so , results in duplicated data in the cube. In many data warehouses a fact table undergoes changes to the data, as well as the addition of new data, and so the incremental update option is not possible. A validation regime must be put in place to compare cube data to the fact data if incremental is used. This validation regime must be used to notify the administrator in the event that duplicate data is inserted into the cube.
· Update Data - Processes data only without building aggregations or indexes. If there is data in the partitions, it is be dropped before re-populating the partition with source data. This processing option is supported for dimensions, cubes, measure groups, and partitions.
· Build Structure - If the cube is unprocessed, Analysis Services processes, if it is necessary, all the cube's dimensions. After that, Analysis Services creates only cube definitions. The build structure option just builds the cube structure without populating it. This can be useful if you have a very large cube and want to validate the design.

Increment Filter

If an incremental processing option is chosen, then a filter statement must be selected to only return those rows that are to be added to the Measure Group. As mentioned above, caution must be taken when using incremental updates. For example, if the Measure Group is accidentally processed twice and the filter is based on date, then duplicate data is inserted into the Measure Group without any warning.

Partitioning

Partitioning is useful for handling large datasets where it is impractical to reprocess the entire Measure Group. In such a case, the full process option would probably be chosen but only selected partitions would be processed. See the section on partitioning for more information. The default process performs a full process on the first pass followed by incremental updates on subsequent processing runs. Caution must be taken when choosing default for the cube.

Partitioning Method

Three options are provided for handling Measure Group partitions. They are:
· One partition only - When this option is selected the partition information for the Measure Group is ignored and one partition is created and processed for the Measure Group. This would be the normal situation unless performance issues require an alternate strategy.
· Manually managed multiple partitions - With this option the partition information stored for the Measure Group is used in the creation and processing of the Measure Group.
· Automatic partition handling - This option is available if the Measure Group is to be partitioned by one numeric value. The partitioning should preferably be on something like day, month or year. (i.e. YYYY, YYYYMM or YYYYDDD). If this option is chosen together with one of the date formats described above, then WhereScape RED automatically creates partitions as required, and process only those partitions that are marked for processing.

Partition by Dimension

This field is only available if automatic partition handling is chosen. Select the dimension in the Measure Group that we will partition by. This would normally be a date dimension.

Partition by Attribute

This field is only available if automatic partition handling is chosen. Select the attribute that we are to partition by. This would normally be a year or maybe a month level. (e.g. cal_year, fin_year from the WhereScape date dimension).

Partition by Value Type

This field is only available if automatic partition handling is chosen. Select the type of level we are dealing with. Choose YYYY for a year partition and YYYYMM for a month partition. This format must correspond with the column in the date dimension. WhereScape RED only supports partitioning by Year, Quarter, month or day.

Fact Partition Lookup Clause

This field is only available if automatic partition handling is chosen. In order to know when to create a new partition WhereScape RED executes a query against the fact table and the date dimension to acquire each unique period. When dealing with a large fact table, such a query may take a long time to complete. This field can be used to include the components of the 'Where' clause to restrict the amount of data examined. For example, we may enter 'dss_update_time < GETDATE()-14' to only look at fact table records that have been inserted or updated in the last 14 days. This still enables you to catch any new partitions and add them. The first time a cube is converted to auto partitioning handling, a full pass of the Fact table should occur to allow inclusion of every partition. This field must therefore only be populated once the cube has been initially built with all partitions intact.

Max Number of Auto Created Partitions

This field is only available if automatic partition handling is chosen. You can specify an upper limit for automatically created partitions. The default is zero, or no limit. This limit may be useful if your source system can get erroneous data. If set, then the processing of the Measure Group will fail if a new partition will exceed the counter.

Number of Historic Partitions Updated

This field is only available if automatic partition handling is chosen. This field enables you to restrict the partition updating to the latest nnn partitions. If for example, you were partitioning by year and set this value to 2, you would process the current and previous years only. WhereScape RED turns OFF partition processing, after it does a partition update, so the first pass still updates all partitions.

To Display Measure Groups

To display a list of measure groups defined against a cube, right-click on a cube and select Display Measure Groups.

To Add a Measure Group

To add a measure group, display the measure groups in the middle pane and either:
· Drag over a new fact table into the target pane - this automatically creates a new measure group in the cube. Any additional dimensions required to support analysis of the Measure Group is added to the cube.
· Right-click the cube in the Objects list pane and then select Add Measure Group and fill in the Measure Group properties.

To Delete a Measure Group

To delete a measure group, display the measure groups in the middle pane and then right-click to select delete measure group.

Displaying Measures

Measures can be displayed or added while viewing measure groups in the middle pane. Right-click a measure group and select the appropriate option.

Displaying Partitions

Partitions can be displayed or added while viewing measure groups in the middle pane. Right-click a measure group and select the appropriate option.