SharePoint lists are another form of tabular data
that can be useful as a data source. SharePoint lists are quite easy to
create and share with other business users. They also prove useful for
gathering or sharing data in a persisted format.
Caution
SharePoint data sources
always connect to the default list view and will take into account any
filtering or column hiding done in this view. It is not possible to work
around this limitation.
For example, if a column is
hidden in the default view, it will not be shown in the list preview in
Dashboard Designer, nor will it be available as a fact or dimension for a
KPI or filter. In addition, if a filter is applied that obscures some
rows in the list, that data will not be available in Dashboard Designer.
This means that this data will be left out of aggregations.
To connect to a
SharePoint list, you must have access to the list. In the following
example, you connect to the SharePoint list, as shown in Figure 1.
1. | After
you create a new data source based on the SharePoint List data source
template, a screen appears that allows the entry of connection
information to the SharePoint list. On this screen, specify the
SharePoint site that contains the list.
|
2. | Select the list collection and list name from the drop-down menus.
|
3. | Click Test Data Source to ensure that the connecting user has read permission to access the SharePoint list. See Figure 2 for an example of complete settings.
|
4. | Switch
to the View tab to verify that all columns have been configured using
the proper column type and that all facts use the correct aggregation
function. This step applies to all tabular data sources.
Tip
A common problem
when working with existing SharePoint lists is that they were not
designed with BI in mind. When working with SharePoint lists that do not
have true fact data, a useful trick is to create a Count aggregation on
the Attachments column. This will have the effect of counting the
number of rows that are in a given query.
|
5. | Check
to make sure that all the columns are configured properly. There is an
additional SharePoint list metadata column, called Attachments, listed
in the preview. Because you probably are not interested in using that
column, verify that the Attachments column is set to Ignore.
|
Note
It is quite common for
SharePoint lists to contain additional metadata columns that you can
choose to ignore. These columns can either be ignored in SharePoint
Designer or removed from the default view.
The Authentication options, Unattended Service Account, and Per-User Identity behave the same as all other tabular data sources.
