I want to collect performance measures regarding the import of data
and the growth of resulting extract_tables.
I use - say - 15 tables from a erp-system (like JDE Edwards)
to build a -say - sales-warehouse and a MS-OLAP-cube.
For every incoming table I got a dts-package witch is
protocolled into msdb.sysdtspackagelog.
Every package got the name
The destination table is namend e.g. extr_F0005
With a seperate DTS-package I transport the
records from msdb-db into my build-db
- say - JDEdwardsExtract.
(eventually filter on buildname, because there are several builds in
So this result is quit good and easy to handle for
seeing elapsed time per day.
But the dtslog won't tell me, how many records the dtspackage
had to copy.(and there is one at least with no records (Cubeupdate))
Now the count(*) comes in.
In the dts-package
sys...log ---- to --- extr_performance_monitor
I added the columns
select name, 'extr_' + replace(name, '[Build]_[Subsystem]','') as
extr_table_name from extr_performance_monitor
I cut the original dts packagename down to the extr_.. name.
I think about a package wich is running after the last
data_import (and cube_refresh) is done.
(but the same day)
So the result could be:
Table_name (as dimension category)
Time to perform
Number of records in import table
Records per second.
The next step could be to look for required space.
The result should be a grafik - say - over 12 month
were you can easily see the amount of data performend
time consumend, (table space used),
and - very important -
you could extrapolate your hardware requirements.