Multivalued dimension modeling (IDT & Webi)

building semantic layer
1

I am having trouble getting a dimensional model using a bridge table to work in Webi. For the HR department I am working on a data model to report on the sickness absence rates. The complexity comes in when an employee works for two (or more) departments. I have created a simple conceptual data model to represent the situation. The hypothetical situation: John is a full-time employee and has a 50% appointment for department A and 50% for department B. John has two sick leaves adding up to a total of 30 days. I expect to see those 30 days for both departments, but the grant total should show 30 as well.

The fact_absence <> dim_department represents a many-to-many relationship. A department has multiple absence records and an absence record can be related to many departments. I have looked up this situation in “the data warehouse toolkit” by Kimball. This situation is referred to as a “multivalued dimension” - that sounds right - and it is suggested to introduce a bridge table. The following example is used in the book:

951×239 97.2 KB

However, either Webi or IDT does not handle it correctly, or I am doing something wrong :-). I’m using Webi version 4.1 Support Pack 6. Below I have described my steps to get to this report, including screenshots.

Any advice / ideas would be much appreciated!

I have translated the bridge table solution to my situation with my simple data model:

977×223 31.1 KB

and defined two contexts:

1050×255 23.1 KB

Finally I included all columns in the business layer and represented the database tables in a Webi report:

951×286 33.5 KB

Webi creates two queries, as expected. When I combine all information in one table, Webi seems to process the two queries correctly:

511×197 17.7 KB

The sickness leave of John is displayed for both departments, but in the sum of all departments, the sick leave is only counted once. So far so good.

Now when I exclude the Employee Name column from this report, suddenly Webi seems to be unable to combine the two queries:

479×189 18 KB

one_bridge_too_far.pdf (264.0 KB)

humarco :netherlands: (BOB member since 2017-01-28)

2

What happens if you use Employee ID and Department ID instead of the respective names?

Andreas :de: (BOB member since 2002-06-20)

3

Same behaviour, point is that the numbers are actually correct when I include employee name, it only fails when I take out the employee name column.

humarco :netherlands: (BOB member since 2017-01-28)

4

As far as I can see, it SHOULD work like you describe, but it just doesn’t. Could this be a bug?

Keesinho :netherlands: (BOB member since 2005-11-11)

5

Looks like you need to create a variable with a calculation context from your measure then?

Mak 1 :uk: (BOB member since 2005-01-06)

6

Can you explain what you mean by “a variable with a calculation context from your measure”?

humarco :netherlands: (BOB member since 2017-01-28)

7

He means using the ForEach context to add Employee Name back into the calculation.

8

Thanks for the suggestion - unfortunately the numbers haven’t changed after using a ForEach, see attachment.
Capture.PNG

humarco :netherlands: (BOB member since 2017-01-28)