Connecting to Cube - model tables and unique values

[RMDNA]

Hi all,

I'm connecting to a cube, and I'm running into a couple issues.

1. When I'm importing data from the cube in the navigator, I get to the 26 tables in the model. If I select multiple tables to import at the same time, it combines them all into a single table once it's done. I'd like to avoid importing 26 tables one at a time.

2. If I import the tables one by one and attempt to manually create relationships, I get the usual "needs unique values" error. I've checked very throughly (even trying it with tables as small as three rows and copy-pasted values), but every potential relationship fails.

[v-qiuyu-msft]

Hi @RMDNA,

1. If we connect to SSAS cube in import mode, imported table are based on the checked measures and dimensions each time. Those measures and dimensions will be treated as "field" in a dataset. All checked measures or dimensions will come to the same table automatically.

2. In Power BI, relationships can be Many to One (*:1) or One to One (1:1). It means at least one column in one of the tables must contain unique values. For more details, see: Create and manage relationships in Power BI Desktop.

In your scenario, you can connect to SSAS cube in live connection. In this way, you needn't to create relationships in Power BI again. All cube models will display in desktop. See: Connect Live Power BI to SSAS Multidimensional.

Best Regards,
Qiuyun Yu

[Rvn86]

Hi.

I see this is an old thread, but I am experiencing a similar issue.

You cannot create a live connection if you do not have a SQL-server version where live connection to PBI is supported. 

I have created a relatively complex multidimensional cube, which is built on DW views with data I have extracted from Dynamics AX. I am able to create most relationships manually (even though it is a time consuming task), but I am struggling witht he financial dimensions from Dynamics AX, since there are no direct ID's for the dimensions to the other views I want to link them to.

Are the no possibility to keep the relationships when importing from a multidimensional cube? Any tips here to make the process as smooth as possible? Or is the solution as simple as doing a server upgrade to an enterprise version to support live connection?

Thank you.

[v-qiuyu-msft]

---

Are the no possibility to keep the relationships when importing from a multidimensional cube? Any tips here to make the process as smooth as possible? Or is the solution as simple as doing a server upgrade to an enterprise version to support live connection?

---

Hi @Rvn86,

It's not supported, all defined relationships in cube will lose after you import SSAS data. You need to create it again. Or upgrade SQL Server to SQL Server 2012 SP1 CU4 or later version.

Best Regards,
Qiuyun Yu

[Rvn86]

Too bad, but that's life... 

Thank you for a fast reply. 

[eskyline]

RMDNA,

I guess I'll ask what may be a stupid question... but why are you trying to import from a cube?  In my experieince if your going to use a cube then you are doing it because of the pre-configured model it provides and potentially the aggregations that is has done for you (assuming multi dimensional).   If you import the data you are not going to leverage the modellign so in my opinion you would be better served to import from the original datasources (those which the cube is based on).  I'd assume those are in the Kimball structure, and likely in a data warehouse, since thats what the cubes ingest best so they should be easier to import.