image
Suhaila

1

Hierarchy In OLAP Cube


What is the difference in these three hierarchy; default hierarchy, multiple hierarchy, ragged and unbalanced hierarchy for a dimension in OLAP cube? When to know which hierarchy to be used?



- Suhaila
image

Lili96

0

Defining a Default Hierarchy

When you define cube dimensions, levels, and hierarchies in SAS OLAP Cube Studio, a default hierarchy for a dimension is automatically created if a hierarchy is not explicitly defined. This default hierarchy includes all levels that were specified for the current dimension and the order that they were listed in for the dimension. In addition, if you define multiple hierarchies and do not select a default, then the default is automatically assigned to the first hierarchy that is created for the dimension. On the Dimension Designer – Hierarchy window, you can click the Default button to set a selected hierarchy as the default for the dimension.

Defining Multiple Hierarchies for a Dimension

SAS OLAP cubes are organized into dimensions and levels of data. The levels are then arranged into hierarchies. After an initial hierarchy has been created, you can define additional hierarchies for a single dimension of a cube. This enables you to have multiple possible drill paths for the same data. When you create more than one hierarchy for a dimension, the levels have some restrictions: A level in a dimension might be used in more than one hierarchy within that dimension. However, levels cannot be used in hierarchies that are not defined within the dimension that the level is defined in. Each level must be used in at least one hierarchy. Levels from the same dimension that are picked for an aggregation must be in the drill order for at least one hierarchy in that dimension. You cannot share levels between dimensions.

Defining Ragged and Unbalanced Hierarchies for a Dimension

Dimension levels are arranged in one or more hierarchies. Hierarchies, by process of ordering, have a branching arrangement, and the different member levels have parent and child relationships. For example, at company X the sales staff are located in different regions and cities in different countries. A balanced hierarchy might look like this: global sales president (top of hierarchy)

sales presidents (per country)

regional sales managers

city sales managers

Because of differences in the cube data, hierarchies are often not balanced and possibly have missing members. For example, some sales regions might not have sales managers assigned to a specific city. Or some countries might not have sales regions, just cities. These real-world scenarios would create hierarchies that have missing member data and possibly ragged hierarchies. This affects the drill path of the cube data. You can also drill to missing members within a path and continue to drill down to members that are present.

Reference:https://support.sas.com/documentation/cdl/en/olapug/69844/HTML/default/viewer.htm#n0x2kg62awzxvzn1jogrb0jznmmk.htm



- Lili96
image

Bjergsen

3

To keep it simple,

As the name suggests , default hierarchy is the default hierarchy when there is no other type of hierarchy defined .

Ragged and Unbalanced Hierarchies are those where hierarchies are arranged, and by order .

Multiple hierarchies are those where you create hierarchies after making a hierarchies , so you will multiple .



- Bjergsen
image

AlvinYap

0

SAS OLAP cubes are organized into dimensions and levels of data. The levels are then arranged into hierarchies. After an initial hierarchy has been created, you can define additional hierarchies for a single dimension of a cube. This enables you to have multiple possible drill paths for the same data.

Default hierarchy identifies a hierarchy as the default hierarchy for the dimension that is defined by the DIMENSION statement.

Multiple hierarchies for a dimension typically share the base-level dimension members and then branch into separate hierarchies. They can share the top level if they use all the same base members and use the same aggregation operators. Otherwise, they need different top levels to store different aggregate values. For example, a Customer dimension may have multiple hierarchies that include all base-level customers and are summed to a shared top level. However, a Time dimension with calendar and fiscal hierarchies must aggregate to separate Calendar Year (January to December) and Fiscal Year (July to June) levels, because they use different selections of base-level members.

Ragged and Unbalanced hierarchy contain at least one member with a different base, creating a "ragged" base level for the hierarchy. Ragged hierarchies are not supported for cube materialized views.



- AlvinYap
image

SandyTang

0

Unbalanced hierarchy

The branches is an unbalanced hierarchy descend to different levels. For example, in the following diagram, the highest level in an organization is the CEO (Level 1); Level 2 is the vice-presidents and the CEO’s executive assistant. The executive assistant does not have subordinates, unlike the vice-presidents. enter image description here

An unbalanced hierarchy can also be ragged. In a ragged-unbalanced hierarchy, there are gaps in the levels and the levels descend to different depths.

Ragged Hierarchy

For example, the highest level is the company (Level 1); Level 2 is Branch; Level 3 is Building; Level 4 is Department. Some branches may only have one building location, with the same departments as multi-building locations. enter image description here



- SandyTang
image

HuiLing

2

Default hierarchy:

  • When you define cube dimensions, levels, and hierarchies in SAS OLAP Cube Studio, a default hierarchy for a dimension is automatically created if a hierarchy is not explicitly defined. This default hierarchy includes all levels that were specified for the current dimension and the order that they were listed in for the dimension. In addition, if you define multiple hierarchies and do not select a default, then the default is automatically assigned to the first hierarchy that is created for the dimension.

Multiple hierarchy:

  • SAS OLAP cubes are organized into dimensions and levels of data. The levels are then arranged into hierarchies. After an initial hierarchy has been created, you can define additional hierarchies for a single dimension of a cube. This enables you to have multiple possible drill paths for the same data. When you create more than one hierarchy for a dimension, the levels have some restrictions:
  • -A level in a dimension might be used in more than one hierarchy within that dimension. However, levels cannot be used in hierarchies that are not defined within the dimension that the level is defined in.
  • -Each level must be used in at least one hierarchy.
  • -Levels from the same dimension that are picked for an aggregation must be in the drill order for at least one hierarchy in that dimension.
  • -You cannot share levels between dimensions.

Ragged and unbalanced hierarchy:

  • A ragged hierarchy is a user-defined hierarchy that has an uneven number of levels.

  • Common examples include an organizational chart where a high-level manager has both departmental managers and non-managers as direct reports, or geographic hierarchies composed of Country-Region-City, where some cities lack a parent State or Province, such as Washington D.C., Vatican City, or New Delhi.

  • In an unbalanced hierarchy, branches of the hierarchy descend to different levels. Parent-child hierarchies are unbalanced hierarchies.

  • For example, the Organization dimension in the Adventure Works DW Multidimensional 2012 sample Analysis Services database contains a member for each employee. The CEO is the top member in the hierarchy, and the division managers and executive secretary are immediately beneath the CEO. The division managers have subordinate members but the executive secretary does not.



- HuiLing
image

tyt2315

0

When you define cube dimensions, levels, and hierarchies in SAS OLAP Cube Studio, a default hierarchy for a dimension is automatically created if a hierarchy is not explicitly defined. This default hierarchy includes all levels that were specified for the current dimension and the order that they were listed in for the dimension. In addition, if you define multiple hierarchies and do not select a default, then the default is automatically assigned to the first hierarchy that is created for the dimension. On the Dimension Designer – Hierarchy window, you can click the Default button to set a selected hierarchy as the default for the dimension. Defining Multiple Hierarchies for a Dimension Overview SAS OLAP cubes are organized into dimensions and levels of data. The levels are then arranged into hierarchies. After an initial hierarchy has been created, you can define additional hierarchies for a single dimension of a cube. This enables you to have multiple possible drill paths for the same data. When you create more than one hierarchy for a dimension, the levels have some restrictions:

A level in a dimension might be used in more than one hierarchy within that dimension. However, levels cannot be used in hierarchies that are not defined within the dimension that the level is defined in.
Each level must be used in at least one hierarchy.
Levels from the same dimension that are picked for an aggregation must be in the drill order for at least one hierarchy in that dimension.
You cannot share levels between dimensions.

You can arrange the levels in a hierarchy in any order. The one exception to this is the TIME dimension. Levels in hierarchies in the TIME dimension must follow a prescribed order that is determined by the numerical value that is assigned to the type. This order is from the smallest value (Years, 16) to the greatest value (Seconds, 3,096). You can have only one TIME dimension for a cube. The dimension hierarchies also have some restrictions:

The first hierarchy that is defined for the dimension is designated as the default. When there are multiple hierarchies, you can designate the default hierarchy for the dimension.
Hierarchy names must be unique across the cube. If there is a single hierarchy for a dimension, then its name must be the name of the dimension. Also, dimension and hierarchy names cannot be the same as a level name within that dimension.
For any cube that is loaded with a star schema, in which a dimension table represents multiple hierarchies for that dimension, the dimension key that is used to join the dimension table to the fact table will be used for all hierarchies of that dimension.

Defining Ragged and Unbalanced Hierarchies for a Dimension Overview Dimension levels are arranged in one or more hierarchies. Hierarchies, by process of ordering, have a branching arrangement, and the different member levels have parent and child relationships. For example, at company X the sales staff are located in different regions and cities in different countries. A balanced hierarchy might look like this:

global sales president (top of hierarchy)
sales presidents (per country)
regional sales managers
city sales managers

Because of differences in the cube data, hierarchies are often not balanced and possibly have missing members. For example, some sales regions might not have sales managers assigned to a specific city. Or some countries might not have sales regions, just cities. These real-world scenarios would create hierarchies that have missing member data and possibly ragged hierarchies. This affects the drill path of the cube data. You can also drill to missing members within a path and continue to drill down to members that are present. Note: Existing SAS 9.2 OLAP cubes that have been updated with one or more new members for a hierarchy can possibly contain ragged hierarchies and must be rebuilt in SAS 9.4.



- tyt2315
Cancel