Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I did not know what else to post it as 
Hi Qlikers
As promised I will go into the server side scripting aspect with a little bit more of an explanation on how we are using the CSV Batch Load process.
PREPARATION
First we will need to analyse our DB table.
We decided that the best and most efficient way of breaking a small/medium/large DB table into manageable chunks was to use an auto-incremental ID field and if one doesn't exist then we would create one.
Now we look at the total record count for our table, lets say we have a 35M record customer table and we want to break the table down by 20K records at a time.
35M / 20K = 1750 chunks, each chunk will be a csv file.
We will create 1750 records in our csv_ranges config table, thus each chunk will have a corresponding entry in the table:
1, users_0-20000, 0, 20000, 10, 0000-00-00 00:00:00
1, users_20001-40000, 20001, 40000, 10, 0000-00-00 00:00:00
1, users_40001-60000, 40001, 60000, 10, 0000-00-00 00:00:00
1, users_60001-80000, 60001, 80000, 10, 0000-00-00 00:00:00
...... etc
We also need to create an entry into the csv_query config table with the DB details for the table and we also add a simple SELECT query "SELECT * FROM users ", that the script will use later.
For our process to constantly run and update the CSV files we created the following DB tables that our script will loop over and extract information etc. I'm just going to list some key fields and not the full schema for each table.
csv_history
Each time a chunk is created or modified it gets added to this table, we can then analyise it to work out how often a chunk get updated, this will enable us to automatically update the refresh value for the chunk in the csv_ranges table.
table_id
chunk_name
timestamp
refresh_value
csv_query
This hold's a basic query string and table connection details.
table_id
table_name
db_name
auto-incremental ID field name
dns
query
enable
csv_ranges
We build multiple ranges for each table that's in the csv_query table.
table_id
chunk_name
min_range
max_range
refresh_value
last_updated
locked_by
md5
I'm not going to explain the reasons why we choose this way of doing things as we could chat all day about it and I just want to give you the juicy details.
One of the problems we encountered was with multiple processes updating the same chunk, so we added a locked_by process field, this eliminates that problem.
OK thats the preparation taken care of.
THE SERVER SIDE SCRIPT
This is a relatively straight forward process and I'm just going to talk about the logic involved not the code, also I'm going to use our 35M record users table as an example, but we loop over the csv_query table which has 100+ tables in it.
We run this script six times on three different backend servers, this gives us a grand total of eighteen processes to create/update the CSVs, the beauty of this process is its fully scalable if we add another 100 tables to chunk, then we just add more backend servers.
FOREACH: Extract our users table config from the csv_query table.
FOREACH: Extract the ranges from the csv_ranges table to loop over.
Has the current chunk for users_0-20000 been created yet.
Lock chunk in the csv_ranges table.
Build query for range: SELECT * FROM users WHERE {auto-incID} >= {min_range} AND {auto-incID} <= {max_range};
MD5 results from query and set last_updated timestamp in csv_ranges for current range.
Create CSV file users_0-20000.csv.
SCP CSV file to Qlikview server.
Add entry in the csv_history table.
If the current time is greater than (last_updated + refresh_value) for the users_0-20000 chunk
Lock chunk in the csv_ranges table.
Build query for range: SELECT * FROM users WHERE {auto-incID} >= {min_range} AND {auto-incID} <= {max_range};
MD5 results from query.
If the current MD5 hash is different from new MD5 hash
Replace CSV file users_0-20000.csv.
SCP CSV file to Qlikview server.
Add entry in the csv_history table.
THE QLIKVIEW SERVER
Create a QVW to import CSVs and store as QVDs, its not quite as simple as that, but you get the picture.
+++++++++++
Thats it I'm afraid, I will tidy up all my posts on this subject and add it to the resource library.
If you have any questions on that matter then please ask, or if you want me to video capture the process then the more likes I get will persuade me.
Just a final word on the speed of the process, I rebuilt the 35M users table yesterday and looking at the created time of the first and last QVDs on the Qlikview Server was 10 minutes total creation time, and thats with adding new fields and calculations to the QVW load script .... SWEET AS A NUT 