September 24th, 2020
Now that you have read the introduction to Planning Analytics Workspace (PAw) blog found here, it is time to talk Input and Spread Data in Planning Analytics Workspace. If you are not using PAw today you are most likely adding in data to your models via Architect, TM1 Web or through Excel. Yes, these options are still available today in Planning Analytics (PA), however there is a much better way to do this and get instant visual results. This is the input and spread data in Planning Analytics Workspace option that I will detail below. At the bottom of this blog you will receive a video link showing how this is done!
Inputting data into Planning Analytics Workspace is like other PA components. First and foremost, the cell or cells you want to input in must be writable. This means there can be no rules in the cell or any other barriers to entry. Once set to input data, you can input in each cell or input in the rollup to breakback equally across all the cells below. See the example screen shots below. I simply entered 1000 in the total year cell and the input info is blue. Once I hit enter you will see the number turn black and spread equally across the months. Once you hit enter that data has been committed to the cube and you can move on to your next input.
Now that we have talked about data input, let’s talk about spreading data. As stated above, you can enter in the parent level and it will auto spread equally across the children. However, this may or may not make sense in your input. PA offers several different spread options that include:
- Proportional - The Proportional spread method distributes a specified value among cells proportional to existing cell values.
- Equal Spread - The Equal Spread method distributes a specified value equally across the cells in a view.
- Repeat - The Repeat method repeats a specified value across cells in a view.
- Clear - The Clear method clears values from cells in a view.
- Percent Change - The Percent Change method multiplies the current cell values by a specified percentage.
- Repeat Leaves - The Repeat Leaves method copies a specified value to the leaves (children) of a consolidation.
- Equal Leaves - The Equal Leaves spreading method distributes a specified value equally across all leaves of a consolidated cell.
- Straight-Line - The Straight-Line data spreading method populates cube cells by linear interpolation between two specified endpoints.
- Growth Percent - The Growth Percent method takes an initial value as a starting point and sequentially increments all values in a selected range of cells by the specified percentage.
- Relative Proportional - Use the Relative Proportional spread method to spread values to the leaves of a consolidation cell proportional to the leaves of a reference cell.
- Relative Percent Adjustment - The Relative Percent Adjustment method spreads values to the leaves of a consolidation by applying a percentage adjustment to the leaves of a reference cell.
Visit here for more in-depth information on data spreading options.
To access spreading in PA simply right click on the cell in question and choose spread from the drop down. There are also keyboard shortcuts to spread, you can find more info here.
Choose the spread you want and follow the instructions. Below I will show screen shots of a relative proportional spread based on a different product.
The result is a spread of 8,000 units based on the 3G, 32GB phone in the line above. The system spread the 8,000 units based on the spread of the 10,258 units.
Other Tips for Data Entry
Since we have covered input and spread data in Planning Analytics Workspace, it is time to provide some additional tips and info on data entry.
Hold Cell – to hold a value in a cell that you do not want changed, you simply right click on choose hold. There may be times where you do not want a particular month to change but want the rest of the months adjusted by a percent or dollar value. Simply hold the cell and make your changes. To release the hold, right click and choose release hold.
Show Cell Values as – Right click and select, show cell values as, to change the view of the exploration. You have several different options as shown below in the screenshot. This is an easy way to change the analysis and gain more insights into your budget or data.
Trace Cell – Want to know where that number comes from? Simply right click and choose trace cell and have your answers.
Comments – From time to time you may have a need to put a comment on a cell, like in Excel. Right click and choose comment and add in your information that you want others to see. Others have the option to read and add their own comments based on your comment.
Planning Analytics Video Series
Recently, Lodestar created a Planning Analytics video series that goes through Planning Analytics Workspace and Planning Analytics for Excel. It includes overviews, modeling tips, input and spread options, sandboxes, virtual hierarchies, an admin overview and a how to on how to promote from development to production. Stay tuned to our blog for new info and links each week.
Here is the link to this video in the series.
This video is the second of our new, 7-part Planning Analytics series. Click here now to join our Planning Analytics mailing list and receive a link to each video as they are released as well as all our blogs on IBM Planning Analytics.
Planning Analytics Workspace is an incredible addition to TM1 and now Planning Analytics. For years, many had to find other options like Cognos Analytics or other BI tools to report their budget and planning data from TM1. Having one tool to input data and see the results instantly in a visualization is a great add to an already great tool. If you need help using Planning Analytics and Planning Analytics Workspace please reach out to firstname.lastname@example.org Thank you for reading this blog on Input and Spread Data in Planning Analytics Workspace Stay tuned for the next blog and video in the series called, Sandboxes in Planning Analytics Workspace.