A typical situation in PowerBI’s Analyze in Excel cube is that you add or change some new dimensions in PowerBI desktop, then publish it to the workspace. However when refreshing Analyze in Excel cube, there is no change in the Excel pivot. You can repeat refresh many times and still new or changed dimensions are not showing up in the cube. So what should you do to get a proper refresh like the drinks above? 🙂
The issue like described above is in the cache when the dataset is published online. If the dataset is not published online, but connected to your PowerBI desktop via a connector like this (Analyze in Excel for Power BI Desktop – SQLBI), then you should not have any such problems. In any case, after publishing the dataset online, you should always run a check with your Analyze in Excel cube if new dimensions are present and if they are not you can do the following steps below.
There are two things you can do to resolve this. The first option is to download a brand new Analyze in Excel cube from your PowerBI workspace and then you will see the new fields. However, by doing this, you need to redo all the pivot formatting, structure and any linkages that you might have to the pivot cube. That is not a desirable option if you want to keep your old pivot cube’s formatting and structure.
The second option, which works at best is to use the current pivot cube, but refresh it in a different way. To do that, open the Connection Properties. In the Connection Name window, just change the name of the connection to something similar. For example instead of “powerbi-connection_source”, type “powerbi-connection_source123”. Or you can type anything as you wish, but the rule is that is has to be different than the previous connection.
Then hit OK button and then the refresh takes place and you will see new or changed dimensions popping up in your Analyze in Excel cube.
I hope this trick helps you avoid unnecessary stress with refreshing excel cubes. I haven’t tested this method on Analysis Services cube, but I assume it will work just the same.