Model Data and filter

[Camstr]

Hello,

My data model is the opposite (i think) of the classic star schema.

I have a global database (via dataflows) with various tables as per the attachment.

In the centre is a dimension table surrounded by fact tables, all linked by 'customer ID'.

For performance reasons I only want to work with one country in my model (i.e UK)

I thought that if I filter the 'country' column in the central table (using power query) then it would also filter the fact tables accordingly, it did not.  Note that none of the fact tables contain a country column...

Question 1: based on my model structure is there a way to filter the connected fact tables by 'Country'?

Question 2: could improvements be made to my model? e.g cardinality, filter direction?

Question 3: this is a huge data base, should i be connected to data in import (i am now) or direct query? I don't recall getting the option...?

Thank you

[Camstr]

Thanks @AmiraBedh 

[AmiraBedh]

Welcome 🙂

[AmiraBedh · Accepted solution]

I will answer question by question.

Q1 :

When you filter a dimension table in Power BI, if there’s no direct relationship and Filter Propagation is not set up, it does NOT automatically propagate to the related fact tables.
As your facts tables don’t have a column ‘country’, the filter on the dimension table wouldn’t directly affect it.To address this:

Establish links between the dimension table (the 'country’ column is in here) and each of the fact tables using ‘customer ID’. This association is required in the propagation of filter.

The filter direction in the relationships can be set from model view of Power BI. Make sure that the direction of flow is set – from dimension table to fact tables. In this manner, when you filter ‘country’ in the dimension table it will affect on the associated records with fact tables.

If the approach above does not work, then you can make calculated tables by using DAX that will be filtered versions of your fact tables based on country concatenated in dimension table.

Q2 :

Make sure the relationships have appropriate cardinality.Most often, it should be one-to-many (1: ), where the 'one' side is described by a dimension table and the other ‘many’ side represents a fact table.

As noted before, filter direction is significant. It should usually be set to single directional from dimension to fact tables unless there is a need for bi-directional filtering specifically.

Remove tables, if any that are unnecessary for your analysis to reduce the model complexity and promote performance.

If possible, implementing hierarchies to your dimension tables can benefit users and even enhance performance.

Q3:
It is typically quicker in terms of the interactions within a report because it holds data stored internally. Nevertheless, it is not always appropriate for terabyte-sized datasets because it relies on your Power BI environment.

This mode interrogates the data source dynamically. It is effective for large data sets but may slow down the performance of reports.

A method that involves some tables that are imported and others accessed through Direct Query. This might be a reasonable compromise based on your individual preferences.

This can also be influenced by frequency of updating your data. Import mode is associated with scheduled refreshes, while Direct Query provides actual data at the time but there may be some performance deficit.

You can also choose the best approach based on nature and size of database. The use of import mode for performance reasons may require a Direct Query or Composite approach with huge data sets.