Analyzing UDM Data using Microsoft Data Analyzer

Microsoft's Data Analyzer is a business intelligence client tool that allows you to analyze data graphically. With the help of charts, graphs, and easy navigation, Data Analyzer helps you to have a consolidated view on one screen so the user can better understand patterns of data and make business decisions. Data Analyzer provides the flexibility to configure various colors for showing data that help surface any anomalies in data quickly. The following steps show you how to analyze your UDM using Data Analyzer:

1. Launch Data Analyzer. You will see the Data Analyzer startup page, which allows you to create a new view or open an existing view as shown in Figure 15-49. Select the option to create a new view and click the OK button, and then click the Next button on the welcome page.

   
   Figure 15-49

2. If you had already established connections you will see the connections in the view connections page. The view connections page (Figure 15-50) shows a clean slate to start with. Click the Add button to create a new connection to Analysis Services 2005.

   
   Figure 15-50

3. Data Analyzer allows you to connect to Analysis Services directly via TCP or over HTTP; it also allows you to analyze data from a local cube file. In the connection properties page specify the Analysis Services instance name next to Server as shown in Figure 15-51. The Advanced option allows you to specify special connection string properties and HTTP passwords. Specify a name for the connection and click the Connect button. Data Analyzer now retrieves the list of databases from the Analysis Services instance along with the cubes within each database. You can choose the database name from the Catalog drop-down list and the cube from the Cube dropdown list. After selecting the catalog and cube names as shown in Figure 15-51, click the OK button. In the View Connections dialog select the connection currently established and click the Next button.

   
   Figure 15-51

4. You now need to select the dimensions to be included in your view for data analysis. Data Analyzer shows all the hierarchies of the dimensions in the selected cube as shown in Figure 15-52. The hierarchy names are shown within parentheses after the dimension name. Select the hierarchies Customer Geography from Customer dimension, Date.Calendar from Data dimension, Departments, Organizations from Organization dimension, and Product Category from the Product dimension. You will notice that certain dimensions do not have the hierarchy name qualified along with dimension name. This occurs when the dimension contains only hierarchy that is visible. Click Next after you complete your hierarchy selections mentioned in step 4.

   
   Figure 15-52

5. You now need to specify the measures that are to be included in your view along with display type. Data Analyzer supports two display types: bar and grid. The bar view provides the graphical representation, while the grid view provides the data similar to the pivot tables. In our opinion the bar view is more helpful in data analysis. Select the bar view as the display type. Select the length of the bar to indicate the Internet Sales Amount as shown in Figure 15-53. Whenever you are browsing the various members, the length of the bar is determined by the value of Internet Sales Amount. Click the Finish button after you have made the selections.

   
   Figure 15-53

6. Data Analyzer now retrieves the data from the Analysis Services instance with the help of several MDX queries, and you will see a view in your Data Analyzer as shown in Figure 15-54. Members within a hierarchy are indicated using bars and the length of the bar is determined based on the Internet sales amount. You can see that United States has a bar that has the maximum length closely followed by Australia. This allows you to easily infer that Internet sales in the United States are at least three times the sales in Germany. The visual indication really helps the analysts to capture the information quickly. In addition to that you see the Internet Sales not only based on the customer's geography, but also on various other dimensions such as Calendar and Product category. If you click a specific member, United States, in a customer geography hierarchy you will notice that that member gets highlighted (Figure 15-55). In addition to that, the length of the bars on other dimensions automatically changes because those bars now represent the data corresponding to the United States. Hover over the member United States to get the Internet Sales Amount as shown in Figure 15-55.

   
   Figure 15-54

   
   Figure 15-55

7. Data Analyzer allows you to drill down or drill up to the members in the next level. For example, if you want to see the sales within the United States you can double-click the bar for United States, or click the bar and then click the icon for drill down. If you hover over the icons on the left you will see the description of each icon. Once you drill down on United States you will see all the states in the United States and the bars representing the sales in corresponding states. You will also notice that the bars for other dimensions automatically get readjusted. You have the ability to hide members in a level or specify a filter condition to view appropriate data. For example, if you want to see the sales amount for the top N members or M percentile of the members you can do so by applying such a condition using the filter icon.

8. To drill down to the states within United States select the member United States and then double click on the member or click on the drill down icon. Launch the filter dialog by clicking on the filter by criteria icon. You will now see the filter dialog as shown in Figure 15-56. Specify filter condition to get the top five states in the United States that have the maximum Internet Sales Amount. Once you click in the Filter by Criteria dialog Data Analyzer now applies the filter and you will see the top 5 states in the United States as shown in Figure 15-57.

   
   Figure 15-56

   
   Figure 15-57

9. Data Analyzer provides you a way to visualize data in Bar View, Grid View, or Pie Chart View. For each of the dimensions. if there is not sufficient space to show the results in the Pie Chart view, Data Analyzer will default to Bar view. To see the effect of the Grid View and Pie Chart views close the windows showing hierarchies Organizations and Departments. Go to the Date (Calendar) hierarchy. Click on the Pie Chart view option at the bottom of the window. To do so, hover the mouse cursor over the "eye" (as shown in Figure 15-58) at the bottom left of the corresponding window. Similarly click on the Grid view for the Product (Product Categories Hierarchy). You will now see the data displayed in the Pie Chart view and Grid views as shown in Figure 15-58.

We have introduced you to some of the basic functionalities available in Data Analyzer. You have likely noticed the flexibility and power of analyzing data from within Data Analyzer from this brief overview. We recommend you refer to the product documentation for an in-depth understanding of the capabilities provided by Data Analyzer.

Data Analyzer dynamically creates MDX queries for each of the operations you perform in the view. Data Analyzer creates Sets and calculated members for querying the data. We have not detailed the MDX queries sent to the server because the Sets and calculated members have dynamically generated names that are lengthy, and because multiple MDX queries are sent to the Analysis Services for each user action. If we start explaining each MDX query sent by Data Analyzer, this book will grow to the size of a New York City phone book. We leave this as an exercise for the reader: to analyze the MDX queries and understand the mapping between the query and the value shown in the view. To identify the MDX query sent to the server, you can use SQL Profiler and create trace events. Please refer to Chapter 13 on how to create traces to Analysis Services.

Figure 15-58

Important

Use of correct terminology is critical in business intelligence; saying one thing when you mean another doesn't help matters. Drill down versus Drill-through is the poster child for inaccurate use of terminology. To drill down means to continue opening lower levels of dimension members, thereby "drilling" into lower levels of data granularity. Drill-through has a completely different meaning; to drill-through means to access the fact (detail) data corresponding to a cell in the cube which you learned in Chapter 9. Nobody who has dutifully read this book should ever confuse the two.