Attribute Relationships: Settings and Properties - Page 2January 22, 2009 Procedure: Define Attribute Relationships and Examine Attribute Relationship Property Settings in Analysis Services 2005In the practice procedures that follow, we will select and examine a representative dimension within the sample cube, and then focus upon the attribute relationship property settings that reference select dimension attributes. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will examine the attribute relationship property settings within our Analysis Services database, ANSYS065_Basic AS DB. In Dimensional Model Components: Dimensions Part I and II, and Dimensional Attributes: Introduction and Overview Parts I through V, respectively, we overviewed the properties underpinning Database and Cube dimensions, and then examined the properties supporting dimension attributes. In Attribute Member Keys – Pt I: Introduction and Simple Keys, and in Attribute Member Keys – Pt II: Composite Keys we focused upon those properties for a simple attribute key and a composite attribute key, respectively. Just as we did for the respective subject matter objects in those articles, we will examine the detailed settings for representative attribute relationships here. As I noted earlier, we can organize attribute hierarchies into levels within user hierarchies to provide navigation paths for users in a cube. A user hierarchy can represent a natural hierarchy, such as city, state, and country, (as we shall see in our practice session) or can simply represent a navigation path that fits a local business scenario, such as employee name, title, and department name. Moreover, as we also mentioned earlier, to the information consumer navigating a hierarchy, these two types of user hierarchies are identical. As a part of my discussion in Introduction to Attribute Relationships in MSSQL Server Analysis Services, I stated that, with a natural hierarchy, if we define attribute relationships between the attributes that make up the levels, Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute. Let’s get some hands-on exposure with defining attribute relationships for the attributes in a natural user hierarchy that exists within our basic UDM. Within our practice session we will work within the Customer Geography (natural) hierarchy of the Customer dimension. Define Attribute Relationships for Attributes in the Customer Geography Hierarchy We will begin our practice with attribute relationships within the Customer Geography hierarchy of the Customer dimension. 1. Within the Solution Explorer, right-click the Customer dimension (expand the Dimensions folder as necessary). 2. Click Open on the context menu that appears, as shown in Illustration 3.
The tabs of the Dimension Designer open. 3. Click the Dimension Structure tab, if we have not already arrived there by default. The attributes belonging to the Customer dimension appear as depicted in Illustration 4.
We note that twenty attributes appear within the Attributes pane. We will get some exposure to attribute relationships, by adding / examining representative relationships among the attributes we see here. We can also see, within the Hierarchies and Levels pane, four levels in the Customer Geography user hierarchy. This hierarchy currently exists simply as a drill down path for information consumers, and appears as shown in Illustration 5.
4. In the Attributes pane, expand the Geography attribute by clicking the “+” sign to its immediate left. We note that four member properties link the non-key attributes from the Geography table to the key attribute from the Geography table, as depicted in Illustration 6.
5. In the Attributes pane, expand the Full Name attribute. We see that the Geography attribute is related to the Full Name attribute. We also note that the Postal Code attribute is indirectly related to the Full Name attribute through the Geography attribute, because Postal Code is linked to the Geography attribute and the Geography attribute is linked to the Full Name attribute. These relationships are circled in Illustration 7.
6. Drag the Postal Code attribute from the Geography attribute to the <new attribute relationship> placeholder for the Full Name attribute, as shown in Illustration 8.
The Postal Code attribute is now directly related to the Full Name attribute. In the Properties window (which appears for the highlighted Postal Code attribute, by default in the right bottom corner of the design environment), we can observe that the RelationshipType property for this attribute is set to Flexible. This is appropriate because the relationship between a customer and a postal code may change over time. The RelationshipType property defines rules for the modification of the key value of the members of the related, dependent attribute (in our example, the Full Name attribute is the current attribute, whereas the Postal Code attribute is the related attribute). When we define an attribute relationship, we use the RelationshipType property to specify that the relationship is one of the following types:
If we define a relationship as Rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as Rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed. Specifying the appropriate relationships and relationship properties increases query and processing performance, as we noted in Introduction to Attribute Relationships in MSSQL Server Analysis Services. We noted that the RelationshipType property in our example is set to Flexible: The key of the related, dependent attribute, and therefore the entire member of the dependent attribute, can be changed anytime. In our example above, the Postal Code attribute is dependent upon the Full Name (Customer) attribute with a Flexible relationship, since the postal code can change anytime a customer moves. While we will not make modifications here, we also see that the Cardinality setting for the Postal Code attribute relationship is set to Many. Cardinality defines the nature of the relationship of the key of related attributes (and their members) when those members are used as member properties of the current attribute (and its members). The Cardinality setting can have one of two possible values:
Finally, we can see that the Visible setting for the Postal Code attribute relationship is set to True. The Visible setting specifies whether the related attribute is accessible, as a member property of the current member, to the information consumer. The Visible setting can have either of two possible values:
For purposes of our immediate example, we will leave the Visible property as its current setting of True. The Properties window for the Postal Code attribute relationship appears as depicted in Illustration 9.
7. In the Attributes pane, expand the Postal Code attribute. The City attribute is currently related to the Postal Code attribute through the Geography attribute, rather than being directly related. We will next directly relate the City attribute to the Postal Code attribute. 8. Drag the City attribute relationship from the Geography attribute to the <new attribute relationship> placeholder for the Postal Code attribute. The City attribute is now directly related to the Postal Code attribute. In the Properties window, we note, as we did for the Postal Code attribute earlier, that the RelationshipType property for this attribute is set to Flexible. This is appropriate because the relationship between a city and a postal code may change over time. 9. In the Attributes pane, expand City. The State-Province attribute is currently (indirectly) related to the City attribute through the Full Name and Geography attributes, hence we do not presently see the State-Province attribute under City. 10. Drag the State-Province Name attribute relationship from the Geography attribute to the <new attribute relationship> placeholder for the City attribute. The value of the RelationshipType property for the State-Province attribute relationship should be set to Rigid because the relationship between a city and a state does not change over time. 11. With the newly placed State Province Name attribute highlighted, change the value of the RelationshipType property for State-Province attribute relationship, from its default setting of “Flexible,” to “Rigid,” in the Properties window, as shown in Illustration 10.
12. In the Attributes pane, expand the State-Province attribute. 13. Drag the Country-Region attribute relationship from the Geography attribute to the <new attribute relationship> placeholder for the State-Province attribute. The value of the RelationshipType property of the Country Region attribute relationship should be set to Rigid because the relationship between a state-province and a country-region does not change over time. 14. With the newly placed Country Region attribute highlighted, change the value of the RelationshipType property for Country Region attribute relationship, from its default setting of “Flexible” to “Rigid” in the Properties window. Because we have, in the foregoing steps, moved all the attribute relationships from the Geography attribute to other attributes, instead of creating new attribute relationships for each of those attributes, there is no reason for retaining the now-empty Geography attribute. NOTE: As we discussed in Introduction to Attribute Relationships in MSSQL Server Analysis Services, we generally assist in aggregation design and improve query performance by defining the most direct relationships within our models. 15. In the Attributes pane, right-click the Geography attribute. 16. Select Delete from the context menu that appears, as depicted in Illustration 11.
This will conclude the first half of our work with attribute relationships. We will continue our practice with these important relationships within the next article of this series. NOTE: Please consider saving the project we have created to this point for use in subsequent related articles of this subseries, so as to avoid the need to repeat the preparation process we have undertaken initially, to provide a practice environment. 1. Select File -> Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for our activities within other articles of this subseries. 2. Click Yes when prompted, via the Visual Studio message box that appears next, to reprocess the affected cube objects. 3. Click Run on the Process Object(s) dialog box that appears next, as shown in Illustration 12.
Processing begins, and we see completion of the various steps via the Process Progress viewer. We see a “Process Succeeded” message in the Status bar at the bottom of the viewer, once processing is complete, as depicted in Illustration 13.
4. Click Close to dismiss the Process Progress viewer. 5. Click Close to dismiss the Process Object(s) dialog box. 6. Select File -> Exit to leave the design environment, when ready, and to close the Business Intelligence Development Studio. ConclusionIn this article, we continued our exploration of attribute relationships, stating that our objective would be to complement the introduction we undertook in Introduction to Attribute Relationships in MSSQL Server Analysis Services, through a more detailed examination of attribute relationships. Our concentration upon these details was enhanced by a hands-on practice session, where we gained exposure to the properties and settings that underlie attribute relationships. Our examination included a review of the nature of the attribute relationship in Analysis Services, and its possible roles in helping to meet the primary objectives of business intelligence, based upon and extending the discussion we initiated in Introduction to Attribute Relationships in MSSQL Server Analysis Services. We performed a detailed examination of the properties underlying attribute relationships, along with a review of the respective settings associated with each property, based upon a representative dimension attribute within our sample UDM, as a part of our practice session. (We stated that we would gain further hands-on practice, working with attribute relationships within additional dimensions, in the article that follows this one.) Throughout our practice procedures we obtained hands-on exposure to creating, modifying and deleting attribute relationships within a select dimension attribute within our sample UDM. About the MSSQL Server Analysis Services SeriesThis article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series. » See All Articles by Columnist William E. Pearson, III Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum. Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) Cube Storage: Introduction to Partitions Introduction to Cube Storage Attribute Discretization: Customize Grouping Names Attribute Discretization: Using the "Clusters" Method Attribute Discretization: Using the "Equal Areas" Method Attribute Discretization: Using the Automatic Method Introduction to Attribute Discretization More Exposure to Settings and Properties in Analysis Services Attribute Relationships Attribute Relationships: Settings and Properties Introduction to Attribute Relationships in MSSQL Server Analysis Services Attribute Member Values in Analysis Services MSSQL Analysis Services - Attribute Member Names Attribute Member Keys - Pt II: Composite Keys Attribute Member Keys - Pt 1: Introduction and Simple Keys Dimension Attributes: Introduction and Overview, Part V Dimension Attributes: Introduction and Overview, Part IV Dimension Attributes: Introduction and Overview, Part III Dimension Attributes: Introduction and Overview, Part II Dimension Attributes: Introduction and Overview, Part I Dimensional Model Components: Dimensions Part II Dimensional Model Components: Dimensions Part I Manage Unknown Members in Analysis Services 2005, Part II Manage Unknown Members in Analysis Services 2005, Part I Alternatively Sorting Attribute Members in Analysis Services 2005 Introduction to Linked Objects in Analysis Services 2005 Distinct Counts in Analysis Services 2005 Positing the Intelligence: Conditional Formatting in the Analysis Services Layer Administration and Optimization: SQL Server Profiler for Analysis Services Queries Mastering Enterprise BI: Time Intelligence Pt. II Mastering Enterprise BI: Time Intelligence Pt. I Design and Documentation: Introducing the Visio 2007 PivotDiagram Actions in Analysis Services 2005: The URL Action Actions in Analysis Services 2005: The Drillthrough Action Mastering Enterprise BI: Introducing Actions in Analysis Services 2005 Mastering Enterprise BI: Introduction to Translations Mastering Enterprise BI: Introduction to Perspectives Introduction to the Analysis Services 2005 Query Log Mastering Enterprise BI: Working with Measure Groups Mastering Enterprise BI: Introduction to Key Performance Indicators Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I Process Analysis Services Objects with Integration Services Usage-Based Optimization in Analysis Services 2005 Introduction to MSSQL Server Analysis Services: Named Sets Revisited Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005 Introduction to MSSQL Server Analysis Services: Introducing Data Source Views Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ... Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II Build a Web Site Traffic Analysis Cube: Part I Reporting Options for Analysis Services Cubes: Cognos PowerPlay Reporting Options for Analysis Services Cubes: MS FrontPage 2002 Reporting Options for Analysis Services Cubes: MS Excel 2002 Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I) Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions Introduction to SQL Server 2000 Analysis Services: Working with Dimensions Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
