Excel Cube Functions

Written by james highland
  • Share
  • Tweet
  • Share
  • Pin
  • Email
Excel Cube Functions
Databases are the building blocks of modern technology. (code image by Charles Taylor from Fotolia.com)

The industry term "cube" refers to a method of analysing the contents of a database for purposes of learning more about your business. Data cubes are complex multidimensional sets of data derived from raw information stored in a standard database. A cube is constructed using third-party software that operates on data dumped from your original database. Microsoft Excel is one program that can create data cubes for additional analysis. The program added cube functions to its 2007 version.

Other People Are Reading

CUBEMEMBER

As data cubes can become large and complex, it is sometimes necessary to query the cube to see if a particular piece of information is available. The Excel function "CUBEMEMBER" does exactly this. It is formed as a standard Excel formula, beginning with an equal sign. The parameters of the function narrow down the aspects of the cube information you seek. The two required components of the function are the field for database connectivity and the type of analysis categorisation of that field. Optionally, if the database stores data with caption labels, that may be included in the function to make the search run faster. The formulas is constructed as "=CUBEMEMBER(connection,member_expression,caption)" with the last component, "caption", optional. Parameters are separated by commas as is standard in all Excel functions that involve multiple parameters. Text strings must be placed in quotes inside the function. For example, the formula may read: =CUBEMEMBER("Sales","[Time].[Fiscal].[2004]"). The "Sales" parameter isolates the name of the data field, while the more complicated "[Time].[Fiscal].[2004]" outlines the expression that the cube forms between the data. The formula will simply return the name of the field if it exists in the cube. This serves as verification that it is indeed a part of the data set the cube constructs.

CUBEMEMBERPROPERTY

The "CUBEMEMBERPROPERTY" function is similar to the "CUBEMEMBER" function except that it takes the process one step further and actually returns the value of the searched property, rather than merely confirming that it exists. This can take longer to complete, which is why the "CUBEMEMBER" function is more efficient for confirming the presence of data than extracting it. All the parameters of the "CUBEMEMBERPROPERTY" are required for it to work. The construction of the formula is "=CUBEMEMBERPROPERTY(connection,member_expression,property)". The first two parameters are identical to their use in the "CUBEMEMBER" function. The last parameter, "property," is a text string for the name of value that the function returns, so it may be assigned as a variable. Alternately, instead of adding this value directly to the formula, a cell reference may be substituted which refers to a cell that contains the desired text string.

CUBERANKEDMEMBER

The "CUBERANKEDMEMBER" is a useful function for evaluating where a particular item of data stands in relation to other similar items. The function allows you to pull Top 10 lists for queries such as "best sales," "busiest production weeks" and other similar concerns. It also allows you to specifically extract a ranked piece of data. For example, if you seek information on the best or worst salesman, or the fifth best-selling product. The function requires three parameters with an optional fourth. The formula is constructed as "=CUBERANKEDMEMBER(connection,set_expression,rank,caption)". The first two parameters are similar to the first two parameters of other similar functions, including "CUBEMEMBER" and "CUBEMEMBERPROPERTY". The third parameter, "rank," is simply the desired rank of the item you seek. To return a Top 10 list, use 10 instances of the same function but alter the "rank" for each one, from 1 to 10. The last parameter, "caption," is optional and identical to the "caption" parameter of "CUBEMEMBER".

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.