Best data modeling approach

[PBI-Enthusiast]

Hello everyone

As a starting point I've got 1 single flat table with several information about services and their attributes:

All the columns like Service Time, Support Time etc. are related / part of Service B (there will be many more columns which are part of Service B in reality).

The requirement I must achieve is a matrix which:

• Has a hierarchy with Service A and Service B (as Rows)
• Shows the different attributes from Service B (as Values)
• Groups the different attributes with an additional parent "Group" (as Columns):

I would like to achieve the goal under the following conditions:

• Using the best data modelling approach
• The data model should allow to add further fact tables (which are related to Service B)
• Using general best practices
• The slicers of Service A and Service B should filter each other (that only related items are displayed)

It looks like an easy requirement, but I've tried different methods and every of them has benefits and disadvantages. Additionally, it's difficult for me to decide, if Service B should be handled like a fact table or a Dimension table, because there are no numeric "values" available.

If I leave out the requirement regarding the "Grouping" for now, these are the options I've validated so far:

Option 1)

Using the original flat table as a single table in Power BI.

Advantages:

• Dependency of slicers are given

Disadvantages:

• Not best practice (not at all)
• Data model is not flexible for further requirements / additional fact tables
• https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-sin...

Option 2)

Defining "Service A" and "Service B" (incl. all attribute) as Dimension tables. Because of a many-to-many-relationship, I need a bridge / mapping table between these two:

Advantages:

• Best practices implemented because of dimensional modelling and because there's no many-to-many-relationship
• Dimension table "Service B" can be used and related with additional fact tables

Disadvantages:

• Slicer dependencies are not given "by default", but can be achieved with DAX and filters: https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/

Option 3)

Defining "Service A" as a Dimension table and "Service B" as a Dimension table (but only with ID, Name and Description) and put the other columns like Service Time into a separate "Fact" table called "Service B Facts". Like in Option 2, I need a bridge / mapping table.

Advantages:

• Proper / separated Fact table (which can be unpivoted later for achieving the goal of grouping the columns in the matrix)

Disadvantages:

• I would have a 1:1 bi-directional relation between "Service B" and "Service B Facts" what's not best practice. Such combinations are candidates for merging / combining them into a single table (like in option 2). A relation between the Fact table and the Mapping table won't solve this issue either, because of the direction (a fact table should not filter dimension, only the other way around) and missing relations will end up in wrong data consistency.
• Slicer dependencies are not given "by default" (but can be achieved with DAX and filters)

Now here's the big challenge for me:

• In my opinion, option 2 seems to be the best solution (until I'll try to achieve the goal with grouping the columns in the matrix, see below) – or am I missing something here and there's even a better solution?
• When using option 2 - how can I now solve the challenge regarding the grouping of columns? The only way seems to be unpivoting the table of "Service B", which has been answered already in my previous post here from @Greg_Deckler : https://community.powerbi.com/t5/Desktop/Grouping-columns-in-Matrix/m-p/2805995. But since "Service B" is a Dimension table, I will generate multiple rows for each Key and end up with a many-to-many relationship between "Service Mapping" and "Service B" and cannot use "Service B" as a Dimension table for other / further fact tables properly with a 1-to-many relationship anymore:

Which is the proper way to reach all my goals?

Thank you in advance for any hints.

Best regards,
PBI-Enthusiast

[v-yueyunzh-msft · Accepted solution]

Hi , @PBI-Enthusiast 

According to your description, you want to get this table . Rught?

For your needs, your Service A belongs to one end table, and the rest belongs to the fact table. I don't recommend that you use many-to-many relationships to maintain your inter-table relationships.

I use the following table relationship:

I create the 'Service A' table in Power Query Editor:

let
    Source = Table.Distinct(Table.SelectColumns(#"Fact Table" ,{ "Key A","Service A","Service A Description" }))
in
    Source

And we need to unpivot the fact table :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHPCsIwDMZfZfQ8IUmzWr2tTyB4HDv476qgB6dPbxPr5sApQj8oX1Lo72ubpjGIaEqzrgHEN6pYIFnZDs/tqyoW4JYAxawgiP6hr+NaHc6X07FAbdtyMoIrlyIoeicSoldiHzBu3/hyKHzjz/0i8eUpN9FwY/SjB7zaMPDtL76rOPHF76L/foj7HyKilCC+VWUewkREtiFYaxNffKfKOQRmTnzxvSrjENoH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key A" = _t, #"Service A" = _t, #"Service A Description" = _t, #"Key B" = _t, #"Service B" = _t, #"Service B Description" = _t, #"Service Time" = _t, #"Support Time" = _t, Classification = _t, Responsible = _t, Criticality = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key A", Int64.Type}, {"Service A", type text}, {"Service A Description", type text}, {"Key B", Int64.Type}, {"Service B", type text}, {"Service B Description", type text}, {"Service Time", type text}, {"Support Time", type text}, {"Classification", type text}, {"Responsible", type text}, {"Criticality", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Key A", "Service A", "Service A Description", "Key B", "Service B", "Service B Description"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then we apply the data to Power Bi Desktop ,here are the steps in Power Bi Desktop you can refer to :

(1)We need to click "New Column" to  create a calculated column in 'Fact Table':

Group = IF([Attribute] in {"Service Time","Support Time"} , "Group A" , IF([Attribute] in {"Classification","Responsible"} , "Group B" , "Group C"))

(2)Then we need to add a hirearchy in this table:

We can click the three dots in the right of the 'Fact Table'[Group] and click 'Create hirearchy' and then add the [Attribute] to this hirearchy.

(3)Then we can create a measure:

Measure = IF( HASONEVALUE('Fact Table'[Service B]) , MAX('Fact Table'[Value]))

 (4)Then we put the field we need on the Matrix visual and then we can get this:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[PBI-Enthusiast]

Hello everyone

As a starting point I've got 1 single flat table with several information about services and their attributes:

All the columns like Service Time, Support Time etc. are related / part of Service B (there will be many more columns which are part of Service B in reality).

The requirement I must achieve is a matrix which:

• Has a hierarchy with Service A and Service B (as Rows)
• Shows the different attributes from Service B (as Values)
• Groups the different attributes with an additional parent "Group" (as Columns):

I would like to achieve the goal under the following conditions:

• Using the best data modelling approach
• The data model should allow to add further fact tables (which are related to Service B)
• Using general best practices
• The slicers of Service A and Service B should filter each other (that only related items are displayed)

It looks like an easy requirement, but I've tried different methods and every of them has benefits and disadvantages. Additionally, it's difficult for me to decide, if Service B should be handled like a fact table or a Dimension table, because there are no numeric "values" available.

If I leave out the requirement regarding the "Grouping" for now, these are the options I've validated so far:

Option 1)

Using the original flat table as a single table in Power BI.

Advantages:

• Dependency of slicers are given

Disadvantages:

• Not best practice (not at all)
• Data model is not flexible for further requirements / additional fact tables
• https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-sin...

Option 2)

Defining "Service A" and "Service B" (incl. all attribute) as Dimension tables. Because of a many-to-many-relationship, I need a bridge / mapping table between these two:

Advantages:

• Best practices implemented because of dimensional modelling and because there's no many-to-many-relationship
• Dimension table "Service B" can be used and related with additional fact tables

Disadvantages:

• Slicer dependencies are not given "by default", but can be achieved with DAX and filters: https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/

Option 3)

Defining "Service A" as a Dimension table and "Service B" as a Dimension table (but only with ID, Name and Description) and put the other columns like Service Time into a separate "Fact" table called "Service B Facts". Like in Option 2, I need a bridge / mapping table.

Advantages:

• Proper / separated Fact table (which can be unpivoted later for achieving the goal of grouping the columns in the matrix)

Disadvantages:

• I would have a 1:1 bi-directional relation between "Service B" and "Service B Facts" what's not best practice. Such combinations are candidates for merging / combining them into a single table (like in option 2). A relation between the Fact table and the Mapping table won't solve this issue either, because of the direction (a fact table should not filter dimension, only the other way around) and missing relations will end up in wrong data consistency.
• Slicer dependencies are not given "by default" (but can be achieved with DAX and filters)

Now here's the big challenge for me:

• In my opinion, option 2 seems to be the best solution (until I'll try to achieve the goal with grouping the columns in the matrix, see below) – or am I missing something here and there's even a better solution?
• When using option 2 - how can I now solve the challenge regarding the grouping of columns? The only way seems to be unpivoting the table of "Service B", which has been answered already in my previous post here from @Greg_Deckler : https://community.powerbi.com/t5/Desktop/Grouping-columns-in-Matrix/m-p/2805995. But since "Service B" is a Dimension table, I will generate multiple rows for each Key and end up with a many-to-many relationship between "Service Mapping" and "Service B" and cannot use "Service B" as a Dimension table for other / further fact tables properly with a 1-to-many relationship anymore:

Which is the proper way to reach all my goals?

Thank you in advance for any hints.

Best regards,
PBI-Enthusiast

[v-shex-msft]

Hi @PBI-Enthusiast,

AFAIK, current power bi does not support store multiple data type in one field. They will be converted to text and you can't directly aggregated on these values.

For this scenario, you can try to only add the group on the matrix column field. Then you can write multiple measure formulas named as the attributes and lookup raw table group-attribute mappings to calculate based on current group.(for the not match parts, you can add if statement to skip the calculations and matrix will auto hide the blank row/column)

Regards,

Xiaoxin Sheng

[PBI-Enthusiast]

Hi @v-shex-msft 

Thank you taking the time to analyse and answer my question. I've created my post twice by mistake and I've got the solution already by the other post:

https://community.powerbi.com/t5/Desktop/Best-data-modeling-approach/m-p/2931647

Thanks again and best regards
PBI-Enthusiast

[PBI-Enthusiast]

Hi @v-yueyunzh-msft 

Thank you very much for taking the time to analyse and answer my question. Your solution uses an unpivot, which I was trying to prevent. But when I "define" this unpivoted table as a fact table, I guess the further requirement (additional fact tables related to Dimension table "Service A") will be possible that way.

Many thanks again and best regards,
PBI-Enthusiast

[lbendlin]

With the special grouping requirements your only option is to create dedicated measures for each of the "columns" of your matrix. This will include more development and maintenance work.

The ideal solution would be to abandon/modify your requirements and use the standard functionality of the matrix visual.  "Don't fight the API".

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[PBI-Enthusiast]

Hi @lbendlin 

I agree, the grouping requirements is the reason, why I won't be able to have the cleanest data model at the end. Unfortunately these requirements are a must have according to our customer.

But I think with the solution from @v-yueyunzh-msft I'm good to go - thank you for your response!

Best regards

PBI-Enthusiast