Data model / star schema

[ethan_lim]

Hi All,

Wanted to double check and get some opinion if such a model is appropriate / recommended / correct

I have table source from SQL server which acts as my data sources for my dimension and fact tables.

In Fact table 1, every record is unique. Each of these unique records has a child record.

The child records are in Fact table 2, every child record in Fact 2 is unique with its corresponding linkage foreign key back to Fact table 1.

Fact table 1 will contain Document A, B

Fact table 2 will contain sub line A1,A2,B1

e.g Document A -> sub line A1

      Document A -> sub line A2

      Document B -> sub line B1, etc

Document A can have 5 sub line items, Document B can have 3 sub line items, Document C can have 0 sub line items

Next level for Fact Table 3, each subline item can have multiple section items. Each subline can have 0 or more of the same sections, but each subline uniquely identifies to its corresponding sections through another foreign key linkage.

e.g 

Document A -> sub line A1   -> section X

Document A -> sub line A1   -> section Y

Document A -> sub line A1   -> section Z

Document A -> sub line A2    -> section A

Document A -> sub line A2    -> section B

Document B -> sub line B1     -> section X

Document B -> sub line B1     -> section Z

Document C -> sub line C1     -> null or N/A

I am building 2 reports, report 1 would only need data from FACT 1 and FACT 2 , while report 2 would need data from FACT 1,2,3.

Question -

1. In star schema, I know it is not recommended to join FACT to FACT relationships, but is it ok if the relationship is properly defined.

2. How should I have designed my FACT tables, i thought of joining FACT 1,2,3 into 1 repeated parent-child type FACT but when i thought about scenario below:

where subline from FACT 2 can have 1 to many relationship to another subline attribute in FACT 4 with its own foreign key link. Section in FACT 3 has no relationship with attribute in FACT4.

4 possible scenarios:

subline A -> many sections, 0 attributes

subline A -> 0 sections, many attributes

subline A -> many sections, many attributes

subline A -> 0 sections, 0 attributes

I'm thinking that having 1 parent-child FACT structure would not work in this case because there is no relationship between 3 and 4.

Appreciate advice on whether FACT to FACT (multiple FACT > 2) relationships are appropriate for such a data scenario?

Currently, I'm having this fact to fact relationship and am able to create my reports, but I'm not sure if this is appropriate to be a star schema then.

[dedelman_clng · Accepted solution]

Hi @ethan_lim  - 

Did you mistype the relationships between Fact 1 and Dim 1-4 ? Usually Dim-to-Fact is 1-to-Many (you have them all as Many-to-1)

Assuming that is a typo, in Power BI, there isn't really a physical distinction between Dimension and Fact tables - just how they are used once in the data model. Your schema looks like it should for fact data that is parent/child/child (e.g. Invoice --> Purchase Order --> Purchase Order Line).

Hope this helps

David

[PaulDBrown]

@ethan_lim 

based on your description, I would venture that the DIM tables in your depiction are redundant

[ethan_lim]

Hi Paul,

Thanks for replying,

Why would the dimensions be redundant in this case, wouldn't the dimensions would still be used for filtering in the model?

[PaulDBrown · Accepted solution]

@ethan_lim 

You can keep them if you wish, but since your tables are already linked by one-to-many relationships you don't really need them. You can use the actual columns in filters/slicers/measures as is. Filters and slicers contain distinct values by definition.

It would be different if you needed to "bridge" tables using a common field, or if the tables didn't allow one-to-many relationships, but that doesn't seem to be your case.

Having said that, if your data contains dates, creating a "Date Table" (as a Dimension Table) is considered a "best practice" and (general consensus) a "must have" (especially important if you are going to use Time Intelligence functions).

The "Date Table" should include consecutive dates (and whatever other columns: month, month name, year etc) and cover the whole range of dates included in your dataset. 

[dedelman_clng · Accepted solution]

Hi @ethan_lim  - 

Did you mistype the relationships between Fact 1 and Dim 1-4 ? Usually Dim-to-Fact is 1-to-Many (you have them all as Many-to-1)

Assuming that is a typo, in Power BI, there isn't really a physical distinction between Dimension and Fact tables - just how they are used once in the data model. Your schema looks like it should for fact data that is parent/child/child (e.g. Invoice --> Purchase Order --> Purchase Order Line).

Hope this helps

David

[ethan_lim]

Hi @dedelman_clng 

Thanks for replying,

Yeah my bad, it's a typo on my end. 

Thanks for your input, as I was thinking from standard star schema, snowflake, where usually we won't be connecting too many facts with each other especially on parent / child relationships and wanted to check that I wasn't doing something "wrong" or out of standards.

But agree, that PBI provides the flexibility if proper relationships can be defined.

Once again, thanks for your input.