Domo workbench - "attempting to open data source"

Jbrorby
Jbrorby Contributor
in Workbench

When a workbench job is executing, does anyone know what is happening when the preview window execution step says "attempting to open data source"? We will have a job that gets stuck on this step for several hours and it is unable to get past this step, then the query times out. The workbench job is executing a stored procedure in our sql server database

preview window.PNG

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    It's attempting to connect to the database but running into connection issues. Are you able to get a response from the server that's hosting the data source? Are you behind a firewall that would restrict access?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Jbrorby
    Jbrorby Contributor

    I have asked the firewall question to someone with more knowledge than me, but I do know the we have other workbench jobs set up on the same database/server that are connecting and running successfully.

    This job that is giving us the "attempting to open data source" struggles has done this many times in the past. It is a very massive stored procedure (425 columns, 1.8 million rows) and typically takes 1.25-2.5 hours to run. We know that the width of this stored procedure is causing us problems in general, but I just don't know if that would have anything to do with the "attempting to open data source"

  • DTGeorge
    DTGeorge Member

    Hello, I'm experiencing this exact same problem. We are executing a bulky Stored Procedure within DOMO Workbench from a SQL Server database that consists of tens of millions of rows. This was a proposed alternative to running the query directly in DOMO due to long and inconsistent run times.
    While the Stored Procedure is capable of running in a fraction of the time the original query took to run, it also gets hung up on this early step, "Attempting to open the data source.". And yes, we did create a separate job to test the connection to the database.
    Any advice on what this error message is indicative of and any guidance on how to resolve it would be greatly appreciated.

  • Jbrorby
    Jbrorby Contributor

    @DTGeorge I still don't know what exactly causes this, but we ended up paying a database administrator (we don't have our own) to really fine tune our stored procedure. She utilized a lot of temp tables and indexing. I am by no means qualified to talk in depth about why this helped, but I believe the gist of it was that temp tables use memory and so it is "easier" or "quicker" on the database.

    For example, we are pulling 4 years of data, but I wasn't specifying the 4 years until the very end of the query, so the procedure was going through the entire history of all the tables I am joining, and then at the end narrowing it down to 4 years.

    She put a temp table at the very beginning narrowing it down to the primary keys and 4 years of data, then used this table to for the joins of the other tables.

    Her work was a lot more complex than this brief explanation, but after she cleaned it up we have no longer had this issue.

  • DTGeorge
    DTGeorge Member

    Interesting, thank you very much for your response! We also implemented temp tables as part of this stored procedure solution, but similarly to what you described, we have a date filter being applied to the main table later in the process. I'll review this with the team that prepared the stored procedure to see if we can implement an additional temp table at the beginning of the stored procedure.

Categories