Alan Whitehouse's Ramblings

Continuing to work until my heavy investment in lottery tickets finally pays off….

  • Categories

  • Archives

  • Deep Thought

    Historically speaking, all true change in the world has come thanks to leaders emerging, them taking charge and giving the masses someone to rally around. Can an intentionally "leaderless" movement survive or will it just slowly fade away?

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 18 other followers

  • Me!

  • Advertisements
  • Disclaimer

    The views expressed in this blog, while intelligent and always right, are strictly my own, and do not necessarily reflect the views of anyone else with which I am in any way affiliated. And don't forget, I own the rights to all information on this blog (except for the stuff I stole from other people).
  • Current Rant


    You want change, then get involved. Vote, run for office, go to shareholder meetings and contact advertisers or investors. Sitting around banging drums, singing kumbaya, smoking weed and having a camp out under the stars is not going to get you the change you want.

  • Admin Stuff

Creating Test Data with Rules and VBA

Posted by Alan on November 19, 2008

First thing I am going to say about this post is that I am stealing this idea!  At the BI Conference in October I attended a break-out session entitled “Advanced Office PerformancePoint Server 2007 Planning Business Rules and Calculations” with a session ID of CL302.   It was presented by Srini Nallapareddy and Steve Hoberecht of Microsoft.  I found this to be one of the more valuable break-out sessions for me personally from the conference.

During the session they make mention of the use of Visual Basic for Applications (VBA) within a business rule to create random data. To me, this is a perfect way to create large sets of data to help ensure that other business rules and calculations are working correctly without having to go in and enter it manually or do a full integration from another system. If you attended the BI Conference (not sure if you can access without having registered) and would like to see the whole presentation you can watch the video here. Now, the problem with the video feed is that the player (at least in IE 7.0) does not let you jump ahead, so you have to watch the video from the start to get the information you might want later on. For those of you who want to see the specific information that I am talking about for yourselves you can find it at approximately 16:30 into the video.

To whoever it was at Microsoft that decided to implement the video stream in this manner, this is a very frustrating way to implement video streaming for these sessions.  I have about 15 or 20 that I want to watch as I have free time and having to sit through every minute of all of them is not the best use of my day!!   
*** END OF RANT ***

For those of you who don’t have the time, here is my write-up…

So how do you do it?   Well, first thing to remember is that in order for this to function, you must use a Native MDX Query rather than a standard MDX Query.  That also means you have to go into the SQL table that holds the rules (dbo.RuleSetsOrRules) and manually activate it prior to deployment.   Forget that and you won’t get anywhere.

So we will start with a fairly basic model.   We will call it “RandomData” and it will consist of three dimensions — Scenario, Time and Program.  Below is the overview of the model:

And here is a very basic input/form based on that model.  It renders, as you would expect, with no data in it:

Excel Form Blank 

In order to get started we need to create a rule to use as a template that we will then modify.  Why did we do this?   Well, for two reasons really and they both come down to the philosophy of “its all about me”.  Firstly, I am not a developer by trade and writing rules like this completely from scratch makes my head hurt.  Secondly, this is how Srini and Steve did it and I am too lazy to spend the time to try to think of a better method.  So the first rule we create is a Procedural type rule and it basically says that for our “Budget” member in the “Scenario” dimension, for all “Descendants” (i.e., months) in the “2008” year in our “Time” dimension and for all members in our “Program” dimension, make the value be 1,000.   The code is show below:

Fixed Number Rule

So just to prove this works.  I check in the model and deploy my rules and then execute the rule and see the following in the Planning Business Modeler (PBM):

Fixed Number Rule Executed

 And if I go back into Excel and refresh my data here is what I now see:

Excel Form Fixed Number

So everything is going to plan.   Now I need to modify my rule to add the VBA that will change 1,000 into random numbers.    To do this I select my current rule and right-mouse click on it and select “Debug Rule” as shown below:

Debug Rule

Once I do that I will get a pop-up window that I will want to copy for later use.   In the code that is displayed, you are looking for the value of “1000”.  This is the key and it is this value that we will be replacing later on with VBA.  Below shows what you should see:

Debug Rule Results  

So next thing I have to do is create my new which I am calling “EnterRandomNumber”.  The key her is to create is as a NativeMdxQuery type rule and not a standard MdxQuery type rule.  You can see it below:

Random Number Rule 01

Once the rule is created I copy my code into the new rule.   Now I make the critical change.   Where the value of “1000” used to be, I replace with my new VBA code.  I am not going to go into all the details of VBA here, but basically the code below puts takes a random value (which for VBA is a value between 0 and 1) and multiplies it by 1000 (to get a larger number) and displays it as a whole integer (so no decimals):

Random Number Rule 02

After that I check in my model.  And go into SQL Server and activate my rule (you have to do this since it is a Native rule) and then come back into PBM and deploy my rule.   When I run it I get the following results:

Random Number Rule Executed

And if I go back into Excel and refresh my data I will see random numbers:

Excel Form Random Number

I personally think this is a pretty cool little trick.  Again, thanks to Srini Nallapareddy and Steve Hoberecht for adding this to their presentation.   They get all the credit.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: