Ok this one is for all of you who like me have to live and work with different regional settings than English US. This makes sure a lot of software behaves not as expected, PowerPivot also has strange behavoir.
When you import data from SSAS into PowerPivot all column are treated as text. This is a by design feature of PowerPivot:
This is a by design behavior in the current version of PowerPivot. The reason behind is due to the fact PowerPivot does not support Variant data type, while MDX does. The cell value from a MDX query may potentially return different data type, for instance, in a IIF statement IIF (condition, expression1, expression2), expression1 and expression2 can return different data type. In order to support all these scenarios inside PowerPivot, we convert the values to string during import. You would have to explicitly change the column data type after the import as you have already observed.
as we can read on this forum post. Sounds like a solution that makes sense, but it also gives the end user more to do. You have to convert all the columns that are not text (like date and decimals) to the type you want in the PowerPivot window. This might give you a problem when you have different regional settings on the client than on the server. In my case our SSAS server uses the English US regional settings on the server but i have Dutch settings on the client.
Just a quick tip if you want to share your PowerPivot files but you are the only one in the organisation who already has the luck to have office 2010 with PowerPivot and you don’t have SharePoint 2010.
The book is mainly targeted at the BI Professional or IT Pro. Chucked with interesting facts and tips you need to know when you want to install or deploy PowerPivot. It also contains very funny story’s about how the product came to be with story’s from the team.
It is split up in three main parts, first an introduction to PowerPivot, why do we need PowerPivot and a first look at PowerPivot. Great introductory work from the writers. The second part is “Creating Self-Service BI Applications Using PowerPivot”. Here we learn how to work with PowerPivot itself from loading the data to publishing it to SharePoint.
The last part is truly meant for the It Pro with a setup, troubleshoot, architecture deep dive and Enterprise considerations. Great information anyone should know when they want to go and install PowerPivot for your company (or as a consultant).
I recommend this book for anyone who wants to start using or consulting PowerPivot at a professional level, it is really full with information and background. I read it in two days while I was on holiday great stuff !
With the release of the Pivotviewer for SSRS application we finally can build our own PivotViewer app on top of our data. In this blog we are going to build an app on top of a PowerPivot sheet.
I have a PowerPivot file that i use for presentations where i loaded data from the contoso database. The scenario is that we use this PowerPivot application to determine which stores have little products on stock.
The PowerPivot sheet looks like this:
To gain even more insight into this scenario, we want to see if there is a relationship between the stores that have little products on stock per employee. We are going to build a PivotViewer app on top of this PowerPivot file. First thing we have to do is of course install the PivotViewer Extension for Reporting Services as I described in a previous blog post.
To create a PivotViewer application we have to do two things:
Create the images that will be the representation for each store. We create this image by using a SSRS report to be used in the PivotViewer BI Report Crawler to automatically create images for each store that will feed the PivotViewer.
Next we have to configure the PivotViewer application using the BI Collection Administration SharePoint List. Here we configure what data will be used to navigate through the Pivotviewer data and show data for each store.
We start by creating the SSRS report. As datasource we use the PowerPivot app that is published to SharePoint.
As we can see here i have created a report which gives me the details for a store. This is very important because the report will be used to generate thumbnail for each store. We need a parameter that can be passed through for each store, i just dragged in the Store dimension at filter part of the query designer and selected parameter.
Secondly we need to remember this report will be used to show in the PivotViewer, we need to be able to identify which store this is and what the state of this store is. In the demo done by Amir Netz at the MS Bi Conference keynote and also at the included PivotViewer demo they have added a background color so we can quickly identify the state of the a store.
So we go to the report Body and set the backgroundcolor dynamically depending the results:
When i run this i get the following report for a specific store:
We save the report in the PowerPivot gallery on sharepoint at the same location as the PowerPivot file:
Now we have to go and create the collection of items for the Pivotviewer. We have to configure the BI collection administration to create a image for each item and determine what data we want to show in the PivotViewer for each item.
In Inventory we need to supply a unique name, we call it StoreInventory.
I’m going to skip the Dataset for a second because i want to finish the report parameters. These parameters will determine what parameters are going to be passed to the report while generating a thumbnail for all the stores. As Report Parameter Datasource we select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”.
At the Report Parameter Datasource Type we use “Adomd.net”. As Report Parameter Query I have created a simple MDX that returns all the stores:
select
[DimStore].[StoreName].[StoreName].members
on 0 from [Sandbox]
At the Dataset part of the collection admin tool we are going to configure what data has to be shown for each items of the PivotViewer. So in our case we want to show data for each store, I want a store to have these properties:
StoreDescription
ContinentName
RegionCountryName
Stateprovincename
EmployeeCount
DaysInStock
AverageDaysinStock
As Dataset Parameter Datasource we again select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”. At the DatasetParameter Datasource Type we use “Adomd.net”. As some of the requested properties are not in the dimension Store I altered my PowerPivot file and added the columns as calculated column to the Store table. Now all we have to do is add a simple MDX Query:
a few things are interesting to notice: first of all the order of items is importent, the order you put in here is the order that you can drill down into the data when using Pivot Viewer:
A second thing that is importent is how you want to show your numerical data. By default a numerical value will be displayed as an string like this:
But a numerical field contains a lot of different values a choice of all of them is not very userfriendly. You can make the PivotViewer aware that this is a numerica value by adding a dimension property to the dataset. In my case i added :
I just wanted to share with you two great presentations that quickly get you up to speed with PowerPivot, they are done by Program managers Julie Strauss and Ashvini Sharma from the PowerPivot team at the Teched 2010 in New Orleans.
Building Sophisticated BI Applications Using Microsoft PowerPivot for Microsoft Excel
PowerPivot for Excel (and SharePoint) is a breakthrough technology that enables end users to build BI solutions and collaborate with their peers. This session dives deep into the capabilities of PowerPivot for Excel and shows how end users can build sophisticated BI applications using an intuitive and seamless user experience. This session consists almost entirely of demos and assumes some basic knowledge of PowerPivot and its value proposition.
Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)
DAX (Data Analysis Expressions) is a new expression language for end users to add business logic to their PowerPivot applications. DAX offers a lot of power and flexibility while retaining the the simplicity and familiarity of Excel like formulas. This session explains the fundamentals and concepts behind DAX and demonstrates how to add business logic in PowerPivot using calculated columns and measures. This session also introduces you to various categories of DAX functions — scalar functions, table functions, aggregates, time intelligence — but is not intended to be a comprehensive overview.
A lot of times you just to show values of rows from the current month compared to other specific time periods, like previous month, ytd, previous YTD, etc. The problem is, how do you determine the current month ? This could be the actual current month (like PerformancePoint 2010 does) but in the most cases this is not sufficient because your last month you have data might be one or more months ago because of your ETL.
In this blog post we create a measure that we can use to filter the data using a filter or slicer so the data in the report will show the the last month where have data.
I start by creating a calculated column in my date table that checks if the month is the current month and returns a Y or N.
To determine if the month is the last month we have data i used the following DAX statement:
The key here is how we determine the last month to have data: LASTDATE(ALL(FactInventory[DateKey]).
Lastdate will check the last date in the column, but because the date table and the fact table have a relationship DAX would automatically use the this to return only rows from the fact table in the current date context. This way we never can check all the fact table rows. We want to overrule the filter context by using ALL() this will give us all the rows from the fact table. LASTDATE(ALL(FactInventory[DateKey]) will give us the one date that is the last date we have a fact for.
Now we can check if the dates from our date table are in the same month and year as the last value of the fact table and return a “Y” when they are and a “N” when they aren’t this will give us the possibility to select all the values from the last month as we can see in this report:
Now we are able to create the following report:
The great thing about is that when because we use a calculated function this is only calculated at creation or during data refresh. This means it is fast at execution and it means that when new data arrives from a new month the measure automatically determines the new CurrentMonth.
We recently created a new web application on our SharePoint farm, we chose claims-based authentication as authentication option. All seemed ok until i wanted to create a report on top of the PowerPivot application in SSRS. I got a error “‘Too many automatic redirections were attempted” in the report builder 3. Searching on the Internet i can across a blog of Dave Wickert that explains that we need to run a PowerPivot web application in classic mode: Why PowerPivot requires ‘classic-mode’ web applications.
Unfortunately we cannot change back classic mode from a claims based web app. So we had to create a new webapplication and copy everything from one site to the other (or create a backup of your sitecollection and restore it in a new web application.
great stuff !
