Fact tables should only contain columns that are needed for relationships to dimension tables and numeric columns that are referenced by DAX Measures. In some models, an additional column that isn't modeled in a dimension table and is needed for analysis, such as Sales Order Number, may also be included in a fact table.
Given their size and central role in the dataset, fact tables receive much greater analysis to deliver optimal performance and scalability.
In the following T-SQL query of the Reseller Sales source fact table, columns are computed that produce the same values as the ExtendedAmount, SalesAmount, and TotalProductCost columns:
Only the UnitPrice, OrderQuantity, DiscountAmount, and ProductStandardCost columns are needed for the import mode dataset since DAX Measures can be written to embed the necessary logic (for example, UnitPrice * OrderQuantity) for the ExtendedAmount, SalesAmount, and TotalProductCost columns. By not importing these columns to the Data Model, a significant amount of data storage is saved and query performance is not compromised. Columns with few unique values, such as OrderQuantity, can be highly compressed by import mode datasets and thus are lightweight to store and fast to scan to resolve report queries.
The $0.04 difference between the sum of the Sales Amount column and the Sales Amount Calc expression is caused by the DiscountAmount column being stored as a float (approximate) data type. In almost every scenario, a variance this small ($.04 out of $80.4 M) is acceptable to obtain the scalability benefit of not importing a fact table column.
If the SQL View for the fact table is exclusively utilized by this dataset, then the three columns can be removed there. If the SQL View cannot be modified, then the three columns can be removed via the M Query for the fact table, as shown in the following screenshot:
As shown in the previous screenshot, the Table.RemoveColumns() function excludes three columns from the source SQL View, as these columns only represent derived values from other columns that are included in the query. Therefore, for an import mode dataset, DAX Measures can be written to efficiently implement these simple calculations via the source columns, such as Unit Price and Order Quantity. However, for a DirectQuery dataset, these derived columns (for example, Total Product Cost) would not be removed due to the performance advantage of the SUM() SQL expressions referencing individual columns. The following chapter contains details on implementing these DAX measures and other measure expressions.