I have written this post in my head about 93 times. At least. It’s hard to start, hard to finish. The middle part isn’t super easy either. I decided to just go for it! Start writing! I still haven’t decided what I am going to write in the middle or end… so, I’m actually pretty excited to see how that goes. You?
Background
There are a few things to know here. First, I have been a software developer my entire life. I got my first computer in 1982 and to this day… really enjoy writing software. I worked across various teams at Microsoft from 1997 till I left in 2010. I have written very little software professionally since then. Sometimes I miss it – but the reality is close to 30 years was enough. I was ready to do other things, and fortunately I have been able to do that.
Second thing to know – I somehow got on a project that required a LOT of measures. Over 1000. The majority of these are minor variations of each other. Given a few measures, say { Sales, Sales YTD, Sales Growth }, if you want to slice up your transactions in various ways like : { Small Customers, Large Customers, International Customers }, { High $ Transactions, Low $ Transactions }, { Online Sale, Brick and Mortar Sale }… if you are crazy enough to enumerate the combinations of these dimensions, you end up with measures like : [Sales YTD – Small Customers – Low $ Trans – B&M]. And you end up with A LOT of these measures.
I should also mention that once you get that many measures, the model starts acting pretty darn slow. Power Pivot doesn’t enjoy that many measures… and starts taking a few minutes just to save a change to the measure.
So… here I am. I have a customer that wants a thousand measures, and… I have a master’s degree in computer science. It seems like there should be a reasonable solution here somewhere… no?
Attempt 1
I had messed around a bit in the past with using VBA to connect to the data model. For example, you can use VBA to find the memory used by your model. So, I turned on the SQL tracing in the power pivot options, added a measure… and looked at what madness happened. I have to admit… it was way more madness than I expected.
But, I did try to plug away at this for awhile. Sending XMLA commands to add measures. I never quite got that to work, and I was always assuming I would be in a weird “out of sync” state between the underlying database and what was displayed in the UI.
<Alter AllowCreate="true" ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>MyDatabaseId</DatabaseID> <CubeID>Model</CubeID> <MdxScriptID>MdxScript</MdxScriptID> </Object> <ObjectDefinition> <MdxScript> <ID>MDXScript</ID> <Name>MDXScript</Name> <Commands> <Command> <Text> CREATE MEASURE 'Table1'[DaCount]=COUNTROWS(Table1); </Text> </Command> </Commands> ...
Attempt 2
So… I asked myself… “Scott, how do you think the addin actually does this?”. Then, cuz I have no fear, I pulled out a disassembler. Back in the good old days, that would have been Reflector, but I’m cheap… so I used the free IL Spy. What I was looking for was code that did the XMLA commands to add a measure… figuring I could then do the same thing in VBA, and hopefully avoid corrupting my model (which was my current state of affairs). However, along the way I found code that looks like this:
using (SandboxTransaction sandboxTransaction = userCalculationsSettingsDialog.SandBox.CreateTransaction())
{
DataModelingUserMeasure dataModelingUserMeasure = new DataModelingUserMeasure();
dataModelingUserMeasure.Name = userCalculationsSettingsDialog.MeasureName;
dataModelingUserMeasure.Table = userCalculationsSettingsDialog.AssociatedTable;
dataModelingUserMeasure.Formula = userCalculationsSettingsDialog.Formula;
dataModelingUserMeasure.FormatInformation = userCalculationsSettingsDialog.FormatInformation;
dataModelingUserMeasure.Description = userCalculationsSettingsDialog.Description;
userCalculationsSettingsDialog.SandBox.Measures.Add(dataModelingUserMeasure);
sandboxTransaction.Commit();
}
Which, if you are aren’t fluent is Geek… is code that adds a new measure to the data model. And I said to myself… “Scott, why are you trying to write XMLA… when the code in the addin already does that? Can’t you just use the code sitting in the Power Pivot assemblies?”. You might be thinking… “Ya, Scott… good thinking”. You would be right! Arent’t we super smart!?
There are two hurdles to overcome. The first is that I still need to write code… that calls the code in the Power Pivot libraries. The second is that the code needs to run inside the same Excel process. Neither of these are super fun to deal with… but aren’t the end of the world either. To make sure we are running in the same process, we just need to create an Excel addin. Actually calling the libraries… well, that would be some trial and error.
The Hand Clap, Jump and Yell
To write an addin, I used NetOffice . I honestly have no idea if that was good or bad. It seems to server my purposes though. I added a cute button on my Excel ribbon, and it responds to the click event by trying to add measures. I will skip a level of code geek that nobody wants to read about – but basically…
var wb = GeminiWorkbookManager.FromWorkbook(activeWorkbook) var sandbox = wb.Sandbox
and once I had a sandbox, I could replicate the code above to create a transaction and measure, and add the measure to the models measure collection. Sinchy
Okay… It was actually a total pain in the butt – but with great effort comes … well, sometimes not a disaster. The first time that this actually added a measure to my model I certainly had an epic rush of excitement and cheering
Flavors.
Realize that I am operating on fairly limited info. It’s not like I am reading the original source code or talking to a developer. I am using a tool that attempts to re-create the source code for a huge project, and understand enough to use some functionality. From what I could tell… I had two options. Create a transaction, add one measure, close the transaction… and, if there is an error in the measure, I can get some info about it. Or, I could create a transaction, add LOTS of measures, then close the transaction. In which case… it runs way way faster, but I wouldn’t know if adding the measures was successful.
In theory, you shouldn’t actually care about this. Oh, who am I kidding… you stopped reading 9 paragraphs ago. Did you know that if you took all the veins, arteries and capillaries out a man’s body… and stretched them end to end… that man would die? wait, what? where was I? Oh ya – flavors. I suspect there is a way I could code it up to… try the bulk method… and if no measures get added, flip to 1 at a time? But I’m telling you … adding 1 measure at a time for 1000 measures it gonna leave a mark! So, I probably need to expose both methods in the UI.
Cool Story, Bro
I know what you are thinking. “Why are you talking so much? JUST GIVE ME THE ADDIN!”. Not only am I not ready to give you the addin, I’m not willing to give you the source code yet, either.
If I released the addin, I would feel compelled to actually support it… and I just don’t have the bandwidth for that. And… honestly, it’s just not “ready” for a packaged up release like that. Hell, the addin crashes each time I launch Excel… so I have to re-enable it each time. And there is no UI to choose the measures to add… it just grabs a file from c:\import.txt.
The source… well, it is closer. I mean, clearly there is useful stuff there… since it works. (Trust me, I did not manually create 1000 measures in a single model). However, I was actually working on two addins at once (the other was to expose a generic oData feed from the addin, and allow folks to “plug in” new generic data sources, like say… a power shell provider). So, my poorly commented and structured code ALSO has remnants of some other dead project. Icky! I need time to clean it up before I release it as open source.
Seriously, Scott. Just wrap it up.
I know you stopped reading a long time ago. I understand. I did, too. Alllllll this writing was to say “Ya, I got this thing… it’s kinda cool… but you can’t have it yet”. I’m a tease.
But I will be releasing it. Mostly, because I want Marco to write an addin that magically reformats ALL measures in my model via daxformatter.com. That will be awesome when reviewing a client model that is ugly!
Maybe the most important use might be as a measure “backup”, in case your model gets corrupted – which does happen once in awhile. Most folks don’t really need to write 1000 measures, I hope.
So – coming soon! Promise.
In the mean time… if you are using Excel 2016 like a total boss… you don’t care. First, you are too busy dancing (because dax code completion is so much better!), but also… you have VBA! That can add measures! You don’t need my silly addin for 2016! For 2013 users… just wait a bit longer.
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Marco Russo says
Scott,
I’m busy with other stuffs these days, but you can write a *supported* VBA macro in Excel 2016 accessing this collection:
ThisWorkbook.Model.ModelMeasures
You can read/write DAX formulas for each measure here:
ThisWorkbook.Model.ModelMeasures.Item(i).Formula
I am already waiting for your addin! 🙂
greg kramer says
Having myself spent zero (0!) years as a professional developer, this stuff is way above my pay grade 🙂
However, with Scott’s fun and engaging style I’m ready to roll up my sleeves with IL SPY and NetOffice to play along at home as long as Scott is ready/willing to explain what the he** is going on. (Smart move to not release until later)
John Bradley says
Keep up the great work Scott. You never stop impressing! We have such amazing people in our community and you are at the top of my list (besides Marco of course).
Tim Rodman says
For the record, I did read the entire post 🙂
Imke Feldmann says
u crazy man 🙂
BdA75 says
Looks promising !
For those interested in VBA code that invokes daxformatter.com web service, below is what I use.
Bertrand
Function Format_DAX(daxformula As String)
Dim xhr As Object
On Error Resume Next
Set xhr = CreateObject(“MSXML2.XMLHTTP.6.0”)
If Err.Number 0 Then
Set xhr = CreateObject(“MSXML.XMLHTTPRequest”)
Application.StatusBar = “Error 0, has occured while creating a MSXML.XMLHTTPRequest object”
End If
If xhr Is Nothing Then
Application.StatusBar = “For some reason it wasn’t possible to make a MSXML2.XMLHTTP object”
Exit Function
End If
‘consuming the web service.
With xhr
.Open “POST”, “http://www.daxformatter.com”, False
.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
.send “r=US&embed=1&fx=” & encodeURL(daxformula)
End With
If xhr.Status = 200 Then
Format_DAX = HtmlToText(xhr.responseText)
Application.StatusBar = False
End If
End Function
Public Function encodeURL(ByVal queryPart As String)
Dim c As String
While Len(queryPart) > 0
c = Left(queryPart, 1)
queryPart = Mid(queryPart, 2, Len(queryPart) – 1)
If c Like “[A-Za-z0-9._~-]” Then
encodeURL = encodeURL & c
ElseIf c = ” ” Then
encodeURL = encodeURL & “+”
Else
encodeURL = encodeURL & “%” & Right(“0″ & Hex(Asc(c)), 2)
End If
Wend
End Function
Function HtmlToText(sHTML) As String
Dim formateddax As String
Dim oDoc As HTMLDocument
Set oDoc = New HTMLDocument
oDoc.body.innerHTML = sHTML
formateddax = oDoc.body.innerText
If Right(formateddax, 3) = vbCrLf & ” ” Then
formateddax = Left(formateddax, Len(formateddax) – 4)
End If
HtmlToText = formateddax
End Function
Scott Senkeresty says
Internet-High-Five!
Ed says
Can you please provide a link to a resource for using VBA to import measures in Excel 2016?
Scott Senkeresty says
Maybe this link?
https://stackoverflow.com/questions/40138524/vba-to-add-measure-to-powerpivot-pivot-table