Data Flows

A Data Flow combines as set of activities (steps) with commands to fill, process and manage the data warehouse content. Each step is connected to each other to form a sequence.

Data Flows can be scheduled to run automatically by the Profitbase Server. This way, your business data stays up to date without any manual work.

Adding Data Flows

To add a new Data Flow, right click the Data Flows folder in the navigator. You then get the option to add:

  • a new empty Data Flow.
  • a new Load Data Flow pre filled with steps according to Solution content.
  • a new Update Data Flow with predefined steps to update Solution content.
  • a new Auto Update Data Flow where data flow steps are added as auto update operations for the selected fact table(s) and the selected SSAS module.
  • a new Incremental Table Data Flow where data flow items are added based on the dependencies of a selected incremental table.

Editing Data Flows

The Data Flow Builder is a graphical designer which lets you easily edit and manage your Data Flows. The Data Flow Builder consists of following main parts;

  • Toolbar (top) with
    • [Save] button.
    • [Execute] button (w/sub command [Save and Execute]).
    • [Stop] button (enabled only when executing).
    • View menu to show/hide the Settings pane.
  • The Data Flow Activities (DFA) menu pane is located on the left side of the Data Flow Builder and contains the building blocks or Steps of a Data Flow. To add a Data Flow Activity, you simply drag and drop an item from the DFA menu onto the Design Surface. Some of the activities can be run in parallel. To run activities in parallel, drop a Parallel Activity onto the design surface and then drop the activities you want to run in parallel onto the Parallel Activity.
  • The Design Surface (center) is where you design your Data Flow. To design a Data Flow, simply drag and drop the desired Data Flow Activities from the DFA menu onto the Design Surface, specify the settings for each activity, and set up the flow sequence by connecting the activities to each other in the desired order.
  • Property panes (right)
    • Data Flow Activity Settings pane from where you specify the settings of each Data Flow Activity in a Data Flow, such as which Source tables to load and which cubes to process.
      To edit the settings of a Data Flow Activity in the Data Flow Activity Settings pane, simply click the desired Data Flow Activity on the Design Surface and then edit its settings.
      You can also edit the settings of an activity by right-clicking it and selecting “Settings” from the context menu of each Activity. 
    • Data Flow Settings pane where you edit name, description and comments of the Data Flow.
    • Data Flow Status pane shows logs and dependencies. Click here for more on the status pane.
  • Message log pane (bottom) is displayed when you execute a Data Flow or a Data Flow step.
The panes listed above can be moved around, made floating and hidden as you prefer.

Types of Data Flow steps

Following table lists types of steps/activities supported in Profitbase Studio 6. 

Note: Upgraded data flows may have obsolete steps (marked in red/pink). These should be replaced to ensure correct operations.

Generic Tasks       SQL Expression

This activity lets you execute a custom SQL statement

Settings: 

  • SQL Statement: Specify the T-SQL statement to execute.
SSIS Package Exec

This activity lets you execute a SSIS Package. The SSIS Package is executed using dtexec.exe on your local machine.

Settings:

  • BitMode: Specify whether to run the 32-bit version of dtexec on 64-bit operating system.
  • dtexec: Specify the arguments to be passed to the dtexec utility.
Parallel Task

This activity lets you execute two or more Data Flow activities in parallel by dragging and dropping them onto a Parallel Task activity.
The following activity types can be executed in parallel:

  • Reload Source Data
  • SQL Expression 
  • SSIS Package Exec
  • Execute Script(s)
  • Generate Dimensions
  • Module Command(s)
  • SSAS Command(s)

Settings:

  • Activity Start Interval: Specify the amount of time in seconds between the execution start of each activity.
Execute Data Flows

This activity lets you execute Profitbase Studio Data Flows.

Settings:

  • Data Flow(s): Specify the data flows to be nested in this data flow.
Execute Script(s) This activity lets you execute Script Extension(s).

Settings:

  • Script(s): Specify the script extensions to be executed.
Web API Call

This activity lets you send a command to a web-api.

Settings:

  • API Address: page/api to call.
  • Base URL: base url where the api call is located.
  • HTTP method: GET/PUT/POST/DELETE
  • Opt.content: Additional parameters.
  • User/Password: Enter opt. user id and password or use the current credentials that the data flow runs as.
MS Azure App, Insights Trace

This activity lets you send messages to MS Azure Application Insights.

Settings:

  • Instrumentation Key: key from Azure App.Insight settings.
  • Message: Text to send.
  • Type: Information | Warning | Error.
Log Maintenance

This deletes older items from Operation and/or Message logs. Older data can be copied to a historic table.

Settings:

  • Historic Settings
    • Copy older data to historic table: True/False
    • Historic table prefix name: prefix for backup table name. Default is Historic_ ++
  • Log selection
    • Days to keep in log; default 30 days are kept, older data is deleted/backed up.
    • Message Log; True/False, default true.
    • Operation Log: True/False, default false.
Override Content Status

This activity lets you override status on selected solution content.

Settings:

  • Content: select content to override status on (e.g. data source tables, dimensions etc).
  • Override: Override status to None | Error | Warning | Ok
Override Dataflow Status  This activity lets you override status of the current dataflow.

Settings:

  • Override:Override status to None | Error | Warning | Ok
 Data Source  Reload Source Data

This activity lets you load or reload data from your Data Sources.
By using this activity, you can specify what data to load from each source by setting up custom filters.
The custom filters available to you include;

  • Day(s) which lets you specify the number of days back you want to load data for
  • Month(s) which lets you specify the number of months you want to load data for
  • Date which lets you specify from which date you want to load data for
  • Date Range which lets you specify a date interval to load data for
  • All lets you (re)load all data from the source
  • Incremental lets you load table data Incrementally. The Data Source Table must have this Load Option enabled beforehand.
  • Custom uses the custom expression defined for a Table in the Data Source Edit screen to load data.

Settings:

  • Source Table(s): Select which Source Table(s) from which Data Source(s) data should be reloaded for. The reload option will also be specified here (Day(s), Month(s), Date,DateRange, All, Incremental or Custom).
Dimension Task Generate Dimensions

This activity lets you load or update your Dimensions.

Settings:

  • Dimension(s): Select which Dimension(s) to load or update.
Module Tasks   Module Command(s)

This activity lets you run commands for a processing modules in the data warehouse database.

Dimensions and module definitions fact results are processed according to commands described here.
SSAS Command(s)

This activity lets you run commands against a SSAS database.

Click here for more on available commands.
SSAS XMLA Expression This activity lets you execute a custom XMLA statement against given SSAS database.

Settings: 

  • XMLA Statement: Specify the xmla statement to execute.

Generic settings:

  • Step Name: This can be changed to get a more user friendly name.
  • Run as: Define which Account the solution database is opened with. The Solution's data warehouse connection settings is used to run the actual command.
  • Timeout: Timeout in seconds used in the execution of command. Setting the timeout to 0, gives an indefinite timeout.
Right click each step to get a context menu with:
  • [Create Connection] between steps, see below.
  • [Execute Current Step] 
  • [Execute Current and Subsequent Steps] executes current step and all steps after it.
  • [Delete] to remove the selected step.
  • [Settings] to get the Data Flow Activity Settings pane as a dialog.

Connect steps and adding conditions

All steps must be connected. Right click a step and select [Create Connection]. A line is then shown. Click the connector box on the next step to connect.

Right click the connector line to get a menu with:

  • [Set Conditions...] to bring up the connection dialog. This has options/check boxes for when to continue to next step. The options can be combined:
    • Continue when last step finished Successfully
    • Continue when last step finished with warnings.
    • Continue when last step finished with errors.
  • [Delete] to remove a connection.

Executing Data Flow and Data Flow steps

A Data Flow can be scheduled or run manually on the Profitbase Studio server from Schedule Management.

Otherwise click [Execute] or right click a step and select [Execute Current Step]/[Execute Current and Subsequent Steps] to run it / test it from the user interface.

When executing, the message log will be shown at bottom. 

Note - When run via the Profitbase Studio Server server, the server services user account is used if not otherwise set in each step (Run As) or in specific data warehouse connection settings. The service user may differ from the Profitbase Studio user.