Drilling


Drilling shows the power of the various engines in the reporting stack seen in Figure 20.1 working in unison. The report engine provides the display of the microcube and interactivity; the microcube creates the calculations, and the semantic layer provides behavioral rules for the engine when the data is being manipulated, calculated, and projected. In drilling, this becomes even more apparent as the semantic layer defines the data hierarchies through which the data can be navigated. Likewise, the semantic layer also provides the means for the user to drill "beyond the cube" seamlessly and return to the database when his analysis goes beyond the content contained within the report. Thus, SQL is autogenerated on the fly, and the data is returned to provide the next step for the business user. The next subheadings cover this in detail.

Hierarchies

Hierarchies are natural data relationships that exist in the data. The term hierarchy refers to the aggregate relationship between entities. For example, time is often thought of as a hierarchy of entitiesYear, Quarter, Month, Week, Day, and so on. The nature of the entities is such that they can be thought of as parent-child relationshipsall of the days comprise a Week, a certain number of weeks comprise a Month, three months comprise a Quarter, and so forth.

When navigating data, drilling refers to moving up and down the data hierarchy. If the user is looking at Year and wants to explore the data at the Quarter level, this is referred to as drilling down because it moves from the parent to the child (year to quarter). Drilling down therefore refers to moving downward through the hierarchy, from parent to child. Drilling up refers to moving upward through the hierarchy, from child to parent.

Business Objects uses a third term, drill by. Drill by refers to moving more than one level up or down, or moving from one dimensional hierarchy to anothersay, from Time to Geography. So, if the user drilled down from Year to Quarter, but then wanted to see the data by Region, this would be drilling by (Region).

The microcube not only takes the resultset from the query, but it understands the hierarchies defined in the semantic layer and allows for navigation down, up, and by these hierarchies. As mentioned previously, the microcube recalculates as the dimensions change, so measures are recast as the drilling takes place.

Drilling on the Web Intelligence Report

In order to activate Drill mode, click the small magnifying glass button in the top-right of the report window. When clicked, the report redraws itself with hyperlinks under the dimensions and measures. Drilling can take place on any of the report blocks or sections, and can take place on dimensions as well as measures. Detail objects, depicted by the small green tetrahedron, are not drillable. Figure 20.19 shows the document in Drill mode.

Figure 20.19. The document in Drill mode. Note that the rows, columns, and values are all underlined, indicating they are drillable.


The ToolTip in Figure 20.19 tells the user what the next dimension is in the hierarchy. The option here is to drill down because year is at the top of the hierarchythere is nowhere to go up from here. Note that California is also drillable, as are the measures in the cross-tab. Likewise, the chart to the right is also drillable. Drill down on 2001; the result is seen in Figure 20.20.

Figure 20.20. When the report is drilled down by Year, Quarter is replaced in the row of the cross-tab block and Year appears on the Analysis Context in the Year drop down.


Notice in Figure 20.20 that the Year object is replaced with the Quarter object in the rows. Also note the up arrow that allows the user to drill up the hierarchy. The hyperlink on the Quarter values implies drilling down. Also note that the chart shows only 2001 data. Unlike report filters, drill filters apply globally to the report tab.

At the top of the pane is the Analysis Context drop-down menu containing 2001. This box allows the user to change the values of the Year variable and change the report display automatically.

Now drill up to Year, and then drill down on California.

In Figure 20.21, note that the columns in the cross-tab changed to Los Angeles and San Francisco. The drop-down list on State shows all the possible values contained within the report. If you choose another state, the report will show the projections for that state.

Figure 20.21. When the report is drilled down by State, City replaces State in the column of the cross-tab block and State appears on the Analysis Context bar in the State drop down.


Now drill up to the original state, and then drill down on the measure intersection for California and 2001.

Figure 20.22 shows drilling on the measure moved both dimensional hierarchies down, from Year to Quarter and from State to City. There are now two drop-down lists in the Analysis Context bar.

Figure 20.22. The report is drilled down by the measure object, and Quarter and City replace Year and State in the row and column of the cross-tab block, and Year and State appear in drop downs on the Analysis Context bar.


Now right-click on Quarter and choose Drill By, as seen in Figure 20.23.

Figure 20.23. The context-sensitive menu is visible and Drill By is selected. All hierarchies represented in the report are available, so drilling by will swap the row with any dimension from any hierarchy.


When drilling by, all dimensional hierarchies present in the report can be seen. By choosing one hierarchy, you see the elements in the hierarchy starting at the top of the hierarchy and moving down, as is demonstrated by Lines, Category, and SKU Desc in the Product hierarchy. By choosing Category, you swap the Quarter object with the Category object, and the cross-tab is redrawn, as shown in Figure 20.24.

Figure 20.24. This is the report after drill by, with Category replacing Quarter. The Quarter object is now in the Analysis Context bar with the State and Year drop-down menus.


Do you see a difference in the accompanying bar chart? It now reflects the categories in the cross-tab. Another feature of drilling is synchronizing the dimensional drills of report blocks when they are being displayed. To synchronize the cross-tab and chart, go to the InfoView Preferences and change the drill behavior to Synchronize Drill on Report Blocks. As previously mentioned, the table and chart do not need to be in sync, but they can be. Regardless of whether the dimensions change in the chart, the chart data is filtered by the values in the Analysis Context drop-downs. The only difference is changing the dimensional display in the chart to match the cross-tab display. Without the synchronization option, the x-axis on the chart would show the Year object (filtered for 2001).

Drilling Beyond the Cube

The report contains a small resultset, but presumably the database contains much more data. Some of this data might be defined within the data hierarchies in the semantic layer, but the objects were not chosen at report runtime. When drilling, the user can drill beyond the data in the report. He would then be faced with some options that are explored next.

Note

It is possible via the CMC to disable the user from drilling beyond the cube and into the database. If the administrator turns this feature off, the user will not be able to explore data outside of the resultset contained within the report itself.


Go back to the original drill showing Quarter and City (Figure 20.23), and drill down to month. Click on the Month drill-down hyperlink, which presents the following dialog in Figure 20.25.

Figure 20.25. The Extend the Scope of Analysis dialog. The hierarchy selected shows the same values in the Analysis Context drop-downs.


The Drill dialog asks to extend the scope of analysis on the Time dimension by including Week, which is the next object in the hierarchy. It also shows another object, Holiday, which is the fifth and final object in the hierarchy, with a check box to include it in the results if desired. The filters under each dimension reflect the current state of the drill filters displayed in the report. When fetching more detail data, the results can be quite large. Therefore, the user is presented with the option of filtering the results at any level, based on the current drill filters, to help ease the resultset size.

Note

On very large databases, this can be a significant performance enhancement. Runaway queries are managed by the semantic layer and security layer, when row restrictions and query run time governors are in place. This means that multimillion row resultsets can be avoided through proper security administration.


Choose the Week option from the dialog. Figure 20.26 shows the result.

Figure 20.26. The report after Week was chosen. The SQL was dynamically generated and executed, the microcube was dynamically updated, and the crosstab row reflects the Week object. Month now appears in the Analysis Context bar with the other dimensional dropdowns.





Crystal Reports XI(c) Official Guide
Crystal Reports XI Official Guide
ISBN: 0672329174
EAN: 2147483647
Year: N/A
Pages: 365

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net