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
