TodoBI - Business Intelligence, Big Data, ML y AI TodoBI - Business Intelligence, Big Data, ML y AI

New features in STPivot (Open Source OLAP Viewer)

The new version of STPivot is now available and has added a number of new features (you can see it in action here) and how to implement and download from LinceBI:

1) Filters

o The filter system has been rewritten to show more contextualised information. With this new system, this is achieved:
- Avoid the appearance of members repeated by the Cartesian product.
- Avoid the appearance of members belonging to Role Playing dimensions without enough context to know which dimension use it belongs to.
- Summarize the information to the maximum in order to acquire a quick image of the filters applied.

o A new button has been added to the toolbar that allows to hide/show the information of the filters. By default, these will always appear hidden.

2) Hierarchize and sort members

o Prior to this new version, the tool always applied the Hierarchize function by default. This function reorders the members to be displayed according to the order defined by Mondrian, which limits the possibilities of displaying the members manually reordered by us.

As you can see, despite having reordered the members to be displayed in the dimension editor, in MDX the Hierarchize function is applied that forces the table elements to be displayed in the order defined by Mondrian.

To solve this situation, it has been added to the Break Hierarchy button the control over the presence of the Hierarchize function as long as a natural order has been established.

When activated, the tool removes the Hierarchize function from the MDX and changes the table to display with the basic Hierarchy Breaker layout.

3) Datepicker for time dimensions

o Server-side support has been provided for the generation of deadlines based on available members in the time dimension. To ensure maximum compatibility, the format type used for the implementation has been extended and now makes use of the one available for the DateTimeFormatter class

The structure needed to make it work requires assigning annotations in the Mondrian schema to at least one level of the dimension. If none of them have annotations, the DatePicker will not be shown. Also, if certain levels do not have formatting annotations, the system will try to infer the most correct date from the


<Annotation name="Format">format</Annotation> <Annotation name="FormatLocale">locale</Annotation> <Annotation name="FormatCase">case</Annotation> <Annotation name="FormatTrim">trim</Annotation>


In this structure it is necessary to define Format and, optionally, FormatLocale, FormatCase, FormatTrim (used for months for example):

Year: uuuu, yyyy, uu, yy
Quarter: 'QTR'Q, 'Q'Q
Month: MM, MMM, MMMM (For MMM and MMMM it is necessary to define the locale to ensure a correct stop)
Week: w
Day: dd
It will be enough to assign the code formed by two letters of the language: es, en, ca, pt, etc.
For those members who do not comply with the standard for language dates, this option allows you to define text formatting: upper, lower, capitalize.
To perform point trimming for those members who, according to the date standard, must contemplate it: true, false
For example, in English, December MMM is Dec:
FormatLocale: en
FormatCase: capitalize
FormatTrim: true
We can get this value modified by Dic to match our cube member.

4) MDX parameterization

o A first version of this functionality has been developed that will allow us to define parameters in the MDX query and to which we can later assign their value in a parameter menu. With this system, we will save ourselves from having to search in complex MDX for certain members or levels that we want to modify frequently by delegating their value to parameters.

The parameters must have the following format ${name}. These parameters and their chosen value can be saved in STPivot views.

5) Export

o Improvements in the table generation system to avoid mismatches with values conforming to many figures.

6) Minor stability and fault resolution corrections