Rolling-Up and/or Printing Annotations

In an earlier post I talked about the ins and outs of Annotations.  Although I love the concept of Annotations, there are two problems that arise when using them.  First, is that there is no way to print Annotations to paper.  Second, is that to see an Annotation (or to even know one exists) you have to be at the leaf level of your matrix as there is no roll-up notification that Annotations exist further down your tree.  

So how do you get around this?   Well it is the answer.  Unfortunately I don’t have time right now to do a complete set of screen-shots to describe how to do this and there are a lot of different ways you might want to actually implement this, so you will have to work with my basic descriptions and modify them to fit your unique needs.

Remember that Annotations are text and therefore cannot be stored in the Analysis Services cubes for your model as cubes are numbers only.  Therefore, they reside in a straight SQL table in your Application database.   The table you are looking for is entitled “dbo.A_YourModelNameHere_MeasureGroup”.  Within that table, you will find all your Annotations for that particular Model.   Once you get data from this table into Excel you can then print it or tweak how it is displayed in order to let users see Annotations that are buried deeper within your hierarchies.

So how do you get this into Excel?  Well go up to the Ribbon bar and select the “Data” tab and then select “From Other Source” from the “Get External Data” section.   At that point you can use the wizard that walks you through connecting to SQL Server and when you are done linking to the database table I mentioned above, a grid with your Annotations will be returned to Excel.

What I described above is the “ugly” and “down and dirty” way to do this.   For a more elegant solution, you would might look at using a View (instead of hitting the table directly) to limit the fields from the SQL table that are returned.   You might also investigate the use of the concept of Parameter Queries in Excel to filter which rows are returned.

Good luck!

Creating Test Data with Rules and VBA

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…