topic Re: How to Check if Tables aren't being used? in QlikView

How to Check if Tables aren't being used?

benvatvandata — Tue, 25 Sep 2018 19:41:37 GMT

Hi,

I am trying to reduce the database size and increase the server performance by finding and removing any tables that aren't being used in reports/sheets/objects/etc... is there a quick way of doing this besides manually going through each sheet and checking what fields are being used in each object?

Thanks in advance,

Ben

Re: How to Check if Tables aren't being used?

nsetty — Tue, 25 Sep 2018 19:51:26 GMT

Document Analyzer V3.0 Update Available

Re: How to Check if Tables aren't being used?

benvatvandata — Tue, 25 Sep 2018 20:31:36 GMT

Hi Nagesh,

Unfortunately when I try to open the .qvw I get the error, "The license that is embedded in this document is not valid"... Is there a way around this? Or any other easy ways to quickly see which tables are not being used?

Thanks,

Ben

Re: How to Check if Tables aren't being used?

rwunderlich — Tue, 25 Sep 2018 20:58:57 GMT

Please download the document analyzer again. I've removed the embedded license.

Search Recipes | Qlikview Cookbook

-Rob

Re: How to Check if Tables aren't being used?

benvatvandata — Wed, 26 Sep 2018 21:39:15 GMT

Hi Rob,

For some reason when I create a -prj directory I am unable to extract the metadata, and I get a 'failed to load' error when attempting to... and after that I can't even open my original file anymore ('failed to load')... do you know the cause of this?

I was able to get the DocumentAnalyzer_V3.8.qvw to work if I didn't create a -prj directory, but this only worked on a sample file I just created... the file I'm actually trying to analyze is much, much larger, and contains hidden script (of which I do not have access to)... so before I try to analyze this file (since it takes over an hour just to copy it to my computer... and since I've already tried this once with creating a -prj directory, but ended up not being able to open my file anymore), will the hidden script or my limited security access be an issue?

Thanks,

Ben

Re: How to Check if Tables aren't being used?

benvatvandata — Thu, 27 Sep 2018 15:52:49 GMT

I'm sure that analyzer works great, but unfortunately it seems like my system can't handle running it for the large file... just takes up too much memory... is this the only way to see unused tables quickly?

Re: How to Check if Tables aren't being used?

rwunderlich — Fri, 28 Sep 2018 16:21:54 GMT

Ben,

I recommend that you analyze without a prj file. There are occasional problems with prj files that may be getting in your way.

You can analyze a large document by making a copy with reduced data. You will still get valid results as to used fields and tables. Open the document, make some selections and use the menu item File, Reduce Data, Keep Possible Values to make a reduced copy.

-Rob

Re: How to Check if Tables aren't being used?

benvatvandata — Mon, 01 Oct 2018 16:30:51 GMT

Unfortunately my document is barely any smaller after reducing the data:(... If I have any smaller files I'll keep this Analyzer in mind... You seem very knowledgeable about this though so I guess I'm wondering why my file would only be 1 KB smaller? Would this analyzer work if I removed all data (like would it tell me what tables/fields are currently being used in objects, etc.)?

Thanks,

Ben

Re: How to Check if Tables aren't being used?

rwunderlich — Mon, 01 Oct 2018 17:36:41 GMT

Hi Ben,

Yes, you can analyze a document with no data. You will get a correct analysis of field usage.

That's odd that your doc got in;y 1k smaller. Did you make selections that represented a small subset of data? Do you have large tables that are not connected?

-Rob

Re: How to Check if Tables aren't being used?

benvatvandata — Fri, 26 Oct 2018 00:21:05 GMT

Hi Rob,

Sorry for such a late response to this - I did want to say that the file with no data worked though!

Regarding the reduced data file... I'm assuming if you make selections and then go to a different sheet with the trigger to 'clear all' on sheet activation, the 'reduce data' option doesn't work well? The document I was working with had a large about of buttons and sheets to navigate through different reports, and most of them (if not all) have that trigger so I ended up trying to get my large file to work instead...

However, I was not able to finish a reload after waiting at least 3 hours several times... How large of files have you been able to run the analyzer on? And how long does the reload typically take?

It seems like my file always freezes during the reload... my file was 1,988,551 KB (now I got it down to 819,738 KB)... Is this too large to run your analyzer on a Server with 60 GB RAM?

Thanks,

Ben

Re: How to Check if Tables aren't being used?

rwunderlich — Mon, 29 Oct 2018 11:25:50 GMT

Hi Ben,

I've run DA against some very large documents but I have seen (and fixed when I can) problems when there are many thousands of sheet objects.

Re reduced data. After making selections, you must use the menu commands, File, Reduce Data, Keep Possible Values and then save a reduced copy.

-Rob