· Jul 18, 2018

Filtering the cube

Hi community, 

I'm in a proyect that I have to load data that cames from 3 different sources and one of these sorces is a csv file that contain the name of a switch and the name of a port. My cube have also this information, but I have to show only the values that match with this information.  I'm trying to filter the cube through this method below, the problem was  the result of the method is not only a value, it is an array with many values ​​and I must use the IN operator and not this "&" symbol. I wanted to know if it's possible or not?

Result of : ‘acc23-mqui2.redip.clGi1/17’,’acc01-mqui2.redip.clGi1/17’,’acc01-mqui2.redip.clGi1/39’,’acc01-mqui2.redip.clGi1/39’,’acc22-mqui2.redip.clGi1/43’.

Another option is to user the Restriction inside the cube, in Architect module. (I have a dimension called “DatoSplunk” that brings these array. )


ClassMethod %OnGetFilterSpec() As %String [ Final ]
               Set tEquipo = ##class(Teleducto.DS.Utils).BuscaEquipoPuerta()
               if tEquipo '= ""
                              Set tFilter = "[EquipoPuerta].[H1].[EquipoPuerta].&["_tEquipo_"]"
                              set tFilter = ""

               Quit tFilter


Thanks all.

Discussion (6)1
Log in or sign up to continue

Hi, Jaqueline!

Have you tried Build Restriction field of the cube?

Build Restriction is in the properties of the cube.

Put there SQL Expression which goes after WHERE clause. It will filter the facts which will be included to your cube. E.g. if you base your cube on Samples.Person you can set the Cube Build Restriction as:


to limit the facts of the cubes to only records with 'John' in Name property.


Hi Evgeny,

Definitely it's possible to use the operator IN inside the build restricction. The problem is that I don'y have a fixed value, the values of this condition cames from a dimension called "DatoSplunk" that cames from a method that brings the string. This values it will be always changing because it's another class and the data cames from a csv file. Also, Deepsee have a restriction about the size inside the operator IN. 

Thanks for your answer

Hi Jaqueline,

You may also want to consider implementing %OnProcessFact (documentation link). This will allow you to run some code to determine if the record should be included or not. This can potentially be easier to implement some advanced logic rather than adding a build restriction. For many people, using the Build Restriction is enough though. As Evgeny says, this will add a WHERE clause to the SQL that is used to get the source records during a cube build.