Browsing the Dimension

After successfully creating the Dim Geography dimension, you definitely would like to see the results of what you have created and find out how you can see the members of the dimension. So far the dimension has been designed, but not deployed to the server. Indeed, there has been no interaction with the instance of Analysis Services instance yet. In order to see the members of the dimension, Analysis Services needs to receive the details of the dimension (the attributes, member properties and the multi-level hierarchies you have created). You learned in Chapter 3 that the Analysis Services 2005 tools communicate to the instance of Analysis Services via XML/A (XML for Analysis).

XML/A is a Simple Object Access Protocol (SOAP)-based XML Application Programming Interface (API), which is an industry standard, designed for OLAP and Data Mining. The XML/A specification defines the two functions, Execute and Discover, which are used to send actions to and retrieve data from the host instance. The Execute and Discover functions take several parameters that help in various actions the instance of Analysis Services will perform. One of the parameters of the Execute function is the command sent to an instance of Analysis Services — note that in addition to supporting XML/A, Analysis Services supports extensions to the standard. Following is a sample Execute request sent to an instance of Analysis Services using XML/A. The Execute request is a modified version of the one in XML/A specification available at http://www.xmla.org.

     <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"     SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">     <Command>     <Statement> select [Measures].members on Columns from Adventure Works</Statement>     <Command>     <Properties>     <PropertyList>     <DataSourceInfo> Provider=SQL Server 2005;Data Source=local; </DataSourceInfo>     <Catalog>AnalysisServices2005Tutorial</Catalog>     <Format>Multidimensional</Format>     <AxisFormat>ClusterFormat</AxisFormat>     </PropertyList>     </Properties>     </Execute>     </SOAP-ENV:Body>     </SOAP-ENV:Envelope> 

In the above XML/A, a request is sent to execute an MDX query that is specified within the command Statement on the catalog AnalysisServices2005Tutorial. The XML request shown above results in the query being executed on the server side and the results sent to the client side via XML/A.

Several commands are used to communicate to Analysis Server 2005. Some of the common commands are Create, Alter, Process, and Statement. These commands are used to change the structure of objects described. Each object in Analysis Services 2005 has a well-defined set of properties. The complete definition of the objects is referred to as Data Definition Language in this book. You will learn some of the DML and DDLs used in Analysis Services 2005 in various chapters of the book through examples. For in depth understanding of DMLs and DDLs we recommend you to read the Analysis Services 2005 documentation.

You might recall that you deployed the Analysis Services 2005 tutorial project in Chapter 2. What actually happens when you deploy a project is that the BIDS packages all the design change information in the project as a single XML/A request and sends it as a whole. In this case, you want to see the contents of the dimension you have created. Therefore you need to deploy the project to an instance of Analysis Services. Deploy the project to your Analysis Services instance by right clicking on the solution AnalysisServices2005Tutorial and selecting Deploy or using the F5 function key. When you deploy the entire project using BIDS to Analysis Services, several XML/A requests are sent by BIDS. They are:

1. Request for a list of the databases from Analysis Services to determine if the current project already exists on the instance. The project name you specified while creating the object will be used as the database name. Based on the deployment settings in your project, BIDS either sends the entire definition of all the objects or only the changes you have made since the last deploy. The BIDS will either use a Create or Alter statement based upon the fact the database already exists on the Analysis Services instance. We have not included the Create/Alter XML/A request below since it is quite large. You can use the SQL Profiler to analyze the XML/A request (you learn to use SQL Profiler in Chapter 12).

2. BIDS then sends an XML/A request to process the objects on the instance of Analysis Services. Following is the request that is sent to the server to process the dimension Dim Geography:

        <Batch Transaction="false"     xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">       <Process>            <Type>ProcessDefault</Type>            <Object>                 <DatabaseID>AnalysisServicesTutorial2005</DatabaseID>                 <DimensionID>Dim Geography</DimensionID>            </Object>       </Process>     </Batch> 

3. BIDS requests schema information through several Discover requests to retrieve information such as the hierarchies and levels. BIDS finally requests Dim Geography dimension data through another XML/A request and automatically changes the tab on the Dimension Designer from Dimension Structure to Browser so that you can view the data. The MDX query that is sent to the server by BIDs to retrieve dimension data is:

        SELECT HEAD ( [Dim Geography].[Geography].LEVELS (0).MEMBERS, 1000 ) on 0     FROM [$Dim Geography] 

Since you are familiar with MDX by now you might have deciphered most of the query. This query uses the HEAD function to request the first 1,000 members from Level 0 of the hierarchy Geography in dimension [Dim Geography]. In the FROM clause you see [$ Dim Geography]. Though you have not created any cube in your data warehouse project yet, you know that the FROM clause should contain a cube name, so how does this MDX query work? When a dimension is created the server internally stores the values of the dimension as a cube. This means that every dimension is internally represented as a cube with a single dimension that holds all the attribute values. The dimension you have created is part of the Analysis Services database AnalysisServicesTutorial2005 and is called a database dimension. Because each database dimension is a one-dimensional cube, they can be queried using MDX using the special character $ before the dimension name. This is exactly what you see in the query, [$Dim Geography].

The Dimension Designer has switched to the Browser pane as shown in Figure 5-18. The hierarchy first shown in the hierarchy browser is the most recently created multi-level hierarchy Geography. You can choose to browse any of the multilevel hierarchies or attribute hierarchies by selecting one from the dropdown list labeled Hierarchy. This list contains the multilevel hierarchies followed by the attribute hierarchies. Each attribute hierarchy and multilevel hierarchy within a dimension has a level called the All level. In Figure 5-18 you can see the All level for the hierarchy Geography. The All level is the topmost level of most hierarchies (the All level can be removed in certain hierarchies) and you can change the name of the All level by changing the property of the hierarchy. It makes sense to call the level "All" because it encompasses all of the sub-levels in the hierarchy. If a hierarchy does not contain the All level then the members of the top most level would be displayed as the first level in the dimension browser.

Figure 5-18

Assume you want to change the All level of the Geography hierarchy to "All Countries." The following steps show you how to do this:

1. Go to the Dimension Structure view of the Dimension Designer.

2. Click on the Geography hierarchy in the Hierarchies and Levels pane.

3. The properties window now shows all the properties of this hierarchy. The first property is AllMemberName and it displays no value. Add a value by typing All Countries in the text entry box to the right of AllMemberName.

4. Deploy the project once again.

5. After the deploy is successful the Dimension Designer automatically switches to the Browser tab. In the Browser tab BIDS requests you to reconnect to retrieve the latest data from the Analysis Services instance. Click on the Reconnect link shown in the Browser.

You can now see that the All level of the Geography hierarchy has changed to All Countries, as shown in Figure 5-19. You can also see in the figure that the All Countries level has been expanded to show all members in the next level.

Figure 5-19

When you expand the All Countries level, the following MDX query is sent to the Analysis Services instance to retrieve the members in the next level:

     WITH MEMBER [Measures].[-DimBrowseKey 0-] AS         '[Dim Geography].[Geography].currentmember.properties ("key0", TYPED)'     SELECT { [Measures].[-DimBrowseKey 0-] } ON 0,     HEAD ( [Dim Geography].[Geography].[All Countries].CHILDREN, 1000) ON 1     FROM [$Dim Geography] 

The goal of the MDX query is to retrieve all the members that are children of the All Countries level. Similar to the MDX query that was sent to retrieve members in level 0, this query only retrieves the first 1,000 children of All Countries level. This is accomplished by use of the HEAD function as seen in the MDX query. This query includes a calculated measure called Measures.[-DimBrowseKey 0-], which is selected in the MDX query. The calculated measure expression in this query retrieves the key of the current member by using the MDX function Properties. The MDX function Properties returns a string value based on the parameters passed to it. The Properties function returns the value of the member property that is specified as the first argument to the expression. In this query the value requested is the Key of the current member.

Other parameters that can be passed to the Properties function are NAME, ID, and CAPTION, or the name of a member property or related attribute. The properties NAME, ID, KEY, and CAPTION are called as intrinsic member properties since all attributes and hierarchies will have these properties. The second argument passed to the Properties function is optional and the only value that can be passed is TYPED. If the Properties function is called without the second parameter, the function returns the string representation of the property. If the second argument TYPED is passed to the Properties function, the function returns the data type of the property (data type that was defined in the data source) requested. For example, if the first argument is Key and if the Key of this attribute is of type integer, the Properties function returns integer values. Typically the second parameter TYPED is useful if you want to filter the results based on a member property. For example, if the key of the Geography hierarchy is an integer and if you want to see only the children of member United States, you can use the FILTER function along with the calculated measure that has been created using the parameter TYPED.

The result of the preceding MDX query is shown in the following table. The dimension browser retrieves this information and shows the names of the members in the hierarchical format shown in Figure 5-19.

	−DimBrowseKey 0−
Australia	Australia
Canada	Canada
France	France
Germany	Germany
United Kingdom	United Kingdom
United States	United States

You defined the member property for the State and City earlier. Now you want to see these member properties in the dimension browser. To do that you can either click the Member Properties icon in the Dimension Designer toolbar (highlighted in Figure 5-20) or choose Member Properties from the Menu Dimension. A dialog appears that has all the attributes of the dimension. Select the attributes Country, State-Province, and City and click OK. The member properties you have selected are now shown in the dimension browser as shown in Figure 5-20.

Figure 5-20

Expand the members of United States to see the member properties of the States and Cities under United States. The member properties of a member are also retrieved with the help of an MDX query. For example, when you want to see all the cities in Alabama, the following MDX query is sent to the server:

     WITH MEMBER [Measures].[-DimBrowseLevelKey 0-] AS '[Dim     Geography].[Geography].currentmember.properties ("key0", TYPED)'     MEMBER [Measures].[-DimBrowseProp City-] AS '[Dim     Geography].[Geography].currentmember.properties ("City", TYPED)'     MEMBER [Measures].[-DimBrowseProp Country-] AS '[Dim     Geography].[Geography].currentmember.properties ("Country", TYPED)'     MEMBER [Measures].[-DimBrowseProp State-Province-] AS '[Dim     Geography].[Geography].currentmember.properties ("State-Province", TYPED)'     SELECT { [Measures].[-DimBrowseLevelKey 0-], [Measures].[-DimBrowseProp City-],     [Measures].[-DimBrowseProp Country-], [Measures].[-DimBrowseProp State-Province-] }     ON 0,     Head ( [Dim Geography].[Geography].[City].&[Huntsville].Children, 1000) ON 1     FROM [$Dim Geography]     CELL PROPERTIES VALUE 

Member Property Icon Similar to the MDX query you analyzed earlier to retrieve all the members of the All level, this query retrieves all the Alabama City members. The member properties City, State-Province, and Country are retrieved with the same query as calculated members using the WITH MEMBER clause as seen in the above query.