I am trying to create data model for profit and loss balance sheet requirement where I have 20,000,000+ records.
for this the General ledger information is coming from a spread sheet, the common key between spread sheet and the Fact table is GL Account ID, and also unit ,department, and amount fields are coming from 3 different tables. UNIT, DEPARTMENT and FACT:
here in my fact table I have Account id and GL Account ID, the relationship between these two fields is many to one.
here I need an aggregated fact table by GL Account ID, Fiscal year, month, unit, department,
two achieve this I have loaded my data as follows:
Fact data is coming from binary load initially, then I have made some transformations on the loaded fact table, then I deformalized it in to 2 tables with a combined key between these two tables. then prepared an aggregated table with ,GL Account ID, Fiscal year, month, unit, department.
now when I reload the Application it is taking nearly 2:40 hours on my machine with 8 GB ram. please suggest me the any best approach to reduce the execution time and better way to optimize the application.
