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.
*** START OF RANT ***
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:
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:
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):
And if I go back into Excel and refresh my data here is what I now see:
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:
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:
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:
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):
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:
And if I go back into Excel and refresh my data I will see random numbers:
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.