SSAS Dimension with encrypted column

D Guthrie 21 Reputation points

I have a dimension table containing 3 Always Encrypted attributes.

In the database they are defined as varchar(). Shown for example is the 'Account' attribute. The encrypted contents is a binary string. SSMS browsing decrypts and shows the data as expected.

However, SSAS thinks the column is binary (and thus the key is binary) - (Image 1)

and an error is thrown (Image 2)

I have tried setting the key column to the table key, name column to the account attribute, everything I can think help.

The furthest I get is when I set the key column to that of the table itself, then the name column to 'Account' as shown (Image 3)

On browsing that at least shows the encrypted data... (Image 4)

But how can I browse decrypted data here?

[1]: /api/attachments/69577-image.png?platform=QnA [2]: /api/attachments/69490-image.png?platform=QnA [3]: /api/attachments/69617-image.png?platform=QnA [4]: /api/attachments/69623-image.png?platform=QnA

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,208 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lukas Yu -MSFT 5,806 Reputation points

    If you were using Tabular model, you could find a property called "Column Encryption Setting" that can easily resolve this issue . As in Use Always Encrypted data with SSAS and Power BI

    It is proved, that for Multidimensional Cube we could also use the method mentioned in this article above.

    Thanks for your feedback and Darren's suggestion. So I edit this post, it could give the correct solution.

1 additional answer

Sort by: Most helpful
  1. Darren Gosbell 2,371 Reputation points

    I have not tried this myself as we don't use multi-dim anymore at my work. But the same steps in the article @Lukas Yu -MSFT linked for Tabular should work for multi-dim. The article talks about using the legacy data sources instead of Power Query and the legacy data sources are the same ones used by multi-dim.

    0 comments No comments