Last week, one of my “SharePoint 2013 customer” asked me to check/test/demonstrate the security features of PowerView & the tabular model. In his specific case, the tabular “cube” should be used by several companies (kind of tenants), but each tenant could only use /see his own data.
I couldn’t find a lot of details in the web, so here is what I did :
Step 2. Create an Excel Spreadsheet with 2 Excel tables : Items and Quantities; the data come from the famous PicNic exemple that Isabelle Van Campenhoudt (my BI partner in crime) and I use for most of our BI demos & sessions.
Step 3. Move these tables to the DataModel : click on the PowerPivot Ribbon, select each table and click (twice) on Add to Data Model :
Step 4. Move to the PowerPivot ribbon, select Diagram View and associate the 2 tables on the ItemID column as follows:
Step 5. (still in the PowerPivot window) add a PivotChart by clicking on the PivotTable icon-select PivotChart :
Select a new window; in the T_Items table, select Category; in the T_Quantities table, select Qty_Served. In the Design ribbon, click change Chart Type and select the Pie.
You should get something like this :
Rename the worksheet as “Chart”. Save the document.
Step 6. Publish the document to a SharePoint document library : in Excel, select Save As – Choose SharePoint – select your destination url; don’t forget to click on Browser View option (which allows the selection of the objects you want to be published)
Step 7. Click on your document in the SharePoint document library and it will show up in Excel Services. TODO OWA.
Interract with the Chart (click on Category, go to Filter, select beverages and breads ) :
Step 8. Start SQL Server Manager and in object Manager, click Connect-Analysis Services and select the default PowerPivot instance you specified during the setup of the BI stack (…in Excel Service, this is a new feature of SharePoint 2013).By default the instance is named PowerPivot. This instance is a SharePoint dedicated in memory Tabular instance.
The first time a first user interacts with the Excel Spreadsheet, if the spreadsheet contains a PowerPivot Model (BI Semantic Model), then the PowerPivot Service Application creates an in memory database (a Cube) in Tabular format (not multidimensional)in the dedicated in memory tabular instance. This database stay there during max 48 hours unless somebody uploads another version of the BISM spreadsheet.
Step 9. How can I make that instance “durable” : well, is very easy : do a backup of the database and restore it into a dedicated instance.
Install another instance of the Analysis service in Tabular Mode; in my case, I’ve installed it in another VM (sorry for the weird name,exchange2013\tabular) and I’ve restored it under the name “demotabular” :
Step 10. Create a PowerView Report
Go to a document library and if the PowerPivot Integration site collection feature has been activated, you will be able to associate the BI Semantic Model connection content Type:
Create a new BI Semantic connection to your tabular cube
Save it with the name demotabular and you see a document with a blue cube :
Now just click on demoTabular and the PowerView Editor will show up; select the fields Category (in T_Items) and Sum of Qty Consumed (in T_Quantities)
Click on the Pie
and you get this:
Now are not limited to PowerView: indeed if you get back to the connection document and you click on the ellipsis (…) menu, see the following menu will show up (keep that in mind)
In the next post, I’m going to illustrate how to secure the data.