Share via

Analysis Service 2016 Model gets corrupted if you add unsupported parameter while configuring the data source

  • Article

Applies To:  Tested on SQL Server Analysis Service 2016

 

Hello Everyone, today I am going to discuss about a known issue is Analysis Service 2016 Tabular Model. If you have built an Analysis Service tabular project and added some unsupported content in the data source, then neither you will able to open the Model from the Visual Studio nor you will be able to browse it from SQL Server Management Studio.

 

Scenario and Issue:

Let's say we have created an Analysis Service Tabular project using SQL Server Data Tools(SSDT). In the project, we are creating a data source to pull the data. If the data source that you are using is a third-party data source, then there will be some additional parameter that you need to mention in the connection. Such parameter you will mentioned under Extended Properties under Advanced tab in Table Import Wizard. (Refer the image below)

1

Now the catch here is at times if you add some parameter which is invalid or has some unsupported content and save the data source, it might get saved but later on if you saved the project and then open it again, the project won’t, and it would throw an error message as follow:

 

Error: An error occurred while scripting the catalog. The Extended Properties property is set to a value that is not supported.

 

The worst part is that you can't even open the data source from the SSDT nor from SQL Server Management Studio(SSMS) after connecting it to the Analysis Service instance.

 

Resolution:

Well the resolution involves a concept that you need to know about Analysis Service 2016. In Analysis Service 2016, we have introduced sqllitedb to store all the metadata of a project. In every project folder you will see a metdata.sqllitedb file inside it.

This file will have all the connection strings, data source details and the metadata of the project.

In order to open the file you have to download a tool called DB Browser for SQLLite(You can download it from this link: https://sqlitebrowser.org/)

Once downloaded, open the tool and import the metdata.sqllitedb in it. Then click to the Browse Data tab and from the Table dropdown select DataSource.

In the table for the Data Source you will see a column named ConnectionString. (Refer the image below)

Click on the connection string value where the parameter is entered wrong.

On the right-hand side, you will see the value of the connection string in encrypted form.

 

All you need to do is to delete the connection string and then click Apply. (Refer the image below)

2

Once the change is done, save the metdata.sqllitedb file and replace it with the existing one for that project.

Now you will be able to open the package in SSDT or SSMS and then you can configure the data source and the connection string again.

This was the only option left for me to remove the unsupported content from the connection string rather than creating the entire project from the scratch.

 

Hope this helps for you as well.

 

Note: This workaround is provided AS-IS, without any warranty from Microsoft. Any modification to SSAS Metadata isn’t Supported by Microsoft.

 

Author:      Jaideep Saha Roy – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft