Before watching this movie, it is recommended to read “InterSoft Java Analysis Services Tools” document first.
Movie is showing some of the capabilities of BINet Analytics web program:

– select a database
– browse database cubes / dimensions / levels
– how to interactive select report elements using drag from cube tree view to report axis containers (columns, rows, …)
– how to create runtime switchable item buttons
– how to define sort operation for particular measure in one of axis containers
– how to select drillthrough fact-table columns
– execute query “at once” (all data in one step on screen)
– how to select / change member properties shown on the report
– how to filter data using slicer axis
– how to browse dimension level members and PROMPT pseudo-member usage
– execution of parametrized query and program prompt for parameter substitution at execution time
– paged result and how to navigate thru pages
– execute query “by steps”
– forward / backward navigation thru query results (step-by-step execution) by clicking grid cells
– perform drillthrough operation by click on grid cell
– how to save query for later execution
– load and execute previously stored query

This is only smaller part of features from BINet Analytics repertoire, limited by movie duration.

Database used in this movie is Microsoft Adventure Works OLAP demo database. Program language selected is English (program is multi-language, English, German, Croatian, …). In reality, using our customer’s OLAP database with real data, demo movie should be much more reliable; however, we cannot publish our customer confidential data.

» Hide

Software functionality details – developer information

Program developer package consists of 3 Java packages, HttpOlapServiceClient, OlapXmlParser, QueryBuilder and OlapService program at SSAS server side.  Usage of packages is simple, trivial program sending data to SSAS and displaying result consists of a few code lines.

BINet Analytics is end-user WEB program combining features all of those packages into OLAP database browser, interactive query builder and query result presentation program.

Here follows description of the each component, without intention to explain “how to do”, but rather a brief explanation how it works and what it is capable for.

OlapService IIS program

OlapService is C# program running on IIS Windows server as relay between SSAS and remote Java client. There are two versions of OlapService available: one for IIS 6 or previous versions, and other for IIS 7 and all later versions. Both versions are able to communicate with any SSAS version starting from SSAS 2000 up to the most recent (currently SSAS 2014), and returning unified data regardless SSAS differences between versions. OlapService is supporting various user login modes, including user impersonalization and control over SSAS user roles. Configuration file with a set of tunable parameters is available to adjust OlapService to our needs.

HttpOlapServiceClient Java package

OlapService counterpart at client side is HttpOlapServiceClient Java package. HttpOlapServiceClient is client Java package responsible for sending requests to remote OlapService and returning resulting data. Among others, data send to server consist of query string and server action code. Action codes are dbinfo (database information), catalog (get Olap database metadata), members (Dimension level members or member children search and browsing), execute (execute MDX query multi-dimensional) and execflat (execute MDX query and get flat result). Transfer protocol used is http/https and xml-formatted data. Data are optimized and compressed to minimize data-transfer size and memory usage at the moment of conversion to DOM objects.

If used in servlet, there is an extra built-in query and traffic optimizer based on session synchronization between Java Web server and IIS server, handling result caching on client or server side and preventing unnecessary data transfer, as well as SSAS server repeated query execution.  This optimization is important for result paging described below.

Query result data are paged. Paging is depending on query type and page size defined in request. Client program is receiving single data page by request depending on selected page number. Data paging is of essential importance for acceptable response time in interactive program.

In some cases MDX query may return a huge amount of data. SSAS server response time is sum of two different values:  first one is query execution time (time elapsed from request to result / cell set return), and second value is cell access time for each cell in the resulting cell set. Recent value is depending on the quantity of cells we are reading (because cell set object is not detached from SSAS, SSAS action is required during the first cell access), and sometimes is this time much longer as query execution time itself.  So, it is obvious why result paging is very important.  After query is once executed, access to next page is done from cached cell set making response time shorter for query execution time.  Paging and smart caching feature is advantage of InterSoft package compared to the most of other alternatives available, preventing infinite wait or SSAS server blocking due to lack of memory.

HttpOlapServiceClient is also responsible for remote error handling, catching Microsoft C# / ADOMD exception in the case of error, and transferring it to client side and throwing there appropriate Java exception.

OlapXmlParser Java package

Data received by HttpOlapServiceClient (xml string) are used by OlapXmlParser as input data. OlapXmlParser is translating raw xml into hierarchized Java objects and array of objects. Object structure is depending on action code.  Object model is similar to already existing and widely known models: Microsoft ADOMD (catalog, cell sets etc.) or ADODB (recordset). For users not familiar with Microsoft object models, there is available a set of objects similar to JDBC structures (DriverManager, Connection, Statement, ResultSet). In addition, resulting data are also available as grid with formatted data organized into headers, rows and columns, ready for use in user-interface software.  Grid is overhead at the top of all other OlapXmlParser structures, and  Grid object user do not  need to have any knowledge how to  manipulate lower-level  objects (ADOMD Axes, Positions, Cells etc.).

OlapXmlParser is also doing drillthrough result post-processing. Raw drillthrough rows are not something we want to see on our report in the most of cases. Drillthrough post-process consists of 2 phases:  extracting only selected columns from the original data row (respecting given column order) into a new row, and finally compressing more rows into single row. Row compression is possible only if we predefine certain numerical columns as sumarizable fields (e.g. quantities, amount totals or similar). Rows are sorted and grouped by given column order, with exception of sumarizable columns, which are summarized for each group of equal rows. All equal rows (exactly the same data in the all non-sumarizable columns) are compressed into single row, and sumarizable column in the compressed row is containing sum of the all column values in removed rows.  Data prepared on such way are more useful for visualization in the grid than raw drillthrough result.

Processed drillthrough result is also available as a Grid object. Grid object is evaluated as a result of 0, 1, 2, or 3-Axis query (action execute, ADOMD Cellset result), as well as drillthrough result or any other execflat action (returning ADODB Recordset).

Regardless flat or multi-dimensional result, for both result types flat structures (ADODB recordset-like object and JDBC ResultSet-like object) are available, OlapXmlParser is doing successful multi-dimensional result conversion to flat form.

Package is taking care about memory usage, evaluating only objects requested by user and releasing them when they are not needed any more, what is important for Java programs running on WEB server in multi-user environment.

QueryBuilder Java package

QueryBuilder is tool helping us to construct MDX query. Simplified, package is using input data – dimension levels, members and measures, organized into Axes structures (columns, rows, pages, and filter – slicer pseudo-axis) to build MDX query string. In addition, there is drillthrough quasi-axis used for drillthrough columns selection. Number of axis to be used in query is choice of user.

MDX query made by QueryBuilder is crossjoin (cross product of level members in different dimensions) type, but query building process is not always simple. Analyzing given components, QueryBuilder is building elements, relations, structure and strategy of the MDX query.  For instance, builder is automatically generating calculated members or involving some other MDX function in order to solve MDX same-hierarchy-on-different-axis conflict.  This and similar build-in features allow user not to care about some MDX restrictions. However, QueryBuilder is involving some other rules and limitations that user must know when building query. QueryBuilder will reject all attempts to add item in illegal combination to some of the axes, preventing error at SSAS server side. There are also other situations when QueryBuilder is automatically adding extra elements on query axes: calculated member as total sum for members selected by user, calculated measures as percent values or differences, etc.

For each axis element (levels, members, measures) there is a variety of options available: level and member property visibility, different sort options, filtering by value of specified measure, etc.

Further, QueryBuilder is offering usage of member variables:  CURRENTYEAR for TimeYears level-type usage, USER for level predefined as set of usernames, and special variable PROMPT. If PROMPT variable appears in the generated query, QueryBuilder will throw special QueryParameterRequestEvent signalizing to program request for user input / interactive level member selection.

There is also a set of general, selectable options available:  non empty members, distinct members, member grouping and totals, etc. Non empty option is on by default, it is important for eliminating empty crossjoin combinations from the query result.

QueryBuilder is also evaluating drillthrough MDX statement based on chosen cell, respecting specified drillthrough columns.

Special QueryBuilder feature is possibility to execute generated query step-by-step.  This feature is drilldown-like action, but more powerful, extended over dimension limits. While drilldown is expanding data only to the child members of subordinated level for specified parent-level member (so, within levels of the same hierarchy only), step-by-step execution is doing this too, but also expanding data to the members of the next hierarchy or dimension level (as specified in the query) in crossjoin relation to the all selected members of previous hierarchies (or dimensions).

While drilldown is related to the parent-child relationship of a data as defined in the database dimension structure, step-by-step execution is related to “evaluated parent-child” relation between different dimension members established by query construction (order of the cross-joined dimensions on the row axis) and user choice (members clicked in the previous selection chain).  Step-by-step execution is especially powerful if combined with non-empty members query option, when next step members are filtered by measures used in the query (not null).

Except step forward (or “step into” selected member), there is also step back operation making possible to user go to the previous step and choose again next (different) member to be expanded.

Step-by-step query execution is powerful operation giving possibility to the user to have on the screen only selected portion of a data by interactive choice, as expected from analysis and decision making program. Step-by-step execution is a forward and backward walk through branches of query items, expanding selected node to subordinated members.

Execute steps are defined along row, next step data are defined with item order in row axis. Query must satisfy certain rules to be suitable for step-by-step execution (measures must be placed on column axis, some limitations in the usage of particular members on row, etc.).  Attempt to execute by step non-appropriate query is detected by QueryBuilder (QueryExecModeConflictException).

At last, but not least, QueryBuilder is also building default query title and selected members based subtitles (dynamically for each step or generated drillthrough statement on cell click).

Query builder is capable to build queries for all SSAS version, starting with SSAS 2000 up to most recent, and in some cases may produce different queries, respecting version differences.

BINet Analytics interactive program

BINet Analytics is WEB program running within internet browser context, written in Java script and using Sencha ExtJs tools. Server part of a program is Java servlet application running on Java WEB server (Tomcat or similar). Program is powered by QueryBuilder, but it is using all other previously described InterSoft Java packages as well.

Briefly, there are three main functionalities:

–          Interactive build MDX query

–          Execute query and display results (in ExtJs grid)

–          Save generated query into query database

BINet Analytics is not using standard pivot table with dicing and slicing to display cube content, it is rather building MDX query with crossjoin of user-selected dimensions in each of the axes offering QueryBuilder feature “execute by step” (grid cell click) to drill into segments of data (see previous chapter, “QueryBilder Java package”).  Not all QueryBuilder-generated queries are suitable for step by step execution. However, BINet Analytics can display result of any query, but without possibility to execute it “by step” in all cases.

Query building process is interactive; user is using drag / drop to grab levels, members, measures or drillthrough items available in the database tree view panel. Tree view is showing selected database cube structure, with dimensions, hierarchies and levels as expandable nodes.  Double click on level is opening member browse / search panel. Selected dimension levels, their members, measures or drillthrough items are moved and dropped into one of query component container panels:  filter (slicer), columns, rows, chapters and drillthrough items. For each item dropped into one of item containers, program is offering possibility to select extra options (properties visibility, sort, measure and member filtering, calculated measure selection) by right mouse click opening item dropdown menu.

Member browse panel, opened by double click on the tree view level, is paged. There is possibility to search members by name or property value. Members listed can be dragged and dropped to one of query component panels. At the bottom of member list, there are listed all available query variables (current year, user, prompt), which also could be dropped into query component panel. If query is containing prompt variable, same panel is opened at query execution time, in order to substitute parameter by user chosen member.

There is also possibility to mark some items as “selectable”. Selectable items are available to user as off/on state buttons on the button bar, giving him possibility to dynamically turn on / off query component items by his choice at query execution time.

There is a set of general query parameters (checkboxes) at main page, which are not related to particular query item. Most important are: show only non-empty members in the result (checked by default), and member grouping option, instruction to query builder to generate totals for the group of particular members of the same level.

For advanced users, there is also possibility to edit and rewrite generated MDX query string.

There are two different query execution buttons: execute query “by steps”, or execute “all at once”, showing all items specified in the query in the single resulting grid.

When “execute by step” button is clicked, only first query step is executed and result displayed in the grid. Next step execution is initiated by appropriate grid cell click, and so forward, until the last step (as defined in query). Clicking to previous step item grid cell, step back operation is done and previous grid content is shown again.

If there are items in drillthrough query component container, clicking on grid cell containing measure value, drillthrough query is send to server and returned result appears in the grid.

If number of grid rows exceeds page size, grid is paged, and we can navigate through pages using page navigation buttons provided below grid.

If chapter panel is used, generated query is 3-axis query, containing except rows and columns, an extra axis named Axis(2) or Page Axis (notice:  to avoid confusion with page numbers at grid bottom, page axis container caption is renamed to “Chapter”).  However, grid displayed on the screen is always two-dimensional, showing rows and columns only. But, in such case there is an extra button available: “Table of content” button. Clicking it, we can do switch from current chapter to some other, and selected chapter rows and columns appears in the grid on the screen.

We can store generated query into database, available for execution in the future.  Once stored, query could be reloaded, updated and saved again. Depending on granted user permission, user can build queries, or just execute queries assigned to him or his user-group.

» Hide