Analysis Services has changed the way many businesses look at data. Although OLAP certainly existed before Microsoft began bundling Analysis Services with SQL Server, Microsoft's move has made OLAP affordable and accessible to companies of any size. All companies can now analyze their data in order to make decisions more quickly and based on better information.
Analysis Services includes a feature called drillthrough that, once enabled by cube designers, lets data analysts drill from the cube data into the underlying relational tables. Drillthrough provides a look at the records that make up the data you see in the cube. For example, if the cube record shows that a client bought $10,000 worth of a particular item, you could drill through on that record to discover whether the client placed a single order for $10,000 or 10,000 orders of $1 each.
Drillthrough works well in most cases, but it has a shortcoming: You can't modify a drillthrough query on the fly. The cube builder sets up the query once for the cube and it's static after that point. If you need to pass parameters into the drillthrough query, you can't use Analysis Services alone. Instead, you can create your own drillthrough report in Reporting Services, then use a cell-level action in Analysis Services to call the report and pass the appropriate parameters. This option is especially useful when you need to pass one of several levels of a dimension as a parameter to a drillthrough query but don't know ahead of time which level you'll have to pass. A drillthrough report also works when you're performing counts, if the field you're counting in the fact table can contain NULL values.
Defining the Scenario
Say your company performs cleanup projects for other companies, and you want to track projects as they flow through a six-step process. In this process, a potential client asks you to bid on a project, you bid on it if you want to pursue it, the client accepts or rejects the bid, you sign the deal, you perform the work, and (usually) you get paid. To keep this example simple, we'll look mainly at the bid phase and the completion phase. For projects that don't have Bid or Completion Phase values—projects that were input but never turned into bids—you have to deal with NULL values.
The project-tracking example involves a simple star schema consisting of the three tables that Figure 1 shows. The DateDim table merely holds a date field that Analysis Services' Dimension Wizard will break into its constituent pieces when you build the Date dimension in the cube. The ProjectDim table holds a ProjectID field and a description field. The StatusFact table shows the status of each project for a particular date.
Figure 2 shows all the data the system uses. Note that only five records have a BidPhase value, and only two have a CompPhase value. Also, one record has a CompPhase but no BidPhase. In theory, that shouldn't happen, but in reality some work might be performed without a bid. More important than this sample data, however, is the concept—you need to count values in the BidPhase and CompPhase fields, but the NULL values in those fields make drillthrough problematic.
The values in the BidPhase and CompPhase fields are integers representing the number of days a project was in each phase. A NULL value in the BidPhase or CompPhase field means that the project hasn't yet been bid or completed. A 0 value means that the bid was accepted or rejected by the client or the project was started and completed. A value greater than 0 indicates that the client accepted or rejected the bid or the work was completed in the number of days specified. Knowing the number of days is important so that you can calculate the average values over time. However, at any point, you might also want to determine how many projects have been through the bid phase, so you also need to be able to count the records with a value in the BidPhase field. Retrieving a count is simple to do inside a cube if you use Count as the measure's aggregate function.
Working with the Cube
You'll work with the cube in the Analysis Services cube editor. As Figure 1 shows, the cube design is simple, with two dimensions: Date and Project. Below the Date dimension's All level are Year, Quarter, Month, and Day levels. The Project dimension has just an All level and a Project level. The cube contains two measures, BidCount and CompCount. You set up those counts by choosing an aggregate function value of Count. Figure 3 shows the cube design, including the details of CompCount.
To enable drillthrough on the cube, click Tools and choose Drillthrough Options. You then enable drillthrough and choose the fields you want to display when someone drills through. Figure 4 shows drillthrough enabled and all fields selected.
Now, it's time to look at the data and attempt to drill through to the underlying data in the relational tables. After processing the cube, you can look at the data in a variety of tools, although as I show later, it's best to avoid the cube browser that comes with Analysis Services. Figure 5 shows the data as viewed with ProClarity 6. Here, you can see in the grid just what you'd expect: For All Date, BidCount is 5 and CompCount is 2, meaning that five records in BidPhase and two records in CompPhase have a numeric value of greater than or equal to 0. You also see a list of detail records—the result of using default drillthrough—far more than the five records that make up BidCount and two that make up CompCount. If you drilled through on the BidCount value, you'd expect to see the five records that make up that value. Instead, you'd see 28 records. This happens because, although the cube counts only the records that have values, drillthrough doesn't filter out the records that have NULLs for BidPhase.
At this point, the fix appears simple. In the Drillthrough Options dialog box is a Filter tab that lets you add a filter, or WHERE clause, to the query that retrieves the relational data. Because the drillthrough query is retrieving relational data, the statement is SQL, not the MDX you use for querying cubes. To fix the problem of excessive records in the drillthrough, you might try this filter (note that although the filter is a WHERE clause, you don't actually add the word WHERE):
BidPhase IS NOT NULL
Using this filter to drill through on BidCount works perfectly. Unfortunately, if you drill through on CompCount, you get the results that Figure 6 shows; in fact, these are the same results as if you had drilled through on BidCount. Only two records have a CompPhase, but the drillthrough displays five records—and only one of those five is correct. When drilling through on BidCount, you want to filter out NULL values for the BidPhase field. When you're drilling through on CompCount you'd want to change the filter:
Prev. page
[1]
2
next page 
