Data Prep Studio Runtime Parameter Filter

Justin_20462

Hi,

I'm new to the Datawatch/Monarch products so maybe I'm overlooking something in the documentation, but is there a way to use runtime parameters as filters in Data Prep Studio?

For example, if we are hooked up to a SQL database of clients and want to only extract data for one client at a time and a certain date range - specified at run time - can we achieve that by setting up "Company", "Start Date", and "End Date" runtime parameters in DPS?

Thanks!

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Steve_Caiels

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------"

Justin_20462

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

I'm new to the Datawatch/Monarch products so maybe I'm overlooking something in the documentation, but is there a way to use runtime parameters as filters in Data Prep Studio?

For example, if we are hooked up to a SQL database of clients and want to only extract data for one client at a time and a certain date range - specified at run time - can we achieve that by setting up "Company", "Start Date", and "End Date" runtime parameters in DPS?

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------"

Steve,

Thank you for that reply and explanation. Our datasets are very large and therefore unable to be completely downloaded into DPS to use the calculated field method you described. I have tried to pull an entire dataset in and it just causes the application to lock-up and crash.

So going with your second approach, specifying a WHERE clause could maybe work, but would that then limit the automation we can achieve within Automator? Wouldn't setting the where clause to be client specific essentially "hard code" the workspace to only be able to run for that specific client?

Ultimately what we were hoping to achieve was some semblance of an automated process to extract data to migrate client/employee data from our current payroll system into the Kronos system. We have to take a company-by-company approach to migration, so we were hoping to create an Automator process that we could pass a client number too and then it would compile and export all of necessary files to feed into Kronos.

Thanks!

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

Steve_Caiels

Steve,

Thank you for that reply and explanation. Our datasets are very large and therefore unable to be completely downloaded into DPS to use the calculated field method you described. I have tried to pull an entire dataset in and it just causes the application to lock-up and crash.

So going with your second approach, specifying a WHERE clause could maybe work, but would that then limit the automation we can achieve within Automator? Wouldn't setting the where clause to be client specific essentially "hard code" the workspace to only be able to run for that specific client?

Ultimately what we were hoping to achieve was some semblance of an automated process to extract data to migrate client/employee data from our current payroll system into the Kronos system. We have to take a company-by-company approach to migration, so we were hoping to create an Automator process that we could pass a client number too and then it would compile and export all of necessary files to feed into Kronos.

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter
Hi,

I'm new to the Datawatch/Monarch products so maybe I'm overlooking something in the documentation, but is there a way to use runtime parameters as filters in Data Prep Studio?

For example, if we are hooked up to a SQL database of clients and want to only extract data for one client at a time and a certain date range - specified at run time - can we achieve that by setting up "Company", "Start Date", and "End Date" runtime parameters in DPS?

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

Hi Justin,
Agreed - this approach would not work well with automation. How were you planning on passing the runtime parameters? Could an option be to form the SELECT statement using VB .NET and running it as a pre process script to download the required records to a static local file (or separate SQL table) that could be passed to DPS?
Cheers,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 07:08 AM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Steve,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------

Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

Steve_Caiels

Hi Justin,
Agreed - this approach would not work well with automation. How were you planning on passing the runtime parameters? Could an option be to form the SELECT statement using VB .NET and running it as a pre process script to download the required records to a static local file (or separate SQL table) that could be passed to DPS?
Cheers,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 07:08 AM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Steve,

Thank you for that reply and explanation. Our datasets are very large and therefore unable to be completely downloaded into DPS to use the calculated field method you described. I have tried to pull an entire dataset in and it just causes the application to lock-up and crash.

So going with your second approach, specifying a WHERE clause could maybe work, but would that then limit the automation we can achieve within Automator? Wouldn't setting the where clause to be client specific essentially "hard code" the workspace to only be able to run for that specific client?

Ultimately what we were hoping to achieve was some semblance of an automated process to extract data to migrate client/employee data from our current payroll system into the Kronos system. We have to take a company-by-company approach to migration, so we were hoping to create an Automator process that we could pass a client number too and then it would compile and export all of necessary files to feed into Kronos.

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------

Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter
Hi,

I'm new to the Datawatch/Monarch products so maybe I'm overlooking something in the documentation, but is there a way to use runtime parameters as filters in Data Prep Studio?

For example, if we are hooked up to a SQL database of clients and want to only extract data for one client at a time and a certain date range - specified at run time - can we achieve that by setting up "Company", "Start Date", and "End Date" runtime parameters in DPS?

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

To expand a little, would the following workflow be appropriate?

1. Create a text file called Parameter.txt, for example. This would contain the three 'runtime' values
2. Have a pre process VB .NET script that reads this parameter file and uses it to form a SELECT statement to save the specified records to a static file.
3. Have a static workspace to perform your required transformations.

This could be monitored to run automatically on the arrival of the parameter text file.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 07:26 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,
Agreed - this approach would not work well with automation. How were you planning on passing the runtime parameters? Could an option be to form the SELECT statement using VB .NET and running it as a pre process script to download the required records to a static local file (or separate SQL table) that could be passed to DPS?
Cheers,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 02-07-2019 07:08 AM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Steve,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.

Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

Justin_20462

To expand a little, would the following workflow be appropriate?

1. Create a text file called Parameter.txt, for example. This would contain the three 'runtime' values
2. Have a pre process VB .NET script that reads this parameter file and uses it to form a SELECT statement to save the specified records to a static file.
3. Have a static workspace to perform your required transformations.

This could be monitored to run automatically on the arrival of the parameter text file.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 07:26 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,
Agreed - this approach would not work well with automation. How were you planning on passing the runtime parameters? Could an option be to form the SELECT statement using VB .NET and running it as a pre process script to download the required records to a static local file (or separate SQL table) that could be passed to DPS?
Cheers,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 02-07-2019 07:08 AM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Steve,

Thank you for that reply and explanation. Our datasets are very large and therefore unable to be completely downloaded into DPS to use the calculated field method you described. I have tried to pull an entire dataset in and it just causes the application to lock-up and crash.

So going with your second approach, specifying a WHERE clause could maybe work, but would that then limit the automation we can achieve within Automator? Wouldn't setting the where clause to be client specific essentially "hard code" the workspace to only be able to run for that specific client?

Ultimately what we were hoping to achieve was some semblance of an automated process to extract data to migrate client/employee data from our current payroll system into the Kronos system. We have to take a company-by-company approach to migration, so we were hoping to create an Automator process that we could pass a client number too and then it would compile and export all of necessary files to feed into Kronos.

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.

Original Message:
Sent: 02-07-2019 04:36 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,

It is not possible to use a runtime parameter to control the SQL directly. However, you could create a calculated field that dynamically determines if the record is active based on the Runtime Parameters, then filter on the calculated field.

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter
Hi,

I'm new to the Datawatch/Monarch products so maybe I'm overlooking something in the documentation, but is there a way to use runtime parameters as filters in Data Prep Studio?

For example, if we are hooked up to a SQL database of clients and want to only extract data for one client at a time and a certain date range - specified at run time - can we achieve that by setting up "Company", "Start Date", and "End Date" runtime parameters in DPS?

Thanks!
------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"

Steve,

Yes, that might be an appropriate way to go about it. We will have play around with that and see if we can make it work for our needs. We're just beginning to dive in to all of this. so I haven't even touched on scripting within Automator yet.

Thanks for you help and I'm sure I'll have more questions at some point!

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
-------------------------------------------
Original Message:
Sent: 02-07-2019 08:35 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

To expand a little, would the following workflow be appropriate?

1. Create a text file called Parameter.txt, for example. This would contain the three 'runtime' values
2. Have a pre process VB .NET script that reads this parameter file and uses it to form a SELECT statement to save the specified records to a static file.
3. Have a static workspace to perform your required transformations.

This could be monitored to run automatically on the arrival of the parameter text file.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 02-07-2019 07:26 AM
From: Steve Caiels
Subject: Data Prep Studio Runtime Parameter Filter

Hi Justin,
Agreed - this approach would not work well with automation. How were you planning on passing the runtime parameters? Could an option be to form the SELECT statement using VB .NET and running it as a pre process script to download the required records to a static local file (or separate SQL table) that could be passed to DPS?
Cheers,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-07-2019 07:08 AM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Steve,

Active Cust is the Runtime Parameter.
Active is the calculated field that flags the record (Note Bluegrass Records = 'Yes')

This would require the entire dataset to be downloaded. If the aim is to avoid this, then you should be able to connect to the SQL source using the SQL connector. This gives you the ability to extract via a simplified form or using a full query where you can use WHERE clauses.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253

Original Message:
Sent: 02-06-2019 02:52 PM
From: Justin Cox
Subject: Data Prep Studio Runtime Parameter Filter

Hi,

------------------------------
Justin Cox
Senior Applications Developer
Cbiz, Inc.
------------------------------
"