In this MS Analytical store docs link , it says that the analytical store doesn't "include" a property if it's of a different type than it's base schema. Just out of curiosity , does it mean in the column store it stores null against that property for that document, or is that property completely ignored at that index?

Hello @ Nandu S Raj Thanks for the question and using MS Q&A forum. When a property in a document has a datatype that differs from what is specified in the base schema, that property is not included in the analytical store at all. This means that: The property is completely ignored for that document in the column store. There is no placeholder or null value stored for that property; it is simply omitted from the analytical store representation. This design choice helps maintain the integrity and performance of the analytical store, ensuring that only properties with compatible datatypes are available for analytical queries. Hope this helps. Do let us know if you have any further queries. If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.

How is the property stored when it has a different datatype that the base schema?

Accepted answer

Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-09T17:37:57.5966667+00:00

Hello @Nandu S Raj

Thanks for the question and using MS Q&A forum.

When a property in a document has a datatype that differs from what is specified in the base schema, that property is not included in the analytical store at all. This means that:

The property is completely ignored for that document in the column store.

There is no placeholder or null value stored for that property; it is simply omitted from the analytical store representation.

This design choice helps maintain the integrity and performance of the analytical store, ensuring that only properties with compatible datatypes are available for analytical queries.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Nandu S Raj 80 Reputation points

2024-12-10T17:57:06.2633333+00:00

Oh ok understood now.
However, won't that disrupt the dimensions which results in a read from the i+1th record's value for that particular property?

Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-10T20:09:40.2333333+00:00

@Nandu S Raj - You are correct in thinking that omitting a property could potentially impact the consistency of your dimensional data if you rely on that property for analysis. However, it's important to understand how the analytical store works in this case:

Dimensions and Incomplete Data: If a property is missing from a document in the analytical store (because of datatype mismatch), this won't cause a read from the i+1th record. Instead, the missing property will simply be excluded for that particular record.

Impact on Querying: When you query the analytical store, the absence of this property for some records won't automatically pull in values from subsequent records. It will simply mean that for those records where the property type mismatch occurs, the value for that property will not be available for analytical queries.

Handling Missing Properties: If you require this property for your analysis, you may need to handle missing or incomplete data in your queries (such as using IS NULL checks or filling in defaults where appropriate).

So, there should be no disruption where values from subsequent records are used in place of missing properties. The system just ensures that the properties stored in the analytical store remain consistent and compatible, avoiding any ambiguity in your analysis.

Hope this helps. Do let us know if you have any further queries.

Ganesh Gurram 2,305 Reputation points Microsoft Vendor

2024-12-11T16:52:39.0833333+00:00

Hi @Nandu S Raj

Following up to see if the above answer helped. If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.

Nandu S Raj 80 Reputation points

2024-12-15T13:19:39.0166667+00:00

Yes it helped , thanks @Ganesh Gurram !
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How is the property stored when it has a different datatype that the base schema?

0 additional answers

Your answer