Jedox OData Hub Connector in PowerBI

You can connect to the Jedox OData Service in PowerBI Desktop by using the built-in OData Connector. There are two different ways to connect to the service: flat API and Hierarchical API.

The Flat API has been designed to be as simple as possible. Cell and dimension information is available from a single table and can be imported with just a few clicks. The flat API only contains the cell and dimension data.

The Hierarchical API has some additional information about the available cubes, views, and dimensions. When building complex models, the Hierarchical API may be more appropriate.

Fetching data with the Flat API

Connecting to the OData Service

In Power BI Desktop, you can connect to the Jedox OData Service by using the built-in OData Connector. Click on Get Data and select OData feed, as shown below:

Connecting to Jedox OData Service in Power BI

In the next dialog, enter the URL to your OData service, followed by the database name. It should match the following syntax:

Copy
https://odata.{InstanceId}.cloud.jedox.com/{DatabaseName}

Make sure the URL begins with https:// and click OK:

If you are not yet authenticated, another dialog will appear, prompting you to log in. Select Basic authentication and use your Jedox credentials to continue. Note: organizational account and other options are not supported at this time, but may appear in a future update of the OData Service.

Upon successful connection to the server, the Navigator will open. Select the cube, views, and dimensions you want to import and click Load.

Power BI will fetch the selected tables. This operation may take several minutes, depending on the size of the tables.

Configuring relationships

When importing tables, Power BI will automatically build relationships between the tables. These generated relationships may be incorrect, so check that the dimension tables are connect from the Name column to the cube or view column containing the name of the dimension:

Rebuilding dimension hierarchies

To use a dimension hierarchy built in Jedox, the dimension must be imported and its relationship to the cube/view must be set up correctly, as shown above.

Go to the Report tab and locate the dimension table in the fields list. Each dimension table contains one or more hierarchical columns named from 0-n. To build the hierarchy, you must have at least two hierarchy columns. Simply drag and drop column 1 into column 0 to generate a hierarchy:

Then drag and drop the remaining hierarchy columns into the newly generated hierarchy:

Optionally, you can rename the hierarchy to something more meaningful.

Hierarchical API

Connecting to the OData Service

In Power BI Desktop, you can connect to the Jedox OData Service by using the built-in OData Connector. Click on Get Data and select OData feed, as shown below:

Connecting to Jedox OData Service in Power BI

In the next dialog, switch to the Advanced view and enter the URL to your OData Service in the URL parts field, using the DNS of your Cloud instance. Check Include open type columns at the bottom of the dialog and press OK.

Upon successful connection to the server, the Navigator opens. Select the Databases on the left side and click Transform Data.

If you are not yet authenticated, another dialog will appear, prompting you to log in. Select Basic authentication and use your Jedox credentials to continue. Note: organizational account and other options are not supported at this time, but may appear in a future update of the OData Service.

In the Power Query Editor, you should now see a list of databases. You can use the “Table” links (indicated below) to navigate into cubes, dimensions, and views.

Loading cell data

Both cubes and views contain a list of cells. To load the cell data, navigate into the desired cube or view, and then into Cells. The Cells table contains the following columns: Path, Values, String Values, and More Columns. More Columns contains the element names of each row. To expand it, click the expand button in the header. In the drop-down menu, make sure that all columns are checked and click OK. Optionally, you can also uncheck the checkbox Use original column name as prefix to prevent renaming of the columns.

Cells tables from both cubes and views support the following custom query options:

  • baseonly: if set to true, the service will query base elements only (not consolidated elements). This is turned off by default for views. For cubes, this is turned on by default.
  • userules: defines if the values should be calculated using rules. This is turned on by default.

Note that changing these options might have a negative impact on the performance. See an example in Using custom query options.

Loading dimension elements

Dimension elements contain the hierarchy and the attributes of each element. To load the elements, navigate into the desired dimension, and then into Elements. The Elements table contains the following columns: Type, Name, ID, and More Columns. More Columns contains the hierarchy of the elements in the Full Hierarchy format. To expand it, click the expand button in the header. In the drop-down menu, make sure that all columns are checked, and click OK. Optionally, you can also uncheck the checkbox Use original column name as prefix to prevent renaming of the columns.

After applying the data to your Power BI model, you can create a hierarchy from the hierarchy columns.

In the Report tab, drag each of the hierarchy columns into each other to create a hierarchy. You can do this using either the ID columns or the name columns.

Using custom query options

Currently Power BI does not allow you to enter custom query options out of the box. To work around this, you need to enter the full URL to the cells/elements. Find the ID of the databases and the cube, dimension, or view. Click on Get Data > OData feed and enter the full URL.

The full URL to a Cells table with the userules option should look like this:

https://odata.live-myinstance.cloud.jedox.com/Databases({id})/Cubes({id})/Cells?userules=false

You can also combine multiple options like this:

https://odata.live-myinstance.cloud.jedox.com/Databases({id})/Cubes({id})/Cells?userules=false&baseonly=false

When entering the URL in the dialog, make sure to check the Include open type columns, and click OK.

See also:

Updated February 11, 2021