Tabular Model – Help – I can’t find my Dimensions in Excel
Problem statement
A colleague reached out last week while connecting to one of our SQL Server Analysis Services models in Excel. He couldn’t find the expected Attribute folders in the model. He was looking for the following dimensions:
So I started to investigate. As a first test, I connected to the same model in Excel and I was surprised that the “lost” dimensions were perfectly visible to me.
On this particular SSAS Tabular Model, we don’t have any kind of security limitations so that couldn’t be the issue.
Since the SSAS Tabular Model was mainly created by a third-party vendor, I don’t really know the model completely besides minor changes I have made myself in the past. I opened the Tabular Model definition in Tabular Editor and continued my investigation.
While looking into the model, I found out that it was working with translations, which made my investigation a bit easier since I knew where to look for now.
In case you want to learn all about translations in Tabular Models: Translations in Analysis Services tabular models | Microsoft Learn
So after a while, I found one of the “missing” dimensions:
As soon as I found out, I reached out to my colleague and he was able to find FinancialDimension1.
He was able to continue his work while I took it one step further to properly solve his problem.
Solution
Since I didn’t know the final solution yet, I was talking to a colleague who had some really good thoughts. Finally, he actually pointed me in the right direction. He was pointing me to the Data Source Connection Properties in Excel.
So I navigate to the Connection Properties in Excel. By going to PivotTable Analyze, Clicking on the Change Data Source expansion button, and choosing Connection Properties…
Which gave me the following properties:
At first sight, I didn’t see anything in particular that could explain this behavior. Until I reached the final section about the Language.
I unmarked the “Retrieve data and errors in the Office display language when available” option and looked at the model again.
As a result, I could finally replicate the problem of my end user.
I went back to the connection properties and re-enabled the option. As a final result, I could see the attribute folders with the translated names again.
