Interesting to see yesterday that XLCubed have released version 5 of their popular OLAP reporting tool. As I've mentioned before, I've always found XLCubed to be very powerful and easy to use, and therefore, in my opinion, it's definitely one of the best Excel OLAP reporting tools out there.
Version 5 sees the introduction of some new features in the Excel/web editions, namely:
- Enhanced Excel integration with 'MicroCharts';
- An improved user interface in the Web Edition;
- Print to PDF and enhanced save to Excel options in the Web Edition;
- Fine grain control over publishing Excel reports to the web.
Personally, I'm used to using the Excel Edition to slice and dice cubes to get what I want quite easily. Although it's not entirely new to V5, you can carry out the same kind of ad-hoc analysis in the Web Edition, as shown in the following video. The end result is that you are able to build the kind of dashboards as shown below, whether in the web or the Excel edition:
As usual, the evaluation editions for the Excel and Web versions can be downloaded here
I've noticed that the PPS Technet documentation has been updated recently to include an official Microsoft method to carry out auditing in PPS Planning.
PPS will do some basic auditing out of the box, namely to the audit.log file on the server. This will automatically capture key events that occur on the server, e.g. creation of a model, updating of a dimension etc. The audit file does not, however, track changes to the model fact data. There has been a custom solution around for this for a while now - Sacha has written an excellent post here that details what you need to do in order to implement your own PPS audit trail.
Like Sacha's method, the Microsoft approach involves creating auditing tables, which should then be populated by running a custom stored procedure. The stored procedure should then be scheduled on a periodic basis (e.g. hourly) to capture any new activity. This is a bit different to Sacha's method, where triggers are used to capture changes in real-time as they occur. In both cases the idea is to use something like Reporting Services to to view detailed auditing reports on your PPS data.
One thing that did catch my eye on in the Technet documentation is a method to decode the binary 'change list' column that's held in the dbo.Submissions table. Whereas you can manually export the change list to a CSV file, there has historically been no way to take what's in the change list column and automatically decode it into a useful format. The following C# code will read the change list, and then insert it into your newly created auditing table:
DataSet ds = new DataSet();
DataLayer dl = new DataLayer("PPSConnection");
ds = dl.ExecuteDataSetFromSQL("SELECT [SubmissionID]FROM [_AppDB].[dbo].[Submissions] s1 where
s1.SubmissionID not in (select SubmissionID from [_StagingDB].[dbo].[SubmissionsAudited])
and s1.[Status] = 0");
string sSQL = "";
foreach (DataRow r in ds.Tables[0].Rows)
{
sSQL = @"INSERT INTO SubmissionsAudited(… ) VALUES(";
// RETRIEVE THE CHANGELIST FOR THIS SUBMISSION
DataSetWrapper dsw = new DataSetWrapper((Byte[])r["ChangeList"]);
foreach (DataRow cldr in dsw.DataSet.Tables[0].Rows)
{
// SUBMISSION ROW DATA
sSQL += r[0].ToString() + ", "
+ r[1].ToString() + ", "
+ r[2].ToString() + ", "
+ r[3].ToString() + ", '"
+ r[4].ToString() + "', ";
// CHANGELIST ROW DATA
foreach (object o in cldr.ItemArray)
{
sSQL += "," + o.ToString();
}
sSQL += ")";
}
// STORE EACH CHANGE TO THE AUDIT TABLE
dl.ExecuteNonQuery(sSQL);
Click here to view the Technet documentation.
PerformancePoint Service Pack 2 is now out and available for download!
As usual it's available in 32 or 64 bit versions:
32 bit PerformancePoint SP2
64 bit PerformancePoint SP2
I've also noticed that there is a new service pack for Management Reporter. This can be downloaded here
In the recent PPS Planning projects that I've been involved in, the challenges have often been around subjects such as business rules, hence the often recurring theme of this blog. Recently the tables were turned though, as I was told by a user that they wanted to enter dates into a PPS assignment.
I was initially a bit concerned that the Excel add-in may not be able to deliver here - after all its great at capturing numbers, but knowing the rigid structure of the fact tables, I couldn't see how it would manage to store a date. Then I remembered something from my VBA days many years ago - that is that Excel stores dates as a number from 30/12/1899, meaning in theory it should be possible to get dates working in PPS. Thankfully it is possible, as this post explains.
Excel Setup
The first step to get this working when designing your form template is to set the matrix to have a matrix style of 'none'. If you don't do this, then the built-in matrix styles will over-ride your formatting changes to the required cells. Speaking of formatting, the next step is to format the data entry cells that will contain dates, just using the standard Excel formatting window:
Once these few simple steps are done, then the assignment will behave just like any other. As the date is stored as a number, the numeric representation of the date will end up in the fact table just as any other piece of data.
Dates in Business Rules
Once the numbers are in the fact table, we need to convert them to dates to use them in business rules in some way. We can't do much in PEL unfortunately, so the options are either NativeMDX or NativeSQL.
As Analysis Services can pickup some of the VBA functions, it's possible to use the VBA DateAdd() function to convert the stored number back into a date. So in the example below, I'm using the DateAdd() function to convert the number to a date, before comparing the resulting date against another date using the VBA DateDiff() function:
WITH
MEMBER [Measures].[DateExample] AS VBA!DateAdd("d", [Measures].[Value], "30/12/1899")
MEMBER [Measures].[DateDiff] AS VBA!DateDiff("d", [Measures].[DateExample], "01/07/1987")
SELECT Descendants([Time].[Monthly].[Year].&[2008],,leaves) ON 0
FROM [Strategic Planning]
WHERE ([Account].[Profit and Loss].&[5010], [Measures].[DateDiff], [Entity].[Divisions].&[5003])
Although the above is just a simple example, it should give you the idea of the kind of calculations that can be performed in Analysis Services. It's possible to use these functions via a NativeMDXScript or a NativeMDXQuery.
It's a similar story with SQL, as it also has its own DateAdd() function, as shown in the simple select statement below:
SELECT DateAdd(d, [Value], '30/12/1899')
FROM dbo.[MG_Strategic Planning_MeasureGroup_default_partition]
WHERE Scenario_memberid = 4 AND Account_MemberId = 5010
So it's a shame that PEL can't work with dates, but the fact that both the database engine and Analysis Services have a DateAdd function means that it's possible to use dates for logic in both definition and procedural business rules.
As Chris Webb has already blogged, the Analysis Services 2008 Performance Guide has now been released.
Like the 2005 version, the 2008 guide contains a wealth of information and is a must read if you are trying to tune either query or processing performance in Analysis Services.
The document can be downloaded here.
Having posted about NativeSql business rules a while back, I though that I might as well cover NativeMdxQuery business rules also, especially as there isn't too much documentation available on the web for this subject.
NativeMdxQuery is a rule implementation type that can be used with assignment rules in PPS Planning. Rather than writing PEL and having the compiler convert your PEL into SQL or MDX, the idea is that you write the raw MDX directly if you pick NativeMdxQuery.
Why Native Rules?
When when I posted about NativeSql, I mentioned a scenario or two where using NativeSql was very useful. The advantages to writing a NativeMdxQuery, however, are less obvious. This is especially the case when you consider that a)standard MDX rules in PPS Planning are less restrictive that the SQL rules, and b) that PEL is very efficient - you can write a very concise MDX PEL rule vs writing the equivalent in MDX itself.
So what advantages are there? Although a fair amount of MDX functions are included in PEL, it's not possible to use all MDX functions/statements/operators, e.g. Case(), Item(). This is one situation where you might want to write a NativeMdxQuery.
Also, the ability to filter and restrict data in a raw MDX statement is far more powerful than the options available in PEL. For example, you can use the Filter()/IIF functions in PEL, but they're quite slow and you're restricted as to where you can put them. If you use MDX, you can use both a WHERE clause to slice the query and/or a HAVING clause for filtering.
Finally, when just writing queries from an SSAS cube, a technique that's sometimes used is to create query-scoped calculated members, by using the MDX statement WITH MEMBER. This allows you to have calculation steps in your MDX query, and can effectively be used in one sense as a temporary variable store for calculations. If you're trying to do complex calculations in PEL, you have to assign everything to the 'this' keyword. It's much cleaner to have a block of code where you can define any calculations that the main query depends on. This is what WITH MEMBER will let you do.
How to Write a NativeMdxQuery
The requirements for a business rule using NativeMdxQuery are that you need to write an MDX select statement, which specifies the target cells that will be written to. Unlike NativeSql statements, you do not need to handle how the data gets inserted - PerformancePoint handles all that for you, as long as you produce the MDX select statement.
An example select statement that makes up the entire content of a NativeMdxQuery assignment rule is shown below. The comments show what the query is doing:
--[Measures].[Last Year] just makes the rule a bit more readable
WITH MEMBER [Measures].[Last Year] AS ([Measures].[Value], [Time].[Monthly].CurrentMember.Lag(12))
CELL CALCULATION queryCalc
FOR
--This specifices the cells that we are overwriting with an expression or value
'([Measures].[Value],
[Scenario].[All Members].[Scenario].&[1],
{[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]},
{[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]},
[BusinessProcess].[Standard].[Level 06].&[8],
[TimeDataView].[All Members].[TimeDataView].&[1],
Descendants([Entity].[Divisions].[(All)].&[0], ,leaves),
Descendants([Currency].[All Members].[(All)].&[0], ,leaves),
Descendants([Product].[Product Category].[(All)].&[0], ,leaves))'
AS
--The 100 is the value that we are giving the cells above
100
--This is the select statement which cells will receive the value above
SELECT NON EMPTY
([Measures].[Value],
({[Scenario].[All Members].[Scenario].&[1]},
{{[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}},
{{[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}},
{[BusinessProcess].[Standard].[Level 06].&[8]},
{[TimeDataView].[All Members].[TimeDataView].&[1]},
{Descendants([Entity].[Divisions].[(All)].&[0], ,leaves)},
{Descendants([Currency].[All Members].[(All)].&[0], ,leaves)},
{Descendants([Product].[Product Category].[(All)].&[0], ,leaves)}))
--Ensure we only write to cells with a certain value by using HAVING
HAVING ([Measures].[Last Year] > 100000)
properties [Scenario].[All Members].Key ,
[Time].[Monthly].Key ,
[Account].[Profit and Loss].Key ,
[BusinessProcess].[Standard].Key ,
[Entity].[Divisions].Key ,
[TimeDataView].[All Members].Key ,
[Currency].[All Members].Key ,
[Product].[Product Category].Key
ON COLUMNS
FROM [Strategic Planning]
--Filter on a dimension member property by using a WHERE CLAUSE
WHERE ([Entity].[Region].&[North])
The points to note about the above statement are:
- You must connect to the correct cube for the current model;
- You don't need to include a cell calculation - but it's the way that Microsoft implement business rules that use MDX, and it's hard to see how you would get a rule to be of any use without it;
- You must include the member keys as properties, otherwise the rule will error.
Conclusion
Writing MDX is clearly not for all users of PerformancePoint, but does provide the ultimate in flexibility when compared to PEL. Most MDX queries written in PPS will use cell calculations. If you're not used to how these work, or you just want to save some time, remember that an easy way to get started is to use the debug button on an MDX PEL rule in PBM - this will output a query that is similar to the one shown above.
A highlight of day 3 for me was seeing Roberta Vork & Alyson Powell Erwin presenting a session titled 'Integrating Microsoft Dynamics Data for Planning, Analysis and Reporting in Office PerformancePoint Server 2007'. This started off with a demo of how to create PPS M&A dashboards on top of the AS cubes that come with a the various Dynamics products. Alyson mentioned that there are currently some sample M&A Dynamics dashboards available here, with more to come in the near future. Check the PerformancePoint Downloads page to get hold of them.
Roberta's part of the presentation focused on how to get data into PerformancePoint via the Data Import Wizard. Roberta demoed importing data from AX, before discussing a few of the issues that you're likely to encounter when importing data from Dynamics. One of those issues may be that your PPS models are at a higher level than the data coming in, meaning that you will have to carry out a many to one mapping in some way before the data is usable in PPS. Another interesting point from the presentation is that PPS will be able to connect to Dynamics SL in mid 2009.
The final session that I made it to was 'Mastering Data Cleansing in SQL Server Integration Services' by Rushabh Mehta. This was well presented, explaining why data cleansing is an important part of ETL. Although some of the techniques & SSIS tasks covered weren't all that new, Rushabh did show the new Data Profiling Task, which will allow you to carry out a detailed analysis of your source data, which is then viewable in the XML output or the Data Profile Viewer tool.
Both presentations are available for download:
So that's it for another year, although the date has already been set for the next conference - 6th-8th October again. Overall the conference was a success in my opinion, so I'll be doing my best to get out again next year.
Just another quick update from me, this time on day 2 of the Microsoft BI Conference in Seattle.
One of the most important sessions for me was 'Mastering Master Data Management', delivered by Colin White & Kirk Haselden. The summary for the session quite openly states that 'there is a lot of confusion surrounding Master Data Management (MDM)'. I would agree with this - but hopefully this presentation goes some way to addressing people's questions on the subject. The presentation covers some really good theory, showing that there are several different ways that MDM can be approached, although unfortunately we didn't get a look at the Microsoft MDM solution just yet.
There was also more on Gemini yesterday, including a new stand in the exhibitor section where it was possible to talk to the development team. I got to see the new 'slicer' feature of the Gemini pivot tables in a bit more detail, which is a fairly similar to the slicers/filters in QlikView.
Speaking about exhibitors, I also spent a bit of time at the Orange Peel stand, where I saw a demo of their Business Intelligence Studio product, which has actually been out for a while now. Business Intelligence Studio is an Excel add-in for PerformancePoint that allows the creation of PPS form templates with a number of interesting features, including the ability to add new dimension members from Excel, cascading filters, and the ability to run rules from Excel buttons to name a few.
Finally there was also a PPS Planning session titled 'Implementation Tips And Tricks', delivered by Celia O'Kane & Julio Triana. This session covered some useful solutions around performance, business rules and security. Again, the presentation can be downloaded here.
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.
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.
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.
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.
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.
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.
| Error Message | Solution/Explanation |
| No hierarchy ‘xyz’ exists in [dimension] | Check that you have specified the correct member set in the source, target and scope parts of the rule. |
| Member set is not defined correctly for cross model mapping. The number of members in source and target member sets in cross model mapping must match. | This message can have 2 meanings. Firstly, as it suggests, it can be that the number of members in the source and target mapping arguments do not match. Or, prior to SP1, it can be this rather more serious problem - http://support.microsoft.com/kb/942640 |
| Execution of rule ‘xyz’ failed. This may have occurred because changes have been made to the model structure without redeploying the model site. | This can occur at execution time when you try and map two members twice. E.g. if your source is {2009, 2009} and your target is {Jan, Feb}. It doesn’t like the 2009 in there twice. |
| Each dimension scope in cross model mapping must be a list of concrete dimension members | Unfortunate but true – you can’t use relative member functions such as Descendants in the 4th and 5th arguments of an allocate statement. |
| Some of the model's dimension members are invalid or missing. The model may need to be redeployed. | This is a run time error that can occur when you are trying to write to non-leaf members. The first argument of the allocate statement will contain the problem, as this is where the cells to receive the allocation are specified. Often it’s because ‘this’ has been entered as the first argument, which could result in using non-leafs for the argument, as it will inherit any non-leafs specified in the rule’s scope statement. |
| Allocation statement must have 5 parameters when they appear in Cross Model Allocation Rule. | Cross model allocation rules expect 5 parameters, in the correct format. So if you don’t have 5 parameters, then there’s the problem. However, be careful how you specify the parameters. For example, if you specify 2 members in the source mappings, such as [Time].[Monthly].[2007], [Time].[Monthly].[2008], then be sure to enclose them in brackets, otherwise the 2nd member will be interpreted as your 5th argument. |
| Unable to compile the cross model allocation rule. The target contains the dimension ‘xyx’ but no explicit mapping has been provided. | If an extra dimension(s) exists in the target model, the allocate statement requires you to specify what member you expect the data to be written to in that dimension. |
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!
More Posts
Next page »