Business Intelligence Shootout: Microsoft SQL Server 2008 BI vs. Pentaho BI Enterprise Edition (Introduction)

Business Intelligence: A History in Review
Business Intelligence (BI) has become a hot topic in IT and business in recent years.  In the past, BI seemed more like a gimmick, with disconnected pieces of software made by various purveyors that seemingly required a significant upfront investment from both IT and business resources, from both a fiscal and training standpoint.


You might have a tremendous analytic engine from someone the likes of Arbor Software with their Essbase platform, but to effectively syphon the data out into a presentable, maintainable format, you'd also have to invest in some sort of enterprise reporting tool like Arcplan.  Furthermore, to grant users the ability to perform ad-hoc slice and dice analysis, you'd need an analytic software package pushed by a company like Hyperion.  The overall experience of BI, much like its roots, seemed disjointed, with no common vision, and as a result, was expensive to learn and maintain and did not impact the business world the way it had hoped.


The days of disjointed BI have thankfully past, and we are in an era of computing where these tools have become nearly as slick as their web and desktop enterprise application counterparts.  Speed of deployment, ease of use, and cost efficiency are the key elements in modern BI.  Users want their data faster, more flexible, and robust enough to truly gain that holy grail of turning business intelligence into business INSIGHT. 


The Geekstantialism BI Shootout!
In this eight-part series, we will bring two up-and-coming BI platforms front and center, and objectively review the strengths and weaknesses of each, as well as make some recommendations on their optimal effectiveness.


In one corner stands a slick platform backed by the billions of the Microsoft Corporation -- SQL Server 2008 BI.


In the other corner, a young upstart Java-based open source BI platform architected by some of the brightest minds in BI since its inception -- Pentaho Business Analytics.


The Parameters
In order to gain the best insight of the strengths and weaknesses of both platforms when stacked up against each other, I've put together the following parameters/requirements that both must follow in this thought exercise...

Pentaho System Setup

• Ubuntu Linux Server 11.10
• Apache Tomcat Server
• MySQL database backend

SQL Server 2008 Setup

• Windows Server 2008 Enterprise Edition
• IIS
• SQL Server 2008 backend

Data Source
The base data warehouse for both will be the AdventureWorksDW2008 database offered by Microsoft.  Obviously, as Pentaho BI runs off of MySQL out of the box, I will be converting said SQL Server database to MySQL, keeping it structurally the same.


Analytic Engine
Using the design tools for both platforms, cubes will be built against all fact tables in the AdventureWorksDW2008 database.  All dimensions will be created.  Dimensional hierarchies will be created where applicable.  Simple calculated members will be created.  Data mining options will be briefly evaluated.


Ad-hoc Analysis
The following ad-hoc slice, dice, and drillthrough will be performed against both platforms using the respective front-ends:

1. Investigation of Internet Sales by Product, Customer, and Sales Territory
2. Investigation of Reseller Sales by Order Date, Employee, and Promotion

Reporting
Both platforms will be evaluated in producing the following reports using provided design tools and persisting them to their respective report servers for distribution:

1. Call Center Report by Date Shift
2. Category of Items Purchased by Customer and Date.

Integration Engine
Both integration engines will be evaluated by using the design tools provided and will perform the following:

1. Refresh DimProduct with new data (CSV source)
2. Refresh DimCustomer with new data (CSV source)
3. Rebuild the respective cubes and dimensions on the respective platform

The Final Verdict
After going through the components of both BI platforms, I will outline the overall strengths and weaknesses of both, and make some educated recommendations on the optimal implementation of either platform.  The goal here isn't necessarily to determine which is truly "better" (as that can be a relative term, depending on the circumstances), but instead to gain a better understanding of what scenarios would call for what tool.  After all, that's the whole fun in technology!


My Qualifications
Finally, if you haven't done so already, you must be asking yourself what makes me qualified to make any sort of judgement call on BI platforms?  Professionally, business intelligence has been my specialty area for the past 6 years.  Having been involved as the lead developer for multiple BI platforms on projects of varying sizes in finance and investments, I have practical experience on BI implementations and the demands of its users.


I do have a depth of knowledge with the Microsoft product stack, as that has also been my professional specialty for about 9 years now (specifically Visual Studio [C#, VB, ASP .Net], SQL Server, and the SQL Server BI stack).


I also have a depth of knowledge in the open source and Java, always experimenting with various flavors of Linux (from RedHat to Mandrake to Debian and now Ubuntu).  For fun, I like to develop Google Android apps.  


This will be my first deep dive into the Pentaho platform.  I hope to gain and document insight into this well-regarded platform and see how it stacks up to the SQL Server BI stack that I know well.  Furthermore, I've always had a soft spot for open source initiatives, but found many times that they're a little too rough around the edges for risk-averse enterprises.  Pentaho looks a bit more promising.


Pentaho claims to be able to lure people away from "big commercial BI" with its platform, and I want to put that to the test.  Is it really a BI platform I can confidently recommend as a viable, and even superior, option when scoping out projects?  Time will tell, and I'm excited to find out!   




Stay tuned for Part 1: Pentaho Analysis Services!

Excel Page Breaks in SQL Server Reporting Services 2008 by Number of Rows and Preserving Column Headers

In your adventures or misadventures with SQL Server Reporting Services 2008 (SSRS), you may be asked to produce exceptionally large tabular reports to be exported to Excel either interactively or on a subscription.  While one would assume this to be a non-issue with SSRS, we then realize that SSRS 2008 unfortunately still exports to an Excel 2003 format, and thus is at the mercy of the dreaded 65536 rows per sheet limitation.


In other words, if you produce a simple tabular report that outputs, say, 100,000 rows of data, and want to export it to Excel, you will get a pretty nasty message from SSRS resembling an unhandled ASP .Net exception, which informs you that Excel sheets (at least in the 2003 version) have a 65536 row limitation.  Drat!


While the solution would be to have SSRS just export to Excel 2007 or 2010 formats and dump the results to one sheet like a proper modern application, Microsoft felt the need to still tightly couple the output to the "lowest common denominator" of Excel 2003, which, to this day, is probably still the most widely used version of Excel (and MS Office).


So, that kind of leaves us developers out on an island initially.  Of course, the next best option is to have SSRS create Page Breaks every 65536 rows, but how are we supposed to do that?  Furthermore, how do we have the column headers for the tabular report repeat on each page?  Through some digging in various forums and MSDN, the solution to this little conundrum is documented below.

• Database Source: AdventureWorks2008 Database
• Database Table: HumanResources.Employees
• Objectives:
• Produce an Excel output from SSRS 2008 which produces a new sheet every 100 rows
• Ensure that the column headers repeat on the new pages
• Freeze the column headers when browsing the report directly on the web browser

So, let's get started by creating a new report which will point to the HumanResources.Employees table of the AdventureWorks2008 database, and throw all of the data elements to the Details section of the report.

Step 1: Standard SQL query to obtain data

Step 2: Move columns to the Details

Step 3: Easy peasy lemon squeezy

Page Breaks Every Specified Number of Rows

So, now we have our standard tabular report.  Let's fulfill our first objective, having a new sheet produced every 100 rows.  This is accomplished by creating a new parent group to the details and specifying a Group Expression for this new parent group.

Step 1: Meet the Parent

Step 2: Use the following syntax to group your details by number of rows --
=Ceiling(RowNumber(Nothing)/[number of rows]).  Example above breaks the groups up by 100 rows.

Hit OK twice and you will notice a new group (called Group1) created as the parent of your details.  We need to further modify this group to create our Page Breaks, so go ahead and double click on that newly created parent group to bring up the Group Properties window.  We will now instruct this report definition to break at the end of the group, as well as remove unnecessary sorting on the group and the new "Group1" column created on our report design.

Step 3: Enable page breaks

Step 4: Click on the "Sort by" and then click the Delete button.  Press OK twice to save Group Properties.

Step 5: Delete the new "Group1" column that's been thrown onto the design.  Delete the columns ONLY.

So, now we have a report definition that page breaks every 100 rows.  Brilliant!  We're done, right?  Well, not necessarily.  Suppose our client wants to see the column headers on each new page, as well as have the column headers freeze themselves when they browse the report on their browser interactively...

Repeating Column Headers in Excel and Freezing Interactive Column Headers

The first thing we need to do is to go into Advanced Mode for our Row Groups.  This is accomplished by clicking the small black down arrow to the far right of the "Column Groups" label.

Step 1: Click on Advanced Mode

Now that we've got Advanced Mode up, click on the first item on the Row Groups labelled "(Static)" and bring up the Properties window if it's not already on your toolbox.

Step 2: Set FixedData to True (freezes column headings in interactive mode), then set KeepsTogether and RepeatOnNewPage to True and KeepWithGroup to After (publishes column heading on each page when output to Excel).

Congratulations, you have now created an SSRS tabular report that breaks every 100 rows and reproduces the column header on each new sheet, as well as freezes the column headers when scrolling down interactively!

In Summary...


Do I wish that there was more apparent solution to this seemingly trivial issue?  I absolutely do.  I'm not sure why this design limitation was overlooked in the development process of SSRS 2008.  It would seem to me that the proper solution would be that the SSRS Excel output algorithm would be loosely coupled from SSRS itself and be dependent upon the version of Excel one installs on the server on which SSRS is running?


Anyway, whatever the reason, at least there is a solution out there, albeit a bit of a roundabout one.

Shrinking Transaction Logs in SQL Server 2005/2008 Using T-SQL

A tripping point of database development in SQL Server 2005 and 2008 tends to be the management of the transaction logs.  When you get into a fairly complicated transactional database design, developers don't often worry too much about the transaction logs -- that is, until, the number of unused pages grows exponentially to the point where the transaction log hits its disk space limitation (if it has one), or worse yet, peg most of the physical disk space available, not to mention give your database an enormous performance hindrance.  


These tasks ought to be taken care of by a DBA (routine transaction log backups/truncations, etc), but developers may not have the luxury of DBA assistance in their test environments.

You can, of course, use SQL Server Management Studio, to assist with shrinking the transaction logs to free up disk space, but there may be times when you need to shrink the transaction as part of your T-SQL stored procedure processes.  Sample code follows:

CREATE PROCEDURE [dbo].[usp_ShrinkTransactionLog]
as

Checkpoint
DBCC OPENTRAN(SampleDatabase)

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

The stored procedure above calls DBCC OPENTRAN to give you information on any open transactions, and is optional.  It's more of a nice-to-have to see what's still ongoing in the database.  


The real meat of the stored procedure is grabbing the internal FileId of your database's log and then issuing a DBCC SHRINKFILE against it, essentially performing the same task as the Tasks --> Shrink --> Files on SSMS -- the code above does not specify a target size, so it will try to shrink to your transaction log's configured default size.


Note that, if your database is on a Full Recovery model, this sproc may not free up all the disk space that you'd want (with DBCC SHRINKFILE potentially coming back with a message saying it couldn't free up all the space requested).  This is because the Full Recovery model, by design, requires that you have regular backups of your logs.  Thus, it will keep all transaction logs in the virtual logs until they are backed up.  The act of backing up your transaction log will truncate it and free up even more space.  So, we can modify the above stored procedure to take this into account:

CREATE procedure [dbo].[usp_BackupAndShrinkTransactionLog]
as

Checkpoint
DBCC opentran(SampleDatabase)
BACKUP LOG SampleDatabase

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

Note that the stored procedure code above assumes that you or your DBA have configured a default Backup Destination for your database.  You can modify the BACKUP LOG statement to dump the transaction log to a specific named Backup Device by adding " TO SampleBackupDevice_Log1" (BACKUP LOG(Sample Database) TO SampleBackupDevice_Log1)


Using either of these stored procedures as part of your T-SQL routines will help in keeping your transaction log sizes manageable.  Of course, do consult with your DBA's if you end up moving your test database to a production environment, as I'm sure they will wonder why the log files backups happen more frequently than they designed.  :-)


Additional info from MSDN: Shrinking the Transaction Log