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 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.
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.
