topic How much memory uses a null value in a table? in QlikView

How much memory uses a null value in a table?

jmvilaplanap — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

In my models, normally I create a Fact Table with several facts, concatenating individual fact tables (later is easy to use, using set analysis expressions to filter the data set)

Then finally I have a Fact Table with a lot of fields with null values for example:

DataSet	Dim1 - ID	Dim2 - ID	Dim3 - ID	Value1	Value2	Value3
1	1	-	-	10	-	-
1	2	-	-	12	-	-
2	-	1	-	-	34	-
2	-	2	-	-	33	-
3	-	-	1	-	-	21
3	-	-	2	-	-	53

From time to time, some datasets have shared dimensions but I still have a lot of nulls. For example, in a model I have a Fact Table with 8 distinct datasets, 1.5M rows and 60 Fields (between dimension keys and values).

There are better way to do this? Is this efficient?

Thanks

Re: How much memory uses a null value in a table?

Anonymous — Fri, 07 Jul 2017 10:24:57 GMT

I guess null() consumes no memory because "it isn't there"...

Re: How much memory uses a null value in a table?

jmvilaplanap — Fri, 07 Jul 2017 10:35:38 GMT

Hi Robin

I think nulls are as the air of a ball, we can't see it but it has a weight.

I made a test with this script:

Table:
LOAD
null() AS nothing
AutoGenerate(10);
STORE Table into 'Table.qvd' (qvd);

And the size of the QVD is 1,311 bytes with 10 rows and 2,216 bytes with 1,000 rows. But the point is if exist another way better than this one.

Thanks.

Re: How much memory uses a null value in a table?

Anonymous — Fri, 07 Jul 2017 10:42:28 GMT

I don't know exactly how it works, but I know, that the main factor for performance problems is the number of distinct values.

Perhaps the experts can leave a comment.

rwunderlich‌ troyansky‌ johnw‌

Re: How much memory uses a null value in a table?

Kushal_Chawda — Fri, 07 Jul 2017 10:55:28 GMT

I don't think that the nulls occupy more spaces. It's the more number of unique values in particular field occupies more memory. AFAIK, Null values does not have any impact on performance.

Re: How much memory uses a null value in a table?

marcus_sommer — Fri, 07 Jul 2017 10:55:35 GMT

I agree NULL itself don't consume memory but the structure of a table respectively the datamodel will of course have an impact.

Your used approach is quite common and worked usually very well and quite performant within the GUI. Nevertheless I think if you could join or map your tables together you would save some RAM because you would need lesser records and therefore the table-pointer would be smaller. With your example above it could be only 2 records instead of 6.

Here an example which is a bit similar to your question even if the NULL handling wasn't the focus there: Re: Data Model Question - Fat or Thin Fact table?

In reality it might be not so easy if your tables aren't 1:1 else 1:n or even n:m related and/or there are missing key-values between them. In such cases your needed transformations might be take longer and are a quite more complex. As far as your approach is fast enough and you are not running into a RAM bottleneck I wouldn't change it.

- Marcus

Re: How much memory uses a null value in a table?

johnw — Fri, 07 Jul 2017 15:31:39 GMT

I'm going on memory from a post by hic, but I was unable to track it down with a brief search.

Your QlikView tables are really storing index values into another table, which is the list of distinct values for that field. The more distinct values you have, the more bits this index requires. However, as I recall, null() is special, it being a possible "value" for every field, so it has its own designation that I believe is independent of the number of distinct values for the field. It's something small, like a couple of bits, though actual memory use depends on some other factors as well that I'm not remembering the details of, something like your table must be an even number of bytes wide or something, so the nulls might take zero bits, or 8 bits, or something like that. Not certain. In any case, my main takeaway was that I don't need to worry much about nulls in my data model. Yes, they take space, but it's not a lot of space. As you said, they're as the air of a ball - we can't see them, but they have weight - but like the air, very little weight.

There's a chance I've remembered wrong. I don't actually know what I'm talking about. Take what I say with a grain of salt.

Re: How much memory uses a null value in a table?

jmvilaplanap — Sun, 09 Jul 2017 11:41:00 GMT

Thanks all for you opinion, I understand that is not necesary to be worried about the null fields, only in the "diversity" of values inside a column. But then, why people do snowflakes schemas? I think you always could do a model as a star, isn't it?

Maybe this is another debate.

Re: How much memory uses a null value in a table?

Oleg_Troyansky — Wed, 12 Jul 2017 21:27:37 GMT

Hi,

sorry I'm late to this discussion... Yes, I can confirm that nulls should not worry you too much. I don't have exact benchmarks of the memory consumption, but it should not be extremely high.

To answer your last question about snowflakes - people usually build snowflakes for their convenience and ease of understanding their data, not to avoid null values. Star schema will always perform better and faster than a snowflake, so if performance is an issue, I'd personally recommend a star over a snowflake.

However, if the data size is manageable, then a snowflake might be easier to build, maintain, and understand, and that holds a lot of value.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: How much memory uses a null value in a table?

jmvilaplanap — Thu, 13 Jul 2017 08:27:50 GMT

Thanks troyansky

But, Why is easier to maintain a snowflake than a star schema? In my opinion star is easier. Concatenating fact tables you can do a star schema almost always.

When I was doing my very firsts Qlik data models I was trying to design a snowflake schemas (like a conventional relational database) but when I understood the Qlik philosophy I began to use a star schemas, they are (in my opinion) easier to design and maintain.

Regards

Re: How much memory uses a null value in a table?

Oleg_Troyansky — Thu, 13 Jul 2017 15:06:56 GMT

Hi Jose,

I think you might be using the term "snowflake" loosely... Snowflake, strictly speaking, requires a single Fact and a number of Dimensions, when some of the Dimensions are linked to other Dimensions and not to the Star.

For example: Fact table contains Sales data. Dimensions: Customer, Product, Supplier, SalesPerson.

In a Star model, all 4 dimensions should be linked to the Fact. In a Snowflake, Supplier might be linked to Products and SalesPerson might be linked to Customer. Both models are valid. If the original data is organized closer to the latter option, then you need to add some data modelling logic to bring it to the Star format. That's the extra effort that may be necessary or may be unnecessary - depending on your preference.

What you described as a "snowflake" sounds like an Entity-Relationship model that's coming straight from a transnational system. That's not what we usually call "snowflake".

These different models are described very well in my book QlikView Your Business - check it out!..

cheers,

Oleg Troyansky

Re: How much memory uses a null value in a table?

jmvilaplanap — Thu, 13 Jul 2017 15:18:55 GMT

Thanks Oleg

You're right I was using the term "snowflake" in a non correct way.

I was speaking about models with more than a Fact Table and linked tables in between.

By the way, this book looks good, have several topic I'm interested in.

Regards