SQL Server 2005 : Advanced OLAP - Perspectives

1/2/2013 6:23:16 PM

One hallmark of Analysis Services 2005 is its flexibility and accommodating nature. For example, the ability to have drillthrough actions, role-playing dimensions, and multiple fact tables allows you to use a single cube for a multitude of multidimensional and relational queries. With Analysis Services 2000, each fact table requires its own cube, role-playing dimensions do not exist, drillthrough functionality is much more limited, and the lack of proactive caching means that cube data cannot be as fresh as is sometimes necessary.

This notion of a single cube acting as a one-stop shop for all your dimensional data gives rise to an Analysis Services 2005 concept called the unified dimensional model (UDM). The UDM paradigm advocates softening the distinction between dimensional and relational data and promotes an outlook, if you will, that a cube is more than just a cube. The UDM philosophy sees a properly built Analysis Services 2005 cube as a comprehensive repository for all aggregated and fact-level data.

That’s all well and good, but the UDM paradigm causes an issue of its own: If you jam too much stuff into a single cube, many of your users might become overwhelmed by the content of that cube and therefore find it less useful. For this reason, Analysis Services 2005 introduces the ability to create perspectives within your cubes.

Perspectives are simply filtered views of a cube’s content. You define a perspective by picking a set of specific measure groups (or specific measures), dimensions (or individual attributes and hierarchies), calculations, KPIs, and actions for the perspective and assigning it a name. Client applications treat perspectives as if they are separate physical cubes. You can, for example, combine sales, inventory, and HR information into a single cube, and then publish each category of data as a separate perspective. This gives you the simplicity and power of the UDM paradigm without dispensing with the ability to deploy components of your dimensional data as discrete units that appear as cubes in their own right.

Creating a perspective is easy. Simply click on the Perspectives tab of the cube designer and click the New Perspective toolbar button (second from the right), the Cube/New Perspective main menu option, or the New Perspective shortcut menu option. Any of these actions creates a new column on the Perspective tab’s grid. From there, you enter the name of your perspective and then select all of the cube objects you want to include. For now, create a new perspective called Product Info and assign it the entire Main measure group, the Products and Suppliers dimensions, the Average Total Sales KPI KPI, the Total Sales Drillthrough action, and the two calculated measures (Adjusted Sales and Adjusted Sales Growth).

Save and deploy your changes. In the cube browser, first reconnect (you might have to do this more than once to see the changes), and then notice that the Perspective drop-down list now contains two items: Sales (the physical cube) and Product Info (the perspective we just created). Select Product Info from the drop-down list, and notice that in the metadata tree view only the cube’s measures (including the two calculated measures) and the two dimensions we selected when defining the perspective are listed, as shown in Figure 1.

Figure 1. The cube browser with the Product Info perspective selected and highlighted

Note also that the calculated measures are both listed under the Sum display folder because the Calculation Properties settings illustrated in Figure 6 were applied to the Sales cube, and they cascade through to the perspective as well.

In Management Studio, the Object Explorer window enumerates only the physical cubes in an Analysis Services database. However, the MDX query window lists the perspectives in the Cubes drop-down list (and because the Product Info perspective precedes the Sales cube alphabetically, the former will be selected by default). The Metadata tab lists just the measures (including calculated measures), dimensions, and KPIs, if any, that belong to the selected perspective or cube, as shown in Figure 2.

Figure 2. The Management Studio MDX query window shown with the Product Info perspective selected and highlighted

Perspectives provide an audience-targeted experience for your cubes, and they do so through server-side configuration, easing the burden on application developers. This notion of a server-enforced customized view of a cube can be extended. For example, language localization of the names of your cube’s objects and the cube’s contents can be governed on the server as well. You can accomplish this by creating Analysis Services 2005 translations. Also, enforcement, in the true sense of the word, is made possible by creating security roles that include declaratively specified membership and cube object-level permissions.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone