Relational Blending API

The Relational Blending API functions listed below can be used to efficiently lookup a value in a cached relational table.  This is based on the current POV values of the analytic engine or by providing specific override values for the Cube Name, Entity Name, Scenario Name, Time Name and Account Name.  It is important to understand how caching of the data table is done.  The CacheLevel parameter is used to control the cache granularity which will in turn control cache efficiency.  To choose the most efficient CacheLevel value, determine how the data will be used in a Cube View.  If the primary Cube View data request will be on a single data unit (Entity, Scenario, Time), then the best cache level choice would be BlendCacheLevelTypes.WfProfileScenarioTimeEntity. 

This is an efficient choice because the first time a cell is requested for the Entity, Scenario, Time combination, a query will run to load all the stage data for the combination.  Then all subsequent cell requests would read values from cache.  On the other hand, if the primary Cube View data request is for multiple Entities, then BlendCacheLevelTypes.WfProfileScenarioTime cache level would be a more efficient choice.  This is more efficient because a single query would run to load all the data for the Scenario and Time into cache and then all subsequent cell requests would read values from cache.  As a cautionary note, be aware that using coarse cache levels (reading more data at once into cache) only benefits performance when the target Cube View can read many values for the cache.  If the target Cube View is only focused on one Entity/Account and the BlendCacheLevelTypes.WfProfileScenarioTime cache level is chosen, all rows for the entire Scenario and Time would need to be read into memory when only values for one Entity/Account combination was needed.  In this case, BlendCacheLevelTypes.WfProfileScenarioTimeEntityAccount would be a more efficient cache level.

In summary, choose a cache level that will minimize the number of actual database queries needed to run in order to get the desired cells for the target Cube View.  This is not an exact science, and it may be difficult to choose a cache level that works efficiently for all target Cube Views.  If there is a diverse set of Cube Views using relational blend data, consider creating specific Members that implement different cache levels that match the Cube View data pattern.

Cache Level Types

BlendCacheLevelTypes.WfProfileScenarioTime

Query will be run and cached using the supplied Workflow Profile, POV Scenario and POV Time as criteria and cache key.

BlendCacheLevelTypes.WfProfileScenarioTimeEntity

Query will be run and cached using the supplied Workflow Profile, POV Scenario, POV Time and POV Entity as criteria and cache key.

BlendCacheLevelTypes.WfProfileScenarioTimeAccount

Query will be run and cached using the supplied Workflow Profile, POV Scenario, POV Time and POV Account as criteria and cache key.

BlendCacheLevelTypes.WfProfileScenarioTimeEntityAccount

Query will be run and cached using the supplied Workflow Profile, POV Scenario, POV Time, POV Entity and POV Account as criteria and cache key.

BlendCacheLevelTypes.Custom

Intended to be used with custom table query (Cache level is explicitly controlled by the supplied SQL query). Query will be run and cached using the supplied cache name.

Relational Model Blending API Functions

GetStageBlendTextUsingCurrentPOV

Function Prototype           

Public Function GetStageBlendTextUsingCurrentPOV (ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal textOperation As BlendTextOperationTypes) As String

Description

Read a stage text attribute value from a cached ado.net data table using the current POV values and optionally perform concatenation on the results. 

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cacheLevel

Cache granularity level used to control how much information is cached in each chunk (Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary).

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up. (Pass an empty String to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix.)

fieldList

List of STAGE fields that will be used as criteria and/or returned.

Criteria

Criteria statement used to select rows in the cached data table.

fieldToReturn

Name of the stage field to return.

textOperation

Text operation to perform on the resulting data table (Note: FirstValue returns the first matching row if there is more than one stage value for the specified cell).       

Return Type
String

Example

'UD8 DynamicCalc - Lookup Attribute 1 From Stage
If Not api.Entity.HasChildren Then
        Dim criteria As New Text.StringBuilder
        criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
        criteria.Append("And U2T = '" & api.Pov.UD2.Name & "' ")
        Return api.Functions.GetStageBlendTextUsingCurrentPov(BlendCacheLevelTypes.

		WfProfileScenarioTimeEntity, "DU", 

		"*.Sales Detail", "U1T,U2T,A1,ConvertedAmount", criteria.ToString, 

			"A1", BlendTextOperationTypes.FirstValue)


Else
        Return String.Empty
End If

GetStageBlendText

Function Prototype

Public Function GetStageBlendText (ByVal cubeName As String, ByVal entityName As String, ByVal scenarioName As String, ByVal timeName As String, ByVal accountName As String, ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal textOperation As BlendTextOperationTypes) As String

Description

Read a stage text attribute value from a cached ado.net data table using the specified POV values and optionally perform concatenation on the results. 

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cubeName

Name of the Cube to use for the POV.

entityName

Name of the Entity to use for the POV.

scenarioName

Name of the Scenario to use for the POV.

timeName

Name of the Time to use for the POV.

accountName

Name of the Account to use for the POV.

cacheLevel

Cache granularity level used to control how much information is cached in each chunk (Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary).

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up (Pass an empty String if to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix).

fieldList

List of Stage fields that will be used as criteria and/or returned.

Criteria

Criteria statement used to select rows in the cached data table.

fieldToReturn

Name of the Stage field to return.

textOperation

Text operation to perform on the resulting data table (Note: FirstValue returns the first matching row if there is more than one stage value for the specified cell).       

Return Type
String

GetStageBlendNumberUsingCurrentPOV

Function Prototype

Public Function GetStageBlendNumberUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal mathOperation As BlendNumericOperationTypes) As Decimal

Description

Read a stage numeric attribute value from a cached ado.net data table using the current POV values and optionally perform aggregation math on the results.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cacheLevel

Cache granularity level used to control how much information is cached in each chunk (Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary).

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up (Pass an empty String if you want to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix).

fieldList

List of stage fields that will be used as criteria and/or returned.

criteria

Criteria statement used to select rows in the cached data table.

fieldToReturn

Name of the stage field to perform math on and return.

mathOperation

Math operation to perform on the resulting data table (Note: FirstValue returns the first matching row if there is more than one stage value for the specified cell).

Return Type
Decimal

Example

'UD8 DynamicCalc - Lookup Average ConvertedAmount From Stage
If Not api.Entity.HasChildren Then
        Dim criteria As New Text.StringBuilder
        criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
        criteria.Append("And U2T = '" & api.Pov.UD2.Name & "' ")
        Return  api.Functions.GetStageBlendNumberUsingCurrentPov(BlendCacheLevelTypes.

		WfProfileScenarioTimeEntity, "DU", 
		"*.Sales Detail", "U1T,U2T,A1,ConvertedAmount", criteria.ToString, 

			"ConvertedAmount", 
		BlendNumericOperationTypes.AverageSkipZero)
Else
        Return 0
End If

GetStageBlendNumber

Function Prototype

Public Function GetStageBlendNumber(ByVal cubeName As String, ByVal entityName As String, ByVal scenarioName As String, ByVal timeName As String, ByVal accountName As String, ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String, ByVal criteria As String, ByVal fieldToReturn As String, ByVal mathOperation As BlendNumericOperationTypes) As Decimal

Description

Read a stage numeric attribute value from a cached ado.net data table using the specified POV values and optionally perform aggregation math on the results.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cubeName

Name of the Cube to use for the POV.

entityName

Name of the Entity to use for the POV.

scenarioName

Name of the Scenario to use for the POV.

timeName

Name of the Time to use for the POV.

accountName

Name of the Account to use for the POV.

cacheLevel

Cache granularity level used to control how much information is cached in each chunk. Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary.

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up. Pass an empty String to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix.

fieldList

List of Stage fields that will be used as criteria and/or returned.

criteria

Criteria statement used to select rows in the cached data table.

fieldToReturn

Name of the stage field to perform math on and return.

mathOperation

Math operation to perform on the resulting data table

NOTE: FirstValue returns the first matching row if there is more than one stage value for the specified cell

Return Type
Decimal

GetStageBlendDataTableUsingCurrentPOV

Function Prototype

Public Function GetStageBlendDataTableUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String) As DataTable

Description

Read stage data into a cached ado.net data table using the current POV values so that it can
be Queried / Analyzed in memory on the application server.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cacheLevel

Cache granularity level used to control how much information is cached in each chunk (Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary).

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up (Pass an empty String to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix).

fieldList

List of Stage fields that will be used as criteria and/or returned.

Return Type
DataTable

Example

'Lookup Attribute 1 From Stage Cached Data Table
If Not api.Entity.HasChildren Then
        Dim result As String = String.Empty
    
'Get the DataTable from cache
Using dt As DataTable =                                                                                                                                                                                                                                                                                             api.Functions.GetStageBlendDataTableUsingCurrentPov(BlendCacheLevelTypes.WfProfileScenarioTimeEntity, "DU", "*.Sales Detail", "U1T,U2T,A1,ConvertedAmount")
    If Not dt Is Nothing Then        
    'Execute a query against the data table and return the first matching row
    Dim criteria As New Text.StringBuilder
    criteria.Append("U1T = '" & api.Pov.UD1.Name & "' ")
    criteria.Append("And U2T = '" & api.Pov.UD2.Name & "' ")
    Dim rows As DataRow() = dt.Select(criteria.ToString)
    If rows.Count > 0 Then
    result = rows(0)("A1")
        End If    
      End If
  End Using
  Return result
Else
    Return String.Empty
End If

GetStageBlendDataTable

Function Prototype

Public Function GetStageBlendDataTable(ByVal cubeName As String, ByVal entityName As String, ByVal scenarioName As String, ByVal timeName As String, ByVal accountName As String, ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal wfProfileName As String, ByVal fieldList As String) As DataTable

Description

Read stage data into a cached ado.net data table using the specified POV values so that it can be Queried / Analyzed in memory on the application server.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cubeName

Name of the Cube to use for the POV.

entityName

Name of the Entity to use for the POV.

scenarioName

Name of the Scenario to use for the POV.

timeName

Name of the Time to use for the POV.

accountName

Name of the Account to use for the POV.

cacheLevel

Cache granularity level used to control how much information is cached in each chunk. (The less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary.)

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

wfProfileName

Name of the Import Workflow profile containing the values to be looked up (Pass an empty String to look up the workflow based on the POV Entity, use *.YourWFSuffix to get workflow profiles with the specified suffix).

fieldList

List of Stage fields that will be used as criteria and/or returned.

Return Type
DataTable

GetCustomBlendDataTableUsingCurrentPOV

Function Prototype

Public Function GetCustomBlendDataTableUsingCurrentPOV(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal sourceDBLocation As String, ByVal sourceSQL) As DataTable

Description

Read data from a custom table into a cached ado.net data table using the current POV values so that it can be Queried / Analyzed in memory on the application server.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cacheLevel

Cache granularity level used to control how much information is cached in each chunk (Less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary).

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

sourceDBLocation

Database location name to query (Application, Framework, or a Named External Connection).

sourceSQL

SQL statement that defines the DataTable to cache for in memory querying.

Return Type
DataTable

Example:

'Lookup Invoice Number From a Custom Table
If Not api.Entity.HasChildren Then
        Dim result As String = String.Empty
    
        Dim queryToCache As New Text.StringBuilder
        queryToCache.Append("Select * ")
          queryToCache.Append("From InvoiceMaterialDetail ")
          queryToCache.Append("Where PlantCode = 'H200' And CustId = 'NH2421' And    InvYear = 2011                                                                                                                                                                                                                                                                    
        and InvMonth = 'M3'    ")
'Get the DataTable from cache
Using dt As DataTable =  api.Functions.GetCustomBlendDataTableUsingCurrentPov(BlendCacheLevelTypes.

	Custom, "Material", 
	"Revenue Mgmt System", QueryToCache.ToString)
    If Not dt Is Nothing Then        
'Execute a query against the CUSTOM data table and return column InvNo for the first matching  row
    Dim criteria As New Text.StringBuilder
     criteria.Append("WorkDay = 15")
    Dim rows As DataRow() = dt.Select(criteria.ToString)
    If rows.Count > 0 Then
    result = rows(0)("InvNo")
    End If    
    End If
End Using
   Return result
Else
    Return String.Empty
End If

GetCustomBlendDataTable

Function Prototype

Public Function GetCustomBlendDataTable(ByVal cacheLevel As BlendCacheLevelTypes, ByVal cacheName As String, ByVal sourceDBLocation As String, ByVal As String) As DataTable

Description

Read data from a custom table into a cached ado.net data table using the specified POV values so that it can be Queried / Analyzed in memory on the application server.

NOTE: Cache only lives for the duration of the WCF call.

Parameters

cacheLevel

Cache granularity level used to control how much information is cached in each chunk. (The less granular cache level helps repeated calls but hurts requests for a single cell because more data is cached than necessary.)

cacheName

Short name used to identify the values placed in the cache (Full CacheID will be CacheName + CacheLevel Values).

sourceDBLocation

Database location name to query (Application, Framework, or a Named External Connection).

sourceSQL

SQL statement that defines the DataTable to cache for in memory querying.

Return Type

DataTable