SQL Queries for SQL Server Datasource

user095972

Hi Team,

We are planning to migrate from Cognos to Domo.

1. Issue is we have prebuilt Custom SQL queries with Joins/Unions/logics etc,Views which should be as it is used in Domo.

I know using Domo workbench we can create a connection to Onpermisses database but will it allow complex SQL queries which will be of 2-3 pages length to be integrated in DOMO. 

2. Currently few excel reports we have to rebuild in Domo. I know domo has excel connector but is it possible to join 3-5 different sheets in single dataset. I mean do we have some data modeling abilities in excel connector ?

What i know is create 3-5 individual datasets and then combine them using Magic ETL/Fusion. But i just want one single dataset in which i can create all instead of depending on multiple objects/steps.

Please suggest.

jaeW_at_Onyx

This is a doozy of a question, and i strongly recommend you find someone who really knows their stuff to help you plan out your implementation ...because you don't want to spin your wheels.  (that said, refactorign in Domo is pretty easy so it's flexible enough if you have to rework your pipeline.)

 

1. Issue is we have prebuilt Custom SQL queries with Joins/Unions/logics etc,Views which should be as it is used in Domo.

- So many choices here.  You can leave your VIEWS and custom Queries in the Source SQL database, and then use Workbench to call those pre-defined VIEWS to pull data into Domo.  Of course the downside is, you're pulling transformed data into Domo, so if your business users request a change to data, there's that friction of ... where should they make the change in Domo? (and effectively make a VIEW on top of a VIEW of the data?) Or do they have to wait for BI/IS to implement the change in the source system which could... take time.  ... I would give different advice depending on the circumstance, but usually my preference is to access data that's as raw as possible or at least, still in a Fact Table  / Dimensional Format and then do any JOINING and UNIONING in Domo.

 

I know using Domo workbench we can create a connection to Onpermisses database but will it allow complex SQL queries which will be of 2-3 pages length to be integrated in DOMO. 

- BTW you could alternatively copy the definition of the SQL VIEWS into the Workbench Connectors to make it a bit easier to move changes in SQL queries into Domo w/o impacting the actual Server.  But thent there are considerations depending on how frequently you want to move data and how much that may impact the source system if the custom queries haven't been optimized.

 

2. Currently few excel reports we have to rebuild in Domo. I know domo has excel connector but is it possible to join 3-5 different sheets in single dataset. I mean do we have some data modeling abilities in excel connector ?

-- not really.  I'm reasonably confident the Excel connectors in Domo will only pull in one table / spreadsheet at a time.

-- in Workbench it is possible to integrate custom scripting and plugins, so if you had the skills to device a data processing pipeline that consolidates the data BEFORE it hits Workbench / Domo's Dataset API you could consolidate workbooks BEFORE ingestion.  (Workbench, Java CLI and the Connectors all more or less use the same Dataset APIs to get the data into Adrenaline)  I've definitely seen and prototyped solutions using a python or a java script to flatten excel files before pushing the data into Domo.  Just use your favorite tool of choice.

 

What i know is create 3-5 individual datasets and then combine them using Magic ETL/Fusion. But i just want one single dataset in which i can create all instead of depending on multiple objects/steps.

-- see above.

-- that said, depending on the use case, it may be easier to process data UNION'ing in Domo, particularly if the workbooks get updated periodically (I'm thinking of 'end of month Financial reporting use cases) where it may be difficult to upload / keep track of every previous version of the workbook from previous months.  In that case it may be easier to upload the workbooks / worksheets as individual datasets and then use Enterprise Stacker App to Bulk Union the data (I'd avoid Magic for big UNION jobs if possible.)

I appreciate that's a lot of word vomit, but I hope it's helpful getting you started.

 

I do offer a freelance consulting service if you're looking for more structured guidance.

Please suggest.

jaeW_at_Onyx

This is a doozy of a question, and i strongly recommend you find someone who really knows their stuff to help you plan out your implementation ...because you don't want to spin your wheels.  (that said, refactorign in Domo is pretty easy so it's flexible enough if you have to rework your pipeline.)

 

1. Issue is we have prebuilt Custom SQL queries with Joins/Unions/logics etc,Views which should be as it is used in Domo.

- So many choices here.  You can leave your VIEWS and custom Queries in the Source SQL database, and then use Workbench to call those pre-defined VIEWS to pull data into Domo.  Of course the downside is, you're pulling transformed data into Domo, so if your business users request a change to data, there's that friction of ... where should they make the change in Domo? (and effectively make a VIEW on top of a VIEW of the data?) Or do they have to wait for BI/IS to implement the change in the source system which could... take time.  ... I would give different advice depending on the circumstance, but usually my preference is to access data that's as raw as possible or at least, still in a Fact Table  / Dimensional Format and then do any JOINING and UNIONING in Domo.

 

I know using Domo workbench we can create a connection to Onpermisses database but will it allow complex SQL queries which will be of 2-3 pages length to be integrated in DOMO. 

- BTW you could alternatively copy the definition of the SQL VIEWS into the Workbench Connectors to make it a bit easier to move changes in SQL queries into Domo w/o impacting the actual Server.  But thent there are considerations depending on how frequently you want to move data and how much that may impact the source system if the custom queries haven't been optimized.

 

2. Currently few excel reports we have to rebuild in Domo. I know domo has excel connector but is it possible to join 3-5 different sheets in single dataset. I mean do we have some data modeling abilities in excel connector ?

-- not really.  I'm reasonably confident the Excel connectors in Domo will only pull in one table / spreadsheet at a time.

-- in Workbench it is possible to integrate custom scripting and plugins, so if you had the skills to device a data processing pipeline that consolidates the data BEFORE it hits Workbench / Domo's Dataset API you could consolidate workbooks BEFORE ingestion.  (Workbench, Java CLI and the Connectors all more or less use the same Dataset APIs to get the data into Adrenaline)  I've definitely seen and prototyped solutions using a python or a java script to flatten excel files before pushing the data into Domo.  Just use your favorite tool of choice.

 

What i know is create 3-5 individual datasets and then combine them using Magic ETL/Fusion. But i just want one single dataset in which i can create all instead of depending on multiple objects/steps.

-- see above.

-- that said, depending on the use case, it may be easier to process data UNION'ing in Domo, particularly if the workbooks get updated periodically (I'm thinking of 'end of month Financial reporting use cases) where it may be difficult to upload / keep track of every previous version of the workbook from previous months.  In that case it may be easier to upload the workbooks / worksheets as individual datasets and then use Enterprise Stacker App to Bulk Union the data (I'd avoid Magic for big UNION jobs if possible.)

I appreciate that's a lot of word vomit, but I hope it's helpful getting you started.

 

I do offer a freelance consulting service if you're looking for more structured guidance.

Please suggest.