In this recipe, we are going to connect to our MongoDB database using an MDX query from Pentaho Analyzer. We are going to create a simple query using Analyzer, copy the generated MDX, and create a data source in CDE to execute the MDX query.
To get ready for this recipe, you first need to start the MongoDB server with the same database as that in the last chapter. You will also have to start the Pentaho BA Server using the server control scripts. Once it is started, you can log in to the BI Server.
The first step is to create an MDX query using the Pentaho EE Analyzer:
- In the PUC, navigate to File | New | Analysis Report.
- Select the mongoDBPentahoCookBook: Orders data source from the list of available data sources, like this:
- Click on OK.
- Click and drag the Country field from the Customers dimension to the analysis report canvas.
- Then, click and drag Total Price from the Measures dimension to the analysis report canvas next to Country, as shown here:
- Click on the cog icon on the Analyzer toolbar.
- Go to Administration | Log. This will open a new browser tab.
- Select and copy the MDX from the Message column. Make sure you select only the MDX query, as shown in this screenshot:
We now have an MDX query that we can add to our CDE Dashboard. Let's create the dashboard and add this MDX as a data source:
- In the PUC, go to File | New | CDE Dashboard.
- Click on the Data Sources tab.
- Expand the OLAP4J Queries data source category.
- Click on the olap4j over olap4j data source.
- Set the Name property to QUERY3.
- Then set the Driver property to
org.pentaho.platform.plugin.services.connections.PentahoSystemDriver. - Next, set the URL property to the following:
jdbc:mondrian4:Host=localhost;dbname=SteelWheels;DataServicesProvider=com.pentaho.analysis.mongo.MongoDataServicesProvider;Catalog=/home/latino/git/pentaho-mongodb-cookbook/source code/chapter4/MongoDBPentahoCookbook.mondrian.xml.
- Open the Query Editor property, and copy and paste the following MDX query:
WITH SET [*NATIVE_CJ_SET] AS 'FILTER([*BASE_MEMBERS__Customer_._Customer_], NOT ISEMPTY ([Measures].[Total Price]))' SET [*BASE_MEMBERS__Measures_] AS '{[Measures].[Total Price]}' SET [*BASE_MEMBERS__Customer_._Customer_] AS '[Customer].[Customer].[Country].MEMBERS' SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET], {([Customer].[Customer].CURRENTMEMBER)})' SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],[Customer].[Customer].CURRENTMEMBER.ORDERKEY,BASC)' SELECT [*BASE_MEMBERS__Measures_] ON COLUMNS ,[*SORTED_ROW_AXIS] ON ROWS FROM [Orders]
This is the query you generated in the Analyzer report in previous steps.
- Click on OK.
- Click on Save As in the CDE Main Menu.
- Save your dashboard by going to Public | Pentaho MongoDB Cookbook | Dashboards, with the name
MongOLAP Data Source.
To test whether this data source works, we are going to execute it directly from the accompanying CDA file:
- Click on the Opened menu on the Pentaho User Console and select Browse Files.
- Navigate to Public | Pentaho MongoDB Cookbook | Dashboards.
- Select the MongOLAP Data Source.cda file, as shown in the next screenshot.
- Click on Open on the right-hand side menu.
- Click on the Data Access dropdown and select Data Access ID : QUERY3, as shown in this screenshot:
The query will execute once you have selected it from the Data Access drop-down menu. You will see the contents of the MDX query that is attached to the dashboard you defined earlier.
In this recipe, we guided you through using Pentaho Analyzer to generate an MDX query that you can use on a dashboard. In dashboard editor mode, you will need to add a custom OLAP4J data source, set the driver connection URL, and set the MDX query copied from Pentaho Analyzer. After saving the dashboard, we can execute the query from the CDA file and see the results from the cube.
This is another way of using MongoDB connectivity—by using analysis cubes. However, this type of connectivity is available in the Pentaho EE version only, as Pentaho Analyzer is an enterprise plugin.