After a model has been deployed, there are additional considerations for securing model data access, backups, and processing operations that can be configured on the Analysis Services server by using SQL Server Management Studio. While these properties and configuration settings are outside the scope of this topic, they are, nonetheless, very important in assuring your deployed model data is secure, kept up to date, and provide a valuable data analysis resource for users in your organization.
Connect to a Tabular Model Database (SSAS)
Client libraries that provide access to Analysis Services databases can be used to connect to model databases that run on a tabular mode server. Libraries include the Analysis Services OLE DB provider, ADOMD.NET, and AMO.
Excel uses the OLE DB provider. If you have either MSOLAP.4 from SQL Server 2008 R2 (file name msolap100.dll, version 10.50.1600.1), or MSOLAP.5 (filename msolap110.dll) that is installed with the SQL Server 2012 version of PowerPivot for Excel, you have a version that will connect to tabular databases. Use the instructions provided in the following topic to check the version of the MSOLAP data provider on your computer: Upgrade PowerPivot for Excel and PowerPivot Data.
Choose from the following approaches to connect to model databases from Excel:
- Create a data connection from within Excel, using the instructions provided in the next section.
- Create a BI semantic model connection (.bism) file in SharePoint that provides redirection to a database running on an Analysis Services tabular mode server. A BI semantic model connection file provides a right-click command that launches Excel using the model database that you specified in the connection. It will also launch Power View if Reporting Services is installed. For more information about creating and using BI semantic model connection files, see Create a BI Semantic Model Connection to a Tabular Model Database.
- Create a Reporting Services shared data source that references a tabular database as the data source. You can create the shared data source in SharePoint and use it to launch Power View. For more information, see Create a Shared Data Source for a Data Model (SSRS).
After you set permissions in Analysis Services, you can return to SharePoint and create a BI semantic model connection.
- In the library that will contain the BI semantic model connection, click Documents on the SharePoint ribbon.
- Click the down arrow on New Document, and select BI Semantic Model Connection File to open the New BI Semantic Model Connection page.
- Set both Server and Database properties. If you are unsure of the database name, use SQL Server Management Studio to view a list of the databases that are deployed on the server.Server name is either the network name of the server, the IP address, or the fully qualified domain name (for example, myserver.mydomain.corp.adventure-works.com). If the server is installed as a named instance, enter the server name in this format: computername\instancename.Database must be a tabular database that is currently available on the server. Do not specify another BI semantic model connection file, an Office Data Connection (.odc) file, an Analysis Services OLAP database, or a PowerPivot workbook. To get the database name, you can use Management Studio to connect to the server and view the list of available databases. Use the property page of the database to ensure you have the correct name.
- Click OK to save the page. At this point, the PowerPivot service application will verify the connection.Verification succeeds if the connection information is correct, and you have granted administrative permissions to the PowerPivot service application so that it can connect to Analysis Services as the current user.Verification fails if the connection information is wrong, or the service application lacks permissions. A validation message will appear on the page asking whether you want to save the file. If you know that the connection is valid, you should save the file anyway, because the error is the result of missing permissions rather than invalid connection information.You can verify the connection by using it in Excel or Power View to connect to tabular model database. If the data source connection succeeds, the connection is valid despite the verification warning.
Detailed OLAP connection strings.
Detailed instructions to create a Shared Data Source.
When you run a report from a SharePoint library, connection information can be defined inside the report or in an external file that is linked to the report. If the connection information is embedded within the report, it is called a custom data source. If the connection information is defined in an external file, it is called a shared data source. The external file can be a report server data source (.rsds) file or an Office Data Connection (.odc) file.
An .rsds file is similar to an .rds file, but it has a different schema. To create an .rsds file, you can publish an .rds from Report Designer or Model Designer to a SharePoint library (a new .rsds file is created from the original .rds file). Or, you can create a new file in a library on a SharePoint Site.
Create a shared data source (RSDS file)
You can create shared data sources on a SharePoint site with SQL Server 2012 Reporting Services (SSRS) SharePoint mode.
- In a SharePoint document library, click the Documents tab > New Document >Report Data Source.
Note If Report Data Source isn’t on the New Document menu, the report data source content type isn’t enabled. The SharePoint site administrator needs to Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).
- In Name, enter a descriptive name for the RSDS file.
- In Data Source Type, select Microsoft BI Semantic Model for Power View.
- In Connection String, specify a pointer to the data source and any other settings that are necessary for establishing a connection to the external data source. How you connect to a data model depends on how it is published or deployed. Here are examples for:
- An Excel workbook in Shared Documents on a SharePoint site with the SQL Server 2012 SQL Server 2012 Reporting Services (SSRS) add-in—for example:
Data Source=”http://<SharePointSite>/Shared Documents/MyExcelWorkbook.xlsx”
Note In SharePoint 2013, if the report and workbook are in the same farm, then you don’t need the PowerPivot for SharePoint add-in. If they’re in different farms, then you still need the add-in in the farm hosting the workbook.
- An Excel workbook in the PowerPivot Gallery on a SharePoint site with SQL Server 2012 PowerPivot for SharePoint and SQL Server 2012 Reporting Services (SSRS) add-ins—for example:
Data Source=”http://<SharePointSite>/PowerPivot Gallery/MyExcelWorkbook.xlsx”
- A tabular model deployed to a SQL Server 2012 Analysis Services (SSAS) instance on a server—for example:
Data Source=ServerName\InstanceName; Initial Catalog=TabularModelName
Note For a default instance, you can leave out “\InstanceName”.
- In Credentials, specify how the report server obtains credentials to access the external data source. For Power View, credentials need to be stored or integrated. Read Credential and Connection Information for Report Data Sources.
- Click Test Connection to validate the data source configuration.
- Click OK to create the shared data source.
Create a BISM connection file
You can create Business Intelligence Semantic Model (BISM) connection files on a SharePoint site configured with the SQL Server 2012 PowerPivot for SharePoint add-in. BISM connection files can connect to Excel workbooks or SSAS tabular models.
Use a BI Semantic model to connect.
If you are connecting to a tabular model database that runs on an Analysis Services server, SharePoint permissions are not sufficient. You must also have database read permissions on the server. This step should have been performed when you created the BI semantic model connection. For more information, see Create a BI Semantic Model Connection to a Tabular Model Database.
For ad hoc report design in SharePoint, the user who creates the report must have SharePoint permissions on the BI semantic model connection (.bism) file and on the business intelligence semantic model database. The security context of the connection is the interactive user who is creating the report.
This section explains the connection behavior between various client applications, such as the Excel desktop application or the Power View reporting client on SharePoint, and a tabular model database inside or outside the SharePoint farm.
Connecting from Excel to tabular data on a network
When an Excel user specifies a BI semantic model connection as a data source, the connection information inside the .bism file is downloaded to the client application, which then issues its own direct request to the tabular model database on Analysis Services. To access the .bism connection, the Excel user must be a SharePoint user with read permissions on the .bism connection file. Once the connection information is downloaded, all subsequent connections bypass SharePoint, flowing directly from Excel to the backend tabular model database.
Connecting from Power View to tabular data on a network
When a SharePoint user clicks on a BI semantic connection in a document library, Power View (if it is installed), starts immediately and opens a connection to the tabular model database.
Connections between Power View and a tabular model database follow a double-hop authentication sequence where the user identity is flowed from the client to SharePoint, and then from SharePoint to a back-end Analysis Services tabular model database that runs outside of the farm. The ADOMD.NET client library that handles the connection request always tries Kerberos on the first attempt. If Kerberos is configured, the user identity is impersonated on the connection to the tabular model database, and the connection succeeds.
If Kerberos is not configured and the request fails, Reporting Services makes a second attempt. Under this scenario, the client library connects to Analysis Services using the Reporting Services service identity and NTLM authentication. The identity of the Power View user is passed on the connection string using the effectiveusername parameter.
Only a member of the system administrator role on the Analysis Services instance has permission to make a connection using the effectiveusername parameter and impersonate another user on the server instance. For this reason, the execution account of the Reporting Services shared service must have administrative rights on the Analysis Services instance. Instructions for granting administrative permissions to the service account is provided in this topic, Create a BI Semantic Model Connection to a Tabular Model Database.