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:


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 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:



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 …





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.



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:



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.



What Does the New SQL 2012 BI Environment Look Like?

This is sort of a continuation of my recent tool by tool exploration of the SQL Server BI ecosystem in SQL Server 2012 that I’ve been writing about for SQL Server Pro Magazine here and here.

For years, I’ve carried around with me many different high-level data flow diagrams of what and end-to-end BI solution using the Microsoft stack would look like. Come to think of it, I was able to use essentially the same diagram in SQL Server 2005 and SQL Server 2008. Some of the rendering tools changed like Proclarity, PerformancePoint and SharePoint added more BI features. But there was always SSRS, SSIS and SSAS, so I would use something like this below:

When SQL Server 2008 R2 came to market and introduced PowerPivot, I still stuck with this general architecture because PP was still on the uptake / heavy-lift portion of the curve and the majority of production-ready BI solutions were using SSAS for the semantic modeling and cube building.

SQL Server 2012 has changed the game enough such that I’ve started a new data flow diagram in Visio, albeit not as detailed or fancy as the one that I show above.  A big reason for that is (1) I just created this new diagram this week! And (2) it has to evolve over time. As SQL Server 2012 BI solutions using Tabular Model databases and techniques becomes more mature and builds up a larger set of best practices and lessons-learned, then I will update these diagrams and share them here on my blog as well as over at SQL Server Pro Mag.

Now that SQL Server 2012 fully embeds and supports columnar compression through the Vertipaq engine in SSAS, you can build semantic models with Visual Studio or with PowerPivot. To use the Power View visualizations such as I am depicting in this diagram, you will need to have a BI Semantic Model, so I’m now shifting to this guidance in many cases. Using PowerPivot for data modeling, IMO, is very advantageous because it expands the data analyst community to Excel users and data experts and allows for easy trail-and-error style of data modeling whereby Excel becomes the design surface to test your models through Pivot reports.

New MSSQLDUDE Videos: SQL Inventory with MAPS and WP7 Cloud BI

If you would like a short 10-minute intro on how to get started using the Microsoft Assessment and Planning Toolkit (MAPS) to inventory, discovery and record your SQL Server footprint, versions, utilization, users, licenses, etc., then you can have a look at the video that I just posted on our Microsoft YouTube channel for SQL Server data warehouse & BI group here in the East Coast U.S. field organization: BTW, the MAPS tool is a FREE download from, see it here. This is version 6.5 of this tool and if you have any version prior to it, I suggest that you upgrade to it and run another SQL Server assessment on your network. This version of MAPS now has the awesome feature of virtualization discovery, proposal and recommendations built-in. This makes MAPS the premier SQL Server tool when consolidation your SQL Server environment.

And if you have a Windows Phone 7 and would like to see what a WP7 Mobile Cloud BI App would look like, take a look at this video that I just posted for our Microsoft field national DW & BI team:!. This describes the updated V2 improved version of the WP7 App for Microsoft Cloud BI on a mobile device which I described in my slide presentation here.

These are some of the BI data visualizations that you get out of the box with this WP7 app on your phone through the native app:


SSRS Against an Oracle Data Source

My continuing coverage of integrating Oracle and SQL Server … Today’s post is a bit of a cheat: I’m just going to point you back to a blog post that I did when I was with Oracle where I showed you how to leverage SQL Server Reporting Services (SSRS) against an Oracle data warehouse that was loaded from the Oracle Primavera enterprise project portfolio management product (EPPM):

The reason that I felt compelled to post that example when I was with Oracle is that I would constantly run into customers who were using 1 of 2 products for ad-hoc reporting: Crystal Reports or SSRS. And usually SSRS and Report Builder where there because customers had existing investments in SQL Server. Of course, Crystal Reports was there because (a) it’s been around forever and (b) most of the customers that I worked with were actually SAP customers, not Oracle Apps customers. Which means that they received Crystal Reports licenses.

Anyway, long story short, that link above is a decent example of how I was able to pull in Oracle database data directly into an SSRS report. Very easy and straight-forward. Also, if you are going to use the SSMA migration assistant from Microsoft to migrate your database from Oracle to SQL Server, you will need to recreate your Oracle Reports in SSRS. There are a few companies out there that will work with you as consultants to migrate those reports for you.

SQL Server Reporting Services Performance

I just returned from having several in-person discussions this week with customers, DBAs and developers who were looking for guidance, best practices and whitepapers on scaling SQL Server Reporting Services (SSRS) 2205 & SSRS 2008. I compiled what I feel are the best publicly available postings from Microsoft’s internal SQL CAT & product teams for each of these areas below … enjoy! Best, Mark

Microsoft BI with Oracle Applications

Is it necessary to always think in terms of Microsoft v. Oracle from a DBA or developer’s perspective? Or can these 2 technologies co-exist and flourish together when you are faced with such a heterogenous environment?

Of course, they can exist side-by-side and I have experienced many environments like this and have written a few pieces about some of these mixes. An example would be a Microsoft .NET or Microsoft BI developer writing solutions and reports against an Oracle application. I have some examples such as on my Oracle blog here which I authored when I was an Oracle product manager, where I demonstrate using Report Builder with Oracle applications and an Oracle data warehouse.

I am going to expand on using Microsoft BI & reporting from SQL Server to access Oracle applications and Oracle databases, as well as best practices in exchanging data bi-direction between the 2 databases. It is important to understand best practices, limitations and gotchas which I run into from time to time, especially when migrating databases from Oracle to SQL Server. I’ll run through this in about 3 posts as a new series starting when I return home later in the week.

In the meantime, if you are interested in more sharing or Oracle & SQL Server co-existing, check out the SQL Server Oracle Virtual Chapter from PASS, the Professional Association of SQL Server chapter for Oracle.

World Map CodePlex Project for Reporting Services

Really cool project on Codeplex to make available maps from across the globe, beyond the out-of-the-box maps that you see in Report Builder today: 

This is a copy & paste right from the Codeplex project site:

MapGallery of Reporting Services in SQL Server 2008 R2
World map separeted by countries, able to join Map Gallery in Reporting Services inside SQL Server 2008 R2. Join us, and make your country in this world map.

Everyone can upload your own country map
By default, only USA maps came with Reporting Services. This proposal is make a world map to use for all in Reporting Services.

How EZ are Bing Maps in SSRS 2008 R2?

IMHO, ridiculously easy using Report Builder 3.0. I put this together in 10 minutes by using this Microsoft tutorial that shows you how to take geospatial data, link it to analytical data and display it on a map, overlay Bing Maps and visualize data points for awesome BI dashboards.

What you will notice right away from my screenshot below is that I am not a graphic artist! But this is an awesome point in a visualization project to get to quickly and then modify the details as you need for your project.

Keep in mind, though, if you walk through that tutorial on your own as well, is that it is a bit of a cheat because the code in the tutorial wraps-up the geography points together with the analytical data for displaying points in the SELECT query instead of pulling the data from a data source. You would likely never do this in the real world. Instead, what you will do is take the points from your geospatial data and your BI data from a DW or cube and then link it in the Reporting Builder Maps Wizard.