Current Session no longer Valid

[masplin]

Another weird one. If I just right click on a few measures in a pivot table and remove after 3 or 4 I get the error

"current session no longer valid due to structural changes in the database"

I then can't save the file due to sharing violations. This makes no sense as I have not even deleted the measure just removed it from a pivot table.

I have all sort of wider things going on so planning to try and rebuild form scratch. i have at least 50 measures in the powerpivot window and another 50 created in excel. Is there a way to export these and import thme into a new model please?

Mike

 

[Jacob Barnett]

See this post to see how to get a list of all measures:
Frederik Vandeputte: PowerPivot Nuggets - Part 14 - Generate a list with all your DAX calculations

This gets you a fair way towards your goal of quickly rebuilding. Also see this post of Rob's that uses the standard pivot filter list defer layout update to allow you to enter measures quickly without waiting for each to process through:
PowerPivot field list and Excel field list at same time! « PowerPivotPro

There is a further technique that is potentially useful to you in 2 ways - firstly because it forces PowerPivot to do a 'recovery' on the data model which I have found often gets rid of these idiosyncrasies that creep into models after a while and secondly because it means that if you've modified the XML with additional measures then the model will recognise them. Rob's post here detail the technique of breaking open the model to get at the XML:
Magic: Grab All Your Measure Formulas as Text! « PowerPivotPro

Then using notepad create an empty 0 byte file called item1.data which you will copy into the 'custom data' folder in the xl folder over the existing file which will be more or less the size of your model. Then rename the model xlsx, open the model and accept the offer to recover the data. This should be successful and you can then refresh your data (it will all have disappeared). By doing this you've essentially rebuilt the model.

MAKE A SAFETY COPY BEFORE DOING THIS.

Hope this is useful, it's a pretty brief description of something not particularly straightforward. Happy to answer questions.

Jacob

 

[masplin]

great that give me lots of things to look at.

 

[masplin]

ah the bit about rebuilding the model seems what I'm after. Think I've built up lots of mess and needs a good clean out. I'm a bit unclear where Robs tutorial (funny that was me the Mike in the tutorial!!!) ends and the next step about item1.data starts? Do I create the zip, drop the 0byte file in and rename to xlsx or have i missed some steps?

 

[Jacob Barnett]

Mike,

Sorry it wasn't clear. I included Rob's post to show how to crack open the .xlsx file but it doesn't actually tell you how to force the model to 'restore'.

The basic steps are:
- Open notepad and 'save as' item1.data (this is effectively the empty data file you are going to force onto the model). Easiest to save this to desktop.
- Change the name of the model from .xlsx to .zip (this enables you to browse the various parts of the model).
- Inside the modelname.zip folder you will find a folder called 'xl' and inside that there is a folder called 'customData'.
- Inside the 'customData' folder is a file called item1.data which is where all your model data resides, its potentially very big (e.g if your model is 100mb then this file may be 98mb).
- Replace the existing item1.data file with the empty one on your desktop.
- Rename your file modelname.xlsx and open the file.
- You'll get a bunch of error messages followed by the opportunity to attempt to restore the model which you should accept.
- Refresh your data and force the model to recalculate by changing the calc option to manual and back again (refresh will take much longer than normal).

This should do it. As I said make a copy before you mess around.
Jacob

 

[masplin]

perfect thanks