Analyze in Excel - No Data Fields in Olap Cube

[zootsuitryan]

Hi All,

When attempting to analyze my data model in Excel 2010 on my work network I receive the error 'No Data Fields Available in the Olap Cube'. The model was was created with SQL data and loaded from Power BI Desktop.

When logged into 365 using my work credentials, I am able to bring the model into Excel when I use my personal computer on my home network, which has Excel 2016 installed. I am also able to pull in example models into Excel 2010 from Power BI ('Customer Profitability') and Power Pivot models that I have published to the web.

When attempting to connect to the data model using 'Connect to Data' in the Power BI tab in Excel, I receive the error 'Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete.' After I hit 'Delete' or 'Cancel' I receive the error 'Can't Access the dataset or report. You can't access the data or report in Power BI. Make sure it exists and you have permissions to use it.'

Any help is appreciated! Trying to implement Power BI and this error is preventing me from moving forward.

-Ryan

[v-qiuyu-msft]

Hi @zootsuitryan,

1. When attempting to analyze my data model in Excel 2010 on my work network I receive the error 'No Data Fields Available in the Olap Cube'. The model was was created with SQL data and loaded from Power BI Desktop.

When logged into 365 using my work credentials, I am able to bring the model into Excel when I use my personal computer on my home network, which has Excel 2016 installed. I am also able to pull in example models into Excel 2010 from Power BI ('Customer Profitability') and Power Pivot models that I have published to the web.

Before you use Analyze in Excel feature for the dataset in your work network, please check if there are data fields in this dataset. Also please check if the gateway used for this dataset is working.

In addition, Analyze in Excel is supported for Microsoft Excel 2010 SP1 and later. Verify your Excel version.

2. When attempting to connect to the data model using 'Connect to Data' in the Power BI tab in Excel, I receive the error 'Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete.' After I hit 'Delete' or 'Cancel' I receive the error 'Can't Access the dataset or report. You can't access the data or report in Power BI. Make sure it exists and you have permissions to use it.'

As the error message stated, please check if you have permission to access the report or dataset when you checked in Connect to Data. Also could you please share the more information about the report or dataset you connect?

Also there are somethings needs to be noticed, please refer to this article: Connect to data in Power BI.

Best Regards,
QiuyunYu

[zootsuitryan]

Hi guavaq/Qiuyun_Yu,

I really appreciate the help.

My connection is good, my Excel 2010 is a late enough version, and I created the model so I have access. It's a model connected to a SQL database.

The issue seems to have been the lack of measures. After adding one I am able to connect to the dataset using either way.

My question is though, why are only measures recognized as 'Values' fields? If I use Power Pivot, Power Query, or just connect to the dataset through a pivot table connection, any Numeric field, as well as any Numeric measure or calculated column, are treated as 'Values' fields in the pivot table.

Thanks!

Ryan

[GilbertQ · Accepted solution]

Hi @zootsuitryan the reason would be that by design of Power BI  / Analysis Services you need to create or have a calculated measure defined in order to view your data with a measure.

This has been like this in Analysis Services OLAP and Tabular for quite some time, and I am going to assume that the Power BI version is based on the Tabular version. 

I hope that helps clarify things.

And that finally you simply have to create a measure or multiple measures in order to interact with your Power BI Model using the Analyze in Excel feature.

[zootsuitryan]

Thanks guavaq, makes sense.

[ascendbi]

Hi -

You mention the Power BI Tab in Excel (AKA Power BI Publisher for Excel Add-in).  Can you confirm you have the latest version of ? 2.37.3272.24642 that I am aware of. We have noticed issues when older version are used.

I can confirm that the Add-in does work with Analyze in Excel with Office 2010 32 and 64 bit - we have many users on it.

For testing, instead of a "pull down" with Power BI Add-in > Connect To, can you install the Analyze in Excel (ODC) files and perfom a "push down" to excel (you have do download a .odc connection file) and see how that works?

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/

Note: I don't like the ODC experience or option - now that Power BI Publisher offers the same capability,w e have completely removed the SQL Server 2016 SQL_AS_OLEDB.msi from our environment. (Originally the only way for AiX to work). If there is some feature it offers, we havenot discovered it yet.

One last note - you are using DAX to create the Measure, correct? The auto-aggregation feature of Power BI does not translate to AiX: http://community.powerbi.com/t5/Service/Analyze-in-Excel-of-Azure-Entreprise-dataset-No-measures/m-p...

David

[zootsuitryan]

Hi David,

Thanks for your reply.

Yes it is the Publisher Add-in and I have version 2.37.3272.24642.

I do already have the Analyze in Excel updates installed and I receive the 'No Data Fields in OLAP Cube' error when I download the ODC file and attempt to open it in Excel.

I actually haven't created any measures in the dataset yet. I have joined tables in the model in Power BI but want to establish that I can connect to the model before I build it out.

Any thoughts?

Thanks!

Ryan

[GilbertQ]

@zootsuitryan I am fairly confident that due to not having any measures is causing your issue.

I know in the past when using Tabular models I always had to have at least one measure before I could connect with Excel. 

So if you could possibly create on measure and then see if you can connect successfully?