SharePoint 2013 BI, Tabular instance , Security, Roles,EffectiveUserName PowerView (Part 1)

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 1. Install SharePoint 2013 Enterprise with SQL Server 2012 SP1 and the BI stack. I strictly followed this techNet article for the BI Stack.You can also read the following post.

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.

image

image

Step 3. Move these tables to the DataModel : click on the PowerPivot Ribbon, select each table and click (twice) on Add to Data Model :

image

Step 4. Move to the PowerPivot ribbon, select Diagram View and associate the 2 tables on the ItemID column as follows:

 

image

 

Step 5.  (still in the PowerPivot window) add a PivotChart by clicking on the PivotTable icon-select PivotChart :

 

image

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 :

image

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)

image

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 ) :

 

image

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.

image

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.

image

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” :

image

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:

image

Create a new BI Semantic connection to your tabular cube

 

image

 

Save it with the name demotabular and you see a document with a blue cube :

 

image

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)

image

Click on the Pie

image

and you get this:

image

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)

image

In the next post, I’m going to illustrate how to secure the data.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s