Alternatively Sorting Attribute Members in Analysis Services 2005
September 10, 2007
About the Series ...
This 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.
While it is quite common to sort the attribute members of our Analysis Services 2005 cubes based upon either their name or key values (perhaps even a composite member key), many of us encounter situations where we need to accomplish a different sort order a sort order that is based upon a characteristic lying outside the attribute members themselves. In these cases, an option we have is to order such an attribute on the basis of the name or key of a related secondary attribute.
In this article we will gain some hands-on exposure to sorting attribute members by a secondary attribute key in Analysis Services 2005. (As we shall see, sorting by the secondary attribute name is handled in a virtually identical manner.) Our examination will include:
Sort Attribute Members by a Secondary Attribute Name or Key
To support the ordering of an attributes members (I will sometimes refer to this as the primary attribute, to minimize confusion) based upon the name or key of a secondary attribute, we must select a secondary attribute that is related to the primary attribute. An attribute relationship defines the relationship or dependency between attributes.
Attributes are related to each other differently for dimensions based upon single relational tables versus dimensions based upon multiple tables. In the case of a single-table-based dimension, all attributes are usually related via the key attribute. In effect, the dimensions attributes exist to provide information about the dimensions members - members that are linked, via the dimensions key attribute, to the facts in the fact table for each related measure group. By contrast, the attributes of multiple-table-based dimensions are typically linked based upon the join key between the tables.
The Dimension Designer affords us an easy means of defining additional relationships between attributes, among other actions related to attributes (such as changing default relationships for performance enhancement purposes, etc.). In so doing, the Dimension Designer supports our ability to meet specific business requirements for additional information about members. When creating attribute relationships, we must keep in mind that the primary attribute must relate to no more than one member value within the attribute to which we wish to relate it.
Analysis Services 2005 allows us a further option within our definition of a relationship between two attributes. Based upon whether the relationship between members will change over time, we can define the relationship as rigid or flexible, as we shall see in our practice section. When we define a relationship as rigid, attribute aggregations are not recalculated every time the dimension is incrementally processed. If the relationship between members does change, however, the dimension must be fully processed.
Establishing the Capability to Sort Attribute Members by a Secondary Attribute Name or Key
Lets consider an example whereby we can obtain hands-on practice with establishing the capability to sort attribute members by a secondary attribute key. (As we have already mentioned, sorting by the secondary attribute name is handled in a virtually identical manner.) We will first create a secondary attribute, and then relate this new attribute to a primary attribute an attribute that we have identified as being a candidate for sorting by the secondary attribute key. We will then align the settings of the primary attribute to support the desired sort presentation of its members.
Lets assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the VP - Marketing group, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they have received a request to add a new sort to the Customer dimension of the existing cube structure. At the heart of the request lies a specific attribute hierarchy, Commute Distance, which, due to an oversight at design time, is not sorting its members, the respective distances, from least to most. As is typically the case within the context of our consulting events, the group assures us that, once they grasp the concepts to meet the immediate need, they will want to expand the capability to fix similar, less-than-optimal design characteristics in other areas of cube structure.
For the time being, our colleagues want simply to remedy the deficiency, and to make the Commute Distance attribute hierarchy members sort intuitively, in ascending fashion. After we initially examine the cube structure, we find a means of meeting the business requirement: we suggest using another column that already exists in the underlying Adventure Works DW star schema as the basis of the sort that the client representatives tell us that they need. We offer to show our colleagues how to establish a capability to sort attribute members by a secondary attribute name or key, whereby we will leverage the data in the column we have identified to deliver just the sort they need to meet their new requirement. (We explain that our example will consist of showing how to sort upon the secondary attribute key, but we emphasize that sorting by the name of the secondary attribute would only involve a different selection within a single property, within an essentially identical procedure.)
Our client colleagues agree that the proposed approach should meet their sorting requirement. We then set about the assembly of our example to illustrate how we will support the sort capability we have described. Before we get started with making these enhancements to a sample cube clone, we will need to prepare our local environment for the practice session. We will take steps to accomplish this within the section that follows.
Preparation: Create a Sample Basic Database within Which to Perform the Steps of Our Practice Session
Create Sample Primary Database for the Practice Exercise
Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:
Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running side-by-side installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:
Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):
Ascertain that the New Practice Database is in Place and Rename It
We are now ready to ascertain that the new Analysis Services database is in place, as well as to rename it to keep it safe from writeover in future exercises within the series. (You can, of course, always delete the practice database if you do not choose to keep it for future reference, etc.) To do this, we will work from within the Microsoft SQL Server Management Studio.
1. Start Microsoft SQL Server Management Studio.
2. Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.
3. Type / select the server name (server name / instance, if appropriate) in the Server name selector.
4. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears similar to that depicted in Illustration 1.
5. Click the Connect button to connect with the specified Analysis Server.
The Microsoft SQL Server Management Studio opens.
6. In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the + sign to its immediate left), appearing underneath the Analysis Server within which we are working.
The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.
7. Right-click the DBJ_Basic AS DB database, and select Rename from the context menu that appears next, as shown in Illustration 2.
8. Rename the Analysis Services database as follows:
ANSYS062_Basic AS DB
Having ensured that the new database is in place, and having renamed it, we are ready to begin the procedural part of our practice session.