The Great Function Project – Part 1

Your First Co-Development Project

P3 Adaptive has tried many different flavors of our brand of “consulting” through the years, but co-development projects that combine real projects with coaching and knowledge transfer have turned out to have been the sweet spot in terms of success.

Sometimes we are hands-on in the build process, guided by your superior industry expertise. Then sometimes we remain entirely hands-off but guide you in the build. The important part is that wherever possible, we work together and that neither party progresses things so far forward that the other feels left behind.

By reading this far, you have implicitly agreed to join me on a trial co-development journey. Today, you are going to drive, and I’ll be your navigator – and that’s a pretty apt analogy because we’re going off-road.

Co-development projects require a commitment from both parties regarding both time & perseverance. Today you’ll only need about 5 minutes of time to completely build out Phase I of this project. Regarding perseverance, you might just need to turn a blind eye to some ugly code while still paying attention to the hidden underbelly of Power BI that I’m going to show you.

We are going to produce this interactive workbook of every DAX function you have available to you. Good news right? Super practical. For me at least I’ve found it easier to refer to than the MSDN pages.

My version looks like this:

Now does that sound like something you might be interested in?

But I’ll do even better than that. Stick with me, and in my next post, we will do the same for every Excel function and then again for every M function. By the end of our project, you will have a single interactive go-to reference for all three languages!

Would that be F’n great? So great that wouldn’t it be called the Great F’n Project? What’s that Editor Reid? No, it wouldn’t be? Not very searchable? Oh OK then….

Welcome to the Great Function Project!

Now I want you to hit save…. as hard as you can…

So now comes the time to commit yourself to this project. Open up a completely blank workbook save it as The Great Function Project and lets GOOOOOO!!!!!.

You know when you’re typing in your SUM function and the Intellisense prompts you with some guidance? It’s helpful right? But how does it know all that stuff? It has to be somewhere deep in the bowels of the file structure right? That’s the metadata, and there’s a ton of it behind the scenes before you’ve even created a table.

We’re going to take our Power BI file and ask it to look inside itself to extract its Metadata. So so meta…

Did you know? Power BI and PowerPivot are virtually identical to SQL Server Analysis Service Tabular under the hood. So it makes sense that we will be connecting to Analysis Services it to find this useful metadata. So go ahead, your turn.

You should end up here… but we don’t yet have everything we need to connect the file with… uh itself.

So now that I’ve gotten you this far, I have to ask you, do you have DAX Studio installed? No? OK, that’s fine, our project has hit a minor speed bump. This happens, but changing course quickly is our specialty. We’re going to need DAX Studio for all of about 30 seconds.

So at this point in our co-development project, it’s your turn to take things over. Before we can go any further, I’ll need you to download DAX Studio, install it and then open it.

For our purposes, DAX Studio is a tool that can show you all the underlying structures and metadata buried beneath the surface of your Power BI file – it does a lot more than just that though.

Connect DAX Studio to the workbook you were just tricked into saving.

In the big white space aka the Query Window enter this query:

Select * from $SYSTEM.DBSCHEMA_CATALOGS

Now go up to the top right and run that query… I know what you’re thinking… what are we doing here? I explained that we were going to get the metadata from our file, well this is the first step.

Technet says that the DBSCHEMA_CATALOGS “identify the physical attributes associated with catalogs accessible from the database management system (DBMS).”…. uh cool, I guess.

I don’t know what that means. But what I do know is that DBSCHEMA_CATALOGS is one of 100+ Dynamic Management Views (DMV’s), each of which stores some key information/metadata about the content of our not-as-empty-as-first-thought workbook. In this case, it’s telling me the database name of the SSAS cube hidden inside our Power BI model.

Another example of a useful DMV is $SYSTEM.MDSCHEMA_FUNCTIONS. This is where some of that Intellisense info lives. We’re going to query that DMV a little later on, but not from DAX Studio, so we’re pretty much done in DAX Studio….

That wasn’t so bad now, was it? Take note of the localhost information in the bottom right indicated with the #2 above. Flip back to the Power BI workbook and enter whatever you are showing here as the Server in your SSAS connection window that should still be open.

Then come back to DAX studio and right click on the cell in the Catalog_Name field market with a #3 above, and then copy the contents. Now returning to the Power BI workbook, paste that into the Database information.

Lastly, expand the MDX or DAX query panel and enter:

select [FUNCTION_NAME], [DESCRIPTION], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $SYSTEM.MDSCHEMA_FUNCTIONS

I would normally just do a select * to get everything here too, but for some reason that didn’t work for me, so I reverted to handpicking the columns based on this [link removed due to 404] reference.

You should end up with a screen looking something like this.

Hit OK. Boom goes the dynamite! We have functions!!!!

But um…. I guess I knew less DAX than I realized – I don’t recognize any of those function names. Click Edit to go through to the Query Editor, and we’ll get to the bottom of this.

If you scroll down though, you will start to see some familiar DAX functions eventually. It turns out that 1 value in the Origin column is an MDX function and the rest look to be DAX, so filter out the 1’s.

Important last step 1: Right-click on the query and uncheck ‘Include in report refresh.’ The Analysis Services details will change later on and break the queries, and a static import will be fine.

Important last step 2: Rename the columns to the names you see below. We want to make sure no later transformations will be required later on… In hindsight, beginning with DAX was a bad idea. M & Excel won’t be so fragile.

And that’s it – for now. Rename the table to DAX and then Close & Apply. Now go to town laying out your interactive DAX reference report in whatever way works for you.

Come back next week and bring your completed file with you. Next week (using a different and simpler technique) I want to help you bring, inside the same file, all 471 Excel functions and their definitions. Why Excel? Well, it is the world’s most widely used programming language, and well, we need to talk about this.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.