Forum Discussion

vmanojrc30's avatar
vmanojrc30
Contributor
2 years ago

SQL Connectors with Multi period selection dynamically

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 l...
Data Integration
Data Sources
Workflow
  • ChrisLoran's avatar
    ChrisLoran
    2 years ago

    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.

ChrisLoran's avatar
ChrisLoran
Valued Contributor
2 years ago

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.

  • vmanojrc30's avatar
    vmanojrc30
    Contributor
    2 years ago

    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.