Different number of results when adding an additional measure column?

Ryan Gartman · 2025-01-20T17:49:53+00:00

There was an error rendering this rich post.

For the sake of example, I made a table in Visualizer to simply list the line numbers in a sales order.

When I check the order in CSD, I see that there are 26 lines (27 including an inactive line).

In Birst, only line # 19 is listed.

However, if I add a Quantity Ordered column (or other Measure from Sales Order Line) to the table, it then displays all 27 lines.

Why doesn't it list all of the order lines unless a measure is added?

Find more posts tagged with

Accepted answers

Enrico Menotti

The last BQuery in your post is looking up Attributes from two different Dimensions. Dimensions only join through a Fact table. In theory there should be no result; yet, Birst tries to find a Fact table to do the join by itself. Probably here the Fact table is not the same in the two BQueries. Try to paste them in Query Admin, navigate, and compare the results.

All comments

Enrico Menotti

Ryan Gartman

https://community.developer.infor.com/discussion/comment/895#Comment_895

Okay, I generally understand. Thanks for the help.

Now that I ran it through Navigate Query, it looks like the fact that it had SASC.cono = 1 for a filter was the main reason for it doing unexpected joins. If I switch that to OEEL.cono = 1, then it doesn't have to jump across multiple tables to get results.

Enrico Menotti

https://community.developer.infor.com/discussion/comment/897#Comment_897

Exactly. If you are querying Attributes all from one single Dimension (including filters) then there are no joins involved.

If you add a Measure, that MAY uniquely define the joins. Usually it does, but in complex scenarios that's not always the case: you may have your Measure in various Fact tables with different grains, and at that point the selection is done by Birst based on Cardinality evaluations, which honestly I never investigated. Another complex situation is where you define Inherited Sources from Dimension tables (e.g., Role-Playing Dimensions) and join them to the rest of the model by Complex Joins. I did a Space in this way and, if I well remember, I have seen unexpected results there due to Birst selecting tables for joins in an uncontrolled way (until I figured out how to control it by properly setting up my model).