Adventures in Relational Data

In some models, the data you are presenting is not multidimensional. Lists, records, transactions are not necessarily made for storing in cubes. Some data just makes more sense in a table. Although Jedox’s bread and butter is multidimensional data (ie cubes, dimensions), you still have the capability to display relational information when you need it in Jedox web.

3 Ways to Use Relational Connections

1. Combo Boxes
If you have used Combo Boxes on the web, you would have noticed that there is the option to point to a relational ODBC source to populate the list.

rel1

This is very useful when you have a report and the data you need from the drop lists cannot be generated from a subset of dimension elements.

rel2

Additionally, you can use variables to store the selected value, and pass it into another relational drop list. This way you can generate cascading drop lists based on user selections.

2. ODBC Paste Query
This automatically generates ODBC.DATA formulas that point to the relational source. As your query changes (by variables for example), the data will automatically update. This is excellent for dynamically pulling through the result-set of a relational query into Jedox web and combining it with other data for a report or dashboard. When using the ODBC Paste query, make sure you have set up the ODBC connection in Connection Manager and have tested the connection.

rel3

3. PHP Macro
Finally, If the top two methods do not return you what you need, you can always go direct with a Web Macro. The advantage of this approach is that you can get the data back to the macro as an array and you can then display it anyway you like. For instance, you could have a message box popping up with data from a query. Or you could dynamically validate some data entry against a relational data source as users are entering it on the web.

rel4

You can download an example of the PHP macro code here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s