Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 117 of 212

105 C H A P T E R 6 | More analytics When you use a PivotTable to query the model, you see how the dimension labels in the rows become the filter context to the value from the fact table, which appears on the same row, as shown in Figure 6-8. In this example, each row is a product line from the Product table. To derive the aggregate value in the Sales Count column in each row, the Analysis Services engine filters the table for the current product line value and computes the count aggregate for sales having that value. Because of the one- directional filter in the relationship, each entry for Sales Count is not only an aggregate value but also a filtered value based on the current row's dimension value. If no relationship existed between the two tables, the Sales Count value would display the total aggregated value of 60,398 in each row because no filter would be applicable. Figure 6-8: Viewing the effect of a one-directional filter between Product and Internet Sales. Although you can create measures in any table in a tabular model, the behavior you see in a PivotTable might not produce the results you want if you add a measure to a dimension table. Let's say that you add a distinct count of products to the Product table and then add Calendar Year to your query. In this case, a one-directional relationship exists between Date and Internet Sales, which can be combined with the one-directional relationship between Product and Internet Sales to compute Sales Count by year and by product line, as shown in Figure 6-9. However, because the relationship between Product and Internet Sales is one-directional from Product to Internet Sales, terminating at Internet Sales, there is no relationship chain that goes from Date to Internet Sales to Product that provides the filter context necessary to compute the distinct count measure. Consequently, the distinct count by product line, which is in the same table and thereby provides filter context, repeats across all years for each product line. Figure 6-9: Viewing the effect of a one-directional filter between Product and Internet Sales and Date and Internet Sales on measures in the Product table. You can override this behavior by changing the relationship between Product and Internet Sales to a bidirectional relationship. To do this, select Manage Relationships from the Table menu, double-click

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016