Partitions define separately manageable data slices of the measure group data. Partitions can be created, processed and deleted independently within a Measure Group. Each Measure Group needs at least one partition to be defined to enable the Measure Group to be processed.
Partitions are managed through the Processing/Partitions tab of the Measure Group's Properties within WhereScape RED.
Field | Description |
---|---|
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. |
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: |
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: |
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. |