DBGET

Note: Although the DBGET function is the central cube reference formula of OLAP, its variant, DBGETC will usually return results faster.

The DBGET Formula and its variants is central to linking spreadsheets to OLAP Server cubes.

Syntax

DBGET(Database alias,Cube,Element1,... Elementn)

You must specify an element from each of the dimensions in the cube. Element1 must be from the first dimension in the cube, Element2 from the second and so on. i.e. the sequence of the elements must reflect the sequence in which the dimensions for the cube were defined.

Example

=DBGET("Best Practices OLAP ","Status","2008","Actual","IFRS","G0000","Start")

Where "Best Practices OLAP" is the database alias name, "Status" is the cube name and the subsequent five arguments are elements from each of the dimensions in "Status".

Unique features of DBGET

DBGET can be used as any other worksheet formula. It can be copied or moved to different cells without losing its link to the cube. But, if you enter a number in a cell with a DBGET formula, you do not overwrite the formula and the number is entered into the database in the referenced cell address. This is helpful in developing data entry sheets from normal spreadsheets.

Note: If the cell address refers to a cell that contains a consolidated element, an error message is returned and the value is not entered.

Using cell references in DBGET formulas

Arguments to this function can be strings or references to cells containing strings. Strings must be enclosed in quotes. Usually, the DBGET formula is written with cell references as its arguments. For example, if the cell A1 contains the label "Best Practices OLAP" and cell A2 the label "Status", you can write:

=DBGET(A1,A2,"2008","Actual","IFRS","G0000","Start")

This means that you do not need to edit the formula if the values in the cells change.

If an element specified in the DBGET formula is not a member of the specified dimension, a #Value error occurs.

DBGET formulas can be complicated to write so OLAP Server provides help with creating them.

You can create an Alea Ad-hoc report and convert it to a formula report, selecting DBGET or DBGETC as the format.

SeeConverting Alea Ad-hoc reports to formula reports.

Another option is the Formula Wizard.

SeeFormula wizard).

There are three variants on the DBGET formula which optimize the speed with which a worksheet retrieves data from the cube. The three formulas are:

Using DBGET with attributes

In worksheets using attributes returned by the AT.HEAD and AT.SUB.HEAD functions, the specified attributes cannot be processed by DBGET or its variants. This is because these attribute functions return an attribute to the title dimensions, not an element name.

In these cases, use the DBGET function with KEY.GET. You convert the attribute specified by AT.HEAD and AT.SUB.HEAD to the corresponding element name.

The only argument required for KEY.GET is the cell containing the AT.HEAD or AT.SUB.HEAD function. This returns the last parameter of this function, the element name. To enter the correct element name, the DBGET function must contain a reference to the cell with the KEY.GET function.