DBGETC

The DBGETC formula is analogous to the DBGET formula and has the same syntax:

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

When a report is recalculated, the report is scanned and all the DBGETC formulas are collated into one request to the database. DBGETC usually returns results considerably faster than the DBGET formula and should be used as a rule. DBGET is usually only faster when accessing low levels of consolidation of a cube. But one disadvantage of DBGETC is that it causes a report to recalculate twice every time <F9> is pressed. However, even a two-pass recalculation of DBGETC formulas is usually faster than a one-pass recalculation of DBGET formulas.

Under certain circumstances the arrangement of large amounts of data may increase response time if the data is read from a Microsoft Excel cross table. To resolve this, use these functions:

Value buffer

While an Alea Ad-hoc or formula report is open, the Value Buffer caches the results of any DBGETC formulas used in the report. This can be convenient if you are working with a large report containing many formulas, because you do not have to wait for the values to be recalculated in the database. You recalculate the values by clearing the buffer.

To clear the Value Buffer, click Clear Value Buffer in the Database group on the ribbon.

Note: DBGETC and the Value Buffer are not available if the OLAP Server add-in is disabled.

See OLAP add-in.

Unique features of the DBGETC formula

DBGETC 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 DBGETC formula, you do not overwrite the formula. 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 DBGETC 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:

=DBGETC(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 DBGETC formula is not a member of the specified dimension, a #Value occurs.

DBGETC 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.

See Formula wizard.

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

Using DBGETC formulas with attributes

In worksheets using attributes returned by the AT.HEAD and AT.SUB.HEAD functions, the specified attributes cannot be processed by DBGETC 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 DBGETC 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 DBGETC function must contain a reference to the cell with the KEY.GET function.