Question
· Feb 1, 2021

KPI idiosyncrasies, double first column, formatting text as number, formatting dates as numbers ...

In addition to charts on a dashboard we have a request to have a crosstab which would be populated by some entries queried out of an error log.  It's basically a detail query with mostly text data.  I have tried making a pivot widget for this using both a cube and kpi source, and while succeeding with the KPI, it leaves a lot to be desired. 

Firstly, the cube is a no go.  As far as I can tell the MDX subsystem in IRIS does not handle empty cells efficiently like other MDX systems such as Oracle's and Microsoft's.  For example, for the error log I am trying to display, it's a table that has just 16 rows and 6 columns.  The columns I turn into dimensions.  If I crossjoin more than 3 of the columns, I will timeout/freeze.  In analyzer it shows how many rows are being processed and in this instance it goes to thousands and thousands despite only 16 rows of source data!  I have tried using NON EMPTY and nonemptycrossjoins in an MDX query window and NON EMPTY and nonemptycrossjoin do not confer any additional performance benefit.  It appears the cube will attempt to compute every cell.

So I tried using KPI and SQL.  However KPIs have the following idiosyncrasies:

- The first column of the KPI is duplicated on your pivot.  In my case I want the internal cache "ID" to be the leftmost column and the pivot is rendered with ID in the grey leftmost "title column" and then in the column right next to it.  How do we prevent double columns for the 1st column?

- Formatting columns appear to be automatic and it does not matter what datatype the SQL returns.  For example I have a "member ID" stored as a string that the KPI renders on the pivot as a number with commas.  I found if I append a space at the end of the member ID in my SQL query, I can force the KPI to render it as a string.  But I have found no way to format these columns directly in the KPI code.

- Dates are rendered in their HOROLOG format, not in a readable date format.  I worked around by returning the date formatted as a string with delimiter characters.

So questions are:

1. Is it possible to control KPI column formats in code?  I see nothing in the documentation about any extra attributes of the column properties.

2. Taking a step back, if you do want to add simple detail query results to a dashboard like I am trying to do, what is the best way to do that?

Thanks for any help!

Product version: IRIS 2020.1
Discussion (3)1
Log in or sign up to continue

Hi Lee.

"For example, for the error log I am trying to display, it's a table that has just 16 rows and 6 columns. The columns I turn into dimensions. If I crossjoin more than 3 of the columns, I will timeout/freeze."

Can you provide reproducible case? It would be interesting to look into this. Not that crossjoining of three dimensions is a best practice, but it should work quickly for 16 rows.

Now to your questions.

  • How do we prevent double columns for the 1st column?

Go to Widgets -> [your widget] -> Data Properties.

Define properties for your columns.

On the ID property put checkbox "hidden"

  • How to prevent putting commas in the numbers:

Put "#" in the format field [0]

*How to format date to be returned in external format.

As far as I know, you should do this in the SQL query itself.

[0] https://docs.intersystems.com/iris20203/csp/docbook/Doc.View.cls?KEY=GIK...

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!