How to utilize DPS's "SQL Server Connection" dialog box to filter by a dynamic date, such as "today."
Altair Employee
Altair Monarch Data Prep Studio (DPS) was formed in the cocoon with ease of use as a core tenet, making it very easy to apply filters in tables within the DPS interface. Unfortunately, all database data must be transmitted to your computer first, then Monarch processes the filter. This can result in performance degradation for large tables as all that data must traverse the network first.
The solution in this situation is to make the SQL server filter the data, sending only the relevant records.
Here too, Monarch DPS makes it easy. After providing credentials and selecting a database and table, all columns are displayed with checkbox/dropdown options to apply filters if needed. For date/time columns, you can type the current day into the text box. Clicking to preview or "OK" will return a table with that day's data only.
However, if you save this Workspace and come back to it in the future, you will need to edit the table info and manually change the date in that dialog box. This runs contrary to Monarch's other tenet of repeatability and automation.
Helpfully, there is a simple solution for that as well. After selecting options to enable filters and entering the current date, click on the "Query" tab. Here you will see that Monarch has written a query that corresponds to your selections from the "Tables & Views" tab. This may be a bit overwhelming, and this article will not address how to read a SQL query in general, but we will focus at the very end of the expression where it says WHERE [Column Name] = '12/7/2022'.
To metamorphosize this from a static expression, simply replace the date and the single quotes with the expression CONVERT(date, GETDATE()). Now you have a fully formed butterfly, and when you save the Workspace, the SQL Server will only transmit the data for the current day.
