Welcome to MSDN Blogs Sign in | Join | Help

I imagine that very few people who read this blog don't already know about the Microsoft BI Conference happening in Seattle this year during October 6-8. I'll be spending as much time as I can there (since I'm local its easy for me to go but I've still got to keep track of normal things happening at work).

I'm still trying to decide what sessions to go to. As a developer I don't get as much customer interaction as the PM side (or sales & support), so I'm leaning towards trying to attend as many of the customer/partner presentations on PPS as possible as well as the PPS chalk talks that should be less PowerPoint driven. In addition I will be a proctor during some of the Hands-on-Labs (HOLs).

I'm registered at msbiconference.com, if you search for "petereb" you can find my profile. (I guess even BI is social now) During this week I'll figure out what sessions I expect to be at, when I'll be in the HOLs, etc. And I'll add that to the profile in case you really want to stalk me and complain about some bug in the AddIn. If you want to influence where I go so we can meet or have some focused discussions, please write in my wall at msbiconference.com or comment here.

0 Comments
Filed under:

Its been quite a while since I've posted. I've still been hanging out in the PPS TechNet forums, but just haven't been finding as much time to post. I've had a little change in job duties that are taking some time to adjust to.

Anyway I'm very pleased to see that others in the blogosphere are doing a good job disseminating information about PerformancePoint. I just want to highlight one post in particular because it is exactly one of the topics I wanted to cover: Sacha Tomey's excellent write up a couple months ago of the new matrix option "Clear Changes After Workflow Action" that was added in SP1. Covers all the points I wanted to and all the relevant pictures. Thanks Sacha!

If you haven’t seen it yet the english builds are available! http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3444589&SiteID=17

 

I have some details about the addin performance improvements http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3445168&SiteID=17.

0 Comments
Filed under: ,

It's actually hard to say. This process is asynchronous which is why the status is initially Pending, then WaitProcess, then finally Partial/Submitted. See my other posts about assignment statuses and assignment actions for more details and a quick overview. Ok so we understand this, but just when-oh-when will data appear in the cube for others to see after I click submit? Well, really, it depends. Otherwise, you wouldn't need this post. There are actually a couple of asynchronous processes that further complicate the description, and even then I can only give you the details so that you can make rough estimate. In general, I think you will have to experiment with your implementation to see what practical guidance you can give your users and balance that with the server resources available during high data submission volume periods.

 

Stage 1 - Contributors choose a Submit option for their assignment.

At this point when the add-in shows a confirmation dialog the submission has been placed into the asynchronous queue. The time it takes for this will vary based on the network connection between the add-in and the front-end server that hosts the PPS Planning web service. The number of values and formulas entered (and if "Include Workbook" has been checked, the size of the workbook) determine the amount of data that needs to be transferred. (Technically so does the number of dimensions in the fact-table, but generally for scalability scenarios the number of rows is more important than a single row's size). At this point the assignment status will be "Pending".

 

Stage 2 - Planning process service processes the item in the queue

If the queue empties, then the planning process service will wait and check the queue again later. The amount of time it waits is called the “Poll interval (milliseconds)” under the “Planning Process Service” tab in the admin console:

PlanningProcessServicePollInterval

If the queue contains something when one of the items is done being processed then it will immediately process the next available item. i.e. there is no waiting. So generally, for scalability, I've seen our server team recommending poll intervals of at least a minute (60000 ms). During periods of high submission frequency like the end of a budgeting cycle or some other deadline. The admin will have to balance the desire for near instantaneous processing with the database and server workload.

Obviously in addition to this poll interval the time to actually process the submission is necessary. This includes checking the submission for data validation problems or permission errors. Then deleting the old records in the fact table and adding new ones. As well the status and changelist needs to be updated for the assignment and other metadata. And of course the partition that the data was loaded into needs to be marked dirty so that the cube will know to read the data when it is reprocessing.

After all this the planning process can go back to the queue to look for more work or to enter a wait period... At this point the status for the assignment will be "WaitProcess".

 

Stage 3 - Cube reprocesses dirty partitions in the fact tables

A separate interval is defined in the admin console called “OLAP cube refresh inteval (seconds)” under the “Workflow” tab:

WorkflowOLAPCubeRefreshInterval

On this interval the partitions for each fact table will be checked. If any of them are dirty then the cube is told to reprocess itself. Analysis Services is pretty smart so it will only need to reread the data from dirty partitions in the fact tables. So this will limit the amount of time that it takes to transfer updated data from the SQL tables to the AS cubes. Then there is some calculation time in the cube before it can start publishing the new data. During processing the old data is still available, so there's no down time. Finally after this point, the assignments that were waiting for processing will be marked "Partial" or "Submitted" as needed...

Here there's not such a straightforward guideline for what to set the interval to. For small data submission volume a lower value in minutes or seconds might be ok. But for really really large data volumes where immediate access is not critical, some organizations might even set it to values in the hours. It all depends on how close to real-time the data is needed and whether or not the server hardware can really handle that kind of workload.

 

So does that explain why its hard to say exactly when your data will be available to others? Experimentation and a little patience are in order. Really once you've used PPS for a few cycles I think the time from submit to availability becomes pretty familiar.

 

A quick side note: an assignment cycle just blocks further additions to the queue. So for contributors who are really pushing the deadline, all they have to do is get the confirmation that their submission was accepted before the deadline. That way the deadline is still meaningful and fairly predictable to AddIn users.

 

Playing in the land of Peter Eb. while writing this post...Criminal by Fiona Apple from Tidal

To filter rows and columns by a filter selection has previously been discussed here. But one thing you might not have noticed is that the list of property values (aka the filter scope) is static. If a new member property appears the form needs to be modified to include it, even if the “all” button. This is different than a normal filter, where the UI allows a dynamic filter scope by a number of expressions. Luckily in the RDL both kinds of filters are stored in the same fashion. The only thing that makes a dimension property filter (DPF) different is that the hierarchy defined for the scope query is the attribute hierarchy that is also created on the AS cube. So in the report definition itself the scope can be modified from a static set to a dynamic set by looking at the RDL for a regular filter and deriving the kinds of expressions supported.

Warning, this involves going to the RDL tab of the report properties editor and editing the extensions that the PPS Planning AddIn uses. I’ll readily admit that this not always a trivial task. It takes some practice to map from the regular treeview tab to the RDL tab. Going into that level of detail is a topic for another post. So for now I’m going to assume that you’ve done some experimentation and know what you’re looking for. (As a hint, while experimenting in your-land use a simple matrix and only 1 filter at a time, it cuts down dramatically on all the extra XML you have to wade through.)

So then, here’s an example of just the HierarchyMemberSets for a normal filter with “Entity.CorpMgmtEnt.All” and “Entity.CorpMgmtEnt.All (children of)” selected as the scope:

   1: <HierarchyMemberSets>
   2:   <Hierarchy>[Entity].[CorpMgmtEnt]</Hierarchy>
   3:   <MemberSets>
   4:     <MemberSet>
   5:       <Member>
   6:         <Label>ASH</Label>
   7:         <UniqueName>[Entity].[CorpMgmtEnt].&amp;[5018]</UniqueName>
   8:       </Member>
   9:       <Selection>Members</Selection>
  10:     </MemberSet>
  11:     <MemberSet>
  12:       <Member>
  13:         <Label>ASH</Label>
  14:         <UniqueName>[Entity].[CorpMgmtEnt].&amp;[5018]</UniqueName>
  15:       </Member>
  16:       <Selection>Children</Selection>
  17:     </MemberSet>
  18:   </MemberSets>
  19: </HierarchyMemberSets>

The key is for a single static selection you’ll see “<Selection>Members</Selection>” and for a dynamic selection you’ll see “<Selection>Children</Selection>” where the type of selection varies. Once you figure out the kind of selection you want by looking at normal filters we can go look at what a property filter look like. So to start with I’ll remove the Entity filter and use a Currency filter based on the property name. Here’s the HierarchyMemberSets for that:

   1: <HierarchyMemberSets>
   2:   <Hierarchy>[Currency].[MemberName]</Hierarchy>
   3:   <MemberSets>
   4:     <MemberSet>
   5:       <Member>
   6:         <Label>Canadian Dollar</Label>
   7:         <UniqueName>Canadian Dollar</UniqueName>
   8:       </Member>
   9:       <Selection>Members</Selection>
  10:     </MemberSet>
  11:     <MemberSet>
  12:       <Member>
  13:         <Label>EURO</Label>
  14:         <UniqueName>EURO</UniqueName>
  15:       </Member>
  16:       <Selection>Members</Selection>
  17:     </MemberSet>
  18:     <MemberSet>
  19:       <Member>
  20:         <Label>US Dollar</Label>
  21:         <UniqueName>US Dollar</UniqueName>
  22:       </Member>
  23:       <Selection>Members</Selection>
  24:     </MemberSet>
  25:     <MemberSet>
  26:       <Member>
  27:         <Label>Yen</Label>
  28:         <UniqueName>Yen</UniqueName>
  29:       </Member>
  30:       <Selection>Members</Selection>
  31:     </MemberSet>
  32:   </MemberSets>
  33:   <MemberProperties>
  34:     <MemberProperty>MemberName</MemberProperty>
  35:   </MemberProperties>
  36: </HierarchyMemberSets>

So now we can see why the selection is static: They are all listed separately (just ignore the fact that the uniquename is not really a uniquename, that artifact is a topic for another post). You’ll note that the hierarchy though is using the attribute hierarchy created by AS so you might have even guessed that the AddIn might generate MDX supporting expressions for DPFs. In fact, I had forgotten about this until somebody posted in the forums a month or so ago that they wanted this scope to be dynamic.

Anyway, in my example I want all the currencies to be available no matter what changes are made to the dimension. So I’m going to change the scope to be the MDX expression “[Currency].[MemberName].[All].[Children]”. First I remove all the static member sets and insert a proper uniquename:

   1: <HierarchyMemberSets>
   2:   <Hierarchy>[Currency].[MemberName]</Hierarchy>
   3:   <MemberSets>
   4:     <MemberSet>
   5:       <Member>
   6:         <Label>All</Label>
   7:         <UniqueName>[Currency].[MemberName].[All]</UniqueName>
   8:       </Member>
   9:       <Selection>Children</Selection>
  10:     </MemberSet>
  11:   </MemberSets>
  12:   <MemberProperties>
  13:     <MemberProperty>MemberName</MemberProperty>
  14:   </MemberProperties>
  15: </HierarchyMemberSets>

Then click Validate RDL to make sure there are no syntax errors. Then click ok and the report will be refreshed. Now using the filter should give the same values as the static selection, but when a new currency member is added with a new value for the property MemberName it will automatically appear in this list. Voila! Not so simple, but not so hard.

 

Playing in the land of Peter Eb. while writing this post...Pigs in Zen by Jane's Addiction from Jane's Addiction

(Sorry for the gap since my last post, things have been a bit busy lately in Peter Eb. land…)

One simple way to do this filtering is to enable suppress blank rows or suppress blank columns. Then when there is no data (non-null) in the cube for those rows/columns they won’t be shown. This is basically turning on NON EMPTY for an axis. Its useful in a lot of scenarios, but is also very heavy handed. You don’t get any all null rows or columns which may not work well for scenarios where data isn’t seeded.

This usually gets authors started, but quickly they will want to only show data that is relevant for the filter selection without filtering out all blanks. The feature we built for this scenario is a “dimension property filter” (DPF). Instead of adding it like a normal dimension its a matrix action in the authoring pane.

What a DPF allows the author to pick a dimension property that controls the filtering. Under the hood we use the sub-select MDX syntax to create a sub cube which actually performs the filtering. Then the user selects the value that controls which members from that dimension are displayed. So if you wanted to control which type of accounts are displayed you can start with the common Time/Scenario on columns and Accounts on rows, start with All Members so we can see a report with far too many accounts displayed…

Report7-TooManyAccounts

Then add a dimension property filter by selecting the matrix in the action pane and choosing “Add dimension property filter” instead of “Select dimensions” you’ll get this dialog where I’ve already chosen Account and the Debit_Credit property.

 Report7-DPF

The values selected (the list on the right) controls filter scope for the DPF (see my previous post for a few more details on filter scopes). You can pick a subset of values or choose to include them all in the scope. So using this mechanism a lot of different kinds of filtering possibilities are possible. If you are working with a dimension that allows properties to be custom defined you can have a process that highlights not just categories like this but also flags the top-10 “valued” items to focus on. Or you might also want to flag members that don’t fit into some kind of mathematical calculation and just flag a bunch of members as “important”. (Yes MDX has the topcount function, but you might not want to have to recreate the logic for this set over all your various reports. And named sets would be another good solution for both these scenarios, but the PPS member picker doesn’t support them, yet)

And here’s the matrix now with Credit chosen:

Report7-JustCreditAccounts

And that’s it!

 

 

Playing in the land of Peter Eb. while writing this post...Give Me One Good Reason by Blink-182 from Take off Your Pants and Jacket

Workflow handles jobs, cycles, assignments and more. In keeping with my focus on the PPS Planning Business Client (aka Add-In for Excel), this post will just cover those actions related to assignments. In keeping with my new analogy of describing workflow as the paper/check-based banking system I'll try to use some comparisons as well, although the analogy doesn't hold as well when we get to this level of detail. I'll also refer you to my previous post that described what the various assignment statuses mean. For now I'm not going to go into the details about "Include Workbook" nor what happens in the local cache nor completely what happens in the PPS system, those are other posts. I'll just give some rough commentary beyond what the official docs say since some are from the modeler perspective. I much prefer the definitions given in the add-in help (but of course I helped in the tech review of those so I'm biased). But I can't find a link to office online for them yet. Look in the add-in help for "About actions", it also improves on that web page by clarifying which user role will see the the actions.

 

Approve

This action should appear only for Approver's after the contributor does a "Submit Final". If the approver is happy with the data they can choose "Approve" and then from the add-in perspective that assignment is done. It won't appear in the 5 latest assignments for the contributor. In the banking system, this is like when the check clears. You're done except for any audits!

 

Discard Included Workbook

This only appears after a submission. Note because the RDL is always updated on the server it will appear even if "include workbook" was not chosen. In SP1 I believe that the server side will tweak the name of this action so that this distinction is more clear. In the banking world this is like when the bank sends back your canceled check. Your check is still cashed, and the money has exchanged hands. But the bank isn't interested in keeping your checks around, so they send them back. (Well they used to until everybody switched to check duplicates instead.)

There are a few reasons you might use this action. 1) Because the form template for the assignment has been updated. And if you don't discard the old one, you'll never get the new one. 2) You might have inadvertently put things in the margin notes that you don't really want the approvers and reviewers to see. This kind of gives you a chance to delete that information. 3) The contributor might have fouled up the assignment, and they need to get back to the "defaults" or what the original author intended.

 

Override Status

As an assignment owner this action can use this action to reset an assignment to a new state. Typically they would do this from Business modeler, but its not filtered out on the client side. This is useful if somebody did Submit Final but meant Submit Draft. Essentially it gives you a chance to do a manual "do-over", but it doesn't affect any of the data. In the the banking analogy.....its kind of like calling in to the help line and having them walk through some kind of operation manually. Like having them extend the date before a late fee might take effect or something along those lines. Not just the simple kind of thing you would work with a teller for, definitely need a supervisor.

 

Purge

This action only appears for assignment owners. Typically they would be doing this from Business Modeler for more assignments than just their own. But its possible if they are contributors for their own assignment to see this in the client. This completely removes the assignment but does not remove any submitted data.  As for the banking system analog: This is like having root access to the bank's systems and deciding that poof, that mortgage bill can just disappear and it doesn't matter how many checks were sent in.

 

Review

This action only appears for Reviewers. Reviewer's are just notified of the submission. They can't Reject or anything so they just get to click Review to say, "Yes, I've seen this. Thank You". In the banking world? Hmm like being a Bear Stern or Enron shareholder maybe? You don't get to do anything: just say, "Yes, Thank you, now I know you're nearly insolvent. Thanks for the head's up!" Ok maybe that's unfair and completely meant to be satirical not judgmental! Sorry, I can't really think of a good analogy for this one. Maybe its like some law enforcement agency who is tracking you and needs to get a notice when a transaction goes through. At least that's how it works in the movies and on TV in shows like CSI and Without a Trace.

 

Reject

This action should appear only for Approver's after the contributor does a "Submit Final". If there is something "wrong" they can choose Reject and optionally add a comment. This will cause the assignment to appear in the contributor's action pane again. Then the contributor can try to update or correct the data and re-submit. This is like having the check sent back to you as NSF or otherwise denied. You shouldn't take it personally, but somebody thinks you made a mistake!

 

Save Privately

With this the data entered by the user is just written to the local cache as well as the workbook and RDL. That way the next time the contributor opens the assignment from the same machine they get their same changes. Quickly I'll note that this doesn't send any data at all to the server so you have to be working with the same machine for this to work. Its like opening your checkbook and writing a check but not sending it. If you go to another check book then you have to start over.

 

Submit, Submit Draft, Submit Final

These 3 actions are very similar. They all take any data entered by the user and try to write to the fact table, and then get published to the cube. One thing that seems to surprise some people is that all of these actions make your data public, even if approval or review is part of the assignment. There are a couple ways to build your models so that it is possible to delay making your data public until after approval but that's another post.

The first difference between them is that typically "Submit Final" can only be performed once, while "Submit" and "Submit Draft" can be performed multiple times. When there are approvers and reviewers the contributor needs to be able to indicate that their work is done. That's what "Submit Final" is for. Sometimes contributors will only see "Submit". This usually means that there are no approvers and reviewers so they can Submit as many times as they like up to the deadline and there is no way to indicate that they are done.

So here there's no good analogy with the banking system. For the most part its the same as putting a check in the mail. Somebody on the other end might be reviewing it and so you could get the bill again, but more often than not your check is just cashed and that's the last you hear of it until you get the next bill.

 

And that's about it. If there is still something that's not clear post to the forums or post a comment here. But by far the best option is to click the Send Feedback link in the add-in help. That goes directly to the documentation writers. They are constantly updating the content and looking for more things to post online. I think its great that we have the capability to direct that feedback directly to the teams responsible for it, so I have to encourage you to take advantage of that!

There are 2 kinds of ranges that the PPS Planning Member Picker supports: 1) From the selected member to an offset from current period (or vice versa). 2) Or from an offset from the current period to another offset from the current period. (It's also possible to simply use an offset from the current period without a range specified). When using the member picker with Time look for the special dropdown that appears - hopefully this isn't surprising - called...."Time"!

Report5-MemberPickerTimeHiLite

Very commonly I see demos and questions about seeing actuals for the past and forecast for the future. In fact I blogged about how to do this asymmetric crossjoin previously. Now I can go back to that sample and update it so that the matrix could be used in a rolling forecast scenario. (Where the form is authored once, but used in a recurring assignment cycle...)

As a reminder this was the last selection for time:

Now we will add a couple dynamic ranges for showing the last quarter of actuals, and the next 3 quarters of forecast. Here's the next 3 quarters as an example:

 Report5-next3quarters

So now if you recall I have actual/forecast and last 3 months/next 9 months and defining intersections is trivial. And my report looks like this (current period is still set to ASH default):

Report5

I'll leave it as an exercise for the reader to experiment with the other 2 options related to current period. Looking at the previews of the members they will insert should now be pretty self explanatory...

 

What about custom MDX, this current period thing doesn't exist in AS/OLAP?

Commonly current period functionality is handled in OLAP by using a calculated member or by setting the default member in a hierarchy. But PPS has cycle's which inherit the current period when they are instantiated. (So that, of course, data entry form's don't jump during an assignment) To do this PPS will replace the text "$CurrentPeriod$" in the query before sending the MDX to AS. So custom MDX authors can simply use the same text in place of a specific member reference. (Probably I'll have more examples in later posts, or you can experiment with view MDX to see the MDX that the add-in generates to see $CurrentPeriod$ used...)

 

Playing in the land of Peter Eb. while writing this post...Tiki God by Presidents of the United States of America from Presidents of the United States of America: II

For data entry forms (and even some reports) its pretty important to keep contributors focused on their specific task. Its especially easy for users who are not familiar with OLAP or PPS to be confused by some things: "TimeDataView" and "Business Process" are two dimensions in particular that are just not relevant to expose to contributors most of the time. To create a data-entry form with a writable region, however, its necessary to use each dimension in your matrix, so let’s see what we can do about this...

Here's a matrix with some distracting filters – the highlighted ones that most contributors don’t need to know – at least for this hypothetical scenario:

Report6-ConfusingFilters

 

Now lets go to the report properties editor, where when you select a filter some properties can be edited:

Report6-FilterProperties

 

Marking the distracting filters hidden will hide it in the Excel worksheet, but not the filter popup dialog:

Report6-Hidden Report6-HiddenStillInPopup

 

Marking a filter locked will hide it in the filter popup dialog:

Report6-ConfusingFiltersReport6-HiddenAndLockedNotInPopup

 

Marking a filter hidden and locked will hide from both the worksheet and the popup:

Report6-HiddenReport6-HiddenAndLockedNotInPopup

In the authoring UI the filter is always shown, which concerns some people. In my experience, it looks like not a lot of contributors are distracted by this. They focus on the areas in the workbook where they are working. And when they do need to change filters they use the popup, and so don't see the "extra" dimensions that can be confusing. (There’s no way to disable the authoring UI for certain reports or for assignments. Users should be able to still use authoring to create their own matrices. And there are valid scenarios to allow them to edit even the data-entry matrices for their ease. Probably in a future version though there will be a way to lock specific matrices added - at least in assignments for the scenarios where users don’t need to make any changes. It's a relatively common request anyway - but of course I can't make any guarantees.)

Workflow in PPS Planning is completely asynchronous. This allows the server side to scale and handle many, many simultaneous submissions, job requests etc. Its possible to add more server's to the pool that handles this asynchronous queue. From the add-in perspective there are 2 things that interact with workflow: Jobs and Assignments. How Jobs work is another post, today we're just going to look at Assignments and even then just the different statuses. This is already documented pretty well in the add-in documentation. (I can't find anything in office online yet, but look for "About assignment status" in the add-in help and you'll get table describing the states.

BTW: my new analogy for how workflow works is the banking system - especially via paper checks. Instead of assignment submissions, think of writing a check and putting it in the mail. By the time the receiver gets the check and asks its bank to cash the check any number of things could have happened to your bank account or even the bank! When you pick a workflow action its like asking the bank to do something. Generally they put you on hold and then say its all taken care of. But later you still need to check your statement to make sure that the promised operation really did happen. Let me know in the comments if this is an apt analogy or if it just confuses the issue...

Now on to the various statuses you are likely to encounter for assignments:

 

Approved

After all approver's have performed the "Approve" action we reach this state. This is like both you, the bank, and the third party agreeing that your check has cleared and the money has exchanged hands. Pretty straightforward I think.

 

Failed

Because the data writeback process is asynchronous its possible that the add-in was out of sync with the server. For example, maybe the form was published prematurely before the administrators realized that permissions were set wrong on the model. But the user had already changed data and submitted to the queue before the permissions changed. Then when the submission was processed it tried to write to the wrong data and a permission error was encountered by the data validation logic. Kind of like bouncing a check. In some kinds of models (financial models usually) its also possible that there was data validation beyond what the server advertised in the writable region that failed.

 

Not started

When assignments are first created they are in this state. Unless a contributor specifically goes to the Assignments dialog and searches for an assignment they won't see these. This status just means that the data entry cycle for the assignment hasn't opened and there is not yet any work for the contributor to do. Contributors generally shouldn't have any available workflow actions for this status. I guess this is like when the bank isn't open and their web site is down.

 

Partial

If the action "Submit Draft" is available to a user, this is the status the assignment will reach after the submission is successfully processed. Any data included in the submission will have been written both to the fact table and is available in the cube. Generally the same actions that are available during "Started" will be availble for this status.

 

 

Rejected

When there are approvers for an assignment they can "Reject" the submission. Probably for a budget/forecast kind of scenario it means the boss wants you to spend less and earn more. Or for an HR scenario you have to reduce headcount or live with what you got last year. Sound like a familiar scenario? Here the contributor will have the same submission options they had previously but generally are expected to modify the data and resubmit. In the banking analogy, this is like the business says "we don't take Discover" or the frustration I have as an American in Europe "we only take MasterCard with the smart chip". You have a "do-over
in American slang.

 

Reviewed

All reviewers for the assignment have reviewed it. I don't know yet how this one fits into the banking system. Anyway it makes enough sense since it happens after the "Review" action, doesn't it?

 

Started

This is usually the status an assignment will have the first time contributors open an assignment. Assignments do not appear on the action pane until they are in this state. This status simply means that the data entry task for a contributor is ready to be worked on. (Although data entry is assumed for an assignment, there is no requirement that an assignment require the user to enter data.) The submission actions generally should be available for contributors in this status. Its like when a bank or store is open: everybody is ready for business (although no guarantee that by the time you see a teller that the computer's aren't down).

 

Submitted

After a successful submission action the assignment can reach this state. Just like any other submission aciton, the data is written both to the fact table and is available in the cube. If the assignment allows multiple submissions, then "Submit" will be still be available. At this point the contributor might be done with the assignment and no further action is needed at this time. But it will continue to appear in the action pane while the assignment is still active.

 

Pending

After an action has been chosen and it is in the workflow queue the assignment will be this technical status. It means the server either hasn't processed the request or is still processing it. Generally there are no actions available to the contributor at this time. They will have to refresh the system data or search in the assignments dialog to wait for the assignment status to change. (There is also the possibility for an e-mail notification but that's another post.) This is basically "the check is in the mail" status.

 

Wait for Process

This is another technical status. It means the server has removed the assignment from the queue and done any fact table updates. But the AS cube is also updated asynchronously. It will remain in this state until the cube has been reprocessed, hence the user just has to wait until the cube is processed. Again they can refresh system data or search in the assignments dialog for an upated status. This is kind of like when the bank says one thing, but the business you are trying to pay says another thing.

 

Ok that's it. Shortly you can expect a post about the workflow actions... Really I think the add-in docs are quite clear on these statuses, but I still get questions. Really if the docs are unclear you should click on the "Send Feedback" link that appears in every page of the docs. That feedback really does go directly to the documentation team and they assure me they make use of it. So please help point out when I reviewed the docs that I missed something wasn't clear. (To be fair though, for that part my job is just technical accuracy. The professionally trained writers are the ones who do the hard work of writing good prose...

This is a confusing error for users and form authors alike, especially since the add-in help does not have a glossary entry for "filter scope". I fear I came up with the error text, so I apologize. So to hopefully rectify this situation, we have this post. (Plus technically its not an error, it's a warning. But that's okay, everybody calls it an error.)

Authoring filters is distinct from authoring rows or columns, in that you both "Select Members" for them and you "Select Default Filter Value". Select members corresponds to the scope. And the default filter value is the initial filter selection that you see when opening a report. When filters are added via the action pane, by default the scope is is empty. And empty scopes are interpreted by the add-in as the entire hierarchy. If you cancel when prompted to select a value for the filter and then go to select members for the filter you can see this. Authoring via the Report Wizard is a little bit different, but in SP1 will be more consistent.

So now hopefully the message makes sense conceptually, but how does the matrix or filter get into this state? When a matrix returns no data grid, the add-in runs an extra query to make sure the filters are not set incorrectly. Especially as authors get used to the PPS Planning security model and assignments it can take a while to understand how to generate a form with a filter scope that is meaningful to end users with more restrictive permissions. So when the result of the extra query says that the list of members resulting by using the users permissions for the filter scope does not contain the filter selection then this error/warning is presented. i.e. with the users permissions the selection is outside the filter scope. All this is meant to do is explain why no data is returned, so its just a warning if a matrix can't render. But generally users aren't happy when no data is returned so for all intents and purposes people treat this as an absolute error condition.

It just means that the user needs to update the filter selection to a new value (although frequently if the form is authored incorrectly, there won't be useful things in the scope). And if the scope is completely empty then the add-in should show the entire hierarchy as the available members but the whole point of the scope is to reduce the choices end users have to make, so even this may still be confusing. (Unless of course the user has no permissions in the dimension in which case they can't pick a member and the whole situation is even more confusing for them)

 

So hopefully this resolves not just some of the confusion around this message but also helps to understand filters, selections and scopes.

 

 

Playing in the land of Peter Eb. while writing this post...You Lied by Green Day from Shenanigans

1 Comments
Filed under: , , ,

A common request by form authors is to create a "start here" worksheet page where contributors can change some global filters. Then they advance to other worksheets in the workbook to do their actual data entry. Other times authors just want to link multiple matrices so that changing the filter on matrix A also changes matrix B at the same time. The way RDL is structured makes it natural for PPS to also define all filters as global filters: the add-in stores them naturally as ReportParameters in RDL which are global. Then each matrix is subscribed to the filter.

So let me give a quick run through of how this works, because there isn't a lot of hinting in the UI that this is possible. First I rename Sheet1 to "START_HERE" then switch to Sheet2. Second, on Sheet2 I add 2 simple matrices with Account on rows, Time/Scenario on columns and Entity as a filter for just the first matrix. That looks like this:

Report4-2Matrices

Here's what we see in the report properties editor at this point: 1 global filters, 2 matrices and the global filter being used in just one place (Matrix1):

Report4-Properties1MatrixUsesFilter

Next either right click on the filter node circled in red and copy to the clipboard. Then right click on the matrix node outlined in blue and paste. You can also use the traditional CTRL-C/CTRL-V keyboard shortcuts or simple drag-and-drop the filter node to the matrix node. You should end up with this (another reference to the filter on Matrix2):

Report4-Properties2MatrixShareFilter

Now click ok and let the report render itself to see that both matrices have the filter rendered:

Report4-2MatricesSharingFilter

Now when you change one of the filters, both matrices will be refreshed. You can see in the filter dialog that both matrices are listed to provide a clue to the user that this is happening and they will both be affected by the change:

Report4-FilterPopup

Now back to the START_HERE sheet I add another matrix. But this time I put nothing on rows or columns or filters (Measures will automatically be added to columns so that the MDX query will run properly). Obviously nothing interesting will render for this matrix yet. Then in the report properties editor I add the global filter reference to Matrix3:

Report4-PropertiesStartHere

Now this is how the START_HERE sheet renders:

Report4-StartHereMatrix

Typically the author will then hide the Value row that will appear using Excel's hide row feature and add a whole bunch of text into the sheet describing how to perform the data entry process that goes with the assignment (or how the other matrices are to be interpreted for a report). Sometimes you might want to provide some high level overview data here instead of hiding the Value row. It just depends. But the main thing I'm trying to show is if this is used in an assignment the contributor only has to pick their Entity once. All the rest of the matrices are automatically updated (and perhaps have the filter rows hidden on those sheets). This gives authors a lot of flexibility in making a streamlined experience for contributors working in assignments. Even in reporting this is a convenience to keep a few matrices in sync automatically. Another common request that fits with this scenario is for the filter to automatically update based on which user opened an assignment or something similar, that's a topic for another post.

Short answer: In SP1 the PPS Planning add-in will use the where clause and life for MDX experts will be back to normal.*

Long answer: Because of the extra properties needed for writeback. The CellSet returned by AdoMd.Net does not return member properties for members specified in the where clause. What additional properties the add-in needs is a topic for another post. In many cases nobody notices that by putting the filter selections on pages (which is more like a crossjoin of a single element than a where filter) that there is any difference in the filter. But everybody notices when they take try to run the Matrix query in most other MDX tools like SQL Management Studio:

Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet
Formatting.
Results cannot be displayed for cellsets with more than two axes.
Execution complete

(Trust me, that display error irritates me an awful lot, just so you know I feel your pain if you don't like this. It's definitely something I am very excited about in SP1 that won't impact many users. But for the troubleshooters I think it is a fantastic luxury to just cut and paste the query for a change...ok you will still have to search and replace $CurrentPeriod$...)

In the matrix itself we don't really render a multi-dimensional result, we just render the first slice so that it looks like a 2D result. (And for our query we try hard to make sure that the 3D result we produce only has a single slice returned which is equivalent a 2D result and no extra data is queried other than the member properties).

So in practice the "on pages" approach worked well to simplify the rendering engine at first. But crucially there are some cases we've found where it degrades performance of the MDX query. And at least in current builds of AS2005 that PPS depends on, the cost of running an extra query to retrieve the missing member properties is negligible. So we were able to make this change in SP1. There simply wasn't enough time for us to have made this change earlier - and I don't remember if when we were working with pre-release versions of AS2005 if the second query for properties was always of negligible cost.

Another convenient side effect of this change in SP1 is that the filter selection is now conveniently available in custom MDX scenarios...but that, of course, is another post.

 

* As of this writing I can't promise any dates for SP1. All I've seen is what it pretty commonly known: spring 2008. We of course have an internal schedule and targets, but software releases are not easy. When the date is known and "official", It will be posted quite widely, on clearly "official" microsoft.com sites, including the technet forums as soon as there's an official date. (Oh and don't worry, asfaik the core PPS dev/test/pm team is based in the northern hemisphere so spring isn't 6 months away.)

This time there's nothing really specific about PPS. It's more of an Excel post actually... 

I've seen this a few times. Sometimes the data form authors want to show is big, and they want to show 1 instead of 1000 or 1 instead of 1000000. There are a bunch of complicated games you an play with formulas and rounding to get close to this behavior. But in this case you shouldn't settle for close. This is bizarrely not explicitly shown in the Excel UI, but this is well supported by the custom number format functionality. You just use a custom number format:

in thousands: #,

in millions: #,,

NumberFormatI1000s

Not sure off hand what to do in other regional settings. You may still have to use a comma "," or you might need to use the decimal separator "." or whatever it is called in your culture... In my test the number format works well:

ShowIn1000s

 

Although I agree formatting a matrix properly is a little tricky until you get the hang of it, unfortunately this is a short post and I'll save larger formatting discussions for another post. For data entry into cells with this formatting, you still type the entire number. If you want to avoid that part too then I suggest doing that conversion in the fact table to begin with...

Also phrased: How can I create an "asymmetric crossjoin" without using custom MDX? MDX experts will probably recognize this issue right away and understand how to build a matrix from scratch for this. But not everybody recognizes that the add-in has specific support for this scenario. It's called "defining member intersections" and it can be done for both rows and columns. To activate the feature select your matrix in the action pane and choose the appropriate item in the available action drop down then click the green go button:

Report3-MatrixAction

Anyway to use it we need a matrix. So to start with lets create a typical matrix with Account on rows and Scenario/Time on columns. I'm going to pick Actuals and Forecast for Scenario, and then all the months for 2008:

Report3-Symmetric

By default the add-in is doing a "symmetric crossjoin" and rendering too much data for our goal (I had to shrink the column width just to be able to get the image down to a reasonable size but clearly show Actuals and Forecast plus all 12 months duplicated. Obviously at this point we could simply hide rows and columns using Excel functionality but for a large matrix this is going to pull down a lot of extra data that we don't really need for this scenario. So let's go ahead and see what happens when we define member intersections for columns with the matrix so far:

Report3-AllIntersections

Let me draw your attention to the rendering options images along the bottom of the dialog. They are meant to visually hint the difference between rendering all intersections and one by one. So now I switch to column by column. Now as the picture indicates I have to match each member in the Scenario column with the appropriate member in the Time column. To do this I click the appropriate "Edit..." area to invoke the member picker where I duplicate the selection (just click add extra times while highlighting actual. Then make sure the order is 3 Actual's followed by Forecast. Here is what Actuals looks like duplicated 3 times in the member picker:

Report3-ActualDuplicated 

Intuitively you might think I now have to duplicate Forecast 8 times for the remaining 9 months, but there is a special case for this: The last member in any dimension will automatically be applied/duplicated to match the other columns. So my final define intersections choices look like this:

Report3-Defined1

(Those red lines I added just to be explict about where the "intersections" are) And the matrix renders like this:

Report3-Asymmetric

(I added the red oval and removed the matrix style so it was easier to see the dividing line between Actuals and Forecast. Typically when we are showing data rather than blank and have a writable region this is much more clear...)

This technique works for more than 2 dimensions. Although I'll be one of the first to admit that while duplicating your selections can be a little tedious sometimes, defining intersections is a very powerful feature. I personally haven't seen a lot of OLAP front-ends that allow this. One popular alternative seems to be to start with everything and then cut out the intersections that you don't like. That frequently reverts the design to static selections which isn't necessarily as convenient. What I've shown so far is also static selection since we had to do some duplication and picked each month individually. That actually made this process more tedious than necessary... Let's go back to how I selected the time members originally:

Report3-TimeStatic

I this case I know I want the first calendar quarter to be used for Actuals and the rest of the year for Forecast. So let's define that using some sets. I'll pick lowest of Q1, then lowest of Q2, Q3 and Q4:

Report3-TimeDynamic

Those more familiar with OLAP might notice that there are many more ways to have expressed this set...that's a topic for another post. For now I'll just point out that if you can describe your set carefully then the tedious part of duplicating selections for column by column or row by row becomes unnecessary. There's even clever ways to describe the set as "from the beginning of the year until now" or "from now until the end of the year"...but that is also a topic for another post.

So I'll leave you today with some final images. First you can see there was no need to duplicate Actuals after using a set for the first part. And since the last selection gets repeated as many times as necessary I also didn't have to duplicate Forecast. I really encourage you to explore the member picker to become familiar with the kinds of expressions and sets possible. It can really make your authoring life that much easier. My goal is to help PPS report/form authors avoid learning MDX for as long as possible! (The matrix image is the same as the first asymmetric image, I'll leave it as an exercise for the reader to confirm I didn't cheat when I reused the same image)

Report3-ActualOnce Report3-Defined2

Report3-Asymmetric

 

The process is the same for rows, with just "column" in the UI replaced by "row". So I won't go through that specifically. From what I've seen the most common question around this involves Time and Scenario - especially for data-entry form templates.

More Posts Next page »
 
Page view tracker