Microsoft Analysis Services (MSAS) 2005 in SQL Server requires much work on a cube architect's part – work that was available for "free" in previous releases of the software. However, the new Analysis Services has considerably more flexibility and superior performance. To aggregate data correctly and to ensure the best possible performance, you must define appropriate attribute relationships and alter the values of certain attribute properties.
Purpose of attribute relationships
When you create a dimension based on a star-schema model (this implies that all attributes exist within a single dimension table), each attribute is related directly to the key attribute, but no other relationships exist between non-key attributes. If you use a snowflake schema and the dimension is based on two or more tables, then the key attribute from the main table is also related to the attribute used as the foreign key of the secondary table. In addition, the foreign key attribute is in turn related to the non-key attributes in the secondary table.
When you create hierarchies, Analysis Services does not define additional relationships between attributes; it is up to you – the cube architect – to advise MSAS how attributes should be related.
Analysis Services 2005 supports three types of hierarchies. Hierarchies that include attribute relationship definitions for each pair of attributes are called natural hierarchies. With previous versions of Analysis Services, you could only create natural hierarchies, even though you didn't explicitly define attribute relationships. With MSAS 2005 you can also create un-natural hierarchies (sometimes called reporting hierarchies) if attributes are not related. MSAS 2005 also creates an attribute hierarchy on each dimension column included as an attribute; attribute hierarchies consist of the all level and the attribute itself.
Attribute relationships advi...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
se Analysis Services how data should be rolled up. For example, within a store dimension, total sales for, say, each country could be divided into multiple states (or provinces), each state could have multiple cities, each city could include multiple postal codes and there could be multiple stores within each postal code. Similarly, a typical time dimension would roll up daily data to each month, monthly data to the corresponding quarter, semester and year, as shown on the screenshot below:
[IMAGE]In addition to defining rules for rolling up data, relationships advise MSAS to create data structures that make drilling from the top level of a hierarchy down to the lower levels considerably more efficient.
Unfortunately, you can't always define attribute relationships. For example, product size, weight and color aren't necessarily related to product category or product price. In prior versions of Analysis Services you'd have to include such columns as member properties; with MSAS 2005 these will be implemented as attributes and will only be related to the key attribute, i.e., product identifier or product name. You can also create a hierarchy on attributes that aren't related to each other – perhaps product color and size, if that's how your users need to browse the data. Do keep in mind, however, that you can see a sizable performance boost by defining relationships between those attributes that can be related.
Defining attribute relationships
It's good to know what attribute relationships are for, but you also need to know how to implement them. Within Business Intelligence Development Studio (BIDS), it's all drag-and-drop magic, but if you're not careful, you can easily define relationships incorrectly. Remember that the attribute with more members must be related to the attribute with fewer members. For example, the day attribute (more members) should be related to the month attribute (fewer members) if the daily data is to be rolled up to the corresponding month, as shown on the previous screenshot.
Note: The attribute with the highest granularity (Calendar Year) does not have any relationships.
It is important to know that attribute relationships can be specified as rigid or flexible; the default value for a relationship type property is flexible. Rigid relationships do not change over time; for instance, each day in a calendar date hierarchy will always belong to a specific month, so the relationship between day and month attributes is rigid.
On the other hand, customer address can change from time to time, so the relationship between a customer name and her address should be defined as flexible. The key point here is that flexible relationships force Analysis Services to drop and re-compute any existing aggregations during incremental dimension processing.
Rigid relationships do not require the re-computing of existing aggregations during incremental processing and thereby reduce total processing time. Hierarchies with rigid relationships can also be queried faster than those with flexible relationships. The flip side of the coin is that changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.
Another useful way to think of attribute relationships is through defining member properties. Member properties played an important role in earlier versions of Analysis Services. They're no longer essential with MSAS 2005 because each column of the dimension table can be exposed as an attribute and can be included in attribute, natural or reporting hierarchies. However, some literature refers to related attributes as member properties; this is because MDX allows you to retrieve the related attribute using the PROPERTIES keyword. For example, you could retrieve the state attribute using the following query if the state attribute is the member property of the city attribute:
WITH MEMBER measures.[state] AS
'Customer.customer.CurrentMember.Properties("state")'
SELECT [customer].customer.city.members
ON 1,
{measures.[state]} ON 0
FROM [sales]
The output will look similar to the following:
CityState
Phoenix, AZAZ
Omaha, NENE
Albuquerque, NMNM
Belen, NMNM
Roswell, NMNM
Amarillo_Inner, TXTX
Arlington, TXTX
Austin, TXTX
Brownsville, TXTX
Corpus Christi, TXTX
With RTM and Service Pack 1 of MSAS 2005, BIDS displays a green light when you define attribute relationships for each pair of attributes in a hierarchy. With Service Pack 2, this is no longer the case. Instead, BIDS warns you with an exclamation mark if you have duplicate relationships between the key and non-key attributes. This warning simply advises you that redundant relationships can exact performance overhead; normally you only need one "path" relating each non-key attribute to the key attribute, either directly or through another attribute.
Benefits of attribute relationships
MSAS 2005 can query natural hierarchies considerably faster than those without attribute relationships, even after you use typical performance-tuning steps, such as creating necessary aggregations and partitions. This is because Analysis Services can use a sophisticated caching mechanism to aggregate lower-level detail data to satisfy queries for higher level attributes. Case in point: If your vehicle hierarchy contains year, brand, make and model, then MSAS could aggregate data at model level in order to resolve queries for make, brand and year. If you don't define attribute relationships within this hierarchy, MSAS will have to aggregate data at vehicle_key level (presuming that is the key attribute) in order to find values for make, brand and year.
Note: The children of a particular dimension member, from a natural hierarchy, could be retrieved considerably faster than running a similar query against an unnatural hierarchy. This has to do with the way MSAS "materializes" the attribute relationships using indexing structures within its storage.
Attribute key columns
In addition to defining attribute relationships, you need to ensure that attribute key columns uniquely identify each attribute member. This is a relatively simple concept, but it can leave you scratching your head for hours when you build your first MSAS 2005 project. For example, how would you go about building a typical time dimension with MSAS 2000? You'd create a table containing a column for day of each month, the month name, quarter name and year. You'll quickly realize, however, that this approach will not work with MSAS 2005. The trouble is that values such as "Quarter 1" or "February" are not unique -- they appear in each year. Unless your warehouse contains data for a single year, you'll see data for months and quarters rolled up incorrectly. The problem isn't limited to a date dimension.
As another example, many products can have the same brand name but might need to roll up to a different product category or subcategory. You have two options for solving this problem:
- Create a unique identifier for each quarter and month value in your relational warehouse (month_id, quarter_id, etc.) and specify it as the single key column for your attribute.
- Specify multiple key columns for each attribute that isn't uniquely identified by its name. For instance, a combination of month and year can uniquely identify each month. Similarly, a combination of brand name and product subcategory could help you to group product names within appropriate brand name/subcategory hierarchy.
Another problem you might notice is that by default attributes are ordered by their name. So when you examine sales by month, you'll see months ordered alphabetically, April and August appearing on top. Of course in most cases you'll want to order months the way they appear in the calendar. You can solve that easily by modifying an attribute property called order by. You can order each attribute by its key, its name or by another related attribute. To order an attribute based on another attribute, you must first define relationships between the two attributes.
Microsoft Analysis Services 2005 builds on the strong foundation of previous releases of this software, plus it allows greater flexibility and better performance than earlier versions. Unfortunately, the price tag for the greater flexibility is the additional time investment required for cube architecture. This tip showed you a couple of gotchas you should keep in mind in order to build your cube properly and to ensure best possible performance.
ABOUT THE AUTHOR:
Baya Pavliashvili is a database consultant helping his customers develop highly available and scalable applications with SQL Server and Analysis Services. Throughout his career he has managed database administrator teams and databases of terabyte caliber. Baya's primary areas of expertise are performance tuning, replication and data warehousing. He can be reached at baya@bayasqlconsulting.com.
Copyright 2007 TechTarget
