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!
No comments:
Post a Comment