A Simple Tutorial on PowerPivot (4)

5. Publishing and Sharing Reports

After doing analysis on the data, it is the time to share the reports and data to other business users. As mentioned early in this tutorial, it is very important to use SharePoint to create a BI collaboration environment in order to protect the Self-service BI from being a pure spreadsheet mart.  

Before publishing PowerPivot workbook to SharePoint, it is normal to consider "beautify" the workbook so that other users find it useful to read and use the report.  For example, it is useful to remove or hide worksheets that are not part of the report, to improve the placement and appearance of the worksheets, or even turn of the grid lines to make it better for using. Another important thing to do is about the configuration of data refresh. The connections that you have at your local PowerPivot for Excel require certain data connection providers, and these connections, after being uploaded to SharePoint, still require the same data connection providers or at least require a re-configuration of the data connections. So it is important to install (or ensure that they are installed) the relevant data connection providers at the server.

When publishing PowerPivot workbook to SharePoint. There is an option to turn on document versioning in SharePont 2010 site. However,  you should be really careful when deciding this option. After turning on the versioning settings, every change to the PowerPivot workbook is saved to the content database. That means many versions (at almost the same size) of the PowerPivot workbook is saved to the content database. This can make the system heavy loaded after some time.

To load the workbook to SharePoint, choose to "Save & Send" in the Excel Ribbon or just upload the workbook to the site. You should use the PowerPivot Gallery at SharePoint to keep the PowerPivot workbooks.

At the SharePoint server side, you must make sure that relevant features and system services are installed in order to use PowerPivot. To be more specific, Excel Web Access, Excel Calculation Services, PowerPivot System Services, and SQL Server Analysis Services are involved when users interact with published PowerPivot workbook at PowerPivot gallery. Here one can have the same "slicing-and-dicing" , "soring and filtering" user experiences as the PowerPivot for Excel).    As illustrated in the example below,  one can still work with the “slicing-and-dicing” feature inside the web page open by Internet Explorer.

The PowerPivot Gallery is designed for end-users of Self-service BI to use the PowerPivot. So any user of the workbook, with provided access, can re-use the data and even do further analysis based on it. One can click on a specific sheet in the PowerPivot workbook to further analyze the data with given features from Excel Services.

The PowerPivot Gallery has three standard views, i.e., Gallery View, Theatre view, and Carousel view.  The following picture shows the differences of these views. 

After a PowerPivot workbook is published at SharePont site, users of the published workbook can create their own reports based on the PowerPivot workbook data (meaning that the workbook can be used like a data source). When you hover on the icons of the workbooks, you can see a option to create new report. Then there are two options available, Open New Excel Workbook and Create Report Builder Report.  Below is an example.

When you create an Excel Workbook by using the PowerPivot workbook data, the data is provided from the PowerPivot workbook (in SharePoint) in the same ways as provided from an Analysis Service database.

When using Report Builder to create reports based on PowerPivot workbook data, the data is treated as a data connection just like data connections.

Another important work when sharing your PowerPivot report in SharePoint is to consider refreshing data at a PowerPivot workbook stored in SharePoint. You can easily manage the data refresh in the PowerPivot Gallery. The PowerPivot data refresh feature currently does not support data refresh within a day (shame, isn't it). But you can perform an "immediate" data refresh by manually initiate a data refresh command.  And you can also configure the SharePoint to send you an email notification after data has been refreshed.  One thing to remember, if you use linked table in PowerPivot workbook, it will not have any data refresh. Below is an example page of data refresh configuration.

6. Conclusion

This Tutorial is made to describe the key features in PowerPivot in simple words. Apparently one can look into the Microsoft site or Youtube to look for more insights or "live-actions" on these features. 

Hopefully this tutorial can be help for someone looking to start using PowerPivot. Good luck and enjoy the world of Self-service Business Intelligence!

A Simple Tutorial on PowerPivot (3)

 4. Analyze with PowerPivot

The key feature that makes Excel an ideal tool for business analysis is the PivotTable and PivotChart capabilities. Now with Excel 2010, the “slicer” capability (as illustrated below) is also added so that the interactivity is further improved.

PowerPivot provides a set of options to create PivotTable and PivotChart (as illustrated below). Details about each option is out of scope of this tutorial. It is very similar to Excel with more enhancement on slicer and “measures” which can be created by DAX.

One important option, is to create “Flattened PivotTable”. One usage of this option is to print out the data. Another way is to convert the result PivotTable to Excel Formulas so that one can apply Excel functions on the result table.  The option is illustrated below.

It is important to note that the “Field List” for PivotTable or PivotChart under PowerPivot workbooks is different from what it was for other data in Excel. The following picture is an example. The left one is a typical PivotTable Field List while the right one is what you can see in the PowerPivot Field List.

Another interesting feature in PowerPivot is Automatic Relationship Detection. As illustrated below, PowerPivot is being active to detect any potential relationships among tables and fields.

As the core part of data analysis, defining and using measures if always compulsory. PowerPivot provides full support through DAX expressions. Besides the implicit measures which are created while dropping things into the Value area in Pivot chart or Pivot table design. One can also create explicit measures by explicitly specify the DAX expression. To begin creating a measure, right-click on a table name in the PowerPivot field list and select Add New Measure (as illustrated below).

And defining DAX measures requires to use the DAX functions. As illustrated below, the measures are defined in a similar way to Excel formulas.

What to be careful with here is that you should not thing in “rows” when measure values are defined here. And this measure must be defined with an aggregation function (such as sum, count, average, etc.) that will return a single value. In such sense, this is a bit different from the definitions of some calculated columns. This is how it looks like after this measure is defined on the PivotTable.

One more exciting feature to mention, Excel 2010 allows users to create “Named Sets” when using PivotTables (illustrated below). This link provides more detail about this feature: http://blogs.office.com/b/microsoft-excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

PowerPivot can also work with Named Sets in Excel.

A Simple Tutorial on PowerPivot (2)

3. Enriching the Data

After importing data, the next natural thing to do is to integrate and enrich the data. There are following ways to enrich data.

First, it is possible to change the actual data types for imported columns. As illustrated below, after choosing a column in a PowerPivot table, one can choose the “Data Type” option in the ribbon to convert the data types.

Compared to changing data type of columns, a second approach is to just change the way data looks like in the table. That is done by re-“formatting” the data which only changes the “view” of data without converting the underlying data type. Formatting changes in PowerPivot are applied to an entire column. This is illustrated below. 

One of the key features of PowerPivot is to create relationships among tables. As illustrated below, it is possible to set up relationships after the PowerPivot tables are imported. The “Create Relationship” option at the “Design” ribbon in PowerPivot window allows one to set up relationship between tables. Due to the limitation of the current version, relationships can only be defined on single columns (as shown below). This means that if you want to establish two distinct relationships between two tables, you must import another instance of the table. It is a bit similar to using SQL to define join statements that use multiple columns in the same table. So users cannot expect to define complicated relationships in PowerPivot. The “Manage Relationship” button shows all the defined relationships of all the imported PowerPivot tables. 

A very existing feature in PowerPivot is that one can define ”measures” (like in Cubes) using the DAX formulas. This includes calculated columns (which are materialized into the table) and measures explicitly defined (which are kept virtual until called). Below is an example of a defined calculated column in a PowerPivot table. The DAX formula in the Fx textbox seems very similar to Excel. But the main difference is that this definition covers a whole column “TotalSales” instead of a single cell in Excel. 

A little bit more about DAX. DAX in fact has a total of 135 functions to help with data analysis. About 80 functions are supported by Excel. DAX also provides functions that operate on tables.The DAX functions can be categorized into eight categories, i.e., Date and Time, Information, Logical, Math and Trigonometric, Statistical, Text, Filter, Time Intelligence. Similar to math and trigonometric functions , only a subset of Excel’s statistical functions is supported by DAX in the current release of PowerPivot. When typing DAX formulas, PowerPivot provides the “autocomplete” feature. One needs to press the Tab key to use this feature.

Like Excel, PowerPivot window provides the ”Sorting” and ”Filtering” options at the “Sort and Filter” Section, and the “Freeze” option at the “View” Section for users to refine the data. This is illustrated below. The column width can also be adjusted in a similar way to Excel.

Another useful feature for improving the view is to use ”hide” and ”unhide” options. As illustrated below, it is possible to hide columns from PowerPivot or PivotTable or both.

After data is loaded to PowerPivot, the “Refresh” button can be used to retrieve the latest source data to PowerPivot. As illustrated below, it is possible to refresh the current table or refresh all tables in PowerPivot. When clicking the “refresh” button, what happens behind the scene is that PowerPivot sends two commands to the VertiPaq engine when you click Refresh:

• The first command is to “Alter”, i.e., to make any metadata changes you have made in the PowerPivot Window.

• The Second command is to “Process”, i.e., to refresh the data from external data sources and any calculations that depend on the refreshed data.

A Simple Tutorial on PowerPivot (1)

1. Introduction

Using PowerPivot is pretty easy. In general, four steps are involved. 

• First, you need to get the data to PowerPivot. This is about importing and doing a little preparation on the data. Data can be brought from relational databases, Analysis Service databases, PowerPivot that have been published at SharePoint sites, Excel and Text files, data feeds (ops, something new from SharePoint), through Copy-Paste actions, linked worksheet, etc.

• Second, after taking everything into PowerPivot, you need to enrich the data through various ways, such as filtering some rows, hide/unhide some columns, formatting columns (or change data types), create calculated columns through DAX definitions, and most important of all, defining relationships among different PowerPivot tables.

• Third, with all data ready to use, it is the time to analyze the data.Here is the time to use DAX to create measures and more Excel functionalities to find out interesting results.

• Forth, and the most important of all, create and share your reports.This is about showing and sharing your reports through SharePoint.

This tutorial has four parts where each part introduces one of the above steps.

Let’s start with the first step.  

2. Data Importing and Preparation

To bring data to PowerPivot is similar to bringing data to SQL Server Express. You will have to be able to connect to different data sources. PowerPivot requires database providers and, in some cases, client libraries in order to be able to import data from these sources. In most cases, the database providers and client libraries are not provided by PowerPivot for Excel and must be acquired and installed separately.

To import data, the simplest way is to import using Table Import Wizard at the PowerPivot window. There are two options when importing data using the Table Import Wizard (so you should open PowerPivot window and click “Table Import Wizard”.

1. Select from a list of tables or view

2. Specify a SQL Query. 

If you choose “Select from a list of tables or view”, the following picture shows the result.

The button “Select Related Tables” gives a big help if you know one of the tables (and have selected it” and wants to make sure that all other relevant tables are included.”

For the selected tables, you can use “Preview&Filter” button to decide whether to include or exclude certain columns or rows from the data. The following picture is an example. 

After selecting all the relevant tables and completing relevant filtering options. Click “Finish” button to start the actual importing process. Result of the Importing process looks like the following.

Now, let’s take a look at the second way, i.e., “Specify a SQL Query” when doing the Table Import Wizard. When choosing this option, “Specify a SQL Query”, one can use the “Design” button to go into the Query Designer to specify the SQL Query. The picture below is an example of the SQL query.

 In addition, Table Import Wizard lets you import data from MS Analysis Services (MSAS) and other PowerPivot workbooks. It’s important to note that the result of MSAS query is typically a "cellset" object, which is a multi-dimensional object. But when the data is imported into PowerPivot, it is transformed into a simple table. When you import data from MSAS you won’t have the same interactivity of slicing and dicing as you typically have when using BI client tools, such as Excel connected to an Analysis Services Unified Dimension Model (UDM) cube. Another very interesting feature of PowerPivot is that  PowerPivot can connect to data in a published PowerPivot workbooks at sharepoint sites. This makes the enterprise mash-ups into reality.  It’s worthy to note that you cannot import data from PowerPivot workbooks if they are .xlsx files on a local disk drive.

Besides importing tables, PowerPivot now supports “data feed”. Data feeds is a new type of technology that provides tabular-like data such that users can subscribe to the data in the same way as the RSS feeds in order to receive updated data as the source changes. Data feeds are based on the Open Data Protocol, which uses the Atom Publishing Protocol and its Data Services URI and Payload Extensions.  Data feed can come from SQL Server reporting services, SharePoint List and any generic data feed that supports the OData protocol.

Here is an example when you choose to import “From Data Feeds” in PowerPivot. PowerPivot understood the URL description and import the initial data from the source.

Text Files is another important source of data. Just like importing data in DTS, PowerPivot works with both Delimited text files and fixed-length-field text files. But to work with the fix-length-field files is a little bit tricky. One of the key step here is to define a schema.ini (exact name as this) at the same location as the file.  The contents of the schema.ini file are described in the MSDN topic at  this link

You can also import from Excel files. This is demonstrated below.

There are also other ways to bring data into PowerPivot. First one is to paste data from clipboard (well, you need to copy these tabular data first), as demonstrated below.

Second one is to create ”Linked Excel tables” which keeps the source and target in sync automatically. The following is an example.

And the result of the linked table is shown in the PowerPivot window with a special icon (the one next to the “DimStore” name in the below picture).

 After specifying the import criteria, PowerPivot will start the importing with the progress reports. It is important to note here that although PowerPivot can import data from many diverse data sources. The data types supported in PowerPivot is very limited compared to the big variety of data types at different sources. One of the things to be alerted is when you are importing data under different currency types, PowerPivot will import currency types as general currency types (“general” to the regional format defined by the machine where PowerPivot is running on). This can definitely lead to unexpected results. But you can use the  PowerPivot formatting tools to adjust the columns’ data type, and format after the import is completed.

Basics About Zachman framework

With inspirations from traditional architectural and engineering discipline, John Zachman started the Zachman framework with its first publication in 1987. Zachman framework provides a simple (at least it looks simple) but well-structured approach that supports many ways of architectural presentation using a common vocabulary and viewpoints.

To be more specific, Zachman framework describes a model of an enterprise system from six perspectives, Planner, Owner, Designer, Builder, Subcontractor and the working system.  The major feature in Zachman framework is this matrix with 36 cells (example picture from Wikipedia) indicating all "possible" aspects to an enterprise system.

Here are the rows of the matrix:
Scope, Business Model, System Model, Technology Model, Components, Working system

Here are the columns of the matrix:
Who, when, why, what, how, where

As indicated in the publications about Zachman framework The major principles behind Zachman framework are:

1. "A complete system can be modeled by depicting answers to the following questions: why,
who, what, how, where, and when."
2. "The six perspectives capture all the critical models required for system development."
3. "The constraints for each perspective are additive; those of a lower row are added to those of
the rows above to provide a growing number of restrictions."
4. "The columns represent different abstractions in an effort to reduce the complexity of any
single model that is built."
5. "The columns have no order."
6. "The model in each column must be unique."
7. "Each row represents a unique perspective."
8. "Each cell is unique."
9. "The inherent logic is recursive."

This framework with 36 cells can be used recursively to management the overall enterprise systems and each components inside it. One can build the 36 cells in different levels to keep views of an enterprise system at different levels.  Implementing Zachman framework must recognize two critical challenges in current enterprises.

1. One must beginning making the descriptive representation of enterprise more explicit.  This means to populate the various cells of Zachman framework
2. Formalize a system to keep populate the different cells in a disciplined way.

There can be two different approaches to get a company a quick-start on using the Zachman framework.

1. Start developing the cells with the purpose of developing an enterprise architecture strategy
2. Start developing row 1 of the matrix in order to establish an implementation road map.

As claimed by different parties, one drawback of this framework is that it does not have so much public, open documentation as compared to TOGAF. However, from my own experience, this is also a good part of a framework where certain spaces are left for the enterprise itself to find out and define the discipline.   To many enterprise architects, working by using everything available makes their life easier and simple. While to some other enterprise architects, having more space to dig out valuable works for the enterprise is of more fun and self-satisfying.

Understanding TOGAF

The Open Group Architectural Framework (TOGAF) is considered to be the most often used framework for the future. Here are some notes that I've taken after reading the first introduction of TOGAF. 

TOGAF support the most commonly accepted architecture types, i.e., business (or business process) architecture, application architecture, data architecture, technology architecture. A key concept built-into TOGAF is that it is driven to create an open systems-based architecture so that solutions made at an enterprise are not relying on any single suppliers but focused on ensuring an open and integrated solution such that multiple vendors and heterogeneous systems can be added at any time without a major cost or risk.
 

TOGAF consists of three parts. 1) The TOGAF architecture development method, which explains how to build an enterprise architecture for a specific enterprise in order to fulfill the business requirements. 2) The Enterprise Continuum whch is a repository of all architecture assets such as models, patterns, etc. 3) The TOGAF Resource Base which is a set of resources (templates, guidelines, handbooks, etc.) that facilitates the development of the architecture.
 

In the TOGAF dictionary, "architecture" is often interpreted as "view" of things. For example, there  will be a business view, a data view, or a technology view of enterprise systems. This is quite typical in the enterprise architecture world. Just like the architecture of a building, it is always useful to present the architecture through different "views" to suit the need of different purposes, such as a construction view, a electricity supply view, a networking view, a security view, etc. 

In fact, creating and using views is the one of the key activities in TOGAF. Seeing the enterprise as a whole thing, different people (in other words, the stakeholders) tend to looking at this "whole thing" from different view points (i.e., perspectives) because they are different "concerns" (which is the key interest that defines the acceptability of any systems inside this whole thing). The focus inside TOGAF is to ensure that all necessary viewpoints are identified and satisfied. The following three steps have to be repeated very often.

    - Choose key stakeholders
    - Understand and document their concerns
    - Figure out how one models and deals with those concerns

TOGAF provides a taxonomy of views but it does not mean every enterprise (who employed the TOGAF concept) must develop all items provided. In fact, it is always important to "pick to use" what is beneficial to an enterprise, which is determined by the architect team. One of the key points to ensure the success of making different views to different stakeholders is to ensure that everyone use the same language (i.e., architecture terms, elements, definitions) to communicate.

The Business Case of Self-service BI

Why Self-service BI makes a difference? If we look at the state of BI before, then the answer is quite clear.

• Only trained and highly-paid BI developers can be used to create BI applications, which is highly customized (which means cost of maintenance, further development, and even decommissioning).

• After BI application is built, it becomes difficult (money, time) to implement changes even the business situations and requirements are changed.

• After having 5 different BI applications, you have to have people with specific skills and knowledge to the ground level of these applications. Each system works in its own way. (so what's the cost of maintenance and operating)
• Since it costs too much, many business analysts start to copy data from production systems and make their own analysis from local PCs. Spreadmarts become popular. Everyone becomes a cowboy and key business decisions are made from some charts in a spreadsheet saved at some one's local PC.
• When people are trying to share the spreadsheet files, the whole business is having the risk of losing confidential data without being aware of it.

Given these scenarios, starting a self-service BI can definitely eliminate the above scenarios by bringing lower cost, shorter time-to-market, reduced IT investment, more precise requirement specifications for corporate BI solutions, and more efficient collaborations among the business users.

To be a bit more specific, here is a few points demonstrating the benefits claimed above.

First, most self-service BI tools start by extending Excel or providing a very easy-to-start interface so that the business users always find it simple and quick to start producing reports, sometimes just like what they have been doing with Excel. And self-service BI means the business users have to serve themselves to produce the end result. There is almost no intervention from IT side, not to mention any "waiting time," "resource bottleneck," etc. This dramatically reduce the development cost for many BI solutions at the business team.

Second, since the development of the BI solutions is at the hand of business users, there is absolutely no communication detour between business-IT-business in order to get most work done. Shorter time-to-market  is ensured since the one who needs it is the one who builds it. Who else to complain about it?

Third, the role of IT in this scenario is to monitor the business work and observe the need for creating corporate BI solutions (which is more reliable in terms of maintenance and operation). What's more important is that after doing these many self-service BI solutions, the business is actually making a very excellent prototype for the potential BI solutions that can benefit more. This saved quite much time of requirement analysis, interviews, escalations and much effort made by the solution architects at every project.

Forth, the concept, which is also a key in many self-service BI solution, is that self-service BI must include a collaborative effort to ensure the communications and reusability among the business team and the IT team. This avoids the spreadmart scenario and in fact, provides a chance to maximize the benefit of solutions developed by the power users at the business team.

Oh, one last point to make. What happens to the corporate BI solutions when self-service BI becomes so popular. The answer is, "nothing." We still need corporate BI solution and we still need to develop these solutions through IT and business corporations. But, the difference is, we've got a better overview of what we need and what we want this time ;)

Purpose of having an Enterprise Architecture

Enterprise Architecture is closely connected to an overview of the whole. It does not matter if an enterprise means a large corporation, or a division, or a department. Enterprise Architecture is to keep an eye on the overall interest of this “enterprise.”

So what’s the business case of having an Enterprise Architecture? One thing for sure is that people tend to follow the trace of “standardization” when an EA process is driving. And this leads to a better commoditization of software and systems within an organization, i.e., controllable operating cost and predictable development time. When having an established EA process, people tend to have better communication effects and are able to make design decisions in shorter time (this is extremely important for large organizations where escalations and politics are popular). And, the most important of all, an EA tells the people, especially the leaders, the directions to go, the initiatives to take, and the strategies to rely on.

Perhaps a better way t to look at this is to see the situation when EA is not in place. Without EA, an organization definitely experiences one or more of the followings.

• Many locally optimal, rather than globally optimal solutions exist and cannot removed
• Very high operating cost due to the fact that things are not globally shared and re-used
• Too much dependency on a vendor or a legacy application (no one knows who's using it, no one knows anything about it, no one dares to close it down)
• Most solutions are created for short term purpose and limits the long term benefit

• IT standards are not respected

It is worth noting that even lots of IT functionalities are outsourced to different vendors, EA is still vital and even more critical in such situations. With more than one vendors pressing the CFO for different payments, the EA team must guardian the enterprise to avoid being a cash-cow kind.

Basics for Understanding PowerPivot

I often met the questions about PowerPivot these days. "What is it?" "Why should we have it?" "Can PowerPivot do this for me?" 

Well, the best way is to make a list of most basic descriptions of PowerPivot and share with those who need it. So here they are.

• What is PowerPivot

PowerPivot is a new data analysis technology in a way that it makes the "impossible" usage of EXCEL (in the past) to be possible now. So business analysts do not always have to wait for a few months to see the end-result of IT implementation. They can instead do self-service and make many of the BI solution implementations on their own (self-service).

• Self-service BI compared to corporate BI

PowerPivot  (together with Excel, and most importantly, SharePoint) enables the self-service BI capabilities of a company. But this does not mean the era of Corporate BI is over. Instead, corporate BI will have an even more important status since the business is now able to specify more clearly on their requirements and is even able to provide a prototype plus data and results that can be used to validate the IT implementations.

• PowerPivot is not a standalone tool

PowerPivot integrates with Excel (2010 since it needs the size extensions enabled from this version of Excel) and SharePoint 2010.

• Data Analysis Expressions (DAX)

This is one of the key extensions of PowerPivot (in addition to the in-memory analysis engine and the compression algorithms). DAX is a collection of formula elements, i.e., functions, operators and constants that can be used to make expressions or do calculations. It is also available to the Tabular Model projects.

• Managed BI collaboration environment

Clearly PowerPivot has enhanced the possibilities of spread marts and too many standalone worksheets across an enterprise. That's why it is critical to establish a managed BI collaboration environment before promoting the self-service BI concept. Here exists lots of process, rule, training, and SharePointing work. With a SharePoint site, the business can clearly do the self-service work while the IT department puts on certain audit-trails and analysis to find out the need for optimizations, further maintenance and even development of Corporate BI solutions.

• Technical specifications

There is no doubt that PowerPivot includes a good in-memory database. Here is the technical specification that one should always remember. "....The maximum file size of a PowerPivot workbook is 2 GB. There are no restrictions on the amount of data users can import into a workbook, but workbooks exceeding the maximum file size can’t be saved. A 2-GB workbook typically corresponds to a 4-GB dataset, considering a 2:1 compression ratio.... Performance tests show that PowerPivot for Excel can load more than 100 million rows and maintain adequate processing performance with 2 GB of memory. However, test results vary depending

on the compressibility of the data. For fastest processing performance, Microsoft recommends multi-core processors and more than 4 GB of RAM...." (this is from the document "Microsoft SQL Server PowerPivot Planning and Deployment").

• Key elements in PowerPivot for SharePoint

The Timer service (automatically refresh the data), the PowerPivot web services and the PowerPivot Management Dashboard.

• The best picture that shows the roles in BI at enterprises

Look at this one  (or use the following URL http://i.technet.microsoft.com/dynimg/IC396763.jpg)

• The real key to success

Having a focused effort to delivery training, technical documentation and support to the "Power users," and business users is the real critical factor to the success of self-service BI. A good practice is to making a good knowledge base for common questions and training materials.

• Understand the performance gain and loss

What PowerPivot provides a a great performance if everything is inside your memory. To initially get the data from somewhere, there is a potential bottleneck on the network bandwidth. In that case, do not blame PowerPivot but try to decentralize the data just like reducing the heavy traffic around the central station of your city.

• Security and access

PowerPivot is definitely bring challenges to those who work with data access and security control . As a "fat-client" layer in an enterprise landscape, it is very important to identify the security needs and utilize SharePoint as a mitigation layer.