Optimising Transactional Table for Resident Reload

[#1]

Hi All,

I have a general ledger transaction table which is used for financial statements. This requires transactional level detail for drill-down analysis.

I use the same transaction table for a resident load, creating a new table which is summarised down to a number of lines.

i.e.

Net Profit Before Tax

Depreciation & Amortisation

Capex

Cash Generated / (Used) Before Tax

This requires no transactional level detail but is still required to be date driven by a date calendar.

In my resident load, I am simply reloading the transaction table where it meets a particular condition.

E.g.

LOAD 'Depreciation & Amortisation' as Group,

Date as GroupDate,

If(GLAC = '1234',GLTran) as GroupTran

RESIDENT GL;

This is extremely inefficient as the transactional table is large and I am not reducing the data down effectively. As mentioned, I still require the new table to be date driven and linked to my date calendar, which is why I can't reduce it with a 'Group B'y to anything higher than a Month-Year.

Any ideas on how I can do this smarter?

Thanks

[pover]

Hi Matt,

Why are your GL Acct groups date driven? To avoid them disappearing from the report if your is no transaction in the period requested or do the GL Acct groups change over time? I use set analysis for my financial reports, not flags, so I don't know if that has something to do with you design decisions

When I do financial reportes my GL Acct Group table (Revenue, COGS, Gross Profit, etc.) is linked to the transaction table only by the account or range of accouts and both tables don't grow overly large. I've also found it easier to do the grouping in Excel instead of directly in the script.

I do something similar to the QlikTechs finance demo of JD Edwards. If you haven't seen it, that might help.

Good luck.

[#3]

The second table I am creating is not a simple copy ot the initial table. (i.e. sum of general ledger accounts). It has other components from different ERP modules and a specified sort order and formatting requirements.

I am looking for the most efficient way to reference a resident table and reduce the rows of data down so i'm not building a table with 10 million+ records when I only need one record, per line, per month.

I think i've answered my own question here, a group by on MonthName(Date) would be the most efficient approach.