Package net.pricefx.formulaengine
Interface DatamartContext
-
public interface DatamartContext
Provides an API to query and load PA data from a formula context.Example:
This example demonstrates how to run a query to read data from PriceAnalyzer datamart and return it as in a form of
ResultMatrix
def ctx = api.getDatamartContext() def dm = ctx.getDatamart("txDM") def query = ctx.newQuery(dm) query.select("invoiceDateQuarter", "Q") query.select("SUM(amount)", "R") query.orderBy("Q") return ctx.executeQuery(query)?.data?.toResultMatrix().entries
- See Also:
newQuery(Table)
,executeQuery(Query)
,ResultMatrix
-
-
Nested Class Summary
Nested Classes Modifier and Type Interface Description static interface
DatamartContext.Calendar
Helper class mainly for working with dates and time periods as used in the PriceAnalyzer module.static interface
DatamartContext.DataLoader
API for loading data into aDMDataFeed
orDMTable
from a formula context.static interface
DatamartContext.DataProfilerResult
static interface
DatamartContext.DataSlice
A DataSlice defines a set of filters to be applied to a PA DataSet (Datamart, DataSource...).static interface
DatamartContext.Query
API for building a PA query in a formula context.
-
Method Summary
All Methods Instance Methods Abstract Methods Deprecated Methods Modifier and Type Method Description ResultPAQuery
buildQuery(DatamartContext.Query query)
EXPERIMENTAL: Not all aspects and properties of a query are supported.DMCalendar
calendar()
Instantiates a PA Calendar utility object.void
consumeData(DatamartContext.Query query, Closure consumer)
Object
dimFilterEntry(String entryName, Column column)
DimFilter input parameter: renders a selection of all possible values for the given dimension field, in the FC which the column's table represents.Object
dimFilterEntry(String entryName, Column column, String defaultValue)
DimFilter input parameter: renders a selection of all possible values for the given dimension field, in the FC which the column's table represents.DatamartQueryResult
executeQuery(DatamartContext.Query query)
Executes the givenDatamartContext.Query
.Matrix2D
executeSqlQuery(String sql, Object... sources)
Executes an ANSI compliant SQL SELECT statement in the PA DB.
Important note: A non-compliant statement that does not fail at the present time may well fail in the future releases.
The DB schema that can be queried is constructed on the fly by means of view definitions.Matrix2D
executeSqlQuery(DMSqlQuery sqlQuery)
Executes an ANSI compliant SQL SELECT statement in the PA DB.Object
fieldSelectionEntry(String entryName, String sourceName)
Object
fieldSelectionEntry(String entryName, String sourceName, Collection<String> sTypes, Boolean multiple)
Object
fieldSelectionEntry(String entryName, Table table, String sType)
FC field selector, optionally limited to fields of a given type.Object
fieldSelectionEntry(String entryName, Table table, String sType, Boolean multiple)
FC field selector, optionally limited to fields of a given type.Table
getDataFeed(String name)
Gets a table object representing a DataFeed with the given name.Table
getDatamart(String name)
Gets a table object representing a Datamart with the given name.Table
getDataSource(String name)
Gets a table object representing a DataSource with the given name.Table
getFieldCollection(String sourceName)
Get a table object representing the FC with the given source name.Table
getModel(String name)
Gets a table object representing a Model with the given name.Table
getRollup(String label)
Gets a table object representing a FieldCollection representing a rollup with the given label.DatamartContext.DataLoader
newDataLoader(Table table)
DatamartContext.DataLoader
newDataLoader(Table table, List<String> headerFieldNames)
DatamartContext.DataSlice
newDatamartSlice()
Creates a new DatamartSlice which allows for setting filter criteria along the Time, CustomerGroup, ProductGroup or any other dimension in a Datamart.Object
newDatamartSlice(String dateFieldName, Object... timePeriodsAndProductAndCustomerGroups)
Creates a new DatamartSlice which allows for setting filter criteria along the Time, CustomerGroup, ProductGroup or any other dimension in a Datamart, initialized with the name of the time dimension field and an optional set of filter criteria.DatamartContext.Query
newQuery(DatamartContext.Query otherQuery)
Creates a new query from an existing one.DatamartContext.Query
newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap)
Deprecated.DatamartContext.Query
newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap, boolean rollup)
Deprecated.DatamartContext.Query
newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap, String joinMode, boolean rollup)
Deprecated.DatamartContext.Query
newQuery(Table table)
DatamartContext.Query
newQuery(Table table, boolean rollup)
Builds a new query on the given table.DMSqlQuery
newSqlQuery()
Instantiates a query object for building SQL statements from source queries, with clauses and parameter bindings.DatamartContext.DataProfilerResult
profileData(DatamartContext.Query query)
Calculates "Min", "Max", "#", "#Nulls", "#Distinct", "Sample" for dimension projections, and "Min", "Max", "Mean", "Std", "#", "#Nulls" for numeric projections.Object
sourceSelectionEntry(String entryName, String... typeCode)
StreamResults
streamQuery(DatamartContext.Query query)
Executes the givenDatamartContext.Query
and returns a result, so that one row at the time can be examined.
-
-
-
Method Detail
-
calendar
DMCalendar calendar()
Instantiates a PA Calendar utility object.- Returns:
Calendar
-
getDataFeed
Table getDataFeed(String name)
Gets a table object representing a DataFeed with the given name. A reference to this table can be used when building aDatamartContext.Query
on DataFeedLoad.- Parameters:
name
- sourceName, uniqueName or label of the DF.- Returns:
- Table representing the DF in DataContext.
-
getDataSource
Table getDataSource(String name)
Gets a table object representing a DataSource with the given name. A reference to this table can be used when building aDatamartContext.Query
on that DataSource.- Parameters:
name
- sourceName, uniqueName or label of the DS.- Returns:
- Table representing the DS in DataContext.
-
getDatamart
Table getDatamart(String name)
Gets a table object representing a Datamart with the given name. A reference to this table can be used when building aDatamartContext.Query
on that Datamart.- Parameters:
name
- sourceName, uniqueName or label of the DM.- Returns:
- Table representing the DM in DataContext.
-
getModel
Table getModel(String name)
Gets a table object representing a Model with the given name. A reference to this table can be used when building aDatamartContext.Query
on that Model.- Parameters:
name
- sourceName, uniqueName or label of the Model.- Returns:
- Table representing the Model in DataContext.
-
getFieldCollection
Table getFieldCollection(String sourceName)
Get a table object representing the FC with the given source name. A source name consists of two parts, separated with a '.':- 'DMF', 'DMDS', 'DM' or 'DMSIM', i.e. the typeCode of DataFeed, DataSource, Datamart and Sim-Datamart resp.
- the FC's uniqueName
DatamartContext.Query
on that FC.- Parameters:
sourceName
- The sourceName of the FC.- Returns:
- Table representing the FC in the DataContext.
-
getRollup
Table getRollup(String label)
Gets a table object representing a FieldCollection representing a rollup with the given label. If more than one such rollup exists, the first one found is returned, in no particular or predefined order.- Parameters:
label
- Label of the Rollup.- Returns:
- Table representing the FC form of the rollup in DataContext.
-
newQuery
DatamartContext.Query newQuery(Table table)
- Parameters:
table
- Table representing the FC to query.- Returns:
DatamartContext.Query
builder.
-
newQuery
DatamartContext.Query newQuery(Table table, boolean rollup)
Builds a new query on the given table.- Parameters:
table
- Represents the FC to query.rollup
- Line level or rollup query.- Returns:
DatamartContext.Query
builder.
-
newQuery
DatamartContext.Query newQuery(DatamartContext.Query otherQuery)
Creates a new query from an existing one. This pattern can be used to build a base or template query, and then instantiate several variants, for example each for a different DataSlice (this year sales data vs. last year etc.).- Parameters:
otherQuery
- Query to use as a basis for a new query.- Returns:
DatamartContext.Query
builder.
-
newQuery
@Deprecated DatamartContext.Query newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap)
Deprecated.
-
newQuery
@Deprecated DatamartContext.Query newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap, boolean rollup)
Deprecated.
-
newQuery
@Deprecated DatamartContext.Query newQuery(DatamartContext.Query query1, DatamartContext.Query query2, LinkedHashMap<String,String> joinFieldsMap, String joinMode, boolean rollup)
Deprecated.The preferred method for running a join query is nowexecuteSqlQuery(java.lang.String, java.lang.Object...)
.- Parameters:
query1
- Sub-source1 represented by the result of query1.query2
- Sub-source2 represented by the result of query2.joinFieldsMap
- Map of fields in source1 to fields in source1, representing the join condition.joinMode
- How to combine source1 and source2 into one, to be the source to this query. Note that the source field names adhere to different naming schemes depending on the join mode:- 'INNER': Join semantics as in the SQL inner join. If source2 has the same named fields as source1, they are not added to the combined source.
- 'LEFT_OUTER': As in SQL left outer join. If source2 has same named fields as source1, they are not added to the combined source.
- 'FULL_OUTER': As in SQL full outer join. Fields in the combined source get a '_<query.alias>' postfix (defaults to '_1' for source1 and '_2' for source2).
- 'INNER_ALL': As in SQL inner join. Fields in the combined source get a postfix as above.
- 'LEFT_OUTER_ALL': As in SQL left outer join. Fields in the combined source get a postfix as above.
rollup
- True if this is to be a rollup query, i.e. with a Group by clause.- Returns:
- New query (select) on the combined source.
-
executeQuery
DatamartQueryResult executeQuery(DatamartContext.Query query) throws InterruptedException
Executes the givenDatamartContext.Query
. For a rollup query, if the internal row limit (set by 'datamart.query.internalRowLimit' Pricefx instance param) is exceeded, anull
is returned. The rationale for this behaviour is that a rollup, or so called analytical query result, is unreliable if not all data in scope could be examined. This is different to a fetch or paging query, which can safely request one page at the time. In syntax check mode, a maximum of 200 rows is returned in all cases. Sample code:def ctx = api.getDatamartContext() def dm = ctx.getDatamart("Transaction DM") def query = ctx.newQuery(dm) query.select("CustomerId") query.select("MaterialID") query.select("SUM(Sales)", "Revenue") query.select("SUM(Quantity)", "Volume") def result = ctx.executeQuery(query) for (def r=0; r < result.data.getRowCount(); r++){ def row = result.data.getRowValues(r) // row #r as map api.trace("query", "row $r", row) }
- Parameters:
query
-DatamartContext.Query
to execute.- Returns:
DatamartQueryResult
providing data in the Matrix2D form and summary information in the map form.- Throws:
InterruptedException
-
streamQuery
StreamResults streamQuery(DatamartContext.Query query) throws InterruptedException
Executes the givenDatamartContext.Query
and returns a result, so that one row at the time can be examined. This is different toexecuteQuery
, which always returns the full data set in the scope of the query. When a row is retrieved and moved onwards from, it is no longer available to the client code. The typical usage is to consume the result row by row, processing it into some accumulating data structure.
Important note: streamQuery is not executed in the syntax check mode and returns null.
Sample code:
def ctx = api.getDatamartContext() def dm = ctx.getDatamart("Transaction DM") def query = ctx.newQuery(dm) query.select("CustomerId") query.select("MaterialID") query.select("SUM(Sales)", "Revenue") query.select("SUM(Quantity)", "Volume") def results = ctx.streamQuery(query) def r=0 while(results.next()){ def row = results.get() // current row as map api.trace("streamQuery", "row $r", row) r++ } results.close()
- Parameters:
query
-DatamartContext.Query
to execute.- Returns:
- StreamResults Similar to a JDBC ResultSet, but only implementing next(), get() and close().
- Throws:
InterruptedException
-
executeSqlQuery
Matrix2D executeSqlQuery(String sql, Object... sources) throws InterruptedException
Executes an ANSI compliant SQL SELECT statement in the PA DB.
Important note: A non-compliant statement that does not fail at the present time may well fail in the future releases.
The DB schema that can be queried is constructed on the fly by means of view definitions. A view is defined using the usual query API. At least one view needs to be defined. The first view gets an alias 'T1', the second 'T2' etc.
The columns of a view are named from the defining query projections' aliases. Note that in the SQL standard the identifiers need to be double-quoted to preserve the case. Therefore, unless a projection alias is all lowercase, the SQL statement will need to double quote references to a view's column names.Example:
def ctx = api.getDatamartContext() def dm = ctx.getDatamart("TransactionsDM") def ds = ctx.getDataSource("ForecastDS") def t1 = ctx.newQuery(dm) t1.select("ProductID", "product") t1.select("SUM(InvoicePrice)", "revenue") t1.select("SUM(Quantity)", "volume") def t2 = ctx.newQuery(ds, false) t1.select("ProductID", "product") t2.select("Revenue, "revenue") t2.select("Volume", "volume") def sql = """ SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) """ return ctx.executeSqlQuery(sql, t1, t2)?.toResultMatrix()
- Parameters:
sources
- Views that make up the DB schema that can be queried, in the form of query definitions of typeDatamartContext.Query
or Strings representing 'SELECT' statements that will be added to the final SQL statement's WITH clause. The sources are assigned the 'Ti' relation alias in the order of appearance in the source Collection ((T1 for the first source).- Returns:
- Query result as a Matrix2D object.
- Throws:
InterruptedException
-
newSqlQuery
DMSqlQuery newSqlQuery()
Instantiates a query object for building SQL statements from source queries, with clauses and parameter bindings. To be executed byexecuteSqlQuery(java.lang.String, java.lang.Object...)
.Example:
def ctx = api.getDatamartContext() def dm = ctx.getDatamart("TransactionsDM") def ds = ctx.getDataSource("ForecastDS") def t1 = ctx.newQuery(dm) .select("ProductID", "product") .select("ProductGroup", "PG") .select("SUM(InvoicePrice)", "revenue") .select("SUM(Quantity)", "volume") def t2 = ctx.newQuery(ds, false) .select("ProductID", "product") .select("Revenue, "revenue") .select("Volume", "volume") def sqlQuery = ctx.newSqlQuery() .addSource(t1) .addSource(t2) def with = """ SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) WHERE T1.PG = ? """ sqlQuery.addWith(with, "PG-ABC") // binding some product group value; with-clause gets assigned the T3 alias def sql = " SELECT SUM(ActualRevenue) - SUM(ForecastRevenue) FROM T3 " sqlQuery.setQuery(sql) return ctx.executeSqlQuery(sqlQuery)?.toResultMatrix()
- Returns:
- New DMSqlQuery object.
-
executeSqlQuery
Matrix2D executeSqlQuery(DMSqlQuery sqlQuery) throws InterruptedException
Executes an ANSI compliant SQL SELECT statement in the PA DB.- Parameters:
sqlQuery
- SQL statement definition.- Returns:
- Query result as a Matrix2D object.
- Throws:
InterruptedException
-
consumeData
void consumeData(DatamartContext.Query query, Closure consumer) throws Exception
- Parameters:
query
-consumer
-- Throws:
Exception
-
buildQuery
ResultPAQuery buildQuery(DatamartContext.Query query)
EXPERIMENTAL: Not all aspects and properties of a query are supported. For example, currently not supported are dim filters, row limit, join queries etc.
Builds and validates a query to be rendered in the client. Technically, a DataTransferObject representing the query is created, compatible with the query format in the REST API.
- Parameters:
query
- Query to be rendered in the client.- Returns:
- ResultPAQuery (calculation result) embedding a REST API compatible map representation of the query.
-
profileData
DatamartContext.DataProfilerResult profileData(DatamartContext.Query query)
Calculates "Min", "Max", "#", "#Nulls", "#Distinct", "Sample" for dimension projections, and "Min", "Max", "Mean", "Std", "#", "#Nulls" for numeric projections.- Parameters:
query
- Query defining the data to a profile.- Returns:
- DataProfilerResult split up in dimensions and numeric projections results.
-
newDatamartSlice
DatamartContext.DataSlice newDatamartSlice()
Creates a new DatamartSlice which allows for setting filter criteria along the Time, CustomerGroup, ProductGroup or any other dimension in a Datamart.- Returns:
- Instantiated, empty
DatamartContext.DataSlice
object.
-
newDatamartSlice
Object newDatamartSlice(String dateFieldName, Object... timePeriodsAndProductAndCustomerGroups)
Creates a new DatamartSlice which allows for setting filter criteria along the Time, CustomerGroup, ProductGroup or any other dimension in a Datamart, initialized with the name of the time dimension field and an optional set of filter criteria.- Parameters:
dateFieldName
- Name of the time dimension field.timePeriodsAndProductAndCustomerGroups
- TimePeriod, CustomerGroup, ProductGroup filters.- Returns:
- Initialized
DatamartContext.DataSlice
object.
-
dimFilterEntry
Object dimFilterEntry(String entryName, Column column)
DimFilter input parameter: renders a selection of all possible values for the given dimension field, in the FC which the column's table represents.- Parameters:
entryName
- Input param name.column
- Columns from the table representing the FC to get a dimension field value from.- Returns:
- Selected dim field value.
-
dimFilterEntry
Object dimFilterEntry(String entryName, Column column, String defaultValue)
DimFilter input parameter: renders a selection of all possible values for the given dimension field, in the FC which the column's table represents.- Parameters:
entryName
- Input param name.column
- Columns from the table representing the FC to get a dimension field value from.defaultValue
- Value to use if no value has been selected yet.- Returns:
- Selected dim field value or the default value if not yet set.
-
fieldSelectionEntry
Object fieldSelectionEntry(String entryName, Table table, String sType)
FC field selector, optionally limited to fields of a given type.- Parameters:
entryName
- Input param name.table
- Table that represents the FC to select a field from.sType
- Type of the field to allow the user to select:- NUMBER
- QUANTITY
- TEXT
- DATE
- MONEY
- CURRENCY
- UOM
- LOB
- DATETIME
- Returns:
- Selected FC field.
-
fieldSelectionEntry
Object fieldSelectionEntry(String entryName, Table table, String sType, Boolean multiple)
FC field selector, optionally limited to fields of a given type.- Parameters:
entryName
- Input param name.table
- Table that represents the FC to select a field from.multiple
- Allow one field or multiple fields selection.- Returns:
- Selected FC fields' names.
-
fieldSelectionEntry
Object fieldSelectionEntry(String entryName, String sourceName, Collection<String> sTypes, Boolean multiple)
-
newDataLoader
DatamartContext.DataLoader newDataLoader(Table table)
Instantiates a newDatamartContext.DataLoader
to load rows of data into aDMDataFeed
orDMTable
. ADatamartContext.DataLoader
works in any logic context, as opposed toDatamartRowSet
which is available only in a PA DataLoad context.Example:
def ctx = api.getDatamartContext() def df = ctx.getDataFeed("Contract_Log") def loader = ctx.newDataLoader(df) loader.addRow(["ApprovalDate": date, "ApprovalUser": user])
- Parameters:
table
- Table representing aDMDataFeed
orDMTable
to load data in.- Returns:
DatamartContext.DataLoader
instance providing an API to add (buffer) and flush (commit to the DB) rows to a feed/table.
-
newDataLoader
DatamartContext.DataLoader newDataLoader(Table table, List<String> headerFieldNames)
Instantiates a newDatamartContext.DataLoader
to load rows of data into aDMDataFeed
orDMTable
. ADatamartContext.DataLoader
works in any logic context, as opposed toDatamartRowSet
which is available only in a PA DataLoad context.Example:
def ctx = api.getDatamartContext() def df = ctx.getDataFeed("Contract_Log") def loader = ctx.newDataLoader(df, "ApprovalDate", "ApprovalUser",...) loader.addRow(approvalDate, approvalUser, ...)
- Parameters:
table
- Table representing aDMDataFeed
orDMTable
to load data in.headerFieldNames
- Fields for which values will be loaded. Defaults to all (persisted) fields in the feed/table if not set.- Returns:
DatamartContext.DataLoader
instance providing an API to add (buffer) and flush (commit to the DB) rows to a feed/table.
-
-