Power Pivot Error While Upgrading a Workbook

I recently tried upgrading a workbook to Excel 2013 from an older version (I believe 2010).

Upgrade message

It succeeded (or at least it looked like it succeeded), but I needed to save, close and reopen the workbook according to Excel. 

Upgrade Data Model

Close and reopen workbook

Saving of the document failed with the below error message and then the Power Pivot Add-in was corrupt and the whole tab was even gone.

Error message

Disabling/enabling the Add-in didn't work, I got an error message saying the add-in cannot be loaded and Office Repair needs to run.

Office (Pro Plus) Repair ran successfully, it only needed a reboot after which the problem was solved. I still have no idea what caused Excel to blow up like this.

The second try of upgrading the workbook did work correctly and now I could save and reopen it and go on with my business! :-)

Power Pivot: unable to convert a value to the data type requested

I just ran into an (for me strange) issue when I was updating a Power Pivot workbook.
I had 2 columns in a table, let's say "One" and "Two", where One has integer values and Two string values. In my model I created a calculated column Three to concatenate these values. Because One and Two are actually redundant and never used on its own, I decided to delete those columns and save some space (the tables contained just over 2.2 million records).
So I started with updating my source view to concatenate the values in T-SQL, but when I refreshed the table in Power Pivot I got the error:
"Unable to convert a value to the data type requested in table x for column Three"

This error occured because I renamed the column in the view One and I already had that column present in my PP earlier (although I deleted it before opening the Table Properties). So my column One was previous INT, but would now be VARCHAR, however PP keeps this setting in mind although the column was deleted.

I found an answer on StackOverflow:
http://stackoverflow.com/questions/6981688/powerpivot-sql-query-fails-unable-to-convert-a-value-to-the-data-type-requeste

My solution was to recreate the column One in my PP and leave it empty (this would get the data type INT), and then add the new column One from the source in the Table Properties. This gives a warning that the column is already present in your workbook, but solves it by renaming the new column to "One 1" when refreshing the changes. After that you can delete the old column One and rename "One 1" to One and you're done!

So in short:

1. Recreate the column (One) you deleted
2. Then add the newly added column from your source with the same name
3. PP renames the new column to "One 1"
4. Delete the old column (One) again and rename "One 1" to One, or the desired name

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• Ultimate Query Tuning by @Rob_Farley
• Incorrect Results Caused by Adding an Index by @SQL_Kiwi
  You can vote for the Connect item here.
• Interested in Power BI? @Technitrain Summed it all up!
• Dump The Results Of A DAX Query To CSV Using Powershell by @Kjonge
• @MarcoRus started a great discussion about advanced BI certifications like SSAS Maestro, MCA:BI, MCM:BI. Check it here.
• Last but not least: Excel 2013 Standalone will NOT add Power Pivot / Power View until September 10, 2013. You can check the SQL Server Forum for more information.

Hopefully you can get somehting useful out of here!

@NickyvV

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• SQL Server Problem Solving Methodology by @grrl_geek
• Counting Things That Didn't Happen by @PowerPivotPro
• New to SSRS? Build Your First SQL Server Reporting Services Report by @grrl_geek
• More advanced with SSRS? @sqlJason does a great job explaining Bullet Graphs in SSRS: Part 1 and Part 2. Part 3 will follow shortly to complete the series
• In case you haven't heard of Power BI by this time, check out this post by @Technitrain, it sums it all up pretty well
• For those who love to write blog posts, but can't seem to find the time should check out Speedblogging

Hopefully you can get somehting useful out of here!

You can also follow me on Twitter: @NickyvV

Power Pivot Field List vs. PivotTable Field List

Something strange happened this week when I was giving a PowerPivot training.
The PowerPivot Field list disappeared and the PivotTable Field List came back. This happened after a few minutes after I had made a PowerPivot table in a new document. It seemed to happen just in a moment, not after any particular action. Saving, closing and reopening the document solved the problem.

This triggered me to look at the autosave options. When you create a new document and autosave is on, make sure it saves it with the right extension. In this case, the format of autosave was on OpenDocument Spreadsheet (.ods). This caused the PowerPivot Field list to disappear and only the PivotTable field list was available.

This can be undone when the document type of autosave is on Excel Workbook.
The autosave options can be changed under File > Options > Save.

Power View in action on Politics

Kasper de Jonge had a blog post to a tweet to this nice video where PowerPivot and Power View in Excel 2013 are used to look at the political landscape of the Dutch parliament over the last 66 years. At september 12 we had elections in Holland, so this topic is quite hot now.

Please keep in mind the video is in Dutch, but I believe it is still worth watching if you don't understand dutch (Watch on YouTube).

MSTechEd Europe 2012 Day 3: BI and Data Visualization

I finally had some time to work out some of the stuff from the MSTeched Europe from last week.
I have a few other sessions I'd like to share and see also on Channel 9, so you'll see some more posts coming soon hopefully!

This session given by Jen Stirrup was about BI and data visualizations, mostly about PowerPivot (download) and Power View.

The session starts out with a more theoretic (Jen's) view of Business Intelligence and how visualizations determine the first impression of humans.

The comparison is made between Traditional BI vs New End User BI, where BI is about people first and the technology is an enabler. Business users have to embrace the BI solutions being build, otherwise there's no feeling with the system build and it will not be adopted within the business.
Traditional BI is difficult for IT and also takes a long time to build where End User BI is more flexible and mashups are possible.

This is a spreadsheet to manage my spreadsheets!

Starting with a quick look at PowerPivot: it gives users the power to create compelling self-service BI solutions using a familiar Excel Interface. It can be used for mashups, has a high performance due to the xVelocity in memory analytics engine (a.k.a. VertiPaq) and has sophisticated Time Intelligence functions.

Business puzzles vs Business mysteries
Excel (PowerPivot) is used  for business puzzles: the business has a very well defined question and a well defined answer. Compared to Power View: we have an ill defined business question and an ill defined answer which make up the business mysteries.

The prerequisites for Power View are
- SQL Server 2012
- SharePoint (Enterprise) 2010 SP1
There is supposed to be no typing in Power View (except for the name of the charts), so it's all about dragging, dropping, slicing and brushing and filtering data.

Data visualization is about people:
- preattentive processing: we see lines and colors, in 2D
- visual integration: we see faces
- cognitive integration: use context and world knowledge and look back top-down
People tend to look at the top right corner of a page or chart first.
The Line, Bar and Scatter chart are best used when we take into account the above theories. This is why Jen doesn't really like Pie charts because they use area and angles, both are not well read.

Andy Caddy pointed out the following pie chart to Jen last week!

Something that also stood by me was the comparison made with Kylie Minoque when looking at pie charts. In "Can't get you out of my head" she nodges here head to left and right, that's what people tend to do when looking at pie charts when the slice is not starting at 12 o'clock in the pie chart.

Power View has the possibility to export your page to PowerPoint and interact with that page inside the presentation. An instance of Power View is loaded, therefore a connection to SharePoint is also needed, and you can slice and dice in the same way as in Power View.

I also had the honour to speak to her in person after her session. Nice meeting you Jen!

You can watch the full session at Channel 9 here.

MSTechEd Europe 2012 Day 1: Optimizing your BI Semantic Model for performance and scale

I joined my first session on tuesday by Dave Wickert. Here's my short version of the session.

VertiPaq is all about Performance, Performance, Performance!
The VertiPaq engine chooses query performance over processor performance.
What the VertiPaq engine does:

1. Encoding (per column): creates a dictionary with value or hash encoding (self-deciding)
2. VertiPaq storage
• dictionary
• column segment
• hierarchy
• relationships
• partitions
3. Processing
• Process Full
• Process Data
• Process Recalc (includes calculated columns)
4. Advanced Processing
• Process Defrag: updates the dictionary
• Parallel processing
• Error handling
5. Server Memory Map
• Databases
• Formula engine (DAX, MDX)
• VertiPaq caches
• Storage engine

Defaults that can be adjusted (be careful):
VertipaqMemoryLimit - 60%
TotalMemoryLimit - 80%
VertipaqPagingMode

Power Pivot Security Architecture Diagram

This morning (thanks to my colleague Bas) I came across a nice diagram of an end-to-end view of all the related components of PowerPivot (for Excel and for SharePoint) for SQL Server 2008 R2.

Included in this diagram are:

• Service Accounts
• SharePoint Databases
• Security Hardening
• Automatic Data Refresh
• User Identity Flow
• PowerPivot Permissions Levels

The original document (in Microsoft Visio, PDF, or XPS format) can be downloaded from the MS Technet site (link).

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!

CALCULATE - The Queen of all DAX functions: From Row context to Filter context

This post is an update on AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE). With special thanks to Marco and Alberto explaining the topic of Evaluation Contexts so well! :)

A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.

Let's start with the 2 measures:

(1)
AVERAGEX(
        FILTER(
              Organisatie,
              Organisatie[Type] = "Woonlocatie"
        ),
        SUM(Productie_OrganisatieGroei[AantalClienten])
)

(2) 

SUMX(

    Productie_OrganisatieGroei,

    CALCULATE(

             AVERAGE( Productie_OrganisatieGroei[AantalClienten]),               

             Organisatie[Type] = "Woonlocatie"

    )

)

To understand the relationships between the tables, there is a 1-to-Many relation between Organisatie and Productie_OrganisatieGroei:

Datamodel

Starting with measure 1, AVERAGEX is an iterator, so it creates a Row context on Productie_OrganisatieGroei. The FILTER on Organisatie however, because it is in a Row context, is not propagated to the many-side. So calculating the SUM of [AantalClienten] doesn't take the filter into account and takes the sum of the whole Organisatie table.

Measure 2 also starts with an iterator (SUMX) which creates a Row context, but then uses CALCULATE inside, which creates a Filter context on that one row. Remember that Filter context does propagate over relationships. Now that we have created a Filter context by using CALCULATE, the filter on Organisatie[Type] does propagate through the relationship and filters the Productie_OrganisatieGroei table. That is why this formula does produce a correct answer on the problem I had.

Once more the pivot table which has measure 1 and 2 in it:

Note: although the project was done with PowerPivot v1, I just imported the xlsx-file in SQL Server Data Tools (Visual Studio / former BIDS) to extract the datamodel for my workbook:

SSAS BISM Tabular Workshop

On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.

BISM Tabular Workshop

It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?

PASS Community event

I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.

Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!

PowerPivot bug?

I ran into an interested thing this afternoon when working in the PowerPivot window.

When the PowerPivot window is maximized I see the following:

So it seems all the tables (which are there) are visible.

But when I restore the window, so it is not longer maximized, the blue triangle reappears and the rest of the tables are accessible again.

I believe it is a bug, but has anybody noticed this before?

I'll try and check this with MS later when I get the chance.

Hub'n'Spoke in PowerPivot - The Core and Thin Workbooks

I'm still working on the Management Dashboard I blogged earlier on when I was Implementing Parent / Child Hierarchy and dealing with the issue with AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE).

I am now dealing with some performance issues, specially when opening and refreshing the Excel file. Another point occured when setting the auto refresh option in SharePoint. The refreshing itself is pretty fast, but when you open the file afterwards, it takes ages to load.. This has to do with the first (PP) and second (Excel) refresh of the file as Rob (PowerPivotPro) explains in his post.
When searching for solutions I came across some posts of Rob, the Core and Thin workbooks posts (1 and 2). The implementation of this hub'n'spoke model for PowerPivot is quite straight-forward with the help of Rob's posts.

In short, you have to get a copy of your workbook, change the connection from embedded to the location of the core workbook on Sharepoint. Then you can rename your thin workbook to .zip and copy an empty item1.data in xl\customData. Rename back to xlsx and your thin workbook is created.

Another performance issue I have is when opening an auto-refresh file in Sharepoint, it takes ages for the file to load, due to the second refresh where Excel has to refresh all the formulas and pivot tables. Everytime the file is opened this refresh takes place. With the Core-Thin concept this is not totally solved, because the thin workbook still has to be refreshed..
Still, I can now auto-refresh the core workbook in Sharepoint (this sets the autorefresh on open mark in the file). But the core never gets opened by the user, the thin only consults the data inside the core, so that's not a problem.
The only issue I now have is keeping the thin workbook up to date. Rob (or PivotStream) seems to have a solution for this problem, so I think patiently waiting is the only option.. :)

Learn DAX Basics in 30 minutes!

Now is the time to start with DAX: a few days ago a tutorial has been released to help you learn DAX basics quickly.
The QuickStart: Learn DAX Basics in 30 Minutes is available as part of the MS Technet Wiki, so we can all contribute to it also!

This QuickStart is for users new to PowerPivot or tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems.

AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE)

I'm still working on the Management Information Dashboard I previously talked about when I was Implementing Parent / Child Hierarchy. (Note: There are some Dutch words and expressions in the examples)

Now I ran into this issue when trying to calculate the number of clients of a particular type of cost center (Activiteitencentrum, Ambulant, etc.).

My implemented hierarchy is in the Organisatie table, I'm interested in the organization type Woonlocatie and I want to calculate the number of clients per Region / Cost center. I have the Region and Type (both of table Organisatie) in the row label, where Year is in my column label.

I first tried this with Measure1:

AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))

But Measure1 does not calculate the totals per region correctly, it takes all the clients into account, not only where type="Woonlocatie". Then I tried Measure2:

SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))

which does take into account the organization type in the hierarchy. I'm not all clear about why this is working correctly with Measure2 and not with 1. If anyone does know why or has something to share about this, I'd like to know.

Measure1: AVERAGEX(FILTER(Organisatie, Organisatie[Type] = "Woonlocatie"), SUM(Productie_OrganisatieGroei[AantalClienten]))

Measure2: SUMX(Productie_OrganisatieGroei, CALCULATE(AVERAGE(Productie_OrganisatieGroei[AantalClienten]), Organisatie[Type] = "Woonlocatie"))

[Edit]

A few months later I participated in the 2-day SSAS Tabular workshop from Marco Russo and Alberto Ferrari. After the workshop I had a better understanding why measure 2 is working correctly, please see my follow-up post.

Manual sorting in PowerPivot

When you have your own row labels and would like to sort them in your own order you can create a set and have them always in that order, also after refreshing the data.

This is the starting point, we have (string) labels of the length of an employment. PowerPivot sorts them in this order, from 0 - 1 - 2 to 5.

What we would like to have is this:

This can be achieved by creating a Set for the Row Labels. You can find the option on the (PivotTable Tools) Options ribbon, under "Fields, Items & Sets".

After selecting "Create Set Based on Row Items..." you can sort the items manually and give the set a relevant name.

After creating the set it also appears in the PowerPivot Field List under Sets:

MS SharePoint Server 2010 SP1

I'll start my first real post on this blog with the following: a few weeks ago Microsoft released Service Pack 1 for SharePoint Server 2010. So why is it important for PowerPivot? It drastically improves performance for PowerPivot users!
In SharePoint 2010 Service Pack 1, members of Excel Services were able to cut the number of ECS query requests nearly in half, which means half the load on PowerPivot application servers per user action and therefore almost double the PowerPivot scalability.







You can find more details about the Service Pack here, you can also download it directly here.