Dimension filter on id not working correctly

[zach_myt]

I have a dashboard with a dimension filter. The dimension filter table is a list of organizations and the products associated with those organizations. I have a widget displaying organization and product buy counts. When I filter on a product ID that is only associated with one organization it is affecting the counts of organizations that are not link to that product ID. 

If I filter for product ID on the fact table where I pull the buy counts from it works fine, but I want to use the dimension table so that it is filtered other places in the dashboard as well. I have verified this product is only linked to one organization. Why would a dimension table filter affect organizations that do not have that product ID linked to them?

[david-h]

Hi Zach,

Your question can not be answered without having more context, especially data model and specific dashboard widgets related to your filters.

It reads like an issue called "random query path". Are you familiar with this concept ? Is you data model a pure star schema with only one fact table in the center of the model or a data model with several fact tables and dimension tables ? If this is the latest, are you following the best practise rules ? Basically:

• link all dim to all facts,
• masking the join fields from the fact table
• always filter from dimensions and never use calculation from dimension (ex. : counting products from product id in the dim table instead of using one of the fact)

If you are using the JAQLine plugin ? It can translate a widget query into a visual diagram explaining the precise tables and joins involved in the query.

Let me know if this helps.

Best, David.

[zach_myt · Accepted solution]

This was due to the dimension table not containing all values from the fact table. Our dimension table only list active data but the fact table also includes data that was active once but is now inactive. For now, my solution was to use widget level filtering. I believe in the future we will look to add the inactive data with another column to identify if the data is active or not.

[SamAlbertCSM]

@david-h May I ask for more elaboration or what you mean with masking the join fields from the fact table?

[david-h]

Hi Sam,

In a multi-fact table data model, there are a few guidelines to follow to be sure the query generation will be as expected. Main ones are :

1) to link all fact tables to all dimension tables

2) hide (or make invisible) in the fact tables the key that is used in the join to the dimension => this is to enforce the use of dimension tables when filtering, and not the fact table. If you use the fact table to filter, then the risk is to generate many to many relationship as the user does not have direct control over the query generation process. In any case, if you keep both keys visible in the date model, the user will have both field available when designing widgets, which will lead to confusion.

One of the limitation of this principle is when you want to count on an id. For instance if you want to count products or clients, you want to do "unique count of productId". But you have to do that from the fact table, and not the dim table. In this case, you would duplicate the productID field from the relevant fact tables, and likely renamed it with some meanings (ex. : OrderedProductId, InvoicedProductId, ...), explicating from which fact table the Id comes from.

Let me know if you want more details. Happy to jump in a call if you want.

Best, David.

[SamAlbertCSM]

Thank you greatly David! Very helpful and timely.