Jeremy Kashel's Blog

Microsoft BI Conference - Day 1 Review

I'm just back at the hotel here in Seattle, so just some time for a quick post about day 1 of the 2nd Annual Microsoft BI Conference.

The big news of the day was undoubtedly the sneak peak of "Project Gemini" - the code name for the core of a BI focused SQL Server release that will happen in the first half of 2010. The impressive part of the presentation for me was seeing an early version of a new Excel add-in to work with Gemini. We witnessed Donald Farmer manipulate 20 million (yes million!) rows of data within Excel, before creating a very slick report via a new version of Excel pivot tables, which he then published to SharePoint at the click of a button. Mosha Pasumansky explains a more about Gemini here.

Once the introductory events were over, it was a chance to attend the more focused sessions, which explained particular tracks in more detail. Although there was a huge amount of choice, I naturally went for the PerformancePoint sessions:

Michael Bower and Scott Sebelsky from Microsoft presented an introduction to Financial Consolidation with PerformancePoint Server 2007. This session gave an insight into what's involved in carrying out financial consolidation with PerformancePoint, providing some useful tips and tricks. There's more to come, as Michael will be releasing a white paper on financial consolidation with PerformancePoint Server at some point in the near future. Watch this space....

Finally, Srini Nallapareddy and Steve Hoberecht presented a detailed presentation on the various different types of business rules that you can use in PPS Planning. Srini explained the pros and cons of using different rule types, as well as providing some useful tips on how to optimise rules for better performance. You can download the presentation here.

BI for IT Professionals using PerformancePoint

The PerformancePoint downloads page has been recently updated to include a framework on how BI can provided to the IT Department.

Normally the IT Department would be helping out with supporting BI solutions, not actually being end users themselves - but it makes sense when you consider the kind of information that Operations Manager captures.

As this video explains, the end goal is to create solutions that will allow effective monitoring the IT infrastructure. An example of the kind of the kind of dashboards that can be produced is shown below:

There is white paper and also a sample solution available for download to learn more.

Tracking PerformancePoint Planning Submissions Using Reporting Services

The standard operational reports that come with PerformancePoint will allow you to report on a variety of PerformancePoint admin related activities, such as cycles, assignments, forms, jobs and associations.

I find that the assignments report is particularly useful - after all, finding out who has/hasn't submitted is an important part of any data-gathering exercise. Whilst it is useful, I do find that the assignments report is the one that admin users want changed, especially when a model site exists containing many cycles and assignments.

Extra Functionality

With a large PPS Planning implementation you can easily end up with many assignment instances, cycles and users. I've been involved in such an implementation recently, and, due to the large number of assignments, the admin user requested a bit more filtering capability than the out of the box assignments report provides.

Also, the existing assignments report tells will tell you that user A has submitted their assignment, but it won't go into any detail about what the submission actually contained. E.g. did the user submit all their entities? For some users it is quite key to know what other users have been submitting - for one thing it makes navigation easier if as an approver you know exactly which department/entity to pick in the assignment filters.

Examples

By knowing which tables to use, you can write an SSRS report that provides the additional functionality mentioned above.

The starting point is to get the base report query right. In my case, as I'm in a separate auditing database, the query goes inside a custom stored procedure, and is as follows:

SELECT              A.AssignmentId,    C.CycleInstanceName, AD.AssignmentDefName, A.AssignmentName,
                    U.UserId, U.UserName, ENT.Name AS EntityName, CUST.Name As CustomerName,
                    CASE
                        WHEN A.Status = 'partial' OR A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END AS Draft_Submitted,
                    CASE 
                        WHEN A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END As Final_Submitted,
                    CASE
                        WHEN A.Status =  'Approved' THEN 1
                        ELSE 0
                    END AS Approved, Approve.UserName As Approver
FROM                dbo.Assignments A 
LEFT OUTER JOIN     dbo.[MG_Planning_MeasureGroup_default_partition] Fact
                    ON A.AssignmentID = Fact.AssignmentID
LEFT OUTER JOIN     dbo.AssignmentDefinitions AD ON AD.AssignmentDefID = A.AssignmentDefID
LEFT OUTER JOIN     dbo.CycleInstances C ON C.CycleInstanceID = A.CycleInstanceID
LEFT OUTER JOIN     dbo.D_Entity ENT ON ENT.MemberId = Fact.Entity_MemberId
LEFT OUTER JOIN     dbo.D_Customer CUST ON CUST.MemberId = Fact.[Customer_MemberId]
LEFT OUTER JOIN     dbo.BizUsers U ON U.UserID = A.ContributorUserId
LEFT OUTER JOIN     dbo.ApproverList AL ON AL.AssignmentID = A.AssignmentId
LEFT OUTER JOIN     dbo.BizUsers Approve ON Approve.UserID = AL.ApproverUserID

You can figure out most of the tables to use by looking at a view called AssignmentsView within the application database.

One thing that I have taken into account is assignment definitions. If you have large number of users completing an assignment, then the chances are that you will have set up an assignment definition that points at a business role or a submission hierarchy. You ideally want to be able to filter on the assignment definition to return all assignment instances that belong to that assignment definition. Therefore, in my case I have three filters for the report, but you could easily add more:

The final view is a report that shows the status of the assignments returned by the filter, but also, when expanded, shows the the entities and customers that the contributor has submitted:

The above is just a taster of what can be achieved. A couple of ways that it can be extended include:

• Integrating with Sacha's data auditing idea to provide detailed history on what values the contributor has changed;
• Including comments, annotations and deadlines.

Analysis Services Properties for PerformancePoint MDX Rules

One of the great features of PEL is that you can choose either a SQL or MDX implementation for your rules, depending on what you want to achieve. Whilst the MDX rules are much less restrictive than the SQL rules, they can sometimes run slower, depending of course on how your environment is set up.

When the MDX rules do take a long time to run, it's possible that you might see this message:

What has essentially happened within Analysis Services is that an object (e.g. a cube or dimension) has been processed and is waiting to commit. Unfortunately this is not possible whilst an existing query is running, so therefore AS waits for the query to complete. It will, however, only wait so long, which is defined by one of the Analysis Services Properties called ForceCommitTimeout. Once this threshold has been reached, then the offending query is canceled, resulting in the error message above.

Finding the right balance for the Analysis Services ForceCommitTimeout and the PerformancePoint PAC 'OLAP Cube Refresh Interval' setting is key. If you have set PPS to re-process its cubes too often then you may well see the above message. On the other hand, if you set the ForceCommitTimeout too high, then queries executed whilst the cube is waiting to commit will be made to wait, meaning your big query will get though ok, but other users may see bad performance.

Darren Gosbell has written an excellent post here that provides a thorough explanation of ForceCommitTimeout and other related properties.

Renaming a PerformancePoint Planning Server

I had a strange situation to deal with a few weeks ago - the IT department at one of our clients wanted to rename the server that was running PerformancePoint Planning, and wanted to know if that would be ok! This was of course a bit of a worry - its not something that I've seen documented, so I wasn't sure what impact it would have on models, form templates, etc.

I was aware from the Planning Server Mirroring Technique that there are several references to the server name within the application, system and service database on a Planning Server. My first step was to follow the aforementioned mirroring technique, and to update all the necessary tables.

The mirroring technique also instructs you to update the config file called PerformancePoint.config, which can be found in Program Files under \Microsoft Office PerformancePoint Server\3.0\Config\.

After updating the tables/config files, everything seemed fine at first, but then I noticed a couple of problems. Although you can of course edit it, the server connection text box in PAC always defaulted to the old server name. In addition, and more of a concern, the auditing and tracing had stopped working.

Getting the sever name to default correctly was taken care of quite easily by updating the following XML that's found at \Program Files\Microsoft Office PerformancePoint Server\3.0\AdminConsole\Web.Config:

<add key="PerformancePointServer" value="http://SERVERNAME:46787/" />

Getting the auditing/tracing to work again required a bit more digging, as you can't change the 'audit writer computer name' that's found in the ManageAuditing.aspx page in PAC. These locked values for auditing are held in the dbo.Configuration table of PPSPlanningSystem, in an XML column, as follows:

<PerformancePoint.audit highLimit="1048576" pollInterval="60000" fileName="D:\Microsoft
Office PerformancePoint Server\3.0\Audit Log\audit.log" writerMachineName="SERVERNAME" />

<listener maxLength="10" maxBackup="9" writerMachineName="SERVERNAME" fileName="D:\Microsoft Office
PerformancePoint Server\3.0\Trace Log\server.log" format="single" name="FileLog" type=
"Microsoft.PerformancePoint.Planning.Common.Configuration.FileListenerElement, Microsoft.
PerformancePoint.Planning.Common, Version=3.0.0000.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />

After updating the above values, everything returned to normal, and the auditing/tracing kicked in immediately.

Finally, one thing to think about if you ever have to do this, check that no local administrators have objects checked out! As you are renaming the sever, your local admin account will belong to a different server name, meaning check outs to OldServerName\Username will be invalid.

Troubleshooting the PEL Allocate Statement

The PEL Allocate statement can sometimes throw unexpected error messages, especially when you're setting up a pull or push cross model allocation rule.

I've been keeping track of a few of the more obscure ones, which I've listed in the table below. Hopefully this will help anyone out there who's struggling to get the cross model rules working.

Using NativeSQL Business Rules for Seeding or What Ifs

You need to give really careful consideration to procedural business rules in PerformancePoint Planning. This is where multi-dimensional knowledge and experience helps, as you need to know whether its best to use a SQL or MDX implementation for your rules.

User Requirements

One area where I've found that this is particularly relevant is user requests for procedural rules that accept parameters that are member(s) of a dimension. E.g. Lets run the rule for the selected member in the entity dimension, perhaps to carry out a what if on a particular entity. This is fine for a single member, but on occasion I've found that the users want to pick either a)a leaf level member (which is fine) or b)a non-leaf member, and for the rule to apply the logic to all descendants of that member.

The Technical Problem

What technical problem? You can use the descendants function to achieve this right? You can with an MDX rule, but you can't with a SQL rule. So, surely just use an MDX rule then?

This is where you have to think relational world Vs OLAP world. Putting a statement such as Descendants($Entity$, 10, leaves) in the scope of your rule will mean that when the rule is run, then it will always operate on the leaves of the dimension, regardless of whether you pick a leaf or non-leaf member. But the issue is that if your scope statement covers cells that have definition rules against them, then an MDX query will pick up the results of of those cells and and shove them into the fact table!

So can you use the Except() function or something similar to filter out those cells that you don't need? Yes you can, but not if the members start to span different levels, the compiler won't let you do that. As I said, you can't use a SQL implementation because using Descendants($Entity$, 10, leaves) in the scope statement will tell you - "A SQL implementation is forbidden by a parameter or variable reference in a MemberSet expression unless the reference is the entire expression."

The Solution

Rules that operate on fact data should really use SQL, its quicker and avoids hitting calculated data. I'm sure that you've seen this coming a mile off (!), but one way to go about this is to use a NativeSQL rule.

You can reference database objects in your NativeSQL rule, one of which will make it easier for you carry out descendant/hierarchy type functions in SQL itself. A function will get created in the Calc schema of your Planning application database in the format fnCalc_0_DimensionName_MemberSetName_IsAncestorOrSelf - this will occur when you deploy your model site. Using this function in conjunction with NativeSQL will allow you to get the leaves of a member quite easily.

The following NativeSQL rule creates a copy of one scenario's data into another scenario, which is referenced by a parameter:

--Although integers, the parameters are actually 'members of a dimension' parameters
create proc Calc.[sp$procedure] @scenario int, @entity int as
begin
begin transaction

    insert into dbo.[tbl$factTable] (alldim$except(dim$Entity, dim$Scenario), dim$Entity, dim$Scenario,
    column$ruleId, Value, LoadingControlID, bmo$CreateDateTime, bmo$ChangeDateTime, ContextID)
    select alldim$except(dim$Entity, dim$Scenario), dim$Entity, @scenario, $ruleId, Value, -9, getutcdate(),
    getutcdate(), -1
    --No need to hard code the fact table name
    from dbo.[tbl$factTable] t
    inner join 
    (
        select e.memberId from dbo.d_Entity e
        --Get leaves of the member key that is in @entity
        where Calc.fnCalc_0_Entity_Divisions_IsAncestorOrSelf(@entity, e.memberid) = 1 and 
        e.memberid not in (select h.parentmemberid from dbo.H_Entity_Divisions h)
    )    lv on lv.memberid = dim$Entity
    where t.dim$Time >= 200701 and t.dim$Time <= 200712 and (dim$Scenario = 1)

if @@error <> 0
rollback transaction
else
commit transaction

end

I suppose the above rule may look a little bit daunting, due to all the dollar signs everywhere. These are just the preprocessor commands, which will get replaced when the stored procedure gets created. But other than that, its just an insert statement. In some cases the syntax is actually quite handy, as you can use statements such as alldim$except(), which is a short hand way of returning fact table dimension column names very easily. The idea behind the preprocessor commands is that you can protect the logic in your rule from minor structural database changes.

The key is that when the rule gets run, it has normal parameters, meaning the user will see the following interface:

In the case above, the member key of North America Operations will end up in the stored procedure, as I have a parameter in the stored procedure called @entity. Inside the stored procedure it is then used in combination with the IsAncestorOrSelf function to pick up the leaf level descendants.

The only caveat that I will make is that this hasn't come from a production environment, so I've skimped a bit on a few of the operational details such as deleting the existing data, decent error handling and returning the number of records affected. These are all things that you should add in if you use this kind of thing in a production environment!

BI Evening Slides for PEL Vs MDX

Many thanks to Chris Webb for organising last night's BI evening at TVP. I found the session very useful, it was great to meet people who are actively implementing MS BI solutions, as well as to see Jes Kirkup's presentation on Excel Services.

The slides from my own presentation, PEL Vs MDX, can be downloaded using the link below (you'll need to register/log in)

PEL Vs MDX Presentation

BI Evening, TVP, 17th July 2008

I'll be giving a talk titled 'PEL Vs MDX' at the UK SQL Server Community's BI Evening on the 17th July at Microsoft's TVP offices in Reading.

The content will be geared towards MS BI developers, and will highlight the differences between the two languages, with the aim that those with MDX experience will be able to make a fast start with PEL.

Also speaking is Jeremy Kirkup from TAH, on 'Using Excel Services with Analysis Services and MOSS'.

If you are just starting to explore delivering BI solutions with Excel Services and SharePoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part.

The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through.  However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services.

You can register for the event here.

Multiple Assumption Models in PerformancePoint

PerformancePoint gives you the flexibility to have multiple assumption models to use as you please in your main models. Although this is great, I've found a problem when the two assumption models have different member sets for the same dimension, and so in an extension to my last assumption model post, this post provides a workaround for the issue.

Consider the following example. I've got a main model where I want to use 2 assumption models, namely:

• HR Assumptions - Uses the HR member set from the BusinessDriver dimension;
• Strategic Assumptions - Uses the Strategic member set from the BusinessDriver dimension.

If you go and add the two assumption models to the main model at the same time, then everything looks normal, as shown in the screen shot below:

Once you deploy successfully, you will of course want to write a business rule to pick up the assumption model data. However, when writing the rule and trying to pick from the BusinessDriver member selector, you will see that you can unfortunately only select from one member set, as shown below:

If you need to write rules that reference specific members in both member sets, then you will be out of luck. It's not even possible in any kind of native MDX rule, as the main model cube that gets created in Analysis Services only contains the dimension that has been created from the 'HR' member set. It would seem that PerformancePoint just picks the member set that is first alphabetically.

The workaround for this issue is simply to create a single member set that combines the two original member sets. Therefore, each assumption model will contain more members than required, but that's far better than not being able to write the rules that you need.

So just something to be aware of, and catch, at design time - rather than in the middle of your build.

SQL Server 2005 Cumulative Update 8 for Service Pack 2

Microsoft have just released Cumulative Update 8 for SQL Server 2005 Service Pack 2.

Amongst the changes are a few fixes for small problems in Analysis Services, such as:

• Infinite recursion occurs in the CalculationPassValue function in SQL Server 2005 Analysis Services;
• A data source view that connects to an Oracle database generates incorrect relationships when you develop a Microsoft SQL Server 2005 Analysis Services project by using BIDS;
• The MeasureGroupMeasures function does not work correctly in SQL Server 2005 Analysis Services;
• All the MDX queries that are running on an instance of SQL Server 2005 Analysis Services are canceled when you start or stop a SQL Server Profiler trace for the instance.

Details the above fixes and many more can be found here, as well as details of how to download the update. As usual, you can only apply the hotfix if you have Service Pack 2 installed.

Details of all SQL Server 2005 builds released after Service Pack 2 can be found here.

XLCubed Version 4 Released

XLCubed Version 4 was recently released, adding support for increased data visualisation via MicroCharts, as well as enhanced web publishing.

I've been using XLCubed since version 1 and have always found it both powerful and easy to use. All the standard functionality that you'd expect from an OLAP/Analysis Services Excel add-in exists and is incredibly intuitive, yet there's plenty more if you want to dive deeper. In particular, I've always liked the following features:

• Wealth of right click functionality, such as remove only, keep only, propagate across sheets;
• Drag and drop pivoting - alter your report quickly without having to open toolbars or menus;
• Converting grids to formula - Allows you to easily create disjoint reports using Excel formulas;
• Linking grids - Means that an action in one grid (such as a drill down on a dimension) can automatically perform the same operation on multiple linked grids.

As mentioned, version 4 sees the inclusion of the interesting MicroCharts feature to the XLCubed grids. This essentially means that you can choose to display just numbers, a graphical MicroChart visualisation of the numbers, or both (see below). The MicroCharts do not hinder the core functionality, such as drill down or pivoting, and can be turned on for any of the measures.

There's a lot more functionality worth looking at, you can find out more at www.xlcubed.com.

Executing Rule Sets in PerformancePoint to Move Data

As I mentioned in my post a few weeks a ago, the PEL Allocate statement is a powerful method of moving data between PerformancePoint models.

Although it's powerful, the end result of an Allocate statement is simply that the destination model will contain data that has been queried from the source model. What if the destination model already contains data for the target scope? If this is the case, then you will have double counting. When creating the Allocate rule, there is unfortunately no option that lets you decide what you want to do with existing data. Ideally you need to have the 'Existing Data' option that you get when running an association, which gives you the 'Append', 'Scoped Replacement' and 'Full Replacement' options.

To get around this, I tend to place two rules inside a rule set. The first rule is a SQL implementation assignment rule with the same scope as the allocate rule, and deletes existing data by using the PEL statement this = NULL inside the rule. Then the second rule is the allocate statement, which appends the data. This is shown below:

The idea is then to execute the rule set, which will execute each of its rules in order. The advantage to doing this is that your whole data movement process can be executed in one clean step (perhaps scheduled or via Excel), without the hassle of executing several individual rules.

Assumption Model Design Techniques and Business Rules

PerformancePoint assumption models are very useful if you want the data in one model to drive and affect another model. However, if you’ve been using assumption models, then the chances are that you may have noticed that they can sometimes be a bit awkward to use. This post highlights a couple of issues that I’ve experienced with assumption models, and provides a few workarounds.

Firstly, when you initially link the assumption model to the main model, you may get the error message ‘the assumption model uses different member sets for the following dimensions’, as shown below:

Lets presume that you ignore this warning and continue to link to the assumption model. Further down the line, when you come to write your PEL rule to pick up the assumption model data, you may find that you get an error when validating the rule. This will occur if you want to populate a member in one member set by referencing a member in another member set, which resides in the assumption model.

To illustrate this, if we want to populate an account called ‘Total Sales’ in the ‘Operational’ member set of the main model by using an account called ‘Price’ in the ‘Drivers’ member set in the assumption model, then we would have the following PEL rule:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;
     
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [Account].[Drivers].[Price]);

end scope;

However, when validating this rule we get told “No hierarchy Drivers exists in dimension Account”. The only account member set that PerformancePoint will let you use in the above rule is 'Operational'. Note: you'll be ok if 'Price' exists in the main model, but you will just need to refer to it as [Account].[Operational].[Price]. But if 'Price' doesn't exist in the main model, then a different approach is needed.....

The simple way around it is to use an approach found in the Alpine Ski House sample application. Add the accounts (if that’s the dimension causing you problems) into another dimension called Business Driver. You can then use the Business Driver dimension in your PEL rule, as PerformancePoint is more than happy for you to reference assumption model specific dimensions. This is shown below:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;

//The rule is almost identical, but validates ok as we're using the BusinessDriver dimension.     
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [BusinessDriver].[Drivers].[Price]);

end scope;

Another problem that you may experience is when you have a difference in granularity between member sets used in the assumption model and the main model. For example, you may have an Entity member set called ‘Regional’ used in the assumption model, but a more detailed member set used for the Entity dimension in the main model.

So in the above example, we want to populate members in the more detailed 'Company' member set by using a value from the member's parent in the higher level 'Regional' member set. In order to do this, we can just use the Parent function on the current member of the main model's entity member set. Even though we've referenced the main model member set inside the MODEL function, if the current member's parent exists in the assumption model, then it will pick it up without any problems:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales],
Descendants([Entity].[Company].[TOT], 1073741823, leaves)) ;
     
this = [Account].[Operational].[Volume] * 
      //Its ok here to use [Dimension].[Main Model Member Set].CurrentMember.Parent
      (MODEL([Sales Assumptions]), [Entity].[Company].CurrentMember.Parent,
      [BusinessDriver].[Drivers].[Price])

end scope;

So both very simple design techniques, but it’s worth being aware of them before you actually start building your models.

Intercompany Eliminations Video for PerformancePoint Planning

Useful post on Norm's PerformancePoint Server Blog showing a short video on how to do intercompany eliminations in PerformancePoint.

The video is actually by Forrest Dermid, who sets the scene by explaining why you would actually want to do an intercompany elimination. He then goes on to show the relevant areas of Planning Business Modeler, demonstrating in detail the changes that you need to make to your account dimension and model properties, amongst others.

Really worth watching in my opinion. You can find it here.