This solution only works if you slice the data using the common dimensions, because otherwise you will get incorrect results due to the granularity differences between both tables.I have a multiple star schema or a galaxy schema.You can use the star schemas to create reports that don’t need to include in a same visual, columns from different fact tables.
Avoid increase the size the data model having fact tables separated.So, you will get a snowflake when the header table acts as dimension, although you should avoid it, sometimes as in this specific scenario could be a good solution to representing the data. It’s a dimension when you need to slice the detail and it’s a fact table when you summarize values at the header granularity. In this solution, linking the header and the detail table breaks the rules of the star schema because both tables act as a dimension and a fact table at the same time. USERELATIONSHIP ( ‘Fact Detail', ‘Fact Header' )
Finally, to summarize or bring the columns from the header table, use the USERELATIONSHIP DAX formula:. Use a Many to one Cardinality and a “Both” cross filter direction:. Relate the header and detail table through an inactive relationship:. Relate them with every fact to get a star schema model for every fact. The reports just needed to be sliced using common dimensions.Ĭonsidering these points, apply the following solution:. You need to make few calculations over the header. The header detail table had a big number of column and flattening it into the detail table would create a huge table. You need to use columns from the different fact tables in a same visual table. You can use it in the following scenario: Solution 2: Relate the header and detail tableīe careful with this solution because you could get incorrect results due to the granularity differences between both tables. Categorical values repeated in the denormalized model. The model will be easier to use because it can be summed and sliced by any dimension. The values will get the correct granularity. You will get a perfect star schema that offers all its advantages. Solution 1: Flatten the header table into the detail table: With this solution you will get a denormalized model, where you locate the data from the header table to the detail table increasing the granularity. The detail table contains the product, the price, the quantity, and detailed information related to the individual order line. The order table has an order number, date, a customer, and much more useful information at the order level. In the header/detail models you must be careful to use the correct granularity in your calculations and try to maintain a good performance considering that they are not a perfect star schema.Īn example of header/detail models is when you have an order (header) and order details. Remember that the granularity represents the level of detail in each row of your fact table. Scenario 2: Multiple fact tables related to each other with different granularities Consolidate the fact tables: Join fact tables through a union (append in Power Query) will create a huge table that can take a long time to refresh, in addition to a lot of blank values in the unmatched columns. 
You can’t use the same dimension to filter the data of both tables.You will increase the size of your model which could impact the refresh time.
As there is no relationship between the fact tables you won’t be able to use values from them in the same visual because you will get the following error:. You can filter both fact tables using the shared dimensions. Star schemas are easy to read, understand and use. Sometimes this model is called constellation schema, where each fact table individually is a perfect star schema design because each one is related with a dimension. Solution: Relate every dimension with their respective fact tables. Scenario 1: Multiple fact tables thar are not related to each other
0 kommentar(er)
