POV String and Query Builder Documentation - Oracle Essbase Module – Casabase Software

The POV string is a user-friendly language developed by Casabase Software designed to provide a familiar and intuitive way to pull back data intersections (or data “slices”) from a source.

While syntax differs slightly depending on the data source, the POV String enables users to easily construct queries and pull data using their favorite reporting or analytic tool.

Syntax Basics

The Oracle Essbase POV String is dash-semicolon (-;) delimited between dimensions and pipe-delimited (|) between members. Members are contained in square brackets [ and ]. You can specify more than one member per dimension (separated by pipe). You can specify one or more dimensions, and not all dimensions must be specified. The order the dimensions appear is not important. The POV String begins with “ExoPowerPOV”.

The following POV String could be used against the Sample.Basic Essbase application:

ExoPowerPOV-;Children([South])|[California]-;Descendants([Year])-;100

This POV String will pull all children of South and the member of California for the Entity dimension, all descendants of Year for the Period dimension, and member 100 from the Product dimension. Children([South]) and [California] are separated by a pipe (|) because they are from the same dimension. There is a dash-semicolon (-;) after California because it signals the last member for that dimension and members from a new dimension will be specified next.

Notice that the POV String allows the use of Children() and Descendants(). Other set operators, such as Members(), Attribute(), and Ancestors(), are also available.

NONEMPTY

In order to filter out missing values, or rows that do not contain data for a particular intersection, the NONEMPTY keyword may be used.

ExoPowerPOV-;NONEMPTY-;[100]-;Members([Year])-;Children([South])|[New York]

NONEMPTY is not case-sensitive and may be placed anywhere in the string as long as it is separated from the other dimensions by a dash-semicolon (-;).

By using the NONEMPTY keyword, the result set returned from the OLAP source will eliminate all empty rows (please note that zero is not an empty row and will be returned). This helps increase the speed of the query as well as the time it takes the downstream application to process the result set. There will rarely be a time that you will not want to use the NONEMPTY keyword.

Alias Tables

All alias tables are available to display in the result set. To add an alias table to the string, add the name of the alias table to the POV String embedded in brackets:

ExoPowerPOV-;NONEMPTY-;[Alias=GermanNames]-;Members([Year])-;Children([South])

Aliases are always available in the metadata (dimensions) returned by ExoInsight, but including the aliases in the data result set can often make reporting easier, allowing you to access them without having to include joins to the metadata tables.

Essbase Substitution Variables

You may use substitution variables by incorporating them directly in the POV String. To do so, simply add the substitution variable to the POV String:

ExoPowerPOV-;NONEMPTY-;[100]-;Members([Year])-;Children([&MyMarket])|[California]

Note that substitution variables can be used stand-alone as a member, or as part of a set operation.

Essbase substitution variable names and values can be viewed directly in the outline:

ExoInsight Variables

(Valid for version 8.7.0 and higher)

ExoInsight Variables (ExoVars) behave similarly to Essbase substitution variables, except you define and maintain them in ExoInsight, outside of Essbase. Adding an ExoVar to the POV String or MDX query will allow you to create dynamic queries that can be updated automatically by updating the ExoVar in ExoInsight.

ExoVars can be utilized in both POV String and MDX queries.

The syntax is as follows:

[ExoVar=MyVariableName]

MyVariableName is the name of the ExoVar defined in ExoInsight. Upon execution, the value of the ExoVar replaces [ExoVar=MyVariableName], including the square brackets.

ExoVar values can be anything: member names, set operators, a partial POV String query, etc.

In the example below, [ExoVar=ReportingMarket] will be replaced with the value of the ReportingMarket ExoVar as defined in ExoInsight:

ExoPowerPOV-;NONEMPTY-;[100]-;Members([Year])-;Children([ExoVar=ReportingMarket])|[California]

If the ReportingMarket ExoVar value was "[New York]", then the resulting POV String would be:

ExoPowerPOV-;NONEMPTY-;[100]-;Members([Year])-;Children([New York])|[California]

ExoVar names and values can be viewed directly in the outline:

Attribute Dimensions

Essbase Attribute dimensions can be used in the POV String the same way a normal dimension is: simply add it to the POV String. In the example below, the Bottle member from the Pkg Type attribute dimension is added to the POV String, limiting the result set to those Products associated with Bottle:

ExoPowerPOV-;NONEMPTY-;[100]-;[Bottle]-;Members([Year])-;Children([&MyMarket])

Numeric, Date, and Boolean attribute dimensions will need to be clarified with the attribute dimension name. For example, to filter the results set by only the 32 ounce bottles, prefix the attribute dimension name and an underscore (_) character:

ExoPowerPOV-;NONEMPTY-;[Ounces_32]-;Members([Year])-;Children([Market])|[California]

UDAs

UDAs can be accessed by using the UDA() command. The UDA() command takes the dimension name as the first parameter, then the UDA to be used as the second parameter:

ExoPowerPOV-;NONEMPTY-;Members([Year])-;UDA([Market],"Major Market")

Noticed that since the dimension specified in the UDA() command was already referenced in the POV String (Children([Market])), the UDA() command is added to the Market dimension specification by adding a pipe (|) character.

Pulling Specific Levels and Generations

Levels

You can pull data for all members at a specific level or generation of the Essbase hierarchy.

To pull data for all members in a dimension at a specific level, use the following syntax:

[DimensionName].Levels(LevNum).members

The example below pulls all members at level 2 of the Year dimension:

ExoPowerPOV-;NONEMPTY-;[Year].Levels(2).members-;descendants([Measures])-;[Actual]-;descendants([Total Products])-;

To pull data for all members below a specific member within a dimension at a specific level, use the following syntax:

Descendants([MemberName],[DimensionName].Levels(LevNum))

The example below pulls all level-0 members below the member 100 in the Product dimension:

ExoPowerPOV-;NONEMPTY-;Descendants([100],[Product].Levels(0))-;descendants([Profit])-;[Actual]

Generations

To pull data for members at a specific generation, use the following syntax:

[DimensionName].Generations(GenNum).members

The example below pulls all members at generation 3 of the Year dimension:

ExoPowerPOV-;NONEMPTY-;[Year].Generations(3).members-;descendants([Profit])-;[Actual]

To pull data for all members below a specific member within a dimension at a specific generation, use the following syntax:

Descendants([MemberName],[DimensionName].Generations(GenNum))

The example below pulls all level-0 members below the member 100 in the Product dimension:

ExoPowerPOV-;NONEMPTY-;Descendants([100],[Product].Generations(3))-;descendants([Profit])-;[Actual]

Removing Shared Members

(Valid for version 8.7.0 and higher)

Removing shared members from a pull can be done by using the NONSHARED syntax:

NONSHARED([MemberSet],[DimensionName])

Note that [MemberSet] must be a set operator, such as level0(), descendants(), children(), etc.

For example, to pull back all level 0 members of Product except members tagged as shared, the following syntax can be used:

ExoPowerPOV-;NONEMPTY-;NONSHARED(level0([Product]),[Product])-;descendants([Profit])-;[Actual]

Removing Specific Members

Removing specific members from a set of members can be done using the EXCEPT syntax. To

Except([MemberSet],[MembersToRemove])

Note that [MemberSet] must be a set operator, such as level0(), descendants(), children(), etc.

For example, to remove all descendants of 200 from the members returned by pulling all descendants of Product, the following syntax can be used:

ExoPowerPOV-;NONEMPTY-;Except(descendants([Product]),Descendants([200]))-;descendants([Profit])-;[Actual]

Loops

Loops is one of the most powerful features of ExoInsight. The loops functionality enables you to take a large, long running query and break it down into multiple smaller queries that are run against the target cube sequentially.

To utilize loops in a POV String, add a loops clause to the POV String using the following syntax:

{loops=ExoPowerPOV-;Dim1Mem1|Dim1Mem2|Dim1MemX-;Dim2Mem1|Dim2Mem2|Dim2MemX}

For example, if you had the following POV String:

ExoPowerPOV-;NONEMPTY-;level0([Year])-;[East]|[West]-;descendants([Profit])-;descendants([Product])-;[Actual]-;

You would get a result set from a single query against the cube. However, you may want to loop on the Market dimension, running a separate query against the cube for East and for West. To do this, move the Market dimension to the loops clause:

ExoPowerPOV-;NONEMPTY-;level0([Year])-;descendants([Profit])-;descendants([Product])-;[Actual]-;{loops=ExoPowerPOV-;[East]|[West]}

This would cause the following queries to be run against the source cube sequentially:

ExoPowerPOV-;NONEMPTY-;level0([Year])-;descendants([Profit])-;descendants([Product])-;[Actual]-;[East]

ExoPowerPOV-;NONEMPTY-;level0([Year])-;descendants([Profit])-;descendants([Product])-;[Actual]-;[West]

ExoInsight will then consolidate the results and present them as a final consolidated result set.

The loops clause can contain any valid POV String. One or more dimensions can be used in the loops clause. The base POV String query will be run once for every combination of members that are contained in the loops clause.

Consider the following POV String with a loops clause:

ExoPowerPOV-;NONEMPTY-;level0([Year])-;descendants([Profit])-;descendants([Product])-;[Actual]-;{loops=ExoPowerPOV-;[East]|[West]-;[FY19]|[FY20]}

This would cause four total queries to be run against the source cube. The base query would be run for the following combinations of loops members:

East, FY19
East, FY20
West, FY19
West, FY20

Associating Dimensions

Dimensions can be associated in the POV String if particular combinations of members across dimensions need to be achieved. This is helpful when it’s important to replicate the exact combination of members that are utilized in the rows or columns of a SmartView or ExoInsight GSheet Add-In retrieve.

To associate dimensions in the POV String, surround them with parenthesis. For example, if you only wanted the Sales member to be pulled for FY19 and the Profit member to be pulled for FY20, enclose those combinations in parenthesis as follows:

ExoPowerPOV-;NONEMPTY-;level0([Year])-;([Sales],[FY19])|([Profit],[FY20])-;descendants([Product])-;[Actual]-;

Note that if you associate two dimensions in a POV String, then every instance of a member from either of those dimensions must be paired with a member from the other dimension.