Using Lookup Cubes to Extend Your Queries

In this part we will look at incorporating values from other cubes into your sets. Doing so allows you to reference a cell and use the value in the underlying cube to filter out certain members, match members etc.

For our example we will build a simple query to allow the user to type in a portion of a Product and have MDX filter to match those members that contain the value typed in. This can be very useful in many cases where you may have many items and need to show certain members that have a name containing a certain string. One example may be where you need to make an update to an account’s attributes and would typically need to scroll to find the account or access the set editor and filter there to return the relevant members.

Using the approach we will look at now, the user can simply type a value into filter within the PAW book and have the set filter immediately.

To build this functionality, we will need to look at a few components:
• User Preferences cube
• PAW book with a suitable widget and Products on rows
• Single cell widget to allow a filter value to be entered
• Product set incorporating the lookup cube and filter

Building the Solution

User Preferences

We are going to assume that each user that interacts with the text filter would want their filter to be separate from other users and that anything captured remains in the filter until they change it.

Our cube is then very simple to create. We need two dimensions:
• }Clients – Use the }Clients from the control objects as it contains all our users
• User Preferences – Add a string element called Product Filter

If we open our cube in the workbench or a book, we should see a cube that looks like the below image. Users are on the rows and Product Filter from User Preferences on the columns:

PAW Book – Cube View

We need a basic view added to a new PAW book as the basis for an analysis. I am using my Sales cube for this example. You could use an }ElementAttributes cube if we were looking to do some updates, mappings etc.

For now I have created a simple Sales view as follows:

What is important is that I want Product on rows as this will be the dimension that I want to filter. I could filter on other axes too but for now the example is based on filtering on rows.

PAW Book – Text Filter

For the text filter I just need a text box on the canvas linked to the Product Filter field and my user.

Typically I would suggest a single cell widget but a recent bug seems to have broken the assignment of sets to fields and rendering the values essentially static, always showing the user initially selected.

Instead we will simply add another cube view and format this for our needs.

Adding a Cube View

Add a view from the User Preferences cube to your canvas. For now it is likely to overlap the other view but we will fix this as we go.

Ensure that the }Clients dimension is on rows and the User Preferences is on columns with Product Filter only showing.

Creating the Current User Set

Open the set editor for }Clients then open the MDX and add the following code:

				
					STRTOMEMBER("[}Clients].[" + UserName + "]")

Once added, commit the code and check that the only value returned is your client ID/user name.

Save the set as Current User.

Essentially all the MDX code is doing is taking the string value of your client ID/user name and converting that to a member in the }Clients dimension. This allows us to have a dynamic set and the client ID/user name will always relate to the logged in user.

We need this behaviour as we want the Product Filter to always be the value typed in by us, against our user name.

Formatting the User Preferences cube viewer

The first thing to do is to link the }Clients dimension to the Current User set. You should be able to drop down on the dimension selector and choose the Current User set.

Next, widen the column width to a size that you think is wide enough to show the likely text someone would want to input to filter on.

You should have something that looks like this:

Essentially all we want is the field containing 123. We can hide everything else.

Collapse the overview and then hide the overview
Access the Visualization properties then hide the Row and Column headers

You should now have a view with only the field displaying your filter value e.g. 123

You can now resize the cube view widget and position it at the top of the canvas.

I added a text box to the left of our field with Product Filter: to give users an idea of what it is for. My book now looks as follows:

Product Set

The next step is the crux of the exercise and that is linking the Products to a set that uses our field to apply the filter.

Building the Set

I am going to assume that we want to look at all members as the basis for our filtering. You could start with a leaf level or other set, as your requirements may differ.

My initial MDX is thus:

				
					{[Product].[Product].MEMBERS}

I now want to filter the members returned based on the text in our User Preferences.

Before filtering, be sure that you understand what the results are likely to be. If I am searching based on part of the Product description but have the Member ID in my set, I am unlikely to get any results. I need to choose the Caption or Description, or another attribute based on what I am filtering on.

The next thing to consider is that we are doing a wildcard search looking for any Products containing our filter rather than starting with or ending with the filter value. We will thus need to enclose our filter string between two asterisks.

My starting MDX Code would look something like this:

				
					{TM1FILTERBYPATTERN(
{[Product].[Product].Members},
"*<filter>*",
"<alias/attribute>")}

If you have not seen it yet or are not familiar with the TM1FilterByPattern() function, note the third parameter which allows you to specify the alias or attribute you want to apply the filter to. This can be very useful in the case where we want to show the Products with the Caption alias but want to apply a filter to them based on an attribute like Brand.

In our example we will filter on the Caption alias and should be presented with a list of members filtered by the value from our User Preferences cube.

We can then adjust our MDX as follows:

				
					{TM1FILTERBYPATTERN(
{[Product].[Product].Members},
"*<filter>*",
"Caption")}

Next we need to incorporate the value from our User Preferences cube into the MDX so that we can filter on the value we entered into the filter.

To do this we can reference the cell in our User Preferences cube using the following syntax:

				
					[<cube].(<tuple>)

We could also use the LookupCube function but I find this more cumbersome and needs to have the whole MDX code string be on one line for it to work i.e. no white space.

Our cell reference is quite simple, we need to refer to the User Preferences cube and look at the Product Filter member where }Clients is our client ID/user name. We can reuse some of our MDX to do this:

				
					[User Preferences].( 
STRTOMEMBER("[}Clients].[" + UserName + "]"),
	[User Preferences].[Product Filter]
)

Don’t get confused with my usage of User Preferences as both the cube name and the dimension. I could have used User Preference Measures to clearly distinguish between the two.

Now that we have the filter value reference, let’s incorporate it into our MDX with the TM1FilterBy Pattern:

				
					{TM1FILTERBYPATTERN(
{[Product].[Product].Members},
"*" + [User Preferences].( 
STRTOMEMBER("[}Clients].[" + UserName + "]"),
		[User Preferences].[Product Filter]
  ) + "*",
"Caption")}

Note that on either side of our filter string I added an asterisk to ensure that a contains filter is applied.

Applying the Set

Open the set editor for the Product dimension and then open the MDX panel.

Copy and paste the MDX from above, or your version of it into the MDX code area and then click Commit to execute it.

Assuming you have a filter string that matches one or more members, you should see a result. If your filter string was left blank you should see all members.

Save the Set

After confirming that the set is working and producing the correct results, save it as a public set with a name like Product filtered based on User Preferences.

Once saved, click on Apply to apply the set to your cube view.

I updated my filter from having the value of 123 to something more relevant like Cardio. My view now looks like this:

Testing the Solution

To test the solution, all we need to do is change the value from Cardio to something else or remove the text to return all members.

When I enter in the text cure and press enter, my view updates to show me the following Products:

By adding an image and an action button I can add a bit more visual appeal to the above:

Depending on your use case, you may want to simply use a magnifying glass to indicate that the field is a search box but for this exercise I have gone with the text and filter icon.

Summary

Lookup cubes can be incorporated into MDX for all sorts of reasons, one may be to allow the user to capture a value and then use that value to filter on Products.

You could similarly use this technique to exclude items and could use InStr() to take a string of items and based on whether a member is part of that string, include or exclude it.

You may also want to incorporate User Preferences into your models to allow users to select their own default values e.g. Current Period. Remember that the User Preferences cube is simply another cube and you could connect picklists to members to give users a dropdown and select capability.

Another benefit of linking sets to User Preferences is that where users are selecting their own periods, entities etc., these would remain constant across any widgets and books where the logic has been incorporated. This is different to PAW synchronization as the selected values are typically shared in the book only.

I find implementing this User Preferences approach particularly useful in mapping sheets where users may need to find a previously mapped member and update an associated field. The set editor will work, but this is just faster for users.

Maybe one day we will get the ability to simply filter the set natively in the PAW cube viewer but until then, I trust that this has given you a tool to add value for yourself and your users.

In the next part, we will extend the use of Lookup Cubes and apply this to joining two or more cubes into a single cube view.

A summary of the MDX view related keywords used up to now:

Keyword	Description
Select	Specifies that you are looking to retrieve data from the cube
Non Empty	Removes all empty tuples from the specified set combinations on each axis. NON EMPTY can be used with both SELECT and ON clauses to filter axes or sets, not just “set combinations.”
On	Allows you to specify the relevant axis
From	Specifies the underlying cube to retrieve the data from
Where	Filters the query or view based on the specified tuple. This is an oversimplification and may require additional reading as it behaves more like a global filter.
With	Tells the MDX to define a temporary calculated member or measure which lasts for the duration of the MDX query. It can also be used to define sets. WITH, MEMBER and AS are typically found together when working with calculated members.
Member	This defines the context and name of the calculated member as part of the WITH clause. Multiple Member statements can be added to the MDX to create additional calculated members.
As	Just a declarative almost like saying Let var = x and tells MDX to assign the calculation to the member
Solve_Order	Where queries are complex and there are dependencies between calculated members, you will need to consider using the solve order to prevent sequencing or logical errors, ensuring correct evaluation.
Format_String	Allows the calculated member to be formatted for presentation to the user. These could be numerical formats similar to what you have used in Excel, currency, dates, and conditional formatting to show formats for positive, negative and zero values.
Sum	Calculates the total of a numeric expression evaluated over a set.
Aggregate	Aggregates a set of tuples by applying the appropriate aggregation function, like sum or count, based on the context.
Min	Finds the minimum value of a numeric expression evaluated over a set.
Max	Finds the maximum value of a numeric expression evaluated over a set.
Stdev	Computes the standard deviation of a numeric expression over a set, measuring the amount of variation or dispersion of the set.
Var	Calculates the variance of a numeric expression evaluated over a set, indicating how spread out the numbers are.
IIF()	The Immediate IF statement allows us to evaluate an expression for a certain condition and the return a result if true and a different result if false.
Case, When, Else, End	The Case statement would be used where you expect multiple outcomes or need to extend the conditions beyond true and false. The first WHEN statement that returns true is returned as the result. An ELSE can be used for a catch-all scenario or default result.
.CurrentMember	Allows us to address the current member the view is dealing with from the context area or another axis.
.Properties(“<propertyname>”<, TYPED>)	Allows us to retrieve a property value which may be an intrinsic like the Element_Level or custom like an attribute that a modeller added. Using TYPED ensures that a data type relating to the underlying definition is returned.
Instr()	Returns the position of a substring within a string, similar to TI’s Scan()
Len()	Returns the length of a string
Left()	Returns the n number of characters from the start of a string
Right()	Returns the n number of characters from the end of a string
LCase()	Returns the lower case of a string
UCase()	Return the upper case of a string
ParallelPeriod()	Returns a member from a prior period in the same relative position within a parallel period e.g. same month, last year.
LastPeriods()	Returns a set of members starting from the specified member and going backwards through the hierarchy by a specified number of periods. If given a negative number, it moves forward through the hierarchy.
PeriodsToDate()	Returns a set of periods from the beginning of a specified level to the specified member. Typically used with year-to-date views as YTD() is not supported by TM1.
OpeningPeriod()	Returns the first member of a specified level within a specified period or its ancestor e.g. January as the first month in our year.
ClosingPeriod()	Returns the last member of a specified level within a specified period or its ancestor e.g. December as the last month in our year.
Lag()	Returns the member that is a specified number of periods before the specified member in the same level. The lag can be positive to move backwards or negative to move forwards.
Lead()	Returns the member that is a specified number of periods after the current member in the same level. Similar to Lag(), you can switch signage to move backwards but better to use Lag() and Lead() as designed for better readability.
UserName	Returns a string containing the client ID of the logged in user
StrToMember()	Converts a text string to a member in the specified dimension
TM1FilterByPattern()	Allows you to implement a wildcard search against a set of members. Don’t forget the third parameter that can specify an alias or attribute t be used to search against.

By George Tonkin, Business Partner at MCi.