Azure content pack for PowerBI - Analyzing data in excel

[pixelicous]

Hi all,

I have the same issue as the following post:

https://community.powerbi.com/t5/Service/Analyze-in-Excel-of-Azure-Entreprise-dataset-No-measures/td...

I tried replying on that post but no comment as of yet.. I was wondering if somebody can help me understand how do i fix the dataset so the "analyze in excel" functionality would work.. i dont get it, as the content pack is only for web, I downloaded the ODC flie to open in excel but i have no idea how to play with it.

i tried in excel to create a new field and added [Usage Details].[Cost] as a MDX definition but that's not it..

thanks in advance

[Seth_C_Bauer]

@pixelicous Content Packs are Service only connections unless directly supported in the Desktop. It is only then that you would be able to adjust and manipulate the dataset. Is there something you are doing that is different than the first post in that you would expect a different outcome other than what was explained?

[pixelicous]

Thanks for replying eno1978..  It seemed from the post that it is possible to create an element that will turn the COST field to be able to be maniuplated in a way that it would be useable, thus i asked..

The guys on that post explained the situation and what needs to be done but it didn't seem like it isn't possible with the current option, like there is a way to create a dataset using that service connection to download a schema or  amodel and based on that tweak it a bit so analyze in excel would be helpful..

How come such an important content pack is released without the ability to do anything in powerpivot with it..

[v-shex-msft]

Hi @pixelicous,

You can use analysis in excel function to export data model to excel, but I don't think it will be possible to create a content pack which support drag and drop column to values field.

Regards,

Xiaoxin Sheng

[pixelicous]

Following what i wrote above.. basically i am not looking to create any content pack, i just want to use that data model correctly in excel, thats my end game? is that somehow possible?

[v-shex-msft]

Hi @pixelicous,

Not very sure for your "i just want to use that data model correctly in excel".Can you share some detail content to let us more clearly for your requirement?

Powerpivot only support measures in value filed. If you want to use column in it, you need to create a measure with max, sum, average or other math function to summary column value.

Regards,

Xiaxin Sheng

[pixelicous]

Well..

Very simple, the Azuer content pack for enterprise currnetly only works in PowerBI web version.

There are many limitations due to this so i thought i'll start analyzing the data in excel.

I use "analyze in excel" option and open the downloaded connection file with excel.

I can see the COST field is a measure itself (in powerbi web), but in excel i cannot use it under the value fields.

Is there a way to make that work?? Analyze in excel is rendered obselete without it, because having the cost as a row is simply not helping in anything.

Again, i do not want to use the cost field as a column but in the VALUE field..

Is there ANY way to do that currently? i am rather newbie to this so any help would be appreciated.. 🙂

[Seth_C_Bauer]

@pixelicous "Thanks for replying eno1978..  It seemed from the post that it is possible to create an element that will turn the COST field to be able to be maniuplated in a way that it would be useable, thus i asked.."

Let's clear this up. Connecting to the Content Pack from the Power BI Service will not allow you to create the necessary measure you need in order to drop it into the "Values" section of the pivot table in Analyze in Excel. The requirement for that Values field when doing Analyze in Excel is that it be a measure.

The referenced post does not clarify that the solution can't be done on a dataset that connects to a Content Pack in the Service.

However. The Azure Enterprise connection IS a datasource (in beta) in the Power BI Desktop. I don't know if it is the same (should be) but this method would allow you to build the measure, publish to the Service, and use Analyze in Excel against the measures you added.

The Power BI Desktop is the only place you can add calculations or modify a model/dataset.

[pixelicous]

Hi,

I think we keep coming back to the same point.. or i explain myself poorly.. I understood that i need to modify the dataset using powerbi desktop, and then and only then i could use that analyze in excel function correctly.

That was my issue, i tried opening the file downloaded for analyzing in excel in powerbi somehow, i tried connecting myself with the settings that are in that file with the desktop version, but i just couldnt get around to manage to do it.

Happen to know of an article that explains that? Or care to explain yourself? 🙂 that would be greatly appreciated!

[Seth_C_Bauer]

@pixelicous In order for this to work you need to follow this sequence.

Start in the Power BI Desktop

Using the data source connection in the Desktop, connect to the "Azure Enterprise (Beta)" data source

Add your measure

Create Reports

Publish to the Power BI Service

Use Analyze in Excel to connect to the Dataset you've created from the above report

Drop the measure in the values section

If you did all this, then something is broken.

If you started in the Service by connecting to the Content Pack (which is how I read all the above) then it won't work and my previous reply still applies.

You cannot connect to a Content Pack in the Power BI Service and somehow modify the model/add measures or calcs to it.

[pixelicous]

First of all, thanks for the reply and help 🙂

I can't open this ODC file in powerbi desktop version.

I tried creating a datasource myself, i created a new dataset of type "Azure Enterprise (Beta)"

As the Azure enterprise rest api endpoint i put "https://analysis.windows.net/powerbi/api"

Then it asks me for the key.. I tried using string i received in the excel connection string, the one after the API endpoint above, it looks like "1234abcb-14ab-az5f-1234-1234567fe083 Model"

That doesnt work, i get "the remote name could not be resolved: 'analysis.windows.net'" in the navigator window.

Is there a different way? should i copy that connection string from the ODC file and create some sort of other OLE data source connection file, and open that up????

[pixelicous]

I understood from what you replied to the original poster that it can be somehow achieved..

So it cant?

There is no way to use the azure for enterprise content pack in order to analyze data correctly in excel??

The fact that the cost cannot be used as a value completely renders the analyze in excel to be obselete...