adding multidimensional data

[ivan77]

Hi,

I use the get data>analysis services.  I choose some measures and dimension attributes.  The query adds all of the info into one query.  Doing this puts all of the attributes and measures into one table, which makes it a beast to work with.  Since it is a query, it looks like it is making a huge table with all of the rows of the query, which wouldn't be efficient at all.

I thought this must be wrong and I should import each table separately. I tried that, but my foreign key fields are not showing up in my measure group, so I dont have anything I could use to create the relationships between the tables. 

What am I missing here?

Thanks,

Ivan

[HarrisMalik · Accepted solution]

@greggyb From the title of the question it seems he is trying to explore Multidimensional model and not Tabular.

@ivan77 Which model you are trying to explore? Multidimensional or Tabular?

In case you are trying to connect with Multidimensional model, it is still in preview so as @greggyb explained you need to enable the preview feature. Secondly, in your earlier comment you mentioned that you are using SQL Server 2012, for live connections to multidimenisonal models you need SQL Server 2012 SP1 CU4 or later with Enterprise or Business Intelligence editions. It won't work on Standard Eidtion.

Please have a look here under Lmitations of SSAS Multidimensional Models in Power BI Desktop.

Regards

Harris

[ivan77]

Thanks for the info.  

After reading though the documentation, I realize this will not work, as I am actually trying to create a dashboard for company use now.  It states that the link @HarrisMalik sent:

In this preview release, SSAS Multidimensional models in Explore Live mode are only supported in Power BI Desktop, and you cannot publish or upload reports that use SSAS Multidimensional models in Explore Live mode to the Power BI service.

So, I guess I have to create a query for each individual visual after all.  

Thanks,

Ivan

[HarrisMalik]

@ivan77 With the introduction of Enterprise gatway, which you can install on SSAS server, you will be able to explore live multidimenisonal models. In enterprise Gatway you can also manage multiple servers and sources.

The only limitation at this stage is that from Power BI Service you cannot connect to MD models using get data option. The best way right now is to follow these steps:

1. Install Enterprise Gatway on server and configure it to connect with your MD server

2. Using Power BI Desktop, create the reports in Live Explore mode

3. Publish the report to Power BI Service

By following these steps yu will be able to use MD models in live mode on Power BI Service.

Regards

Harris

[greggyb]

You don't have to write separate queries for each visual, and in fact this would absolutely destroy most of the interactive features in Power BI.

You should bring over the fact tables and dimension tables as they exist in MD, preserving the relationships (Power Query can do this automatically when you browse the cube for data to import), so that you can report on the same dimensional model.

Also, keep an eye on the Enterprise Gateway, which is currently in preview. It is the piece that will allow direct query against AS MD models from the PBI Service.

[ivan77]

Also,

At 52:08 of this video from MSFT, it looks like he added his whole dimensional model somehow:

https://www.youtube.com/watch?v=EBNV7Vuhwb8 

should this be like using pivot tables in excel (pick and choose from all measures and dimensions), or should it be query based?

Thanks,

Ivan

[greggyb]

Explore the model with a live connection, don't import the data. I believe that live connection with AS MD is still in preview, so you'll have to enable that in your PBI options.

[ivan77]

Hi,

My analysis services connection has the "Explore a tabular module using a live connection" greyed out.  

I am using SQL 2012.  Any ideas why that would be the case?

Thanks,

Ivan

[greggyb]

What's wrong is that you haven't enabled the preview feature to allow live exploration of an AS MD model. The option you have described clearly states it is for a Tabular model.

Please enable the preview feature.