Working with Large Levels in MS SQL Server 7.0 OLAP Services
Updated : July 19, 2001
On This Page
Introduction
Description of the Problems
Referring to Member Names
Listing User-defined Properties
Additional Implementation Notes
Conclusion
Finding More Information
Introduction
When a client connects to Microsoft® SQL Server™ 7.0 OLAP Services, the client retrieves the dimension members from the server. However, if the dimensions contain levels with members higher than a specified threshold amount, the members of these levels (referred to as large levels) are not loaded on the client at connection time. These large levels are not loaded because they are expensive in terms of load time, memory consumption, hashing, and so on.
In this document, client refers to the client component of OLAP Services, PivotTable® Service. OLAP Services architecture delegates many of the query processing roles to PivotTable Service. One of the responsibilities of PivotTable Service is to parse multidimensional expressions (MDX) queries.
Because certain dimension members may not be loaded onto the client side, complications may arise that are related to the parsing of member names that are not fully qualified. This document describes this problem and provides techniques for its resolution.
Note: The threshold for large levels presented in this document is set using the Large level defined as option (default value: 1,000) on the Environment tab of the Properties dialog box in the OLAP Manager.
Description of the Problems
OLAP Services guarantees transparency for all required operations, as long as you refer to the large member using the unique member name. In addition, OLAP Services allows you to work with member names that are not fully qualified. However, if the members of large dimension levels are not loaded to the client side, the parser fails to find the member described in the MDX query and raises an error.
The best way to avoid this situation is to always fully qualify the member name. However, in some cases, the application does not have enough information about the requested member to provide the full qualification through the entire hierarchy tree. For example, a user asks for a specific customer name but does not specify the rest of the information needed to fully qualify the name (country/region, state, and city).
The client application can find the member by using a name/caption restriction on the schema rowset. However, such a restriction automatically causes the client side to load all of the members of the large level into the client memory to perform the search. Loading a large level may be quite expensive in terms of memory consumption on the client side (an average of 70 to 100 bytes per member) as well as costly in terms of network traffic.
Problems are encountered with these activities:
Referring to member names
Without the unique name, how can members that are situated on a large level be referred to without loading the involved large level(s) on the client side?
Listing user-defined properties
How can we get the list of the user-defined properties defined at a given level (which may be large), without including a restriction that might load large levels?
Referring to Member Names
The first problem involves working with members without unique names in large levels.
Solution: Server-side Calculation
In OLAP Services, the OLAP server does not differentiate between large levels and levels that do not exceed the large level threshold; all levels are loaded, irrespective of the number of members they contain.
Therefore, the first step of the solution is to build a query that will be executed on the server. This is easy to do when using the OLE DB property ExecutionLocation, which specifies where the query is to be resolved. The possible values for this property are:
0 (default)
1 (automatic)
2 (on client)
3 (on server)
The execution location can be set both at the query level (for each query), or at the connection level (for each connection) as the default execution location for all queries by specifying the following in the connection string:
"Provider=MSOLAP; … ;Execution Location=3; …"
The second step is to defer the binding of the member. When a member is specified, the OLAP Services engine will try to find the object of the dimension tree that corresponds to the text specified by the user. This phase is known as binding. If, during binding, the member is not found, an error is raised. It is possible to defer the binding to the execution phase by forcing the reparsing. This is accomplished with the help of the StrToSet function. Instead of specifying the set as { <ambiguous members referrals> }, you should specify it as StrToSet ("{ <ambiguous members referrals> }").
The algorithm will involves these steps:
Set the execution location to server (value = 3).
Place the member on an axis containing the following:
StrToSet ("{ <ambiguous members referrals> }") …
- Reset the execution location to the previous value.
Implementation
This is an example of Microsoft Visual Basic® code using Microsoft ActiveX® Data Objects (Multidimensional) (ADO MD) that attempts to get the member unique name for an ambiguous specified member at a large level without loading the level. The example uses the FoodMart database, which is installed with OLAP Services. In order for this example to work, you must reference both Microsoft ActiveX Data Objects (Multi-dimensional) 1.0 Library and Microsoft ActiveX Data Objects 2.1 Library in your Visual Basic project.
Dim cn As New ADODB.Connection
Dim cs As New ADOMD.Cellset
Dim original_execution_location
' Connect to the server.
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Foodmart;"
Set cs.ActiveConnection = cn
' Do some work.
' ......................
' Set execution location to 3 (server).
original_execution_location = cn.Properties("Execution Location")
cn.Properties("Execution Location") = 3
' Execute query.
cs.Open "SELECT StrToSet(""{[Cristian Petculescu]}"") ON COLUMNS FROM [Sales]"
Debug.Print cs.Axes(0).Positions(0).Members(0).UniqueName
' Restore execution location property.
cn.Properties("Execution Location") = original_execution_location
' Do more work.
' ......................
' Done
cs.Close
cn.Close
This code prints the following lines representing the unique name:
[Customers].[All Customers].[USA].[WA].[Redmond].[Cristian Petculescu]
Listing User-defined Properties
The second problem involves retrieving the list of the user-defined properties defined at a given level without including a restriction that could load large levels.
Solution: Educated Schema Iteration
Because OLAP Services does not support the MDSCHEMA_PROPERTIES, a workaround must be found.
The solution is to iterate the schema rowset, from the top of the dimension tree until the desired level is reached, getting only the children of the previous selected parent as the restriction, and then to select the first child as the parent for the next level.
The algorithm involves these steps:
Get the name of the first member at the first level and set it as the current parent.
Repeat these steps until you reach the desired level:
Get the children of the current parent.
Set the first child as the current parent.
In the example later in this section, the user-defined properties are in the columns: 19, 20, …
If some of the levels until the target level are large levels, only a small portion out of them will be loaded (the children of the current parent, which are situated at the previous level).
Implementation
This is an example of Visual Basic code using ADO MD that attempts to get the user defined properties list a large level without loading the level. The example uses the FoodMart database, which is installed with OLAP Services. In order for this example to work, you must reference both Microsoft ActiveX Data Objects (Multi-dimensional) 1.0 Library and Microsoft ActiveX Data Objects 2.1 Library in your Visual Basic project.
Option Compare Text
Private Const MDTREEOP_SELF = 8
Private Const MDTREEOP_CHILDREN = 1
Private Const MDPROP_USERDEFINED0 = 19
' ......................
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' Connect to the server.
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Foodmart;"
' Get the first member at the first level and set it as the current parent.
Dim current_member_unique_name
Set rs = cn.OpenSchema(adSchemaMembers, _
Array(Empty, Empty, "Sales", "[Customers]", Empty, Empty, 0, Empty, Empty,
Empty, Empty, MDTREEOP_SELF))
rs.MoveFirst
current_member_unique_name = rs("MEMBER_UNIQUE_NAME").Value
' Get the children of the current parent and pick the next current parent
Do
rs.Close
Set rs = cn.OpenSchema(adSchemaMembers, _
Array(Empty, Empty, "Sales", Empty, Empty, Empty, Empty, Empty,
current_member_unique_name, Empty, Empty, MDTREEOP_CHILDREN))
rs.MoveFirst
current_member_unique_name = rs("MEMBER_UNIQUE_NAME").Value
Loop While rs("LEVEL_UNIQUE_NAME").Value <> "[Customers].[Name]"
' Print the user-defined property list.
Dim id As Integer
For id = MDPROP_USERDEFINED0 To rs.Fields.Count - 1
Debug.Print rs.Fields(id).Name
Next id
' Done
rs.Close
cn.Close
This code prints the following lines representing the user-defined properties for the level [Customers].[Name]:
Gender Marital Status Education Yearly Income
Additional Implementation Notes
This section lists additional information about the solutions presented earlier in the document.
Referring to Member Names
The algorithm described earlier will not work if the StrToSet function is put on the filter axis, for example:
SELECT Measures.Members ON COLUMNS FROM [Sales] WHERE(StrToSet("{[Cristian Petculescu]}").Item(0).Item(0))
The problem is that, no matter where the execution location is, the WHERE condition is completely executed at the beginning, even before the binding. Therefore, the WHERE condition must be executed on the client side. If such a situation arises, you must get the members' unique names (using the above method), and then rebuild the queries using these unique names.
OLAP Services guarantees transparency if the large level is loaded or if the member that you refer to is loaded. The expression <level_name>.members, applied to an unloaded large level, loads the level as well as all prior unloaded large levels. Some other MDX functions load chunks of the dimension tree as well (for example, the functions Children and Descendants).
This is not a method to resolve ambiguity. You should always qualify the level name as closely as possible. In the above example, the level name [ Redmond ].[Cristian Petculescu] is a more granular, unique qualification than simply [Cristian Petculescu] alone (in the eventuality that another person with the same name exists in another city.)
This method is not recommended for members at a nonlarge level or at a large level that has already been loaded in the memory of the client. This query will travel to the server, even if the large level is loaded on the client, and this becomes an expensive time factor. However, there is no easy way to know whether a large level is loaded.
Some MDX functions, if present in the query, can make the query nonremotable and will cause the queries to fail. When these MDX functions are used, the query will begin to execute on the client side and the incomplete specified members will not be found. The MDX functions that create potential failure in the above example are CreatePropertySet, CreateVirtualDimension, and AddCalculatedMembers (which are all referrals to session sets and session formulas) as well as nonglobal user-defined functions.
Listing User-defined Properties
Using a single restriction involving a fully qualified member at that level initially may seem to be a good solution, but it is not. OLAP Services first loads the level (if it was an unloaded large level) and then solves the restriction, unsuitable for a scalable solution.
Putting a "wrong" restriction (for example, a nonexistent member) gives an empty restriction, but no user-defined properties appear. This is a known bug.
Conclusion
Working with large levels can potentially be costly in terms of time and network load, but there are ways to retrieve information about members of large levels without loading the entire level onto the client component. This document explored two common problems and provided solutions and examples to help you address those problems.
Finding More Information
For more information, see OLE DB for OLAP Programmer's Reference, available at https://msdn2.microsoft.com/library/ms950404.aspx.
For more information about MDX, see Microsoft SQL Server Books Online.