"Current session is no longer valid due to structural changes in the database" error

[Anonymous]

Dear Microsoft,

The error message "Current session is no longer valid due to structural changes in the database" is raised in case a client connection (PowerBI, Excel) is using a so-called perspective instead of the default "Model" and a "Full Process" of a tabular table/partition is in progress. We are using 'SQL Server 2017 CU12' .

Do you know whether this issue is fixed in SQL Server 2017 CU13?

If not, do you need some other information to get this issue fixed in another CU?

I think this bug/error is simular as the issue as mentioned in topic https://community.powerbi.com/t5/Report-Server/Error-Current-session-is-no-longer-valid-due-to-struc...

The company doesn't want to downgrade to 'SQL Server 2017 CU3'; in this version/CU the error isn't raised according to previous mentioned topic. 

BR Frank van Zuilen

[v-qiuyu-msft]

Hi @Anonymous,

Is it possible for you to share a .abf file of data model for us to test on our side? If it is, you can upload .abf file to your OneDrive and send the share link to me via private message. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

As you can imagine the tabular model/cube contains confidential information and credentials so I can't send you that. Also the company disabled 'OneDrive' due to security reasons.

I assume that you aren't able to reproduce the issue at your side then?

FYI: the tabular model in place was ugraded from SQL Server 2012 to 2016 and then to SQL Server 2017.

Do you have any other ideas that might help you the investigate the root cause of this issue?

BR Frank

[v-qiuyu-msft]

Hi @Anonymous,

I'm not very clear about "Current session is no longer valid due to structural changes in the database" is raised in case a client connection (PowerBI, Excel) is using a so-called perspective instead of the default "Model" and a "Full Process" of a tabular table/partition is in progress.", so I want to get your model to test on my side. 

It would be better if you can share details steps for me to create a similar SSAS tabular model so I can test on my side. 

By the way, the issue you want to report happens in Power BI report server, right? 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft,

Good news, I managed to re-produce the issue in a Test tabular Model as well.

It also happens using Excel on another machine than the server itself, see attached picture.

I think it happens at the start of a new transaction at the server side, while selecting another filter instance, dropping another attribute in the rows/cols, or a general refresh.

The issue occurs if a perspecitive is used -> if 'Model'  is used then the issue doesn't occur.

See connection picture.

Please give me your email to which I can send the Test_Issue.abf (81,420 KB), and if required the used input text files to process the tables. Or send me an email to me on which I can reply.

Message occurs if tables are 'Full Process' and a perspective is used in the connection

The issue occurs if a perspecitive is used -> if 'Model' is used then the issue doesn't occur

[v-qiuyu-msft]

Hi @Anonymous,

Great.  I will send my work email to you via private message. 

Best Regards,
Qiuyun Yu 

[ballanda]

I've been struggling with this issue for months.  Here's hoping for a fix.  Thanks!

[Anonymous]

Microsoft is now looking at the Test Tabular model I sent early this week.

I hope indeed that in the next CU this issue can be solved due that the use of perspectives is key in delivering self-service BI-solutions to the end-user in our company.   

[v-qiuyu-msft]

Hi @Anonymous, 

I have tested with your Perspactive 2 in tabular data model against SSAS version 14.0.226.1 and 14.0.239.1(CU13) but not reproduce the issue both in Power BI report server(version 15.0.2.557) and Power BI desktop optimized for report server. 

In your scenario, which visual do you add on the report then cause the error occurs? Please try to run the same Power BI report server and desktop version as ours then test again. 

Currently I don't suggest you install CU 13 for SSAS 2017, as there is a known issue for CU 13, see: https://community.powerbi.com/t5/Issues/Cannot-display-visual-when-enabling-visual-filter/idi-p/589705

Best Regards,
Qiuyun Yu 

[ballanda]

Sample Files and Steps to Reproduce the Error (5-10 minutes)

I have prepared 3 objects to help Microsoft reproduce this issue.

<<See private message for download link>>

File 1) Excel Sheet: "SampleData.xlsx"
- Contains 3 worksheets of small test data that is used for the tabular model source.

File 2) Tabular Model: "SampleTabularModel.abf"
- Contains 3 tables from Excel sample data and 2 perspectives (both of which contain 2 of the 3 tables).
- Created on SSAS version 14.0.239.1

File 3) Power BI: "SampleTabularPBIReport.pbix"
- Contains test visualizations based on the tabular model
- Created in Power BI Desktop December 2018 update

STEPS:

This is all meant to be done on the same SSAS test machine.

1) Put the file, "SampleData.xlsx" in the folder "C:\TEMP". (Otherwise you'll have to edit the SSAS "Connections" info for it in SSMS later.)

2) Restore the "SampleTabularModel.abf" file in SSAS 2017 to server, LOCALHOST.
- In the Restore Database dialog box, choose the "SampleTabularModel.abf" backup file.
- Enter "SampleTabularModel" for the "Restore Database" name, and the appropriate "Storage Location" for your environment.
- Click OK to complete the restore of the "SampleTabularModel" SSAS database.

3) Open the file, "SampleTabularPBIReport.pbix" in Power BI Desktop (not Desktop for Report Server). This is a simple report based on a connection to SSAS server LOCALHOST, database "SampleTabularModel", and perspective "TestPerspective1".

4) Click "Refresh" on the Power BI "Home" ribbon. The report should show data in all visuals (if it doesn't already).

5) Try adding any visual to the "SampleTabularPBIReport" report in Power BI, using the fields in the sample sheets as your data source. This should work fine.

6) Close Power BI Desktop and do not save changes to the report.

7) Open the "SampleTabularPBIReport" report in Power BI again. Do not do anything else in Power BI this time except open the file. Then go to the next step.

😎 Switch over to SSMS, right-click on SSAS database name "SampleTabularModel", and select "Process Database".

9) In the "Process Database" dialog box, choose Mode = "PROCESS FULL", and make sure the "Model" box is checked, then click OK.

10) Wait for SSAS model processing to be successful (which should only take 2 seconds), then continue to the next step.

11) Go back to Power BI Desktop, where the "SampleTabularPBIReport" report is already open.

12) Do the same thing you did in step 5... Add any visual to the Power BI report using the available fields in the list. The visual you create will show up gray, with the caption, "Can't display the visual. See details." The details are, "Couldn't load data for this visual, Current session is no longer valid due to structural changes in the database."

13) To clear the error, click the "Refresh" button in Power BI 2 times.

I also confirmed that this only happens when connected to a specific perspective. When connected to "model" (i.e. the whole model), the error doesn't happen. For me, with 14 perspectives in my model, and model processing happening every 30 minutes, this is a big inconvenience for my analysts working in Excel and Power BI Desktop. A fix would be great. Please let me know if you have questions on the example, or need any more info from me.

Thanks!

[ballanda]

Sample Files and Steps to Reproduce the Error (5-10 minutes)

I have prepared 3 objects to help Microsoft reproduce this issue.

<<See private message for download link>>

File 1) Excel Sheet: "SampleData.xlsx"
- Contains 3 worksheets of small test data that is used for the tabular model source.

File 2) Tabular Model: "SampleTabularModel.abf"
- Contains 3 tables from Excel sample data and 2 perspectives (both of which contain 2 of the 3 tables).
- Created on SSAS version 14.0.239.1

File 3) Power BI: "SampleTabularPBIReport.pbix"
- Contains test visualizations based on the tabular model
- Created in Power BI Desktop December 2018 update

STEPS:

This is all meant to be done on the same SSAS test machine.

1) Put the file, "SampleData.xlsx" in the folder "C:\TEMP". (Otherwise you'll have to edit the SSAS "Connections" info for it in SSMS later.)

2) Restore the "SampleTabularModel.abf" file in SSAS 2017 to server, LOCALHOST.
- In the Restore Database dialog box, choose the "SampleTabularModel.abf" backup file.
- Enter "SampleTabularModel" for the "Restore Database" name, and the appropriate "Storage Location" for your environment.
- Click OK to complete the restore of the "SampleTabularModel" SSAS database.

3) Open the file, "SampleTabularPBIReport.pbix" in Power BI Desktop (not Desktop for Report Server). This is a simple report based on a connection to SSAS server LOCALHOST, database "SampleTabularModel", and perspective "TestPerspective1".

4) Click "Refresh" on the Power BI "Home" ribbon. The report should show data in all visuals (if it doesn't already).

5) Try adding any visual to the "SampleTabularPBIReport" report in Power BI, using the fields in the sample sheets as your data source. This should work fine.

6) Close Power BI Desktop and do not save changes to the report.

7) Open the "SampleTabularPBIReport" report in Power BI again. Do not do anything else in Power BI this time except open the file. Then go to the next step.

😎 Switch over to SSMS, right-click on SSAS database name "SampleTabularModel", and select "Process Database".

9) In the "Process Database" dialog box, choose Mode = "PROCESS FULL", and make sure the "Model" box is checked, then click OK.

10) Wait for SSAS model processing to be successful (which should only take 2 seconds), then continue to the next step.

11) Go back to Power BI Desktop, where the "SampleTabularPBIReport" report is already open.

12) Do the same thing you did in step 5... Add any visual to the Power BI report using the available fields in the list. The visual you create will show up gray, with the caption, "Can't display the visual. See details." The details are, "Couldn't load data for this visual, Current session is no longer valid due to structural changes in the database."

13) To clear the error, click the "Refresh" button in Power BI 2 times.

I also confirmed that this only happens when connected to a specific perspective. When connected to "model" (i.e. the whole model), the error doesn't happen. For me, with 14 perspectives in my model, and model processing happening every 30 minutes, this is a big inconvenience for my analysts working in Excel and Power BI Desktop. A fix would be great. Please let me know if you have questions on the example, or need any more info from me.

Thanks!

[ballanda]

Sample Files and Steps to Reproduce the Error (5-10 minutes)

I have prepared 3 objects to help Microsoft reproduce this issue.

<<See private message for download link>>

File 1) Excel Sheet: "SampleData.xlsx"
- Contains 3 worksheets of small test data that is used for the tabular model source.

File 2) Tabular Model: "SampleTabularModel.abf"
- Contains 3 tables from Excel sample data and 2 perspectives (both of which contain 2 of the 3 tables).
- Created on SSAS version 14.0.239.1

File 3) Power BI: "SampleTabularPBIReport.pbix"
- Contains test visualizations based on the tabular model
- Created in Power BI Desktop December 2018 update

STEPS:

This is all meant to be done on the same SSAS test machine.

1) Put the file, "SampleData.xlsx" in the folder "C:\TEMP". (Otherwise you'll have to edit the SSAS "Connections" info for it in SSMS later.)

2) Restore the "SampleTabularModel.abf" file in SSAS 2017 to server, LOCALHOST.
- In the Restore Database dialog box, choose the "SampleTabularModel.abf" backup file.
- Enter "SampleTabularModel" for the "Restore Database" name, and the appropriate "Storage Location" for your environment.
- Click OK to complete the restore of the "SampleTabularModel" SSAS database.

3) Open the file, "SampleTabularPBIReport.pbix" in Power BI Desktop (not Desktop for Report Server). This is a simple report based on a connection to SSAS server LOCALHOST, database "SampleTabularModel", and perspective "TestPerspective1".

4) Click "Refresh" on the Power BI "Home" ribbon. The report should show data in all visuals (if it doesn't already).

5) Try adding any visual to the "SampleTabularPBIReport" report in Power BI, using the fields in the sample sheets as your data source. This should work fine.

6) Close Power BI Desktop and do not save changes to the report.

7) Open the "SampleTabularPBIReport" report in Power BI again. Do not do anything else in Power BI this time except open the file. Then go to the next step.

😎 Switch over to SSMS, right-click on SSAS database name "SampleTabularModel", and select "Process Database".

9) In the "Process Database" dialog box, choose Mode = "PROCESS FULL", and make sure the "Model" box is checked, then click OK.

10) Wait for SSAS model processing to be successful (which should only take 2 seconds), then continue to the next step.

11) Go back to Power BI Desktop, where the "SampleTabularPBIReport" report is already open.

12) Do the same thing you did in step 5... Add any visual to the Power BI report using the available fields in the list. The visual you create will show up gray, with the caption, "Can't display the visual. See details." The details are, "Couldn't load data for this visual, Current session is no longer valid due to structural changes in the database."

13) To clear the error, click the "Refresh" button in Power BI 2 times.

I also confirmed that this only happens when connected to a specific perspective. When connected to "model" (i.e. the whole model), the error doesn't happen. For me, with 14 perspectives in my model, and model processing happening every 30 minutes, this is a big inconvenience for my analysts working in Excel and Power BI Desktop. A fix would be great. Please let me know if you have questions on the example, or need any more info from me.

Thanks!