question

JasonYeung-2529 avatar image
0 Votes"
JasonYeung-2529 asked CarrinWu-MSFT commented

Relationship created in analysis service not appearing in Power BI

Hi,

I have a Power BI report which connects to analysis services (on prem). It's a LiveConnect connection.. I created a new relationship between 2 tables in the data cubes, but when I refreshed Power BI, the new relationship is not appearing. The new relationships I created is:
132562-billing-frequenct-to-revenuedetails.jpg



I've masked the table names for security. The model is a multidimensional model and I created the relationships by dragging the column from 1 table to the column of another. I was wondering how do I get the relationship to appear in Power BI?

Jason

sql-server-analysis-services
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JasonYeung-2529, after you create the new relationship, have you deploy solution again? Or have you try to analysis in Excel? If the relationship is correct, there will no error shows.

0 Votes 0 ·

Hi Karrin,

Thanks for the info. I was able to analysis a bit in Excel, but not in Power BI. What I want is to relate those 2 tables together so that when I do a table visualization in Power BI, I can display data from both tables from the cube. But what's happening is that when I try this, I get the following error in Power BI:
132798-image.png



I checked the data model in Power BI and found that the relationship I created in the analysis server is not imported into Power BI. I tried refreshing and recreating the connection, but they don't get related. (It's probably why I'm getting the error).

Jason

0 Votes 0 ·
image.png (105.5 KiB)

Hi @JasonYeung-2529, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

When live connected to an MD cube, the relationships you see in Power BI are not DSV-level like you created, but between measure groups and cube dimensions. These don't require relationships at the DSV level, but there's a max of one for any measure group and dimension combination.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @JasonYeung-2529,

As AlexeiStoyanovsky mentioned, Power BI don't support DSV relationships. When you import data from SSAS cube to Power BI Desktop, it will only import objects(like dimensions, measures) with data in SSAS database, then generate single result set based on all retrieved data. It works like the Query Designer in SSRS. The underlying tables with prelateships in DSV will not be imported into Desktop so that you can only see a single table containing all dimension members and measures. For more information, please refer to this thread and Create and manage relationships in Power BI Desktop.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.