Tag Archives: mdx on mongodb

Pentaho Native Analytics on MongoDB

15 Dec

Pentaho has a very rich and complete business analytics product suite. There is ETL, data integration, data orchestration, operational reporting, dashboards, BI developer tools, predictive analytics, OLAP analytics … and I’m probably missing a few others!

So when you are looking to implement a business intelligence and analytics solution for a Big Data platform using a modern technology outside of the traditional RDBMS sphere, like MongoDB NoSQL database, you have the advantage of a complete BI product set that works out-of-the-box to take advantage of that platform’s strengths.

What I mean by that is with Pentaho, there are different tools to optimize each aspect of a complete BI solutions. For instance, Pentaho Data Integration (PDI) has direct hooks into MongoDB using their API directly to manipulate and move data using MongoDB documents. The Pentaho Report Designer (PRD) also uses that same direct access mechanism to provide reporting for your business users directly on MongoDB.

With the Pentaho 5.1 BA Suite Release, interactive OLAP analytics using Pentaho Analyzer was introduced. This is Pentaho’s unique capability to translate business user queries using slice-and-dice MDX mechanisms directly into MongoDB AggPipeline queries.

With these capabilities, Pentaho does not require extracting and staging of MongoDB data from documents in collections into traditional RDBMS tables. Instead, analytics is turned into native MongoDB query syntax on the fly without any SQL requirements. And as I stated above, this allows the user to fully leverage and optimize your Big Data source, in this case MongoDB. Pentaho will push down queries into your MongoDB cluster, thereby not requiring you to establish an entirely separate analytics platform with its own hardware and scalability requirements.

More MDX Fun on NoSQL Databases

2 Oct

Let’s use Pentaho’s Business Analytics Suite one step further to do a few more interesting things with MDX OLAP models that we’ve put on top of MongoDB and Cassandra data stores.

Check over at those links above so that you can follow along with the Pentaho tools. I’m going to start from where I left off with an in-memory OLAP model built using the Instaview tool and the Mondrian MDX engine.

Just as a quick reminder, I will use the set-up from the Integration portion of Instaview for MongoDB using this JSON Query Expression:

{ “$query” : { “url” : “–firstpage–” }, “$orderby” : { “Count” : -1 } }

And I’m selecting just the Key, URL, NextURL and Count fields for this simple demonstration. One of the first changes that you can make to expand your OLAP modeling and MDX capabilities on MongoDB is to choose “Model” from the Pentaho Instaview UI and add measures, dimensions, properties, formatting and your own hierarchies to the model. This Model represents the logical model that your MDX queries will utilize from the in-memory layer when querying MongoDB.

m2

Back in Instaview, you will navigate to the Visualize screen so that we can issue MDX queries to our MongoDB data. Click on the Administration button at the top and select “MDX”. Here will be presented with a blank MDX query box. This is essentially where we were running a few test MDX queries from our first model. In this demo, let’s try a few more interesting ways to interrogate and slice the Mongo data:

1. Let’s order the output of Next URLs by the most frequently hit pages, regardless of the order of the pages in the NextURL dimension. In MDX, we do this with the ORDER function and use BDESC to order the data Descending with a “B” for “Breaking Hierarchy” so that the report user can see the data in order of actual counts, not based on the order in the hierarchy:

SELECT [Measures].[Count] ON 0, ORDER([NextURL].Members,[Measures].[Count], BDESC) ON 1 FROM [Mongo2]

And as we saw before, Pentaho Analyzer will give you the results in an HTML Web page

m4

2. It should be pretty clear now that the normal dimensional navigating of member data is available to you from these NoSQL sources, just like MDX against RDBMS sources. This is how you access a count from a single specific page or dimension member … Just put the member name in square brackets [ ] and refer to the Dimension name first:

SELECT [Measures].[Count] ON 0, [Nexturl].[/careers] ON 1 FROM [Mongo2]

Or use relative MDX functions which really begins to exploit the power we enjoy in MDX land such as FirstChild, Sibling, LastChild, etc:

SELECT [Measures].[Count] ON 0, [Nexturl].FirstChild ON 1 FROM [Mongo2]

SELECT [Measures].[Count] ON 0, [Nexturl].[/about].Sibling ON 1 FROM [Mongo2]

Results in those cases are single-row member calculations:
[Measures].[Count]
[Nexturl].[/about]    504
3. OK, I’m almost done … let’s try 1 more where I will now use the powerful FILTER function in MDX so that my sorted list of counts only shows counts > 1000

SELECT [Measures].[Count] ON 0,
FILTER (
ORDER ([NextURL].Members,[Measures].[Count], BDESC) ,
([Measures].[Count] > 1000) )
ON 1
FROM [Mongo2]

mdx9

cbailiss

Microsoft SQL/BI and other bits and pieces

TIME

Current & Breaking News | National & World Updates

Tech Ramblings

My Thoughts on Software

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Insight Extractor - Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

The SQL Herald

Databases et al...

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

Bill on BI

Info about Business Analytics and Pentaho

Big Data Analytics

Occasional observations from a vet of many database, Big Data and BI battles

Blog Home for MSSQLDUDE

The life of a data geek