Help on my star schema

[Matthieu_R]

Hello everyone,

Here is an example of my star schema i'm planning to do on Power Bi.

Sorry but the schema is in french but i will try to explain 🙂

I have 3 fact tables :

- FAIT_FACTURE (it is actually a list of invoices)

- FAIT_FACTURE_LIGNE (the details of each lines of each factures with product quantity etc....)

- FAIT_REGLEMENT (which are the payments and a facture can have multiple payments)

And then i have a multitude of dimensions tables.

I know that it is a bad idea to connect directly the fact_table between them. That's why i created a dimension table (DIM_FACTURE) that connect all 3 fact tables with the invoice ID.

At this point i think i'm on the right path.

The question i have is on the dim_calendar table on the right side of the schema. I have a date on FAIT_FACTURE and FAIT_REGLEMENT table. And i need to be able to analyse data by both dates.

Should i create only one dimension time table and connect it to both fact table (like it is on the schema) OR should i create two date tables, one for each fact table ?

I hope that i'm clear 😁

Thanks in advance for your answer

[DataInsights]

@Matthieu_R,

It's best to use one date table in your model. This will enable you to filter each fact table using a central date table. Your model is actually a snowflake schema due to dimension tables having relationships with other dimension tables. In a star schema, DIM_PRODUCT and DIM_PRODUCT_CATEGORIE would not exist in the model but would instead be merged into one table DIM_PRODUCTS_CATEGORIES. The same would apply to other dimension tables with this structure.

[Matthieu_R]

Thanks for your answer. Actually i made a small modification to my schema, here is the final version :

Does it look more like a star schema right now ?

Also i've created the schema in power bi desktop. And i can't create two active relationship between date table and the two fact table. Only one would be active and one inactive (dotted line).  This is a normal behaviour ? And this is where USERELATIONSHIP() comes in ?

As the FAIT_FACTURE table is the central fact table of my model, the relation that has to be active is the one between FAIT_FACTURE and the calendar table ? All other relations between fact table and time table are secondary ?

Sorry for all the questions and thanks again.

[DataInsights]

@Matthieu_R,

It's still a snowflake schema. In a star schema, dimension tables don't have relationships with other dimension tables. These three tables would be merged into one table at the product grain, with a column for each product attribute:

The date table needs only one relationship with each fact table.

[Matthieu_R]

@DataInsightsthanks.

When you say "The date table needs only one relationship with each fact table.", you mean one "active" relationship with each fact table ?

For the schema itself, and the snowflake part, should i merged everything ? A product can be in multiple categories so if i merge everything, i can have multiple line with the same product (but different category) in that new table. Is this a problem ?

Thanks again for your answers

[DataInsights]

@Matthieu_R,

Yes, one active relationship between the date table and each fact table.

Would you be able to provide sample data for the product and category tables, as well as the relevant fact table?

[Matthieu_R]

@DataInsights  thanks.

But PowerBi won't let me have two (or more) active relationships between the date table and two (or more) fact tables.

I can only have one active relationship.

After setting up the first active relationship between FAIT_FACTURE and the date table, if i try to set a second relationship between the other fact table (FAIT_REGLEMENT) and the date table, the relationship goes straight to inactive mode (dotted line) and if i try to make it active, PowerBi tell me that it is not possible because of the other relationship already in place (and already active).

What do you mean by "the relevant fact table" ?
I have a pbix sample to share but when i drag and drop the file here, it says "the file type .pbix is not supported".

We are on the Power Bi forum right ?

Thanks anyway for your help