topic Re: Structure of Symbol Tables using AutoNumber on Key fields in App Development

Structure of Symbol Tables using AutoNumber on Key fields

QlikWilliam — Mon, 14 Apr 2025 10:58:41 GMT

Hello,

Am I correct in understanding that if I use AutoNumber on a key field, for instance

AutoNumber(ProductID&CustomerID)

that the symbol table for the Key Field "ProductID&CustomerID" would look like this?

Pointer Value

that is, that the Value is empty because a look-up is not needed? Or would the symbol table look like this?

Pointer Value

1 1

2 2

Also is there a way to view the symbol tables? Thank you.

Re: Structure of Symbol Tables using AutoNumber on Key fields

Chanty4u — Mon, 14 Apr 2025 17:44:02 GMT

Yes the Value becomes a simple integer, and the original concatenated string is no longer stored in the field.

Or try this

AutoNumberHash128(ProductID, CustomerID)

Re: Structure of Symbol Tables using AutoNumber on Key fields

rwunderlich — Mon, 14 Apr 2025 19:16:06 GMT

It would look like neither. When a field contains only sequential integers, the index value in the table row is used as the value. So there is no symbol table. I think you might be saying this in your first example.

-Rob

Re: Structure of Symbol Tables using AutoNumber on Key fields

QlikWilliam — Wed, 16 Apr 2025 09:45:44 GMT

Thanks for your replies!

I did not realize there would be no symbol table at all. But it makes sense.

Should we always use AutoNumber on key fields? Or is there a drawback?

Re: Structure of Symbol Tables using AutoNumber on Key fields

rwunderlich — Wed, 16 Apr 2025 18:36:03 GMT

I recommend always using the AutoNumber on key fields.

I would also recommend using the AutoNumber statement rather than the AutoNumber() function. Just put the statement at the end of your script. Much easier and much faster.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Structure of Symbol Tables using AutoNumber on Key fields

Qlik_William — Fri, 18 Apr 2025 10:04:03 GMT

Yes good point, I suppose this helps with optimized loads aswell

Re: Structure of Symbol Tables using AutoNumber on Key fields

Qlik_William — Wed, 04 Jun 2025 13:39:42 GMT

A related question to the topic of symbol tables.

Let's say I have 1M rows in the Fact table and a column "A" with 100 distinct values. So the data is repeating a lot for this column. Is this a problem because the symbol table will still only store the 100 distinct values?

Will it make a difference if I remove the column "A" from the Fact table, performance wise, and instead only store the 100 distinct values of A in a dimension table that is associated with the Fact table through some key field?

I guess there should be some performance advantage otherwise why would we not store all the fields in a wide fact table, instead of having dimension tables?

@rwunderlich @Chanty4u

Re: Structure of Symbol Tables using AutoNumber on Key fields

marcus_sommer — Wed, 04 Jun 2025 14:12:25 GMT

IMO it's rather unlikely that the outsourcing of fact-fields into extra dimension-tables will have benefits in regard to the performance. Within the most scenarios the opposite will happens.

Especially with huge data-sets and/or if the UI performance is the biggest bottleneck it might be helpful to merge all information into a single big table. That's rather seldom necessary and might be addressed after a working star-scheme data-model (officially recommended as best compromise between script/UI performance but also development/maintainability efforts) was carefully optimized.

Re: Structure of Symbol Tables using AutoNumber on Key fields

rwunderlich — Wed, 04 Jun 2025 14:59:42 GMT

You are correct that the symbol table will only store 100 values in either the Fact or Dimension case. The symbol table size is the same.

In terms of optimizing storage, placing a field in fact or dimension table is driven by how many fields could be in the dimension table.

In the fact table, you have 1M pointers for A. If you move A to a dimension table by itself you still have 1M pointers for A in the fact table. You will also have 100 pointers in the dimension table. This is inefficient and it's better to leave A in the fact.

If A were to move to a dimension table that contained multiple fields, there can be storage savings. For example imagine a set of ten "Customer" fields (Name, address, etc) with 100 values each. If placed in the fact, that be would 1M pointers * 10 fields. If moved to a dimension table, there would be only 1M pointers in the fact for the key field, and 100 pointers * 11 fields in the Customers dimension table.

-Rob

Re: Structure of Symbol Tables using AutoNumber on Key fields

Qlik_William — Thu, 05 Jun 2025 16:38:23 GMT

Thank you both for the replies, very insightful.

So I guess both the number of pointers and pointer size (and thereby the number of rows in symbol tables) matters in terms of performance.

But for fields that do not belong in dimension tables, for instance "Category" with no extra attributes. If you have 1M rows and 100 distinct values for "Category", how does the symbol table help compress this field?

You will then have 1M total pointers in the Fact (Data) table and 100 distinct pointers in the symbol table for "Category". I guess it takes less storage to store in this case a pointer of size 7, than the actual field values for "Category"?

Re: Structure of Symbol Tables using AutoNumber on Key fields

marcus_sommer — Fri, 06 Jun 2025 07:04:58 GMT

The distribution of the data into data-tables which contain only fields with bit-stuffed pointer and are associated with these pointers to the appropriate system-tables which contain only the distinct field-values is not really a compression else a quite efficient way to store data. The more redundant the data are the bigger are the benefits against linear storage-logic like the classical sql or csv.

On top may come a zip-like compression - at least in QlikView there is an appropriate setting - which could significantly reduce the storage-size but usually are the disadvantages of increasing the CPU consumption and storing/opening times greater as the saved storage.

Further possible are measurements like sorting and indexing the data and/or storing only min/max + offset values by incrementing-data or using the pointer as value and/or applying any kind of partitioning but it will also add complexity and increasing the CPU consumption.

In other words the native storage-logic of Qlik which becomes especially obvious by loading qvd's optimized or performing a binary load - which is a direct data-transfer from the storage into the RAM with nearly no data-processing - is quite near the maximum in regard to the performance.

Re: Structure of Symbol Tables using AutoNumber on Key fields

Oleg_Troyansky — Sun, 15 Jun 2025 23:53:22 GMT

@Qlik_William

I missed all the fun on this question...

I'll reflect on your last question - "If you have 1M rows and 100 distinct values for "Category", how does the symbol table help compress this field? "

The way Qlik does de-duplication and internal indexing of the data, with the Symbols table and the Index table, helps reduce the size of duplicate values. Instead of storing 1M rows with a 20-character string (for example), you only store 100 distinct values, and then 1M rows with a pointer - in this case it's a 7-bit pointer. 7 bits take approximately 20+ times less memory than 20 characters. So, roughly 95% compression.
Keep in mind, that this de-duplication happens automatically behind the scene, and you don't need to do anything for it.

Your decision could pertain to the question "Does Category belong in the Fact table, or in a Product Dimension table?". Typically, Category is an attribute that belongs in the Products hierarchy. So, usually you'd have a choice of keeping the field in the Products table or moving it to the Fact table. And the answer to this question is "It depends".

From the memory consumption perspective, a Category field stored in a Products Dimensional table will require less memory, because the Index table for Products is much shorted than the Index table for a Facts table. However, performance of specific objects and measures could differ in this case. Sometimes charts work faster when their Dimensions are stored in the Fact, and sometimes they don't. These issues need to be tested and verified in each particular data set.

Cheers,

Oleg

Re: Structure of Symbol Tables using AutoNumber on Key fields

Qlik_William — Wed, 25 Jun 2025 14:01:27 GMT

Thanks for your comment. Yes this is what I thought would happen. The "Category" was a bad example, I just wanted an example of a dimension that does not belong in a dimension table.

In this case like I think was mentioned in this thread, the dimension should be in the fact table, there is no need to have a dimension table if there are no attributes for it. Also calculation wise, it should be better since you have it in the same table.

I can also add that when you are using AutoNumber in the load statements for different key fields like

AutoNumber(Customer) as Customer,

AutoNumber(Product) as Product

In this case you will not get sequential integers in the individual fields, and thereby you would still get symbol tables for these fields is my understanding. Which can be good to keep in mind. You can solve this by using the second parameter of the AutoNumber function like

AutoNumber(Product, ‘Product’) as Product
AutoNumber(Customer, ‘Customer’) as Customer

However from my testing it seems that this is taken cared of automatically using the AutoNumber fieldlist like @rwunderlich suggested

AutoNumber Product, Customer;