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!