cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Modeling relation fact table / dimension table with missing keys

I want to improve an existing model that produces some errors during data preparation.

Here's the scenario:

 

I have a "sales line" table that includes a "product number" column and the user should be able to filter by "product category".

But not every "product" has a "product category" (roughly 95% have one). Each "product" has at most one "product category".

 

The solution right now:

There is a table "ProductCategory" containing every product that has a category (roughly 10,000 lines of "product number" and "category" combinations with 20 different categories). This table is the base of the dimension, i.e. dragged onto the slicer control.

 

And there is a relationship SalesLine.ProductNumber --> ProductCategory.ProductNumber.

This results in errors during processing for those products that have no category i.e. are not in the ProductCategory table.

 

I considered 2 possible options:

 

Maybe these facts have an influence on the "best" option:

* There are several other tables (~10) that have the same problem

* I'm using an OLAP cube running on SQL server analysis services.

 

1: Change the ProductGroup to a table that only includes the 20 product groups + one new "no product group" line. Add a column to SalesLine that references the product group (and use "no product group" for those that previously had errors). Do this for all the other tables like SalesLine. Most likely I would create a mapping "product" => "product group" table once and use it in the affected fact tables preparation but not use it afterward.

 

2: Include all Products in the ProductGroup table and leave the rest as is.  

 

Although 2 seems simpler (no change in columns only adding new lines in one table), I assume 1 will lead to better design and performance.

 

Any suggestions?

 

Thanks

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Modeling relation fact table / dimension table with missing keys

HI @fraschfn ,

In my opinion, I'd like to suggest you to use method 2 to achieve your scenario, it can work as a bridge table and link with other tables.
BTW, if you do not add too many fields to this table, it doesn't affect the performance of your report.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Modeling relation fact table / dimension table with missing keys

HI @fraschfn ,

In my opinion, I'd like to suggest you to use method 2 to achieve your scenario, it can work as a bridge table and link with other tables.
BTW, if you do not add too many fields to this table, it doesn't affect the performance of your report.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors