SharePoint 2010 and BI: PowerPivot and Entity Profile Page

June 19, 2012

I have recently finished MOC 50429 SharePoint 2010 Business Intelligence with Lior Cohen Amikam which I found very enlightening. I thought I’d share with you some of the excerpts from my summaries throughout the course. It’s a mix of ideas of what to do with SharePoint BI abilities and a short description of what they are. This is the fourth post in a series of posts that I am making from the material of the course:


Entity Profile Page

If the entity I’m using is for instance a customer, and the table describing that customer has a long list of columns to describe the customer attributes, I don’t have to show that entity as a regular list. In a list, that customer with all his attributes will be spread out for a very long line. The user will have to scroll to the left and right to see all of the attributes. Instead, we can use an Entity Profile Page which will show a sort of organized card ID for that customer, which would be more readable for my user.



We actually define security in the level of the file. The minute the user has access to the file, he can access all the data stored in it.

To hide code fields from the user in the Pivot table, we need to hide them in the PowerPivot.

PowerPivot allows us to drag to the pivot table attributes and measures which are not necessarily connected to each other.

PowerPivot automatically does referential integrity between the tables connected. We’ll see a “ “ (blank) customer in case I didn’t have a real customer connected to certain sales.

When you add slicers around your pivot table, try adding them in a way that makes them user friendly by turning to basic logic. For instance:


            Territory > Country > Region > City (Horizontal slicers)


Year >                                      Pivot Data

Month >

Day of Month

(Vertical slicers)


We can expose PowerPivot reports through the Excel services to the PerformancePoint dashboard.

You can add calculated measures to PowerPivot through DAX.

The bottleneck for PowerPivot isn’t usually with getting the answer for the query we ran, but more in rendering it to html.


Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>