Local-Cube MDX

Give mobile users access to the data they need

A common shortcoming of analytic applications is that they can't support mobile or disconnected users. Because analytic applications are complex, developers move more application functionality to Web browser—based UIs, which use dynamic HTML (DHTML) and JScript to minimize the amount of application code that workstations download. Unfortunately, disconnected workstations (e.g., laptops) can't run this limited code without a network connection. Because I'm one of those mobile users, I appreciate applications that I can use whether or not I'm connected. The number of users like me is growing; more workers in the enterprise are using laptops instead of desktop computers. Managers, especially, rely on mobility, and they're heavy consumers of analytic applications. To support disconnected users, developers need to enable users to take part or all of an application with them.

I don't have a solution that will make your fancy DHTML Web application run well on a disconnected laptop. But I can tell you about a new feature in SQL Server 2000 Analysis Services that makes supporting disconnected users easier: local-cube Data Definition Language. DDL provides a simple way to create local-cube files in Analysis Services through MDX. These local-cube files let you put part or all of the data from a server-based cube onto a laptop. You can then use the local-cube file to perform the same analysis that you could if you were connected to the OLAP server on a network. To create a local cube without this new MDX syntax, you must construct a verbose SQL-like statement and pass it to ADO through a connection string.

Local-cube DDL is superior to the old connection-string method for three reasons. First, the shortcuts in the DDL syntax make using it simpler than repeating all the details of the server-based cube to create a local cube with the same dimension structures. Second, most OLAP applications don't give users the ability to customize the connection string to the required degree, so developers created custom applications to provide the CREATECUBE functionality. Third, a variation of the new DDL can create session-scoped temporary cubes. (I explore session-scoped cubes in a moment.)

Creating a Local Cube Simply

Let's compare the syntax you use to create local-cube files through an ADO MD connection string with the syntax you use to make the same cube in the local-cube DDL. In this example, we create a local cube from the FoodMart 2000 Sales cube with the full Customers, Product, and Time dimensions and the Unit Sales measure. Listing 1, page 65, shows the ADO MD connection-string syntax you use to create the cube. The syntax is much like SQL syntax. A CREATECUBE statement defines the structure (as the CREATE TABLE statement does in SQL). Then, an INSERTINTO statement uses SELECT to fill the cube. Because the CREATECUBE statement is independent of the INSERTINTO statement, CREATECUBE contains no information about where the data will come from. Therefore, you can't copy the structure of the source cube. You must include all the details—level names, their types, and information about the measure's aggregation method.

Listing 2 shows how to use the new local-cube DDL to create an equivalent local cube. DDL requires just one statement to perform the whole operation. Because the statement specifies both the source and destination cubes, Analysis Services can determine the structure of the source cube's dimension and replicate that structure in the new local-cube file. This DDL statement is much easier to create than the old connection-string commands. Also, because you can execute this DDL statement in an existing connection, you can create your own local-cube file by simply typing the statement that Listing 2 shows into the MDX Sample Application that Analysis Services includes, then executing the statement.

When users give presentations, travel to remote offices or customer sites, or take work home from the office, they rarely need all the data in a cube, but the information they need can vary from user to user. Therefore, the local-cube DDL lets you subset the cube either by dimension or by slicing by a member. The example in Listing 2 shows how you can subset the cube by dimension. The second type of cube subset, slicing by a member, lets you choose information appropriate to a specific user's needs. For example, a regional sales manager might need only the portion of the cube that includes sales for her region.

Listing 3 shows an example of slicing a cube by a member. This DDL statement creates a local cube with four dimensions: Time, Product, Customers, and Measure. The Measure dimension contains only Unit Sales. The Customers dimension includes only two levels (City and Name) and contains only Los Angeles and the customer names within Los Angeles. (Los Angeles is the member that I used to slice the cube.)

You need to remember a couple of details when you slice by a member. First, you can slice a cube by members only in the top dimension level in the local cube. For example, in Listing 3, I included the City and Name levels of the Customers dimension. Because City is the top level, City is the only level from which I can pick a member to slice. Second, remember that if you slice a dimension in a way that removes the dimension's default member, you need to specify a new default member. If you don't specify a new default member for the dimension, the top left member (i.e., the first sliced member) will be the default.

The ability to subset a cube by dimension or by slicing by a member is useful for limiting the amount of data that the local PC downloads. Subsetting a cube also reduces the complexity of analysis. Reducing the amount of displayed information to a minimum relevant subset is crucial because irrelevant information can keep users from seeing patterns or trends. Analyzing data is like looking for a needle in a haystack—the bigger the haystack, the longer it takes.

Listing 4 shows the complete Backus-Naur form (BNF) notation for the local-cube DDL statement that Listing 3 shows. BNF reveals the legal syntax rules for writing DDL statements. For those who aren't familiar with BNF notation, Table 1 shows definitions for the symbols in Listing 4. All other punctuation symbols (e.g., commas, parentheses) are literal—they're included in the described language just as you see them in the BNF notation.

A variation of the CREATE GLOBAL CUBE statement, CREATE SESSION CUBE, lets the MDX programmer use Analysis Services to create a cube local to the current session. CREATE SESSION CUBE is designed primarily for use with data-mining models. (For more information about why and how to use CREATE SESSION CUBE, see "Analysis Services Data Mining," September 2000.) You can also use CREATE SESSION CUBE to create session-level cubes that don't use data-mining models. You can use session-level cubes in an analytic application to limit access to portions of a cube, either to reduce complexity or to increase security. For an example of the legal syntax rules for CREATE SESSION CUBE statements, download the CREATE SESSION CUBE BNF notation from SQL Server Magazine's Web site at http://www.sqlmag.com (see "More on the Web" for download instructions).

Although Web applications are increasingly popular, more users are working on laptop computers. These trends conflict because laptops are frequently disconnected from the network and Web applications don't support disconnected users well. If you're building an analytic application for your company, don't forget your mobile users. The new local-cube DDL can make supporting mobile users easier. Be sure to give your users a mechanism to create local-cube files and the ability to take enough of the application with them to use those files.

To practice writing a query to analyze sales promotions, see the sidebar "November MDX Puzzle." For the answer to my last column's puzzle, see the sidebar "September MDX Puzzle Solution Revealed."