Creating dimension tables vs filtering based on columns within the fact table

[Anon1234]

I'm new to BI and recently ran into an issue creating a simple line graph where my date period on the x axis was jumbled up. The solution was to put the date periods into a separate dimension table and link to the remaining data table and sure enough, this fixed the issue. The solution poster advised that if I had used star schema I wouldn't have run into this problem.

So next project, I am trying to utilise the star schema. I have a fact table which contains lots of financial data relating to companies that we insure (one company per row). There are a number of different ways I want to slice/group the data and so I set about creating three dimension tables - one for date (DimRenewalDates), one for Business/Contract data (DimBusinessType) and one for Renewal Info (DimRenewalInfo):

These are rough logical groupings, of fields I want to filter on however, with the exception of the RenewalDates table, they fields on each row of data are not related in the way that normally a row of data is considered a record.  The columns in theese tables contain unique values but the columns are of varying length meaning there are numerous null values in some columns. This is preventing me from linking to the fact table becuase it counts >1 null as many so that rules out the 1 to * relationship that I need. So I think I've made a categorical error in my understanding of the purpose of a dimension table.

In a traditional data analysis I would have kept these fields in the fact table and just filtered/grouped on the fields I want however, as I found out in the linked post above, this can cause problems with even the simplest analsyis in Power BI. So my only other thought is to have each field I want to filter/group on in its own dimension table but this would mean 8 dimension tables, all with a single field and this is not how I've interpreted dim tables should be set up.

Therefore, I'm looking for clarity in my understading and how best to proceeed here.

a) Is the current grouping ok and therefore, is there a way to overcome the null values issue preventing 1 to *

b) Should i restructure into dim tables of a single column on the fields I want to group/filter by

c) Should I just have everything in one table except for the dates?

d) Something else.

[Anonymous]

Hi @Anon1234 ,

(a)One approach is to ensure that each dimension table has a unique key for each row. You can replace null values with a placeholder value (e.g., "Unknown") to maintain the integrity of the relationships.

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

(b)Single-Column Dimension Tables: While technically possible, this is not recommended as it can lead to a fragmented and inefficient model. Dimension tables should ideally contain multiple related attributes that describe the entities in your fact table.

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

(c)Keeping everything in one table (except for dates) can simplify the model but may lead to performance issues and difficulties in managing and updating the data. The star schema is designed to optimize performance and usability by separating descriptive attributes into dimension tables.

Build Your First Star Schema Model in Action: Power BI Modeling Basics - RADACAD

(d)Something else:

Flatten Dimension Tables; Ensure Unique Keys; 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[johnt75]

When you don't have a natural column to link tables you can use the COMBINEVALUES function to create one.

Create a new table using 

Dim Business Type =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[Business Type],
        'Table'[High Risk or Mid Corporate],
        'Table'[Low Risk or Trust],
        'Table'[MA Contract Style]
    ),
    "Business Type Link",
        COMBINEVALUES (
            "|",
            'Table'[Business Type],
            'Table'[High Risk or Mid Corporate],
            'Table'[Low Risk or Trust],
            'Table'[MA Contract Style]
        )
)

Then in your fact table create a new column like

Business Type Link =
COMBINEVALUES (
    "|",
    'Table'[Business Type],
    'Table'[High Risk or Mid Corporate],
    'Table'[Low Risk or Trust],
    'Table'[MA Contract Style]
)

You should then be able to create a one-to-many relationship using this column, and you can use the same process for the other dimension table.