Database Reference
In-Depth Information
Figure 10.5
Sparse relationships of products to channel to entity.
Figure 10.6
Combo edits lookup table.
For situations where a combination of mEtArEAD and AnDs in Essbase filter secu-
rity cannot be applied, such as in Essbase databases generated by hyperion Planning,
a code-based solution is necessary. one solution is a combo edits lookup or a set of valid
data-dependent lists that drive the contents of dropdown selections to assure that only
valid selections may be made. For the sake of simplicity, we will use a simple spreadsheet
lookup. A combo edits lookup is simply a table that lists all the valid combinations of
several sparsely related dimensions. In this case, our sparsely related dimensions are
Segment, Channel, and Entity. given your understanding of a spreadsheet lookup after
this exercise, you should easily make the intuitive leap to using an SQL table for the
lookup.
A lookup table for this application may look something like what is shown in
Figure 10.6.
The following Excel vBA macro can be used to generate the lookup shown above.
Public Sub genComboEditsLookup()
Dim cellCount As Integer
Dim cellValue As String
Dim cell As Range
Dim arrValidEnt() As String
