FacetValue Integer values

[Staccato70]

Using v10.0p_2015-11-20/EVAL Deployment in order to evaluate the CubeGrid

The FacetValue object appears to support FacetValue Ids that are of Integer type (there is a SetId method that takes an int argument). This leads to a couple questions...

1) Why isn't there a Constructor that takes an int Id and String Title such as there is for the String Id, String Title?
2) Why doesn't FacetValueMap also support Integer FacetValue Ids?

Having an Int FacetValue is important because the SQL generated by Load on Demand for the CubeGrid appears to automatically wildcard the Facet Values if they are of String type, but doesn't appear to do so when they are of Int type. Which leads to another question...Is it possible to force the Load on Demand to use an exact match for String Facet Values as opposed to wildcard? I've tried setting the AutoFecthTextMatchStyle to Exact, but that had no effect. I also tried using the ignoreTextMatchStyle="true" option on the fields in the DataSource (which are defined as Int), but that didn't work either.

[Isomorphic]

If you are using integer Ids, you should be able to pass them as Strings (eg "5") to facetValueMap.addMapping() and this will still work. But it's a fair point that there should be a dedicated API here instead, and that convenience constructors ought to exist for FacetValue as well - we'll add those.

However, we would recommend using descriptive string Ids instead of integers. It's much, much easier to troubleshoot that way.

As far as seeing wildcard match - this doesn't happen in the provided Advanced Cube sample - matches are exact, not wildcarded. We'd recommend first verifying that this is the case when you run the sample locally, in case you've got some kind of system-wide setting interfering with normal operation. Then, if you see the sample running as expected, you can revisit your own setup and try to figure out what's different.

[Staccato70]

Thank you for the quick reply.

In our case, using Integers is actually better for debugging because of the way our underlying tables are structured. The values represent indexes into a multi-dimensional array. Using a formula we convert those indexes into a number that points to a row in one of our tables.

I started with your Advanced Cube Sample for my code and modified it so that it would access our DB and get real data for our use case. What I am experiencing is that for any Facets that are in the Row or Column Facets, the generated SQL is Exact (as expected), but for any Facets that are "Fixed", then the generated SQL is wildcarding the value. The only difference that I can see between those that are fixed and those that are put into Row or Facet Columns is that the FacetValueMap used for setting the Fixed values does not take an Integer...only Strings so those values (FacetValueIds) are converted to String going into the Map.

Here is a snippet of a SQL where clause showing that some are different...(There are four Facets called RC1_OCCUR_NO, RC2_OCCUR_NO, RC3_OCCUR_NO, RC4_OCCUR_NO). in this case RC3 and RC4 were fixed while RC1 and RC2 were in the Row/Column Facet List.

and (((LOWER(rcs.RC3_OCCUR_NO) LIKE LOWER('%4%') ESCAPE'\' AND rcs.RC3_OCCUR_NO IS NOT NULL)
AND (LOWER(rcs.RC4_OCCUR_NO) LIKE LOWER('%1%') ESCAPE'\' AND rcs.RC4_OCCUR_NO IS NOT NULL)
AND ((rcs.RC2_OCCUR_NO = 4 AND rcs.RC2_OCCUR_NO IS NOT NULL)
OR (rcs.RC2_OCCUR_NO = 3 AND rcs.RC2_OCCUR_NO IS NOT NULL)
OR (rcs.RC2_OCCUR_NO = 2 AND rcs.RC2_OCCUR_NO IS NOT NULL)
OR (rcs.RC2_OCCUR_NO = 1 AND rcs.RC2_OCCUR_NO IS NOT NULL))
AND ((rcs.RC1_OCCUR_NO = 2 AND rcs.RC1_OCCUR_NO IS NOT NULL)
OR (rcs.RC1_OCCUR_NO = 1 AND rcs.RC1_OCCUR_NO IS NOT NULL))))

As you can see, some Facets are correctly using Exact number match while others are using Wildcard text match even though all of the Facets are defined the same.

My theory was that perhaps the CubeGrid is always calling FilterData instead of FetchData for Load on Demand. The documentation of FilterData on the ListGrid states that...

"This method behaves exactly like fetchData() except that textMatchStyle is automatically set to "substring" so that String-valued fields are matched by case-insensitive substring comparison"

Any thoughts or am I way off base on this theory?

[Isomorphic]

Sorry for the delayed response, this was assigned to someone who was actually out on vacation through a glitch in our scheduling system.

We agree, it looks like somehow textMatchStyle:"substring" is being applied to your fixedFacetValues. However, we can't reproduce this in the Advanced Cube sample by introducing fixed values. One way to test this: move Regions to columns, then fix Time to any value that isn't showing because it's collapsed, eg, a month in Q4.

We also see, from the code itself, that textMatchStyle is always forced to "exact" when requests are sent to the server.

So the question is how you are producing a different behavior:
1. if you look in the RPC tab of the Developer Console, what do you see for textMatchStyle for load on demand requests?
2. what do you see in the server log for textMatchStyle? (ideally, just share the complete server log for the request)

[Staccato70]

Hi I see that the TextMatchStyle is indeed "exact" as you state, however the generated Criteria (AdvancedCriteria) uses the "iContains" operator for the fixed facets. Please see attachments. serverLog.txt RPCRequest.txt

[Staccato70]

Hi Isomorphic. Any progress on this? Thank you in advance.

[Isomorphic]

The best way to proceed on this would be for you to show us how to reproduce this problem as we're not getting it to happen on our end.
Since you started with our AdvancedCube sample, we're guessing it shouldn't be too hard to share a simple test case we can experiment with on our end - ideally something we can run as posted with no modifications so we aren't tripped up by some un-shared application code or similar.
We'd need to see the runnable sample code and your dataSource definition. Probably no need for actual test data since presumably the issue will manifest when the fetch is issued (even if there's nothing to back it on the server).

Thanks
Isomorphic Software

[Staccato70]

Hi Isomorphic.

Please find the attached files that can be used to reproduce the problem. They include the following:
1) AdvancedCubeSample.java - Entry point File...Use this file to replace the file of the same name in your Advanced Cube Sample.
2) FourDimensionFacets.jave - Analog to the ProductRevenueFactes.java file that ships with the sample.
3) FourDimensions.ds.xml - Datasource file.

Running this code causes the AdvancedCriteria generated by the Load on Demand to incorrectly use "iContains" as its operator for Fixed Facet values even though they are defines as Integer type.

[Isomorphic]

We're pretty sure we see the problem. Where you setInitialCriteria, you use AdvancedCriteria, but the criteria you're supplying are in fact simple. Try using just a Criteria here; if that solves the issue for you, then we know the framework fix we have in mind is correct.

[Staccato70]

Using Criteria as opposed to AdvancedCriteria did indeed fix the issue in the sample case code above, but in my real life code it did NOT fix the issue.

I believe I know why...
The CubeGrid always requires at least one Row Facet and one Column Facet, but some of our data is only one dimension.
Accordingly, we have an extra "dummy" Facet that we are creating and adding as a Column Facet. (We do this even when our data has more than one dimension also so that all the real facets could be moved to Row Facets if the user desires...leaving only the "dummy" Facet as a Column Facet.)
Because of this "dummy" Facet the criteria generated is being sent on the request as an AdvancedCriteria even though the initialCriteria was a simple Criteria.

I have modified the code above to more accurately reflect our use case. Please see the attached code (I have included all three files even though only two (AdvancedCubeSample and fourDimensions.ds.xml) changed).

Would your proposed "framework fix" address the issue for this use case as well?

[Isomorphic]

Yes, our framework fix should address all the possible ways in which we would end up with the wrong operators as a result of AdvancedCriteria being used. Should be complete within a couple of days.

Just as a note for the future - this test case wasn't usable as is (no SQL schema, no table data) and we actually figured out what the bug was just from reading it (not running it). To quickest way to make a usable test case is typically to modify an existing sample to introduce elements of your application, until you can reproduce the problem. This sidesteps the problem of needing test data, means we've both already got a preconfigured environment where the sample runs, and gives you the opportunity to spot the exact code change that introduced the issue.

If you ever do need to provide a test case that can't realistically be done as a modification to a sample, using a clientOnly DataSource sidesteps all issues of needing your SQL schema and table data, and works to reproduce any client-side issue (as this was - wrong criteria appear in the request).

[Isomorphic]

We've made a change to address this issue. Please try the next nightly build, dated January 15.

Regards
Isomorphic Software

[Staccato70]

Thank you for your assistance and timely response. I will look for the Nightly Build after Jan 15.

Regarding your suggestion in Post #11 about not having provided a schema or test data, I was simply providing what you requested in post #7 in which you state "We'd need to see the runnable sample code and your dataSource definition. Probably no need for actual test data since presumably the issue will manifest when the fetch is issued (even if there's nothing to back it on the server)."

[Staccato70]

Ok....I updated to SmartClient Version: v10.1p_2016-01-15/EVAL Deployment (expires 2016.03.15_12.31.54) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)


The situation appears to have worsened...

1) The CubeGrid is now producing an error upon attempting to load on demand. I have attached the Request and the Response and the Log output.

2) I notice that the AdvancedCriteria is now using an "iEquals" operator for Fixed Facet values as opposed to an "iContains"....this is an improvement, but the SQL generated by the "iEquals" operator will still cause Oracle to be unable to use indexes because it is using the "lower" function on both sides of the equality comparison in order to be case insensitive. Given that the Facet values are specified as part of the Facet definition, shouldn't the default here be an "Equals" operator as opposed to "iEquals" (or at least have that as an option). The CubeGrid is intended to be used with large datasets...therefore the proper use of indexes is important as it I likely to have dramatic effects on performance.

Thank you,
Don

[Isomorphic]

We're going to make the textMatchStyle used be affected by cubeGrid.autoFetchTextMatchStyle, to allow you to set "exactCase" here (instead of the default of "exact"). That should allow you to use your indices.

As far as the error, what it looks like is that one of the Velocity expressions in your SQL Template is wrong. Earlier versions you posted tried to use $criteria.code, which never would have worked with AdvancedCriteria, whether using the old or new build. The correct syntax, assuming that the criterion applied to "CODE" might be nested, would be $advancedCriteria.CODE should work.

However, note also that your server log shows that you don't actually send any criteria for CODE to the server any more - you'll need to correct that too before any variation of the Velocity template will work.