Package net.pricefx.formulaengine

Interface DatamartContext

  • public interface DatamartContext
    Provides an API to query and load PA data from a formula context.

    • Method Detail

      • calendar

        DMCalendar calendar()
        Instantiate a PA Calendar utility object.
        Returns:
        Calendar

      • getDataFeed

        Table getDataFeed​(String name)
        Get a table object representing the DataFeed with the given name. A reference to this table can then be used when building a DatamartContext.Query or DataFeedLoad}.
        Parameters:
        name - The sourceName, uniqueName or label of the DF.
        Returns:
        Table representing the DF in the DataContext.

      • getDataSource

        Table getDataSource​(String name)
        Get a table object representing the DataSource with the given name. A reference to this table can then be used when building a DatamartContext.Query on that DataSource.
        Parameters:
        name - The sourceName, uniqueName or label of the DS.
        Returns:
        Table representing the DS in the DataContext.

      • getDatamart

        Table getDatamart​(String name)
        Get a table object representing the Datamart with the given name. A reference to this table can then be used when building a DatamartContext.Query on that Datamart.
        Parameters:
        name - The sourceName, uniqueName or label of the DM.
        Returns:
        Table representing the DM in the DataContext.

      • getModel

        Table getModel​(String name)
        Get a table object representing the Model with the given name. A reference to this table can then be used when building a DatamartContext.Query on that Model.
        Parameters:
        name - The sourceName, uniqueName or label of the Model.
        Returns:
        Table representing the Model in the DataContext.

      • getDWTable

        Table getDWTable​(String name)
        Get a table object representing the DWTable with the given name. A reference to this table can then be used when building a DatamartContext.Query on that DWTable
        Parameters:
        name - The sourceName, uniqueName or label of the DWT.
        Returns:
        Table representing the DWT in the 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

        A reference to this tbale can then be used when building a 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)
        Get a table object representing the FC representing the 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 the DataContext.

      • newQuery

        DatamartContext.Query newQuery​(Table table,
                               boolean rollup)
        Build 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)
        Create 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's sales data vs. last year's, etc.).
        Parameters:
        otherQuery - Query to use as a basis for this new query.
        Returns:
        DatamartContext.Query builder.

      • newQuery

        @Deprecated
DatamartContext.Query newQuery​(DatamartContext.Query query1,
                               DatamartContext.Query query2,
                               LinkedHashMap<String,​String> joinFieldsMap,
                               String joinMode,
                               boolean rollup)
        Deprecated.
        Preferred method for running a join query is now executeSqlQuery(java.lang.String, java.lang.Object...)
        Parameters:
        query1 - Sub-source1 represented by the result of query1
        query - : 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 SQL's inner join. If source2 has same named fields as source1, they are not added to the combined source.
        • 'LEFT_OUTER': As in SQL's left outer join. If source2 has same named fields as source1, they are not added to the combined source.
        • 'FULL_OUTER': As in SQL's full outer join. Fields in the combined source get a '_' postfix (defaults to '_1' for soruce1 and '_2' for source2).
        • 'INNER_ALL': As in SQL's inner join. Fields in the combined source get a postfix as above.
        • 'LEFT_OUTER_ALL': As in SQL's 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 given DatamartContext.Query. If for a rollup query the internal row limit, set by means the 'datamart.query.internalRowLimit' Price f(x) instance param, is exceeded, then a null is returned. The rationale for this behaviours 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. 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 Matrix2D form and summary information in map form.
        Throws:
        InterruptedException

      • streamQuery

        StreamResults streamQuery​(DatamartContext.Query query)
                   throws InterruptedException
        Executes the given DatamartContext.Query and return so that it can be examined one row at the time. This is different to executeQuery, which always return the full data set in scope of the query. When a row is retreived, 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 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 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 identifiers are to be double-quoted to preserve 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 - The views that make up the DB schema that can be queries, in the form of query definitions of type DatamartContext.Query or Strings representing 'SELECT' statements that will be added to the final SQL statement's WITH clause. The sources are assign the 'Ti' relation alias in the orderof appearance in the source Collection ((T1 for the first source).
        Returns:
        The query result as a Matrix2D object
        Throws:
        InterruptedException

      • newSqlQuery

        DMSqlQuery newSqlQuery()
        Instantiates a query object for building an SQL statements from source queries, with clauses and parameter bindings. To be executed by executeSqlQuery(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)
                t1.select("ProductID", "product")
                t1.select("ProductGroup", "PG")
                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 sqlQuery = ctx.newSqlQuery()
                sqlQuery.addSource(t1)
                sqlQuery.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 "

                return ctx.executeSqlQuery(sqlQuery)?.toResultMatrix()
        * @return A new DMSqlQuery object.

      • executeSqlQuery

        Matrix2D executeSqlQuery​(DMSqlQuery sqlQuery)
                  throws InterruptedException
        Executes an ANSI compliant SQL SELECT statement in the PA DB.
        Parameters:
        sqlQuery - The SQL statement definition.
        Returns:
        The query result as a Matrix2D object
        Throws:
        InterruptedException

      • buildQuery

        ResultPAQuery buildQuery​(DatamartContext.Query query)
        EXPERIMENTAL: not all aspects, properties of a query or supported! For ex: currently not supported are dim filters, row limit, join queries.... Builds and validates a query to be rendered in a client. Technically, a DataTransferObject representing the query is created, compatible with the query format in the REST API.
        Parameters:
        query - The 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 - The query defining the data to 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 any any other dimensions in a Datamart.
        Returns:
        The 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 any any other dimensions in a Datamart, intiialized with the name of the time dimension field, and an optional set filter criteria.
        Parameters:
        dateFieldName - Name of the time dimension field.
        timePeriodsAndProductAndCustomerGroups - TimePeriod, CustomerGroup, ProductGroup filters.
        Returns:
        The initialized DatamartContext.DataSlice object.

      • sourceSelectionEntry

        Object sourceSelectionEntry​(String entryName,
                            String... typeCode)

      • 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:
        The 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:
        The selected dim field value, or 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:
        The 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:
        The selected FC fields' names.

      • fieldSelectionEntry

        Object fieldSelectionEntry​(String entryName,
                           String sourceName)

      • createDWTable

        Table createDWTable​(String tableName,
                    Table table)

      • createDWTableFromProductMaster

        Table createDWTableFromProductMaster​(String tableName)

      • createDWTableFromCustomerMaster

        Table createDWTableFromCustomerMaster​(String tableName)

      • createDWTableFromProductExtension

        Table createDWTableFromProductExtension​(String tableName,
                                        String pxName)

      • createDWTableFromCustomerExtension

        Table createDWTableFromCustomerExtension​(String tableName,
                                         String cxName)

      • createDWTableFromLookupTable

        Table createDWTableFromLookupTable​(String tableName,
                                   Long tableLookupId)

      • newDataLoader

        DatamartContext.DataLoader newDataLoader​(Table feedTable)
        Instantiate a new DatamartContext.DataLoader to load rows with data for all the feed's attributes.
        Parameters:
        feedTable - Table representing the DataFeed to load data in.
        Returns:
        DataFeedLoad instance providing an API to add (buffer) and flush (commit to the DB) rows to a DataFeed table.

      • newDataLoader

        DatamartContext.DataLoader newDataLoader​(Table feedTable,
                                         List<String> headerFieldNames)
        Instantiate a new DatamartContext.DataLoader to load rows with data for the named fields.
        Parameters:
        feedTable - Table representing the DataFeed to load data in.
        headerFieldNames - The fields for which values will be loaded. Defaults to all (persisted) fields in the DataFeed if not set.
        Returns:
        DataFeedLoad instance providing an API to add (buffer) and flush (commit to the DB) rows to a DataFeed table.