Page tree
Skip to end of metadata
Go to start of metadata

The Crosstabs in the following wiki page are intended to present the underlying data model of the Charts from an SAC Story. Hierarchy node(s) are marked bold, while Leaf(s)/Flat member(s) are non-bold.

The lengthy JSON strings on the page are recommended to be copied out to a notepad++ with JSONViewer plugin to make it more readable.

Flat Presentation

Filters in flat presentation are always treated a Data Filter.

We differentiate between Cartesian and non-Cartesian filters. 

In short, filters from same dimensional plane are Cartesian filters, these can be conjuncted with a logical OR together in a "Tuple" and multiple Tuples can be conjucted with a logical AND

Multi-dimensional filters or tuple filters are non-Cartesian filters, these multiple dimensional values are conjuncted with a logical AND together in a Tuple and multiple Tuples can be conjucted with a logical OR.

Cartesian filter

In case of Charts in SAC, if there is only one dimension on the axis, filter selections on widget interaction are resulted in a set of data points, booked for dimension members from the only dimension on the axis. 

It is worth to be mentioned that filters applied on chart by interacting with the widget is always a "filter on data point selection", therefore the context of the whole interacted Row is relevant therefore the measure structure selection needs to be part of the filter tree, as well as other dimensions on the axis.

Example

The following example shows a simple Chart in SAC that has GA_MAT1 Dimension in the Row and GA_SAL in the Column axis.

There is a filter set on two data points, that is resulted in a Cartesian filter set. Two flat dimension members MAT1 and MAT2 of Dimension GA_MAT1 , that is resulted in the following filter expression (Analytics.Definition.Filter)

One can also see that Measure structure selection (UID 4VGQIK77R2ZUZU0UVXIWSKB44 of GA_SAL, on the Key Figure Structure UID 4VGQIK77R2ZUZU0UVXIWSJS5G) is also defined in the filter expression, as the operation was performed on data point selection.

So the whole expression might look like this: (GA_MAT1 = "MAT1" OR GA_MAT1 = "MAT2") AND (4VGQIK77R2ZUZU0UVXIWSJS5G = "4VGQIK77R2ZUZU0UVXIWSKB44").

Cartesian Filter, Flat presentation
			"Filter": {
				"Selection": {
					"Operator": {
						"Code": "And",
						"SubSelections": [{
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "MAT1"
										}, {
											"Comparison": "=",
											"Low": "MAT2"
										}
									],
									"FieldName": "GA_MAT1.KEY"
								}
							}, {
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "4VGQIK77R2ZUZU0UVXIWSKB44"
										}
									],
									"FieldName": "4VGQIK77R2ZUZU0UVXIWSJS5G.KEY"
								}
							}
						]
					}
				}
			}
No Filter, Flat ResultSetCartesian filter

Tuple filter

In case of Charts in SAC, if there are more than one dimensions on the Axis, filter selections on widget interaction are always resulted in a set of data points booked for an intersection of dimension member selections on different dimensions on the axis. 

It is worth to be mentioned that filters applied on chart by interacting with the widget is always a "filter on data point selection", therefore the context of the whole interacted Row is relevant therefore the measure structure selection needs to be part of the filter tree, as well as other dimensions on the axis.

Example

The following example shows a simple Chart in SAC that has GA_MAT1 and GA_EMP8 Dimension in the Row and GA_SAL in the Column axis.

There is a filter set on three data points, that is resulted in a Tuple filter. There are three tuples constructed from the possible combinations of Dimension member MAT1 and EMP1, EMP2, EMP3, that that is resulted in the following filter expression (Analytics.Definition.Filter)

One can also see that Measure structure selection (UID 4VGQIK77R2ZUZU0UVXIWSKB44 of GA_SAL, on the Key Figure Structure UID 4VGQIK77R2ZUZU0UVXIWSJS5G) is also defined in the filter expression, as the operation was performed on data point selection.

So the whole expression might look like this: (GA_MAT1 = "MAT1" AND GA_EMP8 = "EMP1") OR (GA_MAT1 = "MAT1" AND GA_EMP8 = "EMP2") OR (GA_MAT1 = "MAT1" AND GA_EMP8 = "EMP3") AND (4VGQIK77R2ZUZU0UVXIWSJS5G = "4VGQIK77R2ZUZU0UVXIWSKB44")

Tuple Filter, Flat presentation
			"Filter": {(
				"Selection": {
					"Operator": {
						"Code": "And",
						"SubSelections": [{
								"Operator": {
									"Code": "And",
									"SubSelections": [{
											"Operator": {
												"Code": "Or",
												"SubSelections": [{
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.KEY"
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP1"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}, {
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.KEY"
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP2"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}, {
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.KEY"
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP3"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}
												]
											}
										}
									]
								}
							}, {
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "4VGQIK77R2ZUZU0UVXIWSKB44"
										}
									],
									"FieldName": "4VGQIK77R2ZUZU0UVXIWSJS5G.KEY"
								}
							}
						]
					}
				}
			}
No Filter, Flat ResultSetTuple filter

Active Hierarchy Display

Trapezoid filter

By default, if a node is included in the filter all its descendants are also included in the filter. Using trapezoid filters you can change this behavior by defining a level offset filter. The level offset specifies the relative starting level of descendants to include below the matching nodes. The behavior is result set case is the following:

  • The Filter defines the starting point in the hierarchy(a node).
  • The LevelOffset is the number of levels underneath to ignore(not display).
  • Then if the InitialDrillLevel is provided and bigger than zero, it is added to the LevelOffset to calculate the cumulated depth to be read. Example: LevelOffset = 2 and InitiallDrillLevel = 2 means ‘read 4 levels below, do not display the first 2 levels’.

  1. The set of nodes matching the filter is determined. Lets call the resulting set of nodes S (the example below assumes that S now contains 2 nodes)
  2. All descendants up to "InitialDrillLevel+LevelOffset" are included. The resulting set is Sd = S ∪ {Descendants of S up to 'InitialDrillLevel+LevelOffset'}
  3. All descendants of S up "LevelOffset" are excluded in the the final filter set SF = Sd \ {Descendants of S up to 'offset' levels}

In particular, in case of BW scenarios at the moment it is only the following combination is used and only for Chart Drill.

LevelOffsetInitialDrillLevelResult
10The direct children of the matched nodes but not the matched nodes themselves

Example

The following example shows a simple Chart in SAC that has GA_MAT1 Dimension in the Row and GA_SAL in the Column axis.

Hierarchy GA_HIE_MAT1_LINK1 is Active on Dimension GA_MAT1 (Analytics.Definition.Dimensions.Hierarchy.HierarchyName).

There is a Drill operation performed on hierarchy node GA_MAT1!MAT1, that is resulted in a Trapezoid filter (Analytics.Definition.Filter.Selection.Operator.SubSelections.SetOperand.Elements.LevelOffset) on node MAT1 of Hierarchy GA_HIE_MAT1_LINK1 (Analytics.Definition.Filter.Selection.Operator.SubSelections.SetOperand.Elements.Hierarchy.Name).

As it is mentioned, Hierarchy is also called with InitialDrillLevel: 0 (Analytics.Definition.Dimensions.Hierarchy.InitialDrillLevel).

One can also see that Measure structure selection (UID 4VGQIK77R2ZUZU0UVXIWSKB44 of GA_SAL, on the Key Figure Structure UID 4VGQIK77R2ZUZU0UVXIWSJS5G) is also defined in the filter expression, as the operation was performed on data point selection.

The tuples of the measure and non measure dimension are conjuncted with a logical AND.

Trapezoid filter
"Filter": {
				"Selection": {
					"Operator": {
						"Code": "And",
						"SubSelections": [{
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Hierarchy": {
												"Name": "GA_HIE_MAT1_LINK1"
											},
											"LevelOffset": 1,
											"Low": "GA_MAT1!MAT1"
										}
									],
									"FieldName": "GA_MAT1.$$HierarchyField$$.KEY",
									"Hierarchy": {
										"Name": "GA_HIE_MAT1_LINK1"
									}
								}
							}, {
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "4VGQIK77R2ZUZU0UVXIWSKB44"
										}
									],
									"FieldName": "4VGQIK77R2ZUZU0UVXIWSJS5G.KEY"
								}
							}
						]
					}
				}
			}
No Filter, Hierarchical displayTrapezoid filter

Display filter

Only relevant with Active Hierarchy display. Displaying result sets for dimensions with an active hierarchy is based on two fundamentally different types of information:

  • What data should be taken into account for the aggregation?
  • How should the data be presented in the result set?

Display filter specifies display information, how should the data be presented in the result set. In SAC BW Live, Data and Display filters are differentiated by the attribute called in the INA ResultSet request. ConvertToFlatSelection. If the ConvertToFlatSelection: false, the filter is treated as a display filter (It is important to be mentioned that ConvertToFlatSelection: false is presented in the INA request in a way, that ConvertToFlatSelection is not presented at all). 

ConvertToFlatSelection: false means that the nodes should be considered as presentation information, so the node is supposed to be displayed as a root, and there is no filter explicit on the data.

It is worth to be mentioned that filters applied on chart by interacting with the widget is always a "filter on data point selection", therefore the context of the whole interacted Row is relevant therefore the measure structure selection needs to be part of the filter tree, as well as other dimensions on the axis.

Example

The following example shows a simple Chart in SAC that has GA_MAT1 Dimension in the Row and GA_SAL in the Column axis.

Hierarchy GA_HIE_MAT1_LINK1 is Active on Dimension GA_MAT1 (Analytics.Definition.Dimensions.Hierarchy.HierarchyName).

There is a display filter set on node GA_MAT1!MAT1, that is result in the following filter expression (Analytics.Definition.Filter) on the $$HierarchyField$$.KEY FieldName (Analytics.Definition.Filter.Selection.Operator.SubSelections.SetOperand.FieldName) of dimension GA_MAT1, in the in the context of Hierarchy GA_HIE_MAT1_LINK1 (Analytics.Definition.Filter.Selection.Operator.SubSelections.SetOperand.Hierarchy.Name). 

The filter expression has no ConvertToFlatSelection: true attribute, which explicitly means that it is display filter (ConvertToFlatSelection: false).

One can also see that Measure structure selection(UID 4VGQIK77R2ZUZU0UVXIWSKB44ofGA_SAL, on the Key Figure Structure UID 4VGQIK77R2ZUZU0UVXIWSJS5G) is also defined in the filter expression, as the operation was performed on data point selection.

Display filter, Active hierarchy display
"Filter": {
				"Selection": {
					"Operator": {
						"Code": "And",
						"SubSelections": [{
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Hierarchy": {
												"Name": "GA_HIE_MAT1_LINK1"
											},
											"Low": "GA_MAT1!MAT1"
										}
									],
									"FieldName": "GA_MAT1.$$HierarchyField$$.KEY",
									"Hierarchy": {
										"Name": "GA_HIE_MAT1_LINK1"
									}
								}
							}, {
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "4VGQIK77R2ZUZU0UVXIWSKB44"
										}
									],
									"FieldName": "4VGQIK77R2ZUZU0UVXIWSJS5G.KEY"
								}
							}
						]
					}
				}
			}
No Filter, Hierarchical displayDisplay filter

Data filter

It specifies what data should be taken into account for the aggregation.

The only link between data filter and display filter is if based on display filter, it can be derived that certain parts of the data are not to be displayed, then retrieving those records from the data source is useless for the result set, so the display information of the display filter can be used to enhance the filter information of the data filter. In that sense, using display information also for filtering makes sense, but is optional and is just an optimization with respect to data access.

We also differentiate between Cartesian and non-Cartesian filters. On the same dimension: Several (flat or hierarchy) filters may occur (resulted from different sources i.e. in Linked Analysis, or Filter Across Models), but at most one hierarchy filter may contain the presentation information.

In short, filters from same dimensional plane are Cartesian filters, these can be conjuncted with a logical OR together in a "Tuple" and multiple Tuples can be conjucted with a logical AND

Multi-dimensional filters or tuple filters are non-Cartesian filters, these multiple dimensional values are conjuncted with a logical AND together in a Tuple and multiple Tuples can be conjucted with a logical OR.

Tuple filter

Technically in SAC BW Live in case of "non-Linked Scenarios" with active hierarchy display (Linked analysis, Filter Across Models, etc), it is only the Tuple filter that is resulted in a Data filter on widget interaction. If there are more than one dimensions on the Axis and one of them has active hierarchy display, filter selections on widget interaction are always resulted in a set of data points booked for an intersection of hierarchy node(s)/leaf(s) and flat dimension member selections from the dimensions on the axis. 

It is worth to be mentioned that filters applied on chart by interacting with the widget is always a "filter on data point selection", therefore the context of the whole interacted Row is relevant therefore the measure structure selection needs to be part of the filter tree, as well as other dimensions on the axis.

Example

The following example shows a simple Chart in SAC that has GA_EMP8 and GA_MAT1 Dimension in the Row and GA_SAL in the Column axis.

Hierarchy GA_HIE_MAT1_LINK1 is Active on Dimension GA_MAT1 (Analytics.Definition.Dimensions.Hierarchy.HierarchyName).

The "filter on data point selection" is set on the combination of Hierarchy node GA_MAT1!MAT1 and the related flat dimension members EMP1, EMP2 and EMP3 (Analytics.Definition.Filter).

From the OLAP point of view it is not possible to intersect hierarchy node(s) with ranges (set of flat member(s)/leaf(s)). Therefore it is needed to convert the hierarchy node to a ranges of flat leaf(s). This conversion of the hierarchy node is allowed by an attribute in the INA request called ConvertToFlatSelection: true (Analytics.Definition.Filter.Selection.Operator.SubSelections.Operator.SubSelections.Operator.SubSelections.Operator.SubSelections.SetOperand.ConvertToFlatSelection). It basically means from the hierarchy node point of view that the node allows itself to be converted into its ranges of flat leafs, that can be used for intersection. Then it can be converted back to a node, which is used as a display information.

So the whole expression might look like this: (GA_MAT1 with active hierarchy GA_HIE_MAT1_LINK1 = ConvertToFlatSelection: true "GA_MAT1!MAT1" AND GA_EMP8 = "EMP1") OR (GA_MAT1 with active hierarchy GA_HIE_MAT1_LINK1 =  ConvertToFlatSelection: true "GA_MAT1!MAT1" AND GA_EMP8 = "EMP2") OR (GA_MAT1 with active hierarchy GA_HIE_MAT1_LINK1 = ConvertToFlatSelection: true "GA_MAT1!MAT1" AND GA_EMP8 = "EMP3") AND (4VGQIK77R2ZUZU0UVXIWSJS5G = "4VGQIK77R2ZUZU0UVXIWSKB44")

One can also see that Measure structure selection(UID 4VGQIK77R2ZUZU0UVXIWSKB44ofGA_SAL, on the Key Figure Structure UID 4VGQIK77R2ZUZU0UVXIWSJS5G) is also defined in the filter expression, as the operation was performed on data point selection.

Data Filter, Active hierarchy display
			"Filter": {
				"Selection": {
					"Operator": {
						"Code": "And",
						"SubSelections": [{
								"Operator": {
									"Code": "And",
									"SubSelections": [{
											"Operator": {
												"Code": "Or",
												"SubSelections": [{
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"ConvertToFlatSelection": true,
																		"Elements": [{
																				"Comparison": "=",
																				"Hierarchy": {
																					"Name": "GA_HIE_MAT1_LINK1"
																				},
																				"Low": "GA_MAT1!MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.$$HierarchyField$$.KEY",
																		"Hierarchy": {
																			"Name": "GA_HIE_MAT1_LINK1"
																		}
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP1"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}, {
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"ConvertToFlatSelection": true,
																		"Elements": [{
																				"Comparison": "=",
																				"Hierarchy": {
																					"Name": "GA_HIE_MAT1_LINK1"
																				},
																				"Low": "GA_MAT1!MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.$$HierarchyField$$.KEY",
																		"Hierarchy": {
																			"Name": "GA_HIE_MAT1_LINK1"
																		}
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP2"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}, {
														"Operator": {
															"Code": "And",
															"SubSelections": [{
																	"SetOperand": {
																		"ConvertToFlatSelection": true,
																		"Elements": [{
																				"Comparison": "=",
																				"Hierarchy": {
																					"Name": "GA_HIE_MAT1_LINK1"
																				},
																				"Low": "GA_MAT1!MAT1"
																			}
																		],
																		"FieldName": "GA_MAT1.$$HierarchyField$$.KEY",
																		"Hierarchy": {
																			"Name": "GA_HIE_MAT1_LINK1"
																		}
																	}
																}, {
																	"SetOperand": {
																		"Elements": [{
																				"Comparison": "=",
																				"Low": "EMP3"
																			}
																		],
																		"FieldName": "GA_EMP8.KEY"
																	}
																}
															]
														}
													}
												]
											}
										}
									]
								}
							}, {
								"SetOperand": {
									"Elements": [{
											"Comparison": "=",
											"Low": "4VGQIK77R2ZUZU0UVXIWSKB44"
										}
									],
									"FieldName": "4VGQIK77R2ZUZU0UVXIWSJS5G.KEY"
								}
							}
						]
					}
				}
			}
No Filter, Hierarchical displayData filter - Tuple filter

  • No labels