Dimension queries

When you are working with an OLAP database, a common practice is to retrieve elements from a dimension. Then you 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 a 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 enable you to retrieve elements from a single dimension by defining selection criteria. You can also filter and sort the selected elements. You avoid to write the code that selects all possible candidates and finds the ones among them that qualify for a given list of criteria.

If you use 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, this information is required:

• 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 connection = OLAPCreateNamedConnection("");

	string errorMessage = "";
	int errorCode = 0;

	try
	{
		string dim = "Unit";
		
		// Print all elements with their attributes
		OLAPElementList a = on connection select all from dim;
		foreach(OLAPElement el in a)
		{
			WriteLine(el + "\t[\"English\"]=" + OLAPGetStringAttribute(connection, el, "English"));
		}
		WriteLine("");

		// Select leaves from the Unit dimension with the name not "C1100"
		// and English attribute either GENESIS Italy SA or not Dummy
		string value = "Dummy";
		string attrName = "English";
		OLAPElementList lst1 =
			on connection select leaves from dim
			where @()!="C1100" and (@(attrName)=="GENESIS Italy SA" or @("English")!=value);
		foreach(OLAPElement el1 in lst1)
		{
			WriteLine(el1);
		}
		WriteLine("");

		// Select parents from the Units dimension with the name "greater" than "E000"
		OLAPElementList lst2 =
			on connection select parents from dim
			where @()>="E000";
		foreach(OLAPElement el1 in lst2)
		{
			WriteLine(el1);
		}
	}
	catch (errorMessage, errorCode)
	{
		WriteLine("Exception: " + errorMessage);
	}

	OLAPDisconnect(connection);

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 any expression that returns the string type. Specify the function in the left side of a predicate, where clause. It expects either a string parameter, a 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.