Welcome to MSDN Blogs Sign in | Join | Help

Well the Gemini team is making progress towards shipping. Now they have a real name: PowerPivot. They still don’t have any pricing/licensing/packaging details; that is promised later this year. There’s also links to sign up for a beta. But I don’t have any more details about that.

Like all Microsoft employees I had access to a survey about the name a few months ago. I don’t remember anymore what kinds of choices I had, I remember not being very excited about them. But I do remember that it was clear we were going to be positioning PowerPivot as an essential part of the IW (information worker) experience. The marketing materials certainly back that up. But most importantly, from my experimentation with the CTPs and the internal news I hear, I think the product backs that up as well.

I think that’s why it gets branded with a name that references PivotTables, because one of the primary goals is making IWs able to build data models more easily. I originally thought the name options along those lines were too limiting, but I think it’s more and more clear how well SQL and Excel are working together to ensure there is powerful modeling and analysis capabilities available for everyone (in a SQL + SharePoint + Office environment). A name that gave more credence to the engine would not be relevant at all to the IW, so this name communicates our goals better.

Then so you don’t think the SQL team is the only team driving BI for IWs, you might be interested in some more details about how new features in Excel 2010 PivotTables. With all the emphasis on PowerPivot naturally landing in Excel and reusing a familiar environment, the PivotTable team has to keep up appearances as well! Most of these improvements are relevant to OLAP-ish scenarios, but especially the calculation options are meant to let ordinary people (IWs) do analysis, and not just OLAP experts.

Finally made it out the door! For the AddIn I have a couple notes: Besides the usual rollup of hotfixes (bug fixes) there is a new feature: Line Item Details (LIDs). This was supported by the server but not on the client side, and now it is! We did our best to get some data entry accelerators for LIDs based on feedback from those customers who needed LIDs.

For performance of the AddIn not too much changed, although on scenarios involving large submissions (10s of thousands of cells in the change list at a time) there were some improvements to reduce the size of the submission which yields a faster submission. Also the memory consumption is reduced during this step so fewer contributors should have problems making large submissions. On moderate and small sized submissions I think there is no change, although its possible for tiny submissions the extra process of shrinking the change list may add a little bit of overhead, but should not be noticeable.

For outside the AddIn I think the biggest changes are related to how security can be configured with PPSCmd, and the calendar maximum limit has been increased. The Monitoring and Analytics SP3 was also released, and if you missed it ProClarity’s latest SP was also released….

Although the model of using an Excel AddIn can be a little clunky (especially for what PPS Planning called the contributor scenario) I think my team did a pretty good job getting some very usable reporting elements. Now I get to point you to things my team didn’t do, but are native features of Excel. So you get better a better user experience (UX) overall and all these features are programmatically accessible.

Asymmetric CrossJoins

I think PPS Planning’s Excel AddIn handled these in a very easy to understand fashion. Now Excel PivotTables has support for this as describe in this post. But Excel takes it one step further and allows for better reuse of the sets. Over all I think this UX of remaining consistent with earlier releases, but still quickly allowing people to delete the intersections that aren’t meaningful to them is pretty powerful.

Custom MDX

Indeed if you read that post fully, you’ll realize that Excel’s PivotTables couldn’t previously handle this at all because of their default aggregation features. To enable this PivotTables have to be a lot smarter about OLAP vs. other data sources. So now rendering arbitrary MDX is almost a hidden feature. But besides having a friendly UX to edit sets, you can also supply your own expressions. Really they have achieved one of the most highly desired features by MDX geeks. And one of the few reasons why I still sometimes have to answer questions about using the Planning Matrix instead of PivotTables for xl2007 users.

Dynamic Sets

This was one of the frequent asks by report authors. I remember working lots to enable truly dynamic rendering with respect to the filter in the matrix. Because Excel’s implementation of the above features is so complete, this one probably felt like a freebie for people reading about new features. But I know improving the rendering was no simple feat, but this is a big win for OLAP PivotTable users.

Slicers

Ok I can’t claim my team did everything before Excel, and don’t mean to. Although we had global filters, they are nothing compared to slicers. Follow that post and see how easy to use slicers are for the average report user. It’s just amazing! The layout is customizable and that themes are enabled. I think the Excel team did a fantastic job here, and report authors who currently are doing a lot of custom VBA coding and using form controls, really will save a lot of time in the future with slicers.

Business rules and Design-time formulas

I still think rules was an area where PPS Planning shined. I’ll grant you that the V1 UX for maintaining rules was quite a bit of work. But I know some people still found them too complicated. So they asked for design-time formulas in the AddIn. I think clearly my team’s AddIn didn’t get this one right. We missed realizing that what people really wanted was a way to express rules and calculations in a language they already knew: Excel formulas. The Gemini team I think is doing a great job here. The collaborated a lot with the Excel team and I think DAX looks great. Naturally there are a few differences, but check out this link to Data Analysis Expressions (DAX) and I think you’ll agree with me that this looks very cool.

 

 

If you take all these features (and of course PivotTable writeback) you have probably some 90% of the PPS Planning AddIn, as well as some of the features of Business Modeler. It’s not at all the same kind of product that PPS Planning was, but I think is clearly a platform that enables building the same kind of solution. (Remember, except for Gemini which also suffers a bit from the AddIn model, that everything implemented as a native feature of Excel is accessible through the object model!) It’s been so hard to sit quiet while news of Office 2010 was embargoed. But these features I call out here, are what made continue to have faith in my employer. (And I’m just cherry picking the features from Excel 2010 and Gemini that overlapped with PPS Planning scenarios.)

So in a previous post I tried to tear down Gemini to something relatively simple, instead of curing cancer. This post is when I build it back up. Gemini just lets end-users combine data without having to get IT and DBAs involved. This is precisely where some fear begins to be revealed by IT and DBAs and practitioner partners. It’s twofold because some fear for their jobs and others fear for their sanity. (Disclaimer: I’m not on the Gemini team, I’m just talking about my interpretation of all the demo’s I’ve seen and the experimentation that I’ve done. If you are wondering what all the excitement is about, the Gemini team has instructions on how to get access to preview builds here.)

Anyway, I think these fears of Gemini are unwarranted because regular folks don’t have access to raw database tables. (Right? If they do, then I have a lot of fear about your systems.) The integration shown in demos with data feeds and Reporting Services reports looks much more like the kind of data sources that regular folks will consume. IT publishes data in a relatively bland report, which is fairly cheap and easy for them. Power users and some adventurous non-power users will consume these kinds of published models, not building them from scratch. (Ok some people will build them from scratch, but only if you give them access to raw tables. Which not a fault of Gemini. And power users are already beyond your control). In this scenario, yes DBAs who are building trivial models, need a little fear. But just enough to motivate them to move on to the harder problems.

Then we are back to the all-important single version of the truth. That holy grail that IT and DBAs seem to think has been achieved. There is great fear that Gemini will destroy that palace and the empire of IT will be overrun by trolls and gremlins. Ha! What I think the fear-mongers are missing is: end users are already building multiple versions of the truth that are different from the IT version of the truth! Even in places that have the strongest data warehousing implementations. Worst of all, this alternate-reality-version-of-the-truth data goes directly to senior management!

They are doing this with such dastardly tools as Excel and PowerPoint. But it’s so hard to get data into Excel, how can they possibly do this?! They are copy/pasting data or manually entering it while reading from another source. Then they build presentations and give them to senior management. Who is there to make sure that the data still matches the one-truth? Nobody. I just don’t believe that you present by to the board of directors by clicking refresh in a web browser to look at a dashboard. (Well, I wish you would since I work on PerformancePoint Services for SharePoint. Maybe, if you’re lucky, the audience goes back to their desk and does this, but I doubt it.) Anyway, my point should be clear: When people are truly making monumental decisions based on data, it does not come directly from the one-truth-palace, it comes from false prophets!

So how do I think Gemini helps instead of maintaining the status quo? With the data size minimized, there’s no reason not to include the data model in the workbook. That way it can be examined and vetted – especially if you leave the connections back to the data sources. Now the new version of the truth is documented and it has a clear link to the original version of the truth! So if you continue in this vein, why not embed the entire workbook+Gemini into the presentation? Now people with access to the slides also have the data, so they can dive into the assumptions instead of heckling from the back row. I think this is a huge advance. I don’t know if it was intended to bring the one-truth to presentations, but I think a Gemini-style solution is absolutely critical to a one-version-of-the-truth solution. It acknowledges multiple truths, but provides a mechanism to explain how and why this truth is better.

I think that’s pretty powerful, and I’m disappointed that commentators haven’t come to the same conclusion I am. Or maybe I’m wrong? Flame away if you have comments…I can take it… Well at least I can delete them if it hurts too much!

(Again, I’m not on the Gemini team, and I’m not a spokesperson for Microsoft in any shape. I’m just talking through through what I see, and why I am thinking differently than others about what Gemini can do for end-users.)

0 Comments
Filed under: , ,

Most of the people reading this blog have probably also seen demos or at least heard of Gemini and it’s magical powers of solving all BI problems known and unknown. (And you probably want to be looking at it. You can see if you get accepted to the CTP from the instructions here. I understand that you’ll automatically get the preview of Office 2010 if you’re accepted…)

From my perspective of implementing the PerformancePoint Planning Excel AddIn, I can say Gemini would have been a huge help. The compressed size of Gemini models is amazing compared to a local cube file. Not to mention the hassle of maintaining the cached copy outside the workbook and assorted issues of how to keep workbooks in sync with the data. Plus the query speeds really are fantastic. There’s just that little detail of what-if queries…

In the reporting and data analyst space, I think Gemini looks like a huge help. I see lots and lots of fear about how Gemini will be managed and the mess that ordinary users will make. (Disclaimer: I’m not on the Gemini team, I’m just talking about my interpretation of all the demo’s I’ve seen and the experimentation that I’ve done.) With what is in the CTP I would not be afraid of ordinary users. They probably won’t create complicated data models yet so not too much can go wrong. And for reporting to always have the report and the data in sync, seems like a huge help. For scenarios where the data needs to be refreshed at specific intervals or on demand the SharePoint integration I think hides most of the complexity from most of the users. I’ll come back to fear-mongering in another post.

For data modeling, I think the Gemini user experience is a huge leap forward as compared to BIDS. I think this will open up some data modeling capabilities to regular folks. But obviously it doesn’t do everything, but it feels like the kind of tool that end-users can work with. The kind of thing they were already doing manually in Excel with lots of temp tables and macros and manual intervention. For DBAs maybe it still looks limiting, but they are welcome to continue using BIDS. End users want to give up the fragile temp tables and macros.

From the PerformancePoint Planning experience, I know people want an easier way to define business rules. PEL was an interesting step, but it didn’t go far enough. It was easier than MDX, but not as easy as Excel formulas. Gemini takes it to the logical conclusion of as close to Excel formulas as possible. Now I have great faith that business users can achieve the result of defining business rules without a DBA intervening.

Clearly Gemini is not a magic cure-all. But it’s a huge step in the direction of making end-users able to self-serve their simple BI needs. When the data already exists but maybe in different silos. Or they need to provide some assumptions that can’t be edited in a LOB system. Self-service doesn’t have to mean you can prepare a 10 course meal. But you can pour the soda into a cup, and pull a salad together from the buffet line without having to bring in a chef from Le Cordon Blue too. Gemini is definitely the right direction, it will be interesting to see how close they get to the 10 course meal…

Again, I’m not on the Gemini team, and I’m not a spokesperson for Microsoft in any shape. I’m just talking through what I see, and why I am thinking differently than others about what Gemini can do for end-users. Flame away if you think I’m crazy, maybe I won’t delete the comments.

Well radio silence has been maintained quite successfully. Sorry for the lack of updates but with Planning development ended I haven't had a lot to say… Office 2010 (aka Office 14) is finally starting to see the light of day. I know some of you by now have gotten your hands on the Technical Preview and are checking things out. Others probably have watched some of the video recordings that have come out recently.

My team has been mainly working on the Monitoring and Analytics components in SharePoint lately. Not many details are out about SharePoint yet either, so I'll keep quiet on that front. Anyway, most of our work has been under the hood, rather than new feature-related so I won’t have much to say.

So onwards to Excel 2010. The videos I've seen are covering the new Slicer and SparkLine functionality so you should check that out. I like both of them a lot. We got some feedback about how Planning's filters rendered, and I think having end users see all the available choices with slicers, rather than having to navigate to a drop down or through a tree is going to be much more user friendly.

What I haven't seen in videos yet, but am very excited about, is PivotTable what-if and writeback for OLAP (well at least for Analysis Services). I've had to keep quiet about this for a long time. But I've finally seen a brief mention in the official blog and some people are actually using it!

The PPS Planning team had some great opportunities to provide feedback about this feature to make sure it would meet our (expected) needs, as well as pass along the feedback we had about data entry in the matrix. This is probably 40-50% of the functionality in the Planning add-in that is must-have from customers and partners I've talked to. (another 30-40% I would say is workflow but that’s a topic for another post). I think it turned out quite well.

I know a lot of Partners are frustrated with the PPS Planning situation, but I really think you should look at Excel 2010 for your next generation solutions. Excel is now able to participate fully out of the box -- custom code is not required on the client for some of the hardest parts. And it is fully supported through the object model so if you need to have customize, or need to provide additional validation etc, it's all possible. Having writeback as a native feature of Excel is fantastic, so many customizations that weren't supported by the Planning add-in are possible. Plus you get full access to the writeback features of AS, so you aren't limited to the strange spreading mechanisms that the Planning addin forced you to use.

I can’t wait to hear about partner’s using PivotTable writeback.

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.)

More Posts Next page »
 
Page view tracker