Many customers come to us with questions surrounding the use of BISM files. Primarily why should they use them and how to configure them. In the following I will attempt to answer both questions.
Why:
When you upload a PowerPivot workbook to SharePoint, SharePoint is intelligent enough to create a Data Model under the Analysis Services PowerPivot Instance (If you have an Analysis Services Server in PowerPivot Mode and listed under the Excel Services > Data Model Settings). You now have a Data Model using Microsoft's In Memory X-Velocity technology and all you had to do was upload a PowerPivot workbook to SharePoint! This workbook could hypothetically be up to 2GB in size (SharePoint's file limitation is 2GB) and lets also say you want to create 10 unique reports off that same data. You could re-create 10 identical workbooks in the Excel Rich Client, upload them to SharePoint ,and these workbooks would all have their own Data Models occupying system memory OR you could point these 10 thin (or shim) Excel workbooks to that one already existing Data Model. This is great because you don't have to load 10 individual 2GB Data Models under the PowerPivot Instance.
These BISM files are essentially connection files (like an .odc) to Data Source. If you do not want to grant users certain permission to an actual database, but you want them to be able to build reports in Excel off of data from that database, you can achieve this via BISM files. Users can access this data (Data Model), build reports, and these workbooks will show fresh data daily (as long as you configure a Scheduled Data Refresh to the source PowerPivot workbook).
Lastly, if you click on the BISM file (or choose Create Power View Report), it will load a PowerView Report.
How:
First you need to add the BI Semantic Connection Content Type. To do this, you will need to browse to Library Settings
Click on Advanced Settings
Choose Yes under Allow management of content types?
Click Add from existing site content types
Choose BI Semantic Model Connection and click Add >
Browse to the document library, choose the File tab > New Document > BI Semantic Model Connection
This will open a New BI Semantic Model Connection, name the item and then add the URL of the PowerPivot workbook in SharePoint. In this example, the PowerPivot workbook will be stored in the same location as the BISM file, we merely right clicked on the workbook and chose Properties (http://sp/Shared%20Documents/PowerPivotWorkbook.xlsx):
You now have a BISM file that points to the PowerPivot workbook in SharePoint
You can choose to open this in PowerView via Create PowerView Report
As you can see, this opens a PowerView Report
You can also use this BISM file in Excel, but first you will want to make note of the BISM URL. Right click on the BISM file and choose Properties (http://sp/Shared%20Documents/BISM%20File.bism):
Launch the Excel Rich Client > click the Data tab > From Other Sources > From Analysis Services
Paste the BISM URL (http://sp/Shared%20Documents/BISM%20File.bism) next to Server Name: and click Next >
As you can see, this will find the Data Model located under the Analysis Services PowerPivot Instance
Data Model under the Analysis Services PowerPivot Instance via SQL Management Studio
You can then modify the File Name: and Friendly Name: (I chose "BISM Connection") > Finish
Choose PivotTable Report > OK
You can then build your PivotTable and insert Slicers via PIVOTTABLE TOOLS (make sure you have the PivotTable select so the Contextual Tool Tab is present) > ANALYZE > Insert Slicer
Save and Publish this workbook to SharePoint
You can now open this workbook in Excel Services and interact with the Data Model.
IMPORTANT:
At the moment, there is a bug in the product and you will not be able to interact with the Data Model in Excel Services when pointing to a BISM file built off a PowerPivot workbook stored in SharePoint 2013. You will get the error:
"External Data Refresh Failed
An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:
BISM Connection"
If you run a SQL Profile Trace against this Analysis Services PowerPivot Instance while reproducing this error, you will see the Error "You do not have permission to perform the requested operation."
This was fixed in SQL 2014 CU5:
Cumulative update package 5 for SQL Server 2014
http://support.microsoft.com/kb/3011055
KB Article:
Excel Services workbook fails to refresh using a .bism file
http://support.microsoft.com/kb/3016518
