Select and load Essbase data
Once a connection to an Essbase database has been established, you can select data and load it into an app.
Once a connection has been created, it appears:
- In the list of Data connections in Add data, and under Data connections when you use the Data load editor
- In the list of databases under the Data tab in the Edit script dialog.
Selecting data from Essbase
| Properties | Description |
|---|---|
| Applications | Shows the applications in the Essbase service. |
| Databases | Shows the databases contained within the Essbase application. |
| Dimensions |
Shows the database dimensions. Multiple fields can be selected. The dimensions can be a hierarchy. |
| Measures |
Shows the database measures. Multiple fields can be selected. |
| Generate MDX query |
Shows the MDX query that is used to select data from the Essbase service. |
| Preview |
Shows a data preview based on your selections. The preview is available after the MDX query has been generated. The default number of rows displayed in the preview is 50 in Qlik Sense and 20 in QlikView. |
| Functions |
The QlikView Create Select Statement dialog shows the list of MDX functions that can be inserted into the MDX query. |
| Generate hierarchy for selected dimension | The QlikView Create Select Statement dialog provides an option to produce hierarchical structure for selected dimensions. When not enabled, the dimensions are flat. |
MDX queries
Click Generate MDX query to create the query that selects data from the Essbase database.
Multidimensional Expression (MDX) is the query language used for Online Analytical Processing (OLAP) databases like Essbase. The query generated is a simple MDX query that uses the selected database, dimensions, and measures. The query can be entered manually in the MDX Query text box, and the generated query can be edited.
The generated MDX query contains:
- Descendants ([Dimension Name], [Level depth]) if a dimension is selected at the highest level. All members of the dimension are included in the results.
-
[Dimension Name].[Member Name].Children if a member of a dimension is selected. That is, a node below the highest level but not the last level. All levels beneath the level selected are included in the results.
-
[Dimension Name].[Member Name] if the member at the lowest level of a dimension is selected. Only that member is included in the results.
The query does not include a WHERE clause, but one can be added to the MDX query. The WHERE clause must conform to the MDX requirements. In MDX, the WHERE clause filters with a member name, not a value. For example, the syntax WHERE [Profit] is correct, but the syntax WHERE [Profit]=1 is incorrect. Also, the WHERE clause must be the last component of the query.
Functions
Enter MDX functions by placing the cursor at the desired location in the generated MDX query, selecting a function from the Functions drop-down list, and clicking Insert Function.
Adding functions is optional.
XMLA MDX queries differ from MDX queries. In XMLA MDX, level 0 represents a dimension rather than a leaf member as it does in MDX. For example, an XMLA MDX is written as follows:
The same query written in MDX is as follows:
Hierarchical dimensions
Select Generate hierarchy for selected dimension if you want to create a hierarchical structure for the dimension or dimensions in the query.
A hierarchical structure can be created only when the selected dimension is hierarchical and is the root or highest level of the hierarchy. If the selected dimension is a branch or a leaf (the bottom node) in a hierarchy, the Generate hierarchy for selected dimension is not enabled. If one of the selected dimensions is the root of a hierarchy and another selected dimension is a branch, the Generate hierarchy for selected dimension is enabled, but only the dimension that is the root of a hierarchy is loaded as a hierarchy. If a root dimension is selected and one of its branches is also selected, the root dimension is loaded as a hierarchy.
When Generate hierarchy for selected dimension is not selected, the resulting list-box tables are flat. When Generate hierarchy for selected dimension is selected, an additional list-box table is produced with the hierarchical structure. For example:
To get the collapsible view in the Hierarchy Year list box, select Show as TreeView in the List Box Properties dialog.