In your role as the database administrator for X-Tex, a small but successful com
ID: 3772803 • Letter: I
Question
In your role as the database administrator for X-Tex, a small but successful company that designs and manufactures high tech fabrics, you have been asked to help optimize a query that will provide a dynamic view of your supply chain. You are to work with a developer who is tasked with integrating the data into a web portal to be used by the manufacturing operations team. The developer explains that he does not have much experience working with SQL Server 2012, but he seems keen to learn. Please provide the developer with a practical summary of design considerations that will help to ensure the query performs well.
Explanation / Answer
A) Optimize cube and measure group design-Keep cube space as small as possible by only including measure groups that are needed.Define cascading attribute relationships (for example Day > Month > Quarter > Year) and define user hierarchies of related attributes (called natural hierarchies) within each dimension as appropriate for your data.
B) Define effective aggregations -Avoid designing an excessive number of aggregations. Excessive aggregations reduce processing performance and may reduce query performance. If SQL Server Profiler traces indicate that most user queries that are not resolved from cache are resolved by partition reads rather than aggregation reads, consider using the Aggregation Manager sample application to design custom aggregations.
C)Use partitions -For optimum performance, partition data in a manner that matches common queries.Define partitions to enable Analysis Services to query less data to resolve a query when it cannot be resolved from the data cache or from aggregations. Also define the partitions to increase parallelism when resolving queries.