Flat tabel versus star schema

[Anonymous]

I read about the importance of creating a star schema in power-bi. I made one large report using flat tabel that has around 2,8 milion rows and contains out of 5 tabels.

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Then I decided to try making star schema model in power bi with those tabel however I realized that my where statment in the flat tabel is only about tabel. Then I upload this one tabel with where statment and it was obviously quite small. However when I uploaded three another three tabels without a where statment I reaized I have more then 10 M rows per tabel.

Can someone explain me what am I doing here wrong, because I feel like I am missing some principles.

[MartinPerson]

Hi,

I'm making some assumptions that I beleive meets your needs. You started with a query 
SELECT

    *
FROM Fact F
    JOIN Dim1 D1 ON F.d1key = D1.d1key
    JOIN Dim2 D2 ON F.d2key = D2.d2key
    JOIN Dim3 D3 ON F.d3key = D3.d3key
    JOIN Dim4 D4 ON F.d4key = D4.d4key
WHERE 'Some condition on say D2'

Now, to create a proper Star Model from this set AND keep all of your DIM tables small and relevant to the scope of the situation, just use the following:

SELECT
    F.*

FROM Fact F
    JOIN Dim2 D2 ON F.d2key = D2.d2key
WHERE 'Some condition on say D2'

SELECT DISTINCT
    D1.*

FROM Fact F
    JOIN Dim1 D1 ON F.d1key = D1.d1key
    JOIN Dim2 D2 ON F.d2key = D2.d2key
WHERE 'Some condition on say D2'

SELECT DISTINCT

    D2.*

FROM Fact F
    JOIN Dim2 D2 ON F.d2key = D2.d2key
WHERE 'Some condition on say D2'

SELECT DISTINCT

    D3.*

FROM Fact F
    JOIN Dim2 D2 ON F.d2key = D2.d2key
    JOIN Dim3 D3 ON F.d3key = D3.d3key
WHERE 'Some condition on say D2'

SELECT DISTINCT

    D4.*

FROM Fact F
    JOIN Dim2 D2 ON F.d2key = D2.d2key
WHERE 'Some condition on say D2'

Basically, the fact table keeps it all relevant, and you minimize joins to other tables for only what is needed in the where statement.

Instead of D1.*, maybe you only need 3 columns from D1 table. So replace that with the column names.

Maybe in order to get to D4, you have to do a join on D3 to get there. In this case, you'd have to keep D3 in the joins even though its not used in either the SELECT or WHERE statement. Also, if this is the case the original query looks different than the example I provided. 

Perhaps your WHERE statement has conditions on multiple tables. Then you need to maintain the joins in each dim table query to maintain the WHERE statement on those tables. 

In all cases it is extremely imporant to remember to include your relationship columns. If your JOIN is on 2 columns, then in both tables, you need to mesh them together into a single column somehow. I generally default to Col1|Col2. There are many ways to go about doing that, I recommned looking them up and using the method that works for you.

If this resolves your problem please let me know.

[v-yetao1-msft]

Hi @Anonymous 

Your needs are not very clear .

If you want to use Star schema to manage relationships , what are your Dimension tables ? What about your Fact tables ? Could you provide some detailed data or sample to help us better deal with the problem ?

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.

[selimovd]

Hey @Anonymous ,

I think you have to explain a little better what you are doing.

I guess tabel means table. Where do you have a where statement, where do you upload the tables to? Please explain better, with screenshots, data model, etc. that we can help you.

Best regards

Denis