About Level Keys - Is assigning such Attributes to a specific Hierarchy really necessary?

I have opened an Enhancement Request:

https://mingle-portal.us2.prd3.inforcloudsuite.com/v2/CONCIERGE_PRD/?LogicalId=lid://infor.cxp.1&Tab=ERS&ERId=105393

Short description.

In Modeler Relate, Edit Column Properties, when it comes to defining associations between source columns and Level Keys what matters is only the Column Type ("Attribute"), and not the Hierarchy against which the column is targeted. As a consequence, if a source column is only used as the primary key for a Level (not as an additional attribute for any other level), in principle it would be sufficient to let Birst know that the column is an Attribute, without the need of specifying any Hierarchy.

However, the User Interface requires explicitly the Attribute to be targeted against some Hierarchy. This leads to an unclear situation when the column is used as a Level Key for multiple Levels (which happens sometimes - think of a space about Inventory, where there are properties that should be assigned to combinations of both Item and Warehouse, in a specific "Inventory" Hierarchy, and properties that should be assigned to Items only, in a separate "Item" Hierarchy - here, the Item is used both as Level Key for the "Item" Hierarchy and as part of the Level Key for the "Inventory" Hierarchy). Indeed, Birst associates the column to all Levels in the source grain where the column itself is part of the Level Key, although in the column definition the column itself is associated to one single Hierarchy only.

What I propose is that the structure of the Repository.xml be modified so to allow for definition of Attributes which are not explicitly targeted against any Hierarchy.

Please consider endorsing my ER.

Thank you for your attention, and my best wishes for the upcoming holidays.

Enrico

Find more posts tagged with

Comments

Joe McHale

Hi @e-menotti

It's an interesting discussion, thankyou for your post.

I would disagree however, not based on UI / ease of functionality, but on how the Birst data model works behind the scenes ...

Level Keys are essential when setting the Grains of sources (Relate >> Source Properties >> Grain Configuration). This is an important part of how we tell Birst to logically join tables, ahead of it building the star-schema model for us behind the scenes. As a side note, would recommend setting the "type" of any Level Key column to "both" (instead of just Attribute) - this enables count/count distinct of the rows and also generates a fact table if there are no other measures present in the table.

I would say is important to specify the Hierarchy a Level Key column belongs to, so I would disagree slightly with your statement:

"As a consequence, if a source column is only used as the primary key for a Level (not as an additional attribute for any other level), in principle it would be sufficient to let Birst know that the column is an Attribute, without the need of specifying any Hierarchy."

If we take the example of Item, Warehouse and Inventory ..

"think of a space about Inventory, where there are properties that should be assigned to combinations of both Item and Warehouse, in a specific "Inventory" Hierarchy, and properties that should be assigned to Items only, in a separate "Item" Hierarchy - here, the Item is used both as Level Key for the "Item" Hierarchy and as part of the Level Key for the "Inventory" Hierarchy)."

Say we have two tables, Warehouse and Item. Each have their respective Primary Key (Level Key): eg WarehouseKey and ItemKey

The Item table just has information about Items
- In Hierarchies, set the Level Key for Item Hierarchy as ItemKey
- In Relate >> Model Column properties
  - Set ItemKey to type "both" and Hierarchy "Item"
- In Relate >> Source Properties >> Grain Configuration
  - Tick the Grain for Item (if we hover above, it will show Level Key as ItemKey)
The Warehouse table contains many Items, and has the ItemKey column on it
- In Hierarchies, set the Level Key for Inventory Hierarchy as WarehouseKey
- In Relate >> Model Column properties
  - Set WarehouseKey to type "both" and Hierarchy "Inventory"
  - Set ItemKey to type "both" and Hierarchy "Item"
- In Relate >> Source Properties >> Grain Configuration
  - Tick the Grain for Inventory (if we hover above, it will show Level Key as WarehouseKey)
  - Tick the Grain for Item (if we hover above, it will show Level Key as ItemKey)

In the above example, by defining columns and explicitly targeting against a Hierarchy, we set the logical data model up for Birst to then build for us. If we defined WarehouseKey and ItemKey on the Warehouse table just as attributes, but did not explicitly targeted against any Hierarchy, then Birst would be unable to create a logical join between the two tables and build our star schema for reporting (allowing us to view Items by Warehouse). We would likely end up with two ItemKey columns, as attributes, belonging to separate Hierarchies with Birst unable to find further information from the Item Hierarchy when also using Warehouse data.

One final point i'd like to add to the discussion, is that we can easily create conformed dimensions by targeting columns to a newly created hierarchies (duplicate a column, target it and then set the grain), and then using level keys to our advantage to link tables - this has saved us lots of time in the past as we can just explicitly define a column against a Hierarchy, instead of having to use bridge tables etc

Thanks,

Joe

Enrico Menotti

Hi @jmchale ,

My most sincere thanks for your kind and detailed reply, I really appreciate that my point is taken into consideration for further discussion. I may be wrong in my comments, but that's not the most important thing for me now: what I care the most is to understand why Birst is behaving as it seems to do.

That said, there are many points in your reply which still find me perplexed.

As a preamble, I want to clarify here that my approach towards things is usually aligned with the so-called "Occam's Razor" principle, i.e. "Entia non sunt multiplicanda praeter necessitatem", or "Entities must not be multiplied beyond necessity". As such, let me try to express my doubts.

First, I would never set the type of any Level Key column to "both", unless having a Measure is really needed. I understand your point about counting records and generating a Fact table, but that's exactly what I'd avoid if not really needed: generating things that are not necessary, like Fact tables and Measure columns that are not required. If there is no need to count, we don't need a Measure there; if there are no other Measures in the table, not having a Fact is exactly what I want.

Second, my tests seem to show that a Level Key column may belong to multiple Hierarchies. Indeed, Birst looks for all Hierarchies which are in the Source Grain, reads their Level Keys, and for each Attribute in the Source which matches a Level Key the values in the Attribute column are used to fill the Level Key column in the corresponding Dimension table. This happens regardless of the Hierarchy mentioned in the Attribute definition, and if one single Attribute matches multiple Level Keys all the corresponding Dimension tables are filled (in their Level Key column) by the Attribute values. So no, it is not important to specify the Hierarchy a Level Key column belongs to. All of this, clearly, provided my interpretation of my tests is correct.

Let's now clarify about my Inventory example. I have actually three, not two, source tables: Warehouse (Master Data for Warehouses), Item (Master Data for Items) and Inventory (information about stock details; for example, a record will store stock Quantity and Amount for one specific Item in one specific Warehouse. So each record is identified by the couple (Warehouse, Item)). Coming to your comments, the Warehouse table does not contain Items actually; the Inventory table does. The Level Key for the Inventory Hierarchy is not simply WarehouseKey, it is instead given by the couple (WarehouseKey, ItemKey). The Inventory source table includes in its grain all three Hierarchies (Warehouse, needed for info about warehouses, Item, needed for info about items, and Inventory, needed to store any Attribute that is peculiar to a couple (Warehouse, Item), such as the Valuation Method). And both WarehouseKey and ItemKey columns in the Inventory table do belong to two Hierarchies each: WarehouseKey is the Level Key for Warehouse and the first half of the Level Key for Inventory, and ItemKey is the Level Key for Item and the second half of the Level Key for Inventory. So there's no reason to mark ItemKey as belonging to Item and WarehouseKey as belonging to Warehouse (or Inventory, whichever).

I have a comment also on your statement:

"If we defined WarehouseKey and ItemKey on the Warehouse table just as attributes, but did not explicitly targeted against any Hierarchy, then Birst would be unable to create a logical join between the two tables and build our star schema for reporting (allowing us to view Items by Warehouse). We would likely end up with two ItemKey columns, as attributes, belonging to separate Hierarchies with Birst unable to find further information from the Item Hierarchy when also using Warehouse data."

I have built a Space where I have the exact setup I have described: Item, Warehouse, and Inventory as Hierarchies, with ItemKey being the LevelKey for Item, WarehouseKey for Warehouse, and (WarehouseKey, ItemKey) for Inventory. The Item Dimension table is fed by Item Master, the Warehouse by Warehouse Master, and there's an "Inventory" Source table which holds Quantity, Valuation Method and Amount for each couple (Warehouse, Item). Quantity and Amount are Measures, Valuation Method is an Attribute. WarehouseKey and LevelKey are Attributes (only), both marked as belonging to the Inventory Hierarchy, just because something must be specified. With this setup, Birst builds up its star schema just fine, with one Fact table joined to three Dimension tables; both Item and Inventory (Dimensions) have an ItemKey column, but Item Master Data info and Warehouse Master Data info are properly displayed together in Visualizer (provided of course a Measure from the common Fact table is brought out as well, which anyway is necessary whenever we want to use the built-in Time Dimension - I forgot to mention that in my Inventory Source table I have also the Inventory Date as an Attribute, used as a third segment for the Level Key in the Inventory Hierarchy).

One last remark on your final point. In accordance to Occam's Razor, I'd never duplicate a Level Key column. This is only necessary if you want to explicitly set it as belonging to two different Hierarchies. However, as I have stated above, this is not necessary at all. I feel this kind of things is exactly what users are led to by the misunderstanding caused by forcing the user to assign an Attribute to a specific Hierarchy, when the Attribute is only used as a Level Key, potentially for multiple Hierarchies.

I look forward for further comments from you side - and thanks again for the opportunity to discuss. That really helps.

All the best,

Enrico

0911060553270289.pdf

Copyright © 2025 Infor. All rights reserved.