Fast Data Extract Considerations

Review these considerations when using different FDX BRApis.

General Considerations

The BRApis used for extracting data from the data sources have these common variables:

  • useGenericTimeColNames (As Boolean): If you are pivoting Time and will be using Attribute Value dimensions, set your BI Blend MeasureType to TimeWFViewAV. In this case, set useGenericTimeColNames to True. If you want to determine Time using your source dataset, set useGenericTimeColNames to False.

  • filter (As String): You can filter the dataset to determine exactly which data from your source dataset to import. See Filtering Data .

  • parallelQueryCount (As Integer): It is best practice to set this to eight.

  • logStatistics (As Boolean): To log the statistics of your import to the error log, set this to True. If set to True, this generates a row in the error log called FDX Warehouse Query Statistics. These statistics include information about memory usage and the time it took to load each Blend Unit member in milliseconds.

Data Unit Considerations

The Data Unit FdxExecuteDataunitTimePivot BRApi enables you to return Data Unit data. You cannot extract dynamically calculated data with this option.

The variables available provide several options that enable you to define your Data Unit dimensions: entityMemFilter, scenarioMemFilter, and timeMemFilter.

NOTE: You can extract parent entity data with this variable but, because BI Blend imports base member data only, you must map these to a base entity in your Transformation Profile.

Values for these variables must be declared: parentName, consName, scenarioTypeId, and viewName.

The consName variable enables you to extract data at the Local or Translated level only. You cannot extract Share or Elimination data.

The scenarioTypeId variable requires an ID returned from the name. To do this, return the name of the scenario and then return the ID of the name.

Cube View Considerations

Consider the following items when using the FDX BRApis to return Cube View data.

workspaceID

The variables for the FdxExecuteCubeViewTimePivot BRApi include a workspaceID. If you have Cube Views with the same name in different Workspaces, you must indicate which Workspace to use to find your Cube View.

The workspaceID can be returned using this function: 

Copy

VB.Net

Dim gValue As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName(si, isSystemLevel, workspaceName)

Cube View Point of View Row Headers

Consider how the FDX BRApi determines the Cube View members to understand the data that is returned. The FDX BRApi uses the rows in the Cube View to determine which members to return. Column dimension members are not used because there may be multiple members from the same dimension in the columns. The API will not be able to determine which of these column dimension members to use for the Cube View row.

The Cube View FDX BRApi will check the row headers of the Cube View to see which members to return. If dimension members are not defined in the rows, then the Cube View POV will be used to determine the dimension member. If this is not defined in the Cube View POV, then the Cube POV is used.

To check which members are returned, turn off the default row headers and decide which dimensions you want to be displayed in the Cube View Row header. Navigate to your Cube View and go to Designer > General Settings > Header Overrides > Row Headers.

NOTE: Because the Cube View FDX BRApi uses row headers to determine members, this may impact Cube View design. To extract data from existing Cube Views, you may need to pivot your dimensions from columns to rows to extract the correct members.

Dynamic Calculations

If there are dynamic calculations in the Cube View, the data returned as a result of the dynamic calculations in the Cube View can then be imported into the BI Blend table using the Cube View BRApi.

Stage Considerations

FdxGetStageTargetColumnList

The Time members are defined using startTimeName and endTimeName rather than using timeMemFilter, which is used by the Cube View and Data Unit FDX BRApis. Ensure that startTimeName and endTimeName include all the time periods needed to return the data source connector fields. Values must be returned for these variables: parentWFProfileName and scenarioName. These variables define the Workflow POV and are used to extract stage target data.

Warehouse Considerations

NOTE: If you are importing data from an external data source that is in a virtual private network (VPN), then Smart Integration Connector (SIC) is the recommended method for managing your external data source connections.

FDX APIs can extract data from internal or external warehouse data. You must specify the following information for these FDX APIs:

  • The column names that must be returned

  • The column and members to use as the Time dimension, so that the members can be used to pivot Time

  • The column to use as the Amount or Measure column

FdxGetWarehouseColumnList

This FDX API is used to return the column names to the Data Source Connector fields. These are different from the application APIs because you need to specify the column names you want to return. To do this, you need the following variables:

  • dimColNameList As List(Of String): This variable requires a list of the fields you want to return to your Data Source Connector Fields, which can then be mapped to your application dimensions.

  • timePivotColNameList As List(Of String): This warehouse FDX API can only be used with the Time Pivot option. This variable requires a list of Time members that are in your warehouse. These members are returned as the Time columns in your Data Source Connector Fields.

Attributes

You can also map columns in your warehouse data source to Attribute dimensions. To do this, set your includeAttributes variable to True.

FdxExecuteWarehouseTimePivot

This is used to extract the data. You must complete these tasks to use this FDX API:

  • Select the data using Select statements.

  • Declare which column to use as the Time dimension column.

  • Declare which column to use as the Amount or Measure column.

Select Data

Use these variables to build your Select statement:

  • pageSelect As String

  • pageFrom As String

  • pageCriteria As String

  • pageGroupBy As String

  • pageOrderBy As String

Time Dimension Column

The timeDimColName variable is used to declare which column stores the time period members.

Measure (Amount) Column

The measureColName variable is used to declare which column stores the amounts.

ParrallelItemColName

The paralleIItemColName variable is used to declare which column to use to select distinct values. These values are then run in parallel when the FDX API is processed, up to a maximum of eight values at a time. The number is determined by the parallelQueryCount variable. Use the same column in your Select Distinct statement that you use as the parallelItemColName value.

TIP: It is recommended that you select the column with the fewest distinct values for better performance.