Importing measures into Power Pivot

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.

• About Scott
• Latest Posts
• Contact

About Scott Senkeresty

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 >>

• 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

View All Posts

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 >>