How does SSAS populate the LEVEL_TYPE information in MDSCHEMA_LEVELS rowset

Question

I ran the following DMV query on SSAS.

SELECT
 [HIERARCHY_UNIQUE_NAME],
 [LEVEL_NAME],
 [LEVEL_NUMBER],
 [LEVEL_CARDINALITY],
 [LEVEL_TYPE]
from $system.mdschema_levels 
where [DIMENSION_UNIQUE_NAME] = '[DATE]'
 AND [CUBE_NAME] = 'Adventure Works'
 AND [LEVEL_NAME] <> '(All)'.

I get a lot of unexpected LEVEL_TYPES

I wanted to understand what do the LEVEL_TYPE like 4289, 4578, 4385, 4759 signify? Are they computed algorithmically or is they a documentation resource one can refer to?

MoazRub · Answer 1 · 2019-02-16T03:40:47.900

0

These Level_TYPE are determined the "Type" property of the dimension attribute that you set in dimension design window of your SSAS project. In case if you set the Type to regular you get 0 else , if you select from one of the types present you get its identifier. The interesting bit is if the HIERARCHY_UNIQUE_NAME has a attribute hierarchy, it returns the identifier for the attribute hierarchy, if HIERARCHY_UNIQUE_NAME has user hierarchy, then the identifier is returned for the LEVEL_NAME's base attribute. For example in the result below, take a look at the two rows that return 68 in LEVEL_TYPE, the first row is being reported as attribute hierarchy, the second as user hierarchy's level(Notice the Level_Number 3 and the difference between the HIERARCHY_UNIQUE_NAME and LEVEL_NAME)

Edit: Specific type details

4289: type Date, 4578: type QuaterOfYear, 4385: type HalfYearOfYear, 4759: type WeekOfYear

edited Feb 16 '19 at 03:40

answered Feb 10 '19 at 05:11

MoazRub

2,881
2
10
20

@rshetye does it help? – MoazRub Feb 13 '19 at 21:30
I am more curious about the level numbers like the details of levels like 4289, 4578, 4385, 4759 – rshetye Feb 16 '19 at 00:54
@rshetye specific details added – MoazRub Feb 16 '19 at 03:44
could you share the source of the mapping? The list was just a few samples, I am more interested behind the complete list. – rshetye Feb 19 '19 at 21:16
@rshetye the source of the mapping is my answer above. Kindly go through it again.let me Know if any issues. – MoazRub Feb 19 '19 at 22:08
would you know if the mapping is mentioned in some Microsoft documentation? – rshetye Feb 21 '19 at 18:45
@rshetye I have not come across the exact mapping defination document. Would you like to share what are you trying to achive – MoazRub Feb 21 '19 at 19:09
My team is developing a database engine which simulates SSAS behavior – rshetye Feb 21 '19 at 22:19

How does SSAS populate the LEVEL_TYPE information in MDSCHEMA_LEVELS rowset

1 Answers1