When a client, who I’ll refer to as Big Green Cola, asked my team to help them “run scenarios” using a cube, we were skeptical as to whether a cube was the right technology to use. We wanted to make sure that we would be able to meet their requirements, and we feared that the client was hoping to run extremely complex simulations that wouldn’t be possible using cube technology. After several meetings, during which we discussed the client’s needs in more detail, we agreed to attempt to design and develop a cube to run the scenarios.
Big Green Cola manufactures and distributes soft drinks. The company contracts with independent distributors, who in turn distribute to retail outlets. Given the recent upheaval in the economy, the corporate officers at Big Green Cola became concerned that some of their independent distributors might not be able to continue distribution.
Big Green Cola wanted to do scenario-based risk analyses that would simulate the potential failure of some of their distributors to see what effect it might have on their operations. Big Green Cola wanted to be able to plan for the possible reassignments of retail outlets to competing independent distributors on short notice in the event that a distributor declared bankruptcy or otherwise went out of business. The cube would let them see which geographic areas would be affected by the failure of a particular distributor and which other Big Green Cola distributors were already operating in that same geographic area or nearby.
A major user requirement was that the Scenarios Cube show the original structure of the route and its distributor, even after a distributor and its route were cast as “unhealthy” in a given scenario. In other words, the change in status would be reflected only in the measures and calculated members, not in the hierarchies or pivot table layouts. This would make actual versus scenario distribution patterns more obvious, and thus instructive, for the users. Let’s take a look at how we designed and built a cube that let our client use a cube to run simple simulations of changing conditions. (You can download the sample cube by clicking Download the Code Here at the top of the article page.)
Distribution Scenarios Cube Star Schema
We had already provided Big Green Cola with a general usage cube that included dimensions for Date, Time, Product, Distributor, Route, Retail Location, and Geography. The Distribution Scenarios cube reused four of those dimensions: Distributor, Retail Location, Route, and Geography, as shown in Figure 1.
The original Distributor dimension contained a distributor ID, distributor code, and distributor name. The RetailLocation dimension included only RetailLocationID and RetailLocationName, although there are plans to add groups and categories to this dimension in the future.
The Route dimension included information from each distributor regarding how that distributor groups and runs its delivery logistics. Big Green Cola was considering using additional data provided by the distributors for even better short-term contingency planning using geocoded data, but the company decided to table that enhancement for a future release. In lieu of the geocoded data, the Routes dimension simply provided a recognizable grouping of the locations that Distributors actually use in their planning and delivery and that the Big Green Cola users found helpful when developing contingency plans.
The original Geography dimension included the States, Regions, and Divisions of the United States used by the Census Bureau. For more granularity, we added zip code data provided by the US Postal Service. The original Geography dimension, therefore, contained the Region, Division, State, City, and Zip Code levels. For the purposes of this article, however, we reduced the dimension so that it shows only StateName, City, and ZipCode.
Scenario Facts
The single measure in our fact table was based on the Cases field and represented the average case volume. The average case volume was derived by rolling up the total number of cases of all varieties of soft drink products delivered to a single retail outlet during a seven-day calendar week. These weekly numbers were averaged for data going back three months. The measure was simply called Cases in the fact table and was renamed Total Cases in the cube.