- Analysis Services Cubes
- Analytical Views
- MDX Queries
- DW Maintenance
- Summary
Analytical Views
Once you have built cubes, you are ready to create custom reports for displaying the data to your users. You have several options for the DW presentation layer. Microsoft Excel pivot tables are a fine way to analyze data if you just need basic functionality. In fact, because most organizations use Excel as a spreadsheet management tool, you are likely to create many of your reports in Excel. Yet another option that comes with MS Office is using Office Web Components to create simple web pages for viewing the warehouse data. Several third-party vendors supply advanced analytics’ tools for a considerable price. Perhaps the most commonly known business intelligence vendors include Cognos, ProClarity, MicroStrategy, and Business Objects. Although you could write a custom reporting tool for your own company’s needs, rest assured that third-party vendors have excellent offerings that you won’t match with a small effort.
Let’s take a brief look at a couple of analytical views in ProClarity to see the functionality available in this tool. Figure 2 shows the top five products that Northwind sold in 1996.
![Figure 2](/content/images/art_pavliashvili_dw2/elementLinks/thfig02.jpg)
Figure 2 ProClarity view of top five products sold in 1996.
You can easily change the dimensions and measures presented on the report, simply by dragging and dropping the dimensions and measures you wish to see on rows and columns; you can also choose from a variety of chart types to meet the specific needs of your users.
ProClarity also allows you create a "decomposition tree" to see the breakdown of your business operations. For example, the decomposition tree in Figure 3 shows the top five customer countries where Northwind sold products in 1998. It further informs us we sold in 16 other countries in 1998, and these bottom 16 countries account for approximately 36 percent of sales.
![Figure 3](/content/images/art_pavliashvili_dw2/elementLinks/thfig03.jpg)
Figure 3 Breakdown of 1998 sales by customer on country level.
Next we can expand one of the top nodes, perhaps Germany, to find out which cities accounted for most sales there (see Figure 4).
![Figure 4](/content/images/art_pavliashvili_dw2/elementLinks/thfig04.jpg)
Figure 4 Breakdown of sales in Germany.
The decomposition shows that Cunewalde was the top city for sales accounting for almost half of all sales in Germany. On the other hand, the bottom six cities accounted for only 12 percent of all sales. If we drill down under Brazil’s Rio de Janeiro, we see that all sales in this city came from three stores (see Figure 5).
![Figure 5](/content/images/art_pavliashvili_dw2/elementLinks/thfig05.jpg)
Figure 5 Sales in Rio de Janeiro.
As you can see, ProClarity enables your business users to browse the data and create very intuitive and informative reports without any programming. Armed with this tool and the cubes you design, executives can make strategic decisions for improving the business.