Kasper de Jonge BI Blog

I recently found a great PowerPivot feature to make the published PowerPivot sheet “view only” for a end user.

When you have write permissions for a PowerPivot sheet you can open the sheet in Excel and use all the measures and columns and use everything available in the sheet by using the “Open in Excel” button:

Now the great news, you can contain these permission to view only, documents can only be viewed sever side:

When you open the PowerPivot sheet in the browser you see the Open in Excel button is gone:

When we open the PowerPivot gallery as a default document library you will not be able to download the Excel sheet, it will render as html when you download it. I think this will be a very usefull permission !

I’m currently talking to a client who is very charmed by the possibilities of PowerPivot to analyze data, one thing led to another and we came to data mining .. I got thinking wouldn’t it be GREAT if we could use the Excel data mining add-in on PowerPivot data (with DAX at our disposal). After some sparring with Rob Collie I found a way! Which I am going to show to you today in this screencast.

Think about the possibilities we have, make some great calculations with DAX to make a calculated column or measures and then unleash the power of the data mining add ins !

A few prerequisites: since the data mining add-ins are 32-bits you have to install the 32 bits office 2010 with 32 bits PowerPivot. I hope they launch a 64 bits Excel 2010 version soon!
You have to have a SSAS (2005/2008 /2008R2)  instance installed on your network, not the in memory PowerPivot instance but a regular version.

I used this video to guide me in the demo’s Introducing the Table Analysis Tool for Excel 2007. You can download the Excel data mining add-ins here (more videos in there to see the possibilities).

UPDATE: The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. You can use this add-in to run datamining against a service in the cloud, you don’t need a SSAS instance installed ! Check it out on this page:http://www.sqlserverdatamining.com/cloud/

I had a question by Brad at my Introduction to time intelligent functions in PowerPivot Screencast. He wanted to know if we can use the PowerPivot Time intelligent functions when we have a fiscal year that starts at Jul-Jun in combination with YTD.

The answer to that is: yes we can . The DATESYTD function has an optional argument year end date. So you can add the end of the year at the parameter.  This will look like:

 =CALCULATE(
sum(‘Tablix1′[nroforders])
, DATESYTD(‘Date’[Date],”06-01″)
,ALL(‘Date’))

This function will get the sum of nroforders of the entire year until the current month starting at 06-01 of a year, remember the All date that we need to use in CTP3.

This will result in the following pivottable, as I have no data of 2007 in my dataset PowerPivot will starts at 1/1/2008 (first date available) but you see the year starts again in July:

There are a number of other functions you can use this parameter, like nextyear and TotalYtd.

So you are working with multiple tables with PowerPivot, press the create relationship button and then you get the dreaded

What can you do ? Here are some tips to troubleshoot you relationships:

• First thing to check is that you have 2 columns that can be related, columns like a productcode or Orderdate. Make sure your key is unique for each row in the table.
• A relationship is always between a table and a lookup table. Think of a lookup table as a dimension table. A lookup table has to contain unique values with a unique key (like a Primary Key in a database). When you want to join multiple tables always use a lookup table in between.
• Make sure your columns have identical  (or similar) names, PowerPivot uses the name to automaticallyrelate columns
• When you have multiple columns that make a key you should create a new column, use CONCATENATE or & to create a composite column, and that will serve as your key.
• You want to create a relationship between two tables that doesn’t contain a unique key:  
  You can createa lookup table by copying the keys to a Excel sheet and use the Excel remove duplicates function to create a unique table, use create linked table to load te data to PowerPivot. Then use this table as intermediate table.
• When relationship detection is to no avail, you can try to add a relationship by hand in the PowerPivot window. Some relationship will not be detected automaticly although they are valid (like Boolean, datetime or currency type colums)
• You can only create one relationship at the time between two tables, like the adv works FactInternetSales: OrderDate, DueDate, and ShipDate that joins with Dimdate. To be able to do this in PowerPivot create multiple copies of the Dimdate table.
• I want a Many-to-Many relationships in PowerPivot, Marco Russo created a blog post describing this in detail.
• You cannot create a realtionship loop, for example Table1 -> Table2. Table2 -> Table3. A relationship between Table1 -> Table3 is not possible. 

I hope this post will help you solve your relationship problem.

A great challenge for all you PowerPivot geeks out there The PowerPivot Team wants to see what you’re made of! Over the next few weeks, Donald Farmer will be issuing Business Intelligence challenges for you to solve using the new add-on PowerPivot for Excel. Three finalists will receive a $250 Microsoft Store shopping spree, and one lucky Grand Prize winner will receive an all-expenses paid trip to the 2010 Microsoft BI Conference in New Orleans, LA from June 7-10th!

See more at http://www.exceleratorsquiz.com/challenge.html

Too bad only US citizens may enter the contest, so i cannot show my skills .

Dave Wickert answers the question what version of SQL relational server is required for PowerPivot. The question has multiple answers which you can read here:

http://powerpivotgeek.com/2010/01/29/what-database-support-is-needed/

To use as host for the PowerPivot SharePoint services I would say you need to use SQL Server 2008 R2 because without it you won’t be able to install SSAS in integrated mode.

I’ve had the question if its possible to show a PowerPivot workbook using a Excel web access webpart. The answer: Of course it is, the workbook is a regular excel workbook, so you can just point from the Excel web access webpart to a PowerPivot workbook from the powerpivot gallery. Users can interact with the PowerPivot workbook instantly as they would in a report:

The excel web part isn’t available at the PowerPivot site collection. I have created a BI site collection and added a PowerPivot Library to it. In the BI site collection we can use the excel web access webpart.

I’m honored that my abstract is chosen at the SQLPASS EU 2010,  I’ll be speaking in Neuss Germany about (ofcourse) PowerPivot.

In the session we’ll introduce PowerPivot, see why business users want (and have) to use PowerPivot to solve a case, we’ll be using DAX time intelligent functions and share it using SharePoint.  See what happens on SharePoint when we call the PowerPivot workbook. Then we use the published PowerPivot workbook as datasource to build a dashboard in Reporting services 2008 R2 and PerformancePoint 2010. And we discuss why we still want a DWH / Cubes, so we BI Pro’s still can go to sleep at night .

I  hope to meet some of you guys there and talk more BI. This will be my first time for such a big audience, so it’s big event for me too. I’m looking forward to it !

See you guys in Neuss at the PASS European Conference 2010 in April.

I’ve got some colleagues I have infected with the PowerPivot virus, most of them aren’t BI or Excel user so i created a 10 things you should know list to get them started. Most items have a link to The Great PowerPivot FAQ with further information.

1. You need Office 2010, with PowerPivot addin installed.
2. What is PowerPivot?
3. When you add new data from an existing datasource make sure you reuse your connection.
4. When joining tables with multi-column keys use concatenate to create a new key and use that to create a relationship
5. Hide the PowerPivot columns you don’t want to use, like keys.
6. Know how to pimp your workbook so you can impress your manager
7. Understand DAX and the Calculate DAX function
8. When saving your Pivotsheet make sure you save it at the first cell A1 so the workbook won’t render at the wrong place when published to SharePoint
9. When uploading to SharePoint use the file upload instead of the publishing function in Excel
10. The last one is a bit philosophical and took me some time to figure out: Understand (as a Programmer / BI pro) the Excel mindset of the business analyst, think out of your relational/dimensional datawarehouse box, some redundant data is ok, some relational shortcuts are ok. Remember this is Ad-Hoc reporting you don’t have to make a design to solve all possible exceptions!

I would very much like your input on this, what did i forget or what do you think a user should know when he starts with PowePivot.

Update: i’ll add new questions here:

1. Running PowerPivot on a 64 bits OS is recommended for larger data volumes – 1M or more rows. It increases performance a lot.

This screencast shows how you can import data from a reporting services report published to SharePoint into PowerPivot: