MDX Query and visualizations on the dashboard
MDX query is language to retrieve data from OLAP databases, such is SQL Server Analysis Service.
Kyubit BI includes feature to quickly define MDX query that will return data from OLAP cube, render its
values numerically and visually within dashboard visualizations and finally export values and visualization to PDF file.
MDX Query structure and usage
A basic MDX query uses the SELECT statement to identify a data set that contains a subset of multidimensional data. The SELECT statement is composed of the following clauses:
WITH clause (optional):
Allows calculated members or named sets to be computed during the processing of the SELECT and WHERE clauses.
SELECT clause:
Defines the axes for the MDX query structure by identifying the dimension members to include on each axis. The number of axis dimensions of an MDX SELECT statement is also determined by the SELECT clause.
FROM clause:
Names the cube that is being queried, and determines which multidimensional data source will be used when extracting data to populate the result set of the MDX SELECT statement. The FROM clause (in an MDX query) can list only a single cube. Queries are restricted to a single data source or cube.
WHERE clause (optional):
Determines which dimension or member is used as a slicer dimension (the slicer usually refers to the axis formed by the WHERE clause). This restricts the extracting of data to a combination of dimension members. Any dimension that does not appear on an axis in the SELECT clause can be named on the slicer.
Construct new MDX query in 'Queries' section of Kyubit Business Intelligence application and click 'Run'
to test results. Select appropriate OLAP database data source that accepts MDX query language.
Once created MDX query is valid and returns results as expected, it could be saved and used on the dashboard visualizations.
MDX Query Options
Permissions could be added for other users and groups to share MDX query with your team and colleagues.
Add 'Read' or 'Read/Write' permissions to other users and collaborate on query design.
MDX query Impersonation gives option to execute MDX query in the context of another user.
Because OLAP database could have defined permissions which structures of the cube are visible for different users,
executing MDX query with 'Impersonation' could have significant impact on retrieved data.
If MDX query does not change its results significantly in time, it is reasonable to use Cache options of MDX Query.
By using 'Cache' options MDX query results will be stored in application memory, instead of querying OLAP data source each
time it is executed.
Export to PDF file results of the MDX query results as a single visualization results export or as a whole dashboard export.
Export to PDF file consists of dashboard visualization and table of values for a give MDX query.
Subscribe to dashboard with MDX query results and receive on email fresh Dashboard values report on scheduled time.