Relation between tables

Marek Derlukiewicz · 2025-02-14T15:02:25+00:00

There was an error rendering this rich post.

Hi,

I'm starting to learn Birst so please be understanding :)

I'm trying to make relationship between tables MWQIRS and MPDIID through field INSI and it doesn't work. Scripts are just to narrow results to specific CONO and FACI.

What possibly am I doing wrong?

Thanks in advance for the tips.

Marek

Find more posts tagged with

Accepted answers

All comments

Diane McElhiney

Relationships of hierarchies are through a measure table. Based on the screenshots, I'm assuming you are showing 2 separate Scripted Sources Grain Configuration in which the Scripted Sources each output to populate 2 hierarchies. Therefore, MWQIRSscripted should only be grained at the MWQIRSscripted. The same process for MPDIIDscripted. These hierarchies can then be associated with measuring data through another Scripted Source. If no measure exists from the source, a measure can be created with a fact-less measure table.

My statement is based on what I'm seeing in the screenshots, and I do not comprehend your business requirements.

Being a partner, you have access to Infor U and it is advisable to take the Foundation Admin course for Birst to gain knowledge of creating efficient, performant dimensional models within the platform.

Enrico Menotti

@Diane McElhiney What do you mean by "fact-less measure table"? I feel I'm missing this tool in my toolbox.

Enrico

Diane McElhiney

It is a dimensional modeling design method. Definition from the Kimball DW/BI methodology.

For example, a Scripted Source would output the 2 hierarchy-level keys and a numeric column set to 1 (I usually name the output column exists). The result is a measure table relating the hierarchies. As I stated above, without a full understanding of the poster's business requirements, these types of postings need a deeper-dive to provide the appropriate direction for a correct and performant dimensional model.

Enrico Menotti

Thank you Diane, I have read the Definition by Kimball & co. and the concept is clear to me. So it is nothing different than a normal Fact table for Birst; what is peculiar is the meaning it has and the way we use it.

Marek Derlukiewicz

Hi,

What do you mean by "The same process for MPDIIDscripted"?

There is no measure in both tables because it's not needed in the report (just attributes present); how does this fact-less measure table should look like and how to connect id to MWQIRS probably?

MWQIRS takes TX40 from table MPIID via common INSI.

It's very simple setup which I cannot implement :(

The other table I will take care of later when we solve this issue.

Sorry for the local language, it's not easy now to quickly change it for this purpose.

I took foundation courses but this case looks different than in mentioned courses.

Marek

Enrico Menotti

Marek,

Please mention:

What ERP system data are from;
What the meaning of tables MWQIRS and MPDIID is;
What the primary key columns in tables MWQIRS and MPDIID are (in the original ERP system).

Based on these info, there may be a few different approaches feasible.

What Diane is saying is that Dimensions do not relate with each other if not through a Fact table. This is due to the fact that each Dimension only contains its own keys. More precisely, Birst builds up a Surrogate Key for each Dimension, which is an integer, to make joins more performant, and each Dimension only contains its own Surrogate Key, so doesn't know anything about other Dimensions. When you build up a Fact table, the Surrogate Keys of all Dimensions in the grain are put in the Fact table, which permits Birst to correlate the different Dimensions with each other. You can see this in Space Admin → View Published Data.

Other ways are: if the tables have the same primary keys (in the original ERP system), you should probably push them into one single Dimension (this doesn't need a join in prepare…); if not, an option would be to create an Inherited Source from one of the two Dimensions and create a Complex Join, but that's not the cleanest approach in general (it's more complicated and slower).

Enrico