3rd Party Access: ODBO/XMLA
|Important: ODBO connector is being deprecated, starting with Jedox 2019.1. Support for this third-party connector will be discontinued as of Jedox 2019.3.|
Object Linking and Embedding Database for Online Analytical Processing, or ODBO, is a Microsoft-published specification and an industry standard for multi-dimensional data processing.1 The Jedox ODBO provider is included in the normal Jedox all-in-one setup and is installed by selecting “Jedox 3rd Party Access (ODBO/XMLA)” in the setup dialog. It allows Excel 2007/2010/2013 to create pivot tables using the data from a Jedox database and supports writeback and modeling. Other front ends, such as Bissantz DeltaMaster, and Cubeware Cockpit, are also supported.
To be accessible through the Jedox ODBO Provider, a Jedox database must conform to the following preconditions:
- Each dimension must have only one top element.
- Parallel hierarchies are not allowed (for each element only one parent is allowed).
- It is possible to define various measures and time dimensions for a database. However, for MDX access and pivot-table integration, each cube has to be assigned exactly one measure dimension. A single time dimension is possible, but not required. The names of these dimensions are arbitrary.
If there are dimensions with the names “Measure” or “Time”, these are automatically treated as measure and time dimensions. You can also define these dimensions in the Modeler of Jedox Excel Add-in: open the Modeler and click on the cube for which you want to define a measure or time dimension. The dimensions contained in the cube are expanded. Now right-click the dimension which you want to treat as a ‘Measure-dimension’ and select ‘Treat as measures dimension (MDX)’ from the context menu.
This dimension now will be used as ‘Measure dimension’ in Excel Pivot-Table connections. In the same way, a time dimension can be defined.
- The following structural constraints for the time dimension.
If there is a time dimension in the cube, then this dimension must have a structure like: 2005_H1_Q1_M12_W4_D1. H stands for half year, Q for quarter, M for month W for week and D for day. Any position in this structure is optional. One can have 2005_D1, or 2005_M2_D15, for example.
Two detailed examples:
With months as N-elements:
With days as N-elements:
The suffix for H/Q/M/W/D is not bound to a particular number. However the suffix should be consistent with the context. For example, in a structure like 2007_M1_D1, the D element (day) can take any value between 1 and 31 (day of month). In a structure like 2007_M1_W2_D1 the element D can take any value between 1 and 7 (day of week).
How to use other names for the time dimension elements
If you want to use the default names instead of other names for the time dimension elements, then you can define them in the cube ‘# _CONFIGURATION‘.
For the following time elements new names can be defined:
Since the configuration dimension cannot be edited in the Modeler, you must add those time elements, for which you want to specify a different name, with the function PALO.EADD. Before we need the address parameters for PALO.EADD.
Once you have treated e.g. in the demo database ‘Years‘ as a time-dimension of the cube ‘Sales’, in the configuration dimension are additionally created the elements ‘MDXMeasure_Sales‘ and ‘MDXTime_Sales‘:In the view above 3 lines were added for the upcoming element import. The address details for PALO.EADD() can be found in A10 (element ‘MDXTime_Sales‘).
As an example we will define for the element ‘MDXHalfYearElements‘ the names ‘Half1,Half2‘ instead of the standard names ‘H1,H2‘.
First we add the element ‘MDXHalfYearElements‘ to the dimension ‘# _CONFIGURATION_‘ with the function
and the Import Wizard (Data source: internal loop).
|After this addition, you must create a new view. There you can define for ‘MDXHalfYearElements‘ the new names (delimiter is semicolon):|
- Needed address changes for the ODBO connection
- Using Jedox ODBO Provider with Excel 2007/2010
- Jedox 3rd Party Access/MDX: Supported schemas and defined syntax elements
- Jedox 3rd Party Access/MDX: Provided Functions
- Jedox 3rd Party Access/MDX: Supported Statements
1 Source: Wikipedia (https://en.wikipedia.org/wiki/OLE_DB_for_OLAP)