Power Pivot and Small Data: A Self-Service Approach to Business Intelligence

Providing a business intelligence (BI) solution that lets end users create analytics on their own and requires minimal IT intervention, low back-end maintenance, and a short learning curve for IT is a challenge. My solution, which is designed for small-to-midsized businesses (SMBs), involves empowering end users with true self-service BI on the client side.

How the Solution Came About

My organization is a Microsoft shop using enterprise software. In 2010, I found that our only BI option was SQL Server Analysis Services (SSAS) because it was bundled with SQL Server. SSAS generates an OLAP cube that can be queried by Multidimensional Expressions (MDX). SSAS is a comprehensive but complex tool that requires a steep learning curve to create effective cubes correctly. The manpower investment to create, maintain, and query an OLAP cube exceeded our requirements and was too costly to pursue. So, it was eliminated as a possible BI solution.

By 2012, I found two SSAS alternatives that followed the self-service BI paradigm:

Both solutions leverage the Data Analysis Expressions (DAX) query language. Unlike MDX functions, DAX functions use a syntax that's similar to the syntax used by Excel functions. These features let Excel power users master DAX significantly faster than MDX.

I requested a comparison of the server-side and client-side approaches as part of my pre-sales discussion with Pragmatic Works. I contracted with Pragmatic Works for an onsite Power Pivot training class that integrated the use of the organization's data as part of the training.

Table 1 shows the comparison, which is from an enterprise perspective rather than an SMB perspective.

The client-side approach might be viewed as negative because of Excel's upper boundary constraints and other limitations. However, we found that the upper boundary constraints were workable and that a client-side solution would provide end users with the flexibility that we wanted to make available. Remember that the client-side approach is for SMBs, which typically have fewer than 5 million rows of data. SMBs have BI needs that often can only be met with a solution like this.

The client-side approach offers several other advantages:

The client-side approach has a few cons, but they're what I call "the cost of doing business." One cost is upgrading the client hardware, but this cost is minimal. An unquantifiable cost is the learning curve that the end users need to undergo when they're learning how to implement measures, calculated columns, and relationships, and how to test and debug results on the Excel client. It seems to be a cheap price to pay for true self-service BI on the client side.

It didn't take long for us to determine that using a client-side approach was our best option, so I designed the client-side self-service BI solution. As Figure 1 shows, it uses a cube topology (star schema) in an OLTP database on the back end.

Figure 1: Client-Side Self-Service BI Solution

On the client side, Excel 2010's xVelocity engine creates an in-memory cube. With this setup, developers can assist the end users on an as-needed basis rather than performing the BI analyses themselves. Let's take a closer look at the various components in the solution, including the extraction, transformation, and loading (ETL) process, the OLTP database and star schema on the back end, the client-side Excel and Power Pivot tools, and the OData service.

ETL Process

Our organization preserves the state of its information by publishing data by billing cycle. The data is scrubbed prior to loading and is the basis for all our reporting. The preservation of the data by billing cycle provides the end user with the ability to reproduce identical results at that point in time.

The published data is loaded into the star schema in our OLTP database (i.e., destination database) using the T-SQL MERGE command and a linked server pointing to the source data. When new data is loaded into the star schema, some of the tables are truncated, whereas others are upserted. Upserting is inserting rows into a table if they don't already exist and updating the rows if they do exist.

The use of the MERGE command simplifies the ETL process. When the source data is loaded into the destination table, the MERGE command:

Figure 2 shows a sample MERGE command. In it, I noted where these three events occur.

Figure 2: Sample MERGE Command

OLTP Database and Star Schema

As I previously mentioned, the client-side self-service BI solution uses an OLTP database normalized to a star schema. I used the CA ERwin Data Modeler to create the baseline star schema, which Figure 3 shows. In the star schema, all the dimension tables have foreign key relationships to the fact tables.

Figure 3: Baseline Star Schema

The key is relationships rather than star schema optimization because Excel's xVelocity engine works its magic when the data is imported into the client. The dimension and fact tables are backed up every monthly billing cycle.

In the SQL Server database, I developed various computed columns for commonly used data to avoid the repetitive creation of the same information by the client. When the client loads the data from the database using the star schema, the main relationships are immediately available to the user. This helps reduce the learning curve for those people who are new to Power Pivot.

Excel and Power Pivot

Excel's xVelocity engine is an important component in the client-side self-service BI solution. It leverages the columnstore index to create an in-memory cube against which to pivot. (If you're unfamiliar with this type of index, see MSDN's Columnstore Indexes web page.)

We upgraded to 64-bit Excel on the client machines to get around the 65,535 row limitation in the 32-bit version. We also increased the machines' memory to a minimum of 8GBs, which is a small price to pay for the flexibility gained.

Power Pivot is added to Excel through a plug-in. (If you're unfamiliar with Power Pivot, check out the resources listed in the Learning Path at the end of the article.) The data loaded into Power Pivot reflects the design of the star schema model, as Figure 4 shows.

Figure 4: Data Loaded into Power Pivot

Non-related tables added to Power Pivot can be related through the drag-and-drop feature in the Diagram View. Handling nonrelated tables this way is far more intuitive for non-IT folks.

OData Service

In the future, we plan to create an OData service using Windows Communication Foundation (WCF) Data Services. WCF Data Services lets you not only create but also consume OData services for the web (formerly known as ADO.NET Data Services). The services can be leveraged by using the Entity Framework with the OLTP database.

External users will access the data through the OData service. Our plan is to use proxy accounts and users' email addresses to create a custom context to access data based on our security scheme. We will leverage a T-SQL table-valued user-defined function (UDF) using the LDAP-authenticated email address as part of the extended membership to define the user's custom context. All queries will use the T-SQL table-valued UDF to return the data that the user is allowed to see.

A Better Approach for SMBs

SMBs have different needs than enterprises when it comes to BI solutions. In SMBs, using SSAS to create, maintain, and query an OLAP cube is often too costly in terms of manpower investment. A better approach is to design and implement a self-service BI solution on the client side. With this solution, end users can perform their own analytics, with minimal IT involvement. The end users are happy because they don't have to wait for IT to run their BI analyses. The IT folks are also happy because they have more time to perform other job duties. But perhaps most important, this solution is very flexible. Anyone with Excel, the Power Pivot plug-in, and a variety of data sources (e.g., flat files, spreadsheets) can perform BI.

*************************************************************************************

Peter Heller is a computer specialist (level 3) at NYC DCAS in New York City. He is also the project manager of DCAS's Energy Cost, Control, and Conservation (EC3) project, a job for which he was nominated for New York City's Project Management Leadership Award in 2012. The Project Management Leadership Award is part of the city's Excellence in Technology Awards Program (ETAP).
Email: [email protected]
LinkedIn: http://www.linkedin.com/pub/peter-heller/27/2a7/742

*************************************************************************************

Learning Path

SQL SERVER PRO RESOURCES
For more information about Power Pivot, see:
"A Walkthrough of PowerPivot for Excel 2010"
"How to Create PowerPivot Applications in Excel 2010"
"Introducing PowerPivot for SharePoint"
"Taking PowerPivot to the Enterprise"
"Understanding PowerPivot and Power View in Microsoft Excel 2013"
"What's New in Microsoft SQL Server 2012 PowerPivot"

For more information about BI in SQL Server, see:
"Power BI: Digging Into Your Business Data for Profits"
"SQL Server 2012 Business Intelligence Enhancements"
SQL Server BI Blog

OTHER RESOURCES
For more information about Power Pivot, see:
"Data Analysis Expressions (DAX) Measures In PowerPivot"
DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution
Microsoft PowerPivot for Excel 2010: Give Your Data Meaning
PowerPivotGeek
PowerPivotPro
PowerPivot Blog
Practical PowerPivot & DAX Formulas for Excel 2010

For more information about BI in SQL Server, see:
Project Botticelli
SQLBI Blog