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. The POV String syntax for HFM is modeled heavily after the standard HFM query string syntax.
Sections
Syntax Basics
The Oracle HFM POV String is period delimited between dimensions and semi-colon delimited between members within a dimension. Each dimension must be represented in a POV String query, and the members of a particular dimension are preceded by the dimension abbreviation and a hashtag (#) symbol.
The following POV String could be used against the SIMPLE4DIM sample HFM application:
S#Actual.Y#2004;2005.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.
This POV String identifies specific members to pull data for as well as set operators that enables multiple members to be pulled at a single time. Standard set operators are:
[MemberName].[children] : Pulls the members directly below [MemberName] in the outline
[MemberName].[descendants] : Pulls all the members below [MemberName] down to the base level
[MemberName].[base] : Pulls all base (bottom) level members below [MemberName] in the outline
[MemberName].[ancestors] : Pulls all members above [MemberName] in the outline
[MemberName].[parents] : Pulls the member directly above [MemberName] in the outline
Each dimension must be represented in the POV String and the members of the dimension must be preceded by the dimension abbreviation. The common dimension abbreviations are as follows:
- S: Scenario
- Y: Year
- P: Period
- W: View
- E: Entity
- V: Value
- A: Account
- I: ICP
- C1: Custom 1
- C2: Custom 2
- C3: Custom 3
- C4: Custom 4
Dimensions that have a single member or multiple single members specified will be preceded with the dimension abbreviation and a hashtag (#) symbol:
Y#2004;2005
Dimensions that utilize a set operator will have the dimension abbreviation followed by the set operator within curly braces { and }:
E{[Regional].[Base]}
Suppress Missing
In order to filter out missing values, or rows that do not contain data for a particular intersection, the Suppress Missing flag needs to be set. The method to set the flag depends on how the call to HFM is being made. Some of the most common examples are shown below.
Note: There are very few situations where Suppress Missing will not be checked. Unchecking Suppress Missing will significantly increase the number of records and query time.
Query Builder
Alteryx
Tableau
REST API
{
"license": "Enterprise",
"env": "HFM",
"application": "SIMPLE4DIM",
"user": "admin",
"password": "EDeImf952v/CDz/6a6dbm6Za0IZb59sdsmkdroRyF6s=",
"suppress": true,
"dimension": "all",
"savedpov": "HFM.SIMPLE4DIM.SIMPLE4DIM.2004_2005_Management_Actuals"
}
ExoInsight Variables
(Valid for version 8.7.0 and higher)
ExoInsight Variables (ExoVars) allows the creating and maintaining of variables that can be referenced in your HFM queries to make them more dynamic. Adding an ExoVar to the POV String will allow you to create dynamic queries that can be updated automatically by updating the ExoVar in ExoInsight.
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=Products] will be replaced with the value of the Products ExoVar as defined in ExoInsight:
S#Actual.Y#2004;2005.C1#[ExoVar=Products].C2#AllCustomers.C3#[None].C4#ClosingBalance.A#NetProfit.E{[Regional].[descendants]}.P{[[Year]].[descendants]}.W#<Scenario View>.V#<Entity Currency>.I#[ICP Top]
ExoVar names and values and be viewed directly in the outline:
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=Dim1Prefix#MemberList.Dim2Prefix#MemberList...]
For example, if you had the following POV String:
S#Actual.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.Y#2004;2005;2006.
You would get a result set from a single query against the cube. However, you may want to loop on the Year dimension, running a separate query against the cube for 2004, 2005, and 2006 for a total of three separate, smaller queries. To do this, move the Year dimension to the loops clause:
S#Actual.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.[loops=Y#2004;2005;2006.]
This would cause the following queries to be run against the source cube sequentially:
S#Actual.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.Y#2004.
S#Actual.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.Y#2005.
S#Actual.P{[[Year]].[descendants]}.W#<Scenario View>.E{[Regional].[Base]}.V#<Entity Currency>.A{[NetProfit].[descendants]}.I#[ICP Top].C1#AllProducts.C2#AllCustomers.C3#[None].C4#ClosingBalance.Y#2006.
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.
Casabase Software