Skip to main content
V
vmanojrc30
February 27, 2023
Solved

SQL Connectors with Multi period selection dynamically

  • February 27, 2023
  • 2 replies
  • 2 views

I am setting up a Workflow with a SQL connector data source. I need to be able select a range of periods (start and end period) within the SQL query to extract the balances for multiple periods and load the same to OS cube. Users should have a provision to select the start and end periods and the SQL query should run dynamically based on those periods. Workflow should be executed to import and load the data to the multi periods selected at one time (not needing to run one by one period).

I am new to OS. Appreciate suggestions and recommendations to implement this requirement in OS.

 

 

 

Best answer by ChrisLoran

It's not possible (for good reason) to have drop-down / combo box selections on the Import step on an Import workflow.
For the date range selection on the SQL, this is usually performed by the end user making a conscious decision to select (a) A particular workflow profile, (b) A particular year, (c) A particular scenario.
The workflow profile settings, and the scenario settings have various attributes such as Input Frequency, sometimes scenarios contain start/end periods (scenarios with custom range), workflow profile properties determine the tracking frequency (e.g. monthly for actuals, 12 periods for budget )

If you have drop-down boxes on the import step, for ad-hoc user period range selection, then this would be very error-prone.  You could do this by building a simple dashboard, and then have an import workflow profile of type : [Workspace, Import, Validate, Load].  The first step (workspace) runs a simple dashboard with your drop-down period selections , and that runs a query to pull data into a sort of pre-staging table, then when you go to the Import step, it pulls the data retrieved from the dashboard into the main stage tables , where the transformation rules can work from.

2 replies

C
ChrisLoranAnswer
February 27, 2023

It's not possible (for good reason) to have drop-down / combo box selections on the Import step on an Import workflow.
For the date range selection on the SQL, this is usually performed by the end user making a conscious decision to select (a) A particular workflow profile, (b) A particular year, (c) A particular scenario.
The workflow profile settings, and the scenario settings have various attributes such as Input Frequency, sometimes scenarios contain start/end periods (scenarios with custom range), workflow profile properties determine the tracking frequency (e.g. monthly for actuals, 12 periods for budget )

If you have drop-down boxes on the import step, for ad-hoc user period range selection, then this would be very error-prone.  You could do this by building a simple dashboard, and then have an import workflow profile of type : [Workspace, Import, Validate, Load].  The first step (workspace) runs a simple dashboard with your drop-down period selections , and that runs a query to pull data into a sort of pre-staging table, then when you go to the Import step, it pulls the data retrieved from the dashboard into the main stage tables , where the transformation rules can work from.

V
vmanojrc30Author
February 27, 2023

Hi Chris

Thanks for your response. With regards to next steps after import (validate and load) is it possible to run them for multi period at one time? I dont beleive this can be configured in Workflow setup (similar to Import) however could this be done using Extender Business rule or DM job? If so, where can I define to get the multi periods dynamically. 

M
mansi
October 17, 2023

Parallel load of periods is not possible in OneStream. It has to run sequentially. You can write Extensible BR with following APIs

BRApi.Import.Process.ExecuteParseAndTransform(si, wfClusterPk, String.Empty, Nothing, TransformLoadMethodTypes.Replace, SourceDataOriginTypes.FromDirectConnection, True)
Dim ValidateTrInfo As ValidationTransformationProcessInfo = BRApi.Import.Process.ValidateTransformation(si, wfClusterPk, True)
Dim ValidateIntInfo As ValidateIntersectionProcessInfo =BRApi.Import.Process.ValidateIntersections(si, wfClusterPk, True)
Dim LoadInfo As LoadCubeProcessInfo = BRApi.Import.Process.LoadCube(si, wfClusterPk)
Terms & ConditionsAccessibility statement