Star schema or single table in Power BI 

Thanks!!!

Import is always faster at query time. DirectQuery is useful when you have no time to refresh imported data, but you pay a price with slower queries.

FILTER is an iterator, CALCULATETABLE manipulates the filter context, the latter is faster. However, the code you wrote is different in results.

Why is it different from a reporting point of view?

The video is about imported data (VertiPaq). DirectQuery requires different optimizations, depending on the data source.

@@SQLBI Thanks. I must have missed that.

Star schema is better. Header-detail could be very bad for the performance because of the cardinality of the relationship involved. This free video course introduces the topic better: www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/

No differences in performance.

All true! But we heard the "performance" topic too much and we wanted some numbers...

The compression is there, but the optimal compression is a balance between normalization and denormalization. The star schema is usually the best tradeoff.

@@SQLBI Thank u! 😊

In general, yes. In this specific case, probably not much (the SQL Server database was already optimized). However, in every case the difference between DirectQuery and Import mode is orders of magnitude. If you use the INNER JOIN you have to make assumption about data quality which are not always possible.

The earlier, the better. If you can in SQL, do that in SQL. If you can only in Power Query, then use that.

It depends on the performance on MSSQL when you process the model. At query time there are no issue in an import model.

We don't understand the question...

@@SQLBI I was referring to this video "Creating a slicer that filters multiple columns in Power BI" ("ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UvvS376vYSw.html"). Here some degree of denormalization is applied..

Sorry, we still don't understand the question!

In this case we cannot share the data used in the demo (the volume would also make it hard to download).

It seems very strange. Usually, it is the opposite. Did you use particular measures in your reports?

​@@SQLBI The only table left out of fact table was calendar. I just put calendar also in the fact table and the performance of table visual has improved drastically. Before I was able to take only 3 months of one country and with the 4th month in selection, PBI service visual runs out of memory. Now i can take two countries with 5 months, so 3 times data than earlier. So flat table performance has improved whenever I remove one star schema table and bring it into fact table. Model size only increased by 1 MB as per Dax Studio Metrics with calendar in fact table but Table Visual performance improved 3 times which is my requirement to give to users to export data. I think in Table Visual I am adding so many fields/columns, in star schema it has to perform multiple joins over many rows to generate table visual and join seems memory expensive. I am using in that one multi currency formula which I took from your website Amount USD = VAR AggregatedAmountInLCY = ADDCOLUMNS ( SUMMARIZE ( MH03_Reported_Avg_Rate, MH03_Reported_Avg_Rate[Country] ), "@AmountInLCY", [Amount LCY], "@Rate", CALCULATE ( SELECTEDVALUE ( MH03_Reported_Avg_Rate[LCY_to_USD] ) ) ) RETURN Sumx(AggregatedAmountInLCY,DIVIDE([@AmountInLCY],[@Rate],0)) and three formulas like this one Vol Pcs = CALCULATE( sum(D01_TPM_Promo_Data[Amount Local CCY]), D01_TPM_Promo_Data[KPI_Name]="Total Volume (Units)", //D01_TPM_Promo_Data[Promo_No] IN VALUES(D01_TPM_Promo_Data[Promo_No]), ALL(D01_TPM_Promo_Data[Investment_Method]), ALL(D01_TPM_Promo_Data[Fund_Type]), ALL(D01_TPM_Promo_Data[Tactic_ID]), ALL(D01_TPM_Promo_Data[IO_Number]), ALL(D01_TPM_Promo_Data[Promo_Mechanic]), ALL(D01_TPM_Promo_Data[Tactic_Comments]), ALL(D01_TPM_Promo_Data[On_Off_Invoice]), ALL(D01_TPM_Promo_Data[Condition_Codes]), ALL(D01_TPM_Promo_Data[RA_SD_Number]), ALL(D01_TPM_Promo_Data[Deal_Rate_UOM]), ALL(D01_TPM_Promo_Data[KPI_Type]), ALL(D01_PnL) ) and one this formula Deal Rate % = VAR _AggregatedAmountbyFund = FILTER(ADDCOLUMNS ( SUMMARIZE ( D01_TPM_Promo_Data, D01_TPM_Promo_Data[Fund_Type] ), "@AmountbyFund", -[Amount Multi CCY]), D01_TPM_Promo_Data[Fund_Type]BLANK()) RETURN Sumx(_AggregatedAmountbyFund,DIVIDE([@AmountbyFund],IF(D01_TPM_Promo_Data[Fund_Type]="CentralCopack",[Trade GSV],[Net Trade GSV]),

It's possible you get better performance with the flat table because of autoexist, but it's quite strange to see that in the scenario you described. Two questions: 1) How many rows do you have in the Date table when you use the star schema? 2) At which level of granularity the calculation fails (day/month/year...)

You can find the link in the video description!

The differences are similar, but the absolute numbers are smaller and hard to compare. Moreover, a common misconception is that a single "fat" table is faster when there are many rows, which is not true (in Analysis Services and Power BI).