Filtering the cube

Question

Question

JAQUELINE KRIEGER · Jul 18, 2018

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_"]"
               }

               else
               {
                              set tFilter = ""
               }


               Quit tFilter
}

Thanks all.

Discussion (6)1

Log in or sign up to continue

Peter Steiwer · Jul 18, 2018

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.

0 0

score 0 · Answer 1 · 2018-07-18T15:54:14-04:00

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:

NAME='John'

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

HTH

score 0 · Answer 2 · 2018-07-18T15:55:57-04:00

And I think you can use IN operator in this Build Restriction expression - use everything which suitable to put in WHERE clause of a SELECT query.

score 0 · Answer 3 · 2018-07-20T17:06:50-04:00

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

score 0 · Answer 4 · 2018-07-23T03:17:27-04:00

Jaqueline!

I do not get the aim: do you want to filter facts BEFORE building the cube? Or you want to filter cube data on the fly via dimensions and members somehow?

If you are looking for "any this values" in filter expression you could consider to try with %OR

score 0 · Answer 5 · 2018-07-25T09:47:31-04:00

Evgeny,

The idea was to filter the cube by the values from a dimension, but now, I got a better solution, I created another cube and made the relation between them, because I'm taking the wrong cube to be my fact.

Thanks for your help.