One-to-one relationship to reduce size of dimensional table due to product variety

[OleksandrM]

The case: company sales products for photographers, and products are quite different by nature and they have common attributes (country of origin, weight of the box). But there are a lot of different attributes what are specific per product:

• Cameras: megapixels, wireless interfaces, zoom
• Printers: format, number of inks, interface
• Photo paper: type, surface, area
• Inks for printers: inks type, inks colour, ink in cartage in ml or litters

I also need to make respective calculations. E.g.:

• For cameras – I need to calculate total units or revenue sales, and I need to filter by attributes like zoom or interface
• For printers – I need to calculate total units or revenue sales, and I need to filter by format, number of colors
• For photo paper - I need to calculate total sold area in square meters, filtered by format and surface
• For inks – I need to calculate total sold inks in liters

And then, I need to calculate e.g. inks sales per sold printer, or paper sales per sold printer, or inks sales per sold paper – to understand inks consumption - so for creating measures I will need to use different atributes

Now I have a single master table (dimentional table), what has all attributes and therefore the table is giant. Actually, it has more than 165k lines and in the end a hundred columns.

My idea is to split master table, and have main master table with only common attributes, and have one-to-one connected other tables, specific to each product group. Item master has one to many connection with transactional table, and one to one with another four master table.

Is it a good idea?

Can it help on performance?

This is a link  to Excel file with sample tables - master table and table I want to connect via 1-to-1

[OleksandrM]

@Anonymousthank you for your reply.

In the Excel file, I just created a dummy table to show the concept, there is no duplicates in my main master table, it’s just a typo. 

I’m well familiar with data modeling, and I do use star schema in my Power BI data model. But the classical star schema is about a fact table in the center, and dimensional tables around with 1-to-many relationship, ideally, with filtering direction from the one side to the many side.

In my case, you propose to have a star schema on top of the exsisting star schema. In the most of training materials, documentations, I don’t see too many information about one to one relationship. I also see some ideas to split fact table into two, e.g. here, but almost nothing about dimensional tables.

My main questions are:

Is it a good idea? I don’t see such implementation this is why I have doubts.

Will it have performance implications? This is the most important question. I think it will improve performance… but I’m not really sure about it, and that’s the reason for this post.

What do you think?

[Anonymous]

Hi @OleksandrM 

The model you describe is somewhat similar to the star schema . A star schema is composed of one or more central fact tables, a set of dimension tables, and the joins that relate the dimension tables to the fact tables.

Star schemas are easy for end users and applications to understand and navigate. With a well-designed schema, users can quickly analyze large, multidimensional data sets.

More info about Star Schema Advantages can refer to the link :

http://gkmc.utah.edu/ebis_class/2003s/Oracle/DMB26/A73318/schemas.htm

But in your excel ,there is a problem that there is no unique value in your Item master table, so you cannot has one to many connection with transactional table and Item master table .

You can change the ItemNo in your Item master table and then create one to many relationship .Like this :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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