Prepare Trial Balance using Oracle Managed Cloud data

Reply
Highlighted
White Belt

Prepare Trial Balance using Oracle Managed Cloud data

We have migrated Oracle Managed Cloud data to DOMO and now trying to develop a Trial balance report.

 

I have added an attached query to create Dataset. When I run the data set it is running continuously and no completion.

 

I tried creating a small query to run but still the same issue. I am using MYSQL for the query. The same query is working fine from the Oracle database quickly.

 

We have approx 3.3. M records

 

select

  gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)

    from `fmc_gl_balances`

    where ledger_id =  gb.`LEDGER_ID`

      and `ACTUAL_FLAG` = 'A'

      and `PERIOD_YEAR` = gb.`PERIOD_YEAR`

      and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`

      and `PERIOD_NUM` =  gb.`PERIOD_NUM`-1

      group by ledger_id,

              period_year,

              period_num,

              code_combination_id) begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` between 2 and 12

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

UNION

select

 gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)

    from `fmc_gl_balances`

    where ledger_id =  gb.`LEDGER_ID`

      and `ACTUAL_FLAG` = 'A'

      and `PERIOD_YEAR` = gb.`PERIOD_YEAR`-1

      and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`

      and `PERIOD_NUM` =  12

      group by ledger_id,

              period_year,

              period_num,

              code_combination_id) begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` = 1

and substring(gc.`SEGMENT2`,1,1) in ('1','2','3')

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

UNION

select

 gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

, sum(gb.`PERIOD_NET_DR`) p_net_dr

, sum(gb.`PERIOD_NET_CR`) p_net_cr

, 0 begin_bal

from

 `fmc_gl_balances` gb

LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`

and gb.`ACTUAL_FLAG` = 'A'

and gb.`PERIOD_NUM` = 1

and substring(gc.`SEGMENT2`,1,1) in ('4','5','6','7','8')

group BY

gb.`LEDGER_ID`

, date_format(gb.`PERIOD_NAME`,'%b-%d')

, gb.`PERIOD_YEAR`

, gb.`PERIOD_NUM`

, gb.`CODE_COMBINATION_ID`

, gc.`SEGMENT1`

, gc.`SEGMENT2`

, gc.`SEGMENT3`

, gc.`SEGMENT4`

, gc.`SEGMENT5`

, gc.`SEGMENT6`

, gc.`SEGMENT7`

, gc.`SEGMENT8`

Highlighted
Major Red Belt

Re: Prepare Trial Balance using Oracle Managed Cloud data

This is not a Domo problem, this looks like a SQL optimization problem.

Do you have any indexes?  Have you tried paring down your query until it runs?

 

If you're not good at optimizing SQL, have you considered using MagicETL?  There's nothing you're doing in your SQL that you can't do in Magic, and Magic will ultimately probably be more performant given the transforms you're doing.

 

 


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.