Adventure Works SQL DW on Pentaho OLAP, part 1

Let’s say you have SQL Server databases for your data marts and warehouses in your organization, but you are looking for alternatives to SSAS cubes and SharePoint-based dashboards. If you are like me and have built many BI solutions in SSAS OLAP and PPS, then you’ll find a transition to OSS with Pentaho to be very easy and a natural fit for those BI scenarios. We’re going to take this SQL Server 2012 DW and SSAS cube and recreate it all in Pentaho:

aw1aw2

I’m going to kick this series off with a very simple BI dashboard using the traditional SQL Server Adventure Works data warehouse data set and put a Pentaho ROLAP cube and dashboard on top of that data. What I think you’ll really be impressed with is how much easier it is than building cubes in Visual Studio and PPS dashboards.

Start by going to Pentaho.com to download an evaluation version of the Pentaho Business Analytics suite and run the installer. We’re going to stay completely in a thin client browser experience for this demo, so no need to open any IDE tools through this entire workflow.

I’m using 4.8.2 of the Pentaho suite for this series and so when I log into the portal, I will use the Analysis feature to point to the SQL Server database and auto-generate the OLAP cube as well as design the visualizations. This is also where I can put my reports and analysis views together for end-user dashboards:

aw3

 

When you click to create new Analysis content, all you have to do to recreate a cube in the Pentaho Mondrian OLAP engine is to point to your DW database source and the engine will auto generate the model for you. So to replace the SSAS cube I have above, just follow the flow of these next 3 steps as I point to SQL Server, select my facts and dimension tables and create the star joins. This is a very simple beginning model which we will use for the rest of this series to build upon with custom calculations and other features. In classic AdventureWorks mode, I will name InternetSales as my fact table and let’s keep things simple and straightforward for this first intro to Pentaho modeling and just join in the Date and Product dimension tables. Just like SSAS, Mondrian will treat facts as measures and dimension tables as dimensional hierarchies and aggregation levels from your data warehouse. Much, much more on this in the coming weeks …

aw4

 

aw5aw6

 

That’s all you have to do. Now you will select the model which will show up in your list as the name that you typed in from the screenshot above which I called simply “AdventureWorks”. Selecting that model will drop you into the interactive Analyzer reporting tool where you will see a field list to make pivot tables and data visualizations which you can see below. Think of that last step as running through the SSAS cube wizard in Visual Studio with data source views and publishing the cube on the SSAS server. This can be done in a much smaller number of steps here in Pentaho.

aw8

 

Remember that this is using ROLAP for the cube engine (Pentaho’s Mondrian), so in the upcoming parts of the series, I’ll talk about optimizing and customizing the logical OLAP models so that these reports can perform well for you. Your final step is to put your reports together as an interactive dashboard. In upcoming parts of the series, we’ll make the dashboard fully interactive as well. For now, just pick a couple of reports that you’ve generated from the AdventureWorks model and drop them onto the Dashboard Designer surface:

aw9

 

That’s pretty much it. We’ve recreated the OLAP cubes and reports and put them into a dashboard all in a browser using Pentaho with the SQL Server database source, replacing SSAS and SharePoint PPS. I know it was quick & brief, so think of this first part as just a teaser. We’ll continue to build this out with more features so that you can have some guidance when trying out Pentaho’s BI Suite.

As always, just reach out to me with any questions or requests. Enjoy! Br, Mark

FYI, if you need to download the SQL Server sample databases above, you can go here on Microsoft’s Codeplex site to get Adventureworks.

 

Advertisements

SQL Dude Adventures and BI Blogging

Hi Everyone! My apologies for the gap in blogging … It’s been a long couple of months transition over to the Open Source Software world and relocating to Orlando for Pentaho. It’s been an amazing adventure so far and I’ve just started transitioning my work to the hybrid OSS / commercial world for this MSSQLDUDE blog, SQL PASS, local BI Meetups & the SQL Server Pro Magazine BI Blog.

I will post my Adventure Works on Pentaho OLAP blog post this evening to kick it off and if you are looking for the more purist Big Data Analytics and Pentaho coverage, check out my Big Data Analytics blog here.

See ya soon! Best, Mark