We have a scenario where we have a large data source that is currently used to produce a single QVW. What we would like to do is produce a number of smaller QVWs using e.g. Publisher - however, we would like to define the reload schedule and destination folders that these smaller QVWs end up in from an external MySQL table (or config file or similar), rather than through the Publisher GUI. Has anyone done anything similar, or got any recommendations on how to approach this?
Original Post on LinkedIn
The package is executed from the left to the right starting from "Init Variables" object
First of all, we've created the following table to store parameters, it can be extended if necessary
Variables are used to replace one string with another, for example anywhere in the package where <RoorDirectory> is found it will be replaced with c:\Customers.
Advanced ETL Processor is a perfect solution for automating QVX file generation in a complex enterprise environment
The latest version of Advanced ETL Processor can create 14000 QVX records per second, approximately 1 million records per minute.
This is 10x increase comparing to the previous version!
There are two ways of creating QVX files using packages or from the transformation. It is possible to save all tables in the database into QVX files with several clicks. And it is also possible to perform complex data transformations.
Download Q-Eye 32 Bit Download Q-Eye 64 Bit Download Q-Eye Portable Version
Here are some examples:
SUB INSPECT (T) LET LocalAppDataPath = GetRegistryString( 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion' , 'ProgramFilesDir' ); LET QViewerPath = '$( LocalAppDataPath )\DB Software Laboratory\Q-Eye\QEye.exe'; STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd); EXECUTE "$(QViewerPath)" "$(QvWorkPath)\~$(T).qvd"; EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";ENDSUB
SUB EDIT (T) LET LocalAppDataPath = GetRegistryString( 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion', 'ProgramFilesDir' ); LET QViewerPath = '$( LocalAppDataPath )\DB Software Laboratory\Q-Eye\QEye.exe'; STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd); EXECUTE "$(QViewerPath)" "$(QvWorkPath)\~$(T).qvd"; DROP TABLE $(T); LOAD * FROM [$(QvWorkPath)\~$(T).qvd] (qvd); EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";ENDSUB
If we want to edit the table we justCALL EDIT('Log');
Or if we want to view the table we use this codeCALL INSPECT('Log');Note: By default Q-Eye uses partial load, to avoid problems load the entire QVD file before editing it This example is based on:http://bi-review.blogspot.co.uk/2012/12/how-to-look-inside-resident-tables-at.html
Performance is not guaranteed and depends on number factors such as: Hardware configuration, Size of files, Number of distinct fields values
Since QVD is a closed format it is not possible to test it completely. Therefore we would like to ask QlikView users to assist us with testing.
Find the largest possible qvd file and load it completely into Q-Eye (Record the time took) Find the largest possible qvd file and load it completely into QlikView (Record the time took)
Open a number of qvd files and load them completely into Q-Eye. Save the files. Load the files into QlikView and check for data corruption.
Create a QVD file manually by editing the data in Q-Eye. Make sure that you are using all possible formats (text, numeric, date) and languages (We are especially interested in testing non-English languages) Save the file. Load the file into QlikView and compare the data.
Please support the development of Q-Eye by donating
A new version of Advanced ETL Processor is available for download. In this version, we have enhanced support for QVX QlikView files.
Load data from QVX files Generate QVX files UsesQVX_QV_DUAL field type (the fastest way of loading data into QlikView) Can process files generated by QlikView itself
We would like to thank our customers for providing useful feedback
This task was particularly challenging for our development team because there is no documentation for QVX_QV_DUAL field types
The largest QVD file has 22 million records and it is 5.9 gigabytes in size
Using Advanced ETL Processor 64 bit and latest tableau SDK (9.2)
In this version we have made a lot of changes to the file metadata dialogue:
Added File Meta-data DialogueAdded Auto Format OptionAdded Single copy optionAddressed issues with the Single copy optionAdded Load Status to the status barIt is now possible to rearrange tabsAdded donate buttonAdded About buttonAdded Support buttonAdded List of Recent filesRemoved menu
The day has come: Q-Eye is available from Microsoft App Store
It took a lot of time and effort but finally, we are there. We would like to thank Microsoft staff for their assistance.
Note: Q-Eye is not on Qlik Market despite the fact that the number of people and companies promised us to help with this matter. We believe that eventually, it will happen. Meantime we will continue making it better.
Today we have not only great news but the one we are proud of, As you can see from the screenshots below our QVD Editor Q-Eye is incredibly fast now:
it loads 31 million of records in 4 seconds, That is 7.3 million of records per second
We still have some room for improvement for memory usage:
The same file is loaded into QlikView:
We have great news for all QlikView users today.
We have managed to achieve such performance increase by carefully optimizing code, removing bottlenecks and checking memory usage
It took Q-Eye 3-7 seconds to load the file and QlikView between 10 and 17 seconds.Q-Eye was always faster than QlikViewThe actual result depends on the hardware and memory allocation by windows. Loading same file second time usually faster.Users can also use Advanced ETL Processor to transform and create QVD files
Version 5.0 of Q-Eye is available for download.
Updated user interfaceExtended support for QlikSense QVD filesBetter support for Timestamp and Date fields
Please support development of Q-Eye by donating
Changes are:Updated Metadata dialogue
Comparison of data loading strategy into QlikView
+ Up to 2 times faster data extraction from SQL Server + Up to 2 times faster data extraction from ODBC sources + Up to 40 percent faster loading data into SQL Server + Up to 40 percent faster loading data into ODBC + Up to 10 percent faster QVX files creation + Up to 10 percent faster loading data into PostgreSQL - Various bugs fixes and improvements
Those people who work with QlikView know that it requires a direct connection to the database to pull the data from, which is not possible to implement in some situations. One of the solutions is to use QVD files but again there is no way to create them without using QlikView.
In version 10 QlikViewhas introduced support for QVX files.
QVX (QlikView Data eXchange) is a new file/stream format for high-performance data input into QlikView. A QVX formatted file contains metadata describing a table of data and the actual data. In contrast to the QVD format, which is proprietary and optimized for minimum transformations inside QlikView, the QVX format is public and requires a few transformations when exporting data from traditional database formats.
Advanced ETL processor and Visual Importer ETL allow the user to export the entire data warehouse into QVX files just within a few clicks and no programming
Data can be generated from virtually any data source.
All data is exported as UTF8 text using row delimiter