Skip to main content

POV String and Query Builder Documentation - Oracle Cloud EPM Modules

Updated

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.

Sections

 

Syntax Basics

The Oracle PBCS and Oracle FCCS POV String syntax is a self-contained query to pull a slice of data from these systems. The query begins and ends with curly braces, { and }. Dimensions are separated by a comma, and the syntax of the member specification is as follows:

"DimensionName":["MemberName1","MemberName2","SetOperator(MemberName3)", etc]

Within the square brackets [ and ] that denotes the members to pull, members and set operators are separated by a comma. Set operators can be used to pull multiple members at once. Available set operators are:

Children(MemberName)
Descendants(MemberName)
@Descendants(MemberName, generationNumber)
IDescendants(MemberName)
Siblings(MemberName)
@LevMbrs(MemberName, levelNumber)
@ILvl0Descendants(MemberName) (Used for level-0 only)

 

POV String Example

{
  "Years":["FY16"],
  "Consolidation":["FCCS_Contribution"],
  "Currency":["Parent Currency","Entity Currency"],
  "Account":["idescendants(FCCS_Balance Sheet)"],
  "Data Source":["FCCS_No Data Source"],
  "Department":["All_Departments"],
  "Entity":["children(FCCS_Total Geography)"],
  "Intercompany":["FCCS_No Intercompany","FCCS_Intercompany Entities"],
  "Movement":["FCCS_Movements","FCCS_CashFlow","FCCS_CashChange"],
  "Period":["Dec"],
  "Product":["Total Product"],
  "Scenario":["Actual"],
  "View":["FCCS_YTD"]
}

 

Substitution Variables

Substitution variables can be used in place of member names to make the POV Strings more dynamic and controllable. When a substitution variable is used, the value of the substitution variable is replace in the POV String at run-time.

POV String example using a substitution variable:

{
  "Years":["&CurrYr"],
  "Consolidation":["FCCS_Contribution"],
  "Currency":["Parent Currency","Entity Currency"],
  "Account":["idescendants(FCCS_Balance Sheet)"],
  "Data Source":["FCCS_No Data Source"],
  "Department":["All_Departments"],
  "Entity":["children(FCCS_Total Geography)"],
  "Intercompany":["FCCS_No Intercompany","FCCS_Intercompany Entities"],
  "Movement":["FCCS_Movements","FCCS_CashFlow","FCCS_CashChange"],
  "Period":["Dec"],
  "Product":["Total Product"],
  "Scenario":["Actual"],
  "View":["FCCS_YTD"]
}

 

Attributes

Many PBCS and FCCS applications contain attribute dimensions. Attribute dimensions contain members that can be associated with a member in a base dimension. By including members from attribute dimensions in your query, you can further refine and filter the result set to the base members that are associated with the attribute member.

Attribute dimensions do not show up in the PBCS Query Builder screen by default. To include an attribute dimension in the POV String, simply add the attribute dimension and member to the POV String as you would any regular dimension.

POV String example with an attribute dimension:

{
  "Years":["FY17"],
  "Consolidation":["FCCS_Contribution"],
  "Currency":["Parent Currency","Entity Currency"],
  "Account":["idescendants(FCCS_Balance Sheet)"],
  "Data Source":["FCCS_No Data Source"],
  "Department":["All_Departments"],
  "Entity":["children(FCCS_Total Geography)"],
  "Intercompany":["FCCS_No Intercompany","FCCS_Intercompany Entities"],
  "Movement":["FCCS_Movements","FCCS_CashFlow","FCCS_CashChange"],
  "Period":["Dec"],
  "Product":["Total Product"],
  "Scenario":["Actual"],
  "View":["FCCS_YTD"],
  "AttributeDimensionName":["AttributeMemberName"]
}

 

Pulling Specific Levels and Generations

Use the @Relative function to pull data for all members at a specific level or generation of the PBCS or FCCS hierarchy. You can also use this function to pull all members for a specific level or generation under a specific member/rollup. 

The format of the @Relative function is as follows:

@Relative(<MemberName>,Generation or Level Number)

MemberName: Any valid member name in the dimension. The choice of member name will determine which members are pulled below or above, depending on the second parameter

Generation or Level Number: Generations are counted from the top of the hierarchy to the bottom. Generations are positive integers, starting at 1. Levels are counted from the bottom of the hierarchy to the top. Levels are negative integers including zero for the bottom level.

For example, consider the following Account hierarchy:

ExoDocumentation2.png

Assume you wanted to pull accounts 4001 and 5000 only, and you also wanted to pull any additional accounts on the same level that were added to the hierarchy in the future. Since 4001 and 5000 are one level up from the bottom, they are level 1 members. Your query would look like this:

..."Account":["@Relative(GP,-1)"]...

GP was chosen as the Member Name to limit the members returned to only accounts below the GP member, and -1 was chosen to pull Level 1 members.

Assume you wanted to pull all level 1 members below NI (Net Income) for reporting purposes. Simply replace the GP member with NI to pull a larger set of level 1 members (all level 1 members below NI):

..."Account":["@Relative(NI,-1)"]...

Generations work similarly. If you wanted to pull all generation 4 members below the AllA (All Accounts) rollup, your syntax would be:

..."Account":["@Relative(AllA,3)"]...

Important Note! If your member name has spaces in it, you must enclose the member name with double quotes and ALSO escape those quotes with a backslash. For example, if you wanted to pull level 2 members below the OpEx before Allocations member, your syntax would be as follows:

@Relative(\"OpEx before Allocations\",-2)

 

Loops

One of the most powerful features of ExoInsight is the ability to create a single query that runs sequentially as multiple smaller queries. This allows ExoInsight to return very large volumes of data while still keeping the processing burden low on the PBCS or FCCS system.

To enable loops with PBCS or FCCS, first associate the base query with the keyword “base”. This defines the core query that is to be run against the source system. Then add a “loops” section that defines the members to loop on. The format can be seen in the highlighted sections below:

{
  "base": 
  {
    "Years":["FY16"],
    "Consolidation":["FCCS_Contribution"],
    "Currency":["Parent Currency","Entity Currency"],
    "Account":["idescendants(FCCS_Balance Sheet)"],
    "Data Source":["FCCS_No Data Source"],
    "Department":["All_Departments"],
    "Entity":["children(FCCS_Total Geography)"],
    "Intercompany":["FCCS_No Intercompany","FCCS_Intercompany Entities"],
    "Movement":["FCCS_Movements","FCCS_CashFlow","FCCS_CashChange"],
    "Period":["Dec"],
    "Product":["Total Product"],
    "Scenario":["Actual"],
    "View":["FCCS_YTD"]
  },
  "loops": 
  {
    "Years":["FY17","FY18"],
    "Data Source":["FCCS_No Data Source","FCCS_TotalInputAndAdjusted"]
  }
}

Using the syntax above, the query defined in the “base” section will be run for the complete set of members defined in the loops section. In this example, the base query will be run four times, with the Years and Data Source members as follows:

FY17, FCCS_No Data Source
FY17, FCCS_TotalInputAndAdjusted
FY18, FCCS_No Data Source
FY18, FCCS_TotalInputAndAdjusted

Once these four queries are executed sequentially against the source system, ExoInsight will present them as a single combined result set into the destination system.

Please note that the Years and Data Source dimensions are defined in both the base and loops section. When this happens, the members in the loops section will be used and the members in the base section will be ignored. Also please note that in order to use a dimension in the loops section, at least two members must be defined.

Associating Dimensions

There are often situations that require members of one dimension to be used only with specific members from another dimension. For example, you could have a requirement to pull Actual data with FY20 and Budget data for FY21 and FY22. If you used the standard POV String syntax to pull this data like in the following POV String snippet:

{
...
"Years":["FY20","FY21","FY22"],
"Scenario":["Actual","Budget"],
...
}

Then data for the following combinations of Scenario and Years would be returned:

Actual, FY20
Actual, FY21
Actual, FY22
Budget, FY20
Budget, FY21
Budget, FY22

This results in more data being returned than is required (the combinations in red are unnecessary for the data requirements) which can cause slower query performance and more data to process.

For more precise data requirements such as the example above, ExoInsight allows you to associate members from one dimension with specific members of another dimension. To do this, you can use the Member_Combinations_Rows and Member_Combinations_Columns sections of the POV String.

Consider the following SmartView retrieve:

ExoDocumentation1.png

In the section highlighted in red, FY20 is being pulled for Actuals. In the section highlighted in blue, FY21 and FY22 are being pulled for Budget. You can use the Member_Combinations_Rows section to properly represent this query without pulling extraneous data:

"Member_Combinations_Rows":
[
  {"Scenario":["Actual"],"Year":["FY20"]},
  {"Scenario":["Budget"],"Year":["FY21"]},
  {"Scenario":["Budget"],"Year":["FY22"]}
]

The full POV String for this query would be as follows:

{
  "base":
  {
    "Product":["Total Product"],
    "Entity":["Total Entity"],
    "Version":["Working"],
    "Account":["Net Income"],
    "HSP_View":["BaseData"],
    "Period":["YearTotal","Qtr1","Qtr2","Qtr3","Qtr4"]
  },
  "Member_Combinations_Rows":
  [
    {"Scenario":["Actual"],"Year":["FY20"]},
    {"Scenario":["Budget"],"Year":["FY21"]},
    {"Scenario":["Budget"],"Year":["FY22"]}
  ]
}

Member_Combinations_Columns works in a similar way for the dimensions specified in the columns. 

Important notes:

  • Zero, one, or both of Member_Combinations_Rows and Member_Combinations_Columns can be used in a POV String.
  • If only using one, performance will be better using Member_Combinations_Rows.
  • The suppress missing operator will only apply to Member_Combinations_Rows.
  • If a dimension is defined in Member_Combination_Rows or Member_Combinations_Columns, it cannot also be defined in the base section of the query.

Dynamic Time Series

You can reference Dynamic Time Series (DTS) functions in your POV String if they have already been set up in your PBCS or FCCS application.

DTS functions can be referenced in your PBCS/FCCS POV Strings as you would any other member. The general syntax for DTS functions is as follows:

<DTS Function Name>(<MonthName>)

For example, if your PBCS of FCCS application has the half-to-date DTS function set up, you could reference the February value with the following syntax:

H-T-D(Feb)

When calling a DTS member, PBCS does not allow you to utilize the suppress missing option while referencing the Period dimension in a row. In order to always be assured that the Period dimension is not being referenced in a row, utilize the Member_Combinations_Columns syntax as described in the section above.

The following PBCS query allows you to pull back January data, February data, and the half-to-date value of February, which is the values of January and February combined based on the DTS function definition (note that the Period dimension has been removed from the base section of the query and added to the Member_Combinations_Columns section):

{
  "base":
  {
     "HSP_View":["BaseData"],
     "Product":["P_TP"],
     "Scenario":["Actual"],
     "Version":["Working"],
     "Year":["FY22","FY21"],
     "Entity":["100","200"],
     "Account":["descendants(NI)"]
  },
  "Member_Combinations_Columns":
  [
     {"Period":["Jan"]},
     {"Period":["Feb"]},
     {"Period":["H-T-D(Feb)"]}
  ]
}

 

 

 

Related articles

Powered by Zendesk