I’ve been invited to share the Customer Experience Webinar in late October 2022 (about a month away), with DPM as the focal point. I’ve done a lot with DPM and I’m still planning to do more. DPM is powerful and resilient and has lots of great features. Yet it’s also requires a quasi-inverted form of thinking from intuitive process logic, since all calculations must take place in the context of the current time period.
On top of that, DPM is only part of the picture; one piece of the puzzle, if you will, mostly focused on the “P” part of FP&A. Designing a full analysis solution comparing plans to actuals requires going beyond DPM, and sometimes bridging that gap is conceptually difficult, but powerful when we get there.
So, I’d like to get the community’s input on the types of DPM topics you’d like to see covered. I plan to show some things I find useful, even if they might be somewhat complicated, but I don’t want to spend group time on complicated solutions that no one is interested in. Here are some ideas I’m considering showing in the webinar; I’d love to hear your feedback on which of these are more interesting than others, and of course please chime in with your own ideas of what you’d like to see that aren’t on this list.
Comparing DPM Staff Plans to Actuals
Calculating Finances based on Project Milestones
Using DPM to enable end users to manage cube hierarchies
Hi Bob- this sounds like a great webinar topic! Idea #1 about comparing DPM Staff Plans to Actuals sounds intriguing.
I would also be interested in your use of Named Sets for DPM - we have a few but they are MDX formulas that a Prophix rep helped us set up - do you have any that you find useful?
Do you use multiple Scenarios within the DPM? When do you tend to delete old Scenarios?
These are great questions and we can speak to them as part of the discussion. But I can also answer some of them here.
Scenarios: currently we have only 1 active scenario per cube.
I generally have a “test” scenario as well, but I find that having multiple scenarios makes object publishing confusing. It will only publish the objects and member properties related to the “default” scenario, and only when the default scenario is published. So if I have objects dedicated to my test scenario, I have to change the default scenario to the test, post the data, then change the default back.
Also, I find multiple active scenarios to be difficult to keep up to date.
I do keep copies of inactive historical scenarios because I’m a history junkie. By that I mean I’ll make a copy of the default scenario at the end of each reforecast, then disable it, before updating the default scenario for the next reforecast. I do this so that I can go back to it if I need to. After many cycles I’ll sing a little lament and delete some really old ones.
Now, we haven’t yet gotten into scenario forecasting based on what-ifs. We do have plans for that but none of our end users that need the what-if scenario planning are familiar enough with Prophix DPM to feel comfortable doing that themselves. So this is an area I can’t yet speak to from experience.
Named Sets based on DPM: I love this idea and in fact I’m increasing its use. I’ll have attributes in DPM that get posted to Member Properties in the main cube, then I’ll have MDX-based named sets that are based on those property values. This gives me a centralized way to manage many named sets, ensures objects are only in one named set at a time, and helps me drive infoflexes or templates based on those named sets.
@dean.obrien, there’s a great training course on the academy that I highly recommend. It wont make you a master but it is indispensable as an introduction.
There are no templates but there are starter models for staff planning. But seriously, it’s so complex that an introductory course is highly recommended.
All of that said, if you just want to jump in and poke around, start by creating a cube, selecting the option of “Detailed Planning” instead of the default choice. Plan to throw your first one away, so name it something you don’t mind deleting.
Interested to learn about other ways to utilize DPM. We have recently contemplated using it for our COGS planning, with Recipe and raw material landed costs…we have a regional org structure so, this idea had come to mind. Curious to hear if another else has experience using it in this manner. Also, can’t wait to attend this, as the mere exposure to the tool, can help strike up more creativity that applies to our business. Thank you!
For MDX - did you have training on MDX? I have limited knowledge on this topic so I would be interested in training for that.
Scenarios - I’m not sure what your DPM looks like, but I have the option to choose which Scenario I would like to publish. Currently, our Default is 2023 Plan, but I can publish our other two Scenarios by using the drop down that pops up after I select “Post Data”:
Hi @angel.feuerhelm.
No MDX training–I hunt on Microsoft’s website for technical details, but I often find that insufficient because I’m not sure how it fits into the Prophix world. This is one area I wish Prophix had better documentation and training. That said, it’s very technical and basically developer-level material, so it’s not for the faint of heart. (I was a software developer in my earlier years, so SQL is something I’m very familiar with, and I find MDX to be quite a different animal, more like VBA for Databases.)
So what I do is start with an MDX query that one of the Professional Services guys did for me in another area, and then work from that. Perhaps we should start an MDX Support Group here on Red Carpet where we can share examples and learnings!
I’ll reply later today with the examples that I use for named set filtering.
Regarding the Scenarios, I have the ability to choose different scenarios when I publish. But what I found is that if I publish a scenario that isn’t the default, then the member properties don’t get updated. So, if I’m relying upon member properties to drive my MDX-based named sets, I need to work with the default scenario.
@angel.feuerhelm, here’s a full end-to-end description of how I use attributes in DPM to drive my template members, based on an MDX-based named set. I’m showing screen shots for each step, then I’ve got the MDX formula at the very bottom.
In this example, I’m using DPM to manage a JOB object in my JOBS cube, and based on which stage the job is in, a different template is used for forecasting.
Here are the JOB Named Sets in Model Manager.
Notice the “Fx” icon, meaning MDX formula-based. (We’ll review the MDX formula below; you’ll be able to see it better there.)
In Template Studio
The Data View for the template uses the appropriate Named Set. (As you can see, in this case it’s part of a Page Hierarchy group joining many datasets together, but the idea is the same whether in a Page Hierarchy Group or a single data view.)
So, as you suggested in your question, the key is in the MDX formula. Here’s the MDX formula I use for this purpose. It works in any named set as long as you spell the member property name and the DPM attribute value correctly.
This formula filters out any Leaves of the JOB dimension that has the PLAN STAGE property value of PROJECT ANALYSIS. (The other MDX formulas are exactly the same except for the “Project Analysis”; I have one for each possible value in the first screen shot.)
Substitute "Plan Stage" with the name of the member property. (Usually this is the same name as the attribute, but it doesn’t have to be. (This would be the value specified in the first screenshots 4th arrow.)
Substitute "Project Analysis" with the item in the DPM Attribute value list that corresponds to the template you’re looking for.
HI @michael.curley. I agree, that would be a nice end-user feature. DPM Schedules leave a lot to be desired. I keep hearing rumors that there’s an “upgraded DPM coming soon” that will give us better formatting capabilities than we have today, but I don’t have any foresight into that for real. I’m treating that as a rumor at this point until we start getting previews from official Prophix sources.
To those of you watching this thread, you probably noticed that October’s webinar was not Detailed Planning Mania, as expected. We’ve rescheduled it for December 14th. The CEW team was gracious enough to let me reschedule at almost the last minute, due to my tendency to be overly optimistic about how much I can get done at the same time.
That said, here’s an example of something I expect to show. One of our roles is construction of multi-tenant housing, and we’ve recently converted a report that the executives use to see what’s happening from Excel to Prophix. In Prophix, we use DPM to lay out the initial “hypothetical” schedules, cost, and revenue projections, and then fold in other sources as the project advances through the various stages of development (due diligence, construction planning, construction, and finally lease-up and transition to property management). This was a complex endeavor heavily using DPM, multiple schedules for different people providing different data throughout the life cycle, data imports from external systems and other Prophix cubes, and finally a feed to the corporate budgeting cube for cash flow planning and budgeting purposes.
But to make this report really be effective for our executives, we needed to go beyond mere monthly numeric values, so we used some tricks to show a Gantt-like summary with a separate report to show detailed milestones. With so many people and systems contributing to every job, we decided to produce a report binder daily as we lead into the budgeting season so that they can see multiple drafts of the report, giving users a chance to correct the data before it goes to the executive team.
