Logi OLAP

Logi Info includes an OLAP-related family of elements, including the OLAP Grid super-element, that allows users to "slice and dice" OLAP cube data at runtime. This topic provides instructions for using these elements.


Logi OLAP has been deprecated; the related elements are still supported, for now, and will work but they're no longer available in Logi Studio.

Information about connecting to data is available in Connect to MS SQL Server Analysis Services.

About Logi OLAP

On-Line Analytical Processing (OLAP) is a technology for quickly providing answers to analytical queries that are dimensional in nature and can be viewed from multiple perspectives. Typical applications of OLAP include analysis for sales, marketing, business process management, financial reporting, and similar areas. Some organizations deal with massive amounts of transactional data and, in order to optimize their data for analysis, keep pre-aggregated data in multi-dimensional OLAP "cubes" to get faster answers to critical business questions.

Logi Analytics' zero-footprint OLAP elements make using OLAP analysis easy, by allowing your users to quickly make sense of corporate data so they can get immediate answers to their "why" questions.

Logi Info's OLAP elements provide front-end tools that can be used to query, analyze, and display data from OLAP cubes created with Microsoft SQL Server Analysis Services (SSAS).

 Logi OLAP only works with Microsoft SQL Server Analysis Services; it does not support other data cubes.

The Logi OLAP Elements

Logi Info's family of OLAP-related elements provides both an OLAP Grid super-element, a tool with a built-in user interface that allows users to filter, analyze, and visualize data, and an OLAP Table element similar to a standard Data Table. The following examples focus on the OLAP Grid; the OLAP Table is discussed in a later section.
 

An example of the OLAP Grid in use is shown above. The buttons across the top show and hide feature panels, including: Dimensions, Measures, Calculated Measures, Filter, Chart, and Heatmap. By default, the OLAP Grid includes all of the buttons shown, but it can be configured, or restricted by security, to include fewer buttons and panels, if desired.

At runtime, users can:

  • Select Dimensions for the left and top axes
  • Select Measures
  • Select Filters for slicing the data
  • Drill-down on Dimension members
  • Create charts
  • Drill-through to underlying data

With all these possibilities, it may be important for the user to be able to save these settings between sessions. Info developers can create a Process Task that uses Procedure.SaveOlapGrid to do this. A saved OLAP Grid can be reloaded with the rdOgLoadSaved parameter, for example: rdOgLoadSaved = myFilename.xml

Users' settings are automatically maintained during the session but can be cleared by calling the OLAP Grid element's report with the rdOgReset parameter, for example: rdOgReset = True

The example shown above uses the standard Signal theme to style the grid. Here are some of the other standard themes in action:
 

As you can see, the user interface looks quite different depending on the theme that's applied by the developer.

It is very important that the value of the ID attribute for the OLAP Grid element be unique throughout the entire application. This is because Logi Info saves the last state of the OLAP Grid in a user's session information based on the grid's ID. Consequently, if two different report definitions contain an OLAP Grid with the same ID value, the application will display the same grid for both reports.

DevNet includes an OLAP Grid sample application that demonstrates the material discussed in this topic.
 

Caveats and Limitations

Chart Canvas charts are used by default by the OLAP Grid in all new applications. Older Logi applications that are upgraded to v12 will use the classic static charts for their OLAP Grids. To force upgraded apps to use Chart Canvas charts, add the constant rdFavorChartCanvas = True to your _Settings definition.

 The maximum number of rows that can be displayed in the OLAP Grid's data table is 2,000.

 We do not recommend that you use any Logi OLAP elements within an embedded sub-report.

Connecting to Data

These are the basic steps for creating an application using an OLAP Grid and connecting to the data:

  1. In Logi Studio, create a new application as usual and register it with IIS.

     

  2.  

  1. In the _Settings definition, add a Connection.OLAP element, as shown above, and set its attributes. The ADOMD Connection String attribute value can be a bit more complicated than the usual database connection string and there are other data provider requirements as well. For more information, see Connect to MS SQL Server Analysis Services.

Creating a Basic OLAP Grid

Now that you have a connection to Analysis Services configured, you can add the elements that comprise your OLAP Grid:



In your report definition, add an OLAP Grid element beneath the Body element, as shown above.

The OLAP Grid attributes are:
 

Attribute

Description

ID

(Required) Specifies an element ID, unique with the definition.

Batch Selection

Specifies whether UI changes made by the user will be applied immediately (False) or only when the user clicks an "OK" button (True). Deferring until the button is clicked may speed up the user experience by reducing the number of retrieval requests sent to the server.

Caption

Specifies a title that will appear above the grid.

Class

Specifies Cascading Style Sheet classes to be used by the element. Classes will also be used by all child elements that don't have their own classes set. A class set here may be overridden by a built-in theme.

Drilldown All

Specifies if each dimension in the table will show an extra "Drilldown All" button (True). Clicking this button causes a drilldown on all items below the dimension that have the "+" drilldown icon. The default value is False.

Hide Excel Export

Specifies if the Export: Excel button will be hidden from view. The default value is False.

Level Indent

Specifies the amount of indentation, in pixels, used when displaying levels of a hierarchy in a table. The default is 2 pixels.

OLAP Cell Colors

Specifies whether or not to use foreground and background colors, defined in SSAS, for Calculated Members. The default value is False.

Pick Member Properties

Specifies whether the user will be able to pick Member Properties to be displayed in columns next to the Dimension column. Member Properties are selected by clicking an icon shown next to the Dimension name in the table. Selected Member Property columns only appear when there is at least one member that has the property and Member Properties can only be selected for Dimensions added to the left side of the table. The default value is False.

Saved OLAP Grid Folder

OLAP Grid configuration options selected by the user at runtime can be saved for reuse in a file, using the Procedure.Save Olap Grid element. Saved options can be reloaded by passing the "saved" filename in the rdOgLoadSaved request parameter. This attribute specifies the fully-qualified file system path to the folder, beneath the application root folder, that contains the saved files.

Function tokens may be used to facilitate this, for example:

@Function.AppPhysicalPath~\SavedOlapGrids

Security Right ID

When specified, access to this element can be controlled with Logi Security. Specify the ID of a Right defined in the application's _Settings Security element. Only users with matching rights will be able to see the element.

Show Dimension Attributes

Specifies whether the table will show Dimension Attributes in addition to Dimensions and Dimension Hierarchies. Set to True to also show Dimension Attributes. The default value is False.

Template Modifier File

Specifies the name, with file extension, of a Template Modifier Files to be applied to the OLAP Grid at runtime. This is an XML file that can be used to change the underlying template used to create the OLAP Grid. Template Modifier files can be in any folder accessible to the application; if a folder isn't specified, the _SupportFiles folder is assumed.

Width

Specifies the width of the displayed OLAP Grid.

Width Scale

Specifies the width units of the Width attribute value: either px for pixels or % for percent.

A minimal configuration, like the one shown in the example above is all that's needed to display a usable grid. Once the OLAP Grid element is configured, you're ready to add its child elements:


     

Beneath the OLAP Grid element, add a DataLayer.MDX element and set its attributes as shown above. The Connection ID attribute value should be the ID of the Connection.OLAP element you configured at the start of this process. Note that the Attribute Panel identifies the element as a DataLayer.OlapMdx element.


Beneath the datalayer, add an MDX Query element and set its attributes as shown above.

Your OLAP Grid is now functional. Click the Preview tab at the bottom of the Workspace to view it.

Other OLAP Grid-related Elements

The section describes the other elements available for use with the OLAP Grid.
 

MDX Query Child Elements

In the previous section, we saw that the MDX Query element was used to automatically build the OLAP MDX query. These child elements can be used with it to customize the query:
 

Element Description

Mdx Axis Dimension

Specifies the Dimensions that will be shown across the top or left side. Because the OLAP Grid allows user selection of Dimensions, only "default" Dimensions need to be specified.

There must be at least one Dimension for the left axis to see any OLAP results.

You may specify a Hierarchy in the DimensionName attribute. For example: [MyDimension].[MyHierarchy]

You may have any number of Mdx Axis Dimension elements for either Axis.

Mdx Drill-Down
(child of Mdx Axis Dimension)

Specifies a single member of the dimension to be drilled into/ expanded. Multiple MdxDrilldown elements may be used. The Value attribute must be a member of the parent element's dimension.

Separate each level with the period character. When a member has spaces in the name, it must be enclosed in brackets.

Begin the Value with the dimension name. Example for the first level of the Customers dimension: [Customers].[All Customers]

When there are multiple dimensions on a single axis, the members of each axis must be separated by the asterisk character. Example with Customers:
[Customers].[All Customers].[USA].[CA]*[Education Level].[All Education Level].[Graduate Degree]*[Gender].[All Gender].[M]

Specify a Value of AllMembers to expand all members of the dimension.

Mdx Drill-Up
(child of Mdx Axis Dimension)

Collapses a Dimension member that has been drilled-down. This may be used to undo drill-downs caused by an Mdx Drill-Down of AllMembers.

Mdx Calculated Measure

Adds an additional Measure from an MDX formula. The MDX Formula basically follows script syntax and typically references other measures. For example:

VAL((Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales])

Add an Mdx Measure element to display the calculated Measure.

Mdx Filter Dimension

Filters or slices values out of the OLAP cell set. Each Dimension to be filtered should have an Mdx Filter Dimension element. Add Mdx Filter Value child elements for each member to be included or excluded for cell calculations.

Dimensions cannot be used in Mdx Filter Dimension and Mdx Axis Dimension elements at the same time.

Mdx Measure

Specifies the Measures that will be shown in the cells of an OLAP Grid. Multiple Mdx Measure elements may be used. The cube's default measures are shown when none are specified.

Cells are formatted according to the format specified in the cube definition. Localization is applied to these formats according to the client browser's language setting.

OLAP Grid Child Elements

The following elements are available for configuring the OLAP Grid user interface and behavior. Panel-related elements can be used to hide panels; without them all panels are displayed.
 

Element Description

OLAP Calculated Measure Panel

Specifies the configuration of the OLAP Grid's Calculated Measures panel. The Calculated Measures panel is not available when working with a XOLAP Cube.

OLAP Chart Panel

Specifies the configuration of the OLAP Grid's Chart panel.

OLAP Dimension Panel

Specifies the configuration of the OLAP Grid's Dimensions panel.

Allowed Dimension
(child of OLAP Dimension Panel)

Specifies the cube dimensions available in the OLAP Grid's Dimensions panel. Multiple elements may be used.

Use the Security Right ID attribute to set the available dimensions based on the user's roles.

If none of these elements are present, all cube dimensions will be available for selection.

OLAP Drillthrough

This element enables "drill-through" on cell values. It adds drill-through links that appear as the user hovers over each cell. When clicked, a new page is displayed with a basic data table listing the drill-through rows with all the columns used to build the cube.

The element is not available for calculated measures or when there are multiple dimensions on either the left or top axis.

The Caption attribute sets title of the drillthrough page.

The MaxRows attribute sets the maximum number of rows that will be returned shown in the drillthrough table. The default is 1000 rows.

Drillthrough is functional when connecting to Analysis Services 2005 and later. It is not functional for AS2000 or XOLAP Cubes.

OLAP Filter Panel

Specifies the configuration of the OLAP Grid's Filters panel.

Allowed Filter Dimensions
(child of OLAP Filter Panel)

Specifies the cube dimensions available in the OLAP Grid's Filters panel. Multiple elements may be used.

Use the Security Right ID attribute to set the available dimensions based on the user's roles.

If none of these elements are present, all cube dimensions will be available for filtering.

OLAP Heat Map Panel

Specifies the configuration of the OLAP Grid's Heat Map panel.

OLAP Measure Panel

Specifies the configuration of the OLAP Grid's Measures panel.

Allowed Measure
(child of OLAP Measure Panel)

Specifies the cube measures available in the OLAP Grid's Measures panel. Multiple elements may be used.

Use the Security Right ID attribute to set the available measures based on the user's roles.

If none of these elements are present, all cube measures will be available for selection.

OLAP Menu Panel

Specifies the configuration of the OLAP Grid's Menu panel.

OLAP Table Panel

Specifies the configuration of the OLAP Grid's Table panel.

Generated Plug-in Call

Specifies a Plug-in method to be run. The method is called after its parent element is parsed into its XML definition.


Logi OLAP has been deprecated; the related elements are still supported, for now, and will work but they're no longer available in Logi Studio.
 

Using the OLAP Grid

The following exercise will guide you through the steps of configuring the grid dynamically. The instructions and screen shots that follow assume you have made a connection to the AdventureWorks database but are applicable for any data source.
 

  1. Preview or browse your OLAP Grid application.
  2. Click the Dimensions button at the top to open the Dimensions panel, shown above. This is where users select dimensions for the left and top axes of the grid. In the Dimensions panel, select the data for Date Fiscal for the left axis and Product Categories for the top axis, as shown above. Depending on the grid's Batch Selection attribute setting, the changes will be applied, and the table updated, automatically, or you may have to click the Update Table button. You can click the Dimensions button again (or another button) to hide the Dimensions panel.
     

  1. Click the Measures button to display the Measures panel, shown above. Select the measures Sales Amount item, as shown above. Click the Update Table button, if necessary. Click the Measures button again to hide the panel.
     

    The resulting table should look something like the example shown above. Note these features:

    (1) Show or hide any empty members using this checkbox.
    (2) Click this icon to for Excel export, if the OLAP Grid element has been configured to display it.
    (3) Click this icon to swap the axes to view the data in a different relationship.
    (4) Click these member icons to drill-down or filter the data.
     

  1. Click the Calculated Measures button to open the Calculated Measures panel.
     

    In the Calculated Measures panel, shown above, enter "Combined Cost" in the Name text box (1). Measures can be selected in the "Insert a Measure" select list (2) and added to the formula by clicking "Insert". The measures are inserted into the Formula text box (3), and/or you can type them in directly, to create a formula. Click the Formula Help button to view available functions and valid syntax. Then choose the Display Format (4) which can be Number, Percent, or Currency. When your formula is complete, click the Add button (5).

    The calculated measure will appear at the bottom of the panel (6) and it will appear immediately as a new column in your table. Click the calculated measure name (6) to place it back into the controls for editing, and click the Replace... or Remove... button to update or delete it.

    Click the Calculated Measures button again to hide the panel.
     

  1. Now experiment with filtering the measures. Click the Filter button to open its panel, shown above, where you can filter your data by setting Include and Exclude parameters. For example, you can limit your view of sales data across regions of various departments to that which includes a fiscal 2008 shipping date by setting an Include Filter, as follows:

    Click the Ship Date.Fiscal measure (1) and, after a moment, the filter selector area will appear on the right. Click the "I" icon, for Include, adjacent to the FY 2008 item (2). This will add the filter into the "Selected Filters" area (3), which lets you see what filters are in effect and where you can also delete a filter. The table automatically updates to reflect the filters chosen. Note that there's also an "E" icon, for Exclude, in the filter selector area.

    Click the Filter button to hide the panel.
     
  1. Charts and Heatmaps, based on all of the current table data, are initially displayed just by clicking the Chart and Heatmap buttons to open their respective panels:



    Once they're displayed, charts can be customized by changing their Chart Style, and by using their re-sizing handles (circled above) which may not be visible until the mouse cursor hovers over them. A variety of chart styles (Stacked, Line, Spline, etc.) are available using the selection list and charts and heatmaps can be drilled down into through all grid layers.




    In addition, once the chart or heatmap has been displayed, additional small icons appear in each row and column in the table, as shown above; these allow you to pick which measure and dimension layer to chart.

That concludes this exercise in configuring an OLAP grid at runtime.

Using the OLAP Table

The OLAP Table element displays OLAP data in a data table, without any of the built-in user input controls for filtering and drilling down into the data found in the OLAP Grid. This element is useful if you just want to display data or you prefer to add your own user input elements.

Like the OLAP Grid, the OLAP Table gets its data from a DataLayer.MDX element, and an MDX Query element can be used with the datalayer to automatically generate the query.
 

Or, if you prefer, instead of using the MDX Query element, you can enter your own query in the datalayer's MDX Source attribute, as shown above. This attribute is only functional when used with an OLAP Table and is ignored when the datalayer is used with the OLAP Grid.

The OLAP Table elements has all of the attributes of a Data Table and also includes the Level Indent and OLAP Cell Colors attributes from the OLAP Grid.

There are no Data Table Column child elements needed when using the OLAP Table; it includes an "auto columns" functionality and automatically shows all columns in the datalayer.

Using OLAP Elements with Logi XOLAP

Logi XOLAP technology brings the analytical power of data cubes to your relational data and data from other diverse sources such as web services and files. XOLAP is similar to OLAP but doesn't rely on an OLAP data cube. The OLAP Grid and OLAP Table elements are able, however, to use data from a Logi XOLAP cube.

Logi Info includes a special super-element, the Dimension Grid, for use with XOLAP data, so why would you want to use an OLAP Grid instead? The Dimension Grid is designed to be less complicated to use and doesn't support some features, such as multi-dimension sorting and multiple dimensions on both axes, that are found in the OLAP Grid. So, for some use-cases, the OLAP Grid allows more complex analysis of the XOLAP data.

For more information, see Logi XOLAP and Dimension Grid.

Re-using the MDX Query

Many of Logi's other data visualization components are capable of "2-D" representation of OLAP cube data, using MDX queries. So it may be useful to be able to access the MDX query used in an OLAP Grid in one report for use with non-OLAP elements in another report.

Each time the OLAP Grid is manipulated during runtime, a session variable is updated to reflect the resulting MDX query. This session variable, rdOlapLastMdxQuery, is available to other reports, Logi or otherwise. In a Logi report, it would be accessed using the token @Session.rdOlapLastMdxQuery~.

Using Two-Dimensional Data

As mentioned above, it's possible to use two-dimensional data from SSAS cubes for use in standard data tables, charts and other visualizations. This is done using Connection.OLEDB and DataLayer.SQL with an MDX query statement in its Source attribute. Unlike your Connection.OLAP configuration, the connection string for Connection.OLEDB will need to include the User ID and Password needed to log into the database.

Logi Studio includes a tool, the MDX Query Builder, that can help you construct such a query for use with DataLayer.SQL. For more information, see The MDX Query Builder section in Using Logi 12 Studio.

Adding OLAP Grid Visualizations to Dashboards

An exciting level of interaction between the OLAP Grid and the Dashboard element is available. Users working with an OLAP Grid in one report, at runtime, can generate a table or chart and then, with one mouse click, add it as a new panel in an existing Dashboard in another report.

When properly configured, OLAP Grid tables and charts will display an Add To Dashboard icon, as shown above.

When the icon is clicked, the table or chart is added as a new panel in the Dashboard, as shown above. When the resizing control is used to resize a chart, the widths of the dashboard columns and other panels will adjust dynamically.

Behind the scenes, when the icon is clicked, the OLAP Grid writes the underlying XML for its chart into the Dashboard's configuration file (its "Save File"). In the Dashboard, the chart is inserted at the top of Column 1; if Dashboard tabs are being used, the insertion occurs in the currently active tab.

Just before panel insertion, the user will be prompted for the Panel Title and a description for display on the Configuration Page.

The new chart panel thereafter appears in the Dashboard Configuration Page, as shown above, just like any other panel, complete with a thumbnail image. The new panel can be removed from the visible dashboard panels and from the configuration page entirely, using the usual controls.

The user can insert multiple tables and charts into a Dashboard using this technique.

In the report definition, the developer makes this functionality available by adding a Custom Dashboard Panels element, shown above, beneath an OLAP Grid element. Its Dashboard Save File attribute value should be set to match the target dashboard's Save File attribute value.

 The Save File attribute values shown above have been shortened for visual clarity. However, a fully-qualified path and file name, with .xml extension, is required, so a realistic attribute value would be something like:

    @Function.AppPhysicalPath~\DashFiles\DashbdSave.xml

and the account the Logi application runs under (in Windows, ASPNET or NETWORK SERVICE) would be given Write permissions to the "DashFiles" folder. For experimentation purposes, you can use the rdDownload folder, for which appropriate permissions already exist (but its contents are periodically deleted, so don't use it for anything but experimentation).

Dashboard Save Files are often named using a token, such as @Function.UserName~, in order to create personalized dashboard configurations and, in that case, the Custom Dashboard Panel attribute would use the same token.

The Custom Dashboard Panels element has a new attribute, Add Caption, which sets the caption for the button used to save the data table or chart. If left blank, the caption is "Add to Dashboard".

For more information about Dashboards, see Logi Info Dashboard.

Customizing OLAP Grid Appearance

OLAP Grid appearance can be changed most easily by applying a theme to the report definition. Most of the screen shots in this document were taken with the Signal theme applied.

You can create your own custom theme, based on a standard theme, using the Theme Editor tool.
 

Changing Appearance Using Style Classes

OLAP Grid appearance can be also customized using style classes and Logi Studio provides the following standard style sheet for all OLAP Grids:

    <YourAppFolder>\rdTemplate\rdOlapGrid\rdOgStyle.css

Developers can override classes in this style sheet by copying them to their application style sheet and modifying them there.

  Do not make changes in the standard style sheet in the rdTemplate folder.
 

Changing Appearance Using Template Modifiers

The OLAP Grid element uses a "template file" to define certain element properties that are not otherwise available as attributes to the developer for modification. These include language- and culture-specific Caption attributes that you may want to change for locale-based reasons (or you may simply want to change the captions to better suit your application).

The element's Template Modifier File attribute identifies a custom XML file developers can create containing elements that will override the same elements in the template file.

For example, the OLAP Grid template file:

    <yourAppFolder>\rdTemplate\rdOlapGrid\rdOgTemplate.lgx

contains several Label elements. One of them has an ID of "lblShowEmpty"; this controls the caption that appears next to the "Show empty members" checkbox. This text can be modified by changing the Caption associated with that Label element.

For example, to change the Label text from its default to "View empty members", create your own XML file, identify it in the OLAP Grid element's Template Modifier File attribute, and add this code to it:

<TemplateModifier>
    <SetAttribute ID="lblShowEmpty" Caption="View empty members" />
</TemplateModifier>

You can set the attributes for any number of elements in this file; examine the rdOgTemplate.lgx file to learn the ID and Caption attributes available. The template modifier file can be in any folder accessible to the web application; if a fully-qualified file path is not provided in the Template Modifier File attribute value, then the application expects it to be in your project's _SupportFiles folder.

More detailed information about template modifier files can be found in Template Modifier Files.