go to post Lee Cascio · Feb 3, 2021 Hi Alexander, thanks for the help! Yes, crossjoining too many dimensions like that is generally a terrible practice if it is not a small dataset. This is more a hack attempt to allow a mostly textual matrix (member names) to appear on the dash along with charts that retrieve from the cube. I was hoping to use the cube to populate this matrix/pivot so that the filters that control the charts also control the matrix. But it appears the best way it to use an SQL based KPI to retrieve from the underlying cube class, with the drawback that custom filtering has to be coded in the %OnGetSQL of the KPI with the additional challenge to mix MDX filters with an SQL query. For the dashboard, in my case, the HIDDEN = ON appears to not do anything (column remains) and HIDDEN = OFF strangely only shows that column, it makes all the other columns disappear. Maybe this is a bug with the 2020.1 version. But this helps me understand what the data properties are supposed to do. Since they didn't work right I wasn't too sure when I first checked them out. I didn't realize you can format columns from here. This helped so marking as answer. Thanks!
go to post Lee Cascio · Feb 3, 2021 HEAD returns a set, even if that set only has one member in it. You will not be able to use the result of HEAD as an argument to a function that takes a member argument. Some MDX implementations use some sort of iterator function that allows selecting the nth member of a set (e.g. Microsoft uses an "item()" function). It sounds like there may not be something like that in IRIS MDX.
go to post Lee Cascio · Feb 3, 2021 that returns properties of a member. I need to return a specific member from a set.
go to post Lee Cascio · Feb 1, 2021 Here is an example of the double 1st column. How do you avoid this on an SQL based KPI?
go to post Lee Cascio · Jan 6, 2021 Someone has to have a quick answer for this one. I need to know if this is "by design" behavior or if it might be a bug or problem with the install. Can member functions be used in a member range specification that is used to define a set?
go to post Lee Cascio · Jan 6, 2021 Hi Dan, I think this is two issues. One may be a bug with the inconsistent NOW behavior. The other issue may be user error. I did notice that if I was careful to make sure there was a member that should be referenced by NOW, I could clear up the error in those cases. For example if the query is referencing "yesterday" but I had not updated my dev cube in 24 hrs (and had no yesterday data) it would error. The error would go away if I updated my dataset and cube to have a yesterday. One of the key takeaways is to understand what is "populated" into the IRIS data dimensions. The date dimensions are more auto-pilot than other MDX systems, which is really nice and makes creating cubes fast.
go to post Lee Cascio · Jan 5, 2021 It's not an option with my client at this point to upgrade their system. Is there any version documentation that elaborates this issue?
go to post Lee Cascio · Jan 4, 2021 I was able to work around the issue by using LAG functions. However there is one very strange caveat. Any attempt to run a query with NOW in it that has a function off of the NOW function (e.g. ".&[NOW].LAG(1)") will fail with the same cryptic error as attempting the relative NOW functions such as ".&[NOW-1]". However, if you first run the query with hardcoded values instead of NOW, it will execute. THEN if you change the hard coded value to "NOW" and execute it, forever forward, it will work. It's inexplicable but I got it to work that way. So with the below example, I would get an error until I ran the query with the commented out hardcoding first, then that did something (perhaps in the cube cache???) that now allows me to run the query without error.
go to post Lee Cascio · Jan 4, 2021 I forgot the ampersand in the example with NOW-1 but it results in the same error. It looks to me like the the interpreter is not expecting the "-1" syntax.