1. technology and computing
2. software
3. databases

The Changing World of
Business Intelligence:
Leading with Microsoft Excel
Business intelligence (BI) is a continually changing landscape. In-memory
analysis tools are maturing rapidly, and business users are more technically
savvy and demanding of control of information. In the Microsoft Office suite,
Excel and Access have been the tools of choice for these business users,
while BI experts have rejected these solutions as difficult to control, and not
enterprise grade.
Microsoft addressed this tension with the introduction of PowerPivot, making
it much easier to get data into Excel. Now, Excel is becoming the standard
tool for transforming data into relevant and meaningful information. With
the releases of Excel 2013, SharePoint 2013 and SQL Server 2012, we see
business solutions built on the Microsoft BI platform changing the role of
enterprise data warehouses and data cubes. The groundwork is laid to shift
how one gains business insight and how data is managed and delivered in the
world of modern business intelligence.
White Paper
Chuck Whittemore &
Steve Hughes
“...businesses need to be exceedingly responsive to changing
consumer trends, new regulations, and other external
demands. A BI Solution must be timely to be relevant. ”
State of the BI World
Without user input into the design, there’s no way to make sure
The current world of BI seeks to achieve the data warehousing’s
doesn’t do what it is supposed to do, or because of unforeseen
Holy Grail of a “single source of truth.” Traditionally, this
barriers to adoption. The challenge is to get those busy business
involves fairly complex projects that must move and transform
people to invest their time up front, giving the input needed to
data to meet the needs of the data consumer. Often, data
produce the application that will return the investment many
is structured around Kimball’s Dimensional Model which
times over in the form of fast, efficient, leading-edge BI.
organizes measurable facts and descriptive dimensions into a
Project Visibility
star schema. In order to achieve this level of simplicity in the
model, a business intelligence or data warehousing project
must move, transform, and reorganize data from a variety
of sources. Not only is this a time consuming process, but
it is notoriously complex. Only after this process has been
completed are users able to interact with the data.
they get what they need. The solution may stand idle because it
In a highly competitive and often global marketplace,
businesses need to be exceedingly responsive to changing
consumer trends, new regulations, and other external
demands. A BI Solution must be timely to be relevant. We have
seen many situations where the enterprise data warehouse
(EDW) is no longer relevant, yet still being maintained.
Historically, projects would span years before being available to
Business Input Required
the business and the first analysis performed, but that model
Solution builders depend on business users to help them
has changed over the last few years. In the process of trying
understand and process requirements for a project. The quality
to resolve the issues of scope and duration, BI architects use
of this communication is often the determining factor between
agile type methods and iterative development to minimize
success and failure of a BI solution. Most often, the business
the impact of change to the solution. This allows delivery of
users and solution builders struggle to adequately translate
solutions that meet user needs more quickly than in traditional
business needs into a usable solution design. But here’s the
builds.
“While the value of a well-designed,
properly implemented BI solution is
well understood, the actual cost of
BI projects is more than is reflected
on the books.”
However, changing the deployed structures in a data
warehouse to reflect a changing business is still a costly
investment in time and resources.
Costly Barrier to Entry
While the value of a well-designed, properly implemented BI
solution is well understood, the actual cost of BI projects is
more than is reflected “on the books.” For many organizations,
interesting twist: where in the past these communications were
these costs represent a formidable barrier to achieve usable
between business and technical folks, they are increasingly
information from their BI solutions. That first step is way too
happening within business teams. New communications
high.
patterns are emerging to enable these discussions.
2 | The Changing World of Business Intelligence: Leading with Microsoft Excel
The user wants and needs their information turned into data
Data architects build complex ETL solutions to pull data from
they can use. How do we help them overcome this cost hurdle?
their points of origin into a central repository called a data
Changing Our Starting Point
warehouse. What if we could change this paradigm? What if
the users closest to the meaning and value of the data build the
Traditionally, BI projects start with intensive
model? That is not only a possibility, but is quickly becoming
requirements gathering sessions including
the new normal.
interviews and meetings. Once those
requirements are gathered,
“The key difference: the insight is
delivered at a fraction of the cost
and time.”
they must be translated
into the data
model.
PowerPivot for Excel
Microsoft introduced PowerPivot for Excel with Excel 2010.
PowerPivot is a SQL Server add-in which expands Excel’s
capabilities with an in-memory engine called xVelocity InMemory Analytics Engine (xVelocity). PowerPivot gives the
Excel user the ability to pull data from a variety of data sources
into Excel. Once in PowerPivot, traditional data cube functions
such as relationships, calculated members, and calculated
measures can be used to organize the data for use in Excel.
With its impressive compression and indexing capabilities,
xVelocity performs traditionally difficult and time intensive data
tasks with ease. Operations such as sorting and filtering are
now easily done against data tables with millions of rows. All of
this is possible without leaving the comfort of Excel.
“What if the users closest to the
meaning and value of the data
build the model?”
PowerPivot in Excel is our starting point. By making data
available to our users, they will put together a data model
which meets their needs. They are able to pull data from
OData feeds, SQL Server Reporting Services reports, SQL
Server databases, Oracle databases, Teradata databases, Excel
workbooks, and even other PowerPivot models. Then, the
user can create the relationships and build a data model that is
Data
Warehouse
Data
Cube
BI
Nirvana
right for the job . In this scenario, the desired result drives the
underlying implementation, not vice versa.
3 | The Changing World of Business Intelligence: Leading with Microsoft Excel
“Excel is the cornerstone for creating and delivering business
insight within your organization.”
Risk and Reward
true as well, as shown by the success of these products.
By putting this type of power into the hands of business
users, there is opportunity for mistakes. These mistakes are
Building the Solution
magnified when business decisions are based on user-created
Let’s begin our exploration of this paradigm shift toward user
data analysis. But this isn’t new – spreadmarts with cobbled
empowerment with a discussion of Excel. How does that
together data and inaccurate formulas have long persisted
translate into a BI solution? Isn’t this just spreadmarts all over
within organizations. The difference is that with training
again? How do we prevent this from becoming an ad hoc file
and greater familiarity with the data, these users will begin
share nightmare? By strategically planning for growth in users
to produce the business insight that the data warehouse is
and content, you can build a solution that will meet the needs
designed to deliver. The key difference: the insight is delivered
of the organization while still empowering your users.
at a fraction of the cost and time.
The Power of Excel
Although enabling users to create their own insights may
Excel is the cornerstone for creating and delivering business
appear risky, tools such as Qlikview and Tableau have delivered
insight within your organization. All BI components including
the same capabilities since they arrived on the scene. These
reports, dashboards and pivot tables originate within Excel,
in-memory analysis tools are designed to be used by business
and in particular, within PowerPivot. With the latest version of
users to perform analysis of disparate data sets. The reward is
PowerPivot, users can create the data model visually using the
worth the risk. It appears that the market perceives this to be
diagram view.
4 | The Changing World of Business Intelligence: Leading with Microsoft Excel
It is clear that Microsoft’s strategy with BI is to enable business
changes this dynamic by being a place where the technical
users with tools and capabilities once reserved for technical
person and the business person can come together as equal
resources to:
partners in delivering BI solutions.
• Access data from a wide variety of sources (including OData)
• Create relationships within the data
• Identify and remove “dirty” data
• Create dimensional data models
• Publish data models to SharePoint
• Create relevant and timely analysis and visualizations
Excel, with PowerPivot, now provides all of these capabilities,
without requiring any technical resources. Let that sink in
for a moment. Some people may claim heresy, some may be
skeptical, while others may say, “It’s about time, why didn’t
we do this from the beginning?” Whatever your position,
the reality is that this empowerment movement is already
underway.
A Point of Convergence
Sharing with SharePoint
Picture this scenario: your Excel-savvy power user creates
a PowerPivot workbook that the entire finance department
wants to use. She comes to you and asks about sharing her
spreadsheet. Should she email it? Put it on a fileshare? What do
you tell her?
You tell her: publish the PowerPivot workbook into SharePoint.
PowerPivot paired with Excel Calculation Services in SharePoint
provides some additional capabilities such as scheduled
data refreshes. This is not just a file in a library – it is a fully
functional, in-memory data model. Once a PowerPivot
workbook is loaded into SharePoint, Excel Calculation Services
converts it to the server supported version using SQL Server
Analysis Services. Not only does this allow users to share the
uploaded workbook online, the uploaded PowerPivot workbook
becomes a data source for reports and other data visualization
In the good old days of BI, a technical person had all the
tools that can connect to a SQL Server Analysis Services (SSAS)
sophisticated tools and access to data and the business person
database (aka data cube).
had Excel with very limited access to ‘real’ data. PowerPivot
There are a couple of restrictions with this solution. First, the
5 | The Changing World of Business Intelligence: Leading with Microsoft Excel
“A key difference in the world of modern BI is that data will be enhanced
or turned into information by the users and not by the data warehouse
architects. And that is exactly where insight should happen.”
compressing data, the set of data can eventually outgrow 2GB.
Rethinking the Role of the
Data Warehouse
Furthermore, the data refresh is done at the table level. This
As you can see from the previous sections, the data warehouse
means that large tables can be painful to refresh as all of the
is not what it used to be in this scenario. Does a data
data in the table is removed and reloaded.
warehouse still fit into this model of BI delivery? In a word,
Converting to Tabular
Yes. The data warehouse needs to take on a new role. More
workbook can only be 2GB. This is due to how SharePoint
manages files. While PowerPivot does a great job of
When the PowerPivot model has exceeded its 2GB size
limitation in SharePoint or more sophisticated data
requirements are needed such as partitions or more granular
security, the PowerPivot model can easily be transformed into
precisely, the nature of the data changes - data warehouses
have always been places to put data for use by users. Data
warehouses, operational data stores, and data marts serve
a number of purposes for the current design pattern. The
new goal of a data warehouse should be to surface data
a Tabular model in SQL Server 2012 Analysis Services. The
for users to consume with PowerPivot. With the powerful
Tabular Model was added to the SQL Server Analysis Services
capabilities within PowerPivot to handle various data sources
offering in addition to the Multidimensional Model (e.g. data
and relationships, data warehouses will likely need to focus
cubes). The Tabular Model is an in-memory data store built on
on “edge” cases or specific scenarios where more direct
the same xVelocity engine that is in PowerPivot. If size is the
data access will not work such as complex ERP data sources
only consideration, SQL Server 2012 Data Tools supports the
or sources with sensitive data. A key difference in the world
Import from PowerPivot option to create the tabular model
of modern BI is that data will be enhanced or turned into
and you are ready to go. If you need to implement partitioning
information by the users and not by the data warehouse
for more selective data refreshes or you need more granular
architects. And that is exactly where insight should happen.
security, some modification of the model will be required
after the model has been imported. Beyond scalability or size
limitations, the Tabular Model adds management flexibility,
more granular security, and the Visual Studio development
platform.
Going Beyond xVelocity
While the role of the data warehouse as the primary store of
accessible corporate data is diminishing, the data warehouse
will still be a valuable asset for many companies. PowerPivot
highlights gaps in data needed to gain additional insight, which
serves as business justification for investments in enterprisegrade solutions and platforms such as data warehouses. A
Now imagine that the PowerPivot workbook originally created
key change in the approach to data within an organization is
by the business has grown well beyond its starting point. What
to scale the data warehouse only large enough to get started.
are the next steps? The traditional world of BI is still at your
Communication of requirements at a high level to support a
disposal and now you have a data model from which to build.
scaled-down data warehouse takes a fraction of the time that
Cubes, data marts, and even data warehouses can be generated
it does to plan for everything. If you omit critical needs in the
from the models or modifications of the models created in
early stages of developing the data warehouse you can revisit
PowerPivot. But the real question is, should you?
these needs later with a much better-informed perspective.
6 | The Changing World of Business Intelligence: Leading with Microsoft Excel
When the PowerPivot author needs content added or modified
and pivot tables. And that is just the beginning. Interactivity
in the data mart, they are capable of providing very precise
is key to the updated user experience in Excel 2013 including
requirements. This in turn reduces the time required to
the Timeline Filter, Quick Explore and Quick Analysis to name
implement the change in the data warehouse.
a few. GeoFlow is a 3D mapping add-in that is currently in
“Microsoft has positioned Excel as
the de facto standard BI tool.”
Excel Isn’t Just for Data
preview. GeoFlow allows you to create very cool geographic
representations of data that actually “move” through time.
Power View is also embedded in Excel 2013. This breakthrough
visualization tool from Microsoft supports mapping, time series,
and highly interactive analysis.
Excel is no longer just a spreadsheet. With very little work,
These tools are not limited to the desktop. Once deployed
users can create dashboards, interactive analytic visualizations,
to SharePoint 2013 or Office365, data consumers can use
and even reports using Excel. With the release of Excel 2013,
these online without any concerns about capabilities on their
Microsoft has unleashed a premium BI data visualization tool
desktop. This solution also works as you first step into mobile BI
that everyone already knows how to use. As stated previously,
as SharePoint and Office365 expose Excel Web Apps to tablets
Microsoft has positioned Excel as the de facto standard BI tool.
such as the Windows Surface RT and the Apple iPad.
This is a wise move considering the massive adoption of Excel
within organizations.
Excel supports slicers, charts, conditional formatting, sparklines,
7 | The Changing World of Business Intelligence: Leading with Microsoft Excel
“This is how we see the modern BI
solution taking place: even with a
data warehouse, the primary goal is
to get the data into Excel...
When IT enables business, everyone
wins.”
A Glimpse into the Future
As we look into the future, Microsoft is “all-in” with Excel as
the enterprise BI client. As a result, we should expect to see
continued innovations in the product. This includes various
preview products such as Data Explorer and GeoFlow. With the
Now What?
This is how we see the modern BI solution taking place: even
introduction of Office and SharePoint Apps, more visualization
with a data warehouse, the primary goal is to get the data
and data tools will be made available through the Office Store.
into Excel. In the end, data is in the warehouse; information is
Data Explorer
in Excel. As data warehouses continue to age without grace,
What about ETL for Excel? While currently in preview, this addin allows you to discover data in the public arena and retrieve
data from additional data source types such as Hadoop. But
what makes it truly powerful is its capability to “shape” data.
Data Explorer uses steps to manipulate the data that is being
more people are going after the operational data directly.
Traditionally, requesting reports in large IT institutions requires
opening projects, planning, and weeks to months of labor to
complete. Give it to me in Excel, and I can show my boss what
she needs in hours.
requested prior to the data landing in Excel. This includes
The role of IT will be that of data providers more than report
adding columns, changing and separating data, and various
writers or information builders. Developers and data teams will
other transformation tasks usually reserved for ETL tools such
create more ways to access the data that meets their needs
as SQL Server Integration Services. While not as powerful as
for security and performance. Timely and valuable insight is
SSIS at this time, this capability allows the business user to
delivered quickly at a much lower cost, resource and time
create an ETL process to load the data.
profile by enabling the people who best understand their data
Apps for Excel
The Office Store already has a number of apps to support
to harness the power within Excel 2013. When IT enables
business, everyone wins.
Excel. In particular, gauges and map visualizations are available
for free. Other tools exist to help with data such as a time
dimension loader for PowerPivot which creates a PowerPivot
table with date data. The store is still fairly new, but each
day more apps come online. Using .NET and JavaScript, users
are able to create and deploy apps that are relevant to their
business to the public Office store or even internally to the
Excel
2013
business’s SharePoint Farm.
8 | The Changing World of Business Intelligence: Leading with Microsoft Excel
SharePoint
Tabular
Model
BI Nirvana
Data
Warehouse
References
Additional Resources
1
“Kimball Core Concepts - Kimball Group.” Kimball Group |
Dimensional Data Warehousing Experts. N.p., n.d. http://
www.kimballgroup.com/data-warehouse-and-businessintelligence-resources/kimball-core-concepts/.
Microsoft SQL Server PowerPivot Planning &
Deployment. http://technet.microsoft.com/en-us/library/
ff628113(v=SQL.100).aspx
2
“Agile BI -- TDWI -The Data Warehousing Institute.” TDWI
-The Data Warehousing Institute. N.p., n.d. Web. http://tdwi.
org/portals/agile-bi.aspx.
3
“xVelocity in SQL Server 2012.” MSDN – the Microsoft
Developer Network. N.p., n.d. Web. http://msdn.microsoft.
com/en-us/library/hh922900.aspx.
4
PowerPivot Compression Resources
PowerPivot (DW)---a Technical Reference Guide for Designing
Mission-Critical DW Solutions. http://technet.microsoft.com/
en-us/library/hh393583.aspx
Create a memory-efficient Data Model using Excel 2013 and the
PowerPivot add-in. http://office.microsoft.com/en-us/excelhelp/create-a-memory-efficient-data-model-using-excel-2013and-the-powerpivot-add-in-HA103981538.aspx
“PowerPivot – Compression Stat | gavin online.” gavin
online | Just another WordPress.com site. N.p., n.d. Web.
http://gavinrussell.wordpress.com/2010/06/30/powerpivotcompression-stat/.
“Create a memory-efficient Data Model using Excel 2013 and
the PowerPivot add-in - Excel - Office.Microsoft.com.” Office.
Microsoft.com. N.p., n.d. Web. http://office.microsoft.com/
en-us/excel-help/create-a-memory-efficient-data-modelusing-excel-2013-and-the-powerpivot-add-in-HA103981538.
aspx.
“Surprising Example of PowerPivot Compression «
PowerPivotPro.” PowerPivotPro. N.p., n.d. Web. http://
www.powerpivotpro.com/2010/02/surprising-example-ofpowerpivot-compression/.
5
“SQL Server 2012: Tabular Models vs PowerPivot Models
| James Serra’s Blog.” James Serra’s Blog. N.p., n.d. Web.
http://www.jamesserra.com/archive/2012/03/sql-server2012-denali-tabular-models-vs-powerpivot-models/.
6
“Store - Office.com.” Office.Microsoft.com. N.p., n.d. Web.
http://office.microsoft.com/en-us/store.
9 | The Changing World of Business Intelligence: Leading with Microsoft Excel
About the Authors - Chuck Whittemore and Steve Hughes
Chuck Whittemore and Steve Hughes have over 30 years combined
experience in Business Intelligence and Business Consulting. Steve is the
National Practice Lead for Data and BI at Magenic and Chuck is a Lead
Business Consultant at Magenic. Steve has created BI solutions including
multidimensional databases, reporting solutions, and data marts with SQL
Server, SharePoint and Office. He is passionate about using data effectively
and helping customers understand that data is valuable and profitable.
Chuck has specialized knowledge in business intelligence, business
performance management and SharePoint as a business solution platform.
He has proven experience bridging challenging business opportunities
with effective business solutions. Upon seeing the convergence of Steve’s
technology background and Chuck’s business consulting background, they
collaborated to create this white paper showing how the technology and
business work together to form intelligence and create insight.
About Magenic
Founded in 1995 by the same technical minds that still run the company,
Magenic focuses on the Microsoft stack and mobile application development.
Visit us at magenic.com or call us at 877.277.1044
to learn more or to engage Magenic today.
10 | The Changing World of Business Intelligence: Leading with Microsoft Excel