I was wondering if there is some benchmarking information about loadtimes and server sizing. We are facing a new customer with a JD Edwards implementation in a DB2/400 database.
The total size of the production database is about 550 gygabyte, the production database contains 3770 tables of wich we will be using approximately 430 tables. The largest table is the general ledger table with 125,000,000 rows. There are several tables more with above 10 million rows and a lof of tables with more than 1 million rows.
We are probably facing a tight load window and the customer wants to know before we start what loadtimes to expect.
So I'm looking for figures on databasesizes, loadtimes and number of tables, anyone willing to share some insight?
With my experience you won't be able to get a relevant information without bench on your database, it's very dependent on the data model in QV and how you organise the loading.
For us, we had 65 000 000 rows in the fact table, about 2 000 000 in a dimension and 250 000 on another one, we have a loading time of 5 hours for a full load including extraction for oracle and qvd transformation. Running on a 16Go RAM, 64 bits, 4 CPU.
Hope it's helps,
this is a huge database. Your biggest tables (like the GL, and the Sales Invoices F4211/F42119) will ultimately determine the length of the load. It's quite irrelevant to count all various tables and their sizes, before you have an idea about the specific applications that the customer wants.
I can't quite give you any benchmarks (all my JDE loads have been much smaller in size), but here are some recommendations about possible solutions:
1. Data load is a single-threaded process and it's fully utilizing the CPU speed. Data load tasks, however, can be broken manually into separate sub-processes (for example, one process loads Sales table, while another process loads GL). Conclusion: the server needs to have as many cores as possible, and the CPU speed matters a lot. Don't settle for a 2.0 GHZ processor... The best advise for the customer - get the fastest hardware you can afford.
2. Architecture your data load process with performance in mind. For example, load history data into QVD files and perform all necessary calculations up-front, leaving the absolute minimum to the nightly load.
3. Critically review all load scripts from the performance standpoint. For example: the standard JDE Sales Analysis (available to partners for free) has a small macro CONVERT_DATE that converts JDE Julian date into a QlikVIew date. The standard script calls the macro for all dates in the Sales table (F42119), which is just fine when you need to load a million records or so. With your data volumes, you can't afford such luxury, it's too slow. You'll have to keep Julian dates, and then link them to a Calendar Table and translate the date there (the difference in processing time is HUGE!).
4. Keep your front end very-very simple! You won't be able to afford any advanced and fancy stuff like you can with smaller data volumes.
I hope those tips and tricks help... If you need more detailed assistance with your project, drop me a line.
Thanks for your replies, very helpfull!
It gives me at least an idea where to start. I do have the JDE templates from qlikview. Do you happen to know if they are "certified" for version EnterpriseOne 8.12?
Any chance we can speed up things when loading incremental? One full load of 48 hours isn't a big problem (although it's very very long), when an incremental or daily load is possible in a fraction of the 48 hours.
@Oleg, are you one of the developers of the JDE templates? Or in any way very experienced with JDE?
The JDE templates that have been floating around QlikTech are a nice reference, but they're certainly not "plug-and-play". (Unless they've been completely overhauled in the last year.) Yes you can do decent incremental loading on JDE, but it takes a little more work with the Julian date system.
Yes, you'll almost have to implement incremental load, using QVD files to store older data. To achieve that, the whole load script needs to be developed with the "incremental" theme in mind.
I'm not sure about versions of JDE...
I'm not a huge JDE expert and I haven't developed the JDE template, but many of my clients are JDE accounts, and I learned a lot on their projects. I've used and enhanced the JDE templates to my clients' needs.
For example, I developed a technique to load UDC codes "dynamically", based on a spreadsheet. It helps adjusting your solutions to the individual client needs without re-writing the script.
My e-mail address is firstname.lastname@example.org
totally agree with you - "nice reference" and "not plug and play".Far from it, actually...
I don't have any experience with JDE, but I do load large amounts of data from DB2 on z/os. I'll second what others have said about using incremental loads and suggest some other areas for optimizing load performance.
I've found that my QVP server can handle the data as fast as I can get it (with a simple script). The factors that can impact how fast the data is fetched are:
1. Priority of the query task on the server (DB2/400).
2. Network bandwidth between the AS400 and QV.
3. Fetch size configured in the driver (not all drivers are configurable).
4. Add "FOR FETCH ONLY" to the SQL SELECT to avoid lock contention.
5. Make sure your predicates use an index -- especially relevant in incremental loads. Use DB2 Visual Explain or another tool to tune your queries.