Ways to dynamically add a new column into Azure Analysis services

Dee18 31

Hi All,

Background, Power BI > Live connected to Azure Analysis Services > connected to Azure SQL Db

I have instances where I need to introduce a new column after pivoting a key Attribute Value table i.e

Next time around a new attribute value is added as below and a new column needs to be available i.e.

in Visual studio, I can edit the table, and within Power Query, refresh preview, and the new column shows up.

Problem is, this is manual and we have many of the same models that will change in this way but with different Key Attribute Values at different intervals. (there are limitations to prevent too many columns being produced and the data is generally small volumes)

Is there a way to programmatically "refresh preview" that will basically update the table schema so that the new column shows up?

Does anyone have a simple way? Or if there is a more advanced way using AMO / TOM / TMSL please can you point me in the right direction. (I'm a newbie to this so details are highly appreciated!)

Thanks!

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-09T11:02:56.07+00:00

Hi @Dee18 ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Azure Analysis services model can be refreshed programmatically using various approaches listed in this document. Please suggest if this is helpful or for further queries and we will be glad to assist.
Dee18 31 Reputation points

2020-12-09T11:11:28.33+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for responding.

My requirement is to be able to update the table schema programmatically to enable a new column after the transform I mentioned above.
If a new row is added that's a new unique attribute, a new column is produced within the power query steps (see green columns above).
None of the Model Processing or Table processing options actually allow us to alter the table schema and enable a new column to pull through.

Thanks!
HarithaMaddi-MSFT 10,136 Reputation points

2020-12-09T12:06:05.307+00:00

Thanks @Dee18 for sharing more details. I will internally work with more teams and will get back to you as soon as I hear from them.

Thanks for your patience!
Dee18 31 Reputation points

2020-12-09T12:09:17.51+00:00

Thank you @HarithaMaddi-MSFT , I look forward to your response!
HarithaMaddi-MSFT 10,136 Reputation points

2020-12-11T10:52:42.607+00:00

Thanks @Dee18 for your patience! This is to inform that I am still waiting for confirmation from Product team and will get back to you as soon as I get an update.
Dee18 31 Reputation points

2020-12-11T10:57:30.807+00:00

Hi @HarithaMaddi-MSFT ,

Glad to hear that my request has not been forgotten!
Your feedback is highly appreciated!

Regards,
D

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-14T15:43:03.38+00:00

Hi @Dee18 ,

Thanks for your patience.

I got confirmation from Product team that this is not possible programmatically today to dynamically generate columns in Azure Analysis Services model refresh. I would recommend you to submit an idea in feedback forum which will be closely monitored by Azure Analysis Services team and implemented in future releases.

Apologies for the inconvenience.
Please sign in to rate this answer.
Dee18 31 Reputation points

2020-12-14T15:46:18.18+00:00

Hi @HarithaMaddi-MSFT

Sad news indeed! :(
I will submit an idea as you have advised.

Thanks
D

HarithaMaddi-MSFT 10,136 Reputation points

2020-12-14T15:53:04.577+00:00

Thanks @Dee18 for your support. I will convey the pain point to the product team and will share inputs to prioritize the feature request for the same.

Dee18 31 Reputation points

2020-12-14T16:21:21.693+00:00

Hi @HarithaMaddi-MSFT ,

I really appreciate your assitance!
The idea has been logged:
https://feedback.azure.com/forums/556165-azure-analysis-services/suggestions/42204799-enable-users-to-update-table-schema-during-a-datab
Sign in to comment

Ways to dynamically add a new column into Azure Analysis services

0 additional answers