Automatically list all Powerpivot DAX formulae

[adamjm]

Hi,

This may seem like a strange question - but what I would like to do is produce as automatically as possible a list of the names of all fields defined in Powerpivot (both in the Powerpivot backend green screen, and anything user defined in the Excel frotn end) that are DAX formulae, and what the formula for that field is. For the fields that are just values i.e. straight from a database , they can either be listed or ignored, it doesn't matter.

I've seen Excel utilities that do that sort of thing for regular Excel formulae, typically to help audit spreadsheets. But I've not seen how to do that for Powerpivot files - or even to list all the fields enclosed.

I'd ideally be after a table like:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Field name
[/TD]
[TD]Is Formula?
[/TD]
[TD]DAX formula
[/TD]
[/TR]
[TR]
[TD]CustomerID
[/TD]
[TD]No
[/TD]
[TD]n/a
[/TD]
[/TR]
[TR]
[TD]TotalSales
[/TD]
[TD]Yes
[/TD]
[TD]=SUM([Sales])
[/TD]
[/TR]
</TBODY>[/TABLE]

Does that seem possible with either a tool or by developing a macro? I have experience of standard Excel macros but no experience with how much/if any macro functions can interact wtih Powerpivot tables.

Thanks!

 

[miguel.escobar]

Dax Studio should do the job
Other (Better) Ways to Get All Measures As Text « PowerPivotPro

 

[Jacob Barnett]

The simplest way to do it is mentioned in the comments on the post Miguel linked to although you could easily miss it - once you've downloaded the ODC file its 3 clicks every time you want to create the list.

PowerPivot get all DAX - ODC File

 

[adamjm]

Hi,

Happy new year!

Thanks very much for the responses from you both, very interesting. Unfortunately so far I couldn't get either DAX studio or the ODC technique to work yet in my case.

Dax studio does not seem to recognise the Powerpivot data source - when I select to use the internal Powerpivot data, the list of databases remains blank.

With the ODC I get a message to the effect of connection can't be opened when I try to open it in the respective Excel file.

A bit of googling suggests this might be because I am stuck on Powerpivot v1, which has a different model in some ways. Does that seem likely? I am not able to upgrade to Powerpivot v2 unfortunately due to "corporate rules"...

From the page linked to above, I saw a link to:
Guest Post: Catalog Your Measures with a Nifty Macro « PowerPivotPro
which does seem to work for my workbooks - but it shows only the custom DAX formula defined in the Excel front end (i.e. they appear on the right hand side of Excel with a calculator icon - plus a few others I haven't explicitly defined but seem related to the layout of the pivot table in Excel itself) and misses all the ones defined with a custom DAX formula in the main Powerpivot green screen itself.

Any further ideas?

Much appreciated help so far.

 

[miguel.escobar]

Hey Adam!

Happy new year to you too!

For the DAX Studio issue I believe you're trying to run the add-in with a blank workbook with a powerpivot model on the "background". What you need to do is simple, just create a pivot table (just click the pivot table icon on the powerpivot tab) and once the powerpivot field list appears try running the add-in.

With the ODC I'm going to let Jacob take this as I really don't have that much experience with the ODC approach.

With the link, it's the blog prior to the one that I posted before and it's actually not the easiest approach.

Go for ODC or DAX Studios.

Hope this helps.
Miguel

 

[Jacob Barnett]

I wasn't aware that the ODC wouldn't work with v1 and having looked at the connection details I'm not entirely sure why it wouldn't - are you sure the .xslx you are in has a PowerPivot model? Otherwise I don't know of any other way to list the DAX for calculated columns automatically.

The only suggestion I can make is that you need to 'challenge' your corporate rules about getting an upgrade to v2. In my opinion the advantages go way beyond the scope of this topic.

Jacob

 

[adamjm]

Hey Adam!

Happy new year to you too!

For the DAX Studio issue I believe you're trying to run the add-in with a blank workbook with a powerpivot model on the "background". What you need to do is simple, just create a pivot table (just click the pivot table icon on the powerpivot tab) and once the powerpivot field list appears try running the add-in.

Hi again, thanks for the reply.

I've tried it being sure to have an active powerpivot pivot table selected in Excel before opening the Studio icon but unfortunately same effect. The DAX studio must recognise it has a powerpivot model in it as when I go to "Change Connection" icon it lets me select "Powerpivot model: Book2.xlsx" for instace. But it does not produce any database name or option to choose it when selected. If I go back into the Change Connection icon it has automatically reselected a (blank) option to use a tabular server instead of the powerpivot.

The status in the bottom right remains "not connected" always, although no actual error message as such. Likewise the DMV tab, and all the others at the left remain blank.

The reason I wondered if it was due to v1 was this issue?
DAX Studio - View Issue #33097: Can't seem to connect to a local PowerPivot model
where someone mentioned upgrading to v2 was a solution.

I have tried uninstalling/reinstalling both Powerpivot and the Dax Studio program a few times and rebooting.

Cheers

Adam

 

[adamjm]

I wasn't aware that the ODC wouldn't work with v1 and having looked at the connection details I'm not entirely sure why it wouldn't - are you sure the .xslx you are in has a PowerPivot model? Otherwise I don't know of any other way to list the DAX for calculated columns automatically.

Hi,

Thanks again - re why it doesn't work, my suspicion is it is because the ODC seems to use this DMV: system.discover_calc_dependency

This doesn't seem to exist in v1 to me (but definitely could be wrong, I'm not experienced in this!)
Using a technique like in Querying PowerPivot DMVs from Excel « Chris Webb's BI Blog
I enumerated all supported DMVs in my Powerpivot file via:select * from $system.discover_schema_rowsets
and the discover_calc_dependency wasn't there. I'll paste the list of what was there below in case of interest. I look through a few of them but couldn't see anything that stood out yet sadly.

The only suggestion I can make is that you need to 'challenge' your corporate rules about getting an upgrade to v2. In my opinion the advantages go way beyond the scope of this topic.

Totally agree This aside, the headline new features of v2 look such an improvement from v1 I can't wait to use it. Sadly today we are dependent on a 2008 sharepoint version which I am told will not support refreshes of v2 models, and I note that you can't backsave v2 powerpivots into v1 as far as I can see (which is logical!). The campaign for a 2012 installation now has that little bit extra ammunition!!

Thanks again.



List of supported DMVs in my file:

[TABLE="width: 320"]
<TBODY>[TR]
[TD]SchemaName[/TD]
[/TR]
[TR]
[TD]DBSCHEMA_CATALOGS[/TD]
[/TR]
[TR]
[TD]DBSCHEMA_TABLES[/TD]
[/TR]
[TR]
[TD]DBSCHEMA_COLUMNS[/TD]
[/TR]
[TR]
[TD]DBSCHEMA_PROVIDER_TYPES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_CUBES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_DIMENSIONS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_HIERARCHIES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_LEVELS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_MEASURES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_PROPERTIES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_MEMBERS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_FUNCTIONS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_ACTIONS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_SETS[/TD]
[/TR]
[TR]
[TD]DISCOVER_INSTANCES[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_KPIS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_MEASUREGROUPS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_MEASUREGROUP_DIMENSIONS[/TD]
[/TR]
[TR]
[TD]MDSCHEMA_INPUT_DATASOURCES[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_SERVICES[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_SERVICE_PARAMETERS[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_FUNCTIONS[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_MODEL_CONTENT[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_MODEL_XML[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_MODEL_CONTENT_PMML[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_MODELS[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_COLUMNS[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_STRUCTURES[/TD]
[/TR]
[TR]
[TD]DMSCHEMA_MINING_STRUCTURE_COLUMNS[/TD]
[/TR]
[TR]
[TD]DISCOVER_DATASOURCES[/TD]
[/TR]
[TR]
[TD]DISCOVER_PROPERTIES[/TD]
[/TR]
[TR]
[TD]DISCOVER_SCHEMA_ROWSETS[/TD]
[/TR]
[TR]
[TD]DISCOVER_ENUMERATORS[/TD]
[/TR]
[TR]
[TD]DISCOVER_KEYWORDS[/TD]
[/TR]
[TR]
[TD]DISCOVER_LITERALS[/TD]
[/TR]
[TR]
[TD]DISCOVER_XML_METADATA[/TD]
[/TR]
[TR]
[TD]DISCOVER_TRACES[/TD]
[/TR]
[TR]
[TD]DISCOVER_TRACE_DEFINITION_PROVIDERINFO[/TD]
[/TR]
[TR]
[TD]DISCOVER_TRACE_COLUMNS[/TD]
[/TR]
[TR]
[TD]DISCOVER_TRACE_EVENT_CATEGORIES[/TD]
[/TR]
[TR]
[TD]DISCOVER_MEMORYUSAGE[/TD]
[/TR]
[TR]
[TD]DISCOVER_MEMORYGRANT[/TD]
[/TR]
[TR]
[TD]DISCOVER_LOCKS[/TD]
[/TR]
[TR]
[TD]DISCOVER_CONNECTIONS[/TD]
[/TR]
[TR]
[TD]DISCOVER_SESSIONS[/TD]
[/TR]
[TR]
[TD]DISCOVER_JOBS[/TD]
[/TR]
[TR]
[TD]DISCOVER_TRANSACTIONS[/TD]
[/TR]
[TR]
[TD]DISCOVER_DB_CONNECTIONS[/TD]
[/TR]
[TR]
[TD]DISCOVER_MASTER_KEY[/TD]
[/TR]
[TR]
[TD]DISCOVER_PERFORMANCE_COUNTERS[/TD]
[/TR]
[TR]
[TD]DISCOVER_LOCATIONS[/TD]
[/TR]
[TR]
[TD]DISCOVER_PARTITION_DIMENSION_STAT[/TD]
[/TR]
[TR]
[TD]DISCOVER_PARTITION_STAT[/TD]
[/TR]
[TR]
[TD]DISCOVER_DIMENSION_STAT[/TD]
[/TR]
[TR]
[TD]DISCOVER_COMMANDS[/TD]
[/TR]
[TR]
[TD]DISCOVER_COMMAND_OBJECTS[/TD]
[/TR]
[TR]
[TD]DISCOVER_OBJECT_ACTIVITY[/TD]
[/TR]
[TR]
[TD]DISCOVER_OBJECT_MEMORY_USAGE[/TD]
[/TR]
[TR]
[TD]DISCOVER_STORAGE_TABLES[/TD]
[/TR]
[TR]
[TD]DISCOVER_STORAGE_TABLE_COLUMNS[/TD]
[/TR]
[TR]
[TD]DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[Jacob Barnett]

You are well outside my sphere of expertise here although I wonder if changing the connection string to use one of the other DMVs might yield results.

Interesting point about the Sharepoint refresh - sounds like baloney! Not sure Sharepoint 2008 even exists but you need Sharepoint 2010 to run PowerPivot and apart from perhaps upgrading the PowerPivot for sharepoint software there should be no issue.