SSAS Dimensions Work around

14-12-2018  0 Comment(s)

Use Member Properties

Sometimes in a dimension we will have attributes that are important to view as details of another attribute but by themselves these attributes are not all that important for slicing and dicing. The Adventure Works Employee dimension has many attributes configured as member properties (they’re the attributes with the grayed-out icons). These attributes contain data that the business has identified as necessary for viewing as a detail of a given employee but are not necessary for slicing and dicing sales metrics or for filtering reports. In this case, we can improve processing performance and save storage by setting the AttributeHierarchyEnabled property to False.

When we set AttributeHierarchyEnabled to False, we can disable building an attribute hierarchy for the attribute. This means the attribute alone can not be used to slice or filter data in the cube. It also means that indexes are not built for the attribute, saving processing time. This can be especially valuable for attributes that have a high level of uniqueness such as SSN, Login ID, or Emergency Contact Phone and that are only necessary to be viewed as details of another attribute.

Avoid String Data Types

In Analysis Services, string data is stored in special, separate files called string stores. String store files have various extensions depending on where the data is used (ie key, property, member-value, etc.). Strings have to be stored in a separate file because the records are dynamic in size. SSAS also requires at least 12 additional bytes of storage per string: 4 bytes to store the position, 4 bytes for the size, 2 bytes for the type, and 2 bytes for the string NULL terminator character (“String Store Structure”, Microsoft SQL Server 2008 Analysis Services Unleashed, p348-49). Because of these facts, storing strings in our SSAS databases is expensive and degrades performance. For every string added to the solution, there will be an impact to processing and query performance.

Comment Here


No Comments to Show