SSAS connection to the Power BI service with Power BI Desktop

[selimovd]

Hello,

we have the following situation.

- we use the SSAS Tabular model with Power BI service

- the connection happens with the SSAS service connector

- we want to develop the reports in Power BI Desktop

Currently I have no Options to develop the reports with Power BI Desktop, because if I publish my reports to powerbi.com I cannot "change" the dataset for my reports to the SSAS tabular on powerbi.com.

Otherwise I see no option to use the dataset from SSAS Tabular with Power BI service in Power BI Desktop.

Is it possible to develop the reports in Power BI Desktop with the SSAS Tabular model from Power BI Service?

Thanks and best regards

[leonardmurphy · Accepted solution]

You don't have to change the dataset to SSAS tabular on PowerBI.com.

When you use SSAS Tabular through the SSAS service connector, you're connecting to your local SSAS tabular instance and your credentials are passed through PowerBI.com. It's the same data source on PowerBI.com as it is in Power BI Desktop - your local SSAS tabular cube.

Does that make sense? 

If you haven't already, set up the connector and build a really simple report in Desktop & deploy it to PowerBI.com. If you've tried that and it's not working right, let me know what you're doing & seeing.

I ran into troubles myself because the name of the server/cube in the connector was different to the name I used in Power BI Desktop, so on deployment PowerBI.com didn't recognize them as the same server/cube and then couldn't connect. But that's a fairly obscure thing and I was able to fix it by updating the name of the server/cube in the report.

[selimovd]

Hi @leonardmurphy,

thank you, that really worked!

I have one more question. Each report I load up to powerbi.com creates a new dataset. 

So the amount of datasets grows with every report. That ist no really useful because the data source is always the SSAS tabular connector. Is there a possibility that the reports realizes that there is already the required dataset? Or a possibility to delete the duplicated datasets?

[leonardmurphy]

I have to admit I'm not sure. What is the difference between the datasets? Are they for different cubes? Do they have different names for the same cube? Or are they exactly duplicated with no discernable differences at all?

[selimovd]

They have different names (each one like the report) and use the same cube.

[xxcorpxx]

Just use the same dataset and make different reports/dashboards.  You don't need to make new datasets for each report if it is the same.

[Seth_C_Bauer]

@selimovd has a valid point that hasn't been addressed. Each time you pull/publish a PBIX file, a dataset is created with that PBIX file name. If you create 3 PBIX files all using the same Tabular model you will have 3 datasets in the Service, not the 1 datasource of the Tabular model. This is a current problem in my opinion.

Any enterprise level solution that requires version control or backups requires the use of PBIX files and are the only way to accomplish that currently. The assumption also being that a large scale deployment would want to include the use of Tabular models (and now also multidimensional).

Ideal behavior, in my opinion, would be that I deploy the reports (PBIX files) and PBI would understand that it's the same SSAS Tabular model in direct connect mode and not create another dataset, but just use the single Tabular model connection. 

[Bjoern]

I definitely agree, Eno.

//

I think Chris Webb recently published a post where he explained how to avoid 2 datasets with the same name. AFAIk, the fix does not work for AS Tabular, but for SQL models.

http://blog.crossjoin.co.uk/2015/11/09/avoiding-duplication-of-database-connection-information-in-po...

Remark: I have just learned that it is not about duplicate data sets, but about creating variables in Power BI, so you do not have to refresh each SQL table connection string on its own.

[Bjoern]

@Seth_C_Bauer:

I have added a feature request to the database. I have basically just copied your text, I hope this is ok?

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/11081736-avoid-redundancy-of-datasets-s...

[Seth_C_Bauer]

@Bjoern Absolutely, thanks for taking the initiative. Added my 3 votes!