Database Reference
In-Depth Information
• It can be a BISM connection file (
.bism
) that is published in a SharePoint
library (which has the BISM connection file content type) in which the
connection is pointing to either one of the following:
° A database running on a SQL Server 2012 Analysis Services tabular
mode server (which can use only Windows authentication and not
stored credentials).
° A PowerPivot for SharePoint workbook. Note that an embedded
PowerPivot database inside an Excel workbook is the equivalent of a
tabular model database that is run on a standalone Analysis Services
tabular mode server. You can use workbooks that are created using
either the SQL Server 2008 R2 or the Microsoft SQL Server 2012
versions of PowerPivot for Excel.
You can also open the previously mentioned BISM connection file in Excel as an
ODBC file. If you do this, Excel will open a workbook that contains a PivotTable
field list that is populated with fields from the underlying data source.
PowerPivot for SharePoint requires Excel Services and also requires that you
install SQL Server PowerPivot for SharePoint (which installs the "PowerPivot for
SharePoint" server mode in SSAS). A benefit when using the tabular model is that
a PowerPivot for SharePoint model workbook is able to use many different data
sources, such as Microsoft Access, SQL Server 2008, SQL Azure, multidimensional
databases, Excel files, text files, and so on. When using one of these sources, a SSAS
tabular cube is created behind the scenes. So, the tabular model acts as a bridge, or a
semantic layer, between the complexities of the data sources at the backend and your
perspective of the data.
Be aware that when the tabular model connection uses a PowerPivot for SharePoint
workbook, you are using the saved data in that workbook and are not connected to
the source of that data. For example, if you are using a Microsoft Access database as
a source for PowerPivot, when you save the PowerPivot workbook to SharePoint,
you in fact are using a static copy of the data from the Microsoft Access database that
was imported into PowerPivot. Then, when you are using Power View connected to
this PowerPivot for SharePoint workbook, you are using that static data and are not
connecting to the Microsoft Access database until you refresh the PowerPivot model
either manually or using a scheduled refresh.
