Dimension queries

When you are working with an OLAP database, a common practice is to retrieve elements from a dimension and apply the same action to each of its elements. The BI# language offers the OLAPElementList data type and the foreach loop statement to support this practice. The workflow to implement such a processing logic follows these steps:

1. Instantiate an OLAPElementList object and populate it with the dimension elements that must be processed.
2. Loop over the collection of OLAPElement instances and perform the required action on every element in the body of the loop.

The first step of the workflow can be simplified by selecting elements from a dimension in form of a declarative statement. Instead of defining a procedure that retrieves the elements, you can declare a list of criteria upon which the elements must be retrieved. This is exactly the purpose of dimension queries. Dimension queries allow to retrieve elements from a single dimension by defining selection criteria without writing the actual code that selects all possible candidates and finds the ones among them that qualify for a given list of criteria.

By using dimension queries, the workflow to implement the processing logic changes in this way:

1. Define a dimension query with a list of criteria upon which the elements of a given dimension must be selected. The query evaluates an OLAPElementList object.
2. Loop over the collection of OLAPElement instances and perform the required action on every element in the body of the loop.

To write a dimension query, you need this information:

• A valid OLAPConnection instance
• The name of the dimension to retrieve elements from or a valid OLAPDimension instance
• The names of the elements and their attributes
• The values of the attributes

This example demonstrates how to use dimension queries:

    OLAPConnection conn = OLAPCreateConnection("LOCAL/Application Engine", "Admin", "");
    OLAPDimension dim = OLAPGetDimension(conn, "Units");
    
    // Print all elements with their attributes
    OLAPElementList a = on conn select all from dim;
    foreach(OLAPElement el in a)
    {
        WriteLine(el + "[\"Currency\"]=" + OLAPGetStringAttribute(conn, el, "Currency"));
    }

    // Select leaves from the Units dimension with the name not "U01"
    // and CURRENCY attribute either EUR or not USD
    string value = "USD";
    string attrName = "currency";
    OLAPElementList lst1 =
        on conn select leaves from dim
        where @()!="U01" and (@(attrName)=="EUR" or @("Currency")!=value);
    foreach(OLAPElement el1 in lst1)
    {
        WriteLine(el1);
    }

    // Select parents from the Units dimension with the name "greater" than "Name"
    value = "EUR";
    OLAPElementList lst2 =
        on conn select parents from "Units"
        where @()>="Name";
    foreach(OLAPElement el1 in lst2)
    {
        WriteLine(el1);
    }

In these examples, dimension queries are used as the right side of an OLAPElementList variable declaration. To define a criterion upon an attribute's value or an element's name, you must use the @ function in the left side of a predicate (where clause). It expects either a string parameter (variable or a hardcoded string literal) or no parameter at all. In the first case, for example, @("Currency")!=value the parameter is used to evaluate the name of an attribute. In the second case, for example, @()>="Name" no parameter indicates that the predicate is equal to the name of an element.