If you do not know your metadata, you do not truly know your data. Understanding the structure and limitations of a QlikView data model is crucial to correctly interpreting and managing your applications. Many developers and architects are not familiar with the tips described in this article that allow for extremely simple extraction of metadata from any QlikView application.
A QlikView application, with all of its attractive components (data model script, chart objects, variables, etc.), is essentially nothing more than an XML document with a different extension. For those that have experimented with project (PRJ) files in QlikView, this should be somewhat intuitive, as you have already seen how a single QVW can be broken out into its individual components automatically. You can also easily reassemble these PRJ files back into a working QVW. However, a somewhat lesser-known fact is that a QVW itself (without being broken out into project files) can be used an XML source for analyzing metadata. Doing so unlocks a world of potential for system administrators and BI architects. And the best tool to read a QVW's XML backend? You guessed it—QlikView!
Reading a QVW as XML
The first step to reading a QVW's metadata in the way described in this article is to create a new QVW that will be used for metadata administration. This management application (which we will call the Meta QVW for simplicity) can use multiple QVWs as a source, creating a one-stop show for metadata analysis of your entire QlikView infrastructure.
First, let's create this application and named it MetaQVW.qvw. For purposes of this article, we will use the widely-available Pharma Sales application (link) as our source. In MetaQVW, open the script editor, select Table Files, change the drop-down from "All Table Files" to "All Files". Find Pharma Sales.qvw on your computer and open it.
You will see a display of gibberish. Switch the "File Type" radio button to "Xml" and the data will magically decode. Press "Finish" and the QlikView script will auto-populate with a slew of tables.
Cleaning the Fields
If you reload MetaQVW.qvw as-is, the result will be an application rife with synthetic keys and loops. To avoid this, and create an application that can be meaningfully used to analyze metadata, we will need to drop certain irrelevant fields. For an example of how to properly structure this data model, see the attached QVW:
To see the solutions discussed in this article in detail, feel free to download the following QVW: MetaQVW.qvw
The final data model should look something like this:
Analyzing the Metadata
Unfortunately, as you can see from the data model, the metadata is not 100% interconnected. However, meaningful analysis is still quite possible on subsections of the data. For instance, you can quickly analyze the relationships between tables and fields in the data model, and even perform a cardinality analysis. Likewise, you can quickly analyze chart expressions to ensure that best practices are used. The possibilities opened with the freedom to analyze QVW metadata at will are virtually endless so—happy Qliking!
Is it possible to fetch the location where expression getting use.
Like in the Setting->Expression Overview, Location field is there.
Expression is linked to SheetObject in the data model.
Thanks a lot for the post.
Well some qvw are not showing their content in xml while loading QVW as xml is there any workaround ?
Not sure how you got that to happen, I’ve never encountered a non-corrupt QVW that you couldn’t load as XML.